Step 1
Because of the inserts, updates and deletes, the data and the index pages can get fragmented (just as your PC). The follwoing script can help you defrag your database:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 |
--Re-indexes the specified database CREATE PROCEDURE usp_DefragDatabase -- We don't use sysname because it might not be long enough. -- sysname is 128 chars, so we use double that. @dbname nvarchar(256) AS BEGIN -- Quote the database name with brackets DECLARE @quoteddbname nvarchar(256) SET @quoteddbname = quotename( @dbname ) -- The outer EXEC is so we can do USE, not allowed in stored procs -- The inner EXEC does the actual reindex on each table in the -- specified database EXEC(' USE '+ @quoteddbname +' DECLARE @sTableName sysname DECLARE PKMS_Tables CURSOR LOCAL FOR select table_name from information_schema.tables where table_type = ''base table'' order by 1 OPEN PKMS_Tables FETCH NEXT FROM PKMS_Tables INTO @sTableName WHILE @@FETCH_STATUS = 0 BEGIN select @sTablename = quotename(@sTablename, ''[]'') EXEC('' DBCC DBREINDEX ( ''+@sTableName+'') WITH NO_INFOMSGS'') FETCH NEXT FROM PKMS_Tables INTO @sTableName END CLOSE PKMS_Tables') END GO |
Step2
Once you have created the stored procedure (by executing the script above).
You will need to run ‘usp_DefragDatabase’ against your database in order to defrag it.
Content retrieved from: https://support.appliedi.net/kb/a291/how-do-i-defrag-my-ms-sql-database.aspx.