General monitoring for SQL Server Analysis Services using Performance Monitor

Posted by Testas on SQL Blogcasts See other posts from SQL Blogcasts or by Testas
Published on Thu, 27 May 2010 10:15:00 GMT Indexed on 2010/05/27 10:42 UTC
Read the original article Hit count: 540

A recent customer engagement required a setup of a monitoring solution for SSAS, due to the time restrictions placed upon this, native Windows Performance Monitor (Perfmon) and SQL Server Profiler Monitoring Tools was used as using a third party tool would have meant the customer providing an additional monitoring server that was not available.I wanted to outline the performance monitoring counters that was used to monitor the system on which SSAS was running. Due to the slow query performance that was occurring during certain scenarios, perfmon was used to establish if any pressure was being placed on the Disk, CPU or Memory subsystem when concurrent connections access the same query, and Profiler to pinpoint how the query was being managed within SSAS, profiler I will leave for another blogThis guide is not designed to provide a definitive list of what should be used when monitoring SSAS, different situations may require the addition or removal of counters as presented by the situation. However I hope that it serves as a good basis for starting your monitoring of SSAS. I would also like to acknowledge Chris Webb’s awesome chapters from “Expert Cube Development” that also helped shape my monitoring strategy:http://cwebbbi.spaces.live.com/blog/cns!7B84B0F2C239489A!6657.entrySimulating ConnectionsTo simulate the additional connections to the SSAS server whilst monitoring, I used ascmd to simulate multiple connections to the typical and worse performing queries that were identified by the customer. A similar sript can be downloaded from codeplex at http://www.codeplex.com/SQLSrvAnalysisSrvcs.     File name: ASCMD_StressTestingScripts.zip. Performance MonitorWithin performance monitor,  a counter log was created that contained the list of counters below. The important point to note when running the counter log is that the RUN AS property within the counter log properties should be changed to an account that has rights to the SSAS instance when monitoring MSAS counters. Failure to do so means that the counter log runs under the system account, no errors or warning are given while running the counter log, and it is not until you need to view the MSAS counters that they will not be displayed if run under the default account that has no right to SSAS. If your connection simulation takes hours, this could prove quite frustrating if not done beforehand JThe counters used…… 

