October 6, 2015 at 8:04 am
Hi,
We have a (large data set) SQL2014 SSRS report that we have determined runs in 1-2 minutes if it is cache.
Problem is every morning after the nights maintenance - this reports plan is no longer in cache and the report takes 35 minutes to run.
If I run the underlying report query in SSMS it takes 35 minutes first time - but now it is in cache and then my end users can run the report in 1-2 minutes.
I have thought about just scheduling a job to run the report query every morning to get it in the buffer - but that seems like a workaround. Is there a better way for me to force the plan to stay in cache? Or a better alternative than running a query job every morning?
October 6, 2015 at 8:20 am
Are you sure it's the plan being cached that saves the time, and not the data being cached. Test with a warm data cache and the plan removed from cache, see which it actually is.
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
October 6, 2015 at 8:28 am
Are you sure it's the plan being cached that saves the time, and not the data being cached. Test with a warm data cache and the plan removed from cache, see which it actually is.
No, not sure...but
If we use 'option recompile' - it reverts back to taking 35 minutes. So does that mean that it's the data that is being cached, correct?
If that's indeed true then I'm guessing there is not much we can do to persist that large data set? Or is there some other way..
October 6, 2015 at 8:50 am
Ok, yeah, that's the plan compiling taking that time, assuming that it consistently takes 35 minutes run with recompile.
To be honest, not much you can really do. Maybe try to simplify the query. The cases I've seen where there was really long compile time were due to nested views, lots and lots of them.
You could also try running the query on a schedule, but that will add load to the server that may not be acceptable.
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
October 6, 2015 at 9:15 am
Let's say this is a memory buffer issue...is there any way to force this to stay in buffer without querying the indexes..?
October 6, 2015 at 9:19 am
Huh?
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
October 6, 2015 at 11:51 am
krypto69 (10/6/2015)
Let's say this is a memory buffer issue...is there any way to force this to stay in buffer without querying the indexes..?
I'm with Gail, I don't understand what you mean.
Gail earlier suggested that it might be getting your data into cache (not memory buffer, different thing entirely). If the query takes 35 minutes to compile (and that is one seriously long compile time), what does this have to do with data into the cache (which is what I'm assuming you mean by querying the indexes)?
However, if it's about cache, then no, if your nightly processes are clearing the cache (which is pretty common depending on what your nightly processes are doing), then getting the commonly accessed stuff back into cache requires running a query to make that happen.
If this is causing issues, then you need to look to what the nightly processes are and how they're being done in order to see if you can reduce their memory footprint.
"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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply