October 1, 2015 at 11:50 am
Never tried this, but can you load two different trace files into a single instance of Profiler to correlate actions captured by different traces?
October 1, 2015 at 11:54 am
GilaMonster (10/1/2015)
You've either got a filter in the trace definition or the deadlock didn't occur.
Thanks, Gail. Trapping only the Deadlock Graph with no filters worked.
October 1, 2015 at 11:59 am
Lynn Pettis (10/1/2015)
Never tried this, but can you load two different trace files into a single instance of Profiler to correlate actions captured by different traces?
You can open multiple files and then tile them vertically/horizontally depending on your preference. It's a little clunky, but it works.
Cheers!
October 1, 2015 at 12:10 pm
May be totally unrelated, but have seen indexed views on two tables cause deadlock on queries that only reference one of the tables - similar to what you described.
October 1, 2015 at 12:32 pm
prvmine (10/1/2015)
May be totally unrelated, but have seen indexed views on two tables cause deadlock on queries that only reference one of the tables - similar to what you described.
We aren't that sophisticated, no indexed views in our database.
October 1, 2015 at 1:16 pm
Lynn Pettis (10/1/2015)
GilaMonster (10/1/2015)
You've either got a filter in the trace definition or the deadlock didn't occur.Only filter used was to the database and user involved (all connections use the same user).
And there's your problem.
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 1, 2015 at 2:02 pm
Might a job have run and hit that db but using a different user?
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
October 1, 2015 at 2:14 pm
GilaMonster (10/1/2015)
Lynn Pettis (10/1/2015)
GilaMonster (10/1/2015)
You've either got a filter in the trace definition or the deadlock didn't occur.Only filter used was to the database and user involved (all connections use the same user).
And there's your problem.
Yes, I already thanked you for helping me there. 😀
The 2 x 4 was there quite quickly after your first post.
October 1, 2015 at 2:15 pm
ScottPletcher (10/1/2015)
Might a job have run and hit that db but using a different user?
No jobs are active on the test server.
October 1, 2015 at 3:26 pm
Lynn Pettis (10/1/2015)
Anyone have any idea why Profiler wouldn't capture the deadlock graph even when it was the only thing being captured?
The beauty of filters with profiler. You already found it, but I had to point it out. Not a profiler fan especially with XE available.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
October 1, 2015 at 3:30 pm
To recap:
1. Profiler filter issue resolved. You see the deadlocks there now.
2. No indexed views
3. Tuning the code is far from feasible currently.
4. Tracking down how the app accesses the data currently.
Does that about sum it up?
Even without indexed views in the database, are they by chance any updates being performed to tables via a view despite the apparent lack of FKs where the view would involve both tables?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
October 1, 2015 at 11:28 pm
SQLRNNR (10/1/2015)
To recap:1. Profiler filter issue resolved. You see the deadlocks there now.
2. No indexed views
3. Tuning the code is far from feasible currently.
4. Tracking down how the app accesses the data currently.
Does that about sum it up?
Even without indexed views in the database, are they by chance any updates being performed to tables via a view despite the apparent lack of FKs where the view would involve both tables?
Yes, about sums it up. Part of it looks Ajaxy, but I keep getting told that isn't possible. Maybe not, but I'm not a web app developer and I have to call things as I see them. Prove me wrong, that's what I have to do when people tell me the problem is in the database.
We are doing this in a test environment using test automation to run the application and create/edit reports. The Selenium based test suite is able to simulate 18 users working simultaneously. The deadlocks actually start to appear when 6 users are running at a time. I'd have to review all the views but most of the ones against the report tables are not updateable views. The only views that I know that are updateable are the ones written over the one lookup table to rule them all. The application does not update lookup tables. That is done by a DBA or Sys Admin with specific scripts.
This would be a lot easier to isolate if there was a data access layer instead of having the code embedded in the application, and much of it built dynamically. This is what happens when there is no database developer/DBA/Database Architect involved in the building of an application and the developers view the database as nothing more than a data store to dump their data. Also doesn't help if you are writing the application with rockets and mortar shells exploding around you, but that is another story.
Still learning about extended events. The Profiler, now that we figured out what we were doing wrong, allows us to capture events occurring when the deadlocks occur. The big issue with the deadlock graphs is that with the parameterized queries we have nothing to tell us what values were passed to queries. We have been able to capture some of that with Profiler (actually using a server-side trace then loading that into Profiler).
I have a few more areas to research and check out. You could say I am chasing rabbits to be sure it isn't something in the database that we just haven't encountered before.
October 2, 2015 at 12:30 am
Quick question, are the ReportKey columns in in both tables coming from a third table or are there any other such indirect relationships?
😎
+---------------+ +-----------------------------+
| Report | | OperationsSIGACTDraftReport |
+---------------+ +-----------------------------+
|(pk) ReportKey |-(1)--------(n)-| (fk) ReportKey |
| ... |-(1)--, | ... |
+---------------+ | +-----------------------------+
|
| +-----------------+
| | Workflow |
| +-----------------+
'-----(n)-| (fk) ReportKey |
| ... |
+-----------------+
October 2, 2015 at 12:42 am
Eirikur Eiriksson (10/2/2015)
Quick question, are the ReportKey columns in in both tables coming from a third table or are there any other such indirect relationships?😎
+---------------+ +-----------------------------+
| Report | | OperationsSIGACTDraftReport |
+---------------+ +-----------------------------+
|(pk) ReportKey |-(1)--------(n)-| (fk) ReportKey |
| ... |-(1)--, | ... |
+---------------+ | +-----------------------------+
|
| +-----------------+
| | Workflow |
| +-----------------+
'-----(n)-| (fk) ReportKey |
| ... |
+-----------------+
There are no foreign key relationships between any of the tables in the database. There is no table above the base report tables (OperationsSIGACTDraftReport OperationsSIGACTPublishedReport, etc). There are subtables to the base tables, but as I said, no FKs are defined.
October 2, 2015 at 12:55 am
Lynn Pettis (10/2/2015)
There are no foreign key relationships between any of the tables in the database. There is no table above the base report tables (OperationsSIGACTDraftReport OperationsSIGACTPublishedReport, etc). There are subtables to the base tables, but as I said, no FKs are defined.
We can rule that one out then. Another question, where is the read uncommited isolation level set for the second (processa1b3288) and the third (processa1e6bc8) process? Just a hunch, seen similar causing deadlocks.
😎
Viewing 15 posts - 16 through 30 (of 36 total)
You must be logged in to reply to this topic. Login to reply