Query Performance

Running SQL 2005, SP1.
Any idea how to speed up the following query? It takes only a few seconds
when cached, but since it is only run once a week, by the time it gets run,
it is no longer in cache and takes over a minute.
SELECT 'The name of my database' AS DBName,
s.object_id,
s.name AS TableName,
i.index_id,
i.name AS IndexName,
i.fill_factor,
ps.page_count,
ps.avg_fragmentation_in_percent,
ps.fragment_count,
ps.partition_number,
ps.index_type_desc,
ps.record_count,
ps.avg_page_space_used_in_percent,
ps.avg_fragment_size_in_pages,
ps.avg_record_size_in_bytes,
ps.min_record_size_in_bytes,
ps.max_record_size_in_bytes
FROM sys.objects s
JOIN sys.indexes i
ON s.object_id = i.object_id
JOIN master.sys.dm_db_index_physical_stats (19, NULL, NULL , NULL,
'DETAILED') ps
ON s.object_id = ps.object_id
AND i.index_id = ps.index_id
WHERE s.type_desc = 'USER_TABLE'
AND i.index_id > 0
AND i.index_id < 255
AND ps.alloc_unit_type_desc = 'IN_ROW_DATA'
AND ps.index_level = 0 --this essentially makes the query return just
Limited mode records, but with Detailed mode data
GROUP BY s.object_id,
s.name,
i.index_id,
i.name,
i.fill_factor,
ps.page_count,
ps.avg_fragmentation_in_percent,
ps.fragment_count,
ps.partition_number,
ps.index_type_desc,
ps.record_count,
ps.avg_page_space_used_in_percent,
ps.avg_fragment_size_in_pages,
ps.avg_record_size_in_bytes,
ps.min_record_size_in_bytes,
ps.max_record_size_in_bytes
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200708/1what does the query do ?
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:76e5a9a710d68@.uwe...
> Running SQL 2005, SP1.
> Any idea how to speed up the following query? It takes only a few seconds
> when cached, but since it is only run once a week, by the time it gets
> run,
> it is no longer in cache and takes over a minute.
> SELECT 'The name of my database' AS DBName,
> s.object_id,
> s.name AS TableName,
> i.index_id,
> i.name AS IndexName,
> i.fill_factor,
> ps.page_count,
> ps.avg_fragmentation_in_percent,
> ps.fragment_count,
> ps.partition_number,
> ps.index_type_desc,
> ps.record_count,
> ps.avg_page_space_used_in_percent,
> ps.avg_fragment_size_in_pages,
> ps.avg_record_size_in_bytes,
> ps.min_record_size_in_bytes,
> ps.max_record_size_in_bytes
> FROM sys.objects s
> JOIN sys.indexes i
> ON s.object_id = i.object_id
> JOIN master.sys.dm_db_index_physical_stats (19, NULL, NULL , NULL,
> 'DETAILED') ps
> ON s.object_id = ps.object_id
> AND i.index_id = ps.index_id
> WHERE s.type_desc = 'USER_TABLE'
> AND i.index_id > 0
> AND i.index_id < 255
> AND ps.alloc_unit_type_desc = 'IN_ROW_DATA'
> AND ps.index_level = 0 --this essentially makes the query return
> just
> Limited mode records, but with Detailed mode data
> GROUP BY s.object_id,
> s.name,
> i.index_id,
> i.name,
> i.fill_factor,
> ps.page_count,
> ps.avg_fragmentation_in_percent,
> ps.fragment_count,
> ps.partition_number,
> ps.index_type_desc,
> ps.record_count,
> ps.avg_page_space_used_in_percent,
> ps.avg_fragment_size_in_pages,
> ps.avg_record_size_in_bytes,
> ps.min_record_size_in_bytes,
> ps.max_record_size_in_bytes
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200708/1
>|||I tried to run it and got the following error
Msg 297, Level 16, State 12, Line 1
The user does not have permission to perform this action.
I am a sysadmin on the server. hmmm.. Any reason why its failing ?
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:76e5a9a710d68@.uwe...
> Running SQL 2005, SP1.
> Any idea how to speed up the following query? It takes only a few seconds
> when cached, but since it is only run once a week, by the time it gets
> run,
> it is no longer in cache and takes over a minute.
> SELECT 'The name of my database' AS DBName,
> s.object_id,
> s.name AS TableName,
> i.index_id,
> i.name AS IndexName,
> i.fill_factor,
> ps.page_count,
> ps.avg_fragmentation_in_percent,
> ps.fragment_count,
> ps.partition_number,
> ps.index_type_desc,
> ps.record_count,
> ps.avg_page_space_used_in_percent,
> ps.avg_fragment_size_in_pages,
> ps.avg_record_size_in_bytes,
> ps.min_record_size_in_bytes,
> ps.max_record_size_in_bytes
> FROM sys.objects s
> JOIN sys.indexes i
> ON s.object_id = i.object_id
> JOIN master.sys.dm_db_index_physical_stats (19, NULL, NULL , NULL,
> 'DETAILED') ps
> ON s.object_id = ps.object_id
> AND i.index_id = ps.index_id
> WHERE s.type_desc = 'USER_TABLE'
> AND i.index_id > 0
> AND i.index_id < 255
> AND ps.alloc_unit_type_desc = 'IN_ROW_DATA'
> AND ps.index_level = 0 --this essentially makes the query return
> just
> Limited mode records, but with Detailed mode data
> GROUP BY s.object_id,
> s.name,
> i.index_id,
> i.name,
> i.fill_factor,
> ps.page_count,
> ps.avg_fragmentation_in_percent,
> ps.fragment_count,
> ps.partition_number,
> ps.index_type_desc,
> ps.record_count,
> ps.avg_page_space_used_in_percent,
> ps.avg_fragment_size_in_pages,
> ps.avg_record_size_in_bytes,
> ps.min_record_size_in_bytes,
> ps.max_record_size_in_bytes
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200708/1
>|||Hi Hassan
Perhaps you don't have a database with ID 19? Try replacing the 19 in the
call to sys.dm_db_index_physical_stats with this:
db_id('name of a real database on your server')
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Hassan" <hassan@.hotmail.com> wrote in message
news:uBCbwOf4HHA.5796@.TK2MSFTNGP05.phx.gbl...
>I tried to run it and got the following error
> Msg 297, Level 16, State 12, Line 1
> The user does not have permission to perform this action.
>
> I am a sysadmin on the server. hmmm.. Any reason why its failing ?
> "cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
> news:76e5a9a710d68@.uwe...
>> Running SQL 2005, SP1.
>> Any idea how to speed up the following query? It takes only a few seconds
>> when cached, but since it is only run once a week, by the time it gets
>> run,
>> it is no longer in cache and takes over a minute.
>> SELECT 'The name of my database' AS DBName,
>> s.object_id,
>> s.name AS TableName,
>> i.index_id,
>> i.name AS IndexName,
>> i.fill_factor,
>> ps.page_count,
>> ps.avg_fragmentation_in_percent,
>> ps.fragment_count,
>> ps.partition_number,
>> ps.index_type_desc,
>> ps.record_count,
>> ps.avg_page_space_used_in_percent,
>> ps.avg_fragment_size_in_pages,
>> ps.avg_record_size_in_bytes,
>> ps.min_record_size_in_bytes,
>> ps.max_record_size_in_bytes
>> FROM sys.objects s
>> JOIN sys.indexes i
>> ON s.object_id = i.object_id
>> JOIN master.sys.dm_db_index_physical_stats (19, NULL, NULL , NULL,
>> 'DETAILED') ps
>> ON s.object_id = ps.object_id
>> AND i.index_id = ps.index_id
>> WHERE s.type_desc = 'USER_TABLE'
>> AND i.index_id > 0
>> AND i.index_id < 255
>> AND ps.alloc_unit_type_desc = 'IN_ROW_DATA'
>> AND ps.index_level = 0 --this essentially makes the query return
>> just
>> Limited mode records, but with Detailed mode data
>> GROUP BY s.object_id,
>> s.name,
>> i.index_id,
>> i.name,
>> i.fill_factor,
>> ps.page_count,
>> ps.avg_fragmentation_in_percent,
>> ps.fragment_count,
>> ps.partition_number,
>> ps.index_type_desc,
>> ps.record_count,
>> ps.avg_page_space_used_in_percent,
>> ps.avg_fragment_size_in_pages,
>> ps.avg_record_size_in_bytes,
>> ps.min_record_size_in_bytes,
>> ps.max_record_size_in_bytes
>> --
>> Message posted via SQLMonster.com
>> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200708/1
>|||Hi cbrichards
My guess is that the speed difference is not dependent on whether the query
is cached, but whether all the data that the query needs is cached. Your
query looks at every row on every page in every user table in an entire
database. Just because you are restricting the data actually returned
doesn't make it less work for SQL Server to find all the data and perform
the computations.
The reason that sys.dm_db_index_physical_stats function has a 'limited' mode
is because 'detailed' mode can take a long time on a big database. So to
speed this up, you can either use a database that is smaller, or change the
'detailed' mode to 'limited'
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:76e5a9a710d68@.uwe...
> Running SQL 2005, SP1.
> Any idea how to speed up the following query? It takes only a few seconds
> when cached, but since it is only run once a week, by the time it gets
> run,
> it is no longer in cache and takes over a minute.
> SELECT 'The name of my database' AS DBName,
> s.object_id,
> s.name AS TableName,
> i.index_id,
> i.name AS IndexName,
> i.fill_factor,
> ps.page_count,
> ps.avg_fragmentation_in_percent,
> ps.fragment_count,
> ps.partition_number,
> ps.index_type_desc,
> ps.record_count,
> ps.avg_page_space_used_in_percent,
> ps.avg_fragment_size_in_pages,
> ps.avg_record_size_in_bytes,
> ps.min_record_size_in_bytes,
> ps.max_record_size_in_bytes
> FROM sys.objects s
> JOIN sys.indexes i
> ON s.object_id = i.object_id
> JOIN master.sys.dm_db_index_physical_stats (19, NULL, NULL , NULL,
> 'DETAILED') ps
> ON s.object_id = ps.object_id
> AND i.index_id = ps.index_id
> WHERE s.type_desc = 'USER_TABLE'
> AND i.index_id > 0
> AND i.index_id < 255
> AND ps.alloc_unit_type_desc = 'IN_ROW_DATA'
> AND ps.index_level = 0 --this essentially makes the query return
> just
> Limited mode records, but with Detailed mode data
> GROUP BY s.object_id,
> s.name,
> i.index_id,
> i.name,
> i.fill_factor,
> ps.page_count,
> ps.avg_fragmentation_in_percent,
> ps.fragment_count,
> ps.partition_number,
> ps.index_type_desc,
> ps.record_count,
> ps.avg_page_space_used_in_percent,
> ps.avg_fragment_size_in_pages,
> ps.avg_record_size_in_bytes,
> ps.min_record_size_in_bytes,
> ps.max_record_size_in_bytes
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200708/1
>|||I believe Kalen answered your performance question. I assume that the
performance this weekly query is only any issue because you run it
interactively (report?) and perhaps for many databases. You might consider
inserting the results into a reporting/data-mart table and scheduling the
process to run off-hours. Not only will that that allow you to report from
a more optimized/specialized schema, it will allow you to keep historical
information too.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:76e5a9a710d68@.uwe...
> Running SQL 2005, SP1.
> Any idea how to speed up the following query? It takes only a few seconds
> when cached, but since it is only run once a week, by the time it gets
> run,
> it is no longer in cache and takes over a minute.
> SELECT 'The name of my database' AS DBName,
> s.object_id,
> s.name AS TableName,
> i.index_id,
> i.name AS IndexName,
> i.fill_factor,
> ps.page_count,
> ps.avg_fragmentation_in_percent,
> ps.fragment_count,
> ps.partition_number,
> ps.index_type_desc,
> ps.record_count,
> ps.avg_page_space_used_in_percent,
> ps.avg_fragment_size_in_pages,
> ps.avg_record_size_in_bytes,
> ps.min_record_size_in_bytes,
> ps.max_record_size_in_bytes
> FROM sys.objects s
> JOIN sys.indexes i
> ON s.object_id = i.object_id
> JOIN master.sys.dm_db_index_physical_stats (19, NULL, NULL , NULL,
> 'DETAILED') ps
> ON s.object_id = ps.object_id
> AND i.index_id = ps.index_id
> WHERE s.type_desc = 'USER_TABLE'
> AND i.index_id > 0
> AND i.index_id < 255
> AND ps.alloc_unit_type_desc = 'IN_ROW_DATA'
> AND ps.index_level = 0 --this essentially makes the query return
> just
> Limited mode records, but with Detailed mode data
> GROUP BY s.object_id,
> s.name,
> i.index_id,
> i.name,
> i.fill_factor,
> ps.page_count,
> ps.avg_fragmentation_in_percent,
> ps.fragment_count,
> ps.partition_number,
> ps.index_type_desc,
> ps.record_count,
> ps.avg_page_space_used_in_percent,
> ps.avg_fragment_size_in_pages,
> ps.avg_record_size_in_bytes,
> ps.min_record_size_in_bytes,
> ps.max_record_size_in_bytes
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200708/1
>