Feature: ignore parameter sniffing in SQL Server #740
Replies: 1 comment 1 reply
-
We had a similar issue but with the select_changes stored procedure. When you run the query multiple times, SQL Server internally caches the execution plan and stuff so you won't have the same timing behavior. I have no idea how this could happen in write scenarios. But I strongly encourage you to validate the execution plan of your SP and run the queries to clear the execution plan cache and re-build the indexes |
Beta Was this translation helpful? Give feedback.
-
We have an issue where every now and then specific
_changes
stored procedures take very long to execute, leading to synchronizations timing out. This is most probably due to our custom filters, but we found that if we disabled parameter sniffing for the procedure by usingOPTION(OPTIMIZE FOR UNKNOWN)
it instantly resolved the problem: the procedure completed much faster and the synchronization completed successfully. This was on a TEST database, albeit with large amounts of data. We have yet to see how this pans out in production, but we feel optimistic.That said, we think it would be a great feature, if there would be a per table setting (maybe in
SetupTable
class) where you could specify if you wanted to disable parameter sniffing. Is this a viable option? If not, is there a better way of enabling this option aside from running a custom script every time the procedures are rebuilt?Bonus mystery: One thing that still boggles my mind is that to debug this issue I was using SQL Profiler, where I observed that a specific
_change
stored procedure took 180 seconds to complete. I copied the executed command including the used parameters and ran it in SSMS and it completed almost instantly. This was repeated multiple times. After that I disabled parameter sniffing on the procedure, ran the synchronization again and the sync completed successfully (didn't actually look at the execution time, but the whole sync completed in under a minute). Now, if the issue was truly parameter sniffing, how could the same parameters produce different results? I'm thinking caching? But wouldn't that mean that the procedure that was ran during the synchronization would also be cached?Beta Was this translation helpful? Give feedback.
All reactions