Lesson #3: Symptoms of Parameter Sniffing

Lesson 4 of 6 • 1 upvotes • 14:21mins

Sourav Mukherjee

Few suggestible workarounds to address the issue: Create SQL server stored procedures using the with recompile option Use the SQL server hint option (recompile) Use the SQL server hint option (optimize for) Use dummy variables on SQL server stored procedures Disable SQL server parameter sniffing at the instance level Disable parameter sniffing for a specific SQL server query Disable specific trace flags (4136: disable parameter sniffing, 9481: disable new cardinality estimation) Enable trace flag 4199 (for optimizer fixes. Note: by-default optimizer fixes choice is off.)