Most common SQL server related questions I get from clients encouraged me to write this blog. If these are also your questions then you will find this blog interesting.
Problem
1. My query is running slow, how can I see information directly or indirectly related to my query execution?
2. The system is running slow, how can I see low-level system information such as memory, locking, and SQLOS (schedule)?
3. This query should complete in seconds, now it is taking minutes. How can I see details about this transaction including resource locks?
4. My queries are optimized. However, the process is still very slow. How can I see the network, I/O and disk latency?
5. I think my query is slow because of index fragmentation. How can I see database objects and details on indexes?
Solution
These questions require detail analysis and need a very organized answer according to your system. However, in this blog, I will try to give some guidelines to address these questions. In my upcoming blogs, I will address each question in detail.
Generally, the SQL Server storage engine has a complex, yet very interesting process to manage thousands of transactions instantly and records each activity in the back end. If you have a way to look at the back end system information, you will be able to answer most of the above questions. I think the question is how?
Microsoft understood the need to see system information way early in a game. As a result, 15 years a go, with the introduction of SQL Server 2005, Microsoft introduced Dynamic Management Views (DMVs) and functions which allow you to get a better insight into what is happening in SQL Server.
Since SQL server 2005, DMVs and DMFs are a great tool to help troubleshoot performance-related issues and once you understand their power they will become a staple for your Database Administration.
There are 496 SQL Server dynamic management views (DMVs). I don’t see the importance to go over each DMVs but, I will focus on the most import counters which help us to answer our questions.
1. sys.dm_exec _*
Contains information directly or indirectly related to the execution of user code and associated connections. For example, sys.dm_exec_sessions return one row per authenticated session on SQL Server.
2. sys.dm_os_*
Contains low-level system information such as memory, locking, and scheduling. For example, sys.dm_os_schedulers Is a DMV that returns one row per scheduler. It is primarily used to monitor the condition of a scheduler or to identify runaway tasks.
3. sys.dm_tran_*
Contains details about current transactions. For example, sys.dm_tran_locks return information about currently active lock resources. Each row represents a currently active request to the lock management component for a lock that has been granted or is waiting to be granted.
4. sys.dm_io_*
Keeps track of input/output activity on network and disks. For example, the function (DB_ID (N’yourdatabase’),2) return I/O statistics for data and log files.
5. sys.dm_db_*
Contains details about database and database objects such as indexes. For example, sys.dm_db_index_physical_stats Is a function that returns size and fragmentation information for the data and indexes of the specified table or view.
