June 9, 2011 at 10:50 am
opc.three (6/9/2011)
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.
This is the direction I was headed. I would go as far as to say that if it is due to fragmentation then change that NC to a CI on the PK. It will simplify future maintenance.
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:51 am
Something likelier perharps... how about locking mixed with waiting on log autogrow once in a while?
June 9, 2011 at 10:51 am
Thanks for all of the posts, guys!
Yes, there are a ton of inserts and deletes.
And while it deletes records over five mins old, due to poor design, it runs Waaaay too often: 5400 times a minute. Yeah, I know: That's the problem. What I'm trying to find out in this exercise is, why did an index help it?
June 9, 2011 at 10:54 am
Runs 5400 times a minute to delete records older than 5 minutes?????
Index will create a stat and "may" help this out. On a table with 1 page I just don't see how it can really help.
June 9, 2011 at 10:55 am
Ninja's, some data there:
No long-term locking affecting it (lowest I can set the threshold is one second). However, running that often, every bit is a problem!
However, would a block affect the CPU and Reads? We're filtering the trace on 1000 CPU+, and it's logging a bunch of reads (4k+). IE, if it was blocked, it wouldn't continue to accumlate CPU, would it? (That's gonna trash the thread, I bet)
June 9, 2011 at 10:55 am
The answer to that question is probably able to be seen in the query plan after the index create.
One possible explanation - fragged heap that just got a clean index created on the column used in your query. That index was not fragged and a perf gain was seen.
Share the new plan and we could look at that too.
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:58 am
Ninja's_RGR'us (6/9/2011)
How can you defrag a heap that has no required order?
Tangent to the OP but since you asked: ALTER TABLE...REBUILD or you can add a CI and then immediately drop it. The idea for a heap is just to gather all the pages back together in one physical place and get all the Row IDs reset so there are no more pointers.
The proper thing to do though is to add a CI and call it good 😉
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 9, 2011 at 10:58 am
Grubb (6/9/2011)
Ninja's, some data there:No long-term locking affecting it (lowest I can set the threshold is one second). However, running that often, every bit is a problem!
However, would a block affect the CPU and Reads? We're filtering the trace on 1000 CPU+, and it's logging a bunch of reads (4k+). IE, if it was blocked, it wouldn't continue to accumlate CPU, would it? (That's gonna trash the thread, I bet)
Send the new plan. I think I know the answer. I also speculate that the high cpu and reads will be popping back up in your trace before too long.
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 11:52 am
Scrap last comment, sorry. Please provide the second plan that's now occuring as well.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
June 9, 2011 at 12:04 pm
The reason I asked about it being a heap in the first place is because that exact behavior is one of the reasons Microsoft recommends against using heaps in SQL Server. You can end up with all kinds of "mystery performance issues" on them, because of the way they are stored and accessed.
Yes, the table is small enough to fit on a single page, but the odds of it doing so grow progressively lower as the data is changed. Lots of deletes per second is going to result in fragmentation, almost guaranteed. Check the actual physical allocation of the heap after it's been in use for a while in this manner.
The index will be faster because of stats, etc., but also because it probably does stay on one page by itself. If it's sequential, based on a time-stamp of some sort, it will be much more efficient at both storage and access.
So, from your initial description, my first thought was "heap table", because it's the most likely cause of this situation, and adding an index will usually help. A clustered index on the time-stamp column will almost certainly be even better, but it might be better by a small enough margin to not matter. Test it and see.
- 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 12:19 pm
Gsquared you are right on. Depending upon a lot of factors the deletes could have gotten significantly behind the inserts. All inserts always go at the end of heap and if there is no more room a new pages are allocated. I bet that "little" heap table is an allocation nightmare.
I would create a clustered index on date-time and be done with it. Without that, all the deletes are full scans anyway. Even on a small table you don't want full scans happening every second because something is going to be blocked.
The probability of survival is inversely proportional to the angle of arrival.
June 9, 2011 at 12:25 pm
Sorry for the delay, guys...I didn't abandon, just went to lunch!
Here's the "after" plan....
June 9, 2011 at 3:57 pm
GSquared (6/9/2011)
The reason I asked about it being a heap in the first place is because that exact behavior is one of the reasons Microsoft recommends against using heaps in SQL Server. You can end up with all kinds of "mystery performance issues" on them, because of the way they are stored and accessed.Yes, the table is small enough to fit on a single page, but the odds of it doing so grow progressively lower as the data is changed. Lots of deletes per second is going to result in fragmentation, almost guaranteed. Check the actual physical allocation of the heap after it's been in use for a while in this manner.
The index will be faster because of stats, etc., but also because it probably does stay on one page by itself. If it's sequential, based on a time-stamp of some sort, it will be much more efficient at both storage and access.
So, from your initial description, my first thought was "heap table", because it's the most likely cause of this situation, and adding an index will usually help. A clustered index on the time-stamp column will almost certainly be even better, but it might be better by a small enough margin to not matter. Test it and see.
Thanks for the interesting discussion everyone.
Most of the tables I work with have no PK (I primarily work in data warehouses). It's kind of a shame that a "heap" is such an issue since in set based theory, there is no order to the rows anyway, so why the requirement? I suppose it functions as some kind of convenient handle for each row for the engine, but if it was that important, you'd think it would be built in behind the scenes. GSquared, maybe you could explain further?
By the way, most of the tables I work with would have no direct benefit on a PK - there would be no joins on them anyway - we mainly use, eg., year, period, employee ID which is a natural key.
The greatest enemy of knowledge is not ignorance, it is the illusion of knowledge. - Stephen Hawking
June 9, 2011 at 5:18 pm
mtillman-921105 (6/9/2011)
Thanks for the interesting discussion everyone.Most of the tables I work with have no PK (I primarily work in data warehouses). It's kind of a shame that a "heap" is such an issue since in set based theory, there is no order to the rows anyway, so why the requirement? I suppose it functions as some kind of convenient handle for each row for the engine, but if it was that important, you'd think it would be built in behind the scenes. GSquared, maybe you could explain further?
By the way, most of the tables I work with would have no direct benefit on a PK - there would be no joins on them anyway - we mainly use, eg., year, period, employee ID which is a natural key.
It's not the PK that's important, but the clustered index. Clustered Index on the Year/Period/EmpID would be more beneficial probably, but that's the important component. The PK is just that, and roughly ignored by the optimizer unless it's in use.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
June 9, 2011 at 5:23 pm
I have seen a table that was 15 GB in size, even thought the rows were narrow, and it only had 200 rows in the table. It was an application with a high insert and delete rate, and that caused most pages in the heap to be empty.
Do yourself a favor and create a clustered index on column RecordUpdated.
Also, is there really a good reason to run this procedure 5400 times per minute? That's 90 times per second, and that alone could cause a serious performance problem.
Viewing 15 posts - 16 through 30 (of 42 total)
You must be logged in to reply to this topic. Login to reply