SQL Server performance with Query Delete Top

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 2005 Forums

Transact-SQL (2005)

SQL Server performance with Query Delete Top

Author

Topic

rom84
Starting Member

4 Posts

Posted - 2009-07-06 : 22:19:39

Hi all,

I have a problem with my sql server 2005.
I have a table non-index with only one field with data type bigint.And I have a store procedure have query below :

Create table #tblPassportID(PassportID bigint)

Delete top(1) PassportID_Pool
Output deleted.PassportID
Into #tblPassportID

Select @PassportID = PassportID From #tblPassportID

The problem is when my table have 1.000.000 records, my server run with CPU about 4%.
But when my table have 100.000 records, my server run with CPU 100%.

I don't know why? Is my query have problem?

Please help me!
Thank you very much!

@Note: at that time, my server only run this sql server 2005 application and my database only run that query. My database in server have size about 30Gz

Infomation about my server :
Ram : 8Gz
Intel Xeon 2,4x2,4Gz
Window server 2003 service pack 2

asgast
Posting Yak Master

149 Posts

Posted - 2009-07-07 : 04:46:02

try looking into execution plan

rom84
Starting Member

4 Posts

Posted - 2009-07-07 : 05:18:43

Hi asgast,

The execution plan does not help anything in this case.
Because my problem only appear when my table have 100.000 record (CPU high load), but when it has 1.000.000 records, CPU server will be normal.In normal, delete in 100.000 record must be better than in 1.000.000 (Do you think so?)

asgast
Posting Yak Master

149 Posts

Posted - 2009-07-07 : 06:13:19

well i think so, I can think of some highly theoretical things that can be a reason for this, but I have never seen them in reality and i don't know how to replicate this problem and test it.

so my solution would be to start the basic routine

still are your execution plans identical?

then i would check:
delete works faster without output yes/no?
if yes i would try to rewrite it to run without output

you don't do anything else in the sp?

you can also hope, that someone with better knowlege than me will show up :)

I'd really like to see the solution for this problem :)

also is your table just a heap with no keys?

rom84
Starting Member

4 Posts

Posted - 2009-07-07 : 22:29:49

My store only have that code above and the table have no key.

Thank for your answer. I'll wait another solution

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources