June 9, 2011 at 9:23 am
A single 'maintenance' proc is called many times a minute. It has a simple delete that deletes rows over 5 minutes old. For reasons I still can't figure out, it has a high CPU and Reads footprint. The crux is that it only has between 0-250 rows in it at any time (because it is called so frequently).
The table is only 4K...Fits on one page, right?
I argued against the index, because with that small of a table, I knew it wouldn't be used (at least, if the command was a SELECT...This was a Delete, remember).
However, once in place, before-and-after traces of heavy-hitting CPU queries show that the proc has dropped off the list, indicating that the index had great success.
Can anyone offer some insight? Is it because the table may not be in memory, so the index helps, or forces it in memory?
Just when I thought I was getting a handle on what I was doing... 🙂
June 9, 2011 at 9:28 am
Is the table a heap?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 9, 2011 at 9:35 am
Yes, it is a heap...
June 9, 2011 at 9:49 am
Can you post the table definition and the query that's doing the delete? A copy of the actual execution plan may also be handy.
June 9, 2011 at 10:16 am
Guessing here - the maint proc deletes from the table based on date. You created the index on that date field and now all is kosher.
Again, those are guesses since we don't know the execution plan prior to the index nor the table structure.
Indexes are not ignored on small tables - not all the time. I have seen many small tables benefit greatly from appropriate indexes. It depends on your queries. Your delete and selects based on the date range will benefit from that index likely due in part to the sort
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
June 9, 2011 at 10:18 am
Table:
CREATE TABLE [dbo].[RainbowQueue](
[RainbowType] [varchar](50) NOT NULL,
[RainbowKey] [varchar](255) NOT NULL,
[WorkDay] [datetime] NOT NULL,
[UpdatedRID] [bigint] NULL,
[SessionId] [int] NULL,
[BranchId] [varchar](20) NOT NULL,
[RecordUpdated] [datetime] NULL,
[RecordUpdatedBy] [varchar](20) NULL,
[RecordUpdateId] [timestamp] NULL,
CONSTRAINT [PK_RainbowQueue] PRIMARY KEY NONCLUSTERED
(
[RainbowType] ASC,
[RainbowKey] ASC,
[WorkDay] ASC,
[BranchId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Query:
DELETE FROM RainbowQueue WHERE ( RecordUpdated < DateAdd(minute, -5, GETUTCDATE()) )
Plan:
(See Attachment)
June 9, 2011 at 10:20 am
The premise being, indexes ignore when you only have a few rows, and it all fits on a page.
Even if it was ignored...such a small number of rows... :blink:
June 9, 2011 at 10:22 am
Grubb (6/9/2011)
Table:
CREATE TABLE [dbo].[RainbowQueue](
[RainbowType] [varchar](50) NOT NULL,
[RainbowKey] [varchar](255) NOT NULL,
[WorkDay] [datetime] NOT NULL,
[UpdatedRID] [bigint] NULL,
[SessionId] [int] NULL,
[BranchId] [varchar](20) NOT NULL,
[RecordUpdated] [datetime] NULL,
[RecordUpdatedBy] [varchar](20) NULL,
[RecordUpdateId] [timestamp] NULL,
CONSTRAINT [PK_RainbowQueue] PRIMARY KEY NONCLUSTERED
(
[RainbowType] ASC,
[RainbowKey] ASC,
[WorkDay] ASC,
[BranchId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Query:
DELETE FROM RainbowQueue WHERE ( RecordUpdated < DateAdd(minute, -5, GETUTCDATE()) )Plan:
(See Attachment)
Is this the table structure and query plan prior to the index? Or is that NC on the PK the one that was created?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
June 9, 2011 at 10:29 am
This was Prior....the PK is non-clustered (I'm guessing because this database (third-party) was upgraded through the years <shrug> ).
The execution plan is Prior as well.
June 9, 2011 at 10:30 am
Please post the actual execution plan, we can't do much with the jpeg.
June 9, 2011 at 10:35 am
Ah, ok! Here it is...
June 9, 2011 at 10:42 am
This is a fairly straight forward query so it's not getting held up on a joined table which was my first guess. And it's not doing any functions on the table that may be eating up the time. If there were foreign keys the checks on those should show up in the plan, at least they did in my testing. With the run you got the plan with did you see a high number of reads and CPU? I'm wondering if it was just the first run that hit that and now that the table is small that it's not a problem anymore.
June 9, 2011 at 10:45 am
This table has a high volume of inserts as well as the deletes?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
June 9, 2011 at 10:46 am
Since it is a heap, and you were deleting from it every 5 minutes (and I assume something else was inserting into it just as often) it could have been horribly fragmented over time...that said, the table is so small I would not expect it to be a problem. Were any of the inerts or deletes concurrent? Do you have an environment with the table before the index was added where you can check the fragmentation? It would be interesting to see if you de-fragged the heap (opinions vary on the best way) if that would improve performance for the delete.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 9, 2011 at 10:49 am
How can you defrag a heap that has no required order?
Viewing 15 posts - 1 through 15 (of 42 total)
You must be logged in to reply to this topic. Login to reply