February 11, 2015 at 2:23 pm
Help needed from the guru's here! I have a sql snippet from a 3rd party application that will not complete its transaction. The SELECT statement executes but does not finish. Instead the statement just sits in AWAITING COMMAND for 1000 seconds then dies, thus killing the UPDATE statement that is supposed to follow.
The CROSS JOIN and CROSS APPLY seem suspect. Can anyone spot where this query goes wrong?
(
@p0 DATETIME,
@p1 INT,
@p2 INT,
@p3 NVARCHAR(4000),
@p4 INT,
@p5 NVARCHAR(4000),
@p6 DATETIME,
@p7 DATETIME,
@p8 INT,
@p9 INT,
@p10 INT,
@p11 INT
)
SELECT [t4].[Destination],
[t4].[Message],
[t4].[OutboxAN],
[t4].[OutboxStatusAN],
[t4].[OutboxTypeAN],
[t4].[Subject],
[t4].[Fax],
[t4].[FileName],
[t4].[OutBoxData] AS [OutBoxData1],
[t4].[OutBoxDataAN],
[t4].[Type],
[t4].[ClientReferenceID],
[t4].[value] AS [CompanyClientMemberID],
[t4].[value2] AS [ContactClientMemberID],
[t4].[value3] AS [ReportType],
[t4].[value4] AS [CompanyClientRoleID]
FROM (
SELECT [t0].[Destination],
[t0].[Message],
[t0].[OutboxAN],
[t0].[OutboxStatusAN],
[t0].[OutboxTypeAN],
[t0].[Subject],
[t1].[Fax],
[t1].[FileName],
[t1].[OutBoxData],
[t1].[OutBoxDataAN],
[t1].[Type],
[t0].[ClientReferenceID],
(
CASE
WHEN [t0].[CompanyClientMemberID] IS NULL
THEN @p1
ELSE [t0].[CompanyClientMemberID]
END
) AS [value],
(
CASE
WHEN [t0].[ContactClientMemberID] IS NULL
THEN @p2
ELSE [t0].[ContactClientMemberID]
END
) AS [value2],
(
CASE
WHEN [t0].[ReportType] IS NULL
THEN CONVERT(NVARCHAR(50), @p3)
ELSE [t0].[ReportType]
END
) AS [value3],
(
CASE
WHEN [t3].[test] IS NULL
THEN @p4
ELSE [t3].[ClientRoleID]
END
) AS [value4],
[t1].[OutBoxAN] AS [OutBoxAN2]
FROM [dbo].[Outbox] AS [t0]
CROSS JOIN [dbo].[OutBoxData] AS [t1]
LEFT JOIN (
SELECT 1 AS [test],
[t2].[ClientMemberID],
[t2].[ClientRoleID],
[t2].[EndEffectiveDate]
FROM [dbo].[ClientMember] AS [t2]
) AS [t3]
ON ([t0].[CompanyClientMemberID] IS NOT NULL)
AND ([t3].[ClientMemberID] = ([t0].[CompanyClientMemberID]))
AND ([t3].[EndEffectiveDate] = @p0)
) AS [t4]
CROSS APPLY (
SELECT TOP (1) [t10].[test]
FROM (
SELECT NULL AS [EMPTY]
) AS [t5]
LEFT JOIN (
SELECT 1 AS [test]
FROM [dbo].[MemberOther] AS [t6],
[dbo].[ClientMember] AS [t7],
[dbo].[ClientRole] AS [t8],
[dbo].[ClientRole] AS [t9]
WHERE ([t9].[ClientRoleID] = [t4].[value4])
AND ([t6].[TheValue] = 1)
AND ([t6].[Description] = @p5)
AND ([t6].[EndEffectiveDate] = @p6)
AND ([t7].[ClientMemberID] = [t6].[ClientMemberID])
AND ([t7].[EndEffectiveDate] = @p7)
AND ([t8].[ClientRoleID] = [t7].[ClientRoleID])
AND ([t9].[ClientID] = [t8].[ClientID])
AND ([t9].[DefaultBeginLevel] = @p8)
) AS [t10]
ON 1 = 1
) AS [t11]
WHERE (
([t11].[test] IS NULL)
OR (
([t11].[test] IS NOT NULL)
AND ([t4].[OutboxTypeAN] <> @p9)
AND ([t4].[OutboxTypeAN] <> @p10)
)
)
AND ([t4].[OutboxStatusAN] = @p11)
AND ([t4].[OutBoxAN2] = ([t4].[OutboxAN]))
February 11, 2015 at 2:25 pm
To be clear, this is a transaction that puts a lock on a table and the transaction does not complete, causing a block not a deadlock.
February 11, 2015 at 2:52 pm
Without some details it is impossible to help. This isn't even the complete statement as there are variables at the very top. There is also nothing here that is in a transaction.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 11, 2015 at 3:06 pm
Unfortunately this query is the only part of the transaction that I can grab. The rest is buried in .NET code that I do not have access to. Was hoping something would jump out in the sql statement. :crazy:
February 11, 2015 at 3:10 pm
Velveeta22 (2/11/2015)
Unfortunately this query is the only part of the transaction that I can grab. The rest is buried in .NET code that I do not have access to. Was hoping something would jump out in the sql statement. :crazy:
Yuck!!! That sounds like a pretty rough architecture you have to deal with. Gotta love those vendor apps that are painful on a good day. 😛
Nothing jumps out there but without knowledge of the tables and what it is doing there isn't much we can do from here.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 11, 2015 at 3:33 pm
Thanks for taking a look, you rock!
I know this is a batch process and may be able to snag the full thing by using Adam Mechanic's sp_WhoIsActive by changing the @get_full_inner_text to 1 and execute during the block. Will see what I can grab, thanks again.
--If 1, gets the full stored procedure or running batch, when available
--If 0, gets only the actual statement that is currently running in the batch or procedure
@get_full_inner_text BIT = 0,
February 11, 2015 at 3:34 pm
Well, they probably aren't blocking causes, but just taking a look at the query itself, I see some things that jump out as odd. For example, there are CASE statements that could be replaced with the ISNULL function, and there's a JOIN condition that doesn't appear to be necessary ([t0].[CompanyClientMemberID] IS NOT NULL) on the LEFT JOIN. Given a query written with those kinds of things present doesn't bode well for what's buried in the .NET code, either. I'd suggest getting the DBA to run SQL Profiler and see exactly what is being executed and maybe run a trace to find the exact code that's the culprit. My hunch is that one of those joins runs up against something that's blocked most of the time by the app.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
February 11, 2015 at 11:11 pm
It would be very interesting to see the execution plan for this code and whoever wrote this code apparently doesn't like the optimizer
😎
February 12, 2015 at 12:16 am
Velveeta22 (2/11/2015)
Unfortunately this query is the only part of the transaction that I can grab. The rest is buried in .NET code that I do not have access to. Was hoping something would jump out in the sql statement. :crazy:
I have to agree with the others here, there is nothing wrong with this statement per se, just because it is being blocked its not really its fault.
That is not to say that this statement in isolation creates a 'good' plan etc..
There are things you should do and that is find the statement that is doing the blocking.
My first step would be to use sp_whoisactive ( google for it ) and that will tell you which spid you are being blocked by, or lead blocker if there is a big chain.
Note : This is not the same as the statement that created the locks that you are now being blocked by.
From this you should have a good idea of the lock type and the object / page / row that you are being blocked on and the application that is blocking you.
Now you need to talk to the dev of that system and find out what that is doing and if it is doing it right and if the transaction that is running can be committed faster.
If thats not possible, profiler will be your friend and you can monitor other similar transactions as they run to find the SQL they are executing.
You may find that its ok and your query is scanning to much data that is not needed to resolve the query and effective use of indexing could enable sqlserver to not need to read the locked data.
There is not a single pronged "do this A,B,C" here, locking, blocking and concurrency is a difficult subject and IMO :
A) The reason may devs just add NOLOCK everywhere to skip locks
B) The reason for the myth SQL Server doesnt scale.
There is also the blocked process report https://msdn.microsoft.com/en-us/library/ms191168.aspx that may help.
If all else fails you could try running SNAPSHOT isolation.
Firstly though, its information gathering to ensure you understand the locking chain, the causes of those locks that have been created and how that is impacting on you.
February 12, 2015 at 2:00 am
Here's that same query with a little extra formatting:
SELECT
[t4].[Destination],
[t4].[Message],
[t4].[OutboxAN],
[t4].[OutboxStatusAN],
[t4].[OutboxTypeAN],
[t4].[Subject],
[t4].[Fax],
[t4].[FileName],
[t4].[OutBoxData] AS [OutBoxData1],
[t4].[OutBoxDataAN],
[t4].[Type],
[t4].[ClientReferenceID],
[t4].[value] AS [CompanyClientMemberID],
[t4].[value2] AS [ContactClientMemberID],
[t4].[value3] AS [ReportType],
[t4].[value4] AS [CompanyClientRoleID]
FROM ( -- t4
SELECT
[t0].[Destination],
[t0].[Message],
[t0].[OutboxAN],
[t0].[OutboxStatusAN],
[t0].[OutboxTypeAN],
[t0].[Subject],
[t1].[Fax],
[t1].[FileName],
[t1].[OutBoxData],
[t1].[OutBoxDataAN],
[t1].[Type],
[t0].[ClientReferenceID],
[value] = ISNULL([t0].[CompanyClientMemberID],@p1),
[value2] = ISNULL([t0].[ContactClientMemberID],@p2),
[value3] = ISNULL([t0].[ReportType],@p3),
[value4] = CASE WHEN [t3].[test] IS NULL THEN @p4 ELSE [t3].[ClientRoleID] END,
[t1].[OutBoxAN] AS [OutBoxAN2]
FROM [dbo].[Outbox] AS [t0]
CROSS JOIN [dbo].[OutBoxData] AS [t1]
LEFT JOIN ( -- t3
SELECT 1 AS [test],
[t2].[ClientMemberID],
[t2].[ClientRoleID],
[t2].[EndEffectiveDate]
FROM [dbo].[ClientMember] AS [t2]
) AS [t3]
ON ([t0].[CompanyClientMemberID] IS NOT NULL)
AND ([t3].[ClientMemberID] = ([t0].[CompanyClientMemberID]))
AND ([t3].[EndEffectiveDate] = @p0)
) AS [t4]
CROSS APPLY ( -- t11
SELECT TOP (1) [t10].[test]
FROM (SELECT NULL AS [EMPTY]) AS [t5]
LEFT JOIN ( -- t10
SELECT 1 AS [test]
FROM [dbo].[MemberOther] AS [t6],
[dbo].[ClientMember] AS [t7],
[dbo].[ClientRole] AS [t8],
[dbo].[ClientRole] AS [t9]
WHERE ([t9].[ClientRoleID] = [t4].[value4])
AND ([t6].[TheValue] = 1)
AND ([t6].[Description] = @p5)
AND ([t6].[EndEffectiveDate] = @p6)
AND ([t7].[ClientMemberID] = [t6].[ClientMemberID])
AND ([t7].[EndEffectiveDate] = @p7)
AND ([t8].[ClientRoleID] = [t7].[ClientRoleID])
AND ([t9].[ClientID] = [t8].[ClientID])
AND ([t9].[DefaultBeginLevel] = @p8)
) AS [t10]
ON 1 = 1
) AS [t11]
WHERE (
([t11].[test] IS NULL)
OR (
([t11].[test] IS NOT NULL)
AND ([t4].[OutboxTypeAN] <> @p9)
AND ([t4].[OutboxTypeAN] <> @p10)
)
)
AND ([t4].[OutboxStatusAN] = @p11)
AND ([t4].[OutBoxAN2] = ([t4].[OutboxAN]))
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
February 12, 2015 at 2:28 am
Dave Ballantyne (2/12/2015)...I have to agree with the others here, there is nothing wrong with this statement per se...
I have to disagree with you Dave:
-------------------------------------------------------
-- t4 rewrite
-------------------------------------------------------
-- [t3].[EndEffectiveDate] = @p0 turns t3 into an INNER JOIN so [t3].[test] is never NULL
-- The join predicate ON ([t0].[CompanyClientMemberID] IS NOT NULL) would be clearer in the WHERE clause
-- t3 subquery is unnecessary, INNER JOIN to table is sufficient - there's no filtering or aggregation in the subquery
-- Finally, the WHERE clause can go too - because the inner join between t3 and t0 excludes nulls.
SELECT
[t0].[Destination],
[t0].[Message],
[t0].[OutboxAN],
[t0].[OutboxStatusAN],
[t0].[OutboxTypeAN],
[t0].[Subject],
[t1].[Fax],
[t1].[FileName],
[t1].[OutBoxData],
[t1].[OutBoxDataAN],
[t1].[Type],
[t0].[ClientReferenceID],
[value] = ISNULL([t0].[CompanyClientMemberID],@p1),
[value2] = ISNULL([t0].[ContactClientMemberID],@p2),
[value3] = ISNULL([t0].[ReportType],@p3),
[value4] = [t3].[ClientRoleID],
[t1].[OutBoxAN] AS [OutBoxAN2]
FROM [dbo].[Outbox] AS [t0]
CROSS JOIN [dbo].[OutBoxData] AS [t1]
INNER JOIN [dbo].[ClientMember] t3
ON [t3].[ClientMemberID] = [t0].[CompanyClientMemberID]
AND [t3].[EndEffectiveDate] = @p0
-----------------------------------------------------------------------------------
-- There are filters in the outer SELECT:
WHERE
AND ([t4].[OutboxStatusAN] = @p11)
AND ([t4].[OutBoxAN2] = ([t4].[OutboxAN]))
-- which might be better for performance if applied directly to t4 subquery
SELECT
[t0].[Destination],
[t0].[Message],
[t0].[OutboxAN],
[t0].[OutboxStatusAN],
[t0].[OutboxTypeAN],
[t0].[Subject],
[t1].[Fax],
[t1].[FileName],
[t1].[OutBoxData],
[t1].[OutBoxDataAN],
[t1].[Type],
[t0].[ClientReferenceID],
[value] = ISNULL([t0].[CompanyClientMemberID],@p1),
[value2] = ISNULL([t0].[ContactClientMemberID],@p2),
[value3] = ISNULL([t0].[ReportType],@p3),
[value4] = [t3].[ClientRoleID],
[t1].[OutBoxAN] AS [OutBoxAN2]
FROM [dbo].[Outbox] AS [t0]
CROSS JOIN [dbo].[OutBoxData] AS [t1]
INNER JOIN [dbo].[ClientMember] t3
ON [t3].[ClientMemberID] = [t0].[CompanyClientMemberID]
AND [t3].[EndEffectiveDate] = @p0
WHERE [t0].[OutboxStatusAN] = @p11
--AND [t4].[OutBoxAN2] = [t4].[OutboxAN] -- as original query
AND [t1].[OutBoxAN] = [t0].[OutboxAN] -- and in t4
-- That's an INNER JOIN between t1 and t0! The CROSS JOIN is replaced by an INNER JOIN:
SELECT
t0.Destination,
t0.[Message],
t0.OutboxAN,
t0.OutboxStatusAN,
t0.OutboxTypeAN,
t0.[Subject],
t1.Fax,
t1.[FileName],
t1.OutBoxData,
t1.OutBoxDataAN,
t1.[Type],
t0.ClientReferenceID,
[value] = ISNULL(t0.CompanyClientMemberID, @p1),
[value2] = ISNULL(t0.ContactClientMemberID, @p2),
[value3] = ISNULL(t0.ReportType, @p3),
[value4] = t3.ClientRoleID,
[OutBoxAN2] = t1.OutBoxAN
FROM dbo.Outbox AS t0
INNER JOIN dbo.OutBoxData AS t1
ON t1.OutBoxAN = t0.OutboxAN
INNER JOIN dbo.ClientMember t3
ON t3.ClientMemberID = t0.CompanyClientMemberID
AND t3.EndEffectiveDate = @p0
WHERE t0.OutboxStatusAN = @p11
-- This is what t4 *should* look like :-)
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
February 12, 2015 at 3:00 am
-- t11 partial rewrite. Can't go any further than this without ddl/dml for ClientMember / ClientRole / MemberOther
CROSS APPLY ( -- t11
SELECT TOP (1) [t10].[test]
FROM (SELECT NULL AS [EMPTY]) AS [t5]
LEFT JOIN ( -- t10
SELECT 1 AS [test]
FROM [dbo].[MemberOther] AS [t6],
[dbo].[ClientMember] AS [t7],
[dbo].[ClientRole] AS [t8],
[dbo].[ClientRole] AS [t9]
WHERE ([t9].[ClientRoleID] = [t4].[value4])
AND ([t6].[TheValue] = 1)
AND ([t6].[Description] = @p5)
AND ([t6].[EndEffectiveDate] = @p6)
AND ([t7].[ClientMemberID] = [t6].[ClientMemberID])
AND ([t7].[EndEffectiveDate] = @p7)
AND ([t8].[ClientRoleID] = [t7].[ClientRoleID])
AND ([t9].[ClientID] = [t8].[ClientID])
AND ([t9].[DefaultBeginLevel] = @p8)
) AS [t10]
ON 1 = 1
) AS [t11]
-- is the same as
OUTER APPLY (
SELECT TOP (1)
1 AS [test]
FROM [dbo].[MemberOther] AS [t6],
[dbo].[ClientMember] AS [t7],
[dbo].[ClientRole] AS [t8],
[dbo].[ClientRole] AS [t9]
WHERE ([t9].[ClientRoleID] = [t4].[value4])
AND ([t6].[TheValue] = 1)
AND ([t6].[Description] = @p5)
AND ([t6].[EndEffectiveDate] = @p6)
AND ([t7].[ClientMemberID] = [t6].[ClientMemberID])
AND ([t7].[EndEffectiveDate] = @p7)
AND ([t8].[ClientRoleID] = [t7].[ClientRoleID])
AND ([t9].[ClientID] = [t8].[ClientID])
AND ([t9].[DefaultBeginLevel] = @p8)
) t11
-- which is better written as
OUTER APPLY (
SELECT TOP (1)
1 AS [test]
FROM dbo.MemberOther t6
INNER JOIN dbo.ClientMember t7 -- same ClientID as t4
ON t7.ClientMemberID = t6.ClientMemberID
AND t7.EndEffectiveDate = @p7
INNER JOIN dbo.ClientRole t8 -- same ClientID as t4
ON t8.ClientRoleID = t7.ClientRoleID
INNER JOIN dbo.ClientRole t9 -- same ClientID as t4
ON t9.ClientID = t8.ClientID
AND t9.DefaultBeginLevel = @p8
AND t9.ClientRoleID = t4.value4 -- t4.value4 is dbo.ClientMember.ClientRoleID
WHERE t6.TheValue = 1
AND t6.[Description] = @p5
AND t6.EndEffectiveDate = @p6
) t11
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
February 12, 2015 at 8:52 am
ChrisM@Work,
Just curious, but I've never seen anyone use an ON clause together with CROSS APPLY. Given that it's an ON 1=1, it would seem to be completely unnecessary, so does it do something differently than what would happen if the clause were removed?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
February 12, 2015 at 10:55 am
sgmunson (2/12/2015)
ChrisM@Work,Just curious, but I've never seen anyone use an ON clause together with CROSS APPLY. Given that it's an ON 1=1, it would seem to be completely unnecessary, so does it do something differently than what would happen if the clause were removed?
ON clause will throw an error with APPLY . The ON clause applies to the LEFT JOIN 😉
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
February 12, 2015 at 11:24 am
ChrisM@home (2/12/2015)
sgmunson (2/12/2015)
ChrisM@Work,Just curious, but I've never seen anyone use an ON clause together with CROSS APPLY. Given that it's an ON 1=1, it would seem to be completely unnecessary, so does it do something differently than what would happen if the clause were removed?
ON clause will throw an error with APPLY . The ON clause applies to the LEFT JOIN 😉
Yep, missed that... sorry... However, now that I look more closely at that query to which the main query does the CROSS APPLY, I have to wonder just what the original poster is trying to accomplish. SELECT TOP 1 without an ORDER BY seems odd to begin with, but it's only selecting a field that's going to end up as the value 1, or it will be null, so I wonder why an EXISTS clause and a CASE statement aren't there instead of a CROSS APPLY ? Or can that somehow make the optimizer do a better job? Seems to me that a LEFT OUTER JOIN might make more sense if all that's needed is an existence test. Your thoughts?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 15 posts - 1 through 15 (of 55 total)
You must be logged in to reply to this topic. Login to reply