[Notes from Pinal]: Performance tuning is a complex subject, and not everyone knows where to start and how to handle this complex subject. There are so many different areas for performance tuning for example – tuning TempDB, the bottleneck at SAN, disk latency, etc. If novice users start on the subject of performance tuning they will easily get lost as they do not know where to start. This is where I requested help from Eduardo and he suggested that we should take help from DMVs to detect performance problems in SQL Server.
This blog contains some T-SQL Scripts and DMV that can be used to detect performance problems in SQL Server and will help to understand the origin of the problem.
When you have to optimize a SQL Server should review the following infrastructure components and programming:
- Bottlenecks in tempdb. Each instance of SQL Server has a unique tempdb database, which may involve performance problems, since we can a SQL Server instance with contention in tempdb or something more serious as lack of space. A poorly constructed TSQL using excessive resources can affect the performance of tempdb. This may cause other applications or databases that use tempdb to be affected in their performance.
- A query that runs very slow. The performance of existing queries may change because of various factors such as statistics have not been updated or indexes that have not been rebuilt or worse missing indexes causing table scans.
- Changes in the system disk or SAN, a change in the type of disc that is being used can affect the performance.
- When queries are optimized, often slow applications are due to blockages that are being generated due to poor design of the application or because levels has elected not suitable insulation.
Based on the previous possible causes of bad performance I will show some DMV that can be used to detect performance problems with SQL Server.
If we want to see if we have problems of CPU, we can use the DMV sys.dm_os_schedulers. This DMV gives you information about schedulers in SQL Server, usually in an environment without performance issues CPU values of this DMV tend to be zero, therefore, values that are greater than zero means that there are tasks that have to wait for be executed, if these values are too high, then we are in the presence of a problem of CPU capacity.
SELECT scheduler_id, current_tasks_count, runnable_tasks_count, current_workers_count, active_workers_count, context_switches_count, work_queue_count, pending_disk_io_count FROM sys.dm_os_schedulers WHERE scheduler_id < 255
We can interpret the values as follows:
– Runnable_tasks_count should be zero in most cases
– Current_workers_count indicates the number of workers that are associated with this scheduler.
– Work_queue_count number of tasks that are waiting to be assigned to a worker
– Pending_disk_io_count Indicates IO which is waiting to be completed.
Another component that can be checked to optimize SQL Server is the Buffer Pool, which is the component that stores and manages the data cache in SQL Server. Detailed information on the Buffer Pool can be obtained by DMV sys.dm_os_buffer_descriptors, this DMV returns information on all pages that are being used by the Buffer Pool, based on these results we can see the distribution of the Buffer Pool according to usage, it returns the pagetype which can be a data Page, Index Page or TEXT_MIX_PAGE (more information on pages types available).
If we execute the following statement
SELECT * FROM sys.dm_os_buffer_descriptors
Distribution information is obtained from the Buffer Pool. In this result, we see all pages are being used by the buffer pool, remember that each page of SQL Server is 8KB, in this case each data page has a descriptor cache buffer. Buffer descriptors uniquely represent each page that is currently being used in the cache of an SQL Server instance.
If we want a summary view of this information we can run the following T-SQL.
SELECT count (database_id) * 8/1024 AS 'Cache Used (MB)' , CASE database_id WHEN 32767 THEN database_id 'BD Resources' ELSE db_name (database_id) END AS 'DatabaseName' FROM sys.dm_os_buffer_descriptors GROUP BY db_name (database_id), database_id
This T-SQL gives us the amount of cache used by each database from the current instance of SQL Server.
If we want to see if we have problems of physical memory on the server can view information on the buffer manager.
SELECT * FROM SYS.SYSPERFINFO WHERE OBJECT_NAME = 'SQLServer: Buffer Manager'
From the information above, we can verify the values of Target Stolen Pages and Pages. The stolen Pages counter indicates the number of pages that were “stolen” from the cache to meet the demand for physical memory. In this case the number of Stolen Pages should remain stable over the course of time, if it is not, then we have a problem of the amount of available physical memory.
The SQL Server counter: Buffer Manager – pages Database, indicates the number of pages from the cache, usually this value is stable over time, if abrupt changes occur indicates that is becoming swap cache, so both could be an indication that we need to increase physical server memory.
The counter SQL Server: Buffer Manager – Buffer cache hit ratio indicates the percentage of pages that have been found in memory, therefore, the higher the better value.
The SQL Server counter: Page life expectancy represents the average number of seconds that a machine remains in the cache, for OLTP systems one Page life expectancy, average of 300 is five minutes, a lower value that may represent a memory problem.
Since SQL Server version 2008 it includes system_health which is a XEvent that helps us make troubleshooting performance SQL Server engine. If we run the following command:
SELECT * FROM sys.dm_xe_sessions
If we want to see the results of this XEvent run the following T-SQL
SELECT CAST(xe_targets.target_data as xml) FROM sys.dm_xe_session_targets xe_targets JOIN sys.dm_xe_sessions xe_sessions ON (xe_sessions.address = xe_targets.event_session_address) WHERE xe_sessions.name = 'system_health'
This T-SQL will give us the results in XML format with the corresponding information.
If we open that XML will see something like the following
If the SQL Server engine is having problems, then we can review the XML that contains information about the memory, latches, waits, and more detailed information about SQL Server. You can always reach out to me via Twitter here.
Reference: Pinal Dave (https://blog.sqlauthority.com)