I will continue to explain how to Troubleshoot Performance problems of SQL Server in this Article Series.
SQL Server Performance Troubleshooting
Read previous article before this.
DMV ( Dynamic Management View )
With DMV ( Dynamic Management Views ) and DMF ( Dynamic Management Functions) introduced in SQL Server 2005, you can monitor all activities occurred in database, performance status, and display System information for managing and monitoring databases.
All databases (Oracle, SQL Server, MySQL, Sybase, PostreSQL, DB2, etc.) collect Some internal information ( Session,Process etc. activities and their statistics ) about the system and provides these data as System tables and Views.
In order to obtain information about the OS and Database, following informations are generally available in the System tables that are updated periodically by SQL Server.
- General Information about Database and Database Server (Name, Edition, Version,Character Set etc.)
- Active Queries
- CPU, I / O and Memory usage of Active Queries
- Execution Plan
- Index used by a Running Query
- I/O wait in Memory, Disk and Network
- Missing Indexes that adversely affect the performance of the Database
- Usage rates of indexes (Scan, Seek values)
- Response time of disks
DMF ( Dynamic Management Function )
Although DMV and DMF seem like the same in SQL Server, the difference between them can be directly queried as the name of DMV, whereas DMF are functions that take a parameter and return a table accordingly.
Thanks to DMV and DMFs, you can monitor critical databases running as Production, identify problems immediately and take action accordingly. With these queries, you can query most CPU, I / O and Memory Consuming statements from active running queries in the Database, find the missing index tables and find slow running queries.
All DMV and DMF available in SQL Server can be queried with the following query.
select name,type_desc from sys.system_objects where name like 'dm%' order by name;
These DMVs and DMFs used in SQL Server provide information to users according to Categories like Session, Database, OS (Operating System), Transaction, etc. For example; DMV and DMF at Database category start with sys.dm_db or DMV and DMF related to Transactions start with sys.dm_tran.
The list of DMV and DMF by categories is as follows.
Most common used DMV and DMF are like following.
Execution( SQL, Query,Process etc ): Active running queries’ DMV and DMF which provide information such as which user is called from which machine, processes, estimated end times and Query statistics.
The most important ones are as follows.
- sys.dm_exec_query_stats (DMV)
- sys.dm_exec_requests (DMV)
- sys.dm_exec_sessions (DMV)
- sys.dm_exec_connections (DMV)
- sys.dm_exec_query_plan (DMF)
- sys.dm_exec_sql_text (DMF)
- sys.dm_exec_cached_plans (DMV)
- sys.dm_exec_cursors (DMF)
Database : It is DMV and DMF which gives necessary information about Index, Mirroring and partitioning at database level. The most important ones are as follows.
- sys.dm_db_missing_index_details ( DMV)
- sys.dm_db_missing_index_columns ( DMF )
- sys.dm_db_missing_index_groups (DMV)
- sys.dm_db_missing_index_group_stats (DMV)
- sys.dm_db_index_usage_stats (DMV)
- sys.dm_db_index_physical_stats (DMF)
- sys.dm_db_index_operational_stats (DMF)
- dm_db_partition_stats (DMV)
- sys.dm_db_mirroring_connections (DMV)
- sys.dm_os_sys_info (DMV)
- sys.dm_os_waiting_tasks (DMV)
- sys.dm_os_cluster_nodes (DMF)
- sys.dm_tran_active_transactions (DMV)
- sys.dm_tran_session_transactions (DMV)
- sys.dm_tran_database_transactions (DMV)
- sys.dm_tran_current_transaction (DMV)
select top 10 case when sql_handle IS NULL then '' else ( substring(st.text,(qs.statement_start_offset+2)/2, ( case when qs.statement_end_offset = -1 then len(convert(nvarchar(MAX),st.text))*2 else qs.statement_end_offset end - qs.statement_start_offset) /2 ) ) end as query_text , qp.query_plan , (total_worker_time+0.0)/1000 as total_worker_time , (total_worker_time+0.0)/(execution_count*1000) as [AvgCPUTime] , total_logical_reads as [LogicalReads] , total_logical_writes as [logicalWrites] , execution_count , creation_time , last_execution_time , total_logical_reads+total_logical_writes as [AggIO] , (total_logical_reads+total_logical_writes)/(execution_count + 0.0) as [AvgIO] , db_name(st.dbid) as database_name , st.objectid as object_id from sys.dm_exec_query_stats qs cross apply sys.dm_exec_sql_text(sql_handle) st CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp where total_worker_time > 0 order by total_worker_time desc;
When I run the above query in the AdventureWorks test database that I used for my tests, output will be as follows.
I will share the script ( Include OS and Database DMV and DMF ) that gives general information about the OS that SQL Server database instance is runing, such as Total CPU, Memory, Total Disk Size, Database Size and Transaction Log Size.
This script is especially needed when preparing your database inventories.
sp_configure 'show advanced options', 1; GO RECONFIGURE; GO sp_configure 'Ole Automation Procedures', 1; GO RECONFIGURE; GO /*******************************************************/ SET NOCOUNT ON DECLARE @hr int DECLARE @fso int DECLARE @drive char(1) DECLARE @odrive int DECLARE @TotalSize varchar(20) DECLARE @MB Numeric ; SET @MB = 1048576 CREATE TABLE #drives (drive char(1) PRIMARY KEY, FreeSpace int NULL, TotalSize int NULL) INSERT #drives(drive,FreeSpace) EXEC master.dbo.xp_fixeddrives EXEC @hr=sp_OACreate 'Scripting.FileSystemObject',@fso OUT IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso DECLARE dcur CURSOR LOCAL FAST_FORWARD FOR SELECT drive from #drives ORDER by drive OPEN dcur FETCH NEXT FROM dcur INTO @drive WHILE @@FETCH_STATUS=0 BEGIN EXEC @hr = sp_OAMethod @fso,'GetDrive', @odrive OUT, @drive IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso EXEC @hr = sp_OAGetProperty @odrive,'TotalSize', @TotalSize OUT IF @hr <> 0 EXEC sp_OAGetErrorInfo @odrive UPDATE #drives SET [email protected]/@MB WHERE [email protected] FETCH NEXT FROM dcur INTO @drive End Close dcur DEALLOCATE dcur EXEC @hr=sp_OADestroy @fso IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso --SELECT @@Servername --SELECT --drive, TotalSize as 'Total(MB)', FreeSpace as 'Free(MB)' FROM #drives --ORDER BY drive
CREATE TABLE #CPUInfo ( Logical_CPU_Count bigint, Hyperthread_Ratio bigint, Physical_CPU_Count bigint, Physical_Memory_MB bigint )
INSERT INTO #CPUInfo( Logical_CPU_Count, Hyperthread_Ratio, Physical_CPU_Count, Physical_Memory_MB ) SELECT cpu_count AS [Logical_CPU_Count] ,hyperthread_ratio AS [Hyperthread_Ratio] ,cpu_count/hyperthread_ratio AS [Physical_CPU_Count] , physical_memory_kb/1024 AS [Physical_Memory_MB] FROM sys.dm_os_sys_info
CREATE TABLE #DatabaseInfo ( Machine_Name varchar(50), Instance_Name varchar(50), Sql_Server_Name varchar(50), Total_Database_log_size_MB bigint, Total_Database_log_used_MB bigint, Total_Database_Data_File_Size_MB bigint ) INSERT INTO #DatabaseInfo ( Machine_Name, Instance_Name, Sql_Server_Name, Total_Database_log_size_MB, Total_Database_log_used_MB, Total_Database_Data_File_Size_MB ) select convert(varchar(50),serverproperty('MachineName')) 'Machine_Name' ,convert(varchar(50),isnull(serverproperty('InstanceName'),'mssqlserver')) 'Instance_Name' ,convert(varchar(50),@@SERVERNAME) 'Sql_Server_Name' ,sum(ls.cntr_value/1024) as [Total_Database_log_size_MB] ,sum(lu.cntr_value/1024)as [Total_Database_log_used_MB] ,sum(ds.cntr_value/1024) as [Total_Database_Data_File_Size_MB] from sys.databases d left outer join sys.dm_os_performance_counters as lu on lu.instance_name=d.name and lu.counter_name like N'Log File(s) Used Size (KB)%' left outer join sys.dm_os_performance_counters as ls on ls.instance_name=d.name and ls.counter_name like N'Log File(s) Size (KB)%' and ls.cntr_value > 0 left outer join sys.dm_os_performance_counters as lp on lp.instance_name=d.name and lp.counter_name like N'Percent Log Used%' left outer join sys.dm_os_performance_counters as ds on ds.instance_name=d.name and ds.counter_name like N'Data File(s) Size (KB)%' where d.database_id>4; -- sistem database ler haric WITH SizeDisc AS ( -- sunucu üzerindeki tüm drive size ve free size bilgisi SELECT SUM(TotalSize) as 'Total_Disc_Sizeon_Server_MB', SUM(FreeSpace) as 'Total_Free_Disc_SizeOn_Server_MB' FROM #drives ) SELECT * FROM #DatabaseInfo,#CPUInfo,SizeDisc DROP TABLE #Drives DROP TABLE #DatabaseInfo DROP TABLE #CPUInfo GO /*******************************************************/ /* Disabling Ole Automation Procedures */ sp_configure 'show advanced options', 1; GO RECONFIGURE; GO sp_configure 'Ole Automation Procedures', 0; GO RECONFIGURE; /*******************************************************/ GO
The output of the script is as follows.
I will continue to explain SQL Server Performance Troubleshooting in the next article.
Do you want to learn Microsoft SQL Server DBA Tutorials for Beginners, then read the following articles.
1,996 views last month, 4 views today