August 13, 2008 at 10:21 am
I have one of my server running very slow, what range of data can tell me where the problem occurs while reading following data below?
MySQL\SQLServer:Latches\Average Latch Wait Time (ms)MySQL\SQLServer:Latches\Latch Waits/sec
MySQL\Memory\Page Faults/sec
MySQL\Memory\Pages/sec
MySQL\PhysicalDisk(_Total)\Avg. Disk Read Queue Length
MySQL\PhysicalDisk(_Total)\Avg. Disk sec/Read
MySQL\PhysicalDisk(_Total)\Avg. Disk sec/Write
MySQL\PhysicalDisk(_Total)\Avg. Disk Write Queue Length
MySQL\SQLServer:Access Methods\Full Scans/sec
MySQL\SQLServer:Buffer Manager\Buffer cache hit ratio
MySQL\SQLServer:Buffer Manager\Lazy writes/sec
MySQL\\RPTSRVR4\SQLServer:Locks(_Total)\Average Wait Time (ms)4
MySQL\SQLServer:Locks(_Total)\Lock Waits/sec
Any Clue?
August 13, 2008 at 10:41 am
I know only about fewer ones:
MySQL\PhysicalDisk(_Total)\Avg. Disk sec/Read Under 50 ms
MySQL\PhysicalDisk(_Total)\Avg. Disk sec/Write Under 50 ms
MySQL\SQLServer:Buffer Manager\Buffer cache hit ratio
95-100 percent
MySQL\Memory\Page Faults/sec--As close to zero as possible.
MJ
August 13, 2008 at 7:48 pm
Define "running slow". Are the disks grinding away, are users submitting queries and getting long wait times? Can you log on to the console without massive delays?
Start with basics then drill down from there:
Processor: % Processor time
If this is high, check which process is causing it (probably sqlservr, but you never know).
You can then check the sqlservr process threads to see if it is a couple of threads or
lots of things that run briefly but hit the CPU hard.
See this thread for more detail on tracking that:
http://www.sqlservercentral.com/Forums/Topic493141-146-2.aspx#bm495290
Memory: Pages/sec
This should be low (< 20 per second according to MS) to non-existent.
If this is excessive, it can indicate a lack of RAM.
SQL Server Buffer Manager: Page Life Expectancy
Should be > 300. If not, can indicate lack of RAM, causing SQL Server to constantly
retrieve data from disk.
SQL Server Buffer Manager: Total pages, Target pages
Covers what SQL Server would like in the way of RAM for buffer vs what it actually has.
Target should be >= total.
Logical Disk: % Idle Time, Current Disk Queue Length
An indicator of how hard your disks are working. Not entirely useful
with SANs, but if % Idle Time is very low and Disk Queue Length
is very high it can indicate an issue with the disk subsystem.
Profiler:
Run SQL Profiler, trace SP:Completed, RPC:Completed, SQL:BatchCompleted
and filter on Duration > 1000, see what turns up. Your problem could be
a poorly written query/stored procedure or a missing index (I've been coming
across a lot of these myself lately - just taken over managing someone else's
mess)
That should give you some starting points at least.
MARCUS. Why dost thou laugh? It fits not with this hour.
TITUS. Why, I have not another tear to shed;
--Titus Andronicus, William Shakespeare
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply