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 12»»

2008 Index Fragmentation Maintenance Expand / Collapse
Author
Message
Posted Saturday, November 8, 2008 1:55 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 4:14 PM
Points: 15,725, Visits: 28,130
Comments posted to this topic are about the item 2008 Index Fragmentation Maintenance

----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #599480
Posted Sunday, November 9, 2008 10:18 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Friday, September 12, 2014 7:41 AM
Points: 4,389, Visits: 9,531
This is good for a basic start. I would simplify this a little - for example, I would change the dynamic query to the following:

EXEC sp_msforeachdb 'Use ?;
INSERT INTO #Frag (
DBName,
TableName,
SchemaName,
IndexName,
AvgFragment
)
Select db_name()
,object_name(s.object_id) As ObjectName
,object_schema_name(s.object_id) As SchemaName
,s.avg_fragmentation_in_percent
From sys.dm_db_index_physical_stats(db_id(), Null, Null, Null, ''Sampled'') s
Join sys.indexes i On i.object_id = s.object_id And i.index_id = s.index_id
Where i.index_id > 0
And i.index_id < 255
And s.avg_fragmentation_in_percent > 20'

And move the ordering of the results to the cursor instead (guarantees the order will always be what I want).



Jeffrey Williams
Problems are opportunites brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs
Post #599565
Posted Monday, November 10, 2008 6:22 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 4:14 PM
Points: 15,725, Visits: 28,130
I like that clean up. Fewer joins and a simpler query. Nicely done.

Thanks for making me look at the query again. I've still got a bit of the debugging info in there that needs to get cleaned out. Sloppy of me.


----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #599810
Posted Monday, November 10, 2008 2:35 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Friday, September 12, 2014 7:41 AM
Points: 4,389, Visits: 9,531
No problem - if you look at my query, you will see that I forgot to include the index name (oops).

The modified query is:

EXEC sp_msforeachdb 'Use ?;
INSERT INTO #Frag (
DBName,
TableName,
SchemaName,
IndexName,
AvgFragment
)
Select db_name()
,object_name(s.object_id) As ObjectName
,object_schema_name(s.object_id) As SchemaName
,i.Name As IndexName
,s.avg_fragmentation_in_percent
From sys.dm_db_index_physical_stats(db_id(), Null, Null, Null, ''Sampled'') s
Join sys.indexes i On i.object_id = s.object_id And i.index_id = s.index_id
Where i.index_id > 0
And i.index_id < 255
And s.avg_fragmentation_in_percent > 20'



Jeffrey Williams
Problems are opportunites brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs
Post #600223
Posted Tuesday, December 9, 2008 7:51 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 4:14 PM
Points: 15,725, Visits: 28,130
Yes, I want direct control over what is run and how it is run within my system. Maintenance jobs are OK, but they can be problematic. I have a lot more ability to fine tune this process.

----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #616158
Posted Tuesday, December 9, 2008 7:53 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Tuesday, August 5, 2014 12:28 PM
Points: 4,665, Visits: 635
You have more control over what actually happens to the tables, indexes, etc. when you write your own maintenance jobs versus using SQL Server's Maintenance Plans. I have my own jobs for creating compressed full and t-log backups, monitoring database growth, index defrag/rebuild based on level of fragmentation, etc.

Maintenance Plans are a better alternative than no DB maintenance for a novice DBA.
Post #616162
Posted Tuesday, December 9, 2008 7:57 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 4:14 PM
Points: 15,725, Visits: 28,130
Total agreement.

----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #616172
Posted Tuesday, December 9, 2008 9:35 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, September 12, 2014 10:55 AM
Points: 1,082, Visits: 541
Good morning,

I'm running a similar script and yes, it is WAY better to write your own optimizations. How do you handle the exception when the index contains one of the following types?

Msg 2725, Level 16, State 2, Line 1
Online index operation cannot be performed for index 'TEST_PK' because the index contains column 'TESTCOL' of data type text, ntext, image, varchar(max), nvarchar(max), varbinary(max) or xml. For non-clustered index the column could be an include column of the index, for clustered index it could be any column of the table. In case of drop_existing the column could be part of new or old index. The operation must be performed offline.

The script still runs, but it's irritating to show up in the morning and see that the job has 'failed'.

Alan
Post #616273
Posted Tuesday, December 9, 2008 10:23 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 2:43 PM
Points: 772, Visits: 1,185

It will work for SQL 2005 as well if we change
Select db_name()
,object_name(s.object_id) As ObjectName
--,object_schema_name(s.object_id) As SchemaName -- 2008
,object_name(s.object_id) As SchemaName -- 2005

,i.Name As IndexName
,s.avg_fragmentation_in_percent



SQLServerNewbie

MCITP: Database Administrator SQL Server 2005
Post #616324
Posted Tuesday, December 9, 2008 10:51 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 4:14 PM
Points: 15,725, Visits: 28,130
That's great. I hadn't even tried it in 2005. Thanks for posting.

----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #616342
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse