SP Performance Difference
-
Hi,
We have recently migrated from SQL 2000 to SQL 2005, and as part of this we rebuilt all indexes and updated all statistics after setting the compatibility level to 90.
I would have expected performance to be at least as good as it was on 2000, however a particular SP has gone from taking approximately 6 seconds on 2000 to 70+ seconds on 2005 when called through our application, but the exact same call using SSMS with identical parameters (as proven with SQL Profiler) takes around 200ms to complete.
Also, when comparing the two calls in Profiler, the following differences in statistics are displayed:
Application Call
CPU 68578
Reads 15712877
Duration 70019
SSMS Call
CPU 125
Reads 650
Duration 223
I'd be grateful if anyone's got any ideas as to why this SP should perform so badly when called through our application?
-
Is your application passing the parameters in a way that might fool SQL server into thinking that a decimal is an integer (for example). To clarify, suppose that one of the columns that's queried has been defined as decimal. If your proc parameter for this particular column is defined as a different type (int for example), SQL server might not use an available index.
Regards,Carlos
-
Have a look at the exec plan for both, see what the differences are.
In management studio, just run with the execution plan option on. for the application run one, you can get the plan from the proc cache with the sys.dm_exec_query_plan dmv.
If you know the spid that the app's using then you can run the following
SELECT
* FROM sys.dm_exec_requests er CROSS APPLY sys.dm_exec_query_plan(er.plan_handle)
WHERE er.session_id = @AppSpid
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
also search your xml-execution plan for "CONVERT_IMPLICIT" . This can ruin your execution !
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Ok, I've tried dm_exec_requests, but unfortunately can't run it quick enough to capture the fast-performing instance of the query. However, this should come in useful in future!!
I've re-run profiler and captured the xml-execution plan for both queries and there are differences, the main ones being the indexes used on the two largest tables in the query. Is there a logical reason why the optimizer would use different plans when called from within Crystal Report and from within SSMS?
To rule out Crystal as the cause, we have developed a test procedure that simply fires an SQL command, and we have set this to fire the exact code as copied from Profiler. The results of this are that the SP still takes 70 seconds when called from a .NET environment and a fifth of a second when called from SSMS.
I've checked for CONVERT_IMPLICIT in the xml-execution plan and can't see this anywhere. Am I right in thinking that you would see this if the data-type of the parameter differs from the data-type of the field you're comparing it to? If so, I learned a harsh lesson some time back with this one so it's one of the first things I check for!
Thanks for your help so far guys
-
It could be parameter sniffing. I wouldn't think so if you used the proc in SSMS, but is a possibility.
Can you post the proc, the code you used to call it from .net and the sections of the exec plan that differ? It would really help in getting to the bottom of the problem.
Do the connection settings differ from the .net test and management studio? (ansi nulls, etc)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Thanks Gail, following should be everything you've asked for but please let me know if I've missed anything.
-------------------------------------------
Stored Procedure
CREATE PROCEDURE [dbo].[usp_Report_010810_GrossProfit_ProductDetailed_ByDeliveryDepot]
@chvStartDepot VARCHAR(4),
@chvEndDepot VARCHAR(4),
@dtsStartDate DATETIME,
@dtsEndDate DATETIME,
@chvStartProduct VARCHAR(7),
@chvEndProduct VARCHAR(7)
AS
SET NOCOUNT ON
SELECT Invoice.LoadingDepot,
InvoiceItem.Product,
Product.Description,
Invoice.InvoiceNo,
Invoice.ThirdPartyOrder,
Invoice.AccountNo,
ISNULL(Account.Name,'') AS Name,
CONVERT(NVARCHAR,Invoice.InvoiceDate,103) AS InvoiceDate,
SUM(InvoiceItem.[Value]) AS TotalInvoiceValue,
SUM(ROUND(InvoiceItem.CostPrice * InvoiceItem.Quantity,2)) AS TotalInvoiceCost,
SUM(InvoiceItem.Quantity) AS Volume,
(SUM(InvoiceItem.[Value]) - SUM(ROUND(InvoiceItem.CostPrice * InvoiceItem.Quantity,2))) AS GrossProfit,
CONVERT(DECIMAL(10,2), CASE WHEN SUM(InvoiceItem.[Value]) = 0 THEN 0 ELSE ROUND(((SUM(InvoiceItem.[Value]) - SUM(ROUND(InvoiceItem.CostPrice * InvoiceItem.Quantity,2))) / SUM(InvoiceItem.[Value])) * 100,2) END) AS GrossProfitPercent,
CONVERT(DECIMAL(10,4), CASE WHEN SUM(InvoiceItem.Quantity) = 0 THEN 0 ELSE ROUND((SUM(InvoiceItem.[Value]) - SUM(ROUND(InvoiceItem.CostPrice * InvoiceItem.Quantity,2))) / SUM(InvoiceItem.Quantity),4) END) AS MarginPerLitre
FROM Invoice WITH (NOLOCK)
INNER JOIN InvoiceItem WITH (NOLOCK) ON Invoice.InvoiceNo = InvoiceItem.InvoiceNo AND Invoice.AccountType = InvoiceItem.AccountType
INNER JOIN Product WITH (NOLOCK) ON InvoiceItem.Product = Product.Product
LEFT OUTER JOIN Account WITH (NOLOCK) ON Invoice.AccountNo = Account.AccountNo AND Invoice.AccountType = Account.AccountType
WHERE Invoice.LoadingDepot BETWEEN @chvStartDepot AND @chvEndDepot
AND Invoice.InvoiceDate BETWEEN @dtsStartDate AND @dtsEndDate
AND InvoiceItem.Product BETWEEN @chvStartProduct AND @chvEndProduct
GROUP BY Invoice.LoadingDepot,
InvoiceItem.Product,
Product.Description,
Invoice.InvoiceNo,
Invoice.ThirdPartyOrder,
Invoice.AccountNo,
Account.Name,
Invoice.InvoiceDate
ORDER BY Invoice.LoadingDepot,
InvoiceItem.Product,
Invoice.InvoiceNo
GO
------------------------------------------
VB.NET Code
Public Function getDataSetStr(ByVal strQuery As String) As Data.DataSet
Dim oDataAdapter = New SqlDataAdapter(strQuery, oConnection)
Dim ds = New DataSet
Try
'Fill the DataSet with the results of the query.
oDataAdapter.Fill(ds)
Catch oException As Exception
Throw oException
Finally
oConnection.Close()
oDataAdapter = Nothing
oConnection = Nothing
End Try
getDataSetStr = ds
End Function
-----------------------------------------------
Appliaction Connection string
“Data Source=TEDEM2\TEDEM2; Network Library=dbnmpntw; Initial Catalog=eM2; Integrated Security=SSPI”
----------------------------------------
Database Properties
--------------------------------------------------
SSMS Connection Properties
---------------------------------------------------
Execution plans (NB Both execution plans are included in the same image with the 1st execution plan being the one that performs slowly. As the image was so large I excluded the 3 steps to the left that were identical)
-
Can't see the images, because they're on your machine. Could you paste the differeing lines of the exec plan, as well as the query that you called the .net with?
Thanks
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Sorry Gail, I know I'm being dense but how do I paste the differing lines, as they're shown as a graphic in Profiler and I can't find a way to extract them as text?!
The query is consistently executed as follows:
exec "database"."dbo"."usp_Report_010810_GrossProfit_ProductDetailed_ByDeliveryDepot";1 'ETFJ', 'ETFJ', '', 'ZZZZZZZ', {ts '2007-03-27 00:00:00'}, {ts '2007-03-28 00:00:00'}, '',
'ZZZZZZZ'
, '', 'ZZZZZZZ', '', 'D'
-
Gail, not sure why this works but I've declared a second set of variables after the parameters and passed the parameters into these. I've then changed the WHERE clause to use my new set of variables and the SP completes consistently in 7 seconds regardless of where it is called from.
Although I'm delighted I can get the report to finish significantly faster, it seems bizarre that when i call it from within SSMS it should now be significantly slower than it was previously when called from SSMS?!
-
Then it's definatly a parameter sniffing issue. Can you try another .net experiment please?
First revert back to the old stored proc, without the variables.
This time, instead of passing the entire exec string to the data set, try going through the command object, type stored procedure and explicitly add the parameters (I think its .parameters.add from the command object, but I'm not a .net programmer)
If this executes quick, see if there's a way in crystal to specify parameters for the stored proc.
My guess is that the query is coming through, not as an rpc call with params, but as an ahdoc sql statement and the optimiser's misreading one of the param types or values.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Random thought - is you server 64 bit or 32? How much memory?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Start sqlprofiler for a while to captura all the load.
It shows the connection and execution settings !
You can load it into a table and analyse later on.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Gail, unfortunately I'm not a .Net developer either, but I've spoken to our team and although the test procedure we developed DID pass an ad-hoc query to the database, all procedures use the parameter.add method, and the Crystal Reports that do not use a data-set (which includes the one we have an issue with) also specify the parameters to be passed. As far as we are aware, if you're not passing a data-set to Crystal Reports, the only other method is to option is to specify the parameters. Hope I've explained that correctly!
We're using 32-bit Enterprise Edition on a 4 processor code machine with 8GB of RAM, of which 6GB is dedicated to SQL Server.
I've read more about parameter sniffing and am now convinced that this is the root of our problems so will make changes to the affected procedures to hopefully give the optimizer a heads-up on typical values that will be passed in, or at least use local variables when there is no typical value.
Thanks for your help guys.
-
If you're likely to get widely varying parameters, try marking the proc WITH RECOMPILE.
Alternativly, if some params are more common than others, you can try the OPTION (OPTIMISE FOR... hint on the query. Both are pretty much last-resort fixes and don't always work, but is worth a try.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply