March 5, 2008 at 4:46 am
HI Guys,
i am very new in SQL server 2005, i don't know much tools and features about sql 2005, Acutally i need some ideas. we are running a millions of records in a hours time and sql sever process is getting soo slow, it's take ages to finish my single query. and some time it's happend that our network speed get slow the processing get really really slow. @ the moment i m runing 2 database mirroring and replication on the server, i dont' know this might casue the slow down sql server processing,
althought we got the best system to run the processing..
if any body have any good idea please let me know. i mean like if is there any way to spliting up database into different databases on 2 ,3 server to process the things fastly .
Thanks and looking forward.
March 5, 2008 at 8:27 am
Sounds like a bottleneck somewhere although its difficult to say in the absence of any metrics. Are you experiencing memory pressure? I/O pressure? What are your batch requests per second? Take a look at the sys.dm_os_performance_counters DMV – i.e. buffer cache hit ratio, batch requests per second, etc. Check out SQL-Server-Performance.com for more information.
select *
from sys.dm_os_performance_counters
where counter_name = 'Batch Requests/sec'
select (a.cntr_value * 1.0 / b.cntr_value) * 100.0 as [BufferCacheHitRatio]
from (select *, 1 x from sys.dm_os_performance_counters
where counter_name = 'Buffer cache hit ratio'
and object_name = 'SQLServer:Buffer Manager') as a
join
(select *, 1 x from sys.dm_os_performance_counters
where counter_name = 'Buffer cache hit ratio base'
and object_name = 'SQLServer:Buffer Manager') as b
on a.x = b.x
select *
from sys.dm_os_performance_counters
where counter_name = 'Page life expectancy'
You might also want to consider using the performance dashboard or SQLH2 so that you can trend these over time.
Hope this helps, good luck 🙂
Tommy
Follow @sqlscribeMarch 5, 2008 at 9:34 am
some time i m hving I/O problems, but processing is too slow. now the server is working bit fine .. and i hv get the result..
SQLServer:SQL Statistics Batch Requests/sec 146302272696576
99.8725505814880
SQLServer:Buffer ManagerPage life expectancy 1461765792
SQLServer:Buffer Node Page life expectancy 0001461765792
i know this might not true @ the moment, bcoz this was taken when not that much problem is for process the data....
but this might can help u..
March 5, 2008 at 1:58 pm
There are a number of possible causes.
You could be hitting hardware bottlenecks. You could have poor table or index design, you could have poorly written queries. You could have all three.
Is the server always slow? Are there times when everything slows down? Are certain queries always slow?
As a first attempt at diagnosis, run perfon, and check the following counters:
Processor: User time
Memory: Pages/sec
Physical disk: sec/read, sec/write, transfers/sec, %idle time.
SQL Memory manager: Buffer cache hit ratio
There are others that I'm sure people will suggest.
You can use profiler to idenify badly performing queries. Run them in management studio with the exec plan switched on and look for table scans or clustered index scane. Both may indicate that your indexing is poor.
Look for cursors or single row selects in while loops. Both are really bad ways of doing things and if they can be converted to set-based code will run faster.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply