November 8, 2008 at 1:55 pm
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 9, 2008 at 10:18 am
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
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
November 10, 2008 at 6:22 am
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 10, 2008 at 2:35 pm
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
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
December 9, 2008 at 7:51 am
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 9, 2008 at 7:53 am
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.
December 9, 2008 at 7:57 am
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 9, 2008 at 9:35 am
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
December 9, 2008 at 10:23 am
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
December 9, 2008 at 10:51 am
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 9, 2008 at 12:21 pm
Grumpy DBA (12/9/2008)
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.
This is interesting, because I use a combination of my own procedures/code and the maintenance plans. I do this because the maintenance plans are very good at managing the steps a process needs to do, but the plug-ins that are provided are not very good.
So, instead of using the Check Database Integrity Task - I will use the Execute SQL Task and put in that task: DBCC CHECKDB(mydb) WITH PHYSICAL_ONLY;
Which, of course is not an option that is available with the plug-in.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
December 9, 2008 at 12:23 pm
Jerry Hung (12/9/2008)
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
Jerry - object_schema_name is available in SQL Server 2005 and above. Not sure why you are repeating the object_name as the schema_name for the object in the above.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
December 9, 2008 at 12:28 pm
Alan Vogan (12/9/2008)
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
You can check the column 'lob_data_space_id' in the table sys.tables. If there is a value there, then you have LOB data in the table.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
December 9, 2008 at 1:16 pm
Jeffrey Williams (12/9/2008)
You can check the column 'lob_data_space_id' in the table sys.tables. If there is a value there, then you have LOB data in the table.
Woohoo! That's what I was lookin' for! :w00t:
I also use a combination of SQL 2005 provided maintenance tasks and custom scripts.
Thanks for the tip!
Alan
December 10, 2008 at 11:30 am
Jeffrey Williams (12/9/2008)
Jerry Hung (12/9/2008)
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
Jerry - object_schema_name is available in SQL Server 2005 and above. Not sure why you are repeating the object_name as the schema_name for the object in the above.
Interesting
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
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
On 2005 9.0.2047 (SP1), 1399 (RTM)
'object_schema_name' is not a recognized built-in function name.
2005 9.0.3042 (SP2) supports object_schema_name
Note, above code doesn't run in compatibility 8.0 mode either clearly
'object_schema_name' is not a recognized built-in function name.
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply