Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

SQL Deadlock in Reporting Database Expand / Collapse
Author
Message
Posted Thursday, October 9, 2008 5:29 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, February 13, 2013 7:03 AM
Points: 10, Visits: 67
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.



  Post Attachments 
1_1.zip (13 views, 5.61 KB)
Post #583210
Posted Thursday, October 9, 2008 6:06 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 10, 2010 5:07 AM
Points: 2,661, Visits: 23,078
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.
Post #583231
Posted Thursday, October 9, 2008 7:00 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:25 AM
Points: 13,872, Visits: 28,268
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
SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #583275
Posted Thursday, October 9, 2008 7:07 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 10, 2010 5:07 AM
Points: 2,661, Visits: 23,078
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.
Post #583283
Posted Thursday, October 9, 2008 11:21 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 7:26 AM
Points: 40,174, Visits: 36,572
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 2008, MVP
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

Post #583503
Posted Thursday, October 9, 2008 12:36 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, February 13, 2013 7:03 AM
Points: 10, Visits: 67
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?
Post #583547
Posted Thursday, October 9, 2008 12:38 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, February 13, 2013 7:03 AM
Points: 10, Visits: 67
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.
Post #583548
Posted Thursday, October 9, 2008 12:41 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:25 AM
Points: 13,872, Visits: 28,268
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
SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #583550
Posted Thursday, October 9, 2008 1:00 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 7:26 AM
Points: 40,174, Visits: 36,572
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 2008, MVP
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

Post #583563
Posted Thursday, October 9, 2008 1:04 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, February 13, 2013 7:03 AM
Points: 10, Visits: 67
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.



Post #583565
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse