January 9, 2017 at 7:38 am
Hi ALl
I have a view which looks something like below.
Select
* from T1 with(NoLock)
Inner Hash Join T2 With (NoLck) on T1.PID = T2.PID
Inner Hash Join T3 With (NoLck) T1.ID = T3.ID and T3.Type= 1
Inner Hash Join T3 With (NoLck) T1.ID = T3.ID and T3.Type= 2
Inner Hash Join T3 With (NoLck) T1.ID = T3.ID and T3.Type= 3
Inner Hash Join T3 With (NoLck) T1.ID = T3.ID and T3.Type= 4
Inner Hash Join T3 With (NoLck) T1.ID = T3.ID and T3.Type= 5
Inner Hash Join T3 With (NoLck) T1.ID = T3.ID and T3.Type= 6
Inner Hash Join T3 With (NoLck) T1.ID = T3.ID and T3.Type= 7
This query returns about 900 records but takes forever to run in one environment. However in another server in takes just 2 seconds and return 900 rows.
Table T3 seems to be the problematic one with 100K records.I have checked that indexes on both are same and also rebuild the
indexes and updated statistics.
This query used to work fine and return results in lst 5-6 seconds and has suddenly degraded so bad
I have experimented a bit seen that till the table T3 has around 35K record the results are returned fast and only after that the exponential degrade on performance has. With 100K the query never return anything.
January 9, 2017 at 7:40 am
Start by taking out the nolock hints (they allow the query to return incorrect data) and the join hints so that the optimiser is free to choose a good query plan, then if its still slow please post the execution plan, table and index definitions for the table involved.
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
January 9, 2017 at 7:47 am
mishrakanchan86 (1/9/2017)
Hi ALlI have a view which looks something like below.
Select
* from T1 with(NoLock)
Inner Hash Join T2 With (NoLck) on T1.PID = T2.PID
Inner Hash Join T3 With (NoLck) T1.ID = T3.ID and T3.Type= 1
Inner Hash Join T3 With (NoLck) T1.ID = T3.ID and T3.Type= 2
Inner Hash Join T3 With (NoLck) T1.ID = T3.ID and T3.Type= 3
Inner Hash Join T3 With (NoLck) T1.ID = T3.ID and T3.Type= 4
Inner Hash Join T3 With (NoLck) T1.ID = T3.ID and T3.Type= 5
Inner Hash Join T3 With (NoLck) T1.ID = T3.ID and T3.Type= 6
Inner Hash Join T3 With (NoLck) T1.ID = T3.ID and T3.Type= 7
This query returns about 900 records but takes forever to run in one environment. However in another server in takes just 2 seconds and return 900 rows.
Table T3 seems to be the problematic one with 100K records.I have checked that indexes on both are same and also rebuild the
indexes and updated statistics.
This query used to work fine and return results in lst 5-6 seconds and has suddenly degraded so bad
I have experimented a bit seen that till the table T3 has around 35K record the results are returned fast and only after that the exponential degrade on performance has. With 100K the query never return anything.
Agree with Gail's fine replay, further on this, can you post the actual query as the one you posted will never run, missing ON, NoLck is not a table hint option etc.
😎
January 9, 2017 at 8:15 am
The actual query is this.. This is Production so I don't think I can change the query it self. The same query with NoLock and same number of rows works fine in Pre-Prod
SELECT
*
FROM T1 P WITH (NOLOCK)
INNER HASH JOIN dbo.tb_ProductType PT WITH (NOLOCK) ON P.ProductTypeID = PT.ProductTypeID
INNER HASH JOIN T3 pvp1 WITH (NOLOCK) ON pvp1.ParameterID = 96 AND P.ProjectID = pvp1.ProjectID
INNER HASH JOIN T3 pvp2 WITH (NOLOCK) ON pvp2.ParameterID = 98 AND P.ProjectID = pvp2.ProjectID
INNER HASH JOIN T3 pvp3 WITH (NOLOCK) ON pvp3.ParameterID = 149 AND P.ProjectID = pvp3.ProjectID
LEFT HASH JOIN T3 pvp4 WITH (NOLOCK) ON pvp4.ParameterID = 150 AND P.ProjectID = pvp4.ProjectID
LEFT HASH JOIN T3 pvp5 WITH (NOLOCK) ON pvp5.ParameterID = 7 AND P.ProjectID = pvp5.ProjectID
LEFT HASH JOIN T3 pvp6 WITH (NOLOCK) ON pvp6.ParameterID = 178 AND P.ProjectID = pvp6.ProjectID
LEFT HASH JOIN T3 pvp7 WITH (NOLOCK) ON pvp7.ParameterID = 1518 AND P.ProjectID = pvp7.ProjectID
LEFT HASH JOIN T3 pvp8 WITH (NOLOCK) ON pvp8.ParameterID = 94 AND P.ProjectID = pvp8.ProjectID
LEFT HASH JOIN tb_ProcessingCenter PC WITH (NOLOCK) ON PC.ProcessingCenterID = pvp7.ParameterValue
January 9, 2017 at 8:40 am
When you can remove the join hints and get us an execution plan, please post back. Until then, there's nothing we can really do with that query.
Nolock hints mean that SQL is allowed to return incorrect results, duplicates and missing rows. Are your users happy with their results potentially being incorrect
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
January 9, 2017 at 8:47 am
mishrakanchan86 (1/9/2017)
Hi ALlI have a view which looks something like below.
Select
* from T1 with(NoLock)
Inner Hash Join T2 With (NoLck) on T1.PID = T2.PID
Inner Hash Join T3 With (NoLck) T1.ID = T3.ID and T3.Type= 1
Inner Hash Join T3 With (NoLck) T1.ID = T3.ID and T3.Type= 2
Inner Hash Join T3 With (NoLck) T1.ID = T3.ID and T3.Type= 3
Inner Hash Join T3 With (NoLck) T1.ID = T3.ID and T3.Type= 4
Inner Hash Join T3 With (NoLck) T1.ID = T3.ID and T3.Type= 5
Inner Hash Join T3 With (NoLck) T1.ID = T3.ID and T3.Type= 6
Inner Hash Join T3 With (NoLck) T1.ID = T3.ID and T3.Type= 7
This query returns about 900 records but takes forever to run in one environment. However in another server in takes just 2 seconds and return 900 rows.
Table T3 seems to be the problematic one with 100K records.I have checked that indexes on both are same and also rebuild the
indexes and updated statistics.
This query used to work fine and return results in lst 5-6 seconds and has suddenly degraded so bad
I have experimented a bit seen that till the table T3 has around 35K record the results are returned fast and only after that the exponential degrade on performance has. With 100K the query never return anything.
You probably need to pivot the table before joining it. Here's a demonstration on how to do it.
WITH ctePivotedT3 AS(
SELECT ID,
MAX(CASE WHEN Type = 1 THEN SomeColumn END) AS SomeColumnType1,
MAX(CASE WHEN Type = 2 THEN SomeColumn END) AS SomeColumnType2,
MAX(CASE WHEN Type = 3 THEN SomeColumn END) AS SomeColumnType3,
MAX(CASE WHEN Type = 4 THEN SomeColumn END) AS SomeColumnType4,
MAX(CASE WHEN Type = 5 THEN SomeColumn END) AS SomeColumnType5,
MAX(CASE WHEN Type = 6 THEN SomeColumn END) AS SomeColumnType6,
MAX(CASE WHEN Type = 7 THEN SomeColumn END) AS SomeColumnType7
FROM T3
GROUP BY ID
)
SELECT
*
FROM T1
JOIN T2 ON T1.PID = T2.PID
JOIN ctePivotedT3 PT3 ON T1.ID = PT3.ID;
For more information, read the following articles:
January 9, 2017 at 8:47 am
mishrakanchan86 (1/9/2017)
Hi ALlI have a view which looks something like below.
Select
* from T1 with(NoLock)
Inner Hash Join T2 With (NoLck) on T1.PID = T2.PID
Inner Hash Join T3 With (NoLck) T1.ID = T3.ID and T3.Type= 1
Inner Hash Join T3 With (NoLck) T1.ID = T3.ID and T3.Type= 2
Inner Hash Join T3 With (NoLck) T1.ID = T3.ID and T3.Type= 3
Inner Hash Join T3 With (NoLck) T1.ID = T3.ID and T3.Type= 4
Inner Hash Join T3 With (NoLck) T1.ID = T3.ID and T3.Type= 5
Inner Hash Join T3 With (NoLck) T1.ID = T3.ID and T3.Type= 6
Inner Hash Join T3 With (NoLck) T1.ID = T3.ID and T3.Type= 7
This query returns about 900 records but takes forever to run in one environment. However in another server in takes just 2 seconds and return 900 rows.
Table T3 seems to be the problematic one with 100K records.I have checked that indexes on both are same and also rebuild the
indexes and updated statistics.
This query used to work fine and return results in lst 5-6 seconds and has suddenly degraded so bad
I have experimented a bit seen that till the table T3 has around 35K record the results are returned fast and only after that the exponential degrade on performance has. With 100K the query never return anything.
1) Be VERY careful removing the NOLOCKs if you are running this in a production environment. That can wind up completely locking out all tables involved from any INSERT/DELETE/UPDATE activity. If the potential for bad data is unacceptable but you don't want to block concurrent activity you may have to run this in off-hours or a separate copy of the database used for reporting.
2) You stated the indexes on the join tables are the same. If by that you mean they are clustered on the same column then the HASH joins are REALLY REALLY inefficient and the available MERGE joins could be substantially faster.
3) Do you REALLY need EVERY COLUMN from EVERY TABLE?!?!
4) Forcing things (join types in this case) is often a very suboptimal thing to do unless a) you REALLY know what you are doing and b) things don't change from when you did the forcing. And here you definitely have a change - rowcounts increased by 3X on one table (with unknown changes on all the others). Like Gail said, take off the join hints and see how the query plays out.
5) HASH joins (large queries in general) are very sensitive to CPU ticks available (usually not a problem except in poorly virtualized environments) and RAM (VERY often a problem in my experience, especially in virtualized environments). Lack of available RAM means those HASH joins will spool down to tempdb, which is 5 to 8 ORDERS OF MAGNITUDE slower than RAM. I am exceptionally good at tuning SQL Server and have been consulting on it for 20+ years. But I have rejected requests from potential clients to help them until they increased the RAM on their SQL Servers. I can't do anything that touches that level of improvement across the board. That should tell you all you need to know about how important this issue is. 😎
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
January 9, 2017 at 9:04 am
Luis Cazares (1/9/2017)
mishrakanchan86 (1/9/2017)
Hi ALlI have a view which looks something like below.
Select
* from T1 with(NoLock)
Inner Hash Join T2 With (NoLck) on T1.PID = T2.PID
Inner Hash Join T3 With (NoLck) T1.ID = T3.ID and T3.Type= 1
Inner Hash Join T3 With (NoLck) T1.ID = T3.ID and T3.Type= 2
Inner Hash Join T3 With (NoLck) T1.ID = T3.ID and T3.Type= 3
Inner Hash Join T3 With (NoLck) T1.ID = T3.ID and T3.Type= 4
Inner Hash Join T3 With (NoLck) T1.ID = T3.ID and T3.Type= 5
Inner Hash Join T3 With (NoLck) T1.ID = T3.ID and T3.Type= 6
Inner Hash Join T3 With (NoLck) T1.ID = T3.ID and T3.Type= 7
This query returns about 900 records but takes forever to run in one environment. However in another server in takes just 2 seconds and return 900 rows.
Table T3 seems to be the problematic one with 100K records.I have checked that indexes on both are same and also rebuild the
indexes and updated statistics.
This query used to work fine and return results in lst 5-6 seconds and has suddenly degraded so bad
I have experimented a bit seen that till the table T3 has around 35K record the results are returned fast and only after that the exponential degrade on performance has. With 100K the query never return anything.
You probably need to pivot the table before joining it. Here's a demonstration on how to do it.
WITH ctePivotedT3 AS(
SELECT ID,
MAX(CASE WHEN Type = 1 THEN SomeColumn END) AS SomeColumnType1,
MAX(CASE WHEN Type = 2 THEN SomeColumn END) AS SomeColumnType2,
MAX(CASE WHEN Type = 3 THEN SomeColumn END) AS SomeColumnType3,
MAX(CASE WHEN Type = 4 THEN SomeColumn END) AS SomeColumnType4,
MAX(CASE WHEN Type = 5 THEN SomeColumn END) AS SomeColumnType5,
MAX(CASE WHEN Type = 6 THEN SomeColumn END) AS SomeColumnType6,
MAX(CASE WHEN Type = 7 THEN SomeColumn END) AS SomeColumnType7
FROM T3
GROUP BY ID
)
SELECT
*
FROM T1
JOIN T2 ON T1.PID = T2.PID
JOIN ctePivotedT3 PT3 ON T1.ID = PT3.ID;
For more information, read the following articles:
Or build a cross-tab into an APPLY block:
SELECT *
FROM T1 P
INNER JOIN dbo.tb_ProductType PT
ON P.ProductTypeID = PT.ProductTypeID
OUTER APPLY ( -- set up a crosstab query here
SELECT
something = MAX(CASE WHEN pvp1.ParameterID = 7 THEN ...),
something_else = MAX(CASE WHEN pvp1.ParameterID = 94 THEN ...)
.
.
FROM T3 pvp1
WHERE P.ProjectID = pvp1.ProjectID AND pvp1.ParameterID IN (7,94,96,98,149,150,1518)
) x
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
January 9, 2017 at 9:07 am
I agree that the join hints should only be used if you truly understand them.
The critical thing would seem to be that "T3" should almost certainly be clustered on ( ParameterID, ProjectID ). If it's not, if it's clustered on something instead like, say, an identity column, then change the clustering on T3. Then you can safely get rid of the HASH hints on the T3 joins at least.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
January 9, 2017 at 9:40 am
ChrisM@Work (1/9/2017)
Or build a cross-tab into an APPLY block:
I usually avoid APPLY with correlated suubqueries because it tends to run the query row by row. Obviously, this is a general observation and not something bad on itself.
January 9, 2017 at 9:58 am
Luis Cazares (1/9/2017)
ChrisM@Work (1/9/2017)
Or build a cross-tab into an APPLY block:
I usually avoid APPLY with correlated suubqueries because it tends to run the query row by row. Obviously, this is a general observation and not something bad on itself.
It's so fast to code both that I tend to do exactly that, and compare plans and timings.
In this exact scenario, APPLY touches only the required rows. There may be a benefit.
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
January 9, 2017 at 12:02 pm
1. When I say indexes are the same - Prod and Pre- prod have same indexes
2. No i dont need ever column, I .am actually selecting on 6- 7 columns. Here in forum I dint want to be specific so used *
3. I used this query to check fragmentation and i think the results but weird. Let me know if u think the same
SELECT OBJECT_NAME(OBJECT_ID), index_id,index_type_desc,index_level,
avg_fragmentation_in_percent,avg_page_space_used_in_percent,page_count
FROM sys.dm_db_index_physical_stats
(DB_ID(N'IMMS'), object_id('T3'), NULL, NULL , 'SAMPLED')
ORDER BY avg_fragmentation_in_percent DESC
(No column name)index_idindex_type_descindex_levelavg_fragmentation_in_percentavg_page_space_used_in_percentpage_count
tb_ParameterValueProject8NONCLUSTERED INDEX099.78902953586599.4587101556709474
tb_ParameterValueProject6NONCLUSTERED INDEX030.344827586206999.95969854213290
tb_ParameterValueProject1CLUSTERED INDEX 03.7457434733257799.5302693353101881
tb_ParameterValueProject2NONCLUSTERED INDEX0099.6541759327897238
tb_ParameterValueProject3NONCLUSTERED INDEX0099.6541759327897238
tb_ParameterValueProject4NONCLUSTERED INDEX0099.6541759327897238
tb_ParameterValueProject5NONCLUSTERED INDEX0099.4587101556709474
tb_ParameterValueProject7NONCLUSTERED INDEX0099.6972819372374420
January 9, 2017 at 12:37 pm
1. When I say indexes are the same - Prod and Pre- prod have same indexes
2. No i dont need ever column, I .am actually selecting on 6- 7 columns. Here in forum I dint want to be specific so used *
3. I used this query to check fragmentation and i think the results but weid. Let me know if u think the same
SELECT OBJECT_NAME(OBJECT_ID), index_id,index_type_desc,index_level,
avg_fragmentation_in_percent,avg_page_space_used_in_percent,page_count
FROM sys.dm_db_index_physical_stats
(DB_ID(N'IMS'), object_id('T3'), NULL, NULL , 'SAMPLED')
ORDER BY avg_fragmentation_in_percent DESC
(No column name)index_idindex_type_descindex_levelavg_fragmentation_in_percentavg_page_space_used_in_percentpage_count
T3 8NONCLUSTERED INDEX099.78902953586599.4587101556709474
T3 6NONCLUSTERED INDEX030.344827586206999.95969854213290
T3 1CLUSTERED INDEX03.7457434733257799.5302693353101881
T3 2NONCLUSTERED INDEX0099.6541759327897238
T3 3NONCLUSTERED INDEX0099.6541759327897238
T3 4NONCLUSTERED INDEX0099.6541759327897238
T3 5NONCLUSTERED INDEX0099.4587101556709474
T3 7NONCLUSTERED INDEX0099.6972819372374420
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply