MS SQL Deadlock

HaVoC

I have a problem where my report server seems to lock up the database and lags the other applications using it. The server is MS SQL 2000 and I have attached part of the process log to the post could someone please explain the log as I’m not 100 % on what it means.

The process 60 is blocking process 490 which in turn is blocking 3 other processes, anyone have any idea why this would be happening? So what it looks like to me is that the select from the report server has locked up the table am I right?

amen

HaVoC,

SQL Server deadlock issues can be tricky and hard to resolve. The file you have attached doesn't really tell you anything. What you need to do is turn on SQL profiler and add in Batch complete/Deadlock/Start filters and let the profiler run. I would suggest that you also let the profiler output save to disk with a new file created every 500MB. Then when the SQL Server performance drops have a look at the trace. Hopefully you will see the SP/SQL that is causing the deadlock and then you can troubleshoot.
Of course you could be having deadlocks due to bad db design and not just bad sql. Disk I/O, allocated memory etc can impact peformance. Presence of Indexs (clustered/non clustered), multi column indexes etc can also cause deadlocks depending on the actions on the table.
If you can can you tell me how big your db is, memory on the server, memory allocated to SQL Server, how much free disk space, which SP level is SQL Server 2000 on etc. Do you have locking hints? How often is the transaction log backed up ? Do the slowdowns occur around the time of transaction log backup ? Do you run daily/weekly maintenance jobs? Is the server dedicated to SQL Server or does it run other apps (if it does get a dedicated box for SQL Server.) Is it SQL Professional/Enterprise?
Has this just happened or has it only started?


How urgent is this? How much data do you process day?

Jagera

yeah, what amen said.. need a bit more info on the server/db/users & anything else.

I'm assuming it's a dedicated report server, am I right?

bonkey

I have a problem where my report server seems to lock up the database and lags the other applications using it.
...
The process 60 is blocking process 490 which in turn is blocking 3 other processes,

Just being picky, you mention deadlock in the thread title, but what you're describing sounds like blocking, not deadlocking. There's a difference.

The server is MS SQL 2000 and I have attached part of the process log to the post could someone please explain the log as I’m not 100 % on what it means.

The only really useful information you're getting there is that a SELECT statement is blocking an INSERT statement, as well as some info as to what is "generating" each of those statements.

anyone have any idea why this would be happening? So what it looks like to me is that the select from the report server has locked up the table am I right?

Looks exactly like what you're describing.

As to *why* its happening - like Amen says, there could be a number of reasons.

The simplest explanation is that the SELECT has put shared-level locks on the table which is targetted by the INSERT. Thus, until the SELECT finishes, the INSERT cannot proceed.

We can see that the INSERT is waiting for a TAB (table) resource. This suggests that there is a table-level lock from the INSERT statement which is preventing the INSERT from running (until said table-level lock is released)

The INSERT has placed an "INTENT EXCLUSIVE" lock (LCK_M_IX) on the table (or a portion thereof) to make sure that it is next in the queue, so to speak, which is why it in turn could be blocking additional processes. Once the SHARE locks go, the LCK_M_IX will be changed to an EXCLUSIVE lock (of appropriate granularity), the insert will proceed, the lock will be released, and everything will continue onwards.

(This is assuming I'm correct in thinking that what you're describing is blocking behaviour, not deadlocking).

Why the SELECT is blocking the INSERT is harder to explain, as there's tons of possibilities. In theory, MSSQL2000 should use row-level locking, and thus a SELECT should only lock the rows its selecting, which shouldn't interfere with an INSERT. In practice, there's a number of reasons this can fall apart. F'r example, if enough of the table is SELECTed, I believe MSSQL will "escalate" its locks for efficiency purpose, and may be locking pages and/or the entire table. There's a number of other reasons too - this is by no means the only one.

As for ways to correct it or work around it...it depends on what the cause for the blocking behaviour is - why row-level locking isn't working as one would like it to, and what can be done to get around it. You may be able to resolve it simply, you may not.

jc

HaVoC

I am but a humble code monkey, I don’t have access to all that information The database admin is based in a foreign country.

Reason I ask, is that I run the report server that locking up the table and when I restart Tomcat a few time it usually resolves the deadlock.

This means the problem is with the report, but I can’t find anything wrong with the code, SQL or connection pools I’m using

In answer to what i do know:
Yes the report is dedicated
Yes the SQL sever is dedicated
The db is 100gb
Running 3 applications on it & the reports as well as custom queries.

This locked out an application here on Wednesday, which lead the db admin to contact me and say what that the report server was servely affecting the Sql server due to the amount of connections it was using (I was using connection pools) turn them all off problem still remains. I have been scraping the logs ever sense, but the problem has been around for some time just brought to the attention of the db admin by the lockout.

HaVoC

Yep deadlock maybe the wrong therm.

When this blocking behaviour happens it slows down the other applications to a crawl and I haven't let it run out to see if the blocks are released as I can't really do that here.

But the lag we have been seeing before we knew about this , didnt go away quickly so i'd say the select doesnt finish anytime soon if at all. Also the db admin said he was seeing 100% cpu usage at times.

amen

does sound more like a blocking then locking problem
how you run the queries in Query Analyser with the Execution Plan and Show Server traces selected? this will show(probally) you the slow running parts of your query and why SQL server thinks they are slow. You could be querying on non indexed columns.
When were the indexes/tables in the database last updated? If an index is out of date then this could also impact peformance. What are the fill ratios like. Is the DBA a dedicated DBA?

Do you develop the Reports on a test server and if so how old is the data? Do you get the same problme on test.
Do the reports need to be run on an adhoc basis? If not why not schedule them to run at night and populate a table with the results and the let the users get the data from the results table. This might give you time to trouble shooting the locking.
If you need any SQL Server consultancy I'm sure there a few here that would help you :-)

bonkey

amen wrote:

If you need any SQL Server consultancy I'm sure there a few here that would help you :-)

Fly me in from Switzerland! G'wan...you know you want to

Seriously, though, with a DB that size, I reckon a lot of this will boil down to a design issue - INSERTing to large tables, which are also reported off at the same time....its just asking for trouble.

Civilian_Target

Take a closer look at that SELECT statement. How's it doing for things like outer joins? Run an access trace on it, you'll probably find that it's doing a large number of expensive table scans and therefore locking large chunks of your database for up to a second at a time. 90% of times, this can be fixed by the addition of a couple of indices, the other 10% of times its just bad SQL

amen

INSERTing to large tables, which are also reported off at the same time....its just asking for trouble

run the reports out of hours might also help

HaVoC any update? I'm interested in knowing how you got on

HaVoC

Sorry kind of forgot about this thread

The Database Guy came over fitted an other cpu (x4 now) and 1 gig of ram to the server.

Before he did the locking and blocking behaviour I had been seeing from the report server, had stopped and I haven’t seen it sense.

Thanks for all the advice guys optimised the queries that I was using a bit.

What is happen now is that on the server, statements are wait rather large amount of time to get at tables , causing applications to slow down. This doesnt happen often (twice so fare) and usually passes after 10-15 minutes. The database guy think it may have been jobs carried out by people in the head office and is looking into it.

Where we go from here I’m not really in the loop but, he had a few ideas of splitting the databases on the server over separate machines, 3 different ones on the one server at the moment. Also a live synced server for analyses and reporting, but this is probably not needed. But it could just be the jobs that where being run by the head office, it’s kind of out my hands now anyways.