February 17, 2015 at 8:56 am
This filter AND O.CompanyClientMemberID IS NOT NULL won't work in the join, if the filter must be active then it must be in the WHERE clause. I'd mark it as suspect until you can show whether or not the result set should be filtered.
I think your EndEffectiveDate should be '2099-09-09 00:00:00.000', which indicates an active row in a time-expiring set.
Otherwise it's ok. Here's a full query which appears to do the job, but I'm not happy with the t11 part - it looks over-engineered and I suspect a couple of the tables could be removed from the FROM list whilst maintaining the business logic. The current sample dataset is inadequate for testing t11. Note that some of your parameters are now redundant - you don't need three of them for your enddate value.
DECLARE @p0 DATETIME = '2099-09-09 00:00:00.000';
DECLARE @p1 INT = 1;
DECLARE @p2 INT = 1;
DECLARE @p3 NVARCHAR(4000) = 1;
DECLARE @p4 INT = 1;
DECLARE @p5 NVARCHAR(4000) = 1;
DECLARE @p6 DATETIME = '2099-09-09 00:00:00.000';
DECLARE @p7 DATETIME = '2099-09-09 00:00:00.000';
DECLARE @p8 INT = 1;
DECLARE @p9 INT = 1;
DECLARE @p10 INT = 1;
DECLARE @p11 INT = 801;
SELECT *
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,
[value] = ISNULL(t0.CompanyClientMemberID, @p1),
[value2] = ISNULL(t0.ContactClientMemberID, @p2),
[value3] = ISNULL(t0.ReportType, @p3),
[value4] = ISNULL(t3.ClientRoleID, @p4), -- only because ClientRoleID is not nullable
[OutBoxAN2] = t1.OutBoxAN
FROM dbo.Outbox AS t0
INNER JOIN dbo.OutBoxData AS t1
ON t1.OutBoxAN = t0.OutboxAN
LEFT JOIN dbo.ClientMember t3
ON t3.ClientMemberID = t0.CompanyClientMemberID
AND t3.EndEffectiveDate = @p0 -- '2099-09-09 00:00:00.000'
WHERE t0.OutboxStatusAN = @p11
AND t0.CompanyClientMemberID IS NOT NULL
) t4
WHERE
NOT EXISTS(
SELECT 1
FROM dbo.MemberOther t6
INNER JOIN dbo.ClientMember t7 -- same ClientID as t4
ON t7.ClientMemberID = t6.ClientMemberID
AND t7.EndEffectiveDate = @p7 -- '2099-09-09 00:00:00.000'
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) -- '2099-09-09 00:00:00.000'
OR [t4].[OutboxTypeAN] NOT IN (@p9,@p10)[/quote]
This looks fantastic, so you essentially converted the CROSS APPLY to a NOT EXISTS?
February 17, 2015 at 8:58 am
Velveeta22 (2/17/2015)
This filter AND O.CompanyClientMemberID IS NOT NULL won't work in the join, if the filter must be active then it must be in the WHERE clause. I'd mark it as suspect until you can show whether or not the result set should be filtered.I think your EndEffectiveDate should be '2099-09-09 00:00:00.000', which indicates an active row in a time-expiring set.
Otherwise it's ok. Here's a full query which appears to do the job, but I'm not happy with the t11 part - it looks over-engineered and I suspect a couple of the tables could be removed from the FROM list whilst maintaining the business logic. The current sample dataset is inadequate for testing t11. Note that some of your parameters are now redundant - you don't need three of them for your enddate value.
DECLARE @p0 DATETIME = '2099-09-09 00:00:00.000';
DECLARE @p1 INT = 1;
DECLARE @p2 INT = 1;
DECLARE @p3 NVARCHAR(4000) = 1;
DECLARE @p4 INT = 1;
DECLARE @p5 NVARCHAR(4000) = 1;
DECLARE @p6 DATETIME = '2099-09-09 00:00:00.000';
DECLARE @p7 DATETIME = '2099-09-09 00:00:00.000';
DECLARE @p8 INT = 1;
DECLARE @p9 INT = 1;
DECLARE @p10 INT = 1;
DECLARE @p11 INT = 801;
SELECT *
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,
[value] = ISNULL(t0.CompanyClientMemberID, @p1),
[value2] = ISNULL(t0.ContactClientMemberID, @p2),
[value3] = ISNULL(t0.ReportType, @p3),
[value4] = ISNULL(t3.ClientRoleID, @p4), -- only because ClientRoleID is not nullable
[OutBoxAN2] = t1.OutBoxAN
FROM dbo.Outbox AS t0
INNER JOIN dbo.OutBoxData AS t1
ON t1.OutBoxAN = t0.OutboxAN
LEFT JOIN dbo.ClientMember t3
ON t3.ClientMemberID = t0.CompanyClientMemberID
AND t3.EndEffectiveDate = @p0 -- '2099-09-09 00:00:00.000'
WHERE t0.OutboxStatusAN = @p11
AND t0.CompanyClientMemberID IS NOT NULL
) t4
WHERE
NOT EXISTS(
SELECT 1
FROM dbo.MemberOther t6
INNER JOIN dbo.ClientMember t7 -- same ClientID as t4
ON t7.ClientMemberID = t6.ClientMemberID
AND t7.EndEffectiveDate = @p7 -- '2099-09-09 00:00:00.000'
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) -- '2099-09-09 00:00:00.000'
OR [t4].[OutboxTypeAN] NOT IN (@p9,@p10)
This looks fantastic, so you essentially converted the CROSS APPLY to a NOT EXISTS?[/quote]
It was a LEFT OUTER CROSS APPLY 😀
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 17, 2015 at 10:22 am
Matched the column names to the original stuck query and added friendly alias'. Results pull matching data in both test and prod (fresh data). My hope is the simplified logic removing CROSS JOIN and CROSS APPLY will tackle whatever the timeout issue was.
DECLARE @p0 DATETIME = '2099-09-09 00:00:00.000';
DECLARE @p1 INT = 1;
DECLARE @p2 INT = 1;
DECLARE @p3 NVARCHAR(4000) = 1;
DECLARE @p4 INT = 1;
DECLARE @p5 NVARCHAR(4000) = 1;
DECLARE @p6 DATETIME = '2099-09-09 00:00:00.000';
DECLARE @p7 DATETIME = '2099-09-09 00:00:00.000';
DECLARE @p8 INT = 1;
DECLARE @p9 INT = 1;
DECLARE @p10 INT = 1;
DECLARE @p11 INT = 801;
SELECT *
FROM (
SELECT
O.Destination,
O.Message,
O.OutboxAN,
O.OutboxStatusAN,
O.OutboxTypeAN,
O.Subject,
OBD.Fax,
OBD.FileName,
OBD.OutBoxData AS OutBoxData1, -- Necessary to change header of column to OutBoxData1?
OBD.OutBoxDataAN,
OBD.Type,
O.ClientReferenceID,
ISNULL(O.CompanyClientMemberID, @p1) AS CompanyClientMemberID,
ISNULL(O.ContactClientMemberID, @p2) AS ContactClientMemberID,
ISNULL(O.ReportType, @p3) AS ReportType,
ISNULL(CM.ClientRoleID, @p4) AS CompanyClientRoleID -- only because ClientRoleID is not nullable
FROM dbo.Outbox AS O
INNER JOIN dbo.OutBoxData AS OBD
ON OBD.OutBoxAN = O.OutboxAN
LEFT JOIN dbo.ClientMember CM
ON CM.ClientMemberID = O.CompanyClientMemberID
AND CM.EndEffectiveDate = @p0 -- '2099-09-09 00:00:00.000'
WHERE O.OutboxStatusAN = @p11
AND O.CompanyClientMemberID IS NOT NULL -- Not firm on the business rules, is this condition required (suspect)?
) DataSet
WHERE
NOT EXISTS(
SELECT 1
FROM dbo.MemberOther MO
INNER JOIN dbo.ClientMember CM1 -- same ClientID as DataSet
ON CM1.ClientMemberID = MO.ClientMemberID
AND CM1.EndEffectiveDate = @p7 -- '2099-09-09 00:00:00.000'
INNER JOIN dbo.ClientRole CR -- same ClientID as DataSet
ON CR.ClientRoleID = CM1.ClientRoleID
INNER JOIN dbo.ClientRole CR1 -- same ClientID as DataSet
ON CR1.ClientID = CR.ClientID
AND CR1.DefaultBeginLevel = @p8
AND CR1.ClientRoleID = DataSet.CompanyClientRoleID -- DataSet.CompanyClientRoleID is Dat.ClientRoleID
WHERE MO.TheValue = 1
AND MO.Description = @p5
AND MO.EndEffectiveDate = @p6) -- '2099-09-09 00:00:00.000'
OR DataSet.OutboxTypeAN NOT IN (@p9,@p10)
For how small the tables are, I feel like the query has poor execution times at 6 seconds. Query plan attached, clearly has issues with indexes. Thoughts?
February 17, 2015 at 11:44 am
Velveeta22 (2/17/2015)
Matched the column names to the original stuck query and added friendly alias'. Results pull matching data in both test and prod (fresh data). My hope is the simplified logic removing CROSS JOIN and CROSS APPLY will tackle whatever the timeout issue was.
DECLARE @p0 DATETIME = '2099-09-09 00:00:00.000';DECLARE @p1 INT = 1;
DECLARE @p2 INT = 1;
DECLARE @p3 NVARCHAR(4000) = 1;
DECLARE @p4 INT = 1;
DECLARE @p5 NVARCHAR(4000) = 1;
DECLARE @p6 DATETIME = '2099-09-09 00:00:00.000';
DECLARE @p7 DATETIME = '2099-09-09 00:00:00.000';
DECLARE @p8 INT = 1;
DECLARE @p9 INT = 1;
DECLARE @p10 INT = 1;
DECLARE @p11 INT = 801;
SELECT *
FROM (
SELECT
O.Destination,
O.Message,
O.OutboxAN,
O.OutboxStatusAN,
O.OutboxTypeAN,
O.Subject,
OBD.Fax,
OBD.FileName,
OBD.OutBoxData AS OutBoxData1, -- Necessary to change header of column to OutBoxData1?
OBD.OutBoxDataAN,
OBD.Type,
O.ClientReferenceID,
ISNULL(O.CompanyClientMemberID, @p1) AS CompanyClientMemberID,
ISNULL(O.ContactClientMemberID, @p2) AS ContactClientMemberID,
ISNULL(O.ReportType, @p3) AS ReportType,
ISNULL(CM.ClientRoleID, @p4) AS CompanyClientRoleID -- only because ClientRoleID is not nullable
FROM dbo.Outbox AS O
INNER JOIN dbo.OutBoxData AS OBD
ON OBD.OutBoxAN = O.OutboxAN
LEFT JOIN dbo.ClientMember CM
ON CM.ClientMemberID = O.CompanyClientMemberID
AND CM.EndEffectiveDate = @p0 -- '2099-09-09 00:00:00.000'
WHERE O.OutboxStatusAN = @p11
AND O.CompanyClientMemberID IS NOT NULL -- Not firm on the business rules, is this condition required (suspect)?
) DataSet
WHERE
NOT EXISTS(
SELECT 1
FROM dbo.MemberOther MO
INNER JOIN dbo.ClientMember CM1 -- same ClientID as DataSet
ON CM1.ClientMemberID = MO.ClientMemberID
AND CM1.EndEffectiveDate = @p7 -- '2099-09-09 00:00:00.000'
INNER JOIN dbo.ClientRole CR -- same ClientID as DataSet
ON CR.ClientRoleID = CM1.ClientRoleID
INNER JOIN dbo.ClientRole CR1 -- same ClientID as DataSet
ON CR1.ClientID = CR.ClientID
AND CR1.DefaultBeginLevel = @p8
AND CR1.ClientRoleID = DataSet.CompanyClientRoleID -- DataSet.CompanyClientRoleID is Dat.ClientRoleID
WHERE MO.TheValue = 1
AND MO.Description = @p5
AND MO.EndEffectiveDate = @p6) -- '2099-09-09 00:00:00.000'
OR DataSet.OutboxTypeAN NOT IN (@p9,@p10)
For how small the tables are, I feel like the query has poor execution times at 6 seconds. Query plan attached, clearly has issues with indexes. Thoughts?
The portion of your query that adds "AND O.CompanyClientMemberID IS NOT NULL" to the WHERE clause is a definitive change from the original query, as it now limits the query only to NON NULL values for that field, whereas the original query only used this as a part of a LEFT JOIN, which effectively rendered it pointless and having no effect because the same field also participated in an equality within the same join. By having it present in the new query, you are potentially introducing undesired results. It will depend on whether or not any of the records that will ever try to pass through this query have NULL values for that field, but would otherwise qualify to appear in the results. It may be that the existing data has no such records, but you should at least know that you ARE changing the original query by including this in the new one.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
February 17, 2015 at 1:32 pm
Note back from the developer:
I found that there were two objects created identical. 1st was used to query and filter the data and the 2nd was being used to update. After some time the 1st object would allow the 2nd object to do some updates, but after a few seconds it would lock the SQL table. This would lock everything and everyone out of the table. If this was running and tried to query the data table through SQL server management studio it would be locked out as well. The solution was to remove the 2nd object and have the 1st object do the query, filter and updating. This solution seems to working without hanging up the service and without locking the table. I believe that our next release contains this fix.
I sent him some thoughts on the indexes, especially the clustered index on the status column that changes frequently and has low cardinality. Thank you to everyone who jumped in on this one and helped troubleshoot. You guys are SQL geniuses and I learned a lot. 😀
February 18, 2015 at 2:30 am
Velveeta22 (2/17/2015)
Matched the column names to the original stuck query and added friendly alias'. Results pull matching data in both test and prod (fresh data). My hope is the simplified logic removing CROSS JOIN and CROSS APPLY will tackle whatever the timeout issue was.
DECLARE @p0 DATETIME = '2099-09-09 00:00:00.000';DECLARE @p1 INT = 1;
DECLARE @p2 INT = 1;
DECLARE @p3 NVARCHAR(4000) = 1;
DECLARE @p4 INT = 1;
DECLARE @p5 NVARCHAR(4000) = 1;
DECLARE @p6 DATETIME = '2099-09-09 00:00:00.000';
DECLARE @p7 DATETIME = '2099-09-09 00:00:00.000';
DECLARE @p8 INT = 1;
DECLARE @p9 INT = 1;
DECLARE @p10 INT = 1;
DECLARE @p11 INT = 801;
SELECT *
FROM (
SELECT
O.Destination,
O.Message,
O.OutboxAN,
O.OutboxStatusAN,
O.OutboxTypeAN,
O.Subject,
OBD.Fax,
OBD.FileName,
OBD.OutBoxData AS OutBoxData1, -- Necessary to change header of column to OutBoxData1?
OBD.OutBoxDataAN,
OBD.Type,
O.ClientReferenceID,
ISNULL(O.CompanyClientMemberID, @p1) AS CompanyClientMemberID,
ISNULL(O.ContactClientMemberID, @p2) AS ContactClientMemberID,
ISNULL(O.ReportType, @p3) AS ReportType,
ISNULL(CM.ClientRoleID, @p4) AS CompanyClientRoleID -- only because ClientRoleID is not nullable
FROM dbo.Outbox AS O
INNER JOIN dbo.OutBoxData AS OBD
ON OBD.OutBoxAN = O.OutboxAN
LEFT JOIN dbo.ClientMember CM
ON CM.ClientMemberID = O.CompanyClientMemberID
AND CM.EndEffectiveDate = @p0 -- '2099-09-09 00:00:00.000'
WHERE O.OutboxStatusAN = @p11
AND O.CompanyClientMemberID IS NOT NULL -- Not firm on the business rules, is this condition required (suspect)?
) DataSet
WHERE
NOT EXISTS(
SELECT 1
FROM dbo.MemberOther MO
INNER JOIN dbo.ClientMember CM1 -- same ClientID as DataSet
ON CM1.ClientMemberID = MO.ClientMemberID
AND CM1.EndEffectiveDate = @p7 -- '2099-09-09 00:00:00.000'
INNER JOIN dbo.ClientRole CR -- same ClientID as DataSet
ON CR.ClientRoleID = CM1.ClientRoleID
INNER JOIN dbo.ClientRole CR1 -- same ClientID as DataSet
ON CR1.ClientID = CR.ClientID
AND CR1.DefaultBeginLevel = @p8
AND CR1.ClientRoleID = DataSet.CompanyClientRoleID -- DataSet.CompanyClientRoleID is Dat.ClientRoleID
WHERE MO.TheValue = 1
AND MO.Description = @p5
AND MO.EndEffectiveDate = @p6) -- '2099-09-09 00:00:00.000'
OR DataSet.OutboxTypeAN NOT IN (@p9,@p10)
For how small the tables are, I feel like the query has poor execution times at 6 seconds. Query plan attached, clearly has issues with indexes. Thoughts?
That's not an execution plan you've attached.
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 18, 2015 at 5:55 am
Velveeta22 (2/17/2015)
Matched the column names to the original stuck query and added friendly alias'. Results pull matching data in both test and prod (fresh data). My hope is the simplified logic removing CROSS JOIN and CROSS APPLY will tackle whatever the timeout issue was.
DECLARE @p0 DATETIME = '2099-09-09 00:00:00.000';DECLARE @p1 INT = 1;
DECLARE @p2 INT = 1;
DECLARE @p3 NVARCHAR(4000) = 1;
DECLARE @p4 INT = 1;
DECLARE @p5 NVARCHAR(4000) = 1;
DECLARE @p6 DATETIME = '2099-09-09 00:00:00.000';
DECLARE @p7 DATETIME = '2099-09-09 00:00:00.000';
DECLARE @p8 INT = 1;
DECLARE @p9 INT = 1;
DECLARE @p10 INT = 1;
DECLARE @p11 INT = 801;
SELECT *
FROM (
SELECT
O.Destination,
O.Message,
O.OutboxAN,
O.OutboxStatusAN,
O.OutboxTypeAN,
O.Subject,
OBD.Fax,
OBD.FileName,
OBD.OutBoxData AS OutBoxData1, -- Necessary to change header of column to OutBoxData1?
OBD.OutBoxDataAN,
OBD.Type,
O.ClientReferenceID,
ISNULL(O.CompanyClientMemberID, @p1) AS CompanyClientMemberID,
ISNULL(O.ContactClientMemberID, @p2) AS ContactClientMemberID,
ISNULL(O.ReportType, @p3) AS ReportType,
ISNULL(CM.ClientRoleID, @p4) AS CompanyClientRoleID -- only because ClientRoleID is not nullable
FROM dbo.Outbox AS O
INNER JOIN dbo.OutBoxData AS OBD
ON OBD.OutBoxAN = O.OutboxAN
LEFT JOIN dbo.ClientMember CM
ON CM.ClientMemberID = O.CompanyClientMemberID
AND CM.EndEffectiveDate = @p0 -- '2099-09-09 00:00:00.000'
WHERE O.OutboxStatusAN = @p11
AND O.CompanyClientMemberID IS NOT NULL -- Not firm on the business rules, is this condition required (suspect)?
) DataSet
WHERE
NOT EXISTS(
SELECT 1
FROM dbo.MemberOther MO
INNER JOIN dbo.ClientMember CM1 -- same ClientID as DataSet
ON CM1.ClientMemberID = MO.ClientMemberID
AND CM1.EndEffectiveDate = @p7 -- '2099-09-09 00:00:00.000'
INNER JOIN dbo.ClientRole CR -- same ClientID as DataSet
ON CR.ClientRoleID = CM1.ClientRoleID
INNER JOIN dbo.ClientRole CR1 -- same ClientID as DataSet
ON CR1.ClientID = CR.ClientID
AND CR1.DefaultBeginLevel = @p8
AND CR1.ClientRoleID = DataSet.CompanyClientRoleID -- DataSet.CompanyClientRoleID is Dat.ClientRoleID
WHERE MO.TheValue = 1
AND MO.Description = @p5
AND MO.EndEffectiveDate = @p6) -- '2099-09-09 00:00:00.000'
OR DataSet.OutboxTypeAN NOT IN (@p9,@p10)
For how small the tables are, I feel like the query has poor execution times at 6 seconds. Query plan attached, clearly has issues with indexes. Thoughts?
Did you notice the "Missing Index (Impact 32.3606): ..." notification in that SQL Plan ? Here's the recommendation:
/*
Missing Index Details from Updated_Query.sqlplan
The Query Processor estimates that implementing the following index could improve the query cost by 32.3606%.
*/
/*
USE [RedArrow]
GO
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[MemberOther] ([Description],[TheValue],[EndEffectiveDate])
INCLUDE ([ClientMemberID])
GO
*/
And ChrisM - This is the first .sqlplan file I've ever seen that adds up in percentages to well over 200%. Does that have anything to do with your response to the same post I'm responding to ?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
February 18, 2015 at 6:18 am
In the t4 part of the query, there are significant differences between actual and estimated number of rows / number of executions, indicating that statistics may be wildly out of date.
Look at the Index Seek for ClientMember in t4. The table has 326567 rows. The estimate is 66 rows retrieved but 306499 rows are retrieved - very inefficient compared to an index scan and costed at 0% of the total.
Bad statistics can seed a bad plan. Update the statistics and run the query again to get a new plan, then post back. Any suggestions regarding this latest execution plan may not apply to a new plan calculated on the basis of up to date statistics.
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 18, 2015 at 8:13 am
ChrisM@Work (2/18/2015)
In the t4 part of the query, there are significant differences between actual and estimated number of rows / number of executions, indicating that statistics may be wildly out of date.Look at the Index Seek for ClientMember in t4. The table has 326567 rows. The estimate is 66 rows retrieved but 306499 rows are retrieved - very inefficient compared to an index scan and costed at 0% of the total.
Bad statistics can seed a bad plan. Update the statistics and run the query again to get a new plan, then post back. Any suggestions regarding this latest execution plan may not apply to a new plan calculated on the basis of up to date statistics.
The re-index script runs every Sat. I thought statistics would update with that run, but after some Googling it looks like ALTER INDEX … REORGANIZE will not. Unfortunately the previous DBA both left after a month of training me and also left the systems in pretty bad shape. None of our servers have integrity checks right now and I will be implementing Ola's solution for that once I clearance and the time window for each server. Look's like I will need to also enable the statistics portion of that script.
Worst thing the previous DBA did: entire servers full of indexes with a fill factor of 20 (set to default in server properties). Not just on small tables, but on tables over 400 GB sitting on 2 TB databases. Things were blowing up around here and my first year was firefighting and migrating/consolidating all the old boxes.
February 18, 2015 at 8:23 am
Velveeta22 (2/18/2015)
ChrisM@Work (2/18/2015)
In the t4 part of the query, there are significant differences between actual and estimated number of rows / number of executions, indicating that statistics may be wildly out of date.Look at the Index Seek for ClientMember in t4. The table has 326567 rows. The estimate is 66 rows retrieved but 306499 rows are retrieved - very inefficient compared to an index scan and costed at 0% of the total.
Bad statistics can seed a bad plan. Update the statistics and run the query again to get a new plan, then post back. Any suggestions regarding this latest execution plan may not apply to a new plan calculated on the basis of up to date statistics.
The re-index script runs every Sat. I thought statistics would update with that run, but after some Googling it looks like ALTER INDEX … REORGANIZE will not. Unfortunately the previous DBA both left after a month of training me and also left the systems in pretty bad shape. None of our servers have integrity checks right now and I will be implementing Ola's solution for that once I clearance and the time window for each server. Look's like I will need to also enable the statistics portion of that script.
Worst thing the previous DBA did: entire servers full of indexes with a fill factor of 20 (set to default in server properties). Not just on small tables, but on tables over 400 GB sitting on 2 TB databases. Things were blowing up around here and my first year was firefighting and migrating/consolidating all the old boxes.
Nightmare stories like this seem to appear on ssc with increasing and alarming regularity. Kudos to you for your effort into guiding this lot under control.
Use whichever is your preferred method for updating the statistics on those tables, rerun the query and capture the actual plan. It might look superficially similar but under the hood I'd expect it to be quite different. It will be very interesting to see.
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 19, 2015 at 1:42 am
sgmunson (2/18/2015)
Velveeta22 (2/17/2015)
Matched the column names to the original stuck query and added friendly alias'. Results pull matching data in both test and prod (fresh data). My hope is the simplified logic removing CROSS JOIN and CROSS APPLY will tackle whatever the timeout issue was.
DECLARE @p0 DATETIME = '2099-09-09 00:00:00.000';DECLARE @p1 INT = 1;
DECLARE @p2 INT = 1;
DECLARE @p3 NVARCHAR(4000) = 1;
DECLARE @p4 INT = 1;
DECLARE @p5 NVARCHAR(4000) = 1;
DECLARE @p6 DATETIME = '2099-09-09 00:00:00.000';
DECLARE @p7 DATETIME = '2099-09-09 00:00:00.000';
DECLARE @p8 INT = 1;
DECLARE @p9 INT = 1;
DECLARE @p10 INT = 1;
DECLARE @p11 INT = 801;
SELECT *
FROM (
SELECT
O.Destination,
O.Message,
O.OutboxAN,
O.OutboxStatusAN,
O.OutboxTypeAN,
O.Subject,
OBD.Fax,
OBD.FileName,
OBD.OutBoxData AS OutBoxData1, -- Necessary to change header of column to OutBoxData1?
OBD.OutBoxDataAN,
OBD.Type,
O.ClientReferenceID,
ISNULL(O.CompanyClientMemberID, @p1) AS CompanyClientMemberID,
ISNULL(O.ContactClientMemberID, @p2) AS ContactClientMemberID,
ISNULL(O.ReportType, @p3) AS ReportType,
ISNULL(CM.ClientRoleID, @p4) AS CompanyClientRoleID -- only because ClientRoleID is not nullable
FROM dbo.Outbox AS O
INNER JOIN dbo.OutBoxData AS OBD
ON OBD.OutBoxAN = O.OutboxAN
LEFT JOIN dbo.ClientMember CM
ON CM.ClientMemberID = O.CompanyClientMemberID
AND CM.EndEffectiveDate = @p0 -- '2099-09-09 00:00:00.000'
WHERE O.OutboxStatusAN = @p11
AND O.CompanyClientMemberID IS NOT NULL -- Not firm on the business rules, is this condition required (suspect)?
) DataSet
WHERE
NOT EXISTS(
SELECT 1
FROM dbo.MemberOther MO
INNER JOIN dbo.ClientMember CM1 -- same ClientID as DataSet
ON CM1.ClientMemberID = MO.ClientMemberID
AND CM1.EndEffectiveDate = @p7 -- '2099-09-09 00:00:00.000'
INNER JOIN dbo.ClientRole CR -- same ClientID as DataSet
ON CR.ClientRoleID = CM1.ClientRoleID
INNER JOIN dbo.ClientRole CR1 -- same ClientID as DataSet
ON CR1.ClientID = CR.ClientID
AND CR1.DefaultBeginLevel = @p8
AND CR1.ClientRoleID = DataSet.CompanyClientRoleID -- DataSet.CompanyClientRoleID is Dat.ClientRoleID
WHERE MO.TheValue = 1
AND MO.Description = @p5
AND MO.EndEffectiveDate = @p6) -- '2099-09-09 00:00:00.000'
OR DataSet.OutboxTypeAN NOT IN (@p9,@p10)
For how small the tables are, I feel like the query has poor execution times at 6 seconds. Query plan attached, clearly has issues with indexes. Thoughts?
Did you notice the "Missing Index (Impact 32.3606): ..." notification in that SQL Plan ? Here's the recommendation:
/*
Missing Index Details from Updated_Query.sqlplan
The Query Processor estimates that implementing the following index could improve the query cost by 32.3606%.
*/
/*
USE [RedArrow]
GO
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[MemberOther] ([Description],[TheValue],[EndEffectiveDate])
INCLUDE ([ClientMemberID])
GO
*/
And ChrisM - This is the first .sqlplan file I've ever seen that adds up in percentages to well over 200%. Does that have anything to do with your response to the same post I'm responding to ?
Hi Steve
No not really. I read this yesterday whilst dealing with a query with summed percentage costs in the plan somewhere around 500%. Indexes were good, stats up to date. It's not unusual.
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
Viewing 11 posts - 46 through 55 (of 55 total)
You must be logged in to reply to this topic. Login to reply