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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
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