2

This is my query

 SELECT TOP 3 guid FROM eventlog 
    WHERE (guid > 2291399 AND batch_uid = -1) ORDER BY date_created ASC

I'm running this qquery inside a stored procedure cursor about 25 times. Which causes the SP to run for 10 seconds plus.

Can someone advise me where I could optimize my query ?

I have tried the following indexes

CREATE INDEX eventlog_003 ON eventlog (batch_uid,date_created,guid);
CREATE INDEX eventlog_004 ON eventlog (date_created,guid);
CREATE INDEX eventlog_005 ON eventlog (guid,batch_uid,date_created ASC);
CREATE INDEX eventlog_006 ON eventlog (batch_uid,date_created ASC,guid);

Heres some stats.

enter image description here

Jules
  • 7,568
  • 14
  • 102
  • 186
  • 1
    What is the logic you are repeating 25 times? – Martin Smith Aug 31 '11 at 10:06
  • 1
    Could you attach an actual execution plan? (i.e. one including execution statistics instead of just estimates). – Justin Aug 31 '11 at 10:10
  • @Justin, the execution plan is the first graphic above. – Jules Aug 31 '11 at 10:19
  • @Jules - Which of those indexes is it actually using? The one on `date_created,guid` with a bookmark lookup to get `batch_uid`? Also I agree with Justin can you see what the "Actual number of rows" is vs "Estimated Number of Rows" – Martin Smith Aug 31 '11 at 10:21
  • @Jules The tool tip shown for the index scan only includes the "Estimated" values of what SQL Server believes will happen, however a relatively common source of performance problems is SQL Server getting its estimates wrong and choosing an inappropriate plan as a result. An **actual** execution plan also contains the corresponding "Actual" values (which I suspect will indicate what the problem is) – Justin Aug 31 '11 at 10:23
  • @Justin - I agree. Given the times in the OP I'm pretty sure that the bookmark lookup must be happening many more than 3 times before it gets 3 rows that satisfy the residual predicate. – Martin Smith Aug 31 '11 at 10:25
  • Jules, to include an actual execution plan you should tick the "Include actual execution plan" menu item in the "Query" menu and re-run your query - you should also see the "Results" tab appear.(indicating that the query actually ran and returned results) – Justin Aug 31 '11 at 10:28
  • @Jules - Has it started using your index on `batch_uid, date_created, guid` now? – Martin Smith Aug 31 '11 at 10:29
  • @Jules - Well if you've update the stats and that fixed the problem there's not much point now but for future reference `CTRL+M` to turn the option on in SSMS then execute the query. – Martin Smith Aug 31 '11 at 10:30
  • I'm using sql server 2005, don't have actual – Jules Aug 31 '11 at 10:35
  • 1
    Yes SQL Server 2005 does! You can also turn on the option via the "Query" menu. – Martin Smith Aug 31 '11 at 10:36

1 Answers1

0

It looks like most of the time is spent doing that index scan. I found some info in this question. In short, it is better to get the database to try and do an index seek rather than a scan (a scan requiring checking of fields in the table that aren't in the index). It looks like one way you could reduce the time taken would be to create an ascending index on guid, batch_uid and date_created.

Community
  • 1
  • 1
mdm
  • 12,480
  • 5
  • 34
  • 53
  • 1
    It depends - SQL Server believes that it will only be scanning 4 rows in which case a table /index scan is probably the best approach. My guess is instead that the estimate is off, in which case adding an index won't be enough to persuade SQL server to use an index lookup. – Justin Aug 31 '11 at 10:13