Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Deadlock in Reporting Database


SQL Deadlock in Reporting Database

Author
Message
rajeev.suryanarayan
rajeev.suryanarayan
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 68
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.
Attachments
1_1.zip (14 views, 5.00 KB)
Michael Earl-395764
Michael Earl-395764
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2707 Visits: 23078
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.
Grant Fritchey
Grant Fritchey
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17607 Visits: 32267
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

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Michael Earl-395764
Michael Earl-395764
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2707 Visits: 23078
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.
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47287 Visits: 44392
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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


rajeev.suryanarayan
rajeev.suryanarayan
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 68
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?
rajeev.suryanarayan
rajeev.suryanarayan
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 68
Thanks guys..really appreciate you helping me out here but could you dig deeper into your expertise and forsee something Smile 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.
Grant Fritchey
Grant Fritchey
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17607 Visits: 32267
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

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47287 Visits: 44392
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 Smile 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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


rajeev.suryanarayan
rajeev.suryanarayan
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 68
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search