October 31, 2010 at 5:30 pm
I have built a query and tweaked indexes etc to run satisfactorily - with SMS - once I put it inside a stored procedure it crawled along. Exectution plans look very similar but I don't seem to be able to isolate what/where the bottleneck is.
Can you offer some guidance and advice?
When executed as a query from SMS it returns 14 rows (as an example) and takes around 4 secs. When the exact same code is placed in the SPROC and parameters are used instead of variables it returns the same data but is taking 1min12secs!
NB - it does a lot of string search and disseminating, and the data is based on data extracted from Windows Event Logs
Here is the a summary of the query:
-- Add variables which will later become the parameters for the stored procedure
DECLARE @pClientId int = NULL,
@pGroup varchar(255) = NULL,
@pStartDate datetime = NULL,
@pEndDate datetime = NULL
SET @pGroup = 'test_group'
SET @pStartDate='2010-10-01 00:00:00'
SET @pEndDate='2010-10-31 23:59:59'
SET @pClientId=29
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Let's not concern ourselves at this time with LOCKING table - review later
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
-------------------------------------------
-- Validate Dates
-------------------------------------------
-- If StartDate is blank then 1 month back from Today
if @pStartDate is null
SET @pStartDate = dateadd(month,-1,GETDATE())
-- If StartDate is blank then make it Today
if @pEndDate is null
SET @pEndDate = GETDATE()
-- If StartDate is greater than EndDate then make the StartDate 12 hours ago
if @pStartDate >= @pEndDate
SET @pStartDate = dateadd(hour,-12,@pEndDate)
-------------------------------------------
SELECT
@pGroup as 'Group',
substring(e.USERNAME,PATINDEX('%\%',e.USERNAME)+1,255) as SimpleUserName,
case
(
-- do a string lookup/patindex based on EVENTID ....
....
)
when '2' then 'Interactive'
when '3' then 'Access Network Share'
when '4' then 'Batch'
when '5' then 'Service'
when '6' then 'Proxy'
when '7' then 'Unlock/Lock Workstation'
when '8' then 'Network logon using a clear text password'
when '9' then 'Impersonated logon'
when '10' then 'Remote Desktop Session'
when '11' then 'Cached Logon - Mobile'
else 'Unknown - Advise Delta'
end LogonType
,e.[EventDteTme]
,e.[USERNAME]
,e.[CATEGORY]
,e.[RULE_NAME]
,e.[COMPUTER]
FROM [Repos_Events] as e INNER JOIN
( -- Get the lastest Check that contains the Group and Username list
SELECT DISTINCT ClientID, EXPLANATION
from Checks as c
WHERE
(
c.ClientId = @pClientId
AND UPPER(c.CHECKNAME) LIKE '%GROUP MEMBERSHIP LIST%'
AND UPPER(c.CHECKNAME) LIKE ('%' + UPPER(@pGroup) + '%')
)
) as u ON e.ClientId = u.ClientId
WHERE e.ClientId = @pClientId
AND e.EVENTID in (528,538,540,4624,4634)
AND NOT(upper(e.RULE_NAME) LIKE '%FAIL%') -- Exclude where there's a Failure
AND -- Looking for a specific set of UserNames
patindex('%'+ UPPER(substring(e.USERNAME,PATINDEX('%\%',e.USERNAME)+1,255))+'%', UPPER(CAST(u.EXPLANATION AS varchar(max)))) <> 0
AND (e.USERNAME is not null and e.USERNAME <> '')
AND e.EventDteTme between @pStartDate and @pEndDate
AND ( -- Only include XXX when it is an RDP session
-- etc... more string/patindex stuff to include or exclude items
)
ORDER BY
SimpleUserName
,e.COMPUTER
,e.EventDteTme
,e.USERNAME
,e.EventRepId
Here's the SPROC bit that's different:
ALTER PROCEDURE [dbo].[usp_TheProc]
-- Add the parameters for the stored procedure here
@pClientId int = NULL,
@pGroup varchar(255) = NULL,
@pStartDate datetime = NULL,
@pEndDate datetime = NULL
AS
BEGIN
SPROC executed from SMS like this:
DECLARE
@StartDate datetime,
@EndDate datetime,
@ClientId int
SET @StartDate='2010-10-01 00:00:00'
SET @EndDate='2010-10-31 23:59:59'
SET @ClientId=29
EXECUTE usp_TheProc
@ClientId,
'external_support',
@StartDate,
@EndDate
[font="Arial"]-----------------------------------------------------------------
Rich N - Development Manager @ Delta Technology Solutions Ltd - M.O.T.H.E.R. your virtual Sys Admin - Remote Server Health Monitoring - 24 x 7 www.deltatsl.com[/font]
November 1, 2010 at 2:31 am
Best thing to do at this moment is to post both execution plan.
In SSMS activate 'show actual execution plan' in your query toolbar.
Then execute the query.
Select the execution plan tab, rightclick and save the result.
Same for the sproc execution.
Execution plans don't reveal data, but show statistics on which the sqlengine based itself to get to the plan.
You can add both as an attachement to your reply.
If possible, please include the ddl for the touched objects.
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
November 1, 2010 at 10:58 am
Sounds like parameter sniffing is the culprit, offhand. As mentioned above, the .sqlplan's for your results from the above two items would help determine that.
Also, check out this link (by Gail Shaw):
http://sqlinthewild.co.za/index.php/2007/11/27/parameter-sniffing/
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 1, 2010 at 9:33 pm
Execution plans have been attached.
Have tweaked the code to parse the csv string of usernames to a tmp table to see if things would go better by removing some of the string searches - not a lot of diff.
[font="Arial"]-----------------------------------------------------------------
Rich N - Development Manager @ Delta Technology Solutions Ltd - M.O.T.H.E.R. your virtual Sys Admin - Remote Server Health Monitoring - 24 x 7 www.deltatsl.com[/font]
November 1, 2010 at 10:50 pm
Welp, we barked up the wrong tree... those plans are nearly a match.
*takes the problem to his pillows and sleeps on it*
EDIT: I take that back. Dug a little deeper on the last item and the estimated rowcounts are very different, 1000% estimated in the sproc than in the query. Both are inaccurate, however, as 189k is returned vs. the 4.5k or 39k estimated...
The PROC is also using a Parallelism. Can you try the PROC with a OPTION ( MAXDOP 1) on the last statement in it, and check performance?
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 2, 2010 at 2:00 am
This is my Monday morning .... actually Tuesday, but yesterday was a holliday 😉
- Unless your db is case sensitive, remove all upper conversions !!
- If it is case sensitive, and all or most of your queries are using UPPER() to compare, my guest someone chose the wrong collation to start with.
- Keep in mind like statements starting with a % will, in the best case, result in an index scan, if not a full table scan.
edited:
Because you use a function (substring) in your on clause, it will not perform as you may wish.
FROM [uRepository_Events] as e INNER JOIN
#UserListTable as u ON
substring(e.USERNAME,PATINDEX('%\%',e.USERNAME)+1,255) = u.value
If there is an index on e.USERNAME, maybe it's worth to pull all your distinct user names into your temp table #UserListTable. ( use a group by and not a distinct, because a group by will actually be performed with the help of this index ! )
I would create this table having columns "FullyQualifiedUserName" and "UserName".
Whilst inserting, extract the user name from the e.USERNAME into the column UserName.
Then remove the rows that aren't in your csv input var.
Then you can join using "e.USERNAME = u.FullyQualifiedUserName" in stead of the function. It will perform better if this index on username is present.
Also keep in mind, your addition 20101101 will add all rows of uRepository_Events to your result set, but maybe - because of size restrictions - the plan isn't showing the full code of this query.
In that case, please post the ddl of the sproc.
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
November 3, 2010 at 6:03 pm
Thanks Craig - MAXDOP did resolve the Parallelism issue. Performance did tweak a bit.
[font="Arial"]-----------------------------------------------------------------
Rich N - Development Manager @ Delta Technology Solutions Ltd - M.O.T.H.E.R. your virtual Sys Admin - Remote Server Health Monitoring - 24 x 7 www.deltatsl.com[/font]
November 3, 2010 at 6:12 pm
Thanks ALZDBA - this guided me into a number of areas to tweak a validate.
USERNAME - I created a computed field to create a SimpleUserName (sometimes it was fully qualified and other not), well worth the overhead. Altered indexes to utilise SimpleUserName instead of USERNAME - good success.
Also meant that the JOIN ON could go direct to the SimpleUserName field.
UPPER - yep turned out to be completely unnecessary - another good win.
Digging around the Execution Plan and revising some of the WHERE clauses using PATINDEX etc revealed an unnecessary "NOT LIKE" wildcard check - another good win!
Thanks guys for your help - sometimes one just needs pointing in the right direction. Much appreciated.
[font="Arial"]-----------------------------------------------------------------
Rich N - Development Manager @ Delta Technology Solutions Ltd - M.O.T.H.E.R. your virtual Sys Admin - Remote Server Health Monitoring - 24 x 7 www.deltatsl.com[/font]
November 4, 2010 at 1:19 am
HTH 🙂
If you post the new sqlplans (and consumption times), I'm sure we'll take another look.
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
November 4, 2010 at 1:31 pm
New plan, post changes hereto attached.
[font="Arial"]-----------------------------------------------------------------
Rich N - Development Manager @ Delta Technology Solutions Ltd - M.O.T.H.E.R. your virtual Sys Admin - Remote Server Health Monitoring - 24 x 7 www.deltatsl.com[/font]
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply