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

 

 

 

Performance Counters for SSAS 2005                                                                                     

( SQL Server Premier Field Engineers in Microsoft UK )