CODEWITHSUNDEEP
sqlsql-server-2008
jarofclay
jarofclay

Reputation: 680

Filter value causes query to fail

We have an interesting table query (SQL Server 2008) that fails with a different field value. Has anyone seen this before?

This runs fine (500ms)

SELECT ROW_NUMBER() OVER (ORDER BY StatisticNo asc) AS RowId, Statistics.* 
FROM Statistics 
WHERE myear = 2010

This completes after a long time (>3 mins)

SELECT ROW_NUMBER() OVER (ORDER BY StatisticNo asc) AS RowId, Statistics.* 
FROM Statistics 
WHERE myear = 2011

The table contains data for both 2010 and 2011. Interestingly enough it runs fine as well for 2012 as a filter option which is currently an empty set. We checked for normalized problems and there is no missing or null data. The myear field is a required numeric.

There are no open or active locks on the table. It is a statistics table that is written to once a day.

Upvotes: 0

Views: 137

Answers (2)

Quassnoi
Quassnoi

Reputation: 425341

Could you please run this query:

SELECT  year, COUNT(*)
FROM    statistics
GROUP BY
        year

If you have few records in 2010 and 2012 but lots of records in 2011, then it would be perfectly normal for the query to run longer.

Upvotes: 2

Martin Smith
Martin Smith

Reputation: 453212

You probably have an open transaction locking a 2011 row. Try using the NOLOCK hint to confirm this.

If that returns results without being blocked then you can find the culprit spid by running again without the hint then looking in sys.dm_tran_locks whilst the blocking is occurring.

Upvotes: 2

Related Questions