June 9, 2011 at 7:02 pm
Yes, the number of executions is a huge problem...
In fact, we're averaging 600 sessions at any given time, and I see login peaks of 2300-4400 logins per second. We're trying to work with the vendor on fixing this aspect. The activity shows a 1) login, 2) sp_reset_connection, 3) logout, so it looks like connection pooling is being used, from what I can tell.
It's actually the core problem, but since we cannot address that directly, or at the moment, we're trying to work on what we can, and this high-CPU, high-Read proc looked like a good candidate.
June 9, 2011 at 8:25 pm
One thing you could do to make the proc less of a problem is to have it randomly exit most of the time. Since it is just the same delete over and over, running the delete a few times per second, instead of 90 would reduce the workload.
-- Generate random number between 0 and 99 and exit if value >= 5 (95% of the time)
-- Adjust percentage of time is exits as needed.
if convert(bigint,convert(varbinary(7),newid()))%100 >= 5 begin return end
DELETE FROM RainbowQueue WHERE ( RecordUpdated < DateAdd(minute, -5, GETUTCDATE()) )
June 10, 2011 at 7:38 am
Great idea, Michael...Thanks!
June 10, 2011 at 7:48 am
mtillman-921105 (6/9/2011)
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.
PK and clustered index aren't the same thing.
Yes, per relational theory, rows have no order. However, in order to be stored in a binary computer's hardware, they have to have a physical order at the hardware level. That's a fact of the physical universe, and one of the places where math (relational theory) and reality (bytes stored electronically) diverge.
A clustered index, properly defined, makes that physical storage work better with the CPU and the SQL Server engines. It's not mathematically perfect, but neither is any other part of reality. (There was actually an interesting discussion on that just this week on a different part of this site.)
Interestingly, keys are part of relational theory, while indexes are part of database engineering. So, your tables may not have a primary key defined for the engine to use, but if they don't have a "primary key" implicit in their structure, they actually would violate relational theory. But that's another discussion entirely. Right now, I say we stick as closely as possible to fixing your performance problems.
A clustered index will help with that.
- 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 10, 2011 at 7:51 am
Michael Valentine Jones (6/9/2011)
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.
If it's got stats on the column, most executions will know there are no rows to affect, and will exit pretty quickly. That's the main reason an index sped it up. Doesn't make the actual deletes faster (makes them slower, actually, because they have to be removed from the heap AND from the index), but does make the initial probe to determine what needs to be done faster. A clustered index on the appropriate column would speed up both issues, since it wouldn't require a separate index structure with its own insert/update/delete hits.
- 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 10, 2011 at 9:17 am
GSquared (6/10/2011)
PK and clustered index aren't the same thing.
Yes, per relational theory, rows have no order. However, in order to be stored in a binary computer's hardware, they have to have a physical order at the hardware level. That's a fact of the physical universe, and one of the places where math (relational theory) and reality (bytes stored electronically) diverge.
A clustered index, properly defined, makes that physical storage work better with the CPU and the SQL Server engines. It's not mathematically perfect, but neither is any other part of reality. (There was actually an interesting discussion on that just this week on a different part of this site.)
Interestingly, keys are part of relational theory, while indexes are part of database engineering. So, your tables may not have a primary key defined for the engine to use, but if they don't have a "primary key" implicit in their structure, they actually would violate relational theory. But that's another discussion entirely. Right now, I say we stick as closely as possible to fixing your performance problems.
A clustered index will help with that.
Excellent explanation GSquared, now it all makes sense - thanks! I'm going to look for the thread you mentioned too by the way.
The greatest enemy of knowledge is not ignorance, it is the illusion of knowledge. - Stephen Hawking
June 10, 2011 at 10:58 am
A little late to the part and it sounds like you might have it coverd. But, do the statistis show the correct row count? I've seen them get out of wack for various reasons. One time in particular a table with about 25 rows was reported to have over 2 billion according to the statisicts. Which took a sub-second query up over 5 minutes just based on that issue. Just a thought.
June 10, 2011 at 11:02 am
Lamprey13 (6/10/2011)
A little late to the part and it sounds like you might have it coverd. But, do the statistis show the correct row count? I've seen them get out of wack for various reasons. One time in particular a table with about 25 rows was reported to have over 2 billion according to the statisicts. Which took a sub-second query up over 5 minutes just based on that issue. Just a thought.
Stats on an active table can get WAY out of wack sometimes. Usually if "auto update stats" is turned off, but sometimes (rarely) even with that on. Stat malformation mainly matters on big tables with low selectivity.
However, that will usually only affect the execution plan, and shouldn't heavily impact a simple delete statement. I'd expect it to mainly impact joins and sub-queries and such.
- 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 10, 2011 at 2:28 pm
Michael Valentine Jones (6/9/2011)
One thing you could do to make the proc less of a problem is to have it randomly exit most of the time. Since it is just the same delete over and over, running the delete a few times per second, instead of 90 would reduce the workload.
-- Generate random number between 0 and 99 and exit if value >= 5 (95% of the time)
-- Adjust percentage of time is exits as needed.
if convert(bigint,convert(varbinary(7),newid()))%100 >= 5 begin return end
DELETE FROM RainbowQueue WHERE ( RecordUpdated < DateAdd(minute, -5, GETUTCDATE()) )
Superb Idea. I like it a lot.
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 15, 2011 at 11:52 pm
dear sir
char(10) means it accepts upto 10 charecters but i am using 1.sai 2.siva it means 7 bytes,6 bytes memory will be wasted ok
if i am using varchar(10) it also accpts upto 10 charectes but i am using 1.sai 2.siva . now the memory will not be wasted bcz we are using varchar datatype.
to prove the above information is there any perticuler querry is available are not?
for example if you take oracle we can show the prove by using syntax: select dump(columname) from <tablename>
like this way any querry is there or not in sqlserver.
plz kindly solve it
June 16, 2011 at 5:13 am
nsaireddymca (6/15/2011)
dear sirchar(10) means it accepts upto 10 charecters but i am using 1.sai 2.siva it means 7 bytes,6 bytes memory will be wasted ok
if i am using varchar(10) it also accpts upto 10 charectes but i am using 1.sai 2.siva . now the memory will not be wasted bcz we are using varchar datatype.
to prove the above information is there any perticuler querry is available are not?
for example if you take oracle we can show the prove by using syntax: select dump(columname) from <tablename>
like this way any querry is there or not in sqlserver.
plz kindly solve it
please start a new thread.
June 16, 2011 at 5:20 am
can any one show the prrof what is exact difference between char and varchar by writins syntax in our sqlserver
June 16, 2011 at 5:32 am
nsaireddymca (6/16/2011)
can any one show the prrof what is exact difference between char and varchar by writins syntax in our sqlserver
PLEASE START A NEW THREAD.
Thanks in advance!
Viewing 13 posts - 31 through 42 (of 42 total)
You must be logged in to reply to this topic. Login to reply