June 15, 2010 at 2:20 pm
There are lots of good custom scripts that will rebuild just those indexes that need it. Try the one Michelle wrote - http://www.sqlfool.com/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 15, 2010 at 2:24 pm
GilaMonster (6/15/2010)
Will you please read what I'm saying...GilaMonster (6/14/2010)
For reasons that have to do with the way the first few pages are allocated, there's virtually no point in rebuilding an index that occupies less than 24 pages. It's generally recommended not to worry about fragmentation for indexes with less than 1000 pages.
See if the Example 'D' in this BOL article will work for you: http://msdn.microsoft.com/en-us/library/ms188917.aspx
The Redneck DBA
June 16, 2010 at 6:28 am
This is a great example!
Thanks for the effort. Was helpful!
September 7, 2012 at 5:08 am
This will give you a list of indexes in a database, together with their sizes/fragmentation/statistics last updated/etc:
DECLARE @DatabaseID INT
SELECT @DatabaseID = DB_ID()
SELECT [sd].[name] [DatabaseName] ,
[sd].[recovery_model_desc] ,
ss.name [SchemaName] ,
so.name [ObjectName] ,
so.[object_id] ,
si.name [IndexName] ,
si.index_id ,
si.type_desc ,
ps.page_count ,
ps.avg_fragmentation_in_percent ,
si.fill_factor ,
si.is_padded ,
STATS_DATE(si.object_id, si.index_id) AS Stats_Last_Updated
FROM [master].[sys].dm_db_index_physical_stats(@DatabaseID, NULL, NULL, NULL, NULL) ps
INNER JOIN [sys].[objects] AS so ON ps.[object_id] = so.[object_id]
INNER JOIN [sys].[indexes] AS si ON ps.[object_id] = si.[object_id]
AND ps.[index_id] = si.[index_id]
INNER JOIN [sys].[schemas] AS ss ON so.[schema_id] = ss.[schema_id]
INNER JOIN [master].[sys].[databases] AS sd ON [ps].[database_id] = [sd].[database_id]
As Gail has said, though, there's no point defragging small indexes. You may not ever achieve 0% in such indexes as they will be located in mixed extents (i.e. extents shared with other small objects).
[edit] Sorry - didn't see page 2 - thought this hadn't been answered - ignore post!
September 7, 2012 at 9:03 am
I modified this slightly and tried to run for one table. Went on running... I fonally cancelled. What can be wrong with this ?
All that I did was
1.) Removed references to MASTER database.
2.) Added a WHERE clause
That's it. Our DB has about 2000 tables. By including the
WHERE caluse I am narrowing it down to one table. But it still
keeps running forever
USE NGPROD
GO
DECLARE @table_name VARCHAR(100)
DECLARE @DatabaseID INT
SELECT @DatabaseID = DB_ID()
SELECT @table_name = 'ORDER_'
SELECT [sd].[name] [DatabaseName] ,
[sd].[recovery_model_desc] ,
ss.name [SchemaName] ,
so.name [ObjectName] ,
so.[object_id] ,
si.name [IndexName] ,
si.index_id ,
si.type_desc ,
ps.page_count ,
ps.avg_fragmentation_in_percent ,
si.fill_factor ,
si.is_padded ,
STATS_DATE(si.object_id, si.index_id) AS Stats_Last_Updated
FROM [sys].dm_db_index_physical_stats(@DatabaseID, NULL, NULL, NULL, NULL) ps
INNER JOIN [sys].[objects] AS so ON ps.[object_id] = so.[object_id]
INNER JOIN [sys].[indexes] AS si ON ps.[object_id] = si.[object_id]
AND ps.[index_id] = si.[index_id]
INNER JOIN [sys].[schemas] AS ss ON so.[schema_id] = ss.[schema_id]
INNER JOIN [sys].[databases] AS sd ON [ps].[database_id] = [sd].[database_id]
WHERE
so.name = @table_name
September 10, 2012 at 8:48 am
Out of interest, any particular reason why you removed the MASTER database references? That's where the sys.dm_db_index_physical_stats function and sys.databases view reside so specifying their location merely saves SQL Server the bother of figuring that out for itself. Won't make any significant difference however.
Filtering the resultset on so.name will make little difference too. The reason for that is in the paragraph above - where I said "...where the sys.dm_db_index_physical_stats function and...". As it is a function, it will return a dataset based on the parameters you give it. You've given it the database_id only, so it will return the statistics for the entire database. Your query will then filter that resultset, throwing away every row that doesn't correspond to table 'ORDER_'. If the indexes have not been maintained and you have a significant number of tables/indexes, that can take quite a while (obviously also affected by resources available to SQL Server, how busy it is with other requests, etc.).
What you need to do to filter the results for a single table is to provide database_id and object_id (the latter for the table in question) to the sys.dm_db_index_physical_stats function. Something along the lines of:
USE NGPROD
GO
DECLARE @ObjectID INT
DECLARE @DatabaseID INT
SELECT @DatabaseID = DB_ID()
SELECT @ObjectID = SELECT FROM [sys].[tables] WHERE [name] = 'ORDER_'
SELECT [sd].[name] [DatabaseName] ,
[sd].[recovery_model_desc] ,
ss.name [SchemaName] ,
so.name [ObjectName] ,
so.[object_id] ,
si.name [IndexName] ,
si.index_id ,
si.type_desc ,
ps.page_count ,
ps.avg_fragmentation_in_percent ,
si.fill_factor ,
si.is_padded ,
STATS_DATE(si.object_id, si.index_id) AS Stats_Last_Updated
FROM [sys].dm_db_index_physical_stats(@DatabaseID, @ObjectID, NULL, NULL, NULL) ps
INNER JOIN [sys].[objects] AS so ON ps.[object_id] = so.[object_id]
INNER JOIN [sys].[indexes] AS si ON ps.[object_id] = si.[object_id]
AND ps.[index_id] = si.[index_id]
INNER JOIN [sys].[schemas] AS ss ON so.[schema_id] = ss.[schema_id]
INNER JOIN [sys].[databases] AS sd ON [ps].[database_id] = [sd].[database_id]
September 11, 2012 at 3:22 am
You could also try looking at this http://www.mssqltips.com/sqlservertip/1791/fixing-index-fragmentation-in-sql-server-2005-and-sql-server-2008/. I use a modified version that only bothers with tables that have page_count > 8.
BTW You really need to listen to Gail. She's up there in the top 5 SQL people in the world.
September 11, 2012 at 7:15 am
Couple of notes here...
Thanks it worked. Took 20 seconds but gave the result set.
1.) I had to modify your syntax slightly ( a little syntax error )
SELECT @ObjectID = OBJECT_ID FROM [sys].[tables] WHERE [name] = 'ORDER_'
2.) Now lets look at the result set. I always thought a PRIMARY key is always a clustered index ( Right ? Wrong ? )
Look at the result set and why does it say "NONCLUSTERED" ?
September 11, 2012 at 7:36 am
mw112009 (9/11/2012)
I always thought a PRIMARY key is always a clustered index ( Right ? Wrong ? )
Nope. A PK is enforced by a clustered index if there is not an existing clustered index on that table and if NONCLUSTERED is not specified as part of the PK's definition
eg
ALTER TABLE Test
ADD CONSTRAINT pk_Test PRIMARY KEY NONCLUSTERED (SomeColumn)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 11, 2012 at 7:39 am
nharris (9/10/2012)
Out of interest, any particular reason why you removed the MASTER database references? That's where the sys.dm_db_index_physical_stats function and sys.databases view reside so specifying their location merely saves SQL Server the bother of figuring that out for itself.
The DMV definitions (along with all system procedures and views) are in the System Resource database, not master. DMVs don't need a database specified as they are visible in all databases. Some schema views you do want to specify a database to get that DB's schema (eg SELECT * FROM master.sys.objects gets the objects defined in master, SELECT * FROM msdb.sys.objects gets the objects in msdb, etc)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 11, 2012 at 7:45 am
Thank you and peace!
By the way, may I ask what a HEAP is ? A hort answer will be appreciated.
September 11, 2012 at 7:53 am
A heap is an un-ordered table (see http://blogs.msdn.com/b/sqlserverstorageengine/archive/2006/09/19/761437.aspx).
Oh, and apologies for missing the "object_id" in my code - slip of the keyboard...
September 11, 2012 at 7:54 am
A heap is a table that does not have a clustered index defined on it.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 13 posts - 16 through 27 (of 27 total)
You must be logged in to reply to this topic. Login to reply