Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Finding fragmentation in all databases. Expand / Collapse
Author
Message
Posted Sunday, February 16, 2014 7:17 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 10:03 AM
Points: 9, Visits: 159
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
Post #1541902
Posted Sunday, February 16, 2014 1:55 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 7:51 PM
Points: 17,948, Visits: 15,947
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
Post #1541931
Posted Sunday, February 16, 2014 2:47 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 10:03 AM
Points: 9, Visits: 159
I am trying to defrag indexes on user databases. I don't believe heap tables have indexes.
Post #1541935
Posted Sunday, February 16, 2014 4:17 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 10:23 PM
Points: 35,549, Visits: 32,147
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 (5, null,null,null,'Limited') S




--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1541941
Posted Sunday, February 16, 2014 6:42 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 7:51 PM
Points: 17,948, Visits: 15,947
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
Post #1541956
Posted Sunday, February 16, 2014 6:43 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 7:51 PM
Points: 17,948, Visits: 15,947
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 (5, null,null,null,'Limited') S




Yeah, that would be a huge contributor too




Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Post #1541957
Posted Sunday, February 16, 2014 7:45 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 10:03 AM
Points: 9, Visits: 159
sorry about that I was testing the query earlier and I forgot to change the 5 to @dbid
Post #1541973
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse