March 31, 2011 at 7:54 am
I modified someone's query to join data from sys.indexes. The resultset has some null values in i.name and im not sure why.
Declare @db_id smallint
Declare @tab_id int
set @db_id=db_id('database')
--set @tab_id=object_id( 'tblSearch')
SELECT p.object_id, p.index_id, i.name, avg_fragmentation_in_percent, fragment_count, record_count, page_count
FROM sys.dm_db_index_physical_stats
(@db_id,NULL, NULL, NULL , 'Detailed') p
--(@db_id,NULL, NULL, NULL , NULL) p
inner join sys.indexes i
on p.object_id = i.object_id and p.index_id = i.index_id
order by avg_fragmentation_in_percent desc
March 31, 2011 at 8:45 am
Alter your T-SQL to:
SELECT p.object_id,
--Add this to your t-sql
object_name(p.object_id) AS 'object name',p.index_type_desc,
--End addition
p.index_id, i.name, avg_fragmentation_in_percent, fragment_count, record_count, page_count
FROM sys.dm_db_index_physical_stats
(@db_id,NULL, NULL, NULL , 'Detailed') p
inner join sys.indexes i
on p.object_id = i.object_id and p.index_id = i.index_id
order by avg_fragmentation_in_percent desc
The addition will NOT return a name, but will give you the object name and index_type_desc. And then go on from there.
March 31, 2011 at 8:52 am
Make sure that you are in the DB that you refer to. Index Physical Stats is database independent, sys.indexes is not. There's nothing wrong with your code that I can see. Though add the Object_name so that you can see the table name.
I can't recall if heaps show a name in sys.indexes or not, they may be null, in which case that explains the nulls. Confirm by checking the index id - 0
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
March 31, 2011 at 12:22 pm
it turns out all the nulls for name have 0 for indexid. im reading info about heaps now to determine whether or not i should be concerned about fragmenation of heaps. Thanks in advance for any and all information.
March 31, 2011 at 12:48 pm
Yes, more concern over forwarding pointers. However the point is moot, you can't rebuild a heap.
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
March 31, 2011 at 2:11 pm
hmm, i'm thinking i'll add forwarded_record_count to the select statement. I'm planning on running the query on a weekly basis and analyze. Currently, we're running a alter index rebuild on all indexes int he db, but I'm not so sure that its fully defrag'd after the rebuild. I'm looking to confirm my suspicions and recommend any changes to the maintenance plan.
the article below describes some ways to remove forwarding pointers. It includes alter table rebuild, but it looks like that is new for sql 2008.
March 31, 2011 at 2:32 pm
foscsamuels (3/31/2011)
It is indeed, SQL 2008 only.
Generally there aren't too many good reasons for a table to be a heap. There are a few, but in most cases heaps are there out of ignorance, not intentional design.
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
April 1, 2011 at 9:19 am
I ran the query last night and the output is much more useful. Many thanks.
Now I've added it to a Sql Agent Job that is associated with a weekly maintenance plan. I have the query set to output results to a file. However, I'm concerned with the format of the output. I'd like to be able to format the output as a csv. This would enable me to be able to open the file in excel and sort the information on different columns. Currently, I'm thinking i'd have to modify the tsql to output a file or run a sqlcmd command to output a csv. Any suggestions on how to do this?
Declare @db_id smallint
set @db_id=db_id('dbname')
SELECT
object_name(p.object_id) AS 'object name',p.index_type_desc,
p.object_id, p.index_id, i.name, avg_fragmentation_in_percent, fragment_count, forwarded_record_count, record_count, page_count
FROM sys.dm_db_index_physical_stats
(@db_id,NULL, NULL, NULL , 'Detailed') p
inner join sys.indexes i
on p.object_id = i.object_id and p.index_id = i.index_id
order by avg_fragmentation_in_percent desc
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply