The following list of performance counters have been modified to include the most pertinent in identifying the bottleneck of your system. This list is for reference purposes. If needing to troubleshoot slowness read troubleshooting sql server slowness.
Memory
Object: – Memory
Counter: – Available Mbytes
Preferred Value: – > 2000MB
Description: With 64 bit OS’s the available bytes recommendation has increased.
Object: – Memory
Counter: – Pages/Sec
Preferred Value: – < 50
Description: - Pages/sec is the rate at which pages are read from or written to disk to resolve hard page faults. Indicative of possible page file usage, and definitely a memory bottleneck somewhere on the system.
Object: - Paging File
Counter: - %Usage
Preferred Value: - < 5%
Description: - Current amount of Page file being used. This is a key number when weighing the amount of memory allocated to the OS. If this number is high, then the OS is choked for RAM. Either increase the RAM on the box or deallocate from SQL. If this is between 10% and 25% you should reboot.
Object: - Paging File
Counter: - %Usage Peak
Preferred Value: - < 15%
Description: - Show the peak the page file got to since the last reboot. Usually this is not too far off from the page file usage %. Unfortunately when page file issues happen, they do not correct themselves without a reboot.
Object: - SQL Server:Buffer Manager
Counter: - Page Life Expectancy
Preferred Value: - > 300
Description: – The single most important factor in determining whether your SQL Server has enough RAM allocated to it. The greater the number the better off you are. Historically it is said that over 300 is good. If this number is low and you do have a lot of RAM allocated, then the issue is probably lack of indexes. Index scans read in a lot of rows into the buffer pool whereas a seek only reads in a small subset. Investigate indexes.
Object: – SQL Server:Buffer Manager
Counter: – Buffer Cache hit ratio
Preferred Value: – > 90%
Description: – Percentage of pages that were found in the buffer pool without having to incur a read from disk. If this number is low, then you are probably reading from the paging file. Reboot.
Reference: –
Disk Bottleneck Analysis
Object: – PhysicalDisk
Counter: – Avg. Disk Sec/Read
Preferred Value: – < 8ms
Description: - Measure of disk latency. Avg. Disk sec/Read is the average time, in seconds, of a read of data from the disk. While it is better to have fast disk read times, this can easily be compensated for by allocating enough RAM to the server and to SQL.
More Info:
Reads or non cached Writes
Excellent < 08 Msec ( .008 seconds )
Good < 12 Msec ( .012 seconds )
Fair < 20 Msec ( .020 seconds )
Poor > 30 Msec ( .030 seconds )
Object: – PhysicalDisk
Counter: – Avg. Disk sec/Write
Preferred Value: – < 8ms (non cached) < 1ms (cached)
Description: - Measure of disk latency. Avg. Disk sec/Write is the average time, in seconds, of a write of data to the disk.
Reference: -
Object: - PhysicalDisk
Counter: - Avg. Disk Read Queue Length
Preferred Value: - < 2 * spindles
Description: - Avg. Disk Read Queue Length is the average number of read requests that were queued for the selected disk during the sample interval. If this is high, you are probably missing indexes; doing deletes where you could be doing truncates; or could simply be selecting too much data.
More Info:
< (2+ no of spindles) Excellent
< (2*no of spindles) Good
< (3* no of spindles) Fair
Note: If the disk has say 20 disk and it is RAID 10 then no. of spindles = 20/2 = 10. If it is RAID 5 then the no. of spindles = no of disks = 20.
Reference: -
Object: - PhysicalDisk
Counter: - Avg. Disk Write Queue Length
Preferred Value: - < 2 * spindles
Description: - Avg. Disk Write Queue Length is the average number of write requests that were queued for the selected disk during the sample interval.
Reference: -
Object: - SQL Server:Buffer Manager
Counter: - Page reads/sec
Preferred Value: - < 90
Description: - Number of physical database page reads issued. 80 – 90 per second is normal, anything that is above indicates indexing or memory constraint.
Reference: -
Object: - SQL Server:Buffer Manager
Counter: - Page writes/sec
Preferred Value: - < 90
Description: - Number of physical database page writes issued. 80 – 90 per second is normal, anything more we need to check the lazy writer/sec and checkpoint counters, if these counters are also relatively high then, it's memory constraint.
Reference: -
Processor
Object: – Processor
Counter: – %Processor Time
Preferred Value: – < 60%
Description: - % Processor Time is the percentage of elapsed time that the processor spends to execute a non-Idle thread. The consensus is generally that over 80% you officially have a processor bottleneck. What needs to be taken into consideration here is the number of cores and if hyper-threading is turned on. Aside from the fact that you should not turn on hyper-threading on a SQL Server box, if you do, this number should probably be around 50%. Hyper-threading is not a true processor, it is unused cycles of another processor. Also, if you have over 4 cores, I would recommend this number be no more than 55%. While high processor utilization may seem like a major issue, it is actually one of the best bottlenecks to have because it is the easiest to fix. It is usually lack of indexing.
Reference: -
Object: - SQLServer:Access Methods
Counter: - Full Scans / sec
Preferred Value: - < 1
Description: - If we see high CPU then we need to invistigate this counter, otherwise if the full scan are on small tables we can ignore this counter. Values greater than 1 or 2 indicates that we are having table / Index page scans. We need to analyze how this can be avoided.
Reference: -
Object: - SQLServer:Access Methods
Counter: - Worktables Created/Sec
Preferred Value: - < 20
Description: - Number of worktables created in tempdb per second. Worktables are used for queries that use various spools (table spool, index spool, etc). Even if you see this as high there is likely very little you can do about it. It requires rewriting your procedures.
Reference: -
Object: - SQLServer:Access Methods
Counter: - Workfiles Created/Sec
Preferred Value: - < 20
Description: - Number of work files created per second. Tempdb workfiles are used in processing hash operations when the amount of data being processed is too big to fit into the available memory. They may be able to reduce this number by making the queries more efficient by adding/changing indexes, adding additional memory, etc.
Reference: -
Object: - SQLServer:Access Methods
Counter: - Page Splits/sec
Preferred Value: - < 20
Description: - Interesting counter that can lead us to our table / index design. This value needs to be low as possible. If you find out that the number of page splits is high, consider increasing the fillfactor of your indexes. An increased fillfactor helps to reduce page splits because there is more room in data pages before it fills up and a page split has to occur.
Reference: -
Overall SQL Server Bottleneck Analysis
Object: – SQLServer:General Statistics
Counter: – User Connections
Preferred Value: –
Description: – The number of users currently connected to the SQL Server.
Object: – SQLServer:General Statistics
Counter: – Logins/sec
Preferred Value: – < 2
Description: - > 2 per second indicates that the application is not correctly using connection pooling.
Object: – SQLServer:General Statistics
Counter: – Logouts/sec
Preferred Value: – < 2
Description: - > 2 per second indicates that the application is not correctly using connection pooling.
Object: – SQLServer:SQL Statistics
Counter: – Batch Requests/Sec
Preferred Value: – > 300
Description: – The higher this number, the better. What it generally means is that your SQL Server can scale when needed. If you see peaks hit over 2000, then you are on your way to an optimized box. I have worked with servers that never got over 150. Then after changing a critical process like removing a scalar UDF from a computed column, or rewriting a critical process to not use looping, see this number hit over 500 to 2000.
Object: – SQLServer:SQL Statistics
Counter: – SQL Compilations/sec
Preferred Value: – < 10% of the number of Batch Requests / sec
Description: - Not a major indicator. I would not worry too much about it, however be aware of it.
Object: - SQLServer:SQL Statistics
Counter: - SQL Re-Compilations/sec
Preferred Value: - < 10% of the number of SQL Compilations/sec
Description: - No you don't want recompilations, and the higher the number here, probably the more temp tables you use. Not much you can do about this except change code.
Transaction Management
Object: – SQL Server:Locks
Counter: – Number of Deadlocks/sec
Preferred Value: – < 1
Description: – The number of lock requests that resulted in a deadlock.
Object: – SQL Server:Locks
Counter: – Lock Requests/sec
Preferred Value: – < 1000
Description: – Not a critical number but watch it. The higher it gets generally means you are missing indexes.
Object: – SQL Server:Locks
Counter: – Average Wait Time (ms)
Preferred Value: – < 500
Description: – This is the average wait time in milliseconds to acquire a lock. Lower the value the better it is. If the value goes higher then 500, there may be blocking going on; we need to run blocker script to identify blocking.
Reference: http://sqlserverplanet.com/optimization/performance-counters
Memory
Object: – Memory
Counter: – Available Mbytes
Preferred Value: – > 2000MB
Description: With 64 bit OS’s the available bytes recommendation has increased.
Object: – Memory
Counter: – Pages/Sec
Preferred Value: – < 50
Description: - Pages/sec is the rate at which pages are read from or written to disk to resolve hard page faults. Indicative of possible page file usage, and definitely a memory bottleneck somewhere on the system.
Object: - Paging File
Counter: - %Usage
Preferred Value: - < 5%
Description: - Current amount of Page file being used. This is a key number when weighing the amount of memory allocated to the OS. If this number is high, then the OS is choked for RAM. Either increase the RAM on the box or deallocate from SQL. If this is between 10% and 25% you should reboot.
Object: - Paging File
Counter: - %Usage Peak
Preferred Value: - < 15%
Description: - Show the peak the page file got to since the last reboot. Usually this is not too far off from the page file usage %. Unfortunately when page file issues happen, they do not correct themselves without a reboot.
Object: - SQL Server:Buffer Manager
Counter: - Page Life Expectancy
Preferred Value: - > 300
Description: – The single most important factor in determining whether your SQL Server has enough RAM allocated to it. The greater the number the better off you are. Historically it is said that over 300 is good. If this number is low and you do have a lot of RAM allocated, then the issue is probably lack of indexes. Index scans read in a lot of rows into the buffer pool whereas a seek only reads in a small subset. Investigate indexes.
Object: – SQL Server:Buffer Manager
Counter: – Buffer Cache hit ratio
Preferred Value: – > 90%
Description: – Percentage of pages that were found in the buffer pool without having to incur a read from disk. If this number is low, then you are probably reading from the paging file. Reboot.
Reference: –
Disk Bottleneck Analysis
Object: – PhysicalDisk
Counter: – Avg. Disk Sec/Read
Preferred Value: – < 8ms
Description: - Measure of disk latency. Avg. Disk sec/Read is the average time, in seconds, of a read of data from the disk. While it is better to have fast disk read times, this can easily be compensated for by allocating enough RAM to the server and to SQL.
More Info:
Reads or non cached Writes
Excellent < 08 Msec ( .008 seconds )
Good < 12 Msec ( .012 seconds )
Fair < 20 Msec ( .020 seconds )
Poor > 30 Msec ( .030 seconds )
Object: – PhysicalDisk
Counter: – Avg. Disk sec/Write
Preferred Value: – < 8ms (non cached) < 1ms (cached)
Description: - Measure of disk latency. Avg. Disk sec/Write is the average time, in seconds, of a write of data to the disk.
Reference: -
Object: - PhysicalDisk
Counter: - Avg. Disk Read Queue Length
Preferred Value: - < 2 * spindles
Description: - Avg. Disk Read Queue Length is the average number of read requests that were queued for the selected disk during the sample interval. If this is high, you are probably missing indexes; doing deletes where you could be doing truncates; or could simply be selecting too much data.
More Info:
< (2+ no of spindles) Excellent
< (2*no of spindles) Good
< (3* no of spindles) Fair
Note: If the disk has say 20 disk and it is RAID 10 then no. of spindles = 20/2 = 10. If it is RAID 5 then the no. of spindles = no of disks = 20.
Reference: -
Object: - PhysicalDisk
Counter: - Avg. Disk Write Queue Length
Preferred Value: - < 2 * spindles
Description: - Avg. Disk Write Queue Length is the average number of write requests that were queued for the selected disk during the sample interval.
Reference: -
Object: - SQL Server:Buffer Manager
Counter: - Page reads/sec
Preferred Value: - < 90
Description: - Number of physical database page reads issued. 80 – 90 per second is normal, anything that is above indicates indexing or memory constraint.
Reference: -
Object: - SQL Server:Buffer Manager
Counter: - Page writes/sec
Preferred Value: - < 90
Description: - Number of physical database page writes issued. 80 – 90 per second is normal, anything more we need to check the lazy writer/sec and checkpoint counters, if these counters are also relatively high then, it's memory constraint.
Reference: -
Processor
Object: – Processor
Counter: – %Processor Time
Preferred Value: – < 60%
Description: - % Processor Time is the percentage of elapsed time that the processor spends to execute a non-Idle thread. The consensus is generally that over 80% you officially have a processor bottleneck. What needs to be taken into consideration here is the number of cores and if hyper-threading is turned on. Aside from the fact that you should not turn on hyper-threading on a SQL Server box, if you do, this number should probably be around 50%. Hyper-threading is not a true processor, it is unused cycles of another processor. Also, if you have over 4 cores, I would recommend this number be no more than 55%. While high processor utilization may seem like a major issue, it is actually one of the best bottlenecks to have because it is the easiest to fix. It is usually lack of indexing.
Reference: -
Object: - SQLServer:Access Methods
Counter: - Full Scans / sec
Preferred Value: - < 1
Description: - If we see high CPU then we need to invistigate this counter, otherwise if the full scan are on small tables we can ignore this counter. Values greater than 1 or 2 indicates that we are having table / Index page scans. We need to analyze how this can be avoided.
Reference: -
Object: - SQLServer:Access Methods
Counter: - Worktables Created/Sec
Preferred Value: - < 20
Description: - Number of worktables created in tempdb per second. Worktables are used for queries that use various spools (table spool, index spool, etc). Even if you see this as high there is likely very little you can do about it. It requires rewriting your procedures.
Reference: -
Object: - SQLServer:Access Methods
Counter: - Workfiles Created/Sec
Preferred Value: - < 20
Description: - Number of work files created per second. Tempdb workfiles are used in processing hash operations when the amount of data being processed is too big to fit into the available memory. They may be able to reduce this number by making the queries more efficient by adding/changing indexes, adding additional memory, etc.
Reference: -
Object: - SQLServer:Access Methods
Counter: - Page Splits/sec
Preferred Value: - < 20
Description: - Interesting counter that can lead us to our table / index design. This value needs to be low as possible. If you find out that the number of page splits is high, consider increasing the fillfactor of your indexes. An increased fillfactor helps to reduce page splits because there is more room in data pages before it fills up and a page split has to occur.
Reference: -
Overall SQL Server Bottleneck Analysis
Object: – SQLServer:General Statistics
Counter: – User Connections
Preferred Value: –
Description: – The number of users currently connected to the SQL Server.
Object: – SQLServer:General Statistics
Counter: – Logins/sec
Preferred Value: – < 2
Description: - > 2 per second indicates that the application is not correctly using connection pooling.
Object: – SQLServer:General Statistics
Counter: – Logouts/sec
Preferred Value: – < 2
Description: - > 2 per second indicates that the application is not correctly using connection pooling.
Object: – SQLServer:SQL Statistics
Counter: – Batch Requests/Sec
Preferred Value: – > 300
Description: – The higher this number, the better. What it generally means is that your SQL Server can scale when needed. If you see peaks hit over 2000, then you are on your way to an optimized box. I have worked with servers that never got over 150. Then after changing a critical process like removing a scalar UDF from a computed column, or rewriting a critical process to not use looping, see this number hit over 500 to 2000.
Object: – SQLServer:SQL Statistics
Counter: – SQL Compilations/sec
Preferred Value: – < 10% of the number of Batch Requests / sec
Description: - Not a major indicator. I would not worry too much about it, however be aware of it.
Object: - SQLServer:SQL Statistics
Counter: - SQL Re-Compilations/sec
Preferred Value: - < 10% of the number of SQL Compilations/sec
Description: - No you don't want recompilations, and the higher the number here, probably the more temp tables you use. Not much you can do about this except change code.
Transaction Management
Object: – SQL Server:Locks
Counter: – Number of Deadlocks/sec
Preferred Value: – < 1
Description: – The number of lock requests that resulted in a deadlock.
Object: – SQL Server:Locks
Counter: – Lock Requests/sec
Preferred Value: – < 1000
Description: – Not a critical number but watch it. The higher it gets generally means you are missing indexes.
Object: – SQL Server:Locks
Counter: – Average Wait Time (ms)
Preferred Value: – < 500
Description: – This is the average wait time in milliseconds to acquire a lock. Lower the value the better it is. If the value goes higher then 500, there may be blocking going on; we need to run blocker script to identify blocking.
Reference: http://sqlserverplanet.com/optimization/performance-counters
No comments:
Post a Comment