I recently had a need to monitor inserts against a particular table in order to determine what kind of locks they were acquiring. Being that we could not run traces on the system, I had to resort to a roll-your-own approach to monitoring the table. The goal was to determine what kind of locking was [...]
Archive of posts filed under the DMV Queries category.
Find Resource Usage by Application
Often times when troubleshooting, it is helpful to know how much resources a particular application is requesting from your SQL Server. The easiest way to get this information is by using the connections and sessions DMV. These DMV’s when combined are powerful. Here we will query the aggregate connection information. The values below represent an [...]
Find Most Executed Stored Procedures
An important step in optimizing a system is to take a holistic approach and look at stored procedures that are called very often. These procedures can often be the backbone of a system. Sometimes optimizing these procedures can have a dramatic effect on relieving the physical bottlenecks of the system and improving end-user experience. The [...]
Find Best Clustered Index
The following query will compare the nonclustered indexes vs the clustered index and determine which index would qualify as the best clustered index based upon the DMV statistics. If a clustered index does not exist on the table, it will also suggest one of the existing nonclustered indexes as the clustered index. DECLARE @NonClusteredSeekPct float [...]
Find All Queries Run Against a Table
Dynamic management view to find all queries that have been run against a table since the last reboot.
Find Number of Pages Each Database has in BufferPool
This query shows how many pages each database has in the buffer Pool. This will show you the breakdown of memory allocation for each database. SELECT DB_NAME(database_id), COUNT(page_id)as number_pages FROM sys.dm_os_buffer_descriptors WHERE database_id !=32767 GROUP BY database_id ORDER BY database_id Popular search terms:sql get number of pageshow to find how many pages in databases sql [...]
Find Queries with Highest Reads (Highest IO)
SELECT TOP 10 ‘Procedure’ = qt.text ,DiskReads = qs.total_physical_reads — The worst reads, disk reads ,MemoryReads = qs.total_logical_reads –Logical Reads are memory reads ,Executions = qs.execution_count ,CPUTime = qs.total_worker_time ,DiskWaitAndCPUTime = qs.total_elapsed_time ,MemoryWrites = qs.max_logical_writes ,DateCached = qs.creation_time ,DatabaseName = DB_Name(qt.dbid) ,LastExecutionTime = qs.last_execution_time FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt ORDER BY [...]
A Better sp_who2 using DMVs (sp_who3)
The following code generates the same information found in sp_who2, along with some additional troubleshooting information. It also contains the SQL Statement being run, so instead of having to execute a separate DBCC INPUTBUFFER, the statement being executed is shown in the results. Unlike sp_who2, sp_who3 only shows sessions that have a current executing request. [...]
Find Queries Performing Most Writes (Inserts)
SELECT TOP 10 ‘Procedure’ = qt.text ,DiskReads = qs.total_physical_reads — The worst reads, disk reads ,MemoryReads = qs.total_logical_reads –Logical Reads are memory reads ,Executions = qs.execution_count ,CPUTime = qs.total_worker_time ,DiskWaitAndCPUTime = qs.total_elapsed_time ,MemoryWrites = qs.max_logical_writes ,DateCached = qs.creation_time ,DatabaseName = DB_Name(qt.dbid) ,LastExecutionTime = qs.last_execution_time FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt ORDER BY [...]
Find Memory Usage of Executing Procedures
To get the query memory usage of currently executing queries run the following: SELECT TEXT ,query_plan ,requested_memory_kb ,granted_memory_kb ,used_memory_kb FROM sys.dm_exec_query_memory_grants emg CROSS APPLY sys.dm_exec_sql_text(sql_handle) CROSS APPLY sys.dm_exec_query_plan(emg.plan_handle) ORDER BY emg.requested_memory_kb DESC Popular search terms:sql server 2008 memory usage dmvsql server 2005 dmv memory usagesql query memory usage sql serverdmv memory usagesql server memory usage [...]
