To sort in tempdb during an index rebuild, add WITH (SORT_IN_TEMPDB = ON) to ALTER INDEX. This post tests the option and shows that the user database log stays the same size, because only the sort workspace moves. It also explains when the option helps, how much tempdb space it needs, and when to leave it off.
How to List All the Nonclustered Indexes of Your Table? – Interview Question of the Week #155
How do you list all the clustered and nonclustered indexes of your table, including columnstore? Here is the script I use in my performance health checks.
How to List All ColumnStore Indexes with Table Name in SQL Server? – Interview Question of the Week #153
Question: How to List All ColumnStore Indexes with Table Name in SQL Server? As ColumnStore Indexes are getting more and more popular.
CPerIndexMetaQS::ErrorAbort: Too Many Dumps From Index Corruption
CPerIndexMetaQS::ErrorAbort in a stack dump points to index corruption in one of your databases. A reader had too many dumps, and the ERRORLOG and DBCC CHECKTABLE showed which index was bad. Here are the two repair options and why backups matter.
What are Forwarded Records in SQL Server? – Interview Question of the Week #145
What are forwarded records in SQL Server? It is an old concept, yet few new DBAs know it. Here is a short answer about heap rows that moved to a new page.
Querying a Histogram With sys.dm_db_stats_histogram Instead of DBCC
Query sys.dm_db_stats_histogram to inspect statistics steps, compare stored estimates with data, and identify values beyond the last step.
SQL SERVER – How to Abort Index Rebuilding After Specific Time?
An index rebuilding limit can control lock waiting or a resumable execution interval. I decide which behavior the maintenance window needs.