August 7, 2009 at 9:30 am
Can somebody help me tune this stored proc? It is erratic and average time is between 500 and 1000 milliseconds. Sometimes it goes up to 13 seconds... we get about 10/15 hits per second, and it is creating a bottleneck...
I've attached txt files for
1. The stored proc
2. The table and their SP_HELP
3. Screen shot of the query plan
4. Sample of the query call.
The rowcounts are as follows:
CompanysPrograms - 2000
NodesActivationCodes - 1.5 million
ActivationCodes - 1 million
ActivationCodesProducts - 1 million
Products - 60
BundlesProducts - 300
NodesActivationCodes_ISP - 800K
ActivationCodes_ISP - 500K
ActivationCodesProducts_ISP - 500K
I'm not sure how to export the execution plan out in text so you can look at the details...
August 7, 2009 at 9:45 am
parameter sniffing: search here on SSC for some examples and solutions;
parameter sniffing happens when an execution plan is built that does not reflect the real way to get the data; it is often due to having optional parameters... for example, your proc has a parameter like this:
@componentType varchar(10) = null,
so the engine decides that since you provided the value, NULL, it must be the default / most common value to use, and creates an execution plan based on that value being null; if you look at the code, the first thing you do is test for a value other than that.
i reckon if you change it around so that local variables are used inside the proc, the perforance will get better due to a better execution plan.
change the very top of your proc to this:
CREATE PROCEDURE [dbo].[GetEntitlementResponse](
@locbios nvarchar(300),
@loccomputerName nvarchar(300),
@locdiskVolume nvarchar(300),
@locguid uniqueidentifier,
@locmacAddress nvarchar(300),
@locmotherboard nvarchar(300),
@locnodePKID uniqueIdentifier,
@loccomponentType varchar(10) = null,
@loclang varchar(10) = null,
@locissVersion varchar(10) = null,
@locentitlementSuite varchar(10) = null,
@locplatform varchar(10) = null,
@locmetaUpdate int = 0)
AS
DECLARE
@bios nvarchar(300),
@computerName nvarchar(300),
@diskVolume nvarchar(300),
@GUID uniqueidentifier,
@macAddress nvarchar(300),
@motherboard nvarchar(300),
@nodePKID uniqueIdentifier,
@componentType varchar(10) ,
@lang varchar(10) ,
@issVersion varchar(10) ,
@entitlementSuite varchar(10) ,
@platform varchar(10) ,
@metaUpdate int
SET @bios = @locbios
SET @computerName = @loccomputerName
SET @diskVolume = @locdiskVolume
SET @GUID = @locguid
SET @macAddress = @locmacAddress
SET @motherboard = @locmotherboard
SET @nodePKID = @locnodePKID
SET @componentType = @loccomponentType
SET @lang = @loclang
SET @issVersion = @locissVersion
SET @entitlementSuite = @locentitlementSuite
SET @platform = @locplatform
SET @metaUpdate = @locmetaUpdate
Lowell
August 7, 2009 at 9:47 am
See this article for posting the exec plan for SQL 2000. http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
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
August 7, 2009 at 10:55 am
@Gail - I tried the instructions from the post, but no additional grid was returned..
SET STATISTICS PROFILE ON
GO
exec GetEntitlementResponse @bios = N'None', @computerName = N'OWNER-B23156214', @diskVolume = N'4472755e', @GUID = '96FB784E-BE48-4378-BFAF-D568F6AC7E0A', @macAddress = N'00-24-1d-2c-a2-f0', @motherboard = N'None', @nodePKID = '150FB556-707C-4194-B605-ED07FC747242', @lang = 'en-US', @issVersion = '5.0.0.581', @entitlementSuite = 'ISS', @platform = 'x86', @metaUpdate = 1
@lowell - I had tried this previously, and I tried it again now - it doesn't seem to help, because most of the parameters are not being used. If you see the query, there is really 1 parameter being used really - @nodePKID - this joins amongst the other tables, and the rest are all just based on indexes and such.
August 7, 2009 at 11:03 am
yeah i was looking at that;
really what you want to do is have your original be a "master" procedure, and depending on parameters, call other, more refined procedures for specific data instead.
It might be a pretty big rewrite of the procedure, so you avoid the code like below, if you knew
@componentType IS NULL, you could call subproc1, and if it is not null, call subproc2;
that would fix that WHERE statemtn that has the OR in it:
and ((vp.IsBundle = 1 and vp.ComponentType_BP = @componentType) OR (vp.ComponentType = @componentType))
Lowell
August 7, 2009 at 11:07 am
I can break it up if need be, but majority of my calls are in the 'else' part - basically, the @componentType is null in most of the cases...
August 7, 2009 at 11:14 am
wouldn't changing the defaults on these to empty strings improve your proc a bit?
@componentType varchar(10) = null, @lang varchar(10) = null, @issVersion varchar(10) = null, @entitlementSuite varchar(10) = null, @platform varchar(10) = null,
comparing SomeColumn = NULL makes that portion of your WHERE statements incorrect, i think? does that sound right?
Lowell
August 7, 2009 at 11:23 am
I can try it, but I've tried it previously. It doesn't help because they aren't actually used in the where statements....
August 7, 2009 at 12:00 pm
Tried - no dice 🙁
August 7, 2009 at 12:06 pm
MR (8/7/2009)
I can break it up if need be, but majority of my calls are in the 'else' part - basically, the @componentType is null in most of the cases...
It's still worth breaking it up, at the very least because it will mean that it can be optimised in pieces.
Try breaking the proc up so that the if and else blocks just call other procs, then test and see where the performance problem is and then we can work on the smaller pieces.
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
August 14, 2009 at 10:00 am
I also see that perhaps we could build you some better indexes. As there are 10 bookmark lookups in the first query and bookmark lookups are normally very expensive.
I also noticed that the code is written using the old style(non ansi standard), where you put you join criteria in the where clause, although this should not have an impact on the speed of there query, you need to make sure that perhaps you haven't created a cross join...
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply