October 9, 2008 at 5:29 am
Hi,
I am facing a deadlock issue in SQL server 2005 when I run a report from Business Objects. Could some one provide me with some options that I could set at the Database level to avoid such a thing from happening?
The SQL Database does not have any other job or event running that Inserts or Updates. Only concurrent querying by different users, it’s an outright reporting server. I am surprised that Select queries causes deadlocks
I used the profiler but the deadlock event did not return a deadlock graph. The XML output pointed to a “Select query” (attaching the same)
There are some reports that incorporate multiple joins with different dimension tables and contribute to huge data fetch. I suspect that the row level share locks are getting escalated to table level during the tenure of the query run. Hence the deadlock issue.
I do not really want to add hint in the queries instead would like to manage it at the Database level. I wasn’t sure about the new isolation level – snapshot. Anything else like memory, buffer size, maxlocks etc that I can set?
There are indices in place (clustered for primary and non-clustered on foreign keys and other reporting fields) as well.
Pointers would be really helpful.
October 9, 2008 at 6:06 am
If you only have shared locks, you cannot generate a deadlock. The only possibility would be if there ended up some kind of deadlock within the system tables while trying to asquire locks. I have never seen this and it would likely have to be a bug in the database engine.
I would suspect something else is happening in the database that you are unaware of. There is some updating or inserting also happening. You may want to start by checking the transaction log.
Lock escalation does not change the lock type. So, if you have a regular select statement, even escalating to a table lock will still be a shared lock.
October 9, 2008 at 7:00 am
I agree with the last post. You must be doing inserts, updates, deletes, somewhere in order to get deadlocks.
Are you possibly creating global temporary tables as part of the querying and then inserting, updating data there? That could lead to deadlocks.
However, you could try, since this is a reporting database, setting the database to be read only. Then you shouldn't see any kind of locking at all, including shared locks. You should also get a performance increase because of that.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 9, 2008 at 7:07 am
Good idea Grant.
If it is the global temp tables, the locking behavior will still continue.
If it isn't, you will quickly find out what process is updating data - because it will error.
October 9, 2008 at 11:21 am
You can enable traceflag 1222 and SQL will write the deadlock graph into the error log. That will help you locate the cause of the deadlock.
The only way to get a deadlock with select statements is if one or more is running with either the xlock or updlock hints, forcing an exclusive lock instead of shared as is default.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 9, 2008 at 12:36 pm
How about slowly running queries? Would there be any problems caused due to memory paging or something of that sort? I did do the trace using profiler and have attached a zip file containing the output (XDL file) in my main problem dialog.
To explain a little more, we have a database A where daily updates and insertion of records happen. Then in the evening, the database is taken offline and copied over as Database B. This particular database does not have any activity happening in it other than BO reports.
Wait a minute, does having the logical name of Database B same as the Database A create an issue?
October 9, 2008 at 12:38 pm
Thanks guys..really appreciate you helping me out here but could you dig deeper into your expertise and forsee something 🙂 Pleasssee...
I am not using any temp table or anything of that sort. Its just a plain SQL SELECT query that few outer joins with tables from another database.
October 9, 2008 at 12:41 pm
Whoop!
Hold the phone. Did you say it goes against another database? Is that other database only performing reads or does it also have inserts & updates? And now that we're on the topic, do you know that this query is the cause of the deadlocks or is a deadlock victim? If it's a victim, then it's some other query, probably in the other database, that's causing the problem.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 9, 2008 at 1:00 pm
rajeev.suryanarayan (10/9/2008)
Thanks guys..really appreciate you helping me out here but could you dig deeper into your expertise and forsee something 🙂 Pleasssee...
See my post above, enable the traceflag and get the deadlock graph. That will show exactly what queries were involved in the deadlock. With the graph we can help you find the cause of the deadlock and maybe even fix the root cause.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 9, 2008 at 1:04 pm
The select query is the victim!
Ok..let me put it down much more clearer of what I am dealing with. I know am dealing with a screwed set of databases but i didnt have such problems earlier.
I have Database A- This has 5-6 tables that contain raw data from text files. These tables did not have any kind of keys or constraints set. Its just got identity fields and data is refreshed in these tables on a daily basis.
I have a second database B- This has tables designed with proper keys and indices. There are about 8 tables in it.
Now- There is a requirement where you move fresh data from Database A to Database B on a daily basis. SAy Table 1 in Database A has the core data and it needs to join with Table 2, 3 and 4 from the same Database A to derive 6-7 fields (look up tables).
There is also a need to look up few tables in Database B to derive 3-4 more fields. So ultimately its a select query from table 1 of Database A, having joins with Table 2,3 and 4 from same DB and few more joins with the tables in Database B.
Output of this Select query is fed into another table in Database A itself before moving the same to Database B (as next step)
The select query that loads the intermediate table is now failing as the victim in the deadlock.
I also face a deadlock issue in a third database C. This database C, is nothing but a copy of Database B. There are no processes running on Database C other than business object reports.
Database A and Database B would have insert and update statements running in parallel.
If the deadlock was caused by an update or delete, shouldnt it be caught in the deadlock graph from the trace component of lock using profiler?
I suspect I am missing here.
October 9, 2008 at 1:07 pm
Gail, I have already done that. I have attached the Trace graph in my first post itself (as zip file). Could you look into it? All the object IDs in that XDL file pointed to the select query. Also it did not build the graph citing some weird error, I had to open it in text mode and view the xml after that.
October 9, 2008 at 1:10 pm
I also tried viewing the graph from the profiler window, but of no avail! How do I know which all processes (as in user processes) that are running at the same time, Can I find that out?
There are hardly any SPs and absolutely no triggers written
October 9, 2008 at 1:20 pm
My thoughts and plan of action:-
1. First lets not call it Deadlock.
2. As GilaMonster said run the trace flag.
3. Run your business object report and at the same time run
select * from master..sysprocesses where spid = ? and .... blah blah blah
capture your report's spid.
4. What does blocked by say ?
5. Is it blocked by iteself ?
6. Do you see CxPacket waittype, which means rest of the threads are waiting on some of its own thread ?
In that case, try running your report with MAXDOP( 1 ) , telling it to use only 1 processor.
7. After doing all this did you find deadlock info in Errorlog ? If you did fix it by re-writing your query.
My 2 cents 🙂
October 9, 2008 at 1:29 pm
rajeev.suryanarayan (10/9/2008)
Gail, I have already done that. I have attached the Trace graph in my first post itself (as zip file).
No, that comes from profiler. I looked through and there's very little useful in there. Please can you enable traceflag 1222, get the deadlock graph that's written to the error log and post that. I want to see if that gives more into. It should.
Do you have any linked servers that reference the server that's having the deadlocks?
Does this return anything?
SELECT * from sys.dm_exec_sql_text(0x02000000b812c30a4c92fa0d08189f8375d9569df6683871)
Whatever SPID 60 was running had paralleled over 16 processors. It definitely wasn't a select, as there's a reference to the amount of log space used, and the log is only used for data modifications.
There's no reference to anything other than SPID 60, which there should be.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 9, 2008 at 8:32 pm
Oh ok..Let me put the trace on and monitor as you guys have pointed out. I will give you the results after I am done today. Thanks for keepin my hopes alive 🙂
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply