Delete Statments running slowly when called from .NET Application

Kai

Hi,

Last friday we doubled the amount of ram on one of our servers from 2 Gig to 4 Gig. Almost immediately delete statements coming from one of our .net services slowed down dramatically to the point where they stopped running. The queries being ran were quite small and ive tested them in QA and they run in a few seconds but from the .net service it runs very slowly and i can see Blocks in EM on the tables its deleting rows from.

Anyone have any ideas ? Nothing else has changed, we were having some resource issuse with the server unable to acquire locks due to a lack of resources. Thats why we upgraded the Ram but it appears to have made things a lot worse. Select Statements seem to run fine.

This may not be a SQL Server only issue but something more subtle. Has anyone seen something similar ?

OfflerCrocGod

Did it return to normal when you removed the RAM?

ressem

Would that be SQL server 2000? And if so, have you got the latest service pack installed?

What sort of Xeon/Opteron/other has the server?

bonkey

Kai wrote:

Almost immediately delete statements coming from one of our .net services slowed down dramatically to the point where they stopped running. The queries being ran were quite small and ive tested them in QA and they run in a few seconds but from the .net service it runs very slowly and i can see Blocks in EM on the tables its deleting rows from.

It sounds to me like the .NET service is doing a hell of a lot more than just deleting the records.

*How* do you do the delete through .NET?
Have you traced the exact statements being sent (using SQL Trace) from .NET to make sure

we were having some resource issuse with the server unable to acquire locks due to a lack of resources. Thats why we upgraded the Ram but it appears to have made things a lot worse.

Odd. Sounds like you've other problems as well then. Have you run a consistency check on your database? Checked its resource configuration?

This may not be a SQL Server only issue but something more subtle. Has anyone seen something similar ?

Yes, but only when a database was corrupted somehow, or when the data / data-files were fragmented to hell and back.

NutJob

Theres something else at play here other than the ram.

Get a sql trace.

Can you run with remote debugging and walk through the code?

if its a stored proc can you debug that two.

This looks more like a bug hunt than a ram problem.

amen

What version of MS SQL are running and which service pack?

Do you have a multi processor server? If so and you have SP4 you may need the SP4 hotfix

If I remember correctly MS SQL 2000 needs to be told to address 4GBs of memory. I think EM incorrectly reports that you are using 4GB when you aren't. You need to use Perfomance counter or maybe the registry.
Either way its documented on MS web site.

Have you checked your SQL Event Logs? NT Event Logs?

Have you run a SQL profiler to catch the sql passed down from your .Net code.

acquire locks due to a lack of resources

You probably have some SQL somewhere that is grabbing all the resources. Profiler should help you sort this.
Out if interest how big is the database, how big is the bigest able, are the indexs current?
Have the SPs been recompiled to match the latest indexes?
How often does those the recompile happend?
Are there any lock hints?
How much data an hour do you process?
In the SQL Trace and Deadlock or rollbacks?
Do you use Explicit Transactions?
Are there any DBCC errors in SQL Logs?
Those the problem occur on all deletes or only some? Are there other jobs running at the same time accessing the same data?

I would be interested in the solution to this issue.

amen

excuse my spelling above. Don't know what I was typing