January 16, 2013 at 1:10 pm
I know that if you filter this on things like page count or avg_fragmentation_in_percent or some such, it cannot apply the filter until after all results are returned. that makes sense, because you have to count pages before you can tell how many pages there are...
does the same rule apply when filtering it by dbid? ie. if I filter it on dbid for a very small db on a server full of larger databases, will it roll through all data, and then only present data for that particular dbid?
I dont believe thats the case, but I ask because I am currently running the following query:
SELECT 'dbname',o.name as TableName, i.name as IndexName, ps.index_id , ps.avg_fragmentation_in_percent, ps.page_count, getdate()
FROM sys.dm_db_index_physical_stats (db_id('[dbname]'), NULL, NULL, NULL, 'LIMITED') ps
join sys.objects o on o.object_id = ps.object_id
join sys.indexes i on i.object_id = ps.object_id and i.index_id = ps.index_id
against a little db with < 10 tables and a couple dozen indexes. its been going for 35+ minutes. There are some medium large databases on this server as well. does it have to parse through all index data for all databases to retreive these few?
the IO on this system isnt stellar but its not bad enough to explain away this kind of a duration on its own.
January 16, 2013 at 1:14 pm
That should just run on the indexes in the specified database.
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
January 16, 2013 at 1:15 pm
thanks for confirming what I thought I knew but was questioning 😉
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply