Optimizing Reports

I've got a report thats somewhat time consuming that runs on my
reporting server, and what I've found is that if I select anything more
than a few months in my daterange parameters, its like 5 minutes to run.
What I'd like to know...
Can I snapshot a years worth of default data, and have the report run
off the snapshot, but allow you to specify date range within the two dates?
Thanks in advance
WestonWeston Weems wrote:
> I've got a report thats somewhat time consuming that runs on my
> reporting server, and what I've found is that if I select anything more
> than a few months in my daterange parameters, its like 5 minutes to run.
> What I'd like to know...
> Can I snapshot a years worth of default data, and have the report run
> off the snapshot, but allow you to specify date range within the two dates?
> Thanks in advance
> Weston
I think a linked report with the default parameter of a years worth of
data setup in a snapshot would be good. Then just reference the linked
report and change the parameters. The report should be served from the
snapshot.
Just theory, I haven't tried it to see if it would work.

Opinions on Option (Keepfixed Plan)

I've got this situation where a certain large stored proc (that's used
all day long) just compiles way too often, to the point where it hurts
the performance. I've tried all the tricks I could find to reduce the
number of compilations. So my last option is to use Option (Keepfixed
Plan) hint.
Are there any downsides to using Option (Keepfixed Plan)?
Regards
Hi Frank
Have you read these article to determine the cause of the recompilations and
address the actual reasons?
Troubleshooting stored procedure recompilation
http://support.microsoft.com/kb/243586
How to identify the cause of recompilation in an SP:Recompile event
http://support.microsoft.com/kb/308737
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Frank Rizzo" <none@.none.com> wrote in message
news:uuunQQy5GHA.4064@.TK2MSFTNGP03.phx.gbl...
> I've got this situation where a certain large stored proc (that's used all
> day long) just compiles way too often, to the point where it hurts the
> performance. I've tried all the tricks I could find to reduce the number
> of compilations. So my last option is to use Option (Keepfixed Plan)
> hint.
> Are there any downsides to using Option (Keepfixed Plan)?
> Regards
|||Kalen Delaney wrote:
> Hi Frank
> Have you read these article to determine the cause of the recompilations and
> address the actual reasons?
> Troubleshooting stored procedure recompilation
> http://support.microsoft.com/kb/243586
> How to identify the cause of recompilation in an SP:Recompile event
> http://support.microsoft.com/kb/308737
Yep, I did all these. I decreased SP:Recompile events in Profiler
substantially, however, they were still happening quite a bit.
Inserting Option (Keepfixed Plan) got rid of the SP:Recompile events and
(anecdotally) increased the performance of the stored proc. In Perfmon,
however, I still get a large number for the SQL Statistic/SQL
Compilations. It must mean something else than what is reflected by the
SP:Recompile event in Profiler.
|||Frank Rizzo wrote:
> Kalen Delaney wrote:
> Yep, I did all these. I decreased SP:Recompile events in Profiler
> substantially, however, they were still happening quite a bit.
> Inserting Option (Keepfixed Plan) got rid of the SP:Recompile events and
> (anecdotally) increased the performance of the stored proc. In Perfmon,
> however, I still get a large number for the SQL Statistic/SQL
> Compilations. It must mean something else than what is reflected by the
> SP:Recompile event in Profiler.
If the recompilations are caused by a large amount of
inserts/updates/deletes, then you could experiment with turning off
auto-update statistics and run a schedule to manually update the
statistics at a time that is convenient for you.
Gert-Jan
|||Gert-Jan Strik wrote:
> Frank Rizzo wrote:
> If the recompilations are caused by a large amount of
> inserts/updates/deletes, then you could experiment with turning off
> auto-update statistics and run a schedule to manually update the
> statistics at a time that is convenient for you.
I turned off the auto-update stats from the get go, but the problem is
still happening.

> Gert-Jan