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
>

Query parameter drop-down not available once report is cached

Hi, I have a date parameter that is populated from a query to give me a
month drop-down on my report. When a user selects the month and runs
the report, it passes the parameter to a query to return a dataset.
This is all straight forward. Since the report runs quite slowly, I
wanted to cache the report witha dafault month, but allow the users to
then re-run the report with other months. I found that once I cached
the report on a shared schedule, the month drop-down is no longer
available for me to select a new query parameter to rerun the report.
Another filter parameter is available, so I am assuming it it because
it is a query parameter.
Is there a way to cache the reoprt, but still allow users to reselect
the query parameter and rerun the report?You can make the month parameter a filter parameter, bring back a all the
months data and then apply the month filter to the data regions in the
report..
Or if you are using Enterprise Edition you could preload the cache using a
data-driven subscription, see "Preloading the Cache"
here:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rswork/htm/rms_processing_v1_2p89.asp
--
Douglas McDowell
douglas@.nospam.solidqualitylearning.com
"MDXQuery" <imgroup1@.hotmail.com> wrote in message
news:1109694129.517130.311640@.g14g2000cwa.googlegroups.com...
> Hi, I have a date parameter that is populated from a query to give me a
> month drop-down on my report. When a user selects the month and runs
> the report, it passes the parameter to a query to return a dataset.
> This is all straight forward. Since the report runs quite slowly, I
> wanted to cache the report witha dafault month, but allow the users to
> then re-run the report with other months. I found that once I cached
> the report on a shared schedule, the month drop-down is no longer
> available for me to select a new query parameter to rerun the report.
> Another filter parameter is available, so I am assuming it it because
> it is a query parameter.
> Is there a way to cache the reoprt, but still allow users to reselect
> the query parameter and rerun the report?
>