September 5, 2014 at 12:32 pm
Hi, am new to sql troubleshooting......Our Dev team is running the below sql script from 2 views dim.cvDimPurchaseOrder1 & dim.cvDimPurchaseOrderLineItem1 in XYZ db
SELECT TOP 100 * FROM XYZ.DBO.Test_GL
INNER JOIN dim.cvDimPurchaseOrder1 PO ON (GL.PURCHASE_ORDER_NUMBER = PO.PONumber)
INNER JOIN dim.cvDimPurchaseOrderLineItem1 POL on (gl.PURCHASE_ORDER_LINE_BUS_KEY = POL.POLineBusinessKey)
WHERE GL.GR_IR_FLAG = 'Y'
The issue is the query executes in 3-4seconds in Dev and takes 40seconds in Test server...can you please let me know how to proceed on this.
September 5, 2014 at 12:35 pm
Assuming the schema are identical it is most likely going to be fragmented indexes or stale statistics. Another option is it might data quantity of data revealing a nonperformant query.
What would help is to see the actual execution plans from both servers. Also table and index definitions would be a big help.
_______________________________________________________________
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/
September 6, 2014 at 3:49 am
Quick question, can you post the two execution plans?
😎
September 6, 2014 at 4:36 am
Validate that the two systems are the same in every possible way because even small differences could lead to major performance changes. But, I suspect it's probably related to statistics as was already suggested.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 10, 2014 at 8:02 am
Hi,
Thanks everyone who replied. I actually looked at execution plans and compared on dev n Stg and found there were some indexes missing on Stg...created them now..looks okay..but what else should I be looking for...say if indexes were all present?
September 10, 2014 at 8:40 am
Statistics would be the next thing to check. After that, the execution plan. After that, whatever the execution plan shows.
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply