Performance difference between different users calling the same stored procedure?

I noticed the major difference between the two user types is the additional query against tblYY. (ANDtblX.Div IN (SELECT FieldId FROM tblYY WHERE HRG='''+@EmpId+''') ).

Unfortunately, it seems like all the execution plans you provided are based on @AccessLevel <> 'HRG'. Therefore, we can't compare the related execution plans.

What I also noticed: there are numerous RID lookups, some of them consuming more than 90% of te execution time. It seems like the indexes are not covering the queries. You might want to add the required columns as INCLUDED(), especially to the ZZEml table. This should help overall performance.

MB, as Lutz pointed out, none of these queries allow us to compare the difference in the queries in your system when you do, or don't, add in the:

AND tblX.Div IN (SELECT FieldId FROM tblYY WHERE HRG='''+@EmpId+''')'

clause. Btw, easier way to re-write this would probably be to set the @sSQLSalary string once, and then merely append the extra where clause. Would save you keeping two definitions of the same base query.

Now, to my earlier question, this does have multiple subcalls. In particular, this component:

EXEC (@sSQLSalary)

EXEC (@sSQLPreviousSalary)

EXEC (@sSQLPriorYearSalary)

EXEC (@sSQLUpdateLastYear)

EXEC (@sSQLUpdateSalary5)

EXEC (@sSQLUpdateCCRecoms)

Here's what I'd like you to do. Create a quick logging table, it doesn't need a lot we just need it for a few minutes. Then create a second copy of the proc that does the following.

First, the logging table:

CREATE TABLE zLoggingDataFromProc ( StepName VARCHAR(100), DurationInMS BIGINT)

Next, wrapper your code as so:

DECLARE @TimeStart AS DATETIME

SET @TimeStart = GETDATE()

EXEC (@sSQLSalary)

INSERT INTO zLoggingDataFromProc VALUES ( '@sSQLSalary', DATEDIFF( ms, @TimeStart, GETDATE()))

SET @TimeStart = GETDATE()

EXEC (@sSQLPreviousSalary)

INSERT INTO zLoggingDataFromProc VALUES ( '@sSQLPreviousSalary', DATEDIFF( ms, @TimeStart, GETDATE()))

SET @TimeStart = GETDATE()

EXEC (@sSQLPriorYearSalary)

INSERT INTO zLoggingDataFromProc VALUES ( '@sSQLPriorYearSalary', DATEDIFF( ms, @TimeStart, GETDATE()))

SET @TimeStart = GETDATE()

EXEC (@sSQLUpdateLastYear)

INSERT INTO zLoggingDataFromProc VALUES ( '@sSQLUpdateLastYear', DATEDIFF( ms, @TimeStart, GETDATE()))

SET @TimeStart = GETDATE()

EXEC (@sSQLUpdateSalary5)

INSERT INTO zLoggingDataFromProc VALUES ( '@sSQLUpdateSalary5', DATEDIFF( ms, @TimeStart, GETDATE()))

SET @TimeStart = GETDATE()

EXEC (@sSQLUpdateCCRecoms)

INSERT INTO zLoggingDataFromProc VALUES ( '@sSQLUpdateCCRecoms', DATEDIFF( ms, @TimeStart, GETDATE()))

SET @TimeStart = GETDATE()

Perform three runs with an NT login, and 3 runs without it. That'll give us baseline data. What we want to see is if all of these sub-calls are running slower, or if it's one or two, allowing us to concentrate either on specific query plans, or on the generic call for:

tblX.Div IN (SELECT FieldId FROM tblYY WHERE HRG='''+@EmpId+''')'

If this code doesn't make sense to you, it's basically just setting the @TimeStart variable to the current moment, then after the subcall runs, it's getting the difference in milliseconds from before and after the call, and dropping it to zLoggingDataFromProc

Do not keep this included in your primary production proc, it's almost always only used for testing.

I have been busy with everyone's suggestions today.

mister.magoo:

Thank you for pointing out that the non-Winuser was making calls to those tables. I am in a situation where I am attaching a module to a vendor application, and this non-Winuser is created and maintained within that vendors application. But I can use it on the server end, and so I have been suspecting that this user is making extra calls to resolve security, but I didn't know how to find that out. I assume that it was obvious in the execution plans, so I will make an effort to understand how to better read those.

LutzM and Craig:

I have to make that extra call to tblYY. (AND tblX.Div IN (SELECT FieldId FROM tblYY WHERE HRG='''+@EmpId+''') ). That is what makes the difference between the users and what they are allowed to see.

As far as the adding indexes, again I am dealing with a vendor app, and I have some control, but not all, so I will see what I can do with that.

Craig, I understand what you mean about the appending the strings into one and making the call once. I also did what you suggested with the logging table. I am attaching the results in a spreadsheet. I had to add a column for the user. I ran the proc with different params, so I have BUL winuser, BUL non-winuser, HRG winuser, HRG non-winuser, and Mgr. I hope that is enough.

I think that the solution for me might be to try and not use the non-WinUser. I was just curious why the difference.

If you have any additional comments for me that would be great, if not I am extremely thankful for everyone's help and patience.

M

First of all: you did an excellent job setting up the test scenario and collecting the data!

What I would do as a next step is to compare the actual execution plans for @sSQLUpdateLastYear, run by BUL non-Windows-user and BUL Windows-user.

The reason why I picked those two: the time difference between non-Windows and Windows user is by far the most significant for the @sSQLUpdateLastYear sproc. The duration per user user (BUL, HRG and MGR) is almost identical within a user/non-user group, so this shouldn't really make the difference. I picked BUL since it has the largest difference between Win and non-Win.

Maybe there's something obvious when comparing the two execution plans...

This is just from my past experience. If you are on a 64 bit box and using a linked server for your process i have noticed big delays one of the reason is because windows account in a 64 bit server has to go through more number of checks like NTLM, Kerberos..etc...

Let me help you out with a starter for 10....

You mentioned that Query#4 was slow, so I opened the sql execution plan for Query4 and saw this:

Notice the Filter shows as 75% of the query cost... I saw that and clicked it. Then Press F4 - to open the Properties window - this can be seen on the right.

The properties window shows you a lot more detailed information about the selected item.

In this case it was the "Predicate" I was interested in because the item was a Filter.

Clicking on the ellipses to the right of the Property value (they appear when your mouse is over the value) opens a small window containing the WHERE clause for the query:

Copy this and paste it into a new query window , format it a bit and you can see the contents a bit clearer:

user_name () = N'nudevadmin' OR

user_name () = N'dbo' OR

[NuHRS].[dbo].[ZZSecRol].[Sec] = convert (VARCHAR (50), user_name (), 0) AND

([NuHRS].[dbo].[ZZSecRol].[Rol] = 'AllAccess' OR

... (code omitted for brevity)...

[NuHRS].[dbo].[ZZSecRol].[Rol] = 'TimAttAdmin')

You can then see quite plainly that nudevadmin and dbo always get access, while other users only get access based on the lookup to ZZSecRol.

Hope that helps as a starter to understanding the plans - obviously there is much, much more to it than that, but investigating the properties of objects in the plan will often help to identify potential issues.