One of the key tasks I have done to improve performance on SQL Server database is defragmenting indexes in a SQL database.
Indexes are very crucial for improving database performance because they make database searches efficient and faster.
Index problems
What are some of the issues indexes may have in a database?
There are some issues usually associated with indexes. Here are some of them:
Indexes with high fragmentation ratio: Indexes with fragmentation will cause a huge performance issue on the database.
Too many indexes on a table: Though indexes are desirable, too much of them may be a problem in a database. Index redundancy can occur from too many indexes on a table. Also, it may cause confusion as to which index to use for sql execution. Also, update statements will take a very long time to complete.
Hypothetical indexes: These are indexes created during a SQL tuning operation to improve performance. They may not be needed after the query is executed.
Unnecessary index: creating an index on a very small table is not recommended because it amounts to misuse of resources.
Databases identified with one or more tables, with indexes that may require update statistics: If statistics of an index is not updated, there could be a performance issue.
There are foreign keys with no supporting indexes: It is best practice to create an index to support foreign keys.
Indexes have been identified with an index key larger than the recommended size (900 bytes): Index keys should not be greater than 900 bytes.
High number of Full Scans versus Index Searches: This is a clear case of lack of index on a table.
Rebuilding an index
You can use the GUI tool to rebuild an index.
Retrieving Fragmentation info on all indexes
--Use this script to get all the information about all the tables and indexes in a database. You should use this list to prepare a collation of all indexes to defrag in a database.
You can also use this list to set a threshold of defragmentation based on the trend.
-- Declare database
use Patientdatadb
go
-- Show fragmentation per table
SELECT dbschemas.[name] as 'Schema',
dbtables.[name] as 'Table',
dbindexes.[name] as 'Index',
indexstats.avg_fragmentation_in_percent,
indexstats.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
AND indexstats.index_id = dbindexes.index_id
WHERE indexstats.database_id = DB_ID()
ORDER BY indexstats.avg_fragmentation_in_percent desc
Result should list all the indexes on all the tables in each database along with the fragmented percentage.
DeFragmenting all indexes in a database
To defragment all indexes in a database that are fragmented above a declared threshold, you can use the script below:
Please note that my threshold in my script is set to 30%
-- Declare database
use Patientdatadb
go
-- Declare variables
SET NOCOUNT ON;
DECLARE @tablename varchar(255);
DECLARE @execstr varchar(400);
DECLARE @objectid int;
DECLARE @indexid int;
DECLARE @frag decimal;
DECLARE @maxfrag decimal;
-- Declare maximum fragmentation to allow
SELECT @maxfrag = 30.0;
-- Declare a cursor
DECLARE tables CURSOR FOR
SELECT TABLE_SCHEMA + '.' + TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE';
-- Create the table
CREATE TABLE #fraglist (
ObjectName char(255),
ObjectId int,
IndexName char(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 decimal,
BestCount int,
ActualCount int,
LogicalFrag decimal,
ExtentFrag decimal);
-- Open the cursor
OPEN tables;
-- Loop through all the tables in the database
FETCH NEXT
FROM tables
INTO @tablename;
WHILE @@FETCH_STATUS = 0
BEGIN
-- Do the showcontig of all indexes of the table
INSERT INTO #fraglist
EXEC ('DBCC SHOWCONTIG (''' + @tablename + ''')
WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS');
FETCH NEXT
FROM tables
INTO @tablename;
END;
-- Close and deallocate the cursor
CLOSE tables;
DEALLOCATE tables;
-- Declare the cursor for the list of indexes to be defragged
DECLARE indexes CURSOR FOR
SELECT ObjectName, ObjectId, IndexId, LogicalFrag
FROM #fraglist
WHERE LogicalFrag >= @maxfrag
AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0;
-- Open the cursor
OPEN indexes;
-- Loop through the indexes
FETCH NEXT
FROM indexes
INTO @tablename, @objectid, @indexid, @frag;
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'Executing DBCC INDEXDEFRAG (0, ' + RTRIM(@tablename) + ',
' + RTRIM(@indexid) + ') - fragmentation currently '
+ RTRIM(CONVERT(varchar(15),@frag)) + '%';
SELECT @execstr = 'DBCC INDEXDEFRAG (0, ' + RTRIM(@objectid) + ',
' + RTRIM(@indexid) + ')';
EXEC (@execstr);
FETCH NEXT
FROM indexes
INTO @tablename, @objectid, @indexid, @frag;
END;
-- Close and deallocate the cursor
CLOSE indexes;
DEALLOCATE indexes;
-- Delete the temporary table
DROP TABLE #fraglist;
GO
