April 23, 2015 at 1:31 pm
I know it's been asked but:
I have a pretty large DB and a fairly complex query. If I drop buffers and clear cache the query runs in 20 seconds returning 25K rows. Subsequent runs are 2 seconds. Is this the result of the results being cached, execution being cached, other? Are there good ways to close the gap between the initial and later runs? Does the cache stay present until the service restarts or does SQL recycle the memory and if so, based on what criteria?
The execution plan shows index seeks.
April 23, 2015 at 2:22 pm
It depends on which cache you flush - the Plan Cahce, using DBCC FREEPROCCACHE, or the data cache using DBCC DROPCLEANBUFFERS.
You can check if most of the time goes on generating the plan by choosing "show estimated execution plan" in SSMS, or using SET STATISTICS TIME ON.
SQL Server decides which items to evict from cache using a variation of LRU - least recently used. Generally speaking, a plan or data page that is frequently accessed will stay in cache, while non-accessed items will be evicted if needed.
April 23, 2015 at 3:02 pm
Firstly, results are never cached. It will be pages that are being read physically which get cached.
Use the query found here to see if your query is running out of time compiling.
http://www.brentozar.com/blitzcache/compilation-timeout/
But I dont think plans are allowed to compile for 18 seconds so,
what probably is occuring is that you have many physical reads on the initial execution but after that, because pages are in cache, it runs for 2 seconds due to no physical reads.
Let us see the query to see if there may be a way to reduce the complexity for the optimizer.
WITH XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p)
SELECT st.text,
qp.query_plan
FROM (
SELECT TOP 50 *
FROM sys.dm_exec_query_stats
ORDER BY total_worker_time DESC
) AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
WHERE qp.query_plan.exist('//p:StmtSimple/@StatementOptmEarlyAbortReason[.="TimeOut"]') = 1
April 24, 2015 at 6:15 am
I've seen a 5 minute compile time on queries, so they can run for a very long time indeed. If you're looking at the plan, you can see the compile time in the properties of the first operator. Just because you're seeing index seeks doesn't mean the plan is best for the data involved. If you're looking at very large data sets, seeks may be the wrong solution. Double check your statistics to ensure that the estimated and actual values are inline.
As far as getting that spin-up, there is no shortcut. You have to compile the plan, which takes however long it takes. The only way to improve that is to improve the available indexes and statistics so that the optimizer doesn't work too hard. Or, you can reduce the complexity of the query by breaking down the processing into smaller steps, possibly even using a temp table. Then, the data has to be loaded into memory. That's completely dependent on the amount of data being moved and the speed of your disks. You can get faster disks. Once stuff is in memory, as long as it's being accessed, it will tend to stay in memory as long as that memory isn't needed by other processes. But as soon as another query with a different set of data needs comes along, your data can be flushed from memory to make room for the next data set.
"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
April 24, 2015 at 8:24 am
Thanks for the replies. I think I'm going to add an aggregate (reporting) table to the application to make this initial information easier to find and faster to load. I think the problem was twofold: first there is a ton of data to wade through with some many to many relationships. Joining this information together takes a while. By pre-aggregating it I cut out that step and improves the cold-run performance by reducing the number of physical reads and narrowing the execution plan (one stop shopping).
Second, even after getting the data the output is 100K rows. There's no real way around that.
Thanks again as always
ST
April 24, 2015 at 10:21 am
Why 100K rows? Is it an export? If it's not an export of data, 100K is not a reasonable number of standard evaluations.
"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 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply