Finding Culprit Blocking Sql

All we need is an easy explanation of the problem, so here it is.

Whenever in our system there is a block (everything stopped). I see the below query is the culprit,

  (@P1 numeric(10))SELECT "Col1128","Tbl1029"."A_NAME" "Col1124" FROM "DbName"."Schema"."MyTable1" 
"Tbl1029" RIGHT OUTER JOIN (SELECT "Tbl1027"."MyColumn1" "Col1126","Tbl1027"."MyColumn2" 
"Col1127","Tbl1027"."MyColumn4" "Col1128","Tbl1027"."MyColumn5" "Col1130","Tbl1027"."MyColumn6" 
"Col1129","Tbl1027"."MyColumn5" "Col1131" FROM "DbName"."Schame"."MyTable2" "Tbl1027" WHERE 
"Tbl1027"."MyColumn1"[email protected]) Qry1132 ON CONVERT(numeric(2,0),"Tbl1029"."MyColumn3",0)="Col1126"

We have 2 separate DB servers with completely different databases. The above is running in DB server 2 but request is coming DB server 1 (client host name). We have 100 of applications connecting to DB servers. Also, we have link server among DB servers.

Now, I know that the query came form DB server 1 with ProgramName Microsoft SQL Server. Now I am confused whether the program Microsoft SQL Server is indicating the link server query or application query using ORM.

I am searching in all applications but not finding anything yet. Form the DBA perspective what else I can do to get the real issue

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

Now, I know that the query came form DB server 1 with ProgramName Microsoft SQL Server. Now I am confused whether the program Microsoft SQL Server is indicating the link server query or application query using ORM.

The host name "DB server 1" along with the "Microsoft SQL Server" program name indicates a linked server query, not a query executed directly from the application.

From the DBA perspective what else I can do to get the real issue?

As @Learning_DBAdmin suggested in comments, examine the active queries running on DB server 1 during the blocking episode. One or more should show the query with the linked server along with the originating application host and program name.

The real issue is likely the need for query/index tuning to optimize the remote query. You may need indexes on the join/where clause columns. The explict conversion on Tbl1029.MyColumn3 will preclude using an index on the column efficiently (if one exists). The resultant full scans can lead to blocking.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply