Downloaded 19 times
![Listing DMVs SELECT name AS DMV_Name, [type] AS DMV_Type, type_desc FROM sys.system_objects WHERE name LIKE 'dm_%' ORDER BY name SELECT count(*) TotalDMVCount FROM sys.system_objects WHERE name LIKE 'dm_%'](https://image.slidesharecdn.com/dmvssqlserver-170809181248/75/Dmv-s-Performance-Monitor-in-SQL-Server-5-2048.jpg)
Dmv's & Performance Monitor in SQL Server
- 1. DMVs And PerformanceMonitor in SQL Server By Zeba Tabassum
- 2. Agenda Definition DMVs Type DMVs Category DisadvantageOf DMVs Performance Monitor Counters
- 3. DMV’s Definition Dynamic management viewsand functions return server state information that can be used to monitor the health of a server instance, diagnose problems, and tune performance. Prior to SQL Server 2005, we had several system tables(for example sysprocesses, syslockinfo etc.) to monitor the state of the system. Though these system tables are still available in SQL Server 2005 and in later versions but SQL Server 2005 introduced whole new set of Dynamic Management Views
- 4. DMV’s Type There aretwo types of dynamic management views:- • Server-scoped DMV’s They reside in master database and provide SQL Server instance wide information. To access these DMV we need to have SELECT permission on the objects and VIEW SERVER STATE permission on the server. • Database-scoped DMV’s They reside in each database and provide database wide information. To access these DMV we need to have SELECT permission on the objects and these require VIEW DATABASE STATE permission on the database.
- 5. Listing DMVs SELECT nameAS DMV_Name, [type] AS DMV_Type, type_desc FROM sys.system_objects WHERE name LIKE 'dm_%' ORDER BY name SELECT count(*) TotalDMVCount FROM sys.system_objects WHERE name LIKE 'dm_%'
- 7. DMVs The most frequentlyused DMV sections are: • Database • Execution • IO • Index • SQL operating system
- 8.
- 9. Database DMVs SELECT TOP3 object_name(object_id) AS objname, in_row_data_page_count, in_row_reserved_page_count, used_page_count, reserved_page_count, row_count FROM sys.dm_db_partition_stats GO
- 11. Execution Related DMVs •sys.dm_exec_connections • sys.dm_exec_sessions • sys.dm_exec_requests • sys.dm_exec_cached_plans • sys.dm_exec_query_plans • sys.dm_exec_sql_text • sys.dm_exec_query_stats
- 12. Execution Related DMVs SELECTsession_id, login_name, last_request_end_time, cpu_time FROM sys.dm_exec_sessions WHERE session_id >= 61 GO
- 13. Execution Related DMVs SELECTconnection_id, session_id, client_net_address, auth_scheme FROM sys.dm_exec_connections WHERE session_id >= 61
- 16. I/O Related DMV’s •sys.dm_io_virtual_file_stats • sys.dm_io_pending_io_requests • sys.dm_io_cluster_shared_drives
- 17. I/O Related DMV’s --ShowsI/O stats for (data and log files) and virtual files no. of read and write SELECT * FROM sys.dm_io_pending_io_requests GO SELECT TOP 5 db_name(database_id) DataBaseName, file_id, num_of_reads, num_of_writes FROM sys.dm_io_virtual_file_stats(NULL, NULL)
- 19. Index Related DMVs •sys.dm_db_index_physical_stats • sys.dm_db_index_usage_stats • sys.dm_db_index_operational_stats • sys.dm_db_missing_index_details • sys.dm_db_missing_index_groups • sys.dm_db_missing_index_group_stats • sys.dm_db_missing_index_columns
- 20. Index Related DMVs ---Returns system_scansCounts with different date and objects fragmentation of index operations. -- DECLARE @DBName VARCHAR(50)='testnew' DECLARE @dbid BIGINT-- Database DB Id SET @dbid=DB_ID(@DBName) SELECT @DBName AS DataBaseName, system_scans, last_system_scan FROM sys.dm_db_index_usage_stats WHERE database_id = @dbid
- 21. Index Related DMVs SELECT@DBName AS DataBaseName, object_id, avg_fragmentation_in_percent, fragment_count FROM sys.dm_db_index_physical_stats (@dbid, NULL, NULL, NULL, NULL) WHERE database_id = @dbid AND fragment_count > 0
- 24. Operating System RelatedDMVs • sys.dm_os_performance_counters • sys.dm_os_schedulers • sys.dm_os_nodes • sys.dm_os_waiting_tasks • sys.dm_os_wait_stats
- 25. Operating System RelatedDMVs ---Returning cached page count for each database SELECT COUNT(*)AS cached_pages_count, db_name(database_id) AS database_name, database_id FROM sys.dm_os_buffer_descriptors WHERE database_id NOT IN ( 32767, 4, 2, 1,3, 5 ) GROUP BY db_name(database_id), database_id ORDER BY cached_pages_count DESC; GO
- 26. Operating System RelatedDMVs --How memory is used overall on the server, and how much memory is available SELECT total_physical_memory_kb, available_physical_memory_kb, total_page_file_kb, system_memory_state_desc FROM sys.dm_os_sys_memory
- 28. Disadvantages • The biggestdisadvantage in my opinion is the data in the DMVs is reset upon SQL restart. So if you are trying to do trending type of reports, you'll need to capture this DMV data on a regular basis to a DW somewhere. DMV data is not guaranteed to be accurate at the time of retrieval. They are updated asynchronously.
- 29. Performance Monitor Performance Monitor,commonly referred to as PerfMon, is a Microsoft Windows utility that allows us to capture :- statistical information about the hardware environment, operating system, and any applications that expose properties and counters. We will learn how to use System Monitor to gather counters into counter logs, which can be used to troubleshoot system and performance issues.
- 30. There are fivemajor resource areas that can cause bottlenecks and affect server performance: physical disk, memory, process, CPU, and Network If any of these resources are overutilized, your server or application can become noticeably slow or can even crash.
- 31. Performance Counters LogicalDisk% FreeSpace This measures the percentage of free space on the selected logical disk drive. Take note if this falls below 15 percent, you risk running out of free space for the OS to store critical files. One obvious solution here is to add more disk space. PhysicalDisk% Idle Time This measures the percentage of time the disk was idle during the sample interval. If this counter falls below 20 percent, the disk system is saturated. You may consider replacing the current disk system with a faster disk system.
- 32. Disk Counters PhysicalDiskAvg. DiskSec/Read :This measures the average time, in seconds, to read data from the disk. PhysicalDiskAvg. Disk Sec/Write:This measures the average time, in seconds, it takes to write data to the disk. PhysicalDiskAvg. Disk Queue Length : This indicates how many I/O operations are waiting for the hard drive to become available. If the value here is larger than the two times the number of spindles, that means the disk itself may be the bottleneck.
- 33. Memory Counters MemoryCache Bytes:This indicates the amount of memory being used for the file system cache. There may be a disk bottleneck if this value is greater than 300MB. Memory% Committed Bytes in Use:This measures the ratio of Committed Bytes to the Commit Limit—in other words, the amount of virtual memory in use. This indicates insufficient memory if the number is greater than 80 percent. The obvious solution for this is to add more memory.
- 34. MemoryAvailable Mbytes:This measuresthe amount of physical memory, in megabytes, available for running processes. If this value is less than 5 percent of the total physical RAM, that means there is insufficient memory, and that can increase paging activity. To resolve this problem, you should simply add more memory. MemoryPages per Second :This measures the rate at which pages are read from or written to disk to resolve hard page faults. If the value is greater than 1,000, as a result of excessive paging, there may be a memory leak.
- 35. Processor Counters Processor% ProcessorTime:This 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. Processor% User Time :This 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 optimize the application that is using up the processor resources. SystemProcessor 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.
- 36. Network Counters Network InterfaceBytesTotal/Sec : This measures the rate at which bytes are sent and received over each network adapter, including framing characters. The network is saturated if you discover that more than 70 percent of the interface is consumed. For a 100-Mbps NIC, the interface consumed is 8.7MB/sec (100Mbps = 100000kbps = 12.5MB/sec* 70 percent). In a situation like this, you may want to add a faster network card or segment the network. Network InterfaceOutput Queue Length: This measures the length of the output packet queue, in packets. There is network saturation if the value is more than 2. You can address this problem by adding a faster network card or segmenting the network.
- 37. SQL Server Counters SQLServer:General Statistics – User Connections SQLServer: Memory Manager – Memory Grants Pending SQLServer: SQL Statistics – Batch Requests/sec SQLServer: SQL Statistics – Compilations/sec SQLServer: SQL Statistics – Recompilations/sec SQLServer:BufferManager - Buffer Cache hit ratio This is the percentage of requests serviced by data cache. When cache is properly used, this should be over 90%. The counter can be improved by adding more RAM. SQLServer:Databases,
- 38. SQL Server Counters SQLServer:ExecStatistics, SQLServer:General Statistics, SQLServer:Latches, SQLServer:Locks– Average Wait Time This counter shows the average time needed to acquire a lock. This value needs to be as low as possible. SQLServer:MemoryManager– Target Server Memory (KB): indicates how much memory SQL Server “wants”. SQLServer:MemoryManager— Total Server Memory (KB): Indicates how much memory SQL Server is actually using. SQLServer:PlanCache SQLServer:Access Methods – Full scans/sec: higher numbers (> 1 or 2) may mean you are not using indexes and resorting to table scans instead.