Get most frequently modified indexes and statistics

 Get the most frequently modified indexes and statistics

When you're working with SQL Server, understanding your database's statistics is crucial for optimal performance. Outdated or missing statistics can lead to poor query plans and slow execution times. This SQL query is a handy tool to help you identify statistics that have gone stale and might need updating.

Find Outdated SQL Server Statistics with This Handy Query

This script helps pinpoint statistics that have a high modification counter, meaning the underlying data has changed significantly since the statistics were last updated. By focusing on these, you can prioritize which statistics to rebuild or reorganize to keep your queries running smoothly.

SELECT o.[name] AS [Object Name]
  ,o.[object_id]
  ,o.[type_desc]
  ,s.[name] AS [Statistics Name]
  ,s.stats_id
  ,s.no_recompute
  ,s.auto_created
  ,s.is_incremental
  ,s.is_temporary
  ,sp.modification_counter
  ,sp.[rows]
  ,sp.rows_sampled
  ,sp.last_updated
FROM sys.objects AS o WITH (NOLOCK)
INNER JOIN sys.stats AS s WITH (NOLOCK) ON s.object_id = o.object_id
CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) AS sp
WHERE o.[type_desc] NOT IN (N'SYSTEM_TABLE', N'INTERNAL_TABLE')
  AND sp.modification_counter > 0
ORDER BY sp.modification_counter DESC
  ,o.name
OPTION (RECOMPILE);

This query looks at all objects in your database (excluding system and internal tables) and joins them with their associated statistics. The key component here is sys.dm_db_stats_properties, which provides valuable information like the modification counter, indicating how many changes have occurred to the data since the last statistics update. We're specifically looking for those with a modification_counter greater than zero, ordered by the highest changes first. This way, you can easily spot and address the most critical statistics that need attention.

Get index fragmentation information

 Get index fragmentation information

Here's a handy SQL query that can help you identify fragmented indexes within your database. This script provides valuable insights into the fragmentation level of your indexes, allowing you to pinpoint which ones might be impacting performance. By proactively addressing index fragmentation, you can significantly improve the efficiency of your database operations.

SELECT DB_NAME(ps.database_id) AS [Database Name]
 ,SCHEMA_NAME(o.[schema_id]) AS [Schema Name]
 ,OBJECT_NAME(ps.OBJECT_ID) AS [Object Name]
 ,i.[name] AS [Index Name]
 ,ps.index_id
 ,ps.index_type_desc
 ,ps.avg_fragmentation_in_percent
 ,ps.fragment_count
 ,ps.page_count
 ,i.fill_factor
 ,i.has_filter
 ,i.filter_definition
 ,i.[allow_page_locks]
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, N'LIMITED') AS ps
INNER JOIN sys.indexes AS i WITH (NOLOCK) ON ps.[object_id] = i.[object_id]
 AND ps.index_id = i.index_id
INNER JOIN sys.objects AS o WITH (NOLOCK) ON i.[object_id] = o.[object_id]
WHERE ps.database_id = DB_ID()
 AND ps.page_count > 2500
ORDER BY ps.avg_fragmentation_in_percent DESC
OPTION (RECOMPILE);

This query specifically targets indexes within the current database (DB_ID()) and filters for those with more than 2500 pages, as smaller indexes are less likely to benefit from defragmentation. The results are ordered by the average fragmentation percentage in descending order, making it easy to see your most fragmented indexes right at the top. Understanding and managing index fragmentation is a key aspect of database performance tuning!

How to find deleted object detail in SQL server default trace

How to find the deleted object detail in SQL server default trace

This SQL query helps identify deleted objects in SQL Server using T-SQL. It reads from the default trace and presents the relevant details.

;WITH cteObjectTypes
AS (
 SELECT TSV.trace_event_id
  ,TSV.subclass_name
  ,TSV.subclass_value
 FROM sys.trace_subclass_values AS TSV
 JOIN sys.trace_columns AS TC ON TSV.trace_column_id = TC.trace_column_id
 WHERE TC.[name] = 'ObjectType'
 )
 ,cteEventSubClasses
AS (
 SELECT TSV.trace_event_id
  ,TSV.subclass_name
  ,TSV.subclass_value
 FROM sys.trace_subclass_values AS TSV
 JOIN sys.trace_columns AS TC ON TSV.trace_column_id = TC.trace_column_id
 WHERE TC.[name] = 'EventSubClass'
 )
SELECT TE.[name]
 ,I.ApplicationName
 ,I.BigintData1
 ,I.ClientProcessID
 ,I.ColumnPermissions
 ,I.DatabaseID
 ,I.DatabaseName
 ,I.DBUserName
 ,I.Duration
 ,I.EndTime
 ,I.Error
 ,I.EventSequence
 ,Convert(NVARCHAR(10), I.EventSubClass) + N'-' + ESC.subclass_name AS EventSubClass
 ,I.FileName
 ,I.HostName
 ,I.IndexID
 ,I.IntegerData
 ,I.IsSystem
 ,I.LineNumber
 ,I.LoginName
 ,I.LoginSid
 ,I.NestLevel
 ,I.NTDomainName
 ,I.NTUserName
 ,I.ObjectID
 ,I.ObjectID2
 ,I.ObjectName
 ,Convert(NVARCHAR(10), I.ObjectType) + N'-' + OT.subclass_name AS ObjectType
 ,I.OwnerName
 ,I.ParentName
 ,I.Permissions
 ,I.RequestID
 ,I.RoleName
 ,I.ServerName
 ,I.SessionLoginName
 ,I.Severity
 ,I.SPID
 ,I.StartTime
 ,I.STATE
 ,I.Success
 ,I.TargetLoginName
 ,I.TargetLoginSid
 ,I.TargetUserName
 ,I.TextData
 ,I.TransactionID
 ,I.Type
 ,I.XactSequence
FROM sys.traces T
CROSS APPLY sys.fn_trace_gettable(CASE 
   WHEN CHARINDEX('_', T.[path]) <> 0
    THEN SUBSTRING(T.PATH, 1, CHARINDEX('_', T.[path]) - 1) + '.trc'
   ELSE T.[path]
   END, T.max_files) I
JOIN sys.trace_events AS TE ON I.EventClass = TE.trace_event_id
LEFT JOIN cteEventSubClasses AS ESC ON TE.trace_event_id = ESC.trace_event_id
 AND I.EventSubClass = ESC.subclass_value
LEFT JOIN cteObjectTypes AS OT ON TE.trace_event_id = OT.trace_event_id
 AND I.ObjectType = OT.subclass_value
WHERE T.is_default = 1
 AND TE.NAME = 'Object:Deleted'