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 @DBID INT
SET @DBID = [...]
Dynamic management view to find all queries that have been run against a table since the last reboot.
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
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 qs.total_physical_reads + qs.total_logical_reads DESC
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.
What [...]
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 qs.max_logical_writes DESC
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
The following DMV query retrieves the usage statistics for existing indexes.
User Seeks – A high number indicates a well utilized index.
User Scans – Number of times the index has been scanned. Could indicate improper ordering of the composite columns
User Lookups – Indicates a different index was used for criteria and the actual [...]
This query will show the indexes that are missing ordered according to those having the most impact. It will also provide the create index script needed in order to help you create the index.
SELECT
mid.statement
,migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) AS improvement_measure,OBJECT_NAME(mid.Object_id),
‘CREATE INDEX [missing_index_' + CONVERT (varchar, mig.index_group_handle) [...]
SELECT TOP 10
ObjectName = OBJECT_NAME(qt.objectid)
,DiskReads = qs.total_physical_reads — The worst reads, disk reads
,MemoryReads = qs.total_logical_reads –Logical Reads are memory reads
,Executions = qs.execution_count
,AvgDuration = qs.total_elapsed_time / 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)
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
WHERE qt.dbid = db_id() — Filter by current database
ORDER BY qs.total_elapsed_time DESC