September 13, 2011 at 9:51 am
I've got some stored procs that aggregate large datasets and return roughly 18-200 rows (an 18 month stacked bar chart).
I have similar stored procs that run fine but a few seem to have trouble on the first time they run. Sometimes, dropping the proc and recreating it helps.
This points me toward a cached query plan issue. Would a WITH Recompile hint help me? Is this the "parameter sniffing" problem I've heard about?
Thanks in advance,
SDB
September 13, 2011 at 10:01 am
Usually first time only issue is with data cache.
Sp cache would have troubles all over the day.
You could schedule a job to warm up the cache or run the sp / report at off hours and then the subsequent run should be faster.
Last option would be that the first run caches the report and then all runs only show the cached report.
September 13, 2011 at 10:35 am
It's hard to say without more information, but here's an article I found very useful when I ran into similar problems that did turn out to be an issue with parameter sniffing:
September 14, 2011 at 4:01 am
It could be bad parameter sniffing (differentiated because parameter sniffing is going on constantly and helps you). You need to get a copy of the execution plan when the query is running well and a copy when it's running poorly in order to compare the two. If you've got two different execution plans, there's a good chance it's bad parameter sniffing. If they're the same, it's something else, compile time, moving data into cache, something along those lines.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
September 27, 2011 at 9:37 am
notoriousdba (9/13/2011)
It's hard to say without more information, but here's an article I found very useful when I ran into similar problems that did turn out to be an issue with parameter sniffing:
I looked through this and identified that two different query plans are being used (using the arithabort trick) so parameter sniffing is a likely culprit. But as I'm using dynamic search queries (as described in the article) I'm not sure how to stop it/ fix it (using the ROBUST PLAN option didn't seem to do much).
The procs run off of tables that are dropped and rebuild nightly so I'm wondering if switching that to truncate and repopulate might maintain the statistics better and allow the good plan to stay cached.
However, I'm also seeing a high number of physical reads reads on the first runtime when I look at sys.dm_exec_requests. So a warming up the cache might also help. For now, I think I'll put a statement in the last step of the build job that executes my problem procedures to try to warm them up each morning.
Thanks again for all the replies, I think I'm getting close to a solution here. Let me know what you think before I implement something that doesn't address the actual issue.
September 27, 2011 at 9:46 am
Sorry for the double post, I'm also looking into putting DBCC pintable on the tables in question (2 tables that make up the greater part of our datamart). Im hesitant to do so, since I know I could potentially eat a lot of memory by doing so, causing other queries to suffer.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply