To check the fragmentation of all indexes on the Database

SELECT OBJECT_NAME(ind.OBJECT_ID) AS TableName, ind.name AS IndexName, indexstats.index_type_desc AS IndexType, indexstats.avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats INNER JOIN sys.indexes ind ON ind.object_id = indexstats.object_id AND ind.index_id = indexstats.index_id WHERE indexstats.avg_fragmentation_in_percent > 30 ORDER BY indexstats.avg_fragmentation_in_percent DESC

Live Insert Counts Without Disturbing Running Transaction

There are times long running insert transactions are annoying and we don’t get direct details of running transaction like how many rows inserted and how many are pending. Following query gets estimated rows inserted for the specific table mentioned. SELECT     TableName = t.NAME,    TableSchema = s.Name,    RowCounts = p.rowsFROM     sys.tables tINNER JOIN     sys.schemas s ON t.schema_id = s.schema_idINNER JOIN          sys.indexes i ON t.OBJECT_ID = i.object_idINNER JOIN     sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_idWHERE     t.is_ms_shipped = 0 and t.name=’Table Name’… Read More