SQL query is slow (for some parameter values)

Question

I have a SQL Server 2005 database with multiple tables. One of the tables is used to store timestamps and message counters for several devices and has the following columns:

CREATE TABLE [dbo].[Timestamps] (
[Id] [uniqueidentifier] NOT NULL,
[MessageCounter] [bigint] NULL,
[TimeReceived] [bigint] NULL,
[DeviceTime] [bigint] NULL,
[DeviceId] [int] NULL
)

Idis the only primary key (Guid.Comb), and I have indexes for the DeviceIdand columns MessageCounter.

What I want to do is find the last inserted row (the row with the largest MessageCounter) for a specific device.

The strange thing is that the request is for no. 4 (and all other devices except 1) returns almost instantly:

select top 1 * 
   from "Timestamps"
   where DeviceId = 4
   order by MessageCounter desc

but same request for device no. 1 runs forever:

select top 1 * 
   from "Timestamps"
   where DeviceId = 1 /* this is the only line changed */
   order by MessageCounter desc

, 1 , 4:

select count(*) from "Timestamps" where DeviceId = 4
(returns 1,839,210)

select count(*) from "Timestamps" where DeviceId = 1
(returns 323,276).

- , ?

[]

, 1 ( ) :

4 () 1 () http://img295.imageshack.us/img295/5784/execplans.png

, :

Device 4 Actual Number of Rows: 1

Device 1 Actual Number of Rows: approx. 6,500,000

6 500 000 - , select count(*) 300 000 1!

Answer 1

(DeviceId, MessageCounter DESC).

:

select * 
   from "Timestamps"
   where DeviceId = 1
   and MessageCounter = (SELECT MAX(MessageCounter) FROM "Timestamps" WHERE DeviceID = 1)

: , DeviceId = 1 , DeviceId = 4. , , , .

Answer 2

, ? :

UPDATE STATISTICS dbo.Timestamps

? sniffing?

Answer 3

, , , MessageCounter , 6 500 000, , DeviceId=4, DeviceId

, DeviceId=4 , .

DeviceId, MessageCounter . DeviceId=4 , ? , DeviceId = 4 , .

Guid.Comb ? , DeviceId, MessageCounter , .

Answer 4

, , .

select DeviceId, max(MessageCounter) from "Timestamps" group by DeviceId

, MessageCounter 2 4 . MessageCounter - .

SQL- :

MessageCounter . .

2-4 , MessageCounter 2-4. 1 6 , 1.

deviceid custered. 323k. .

, , MessageCounter ( ).

Answer 5

, - , SQL Server , , . . http://www.sqlshare.com/solve-parameter-sniffing-by-using-local-variables_531.aspx

, , . Query Analyzer - "". , ...

Answer 6

SQL Server ,

select top 1 * 
   from "Timestamps"
   where DeviceId = 4
   order by MessageCounter desc

NHibernate ? (where deviceid = @deviceid - )??

: SQL Server DeviceId = 4, , , DeviceId = 1 - .

? DeviceId = 1, DeviceId = 4 - ?