SQL SERVER Articles

SQL SERVER Articles Dear Readers, Please check below articles to get good knowledge on SQL SERVER. SNO DESCRIPTION ARTICLE LINK 1 SQL Server – Issue to view Alwayson Health Events https://www.ktexperts.com/sql-server-issue-to-view-alwayson-health-events/ 2 Script to copy one particular database role from one SQL server to another… Read More

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