June 14, 2010 at 1:33 pm
I run the script below as part of a job every night at the same time and the duration of the job varies from a few seconds to an hour.
In BOL I read that sys.dm_db_index_physical_stats requires intent-shared table locks.
Is this the reason duration varies so widely from time to time, ie. depending on what else is running on the database?
SELECT
(select '['+s.name+'].['+OBJECT_NAME(I.object_id)+']'
from sys.schemas s,sys.all_objects o
where s.[schema_id] = o.[schema_id] and
o.[object_id] = I.[object_id]
) AS ObjectName
,I.[name] AS IndexName
,I.index_id
,I.fill_factor
,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
,0
FROM
sys.dm_db_index_physical_stats( DB_ID(), NULL, NULL, 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
WHERE
F.avg_fragmentation_in_percent > 15
AND F.page_count > 100
ORDER BY
F.page_count DESC
,F.avg_fragment_size_in_pages ASC
,F.avg_fragmentation_in_percent 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]
June 15, 2010 at 7:15 am
Yes, it's absolutely possible that you're seeing contention. That much contention though would be a bit concerning. What is running on the systems that could block a shared lock for over an hour?
"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
June 15, 2010 at 7:28 am
Grant Fritchey (6/15/2010)
Yes, it's absolutely possible that you're seeing contention. That much contention though would be a bit concerning. What is running on the systems that could block a shared lock for over an hour?
I haven't beem able to pinpoint anything out of the ordinary, but here is what I noticed last night;
duration appears to vary a lot with the fragmentation threshold that I set to filter out which indexes to look at for fragmentation:
at 15%, job completed in 7 seconds, but at 10%, job completed in 16 minutes!
Does that make any sense?
__________________________________________________________________________________
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 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply