I'm tuning a stored procedure that is used by the first page that loads whenever a user logs into our website and it's very frequently used. When I run the procedure the first time it runs in about 10 to 12 seconds but when I run it the second time it executes in 1 second or less.
I'm assuming it's because the optimizer is reading the cached pages the second time. When I'm testing my tuned procedure against the current proc I'm having to wait at least an hour between each run to test their actual performance without any cached being used.
Unfortunately I don't have the option of freeing up the cache since our DBAs won't allow it. This type of testing is very time consuming since everytime I make some change I'll have to wait at least an hour to compare runtimes.
I'm very sure there are much better ways of testing. What kind of testing strategies do you use? Do the real world applications use cached data or read from disk? If you can provide any insights into this and also provide some articles I would really appreciate it. Thanks!
-
If this proc runs frequently, how is testing it against a cold cache realistic?– Erik DarlingOct 22, 2022 at 17:39
-
@erik darling That is what I'm trying to understand. I've read random articles on hot and cold caches but I'm very confused as to how sql server accesses pages in real world applications. Can you please point me to some resources that expound on this? Thanks!– JoeCharlesOct 22, 2022 at 17:50
1 Answer
What kind of testing strategies do you use?
I look at the actual execution plan. Most times, it'll indicate where most of the time was spent for your query in addition to warnings / problems and relevant metrics. If you upload your execution plan to Paste The Plan, you can link it in a DBA.StackExchange question to get additional help here.
I don't care about cold vs warm cache, since if there's an actual issue to tune, it probably is irrelevant to whether the data pages are cached yet (for the most part). See related information on why that's so in this recent answer to a tangentially related question.
You can also look at the I/O Statistics and Time Statistics which can help pinpoint the source of your problems too. They can show long CPU Times which can indicate multiple things such as heavy computational operations, maybe a scalar function running row by row for example. It can also show large Physical Reads or Logical Reads, which may indicate an indexing issue because your execution plan isn't using the most efficient index or index operation such as Index Scans or Table Scans.
The other common issue with stored procedures is parameter sniffing (when the procedure has parameters). A quick way to determine if that's the issue is to add the OPTION (RECOMPILE) or WITH RECOMPILE query hint to your procedure. If the problem goes away, then it's likely a parameter sniffing issue. That doesn't mean this is the solution, as that query hint causes the procedure to be recompiled every time it runs (which if frequently, can be computationally taxing on your server's CPU). Rather I use it as a test for the issue sometimes. But in some cases it is a valid solution.
