Parameter Sniffing (or Spoofing) in SQL Server

by

FYI – you need to be aware of something else when you’re working with SQL 2005 and stored procs with parameters.

SQL Server will compile the stored proc’s execution plan with the first parameter that’s used. So if you run this:

usp_QueryMyDataByState 'Rhode Island'

The execution plan will work best with a small state’s data. But if someone turns around and runs:

usp_QueryMyDataByState 'Texas'

The execution plan designed for Rhode-Island-sized data may not be as efficient with Texas-sized data. This can produce surprising results when the server is restarted, because the newly generated execution plan will be targeted at whatever parameter is used first – not necessarily the best one. The plan won’t be recompiled until there’s a big reason to do it, like if statistics are rebuilt.

This is where query plans come in, and SQL Server 2008 offers a lot of new features that help DBAs pin a particular query plan in place long-term no matter what parameters get called first.

My concern is that when you rebuilt your stored proc, you forced the execution plan to recompile. You called it with your favorite parameter, and then of course it was fast – but the problem may not have been the stored proc. It might have been that the stored proc was recompiled at some point with an unusual set of parameters and thus, an inefficient query plan. You might not have fixed anything, and you might face the same problem the next time the server restarts or the query plan gets recompiled.

More Related Contents:

  1. T-SQL: Opposite to string concatenation – how to split string into multiple records [duplicate]
  2. Check if table exists in SQL Server
  3. Sql Server string to date conversion
  4. Insert multiple rows WITHOUT repeating the “INSERT INTO …” part of the statement?
  5. Does T-SQL have an aggregate function to concatenate strings? [duplicate]
  6. How to get Time from DateTime format in SQL?
  7. SQL Server String or binary data would be truncated
  8. Datatype for storing ip address in SQL Server
  9. How to change identity column values programmatically?
  10. Cannot truncate table because it is being referenced by a FOREIGN KEY constraint?
  11. How much size “Null” value takes in SQL Server
  12. best way to convert and validate a date string
  13. SQL Server ORDER BY date and nulls last
  14. How to remove accents and all chars a..z in sql-server?
  15. Find non-ASCII characters in varchar columns using SQL Server
  16. WHERE clause on SQL Server “Text” data type
  17. SQL Server error on update command – “A severe error occurred on the current command”
  18. Aggregate bitwise-OR in a subquery
  19. SQL server schema and default schema
  20. Check if a string contains a substring in SQL Server 2005, using a stored procedure
  21. FOR XML PATH(”): Escaping “special” characters
  22. Cannot find either column “dbo” or the user-defined function or aggregate “dbo.Splitfn”, or the name is ambiguous
  23. Does query plan optimizer works well with joined/filtered table-valued functions?
  24. What are row, page and table locks? And when they are acquired?
  25. @@ERROR and/or TRY – CATCH
  26. Is it possible to write single query on two table which are not connected to each other?
  27. SQL Server: Isolation level leaks across pooled connections
  28. SQL Server – Dynamic PIVOT Table – SQL Injection
  29. How to add custom attributes to SQL connection string?
  30. Why do SQL Server Scalar-valued functions get slower?

Leave a Comment