Object	Counter	Instance	Justification
System	Processor Queue legnth	N/A	Indicates how many threads are waiting for execution against the processor. If this counter is consistently higher than around 5 when processor utilization approaches 100%, then this is a good indication that there is more work (active threads) available (ready for execution) than the machine's processors are able to handle.
System	Context Switches/sec	N/A	Measures how frequently the processor has to switch from user- to kernel-mode to handle a request from a thread running in user mode. The heavier the workload running on your machine, the higher this counter will generally be, but over long term the value of this counter should remain fairly constant. If this counter suddenly starts increasing however, it may be an indicating of a malfunctioning device, especially if the Processor\Interrupts/sec\(_Total) counter on your machine shows a similar unexplained increase
Process	% Processor Time	sqlservr	Definately should be used if Processor\% Processor Time\(_Total) is maxing at 100% to assess the effect of the SQL Server process on the processor
Process	% Processor Time	msmdsrv	Definately should be used if Processor\% Processor Time\(_Total) is maxing at 100% to assess the effect of the SQL Server process on the processor
Process	Working Set	sqlservr	If the Memory\Available bytes counter is decreaing this counter can be run to indicate if the process is consuming larger and larger amounts of RAM. Process(instance)\Working Set measures the size of the working set for each process, which indicates the number of allocated pages the process can address without generating a page fault.
Process	Working Set	msmdsrv	If the Memory\Available bytes counter is decreaing this counter can be run to indicate if the process is consuming larger and larger amounts of RAM. Process(instance)\Working Set measures the size of the working set for each process, which indicates the number of allocated pages the process can address without generating a page fault.
Processor	% Processor Time	_Total and individual cores	measures the total utilization of your processor by all running processes. If multi-proc then be mindful only an average is provided
Processor	% Privileged Time	_Total	To see how the OS is handling basic IO requests. If kernel mode utilization is high, your machine is likely underpowered as it's too busy handling basic OS housekeeping functions to be able to effectively run other applications.
Processor	% User Time	_Total	To see how the applications is interacting from a processor perspective, a high percentage utilisation determine that the server is dealing with too many apps and may require increasing thje hardware or scaling out
Processor	Interrupts/sec	_Total	The average rate, in incidents per second, at which the processor received and serviced hardware interrupts. Shoulr be consistant over time but a sudden unexplained increase could indicate a device malfunction which can be confirmed using the System\Context Switches/sec counter
Memory	Pages/sec	N/A	Indicates the rate at which pages are read from or written to disk to resolve hard page faults. This counter is a primary indicator of the kinds of faults that cause system-wide delays, this is the primary counter to watch for indication of possible insufficient RAM to meet your server's needs. A good idea here is to configure a perfmon alert that triggers when the number of pages per second exceeds 50 per paging disk on your system. May also want to see the configuration of the page file on the Server
Memory	Available Mbytes	N/A	is the amount of physical memory, in bytes, available to processes running on the computer. if this counter is greater than 10% of the actual RAM in your machine then you probably have more than enough RAM. monitor it regularly to see if any downward trend develops, and set an alert to trigger if it drops below 2% of the installed RAM.
Physical Disk	Disk Transfers/sec	for each physical disk	If it goes above 10 disk I/Os per second then you've got poor response time for your disk.
Physical Disk	Idle Time	_total	If Disk Transfers/sec is above  25 disk I/Os per second use this counter. which measures the percent time that your hard disk is idle during the measurement interval, and if you see this counter fall below 20% then you've likely got read/write requests queuing up for your disk which is unable to service these requests in a timely fashion.
Physical Disk	Disk queue legnth	For the OLAP and SQL physical disk	A value that is consistently less than 2 means that the disk system is handling the IO requests against the physical disk
Network Interface	Bytes Total/sec	For the NIC	Should be monitored over a period of time to see if there is anb increase/decrease in network utilisation
Network Interface	Current Bandwidth	For the NIC	is an estimate of the current bandwidth of the network interface in bits per second (BPS).
MSAS 2005: Memory	Memory Limit High KB	N/A	Shows (as a percentage) the high memory limit configured for SSAS in C:\Program Files\Microsoft SQL Server\MSAS10.MSSQLSERVER\OLAP\Config\msmdsrv.ini
MSAS 2005: Memory	Memory Limit Low KB	N/A	Shows (as a percentage) the low memory limit configured for SSAS in C:\Program Files\Microsoft SQL Server\MSAS10.MSSQLSERVER\OLAP\Config\msmdsrv.ini
MSAS 2005: Memory	Memory Usage KB	N/A	Displays the memory usage of the server process.
MSAS 2005: Memory	File Store KB	N/A	Displays the amount of memory that is reserved for the Cache. Note if total memory limit in the msmdsrv.ini is set to 0, no memory is reserved for the cache
MSAS 2005: Storage Engine Query	Queries from Cache Direct / sec	N/A	Displays the rate of queries answered from the cache directly
MSAS 2005: Storage Engine Query	Queries from Cache Filtered / Sec	N/A	Displays the Rate of queries answered by filtering existing cache entry.
MSAS 2005: Storage Engine Query	Queries from File / Sec	N/A	Displays the Rate of queries answered from files.
MSAS 2005: Storage Engine Query	Average time /query	N/A	Displays the average time of a query
MSAS 2005: Connection	Current connections	N/A	Displays the number of connections against the SSAS instance
MSAS 2005: Connection	Requests / sec	N/A	Displays the rate of query requests per second
MSAS 2005: Locks	Current Lock Waits	N/A	Displays thhe number of connections waiting on a lock
MSAS 2005: Threads	Query Pool job queue Length	N/A	The number of queries in the job queue
MSAS 2005:Proc Aggregations	Temp file bytes written/sec	N/A	Shows the number of bytes of data processed in a temporary file
MSAS 2005:Proc Aggregations	Temp file rows written/sec	N/A	Shows the number of bytes of data processed in a temporary file

 

© SQL Blogcasts or respective owner