April 26, 2016 at 3:25 pm
I have a series of SPs that run nightly. They run twice each, once building current month data, once building prior month data. Each night the first run of any given proc takes 4-8 minutes, the second run takes less than a minute. If I were to run it again it would consistently take less than a minute. The database is highly transactional, the statistics will have changed from the previous day's run. Is there anything I can do to prep the SP so that the first run is more efficient? I tried making a call to the SP with a parameter that would essentially not return any data, that still took a while. The subsequent calls with valid data producing parameters took less than a minute. I ran a trace and the CPU, reads, and writes were very similar but the duration was 5 times longer.
What additional information should I be gathering in my analysis?
Thanks,
Myles
April 26, 2016 at 3:52 pm
The most likely cause of this scenario is data not being in cache.
With the underlying pages not being in memory, the initial run has to load the pages from disk to memory. Subsequent runs access the data in memory.
To see if that is the case, check physical IO for each of the runs. If this is your scenario, then the first run will have high physical IO, and subsequent runs will have very little.
Cheers!
April 26, 2016 at 4:27 pm
Thanks! I ran it with STATISTICS IO ON; and that appears to be the case. Here's a pretty typical line, no physical reads.
Table 'person'. Scan count 2156, logical reads 6911, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
I'd have to wait until later to run it with the data out of memory. Is there anything I can do, or just have to deal with it?
April 26, 2016 at 4:54 pm
There are really only a couple general ways to get around it.
1) Make sure the data is in memory for the runs where duration matters. This can take a couple different forms.
1A) Allocate enough memory to the instance so that the pages stay in memory.
1B) Run some SELECTs from the underlying tables to warm the cache before the real runs.
2) Tune the query so that it does less IO. The less IO it does, the less of a difference (in absolute terms) there will be between cold cache performance and warm cache performance.
Without knowing any more detail, it's difficult to say which of these would work best for you.
The fact that the person table is accessed 2156 times suggests that there are probably tuning opportunities, but it's difficult to say with certainty.
Cheers!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply