SQL Server Query time out depending on Where Clause

by

The Problem you are experiencing is (almost certainly) due to a cached query plan, which is appropriate for some version of parameters passed to the query, but not for others (aka Parameter Sniffing).

This is a common occurance, and is often made worse by out of date statistics and/or badly fragmented indexes.

First step: ensure you have rebuilt all your indexes and that statistics on non-indexed columns are up to date. (Also, make sure your client has a regularly scheduled index maintenance job)

exec sp_msforeachtable "DBCC DBREINDEX('?')"
go

exec sp_msforeachtable "UPDATE STATISTICS ? WITH FULLSCAN, COLUMNS"
go

This is the canonical reference: Slow in the Application, Fast in SSMS?

If the problem still exists after rebuilding indexes and updating statistics, then you have a few options:

  1. Use dynamic SQL (but read this first: The Curse and Blessings of
    Dynamic SQL    )

  2. Use OPTIMIZE FOR

  3. Use WITH(RECOMPILE)

More Related Contents:

  1. Convert text to system date format [duplicate]
  2. How to get a particular preceding value of a column in SQL [closed]
  3. How to select just the row in the second table?
  4. SQL update from one Table to another based on a ID match
  5. What is the meaning of the prefix N in T-SQL statements and when should I use it?
  6. SQL JOIN vs IN performance?
  7. Can a foreign key be NULL and/or duplicate?
  8. How to generate a range of dates in SQL Server
  9. SQL RANK() versus ROW_NUMBER()
  10. Generate SQL Create Scripts for existing tables with Query
  11. Check if a row exists, otherwise insert
  12. Export SQL query data to Excel
  13. Insert Picture into SQL Server 2005 Image Field using only SQL
  14. sql server select first row from a group
  15. How to check if a string is a uniqueidentifier?
  16. How do I remove extended ASCII characters from a string in T-SQL?
  17. Why is SQL server throwing this error: Cannot insert the value NULL into column ‘id’?
  18. How can I see all the “special” characters permissible in a varchar or char field in SQL Server? [closed]
  19. Get unique values using STRING_AGG in SQL Server
  20. The most elegant way to generate permutations in SQL server
  21. What’s the difference between a Table Scan and a Clustered Index Scan?
  22. How do I use alias in where clause? [duplicate]
  23. Backup a single table with its data from a database in sql server 2008
  24. The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions
  25. COALESCE Function in TSQL
  26. What is the meaning of SELECT … FOR XML PATH(‘ ‘),1,1)?
  27. Try_Convert for SQL Server 2008 R2
  28. SQL Server variable scope in a stored procedure
  29. SQL Sort Order with Null values last
  30. When to use EXCEPT as opposed to NOT EXISTS in Transact SQL?

Leave a Comment