June 28, 2010 at 8:46 am
HI i got 2 servers. Server 1 and Server 2.
Server 1 runs with lot of backend applications
server 2 with reporting server applications and others.
I got enough Ram and pretty much both the servers configurations are same.
I have a stored procedure from a particular database when i run this stored procedure it takes almost 10+ min to exec on server 1 and it just takes 30sec on server 2.
I made my own investigation i found couple of changes in execution plan i saw in some areas server 1 is doing Clustered scan where it was a Clustered seek on server2.
Clustered scan and Clustered seek on both servers are performed on a particular view. When i open this particular view and ran separately i saw it takes 1:45sec on Server1 and just 25sec on server 2 and even the execution plan for both is different.
According to my guess it particular view is called a couple of times in my stored procedure and it's killing my performance. I verified the code and each and every corner .. everything looks same on both databases. i don't know whats wrong. Please give me some ideas or suggestions. I have attached the Stored procedure, function and view for reference.
Thanks
June 28, 2010 at 8:52 am
Have you tried a statistics update?
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
June 28, 2010 at 8:54 am
Post both execution plans.
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
June 28, 2010 at 8:54 am
Please post execution plan and DDL as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
June 28, 2010 at 8:59 am
In addition to what's been asked, are the data sets the same? Same sizes/counts on the tables?
June 28, 2010 at 9:04 am
Ya I updated the stats and i guess everything is same? Here are execution plans for procedure and view on both servers.
June 28, 2010 at 9:08 am
You sure about the stats ?
The table STOREROOM has an estimated row count of 7 but actual of 250,000+.
Update the statistics and issue sp_recompile "STOREROOM", to force the plan out of the cache.
Then retry...
Let us know, how things go.
June 28, 2010 at 9:09 am
sam-1083699 (6/28/2010)
Here are execution plans for procedure and view on both servers.
Which is fast, which is slow?
Is the procedure slow or are adhod queries of the view 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
June 28, 2010 at 9:10 am
Dave Ballantyne (6/28/2010)
Update the statistics and issue sp_recompile "STOREROOM", to force the plan out of the cache.
No need for the recompile, the stats update will invalidate the plan.
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
June 28, 2010 at 9:15 am
Both view and stored procedure is slow. Exec plans with AX are slow and J or Jasom are fast. As i said this view has called in that stored procedure.
View takes 1:45sec on Server1 (AX) and just 25sec on server 2 (Jasom) .
June 28, 2010 at 9:24 am
Dave Ballantyne (6/28/2010)
The table STOREROOM has an estimated row count of 7 but actual of 250,000+.
That's a correct estimate.
7 rows (estimated) * 36876 (number of executions) = 258132 (actual rows)
http://sqlinthewild.co.za/index.php/2009/09/22/estimated-rows-actual-rows-and-execution-count/
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
June 28, 2010 at 9:25 am
Sure about that Gail ?
Difference in 2005 / 2008 Perhaps ?
This profiler trace screen shot is 2005.
Doesnt Look like the update statistics has invalidated the plan.
June 28, 2010 at 9:27 am
GilaMonster (6/28/2010)
Dave Ballantyne (6/28/2010)
The table STOREROOM has an estimated row count of 7 but actual of 250,000+.That's a correct estimate.
7 rows (estimated) * 36876 (number of executions) = 258132 (actual rows)
http://sqlinthewild.co.za/index.php/2009/09/22/estimated-rows-actual-rows-and-execution-count/
Missed that that was a nested loop :doze:
June 28, 2010 at 9:29 am
Sam, first thing I'll suggest is that you remove all of your join hints from the query, then see how it performs on the two servers.
Also, fix the EXISTS, there's no point in aggregation and group bys in an EXISTS, it doesn't return any data, just checks whether or not there are rows.
EXISTS ( SELECT
MIN(INVTRANS_ISC_vw.introi) First_Right_TransOI
FROM
dbo.INVTRANS_ISC_vw INVTRANS_ISC_vw
WHERE
INVTRANS_ISC_vw.costbefore_amt IS NOT NULL AND
INVTRANS_ISC_vw.posted=1
AND dbo.Get_LocalDateTime(INVTRANS_ISC_vw.trnspd_dttm,'EST') >= @BalanceInquiryDate
AND INVTRANS_ISC_vw.stor_oi = INVTRANS.stor_oi
AND INVTRANS_ISC_vw.item_oi = INVTRANS.item_oi
AND INVTRANS_ISC_vw.invcls_oi = INVTRANS.invcls_oi
--AND INVTRANS_ISC_vw.introi = INVTRANS.introi
GROUP BY
INVTRANS_ISC_vw.item_oi,
INVTRANS_ISC_vw.stor_oi,
INVTRANS_ISC_vw.invcls_oi
With
EXISTS ( SELECT 1
FROM
dbo.INVTRANS_ISC_vw INVTRANS_ISC_vw
WHERE
INVTRANS_ISC_vw.costbefore_amt IS NOT NULL AND
INVTRANS_ISC_vw.posted=1
AND dbo.Get_LocalDateTime(INVTRANS_ISC_vw.trnspd_dttm,'EST') >= @BalanceInquiryDate
AND INVTRANS_ISC_vw.stor_oi = INVTRANS.stor_oi
AND INVTRANS_ISC_vw.item_oi = INVTRANS.item_oi
AND INVTRANS_ISC_vw.invcls_oi = INVTRANS.invcls_oi
--AND INVTRANS_ISC_vw.introi = INVTRANS.introi
)
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
June 28, 2010 at 9:33 am
Shall i update the stats with no recomplie ? or just update stats?
Viewing 15 posts - 1 through 15 (of 29 total)
You must be logged in to reply to this topic. Login to reply