May 16, 2011 at 8:49 am
Where would I put a not in cause in this query?
SET NOCOUNT ON;
DECLARE @objectid int;
DECLARE @indexid int;
DECLARE @partitioncount bigint;
DECLARE @schemaname nvarchar(130);
DECLARE @objectname nvarchar(130);
DECLARE @indexname nvarchar(130);
DECLARE @partitionnum bigint;
DECLARE @partitions bigint;
DECLARE @frag float;
DECLARE @command nvarchar(4000);
-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function
-- and convert object and index IDs to names.
SELECT
object_id AS objectid,
index_id AS indexid,
partition_number AS partitionnum,
avg_fragmentation_in_percent AS frag
INTO #work_to_do
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')
WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;
-- Declare the cursor for the list of partitions to be processed.
DECLARE partitions CURSOR FOR SELECT * FROM #work_to_do;
-- Open the cursor.
OPEN partitions;
-- Loop through the partitions.
WHILE (1=1)
BEGIN;
FETCH NEXT
FROM partitions
INTO @objectid, @indexid, @partitionnum, @frag;
IF @@FETCH_STATUS < 0 BREAK;
SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)
FROM sys.objects AS o
JOIN sys.schemas as s ON s.schema_id = o.schema_id
WHERE o.object_id = @objectid;
SELECT @indexname = QUOTENAME(name)
FROM sys.indexes
WHERE object_id = @objectid AND index_id = @indexid;
SELECT @partitioncount = count (*)
FROM sys.partitions
WHERE object_id = @objectid AND index_id = @indexid;
-- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding.
IF @frag < 30.0
SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';
IF @frag >= 30.0
SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD';
IF @partitioncount > 1
SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10));
EXEC (@command);
PRINT N'Executed: ' + @command;
END;
-- Close and deallocate the cursor.
CLOSE partitions;
DEALLOCATE partitions;
-- Drop the temporary table.
DROP TABLE #work_to_do;
GO
MCSE SQL Server 2012\2014\2016
May 16, 2011 at 8:52 am
http://sqlfool.com/2010/04/index-defrag-script-v4-0/
Why roll your own when someone's done it for you?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 16, 2011 at 9:01 am
The DB is crazy. In orde to get the index to rebuild we are going to have to exclude about 10 and place them into another job. The other job seems to work but fails because the 10 indexes are included. It sounds crazy but it's the only way we can get it to work
MCSE SQL Server 2012\2014\2016
May 16, 2011 at 9:04 am
SQL New New (5/16/2011)
The DB is crazy. In orde to get the index to rebuild we are going to have to exclude about 10 and place them into another job. The other job seems to work but fails because the 10 indexes are included. It sounds crazy but it's the only way we can get it to work
There's an exclusion list integrated in the script Gail posted.
May 16, 2011 at 9:46 am
Can anybody cut the exclude and paste it in here. That script is a little overwhelming to me.
MCSE SQL Server 2012\2014\2016
May 16, 2011 at 9:53 am
Errr, right up close to the top of the script....
CREATE TABLE dbo.dba_indexDefragExclusion
(
databaseID INT Not Null
, databaseName NVARCHAR(128) Not Null
, objectID INT Not Null
, objectName NVARCHAR(128) Not Null
, indexID INT Not Null
, indexName NVARCHAR(128) Not Null
, exclusionMask INT Not Null
/* 1=Sunday, 2=Monday, 4=Tuesday, 8=Wednesday, 16=Thursday, 32=Friday, 64=Saturday */
CONSTRAINT PK_indexDefragExclusion_v40
PRIMARY KEY CLUSTERED (databaseID, objectID, indexID)
);
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 16, 2011 at 9:59 am
Thank you Gail, I have perople breathing down my neck, really overwhelmed at the moment...Thank you!:-D
MCSE SQL Server 2012\2014\2016
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply