February 16, 2014 at 7:17 am
I am trying to search for fragmented indexes greater than 10 percent from all databases with the exception of the system tables using SQL Server 2012.
I tried creating the following script as a sproc in the master folder but when I exec I get no results. I also tried creating a sproc with the same script in one of the user’s database folder, I get results from other database tables but it is not accurate. Where do I put the sproc to get accurate information from all the databases table indexes and do there need to be modification to the script? I am looking for something simple which can be easily modified later.
I considered using MSforeachDB but hear it may skip some database.
DECLARE @Database_id VARCHAR(255)
DECLARE @name VARCHAR(255)
DECLARE @cmd NVARCHAR(500)
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 @fragPercent float;
DECLARE @command nvarchar(4000);
DECLARE @dbid smallint;
DECLARE DatabaseCursor CURSOR FOR SELECT Database_id , name FROM sys.databases
WHERE database_id > 4 -- exclude system databases
ORDER BY 1
OPEN DatabaseCursor
WHILE (1=1)
BEGIN
FETCH NEXT FROM DatabaseCursor INTO @Database_id , @name
IF @@FETCH_STATUS < 0 BREAK;
Print N'Database ' + @name
set @dbid = db_id(@name);
select @name as Name,
OBJECT_SCHEMA_NAME(I.OBJECT_ID) AS SchemaName
,OBJECT_NAME(I.OBJECT_ID) AS ObjectName ,I.NAME AS IndexName,
avg_fragmentation_in_percent AS FragPercent, partition_number AS partitionNum
INTO #work_to_do
from sys.dm_db_index_physical_stats (5, null,null,null,'Limited') S
Join sys.indexes I on
s.OBJECT_ID = I.OBJECT_ID AND I.index_id = s.index_id
WHERE avg_fragmentation_in_percent > 10.0 -- Allow limited fragmentation
AND S.index_id > 0 -- Ignore heaps
AND page_count > 25; -- Ignore small tables
-- Declare the cursor for the list of partitions to be processed.
DECLARE partitions CURSOR FOR SELECT SchemaName, ObjectName, IndexName, fragPercent, partitionNum FROM #work_to_do;
-- Open the cursor.
OPEN partitions;
-- Loop through the partitions.
WHILE (1=1)
BEGIN
FETCH NEXT
FROM partitions
INTO @SchemaName, @ObjectName, @IndexName, @fragPercent, @PartitionNum;
IF @@FETCH_STATUS < 0 BREAK;
-- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding.
IF @fragPercent < 30.0
Begin
SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE'
Print '@command '+ @command;
ENd
IF @fragPercent >= 30.0
Begin
SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD'
Print '@command '+ @command;
end
IF @partitioncount > 1
Begin
SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10))
Print '@command '+ @command;
end
--EXEC (@command);
--PRINT N'Executed: ' + @command;
END --end loop for indexes
---- Close and deallocate the cursor.
CLOSE partitions;
DEALLOCATE partitions;
-- Drop the temporary table.
DROP TABLE #work_to_do;
END -- end loop for databases
CLOSE DatabaseCursor
DEALLOCATE DatabaseCursor
February 16, 2014 at 1:55 pm
you are joining to sys.indexes which will cause the results to only be returned for the current execution database.
You will need to run the same thing against each database. It doesn't really matter where you place the proc.
Yes, msforeachdb can miss databases. You can create a cursor to loop through each database and execute a dynamic sql string.
I am curious to know why you are ignoring heaps.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 16, 2014 at 2:47 pm
I am trying to defrag indexes on user databases. I don't believe heap tables have indexes.
February 16, 2014 at 4:17 pm
I believe that a part of your problem is that you've hardcoded the database ID in your code at the following line.
from sys.dm_db_index_physical_stats ([font="Arial Black"]5[/font], null,null,null,'Limited') S
--Jeff Moden
Change is inevitable... Change for the better is not.
February 16, 2014 at 6:42 pm
vanessa4biz (2/16/2014)
I am trying to defrag indexes on user databases. I don't believe heap tables have indexes.
Heap tables can have indexes. Heaps can become fragmented too and should be given some TLC too.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 16, 2014 at 6:43 pm
Jeff Moden (2/16/2014)
I believe that a part of your problem is that you've hardcoded the database ID in your code at the following line.
from sys.dm_db_index_physical_stats ([font="Arial Black"]5[/font], null,null,null,'Limited') S
Yeah, that would be a huge contributor too:w00t:
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 16, 2014 at 7:45 pm
sorry about that I was testing the query earlier and I forgot to change the 5 to @dbid
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply