Some interview questions never seem to disappear. I hear this one from DBAs early in their career: How do you reindex every table in a database?
My older answer gave cursor scripts for several SQL Server generations. Those scripts showed the mechanics, but they also executed a rebuild on every table and imposed an 80 percent fill factor everywhere. I would pause before doing either on a real server. A rebuild uses resources, log space, and potentially a maintenance window; low page density can make reads more expensive.
If the interviewer wants the T-SQL syntax, this read-only query for SQL Server 2014 and later generates commands for disk-based tables that have indexes. It does not run them:
SELECT N'ALTER INDEX ALL ON '
+ QUOTENAME(SCHEMA_NAME(t.schema_id))
+ N'.' + QUOTENAME(t.name)
+ N' REBUILD;' AS ReviewCommand
FROM sys.tables AS t
WHERE t.is_memory_optimized = 0
AND EXISTS
(
SELECT 1
FROM sys.indexes AS i
WHERE i.object_id = t.object_id
AND i.index_id > 0
)
ORDER BY SCHEMA_NAME(t.schema_id), t.name;The QUOTENAME calls matter. A table or schema name may contain spaces or reserved words, so joining bare names into dynamic SQL is fragile. Inspect the output and choose the tables and indexes that actually need maintenance. The query is for the connected database; do not paste generated commands into an unattended job just because the list exists.
My practical answer is narrower than “rebuild everything”: look at the workload, page count, page density, and fragmentation; then decide whether a particular rowstore index warrants a rebuild, a reorganization, or no work. Do not apply a universal fill factor of 80 without evidence. Memory-optimized tables and other index types need their own rules.
So yes, I can show the ALTER INDEX syntax. In a production interview I would also explain which indexes I would leave alone. That is the part that protects the server.
Original index-maintenance examples
These earlier walkthroughs explain the historical versions. Review their operational commands for your current index types and maintenance requirements.
- SQL SERVER , Online Index Rebuilding Index Improvement in SQL Server 2012
- SQL SERVER , 2008 , 2005 , Rebuild Every Index of All Tables of Database , Rebuild Index with FillFactor
- SQL SERVER , ReIndexing Database Tables and Update Statistics on Tables
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Discover more from SQL Authority with Pinal Dave
Subscribe to get the latest posts sent to your email.
9 Comments. Leave new
You could really hurt your overall performance by resetting the fillfactor on all indexes to only 80%, particularly on large+ tables. Why not just leave the fillfactor out, leaving it unchanged, rather than forcing a single value across all indexes?
I agree Scott. The script can be modified based on business requirement. There is no one-size-fit-all script which I can make.
Hello Sir ,
Can i use dbreindex dbcc command
alter proc usp_defrag as
begin
CREATE TABLE #TMPFRAG (
ObjectName VARCHAR (255),
ObjectId INT,
IndexName VARCHAR (255),
IndexId INT,
Lvl INT,
CountPages INT,
CountRows INT,
MinRecSize INT,
MaxRecSize INT,
AvgRecSize INT,
ForRecCount INT,
Extents INT,
ExtentSwitches INT,
AvgFreeBytes INT,
AvgPageDensity INT,
ScanDensity INT,
BestCount INT,
ActualCount INT,
LogicalFrag INT,
ExtentFrag INT)
INSERT INTO #TMPFRAG
EXECUTE (‘DBCC SHOWCONTIG WITH TABLERESULTS’)
DECLARE @OBJid INT,@OBJNAME VARCHAR(30),@scanden int
declare c1 CURSOR
for select ObjectId,OBJECTNAME,SCANDENSITY FROM #TMPFRAG where ScanDensity <=50
OPEN C1
FETCH NEXT FROM C1 INTO @OBJID,@OBJNAME,@SCANDEN
WHILE(@@FETCH_STATUS=0)
BEGIN
dbcc dbreindex(@OBJNAME);
FETCH NEXT FROM C1 INTO @OBJID,@OBJNAME,@SCANDEN
END
CLOSE C1
DEALLOCATE C1
end
exec usp_defrag
Following script works fine for me:
DECLARE @sql VARCHAR(500)
DECLARE @fillfactor INT
SET @fillfactor = 80
SET @sql = ‘ALTER INDEX ALL ON ? REBUILD WITH (FILLFACTOR = ‘ + CONVERT(VARCHAR(3),@fillfactor) + ‘)’
exec sp_MSforeachtable @command1=@sql
This will not work if your database contains in-memory tables.
The sp_msforeachtable method also works.
Examples:
exec sp_msforeachtable “set quoted_identifier on alter index all on ? rebuild”
exec sp_msforeachtable “set quoted_identifier on alter index all on ? reorganize”
exec sp_msforeachtable “set quoted_identifier on alter index all on ? rebuild with ( fillfactor = 100 )”
Yes. that would also work.
I use an application that has a lot of data. The application is doing heavy calculations that on a big database can take hours sometimes. Index defragmentation has a major impact on calculations performance, so a index maintenance is periodically required. I used many index defragmentation scripts, but this one had the biggest impact on performance for my application. After running this script, my calculation time was shortened to less than half of how long usually took to complete. So many thanks for this wonder script.
You should reindex the table which had a lot of changes. If no data is changed, don’t run reindex.