April 14, 2014 at 2:20 am
GilaMonster (4/14/2014)
So the query, when run in production, returns 0 rows?
Not everytime that is the problem.
Most of the time it is some millisecond but some time it is nearly 5 sec to 10 sec.
April 14, 2014 at 2:23 am
yuvipoy (4/14/2014)
GilaMonster (4/14/2014)
So the query, when run in production, returns 0 rows?Not everytime that is the problem.
Most of the time it is some millisecond but some time it is nearly 5 sec to 10 sec.
I didn't ask about the time.
The query returns 0 rows (empty resultset) in production? True or false?
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
April 14, 2014 at 2:34 am
No it will run it will not return 0 rows
Table1 44 rows
Table2 500 rows
April 14, 2014 at 2:39 am
Well the execution plan you posted was from a query which returned 0 rows. If the query you want help with doesn't return 0 rows, then please post an execution plan of the query which is performing badly as it actually runs in production (i.e. not returning 0 rows), and from an execution which is slow.
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
April 14, 2014 at 3:02 am
attached latest query execution plan
Hope this will help me finally.
April 14, 2014 at 3:18 am
That's an estimated plan, it has no actual row counts or values, and it doesn't even load into Management Studio
TITLE: Microsoft SQL Server Management Studio
------------------------------
Error loading execution plan XML file C:\Users\REDACTED\Downloads\plan.sqlplan_1.sqlplan. (SQLEditors)
------------------------------
ADDITIONAL INFORMATION:
There is an error in XML document (6, 155). (System.Xml)
------------------------------
'StatementText' is an unexpected token. The expected token is '"' or '''. Line 6, position 155. (System.Xml)
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
April 14, 2014 at 3:32 am
Updated.i am able to load
April 14, 2014 at 3:43 am
It's still an estimated plan. Please can you post the actual execution plan of an execution of that query which is slow
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
April 14, 2014 at 4:12 am
Here we go.. Run the query with ctrl+m(Include Actual Execution Plan)
April 14, 2014 at 4:16 am
I'm done.
TITLE: Microsoft SQL Server Management Studio
------------------------------
Error loading execution plan XML file C:\Users\REDACTED\Downloads\plan.sqlplan_1.sqlplan. (SQLEditors)
------------------------------
ADDITIONAL INFORMATION:
There is an error in XML document (599, 33). (System.Xml)
------------------------------
The 'Sort' start tag on line 310 does not match the end tag of 'NestedLoops'. Line 599, position 33. (System.Xml)
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
April 14, 2014 at 4:24 am
Gail Really thanks for your patient ,but i am able to load here in my SSMS 2008
April 14, 2014 at 6:43 am
Estimated cost operation is 95% on a single statement in the overall execution plan.
April 14, 2014 at 7:30 am
Reformatted your code and made some annotations. Be sure to read them.
SELECT
M.*,
COL1, -- Based on the DDL you provided all of these columns are in TABLE1
COL2, -- Which table are they actually in? Since this Query is joining 2 tables
COL3, -- you should prefix ALL columns with their appropriate table alias.
COL9,
COL4,
COL5,
COL6,
COL7,
COL8,
COL10,
COL12,
COL11,
COL13
FROM
--TABLE1 M (NOLOCK), -- Use of NOLOCK is HIGHLY Suspect and can provide erronous data
--TABLE2 OM (NOLOCK) -- including missing and/or duplicated data.
TABLE1 M
inner join TABLE2 OM -- This is an ANSI-92 STYLE join, and is recommended
on (M.COL1 = OM.COL1) -- Problem here, your table defs don't have a COL1 in TABLE2,
-- all columns in the provided DDL for this table are prefixed with T
WHERE
M.COL3 = '{33F3A7A0-C455-49E8-ACD2-0008694F72CD}'
ORDER BY
M.COL4,
M.COL7,
M.COL19,
M.COL14,
M.COL11 DESC,
M.COL10,
M.COL15 DESC,
COL2, -- Again, which table? See note above
COL6,
COL4,
COL5;
I was able to load your last execution plan into Plan Explorer. Looks like you aren't giving us everything. Plan Explorer shows two indexes, IX_TABLE1_DATE and IX_TABLE2_DATE, but I don't see any indexes in the DDL you provided with these names. In fact, none of the index names match what you provided. Column names don't match either.
I understand the obfuscation but you seem to have taken it to such an extreme that it is difficult to even try and help. About the only thing that I can tell you at this point is that your indexing scheme does not appear to help this particular query.
You have some work to do reevaluating the code in the query and the indexing. Not much more I can do until you can provide DDL, sample data, and expected results based on the sample data that at least mirrors your production system better.
You say the query returns in millisecond time some times but in others 5 to 10 seconds. How often is this query run during the day?
April 14, 2014 at 8:42 am
FROM
--TABLE1 M (NOLOCK), -- Use of NOLOCK is HIGHLY Suspect and can provide erronous data
--TABLE2 OM (NOLOCK) -- including missing and/or duplicated data.
TABLE1 M
inner join TABLE2 OM -- This is an ANSI-92 STYLE join, and is recommended
on (M.COL1 = OM.COL1) -- Problem here, your table defs don't have a COL1 in TABLE2,
-- all columns in the provided DDL for this table are prefixed with T
Where as in real statement it is as
FROM TABLE1 M (NOLOCK), TABLE2 OM (NOLOCK) WHERE (M.COL1 = OM.TCOL1)
so these two statements (yours and mine) are not same?
have added table definition as well as data and execution plan.
April 14, 2014 at 9:23 am
yuvipoy (4/14/2014)
FROM
--TABLE1 M (NOLOCK), -- Use of NOLOCK is HIGHLY Suspect and can provide erronous data
--TABLE2 OM (NOLOCK) -- including missing and/or duplicated data.
TABLE1 M
inner join TABLE2 OM -- This is an ANSI-92 STYLE join, and is recommended
on (M.COL1 = OM.COL1) -- Problem here, your table defs don't have a COL1 in TABLE2,
-- all columns in the provided DDL for this table are prefixed with T
Where as in real statement it is as
FROM TABLE1 M (NOLOCK), TABLE2 OM (NOLOCK) WHERE (M.COL1 = OM.TCOL1)
so these two statements (yours and mine) are not same?
have added table definition as well as data and execution plan.
What I am telling you is that you should start using the ANSI-92 style joins instead of ANSI-89 style joins. When you start using OUTER JOINs in your queries you will need to use the ANSI-92 style. You might as well start getting used to using them. Also, separating the join criteria from the filter criteria also makes reading the code easier.
Viewing 15 posts - 31 through 45 (of 52 total)
You must be logged in to reply to this topic. Login to reply