\Performance Monitor captures statistical information about the hardware environment, operating system and any applications that expose properties and counters. It can collect and display real-time performance data in the form of counters for server resources such as processor and memory use and for many SQL Server–specific resources such as locks and transactions.
A data collector set provides the ability to group data collectors into reusable elements for different performance-monitoring scenarios.
The following is the basic terminology for Windows Performance Monitor:
- An object is a resource that can be monitored.
- An object exposes one or more counters.
- A counter might have several instances if more than one resource of that type exists.
NOTE: Minimise the number of counters to avoid being overwhelmed with data.
Because the only data Performance Monitor allows is numeric and processes are not being executed to calculate the values as data is gathered, the overhead for Performance Monitor is very small.
EXAM TIP : Be familiar with the basic performance objects and counters and be able to use that information to diagnose problems within a SQL Server instance. SQL Server is typically affected by the following bottlenecks:
- File I/O
- Locking, blocking, or deadlocking
We can use Performance Monitor to identify how these potential bottlenecks might affect SQL Server. When SQL Server is not the only process using the resources on a server, we can also use performance counters to determine whether that application is negatively affecting the performance on your SQL Server instance.
- Processor: An overwhelmed processor can be due to the processor itself not offering enough power or it can be due to an inefficient application. We must double-check whether the processor spends a lot of time in paging as a result of insufficient physical memory. When investigating a potential processor bottleneck, the Microsoft Service Support engineers use the following counters:
- % Processor Time - measures the number of processors actively performing work & the amount of time a given processor is in use. Measures the percentage of elapsed time the processor spends executing a non-idle thread. If the percentage is greater than 85 percent, the processor is overwhelmed and the server may require a faster processor.
- % User Time - measures the percentage of elapsed time the processor spends in user mode. If this value is high, the server is busy with the application. One possible solution here is to optimise the application that is using up the processor resources.
- % Interrupt Time - measures the time the processor spends receiving and servicing hardware interruptions during specific sample intervals. This counter indicates a possible hardware issue if the value is greater than 15 percent.
- Queue Length - This indicates the number of threads in the processor queue. The server doesn’t have enough processor power if the value is more than two times the number of CPUs for an extended period of time. It includes any SQL Server requests that are waiting for processor resources to be allocated, it also includes requests from any other applications and the operating system that are waiting on processor resources.
- Buffer Manager:
- performance object counter
- Page Life Expectancy counter is the number of seconds a page will stay in the buffer pool without references
- Free pages is the total number of pages on all free lists
- Page lookups/sec is the number of requests to find a page in the buffer pool
- Page reads/sec is the number of physical database page reads that are issued per second
- performance object counter
- Physical Disk:
- Avg. Disk Queue Length counter measures how many system requests, on average, are waiting for disk access.
- % Disk Time counter monitors the percentage of time that the disk is busy with read/write activity.
- Performance Monitor captures numeric statistics about hardware and software components
- A counter can have zero or more instances
- You capture counter logs with Performance Monitor to perform analysis
- Use Performance Monitor to capture metrics for Windows Server and SQL Server
- Performance Monitor to capture metrics for Windows Server and SQL Server
- A counter object must have at least one counter and are organised into a three-level hierarchy: counter object, counter, and counter instance
- Columns can be specified to capture for a given event
- Use SQL Server Profiler to define traces and then script them for SQL Trace
- Use dynamic management objects for real-time monitoring and troubleshooting
- Use Activity Monitor for easy access to performance information.
- The sys.dm_db_* DMVs provide general space and index usage information.
- The sys.dm_exec_* DMVs return information about currently executing queries and queries that are still in the query cache.