|
Analysis Services 2005 - Performance Counter Guidance
|
|
Traditionally we have shied away from noting specific values or thresholds that are indicative of good or bad performance. One reason for this is because coming up with good values is quite hard to do, and people sometimes see that a particular value is outside of some threshold and become fixated on that being the issue when in reality it may not be. For example, the Windows NT Resource Kit had a section that stated that a disk queue length greater than two to three times the number of disk spindles was indicative of a performance problem. When working with SQL Server this is not always true, especially if read ahead activity is driving the disk queue length. Just because there is a queue waiting for IO does not necessarily mean that SQL Server is stalled waiting for the IO to complete. We have seen disk queue lengths up in the 20-30 range (on much fewer than 10 disks) where SQL Server performance was just fine. However, in the absence of specific values people sometimes look at Performance Monitor data and fail to spot interesting trends. So despite the hesitation to provide any specific values, we will attempt to note some thresholds where a given counter starts to attract the interest of those who have been working some of these cases over the past several years. Hopefully this will provide you some guidance in things that stand out. But ultimately, you will need to look for other counters that also seem out of range so that you can start developing a pattern of indicators that point to a particular type of problem. It should be fairly easy for you to visually identify a counter whose value changed substantially during a problematic time period. Quite often you will find that there are many counters that changed significantly. With a blocking problem, for example, you might see user connections, lock waits and lock wait time all increase while batch requests/sec decreases. If you focused solely on a particular counter (or a few counters) you might come to some very different conclusions about what the problem is, and you could very likely be wrong. Some of the changes in counter values are the cause of the original problem, whereas others are just side affects from that problem. In the ideal situation, the change in the counters that indicate the cause of the problem should lead the counters showing the affect, but due to the granularity used to capture Performance Monitor data some of these distinctions can be lost. If you collect data once every 15 seconds and the problem was of quick onset, it can be hard to figure out if user connections went up first and then lock timeouts, or vice versa. This is where you have to use other available information, such as other performance counters, the customer’s description of the problem, etc, to form a theory as to what you think may be wrong and then look for other supporting data to prove or disprove your theory. The most important counters are in blue. It is recommended to save the counters to a CSV file or a SQL Server database. The sample rate should be every 15 seconds.
This is the list of the SQL Server counters only. Please also use the Windows Server Performance Counters. |
|
SSAS Query Counters
|
|||
|
Object |
Counter |
Preferred Value |
Description |
|
MSAS 2005:Memory |
Memory Limit Low KB |
See description |
Displays the Memory\LowMemoryLimit from the configuration file
|
|
MSAS 2005:Memory |
Memory Limit High KB |
See description |
Displays the Memory\TotalMemoryLimit from the configuration file. |
|
MSAS 2005:Memory |
Memory Usage KB |
|
Displays the memory usage of the server process. This is the value that is compared to Memory\LowMemoryLimit and Memory\TotalMemoryLimit. Note that the value of this performance counter is the same value displayed by the Process\Private Bytes performance counter. |
|
MSAS 2005:Memory |
Cleaner Balance/sec |
around 2 |
Shows how many times the current memory usage is compared against the settings. Memory usage is checked every 500ms, so the counter will trend towards 2 with slight deviations when the system is under high stress. |
|
MSAS 2005:Memory |
Cleaner Memory nonshrinkable KB |
|
Displays the amount of memory, in KB, not subject to purging by the background cleaner. |
|
MSAS 2005:Memory |
Cleaner Memory shrinkable KB |
|
Displays the amount of memory, in KB, subject to purging by the background cleaner. |
|
MSAS 2005:Memory |
Cleaner Memory KB |
|
Displays the amount of memory, in KB, known to the background cleaner. (Cleaner memory shrinkable + Cleaner memory non-shrinkable.) Note that this counter is calculated from internal accounting information so there may be some small deviation from the memory reported by the operating system. |
|
MSAS 2005:Memory |
AggCacheKB |
|
Current memory allocated to filestore (file cache), in KB. |
|
MSAS 2005:Memory |
FileStoreKB |
|
Current memory allocated to filestore (file cache), in KB. |
|
MSAS 2005:Memory |
in-memory dimension index (hash) file KB |
|
Current in-memory dimension index (hash) file KB. |
|
MSAS 2005:Memory |
in-memory dimension property file KB |
|
Current in-memory dimension property file KB. |
|
MSAS 2005:Memory |
in-memory dimension string file KB |
|
Current in-memory dimension string file KB |
|
MSAS 2005:Memory |
in-memory fact aggregation file KB |
|
Current in-memory fact aggregation file KB |
|
MSAS 2005:Memory |
in-memory fact data file KB |
|
Current in-memory fact data file KB |
|
MSAS 2005: Storage Engine Query |
Queries from Cache Direct / sec |
|
Rate of queries answered from cache directly. Find the ratio between (Queries from cache direct + Queries from Cache Filtered ) / Queries from file |
|
MSAS 2005: Storage Engine Query |
Queries from Cache Filtered / Sec |
|
Rate of queries answered by filtering existing cache entry. Find the ratio between (Queries from cache direct + Queries from Cache Filtered ) / Queries from file |
|
MSAS 2005: Storage Engine Query |
Queries from File / Sec |
|
Rate of queries answered from files. Find the ratio between (Queries from cache direct + Queries from Cache Filtered ) / Queries from file |
|
MSAS 2005: Cache |
Direct hits / Sec |
|
Rate of cache direct hits. Queries were answered from an existing cache entry. |
|
MSAS 2005: Cache |
Lookups / Sec |
|
Rate of cache lookups. |
|
MSAS 2005: Cache |
Direct Hit Ratio |
|
Ratio of cache direct hits to cache lookups, for the period between obtaining counter values.
|
|
MSAS 2005: Connection |
Current connections |
|
Current number of client connections established (usually equal to the number of User Sessions) |
|
Process (msmdsrv) |
IO Read Bytes/sec |
|
|
|
MSAS 2005: Locks |
Current Latch Waits |
|
Current number of threads waiting for a latch. These are latch requests that could not be given immediate grants and are in a wait state. |
|
MSAS 2005: Locks |
Current Lock Waits |
|
Current number of clients waiting for a lock. |
|
MSAS 2005: Threads |
Query Pool job queue Length |
|
Number of jobs in the queue of the query thread pool. |
|
Processing Counters
|
|||
|
Object |
Counter |
Preferred Value |
Description |
|
MSAS 2005:Proc Aggregations |
Temp file bytes written/sec
|
|
temporary files used during processing |
|
MSAS 2005:Proc Aggregations |
Temp file rows written/sec |
|
temporary files used during processing |
|
MSAS 2005:Proc Aggregations |
Rows coverted/sec |
|
|
|
MASAS 2005: Processing |
Rows read/sec |
|
|
|
MASAS 2005: Processing |
Rows written/sec |
|
|
|
|
|||