SQL poor stored procedure execution plan performance – parameter sniffing

by

Basically yes – parameter sniffing (in some patch levels of) SQL Server 2005 is badly broken. I have seen plans that effectively never complete (within hours on a small data set) even for small (few thousand rows) sets of data which complete in seconds once the parameters are masked. And this is in cases where the parameter has always been the same number. I would add that at the same time I was dealing with this, I found a lot of problems with LEFT JOIN/NULLs not completing and I replaced them with NOT IN or NOT EXISTS and this resolved the plan to something which would complete. Again, a (very poor) execution plan issue. At the time I was dealing with this, the DBAs would not give me SHOWPLAN access, and since I started masking every SP parameter, I’ve not had any further execution plan issues where I would have to dig in to this for non-completion.

In SQL Server 2008 you can use OPTIMIZE FOR UNKNOWN.

More Related Contents:

  1. How do I obtain a Query Execution Plan in SQL Server?
  2. I dont know sql syntax for my table [closed]
  3. How do I UPDATE from a SELECT in SQL Server?
  4. Get top 1 row of each group
  5. How to calculate age (in years) based on Date of Birth and getDate()
  6. CROSS JOIN vs INNER JOIN in SQL
  7. Convert Month Number to Month Name Function in SQL
  8. Function vs. Stored Procedure in SQL Server
  9. Multiple INSERT statements vs. single INSERT with multiple VALUES
  10. Limit on the WHERE col IN (…) condition
  11. Removing leading zeroes from a field in a SQL statement
  12. Generate a resultset of incrementing dates in TSQL
  13. Getting result of dynamic SQL into a variable for sql-server
  14. How to get difference between two rows for a column field?
  15. Better techniques for trimming leading zeros in SQL Server?
  16. How do I split a delimited string so I can access individual items?
  17. Common Table Expression, why semicolon?
  18. T-SQL split string based on delimiter
  19. SQL Server SELECT into existing table
  20. Is there a performance difference between CTE , Sub-Query, Temporary Table or Table Variable?
  21. Why use Select Top 100 Percent?
  22. How do I prevent a database trigger from recursing?
  23. What is the Null Character literal in TSQL?
  24. TSQL – How to use GO inside of a BEGIN .. END block?
  25. SQL take just the numeric values from a varchar
  26. SQL Server SELECT INTO @variable?
  27. WITH CHECK ADD CONSTRAINT followed by CHECK CONSTRAINT vs. ADD CONSTRAINT
  28. Select a column if other column is null
  29. Date serial in SQL?
  30. SQL Server: +(unary) operator on non-numeric Strings

Leave a Comment