Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

SQL Server Puzzling Performance Expand / Collapse
Author
Message
Posted Friday, October 25, 2013 6:36 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 5:46 AM
Points: 2,443, Visits: 7,557
From what I can tell after a quick look, your issue is that with the lower memory setting SQL Server decides to do a hash match, whereas with more memory it correctly decides to do a nested loop. I suspect that your index is massively fragmented, so when it does the order for the nested loop it spills to disk which would consume a massive amount of CPU. I also dislike the amount of implicit conversions that are going on in your query, you're forcing the optimizer to do more work than is necessary.

Really, you could do with Gail taking a look but with the amount of information you've added to this post, I think it's unlikely she'd stop by

A couple of bits of advice: -
1. Have a read through this article (http://www.sqlservercentral.com/articles/SQLServerCentral/66909/) on how to post performance issues. It'll explain what information is required for people to help you.
2. Formatting your code when posting it online is a godsend. What you've posted is a bit of a mess which makes it difficult to see what is going on. If we instead do it like this: -
SELECT t11.DocNum,
convert(NVARCHAR, t11.DocDate, 103),
t11.Filler AS 'Origin W/H',
t11.U_RecWshe AS 'Final W/H',
t12.ItemCode,
t12.Dscription,
(SELECT t13.ItmsGrpNam
FROM OITB t13, OITM t14
WHERE t13.ItmsGrpCod = t14.ItmsGrpCod
AND t14.ItemCode = t12.ItemCode) AS 'Item Group',
t12.Quantity
FROM OWTR t11, WTR1 t12
WHERE t11.DocEntry = t12.DocEntry
AND t11.DocNum IN (SELECT t0.DocNum
FROM OWTR t0
WHERE t0.U_TrnType = 1
AND t0.DocDate <= '2012/12/31'
AND t0.U_EType = 0
AND CONVERT(NVARCHAR, t0.DocNum) NOT IN (SELECT CONVERT(NVARCHAR, ISNULL(t11.U_TrnDocNo, ''))
FROM OWTR t11)
);

It becomes much more obvious what is going on.

After formatting it, I'd immediately stop doing the old style joins that you have going on in your query and start using the "new" (the old style for outer joins was deprecated in SQL Server 2005).

SELECT t11.DocNum,
convert(NVARCHAR, t11.DocDate, 103),
t11.Filler AS 'Origin W/H',
t11.U_RecWshe AS 'Final W/H',
t12.ItemCode,
t12.Dscription,
(SELECT t13.ItmsGrpNam
FROM OITB t13, OITM t14
WHERE t13.ItmsGrpCod = t14.ItmsGrpCod
AND t14.ItemCode = t12.ItemCode) AS 'Item Group',
t12.Quantity
FROM OWTR t11
INNER JOIN WTR1 t12 ON t11.DocEntry = t12.DocEntry
WHERE t11.DocNum IN (SELECT t0.DocNum
FROM OWTR t0
WHERE t0.U_TrnType = 1
AND t0.DocDate <= '2012/12/31'
AND t0.U_EType = 0
AND CONVERT(NVARCHAR, t0.DocNum) NOT IN (SELECT CONVERT(NVARCHAR, ISNULL(t11.U_TrnDocNo, ''))
FROM OWTR t11)
);

The next thing I'd want to do is move the Item Group into an APPLY.
SELECT t11.DocNum,
convert(NVARCHAR, t11.DocDate, 103),
t11.Filler AS 'Origin W/H',
t11.U_RecWshe AS 'Final W/H',
t12.ItemCode,
t12.Dscription,
oa.[Item Group],
t12.Quantity
FROM OWTR t11
INNER JOIN WTR1 t12 ON t11.DocEntry = t12.DocEntry
OUTER APPLY (SELECT t13.ItmsGrpNam
FROM OITB t13
INNER JOIN OITM t14 ON t13.ItmsGrpCod = t14.ItmsGrpCod
WHERE t14.ItemCode = t12.ItemCode) oa([Item Group])
WHERE t11.DocNum IN (SELECT t0.DocNum
FROM OWTR t0
WHERE t0.U_TrnType = 1
AND t0.DocDate <= '2012/12/31'
AND t0.U_EType = 0
AND CONVERT(NVARCHAR, t0.DocNum) NOT IN (SELECT CONVERT(NVARCHAR, ISNULL(t11.U_TrnDocNo, ''))
FROM OWTR t11)
);

Next, I'd get rid of the implicit conversions.
SELECT t11.DocNum,
convert(NVARCHAR, t11.DocDate, 103),
t11.Filler AS 'Origin W/H',
t11.U_RecWshe AS 'Final W/H',
t12.ItemCode,
t12.Dscription,
oa.[Item Group],
t12.Quantity
FROM OWTR t11
INNER JOIN WTR1 t12 ON t11.DocEntry = t12.DocEntry
OUTER APPLY (SELECT t13.ItmsGrpNam
FROM OITB t13
INNER JOIN OITM t14 ON t13.ItmsGrpCod = t14.ItmsGrpCod
WHERE t14.ItemCode = t12.ItemCode) oa([Item Group])
WHERE t11.DocNum IN (SELECT CAST(t0.DocNum AS NVARCHAR(30))
FROM OWTR t0
WHERE t0.U_TrnType = '1'
AND t0.DocDate <= '2012/12/31'
AND t0.U_EType = '0'
AND CONVERT(NVARCHAR, t0.DocNum) NOT IN (SELECT CONVERT(NVARCHAR, ISNULL(t11.U_TrnDocNo, ''))
FROM OWTR t11)
);

I'd consider the following index: -
/*
The Query Processor estimates that implementing the following index could improve the query cost by 41.9829%.

WARNING: This is only an estimate, and the Query Processor is making this recommendation based solely upon analysis
of this specific query. It has not considered the resulting index size, or its workload-wide impact, including its
impact on INSERT, UPDATE, DELETE performance. These factors should be taken into account before creating this index.
*/
CREATE NONCLUSTERED INDEX [SQL_SERVER_SUGGESTED_INDEX] ON [dbo].[OWTR] ([DocDate]) INCLUDE ([DocNum],[U_TrnType],[U_EType]);




Not a DBA, just trying to learn

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better, quicker answers on SQL Server performance related questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/



If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...

(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock


LinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #1508434
Posted Friday, October 25, 2013 6:46 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Thursday, August 7, 2014 2:08 AM
Points: 4,432, Visits: 4,171
Obviously, adding more memory results in a different, less efficient plan.

Could you try what happens if you simulate even more memory, like let's say 16Gb.
You can do this by using DBCC OPTIMIZER_WHATIF

DBCC TRACEON(3604) WITH NO_INFOMSGS
DBCC OPTIMIZER_WHATIF(MemoryMbs,16000) WITH NO_INFOMSGS;
GO
Run your Query now.

For more info on this undocumented DBCC command see here:
https://www.simple-talk.com/sql/database-administration/using-optimizer_whatif-and-statsstream-to-simulate-a-production-environment/


Markus Bohse
Post #1508438
Posted Friday, October 25, 2013 6:58 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 12:26 AM
Points: 2,840, Visits: 3,963
Worth reading here http://www.dbsophic.com/SQL-Server-Articles/sql-server-logical-reads-what-they-really-tell.html

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done
Post #1508444
Posted Monday, November 4, 2013 1:48 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, July 17, 2014 2:56 PM
Points: 13, Visits: 45
Hi Guys,

I can say that after trying to resolve this issue by optimizing my code and checking my server configuration i ended up refering the issue to MS support and they found the solution in a couple of days. See below.

Thank you guys for the time you put in to help out. Your the best

Regards
FK

Hi Francis,

I did some research on this issue and found that it’s not a very common behavior of optimizer but we see this often with specific queries.

When optimizer compiles for a plan, various factors such as memory, CPU and cardinality are used to determine the most effective plan.
There have been a few cases where inefficient query plan results from large amount of memory, though it is very rare.
In these cases, what we experience is that if we reduce max server memory setting, the query runs faster.
So, the issue faced by you is very much similar and the point where its suddenly changing the plan is something we refer as “Tipping point”.

Recommendation:
=================
You can use trace flag 2335 to see if the problem is resolved.
This trace flag 2335 assumes SQL Server only has 1GB of memory available for query execution, and uses this memory setting to generate the query plan.
This will results in less memory intensive query plans such as nested loops.

Note: Setting this trace flag only affects optimizer for plan generation. It does not limit SQL Server from using all available memory for data buffers, query executions, etc.

We have a KB article http://support.microsoft.com/kb/2413549 documenting this.

Please let me know if this helps in fixing the issue.
Waiting for your findings so that we can move to next level of troubleshooting.


This did not work until I added this other trace flag as per their email below.


There is one more trace flag 4199 which is for implementing all optimizer fixes introduced so far.
Could you please implement both these trace flags in your SQL server startup parameter and then restart the SQL server services.

Please ensure that the trace flags are enabled from SQL server errorlog.
Let me know how this go.
Post #1511004
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse