Unanswered: Reindexing script making my applications hang
I am using the following reindexing script to reindex my database
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE PROCEDURE Re_Index_Table
@Db_Name as varchar(100),
@Table_Name as varchar(100),
@Index_Name as varchar(100),
@Option_Name as varchar(100)
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
DECLARE @sSQL as Varchar(1000)
SET NOCOUNT ON;
SET @sSQL = 'ALTER INDEX ' + @Index_Name + ' ON ' + @Db_Name + '.dbo.' + @Table_Name + ' ' + @Option_Name
DECLARE Index_List CURSOR FORWARD_ONLY
OBJECT_NAME(B.OBJECT_ID) as TableName, b.name as Index_Name,
WHEN ps.avg_fragmentation_in_percent > 30 THEN 'REBUILD WITH (FILLFACTOR =80,ONLINE=ON,SORT_IN_TEMPDB = ON)' --FOR ONLINE ADD USE THIS 'REBUILD WITH(ONLINE = ON)'
END AS INDEX_OPTION,
ps.Page_count as TotalPage
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS ps
INNER JOIN sys.indexes AS b ON ps.OBJECT_ID = b.OBJECT_ID
AND ps.index_id = b.index_id
WHERE ps.database_id = DB_ID() AND b.name IS NOT NULL AND ps.avg_fragmentation_in_percent > 10
ORDER BY TotalPage desc
Use SQL BI Development Tool and then create SSIS package then go to Tool and create REBUILD INDEXES task and then double click on it then set the connection and database then click on view script then just copy this whole script and then rebuild indexes at a time as you want,check attachement for steps
"FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS ps"
..****ns an analysis against every single index in every table in your database. On a large database it will churn for quite a while and eat up a massive amount of resources. The underlying code for that system function is not efficient. You should provide specific table names to the function, and only run it against tables which are known to be performance bottlenecks.
If it's not practically useful, then it's practically useless.