Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Finding fragmentation in all databases.


Finding fragmentation in all databases.

Author
Message
vanessa4biz
vanessa4biz
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 174
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
SQLRNNR
SQLRNNR
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21075 Visits: 18259
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

vanessa4biz
vanessa4biz
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 174
I am trying to defrag indexes on user databases. I don't believe heap tables have indexes.
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45041 Visits: 39895
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
SQLRNNR
SQLRNNR
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21075 Visits: 18259
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

SQLRNNR
SQLRNNR
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21075 Visits: 18259
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 toow00t



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


SQL RNNR

Posting Performance Based Questions - Gail Shaw

vanessa4biz
vanessa4biz
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 174
sorry about that I was testing the query earlier and I forgot to change the 5 to @dbid
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search