Query cost / plan question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Query cost / plan question

Author

Topic

aiken
Aged Yak Warrior

525 Posts

Posted - 2002-08-27 : 13:45:19

Ok, so I've got a table that logs events. Something like this:

CREATE TABLE events (
  [datetime] smalldatetime DEFAULT(getdate()),
  i_users_source int NOT NULL,
  i_users_dest int NOT NULL
  )

Of course, those pile up. So I've got a job that runs a SP to delete old events:

CREATE PROCEDURE p_DeleteEvents AS
set nocount on
delete from events where [datetime]<dateadd(hour,(-24*30),getdate())

...I noticed that this was running slowly (it runs every four hours, and it was taking about 8 minutes to run). Sure enough, no index on [datetime] (and don't lets get started on naming the column that; it wasn't my choice). I looked at the execution plan in query analyzer and found that its cost was 149.

So I go and add the nonclustered index on [datetime], and the query is much faster; about 25 seconds every four hours. Much better. However, query analyzer now shows the cost as being 317.

Looking through the execution plan, the main cost comes from two different sorts, which seem to be happening on the same data (apparently the entire table is being handled by a "Table spool / eager spool", and then the 1,100,000 or so rows are being sorted).

So I'm confused on a couple of points:

- What's up with the "cost"? I thought that was at least a ballpark estimation of efficiency and therefore execution time/db load.

- Why is the entire table being spooled and sorted twice, apparently in parallel? I'm attaching the showplan_all at the end here in case it sheds some light. I should note that the clustered PK on this table is on i_users_dest,datetime,i_users_source (reflecting the most common lookup), idx_events_date is only on datetime, and that the index idx_events_source_date is on i_users_source,datetime.

Thanks
-b

delete from events where [datetime]<dateadd(hour,(-30*24),getdate())
  |--Sequence
       |--Index Delete(OBJECT:([db].[dbo].[events].[idx_events_date]))
       |    |--Sort(ORDER BY:([events].[datetime] DESC, [events].[i_users_dest] ASC, [events].[i_users_source] ASC, [events].[rowguid] ASC, [Bmk1000] DESC))
       |         |--Table Spool
       |              |--Clustered Index Delete(OBJECT:([db].[dbo].[events].[PK_events]))
       |                   |--Top(ROWCOUNT est 0)
       |                        |--Nested Loops(Inner Join, OUTER REFERENCES:([Expr1006], [Expr1007], [Expr1008]))
       |                             |--Compute Scalar(DEFINE:([Expr1006]=NULL, [Expr1007]=Convert(dateadd(hour, -720, getdate()))+'Jan  1 1900 12:01AM', [Expr1008]=If (Convert(dateadd(hour, -720, getdate()))+'Jan  1 1900 12:01AM'=NULL) then 0 else 10))
       |                             |    |--Constant Scan
       |                             |--Index Seek(OBJECT:([db].[dbo].[events].[idx_events_date]), SEEK:([events].[datetime] < [Expr1007] AND [events].[datetime] > [Expr1006]),  WHERE:(Convert([events].[datetime])<dateadd(hour, -720, getdate())) ORDERED FORWARD)
       |--Index Delete(OBJECT:([db].[dbo].[events].[idx_events_source_date]))
            |--Sort(ORDER BY:([events].[i_users_source] ASC, [events].[datetime] ASC, [events].[i_users_dest] ASC, [events].[rowguid] ASC, [Bmk1000] DESC))
                 |--Table Spool

Edited by - aiken on 08/27/2002 13:47:56

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources