October 30, 2012 at 11:32 am
When running a query on sys.dm_db_index_physical_stats to get the fragmentation level of an index I get a very low page-count number (500 pages).
When I use a different query - using sys.dm_db_partition_stats - to get the size of the index, I get a much larger page count (10 million pages).
The 2nd number is correct, as confirmed independently from SSMS (Storage tab).
Can anyone see what I am doing wrong?
Here are the 2 queries:
USE [db1];
SELECT
I.[name] AS IndexName
,I.index_id
,I.fill_factor
,F.page_count
,F.index_type_desc
,F.index_depth
,F.index_level
,F.avg_fragmentation_in_percent
,F.fragment_count
,F.avg_fragment_size_in_pages
,F.page_count
,F.page_count * 8192 / (1024 * 1024) AS Size_MB
FROM
sys.dm_db_index_physical_stats( DB_ID(), OBJECT_ID('table1'), 9, NULL, 'DETAILED' ) AS F
JOIN
sys.indexes AS I WITH ( NOLOCK )
ON
I.object_id = F.object_id AND I.index_id = F.index_id;
--Returns largest tables and indexes (by page count) in descending order
--Run in desired database
SELECT
schema_name(t.schema_id) as schemaName
,object_name(p.object_id) as table_name
,i.[name] AS index_name
,f.name as fileGroupName
,p.used_page_count
,p.row_count
,p.used_page_count * 8192 / (1024 * 1024) AS Size_MB
FROM
sys.dm_db_partition_stats p
INNER JOIN
sys.tables t
ON
p.object_id = t.object_id
LEFT OUTER JOIN
sys.sysindexes as i
ON
i.id = p.object_id
and i.indid = p.index_id
INNER JOIN
sys.filegroups f
ON
i.groupid = f.data_space_id
ORDER BY
p.used_page_count DESC;
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
October 30, 2012 at 11:40 am
You're looking at a nonclustered index in the first one (index id 9) and all indexes (clustered and nonclustered in the second)
p.s. sys.sysindexes is deprecated, should not be used. Replacement is sys.indexes.
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
October 30, 2012 at 12:28 pm
GilaMonster (10/30/2012)
You're looking at a nonclustered index in the first one (index id 9) and all indexes (clustered and nonclustered in the second)p.s. sys.sysindexes is deprecated, should not be used. Replacement is sys.indexes.
Thank you for the suggestions, I have changed my 2nd query accordingly.
I'm still seeing the same discrepancy though...
SELECT
SCHEMA_NAME( t.[schema_id] ) AS schemaName
,OBJECT_NAME( p.[object_id] ) AS table_name
,i.[name] AS index_name
,f.name AS fileGroupName
,p.used_page_count
,p.row_count
,p.used_page_count * 8192 / ( 1024 * 1024 ) AS Size_MB
FROM
sys.dm_db_partition_stats p
INNER JOIN
sys.tables t
ON
p.[object_id] = t.[object_id]
LEFT OUTER JOIN
sys.indexes AS i
ON
i.[object_id] = p.[object_id]
AND i.index_id = P.index_id
INNER JOIN
sys.filegroups f
ON
i.data_space_id = f.data_space_id
WHERE
OBJECT_NAME( p.[object_id] ) = 'table1'
AND i.index_id = 9
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
October 30, 2012 at 12:59 pm
As I said...
You're looking at a nonclustered index in the first one (index id 9) and all indexes (clustered and nonclustered in the second)
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
October 30, 2012 at 1:04 pm
GilaMonster (10/30/2012)
As I said...You're looking at a nonclustered index in the first one (index id 9) and all indexes (clustered and nonclustered in the second)
I added a filter in the 2nd query, so I am only looking at that one index now;
still same issue:
...
WHERE
OBJECT_NAME( p.[object_id] ) = 'table1'
AND i.index_id = 9;
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
October 30, 2012 at 1:09 pm
Post the results of both please.
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
October 30, 2012 at 1:56 pm
My fault, I was filtering on the following in the 1st query, but did not post that part in the opening post:
WHERE
F.avg_fragmentation_in_percent > 10
AND F.page_count > 100
As a result, I was not getting all the index levels in the result set - only index level 2 with 535 pages was returned.
This index is huge (has five levels), but only level 2 is significantly fragmented: avg frag=89%.
Here are the fragmentation details of this index (level 0 is the leaf level):
level, page_count, avg_frag_percent, Size_MB
--------------------------------------------------------------------------------------------------------------------------
0, 9870459, 1.56, 77112
1, 54625, 6.55, 426
2, 537, 89.57, 4
3, 4, 50, 0
4, 1, 0, 0
My question now is, is it still worth defragmenting this index if only level 2 is heavily fragmented?
Defragging this index is a very costly operation.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
October 30, 2012 at 2:02 pm
Probably not. The fragmented level is not 1000 pages, it's most likely going to be in memory. Maybe, if you're doing huge range scans that require SQL to read ahead 2 levels up the index, but probably not.
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
October 30, 2012 at 2:33 pm
GilaMonster (10/30/2012)
Probably not. The fragmented level is not 1000 pages, it's most likely going to be in memory. Maybe, if you're doing huge range scans that require SQL to read ahead 2 levels up the index, but probably not.
Thank you!
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply