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.
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?
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.
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.
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.
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.
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.