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. [cc lang=”sql”] DECLARE @NonClusteredSeekPct float DECLARE @ClusteredLookupFromNCPct float — Define percentage of usage the non clustered should — receive over the clustered index SET @NonClusteredSeekPct = 1.50 — 150% — Define the percentage of all lookups on the clustered index — should be executed by this non clustered index SET @ClusteredLookupFromNCPct = .75 — 75% SELECT TableName = object_name(idx.object_id) ,NonUsefulClusteredIndex = idx.NAME ,ShouldBeClustered = nc.NonClusteredName ,Clustered_User_Seeks = c.user_seeks ,NonClustered_User_Seeks = nc.user_seeks ,Clustered_User_Lookups = c.user_lookups ,DatabaseName = db_name(c.database_id) FROM sys.indexes idx LEFT JOIN sys.dm_db_index_usage_stats c ON idx.object_id = c.object_id AND idx.index_id = c.index_id –AND c.database_id = @DBID JOIN ( SELECT idx.object_id ,nonclusteredname = idx.NAME ,ius.user_seeks FROM sys.indexes idx JOIN sys.dm_db_index_usage_stats ius ON idx.object_id = ius.object_id AND idx.index_id = ius.index_id WHERE idx.type_desc = ‘nonclustered’ AND ius.user_seeks = ( SELECT MAX(user_seeks) FROM sys.dm_db_index_usage_stats WHERE object_id = ius.object_id AND type_desc = ‘nonclustered’ ) GROUP BY idx.object_id ,idx.NAME ,ius.user_seeks ) nc ON nc.object_id = idx.object_id WHERE idx.type_desc IN (‘clustered’,’heap’) — non clustered user seeks outweigh clustered by 150% AND nc.user_seeks > (c.user_seeks * @NonClusteredSeekPct) — nc index usage is primary cause of clustered lookups 80% AND nc.user_seeks >= (c.user_lookups * @ClusteredLookupFromNCPct) ORDER BY nc.user_seeks DESC [/cc] The way it performs this determination is […]

Continue reading ...

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.

Continue reading ...

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. [cc lang=”sql”] 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 [/cc]

Continue reading ...

Find Procedures using Most Memory DMV

The following query displays the top 10 procedures that use the most memory and disk IO on your system. The query further breaks down the logical vs physical reads. Physical reads are the ones that actually touch the disk, logical reads are the ones that read from memory. The ones that read from disk more often will be the ones that are most likely executed less. Fixing the following queries will most likely alleviate your server’s resources quite a bit. [cc lang=”sql”] SELECT TOP 10 ObjectName = OBJECT_SCHEMA_NAME(qt.objectid,dbid) + ‘.’ + OBJECT_NAME(qt.objectid, qt.dbid) ,DiskReads = qs.total_physical_reads — The worst reads, disk reads ,MemoryReads = qs.total_logical_reads –Logical Reads are memory reads ,ReadsPerExecution = NULLIF(qs.total_physical_reads + qs.total_logical_reads,0) / qs.execution_count ,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 [/cc]

Continue reading ...

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, this custom sp_who3 only shows sessions that have a current executing request. What is also shown is the reads and writes for the current command, along with the number of reads and writes for the entire SPID. It also shows the protocol being used (TCP, NamedPipes, or Shared Memory). The lead blocker below will show in the BlkBy column as -1. [cc lang=”sql”] USE [master] GO CREATE PROCEDURE [dbo].[sp_who3] AS BEGIN SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; SELECT SPID = er.session_id ,BlkBy = CASE WHEN lead_blocker = 1 THEN -1 ELSE er.blocking_session_id END ,ElapsedMS = er.total_elapsed_time ,CPU = er.cpu_time ,IOReads = er.logical_reads + er.reads ,IOWrites = er.writes ,Executions = ec.execution_count ,CommandType = er.command ,LastWaitType = er.last_wait_type ,ObjectName = OBJECT_SCHEMA_NAME(qt.objectid,dbid) + ‘.’ + OBJECT_NAME(qt.objectid, qt.dbid) ,SQLStatement = SUBSTRING ( qt.text, er.statement_start_offset/2, CASE WHEN ( CASE WHEN er.statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(MAX), qt.text)) * 2 ELSE er.statement_end_offset END – er.statement_start_offset / 2 ) < 0 THEN 0 ELSE CASE WHEN er.statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(MAX), qt.text)) * 2 ELSE er.statement_end_offset END - er.statement_start_offset / 2 END ) ,STATUS = ses.STATUS ,[Login] = ses.login_name ,Host = ses.host_name ,DBName = DB_Name(er.database_id) ,StartTime = er.start_time ,Protocol = con.net_transport ,transaction_isolation = CASE ses.transaction_isolation_level WHEN 0 THEN 'Unspecified' WHEN 1 THEN 'Read […]

Continue reading ...

Featured Articles

 Site Author

  • Thanks for visiting!