January 24, 2011 at 3:59 pm
Hi,
I have two users on a SQL Server 2005:
1. Windows AD account, which has admin rights to the SQL Server.
2. SQL Server account, which is given individual rights to select stored procedures and tables.
It seems that I get different performance depending on which account runs the stored procedure. With the Windows AD account, I would get results back in 4-5 seconds, and with the SQL server account I am getting 20-25 seconds for the same stored procedure.
Does anybody know why this is happening?
Thanks a lot in advance.
M
January 24, 2011 at 4:06 pm
It shouldn't, so that's confusing. Can you describe more about how you're making the calls? Are you opening up a query window connected to the server with the two accounts and running the proc from there, or are there more layers of connectivity involved?
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
January 24, 2011 at 4:10 pm
I am opening a query window in Management Studio under each account and the run the same sp with the same parameters, and get different results.
January 24, 2011 at 4:18 pm
MB-400824 (1/24/2011)
I am opening a query window in Management Studio under each account and the run the same sp with the same parameters, and get different results.
Do you actually get different results?
Is it possible that the user ID is being picked up within the sp and being used as a paramater?
Does the sp have an user_name() call in it?
Leo
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
January 24, 2011 at 4:29 pm
Do you actually get different results? - No, I get the same results, and actually not that many records back, about 171.
Is it possible that the user ID is being picked up within the sp and being used as a paramater? - I would like to say no, but maybe you can explain how that would happen. I am not sure I would know.
Does the sp have an user_name() call in it? - No, it doesn't.
I was not sure if the below would be important that is why I left it out:
I have devised a way to handle the required security for an application, and I have four different groups of security. The stored procedure I call then calls another stored procedure (which I am calling a 'helper'), so for example:
exec sp_main @userid
Inside sp_main I determine what is the level of security for @EmpId and then I call: sp_main_helper @EmpId , 'admin' or sp_main_helper @EmpId, 'non-admin'. (I hope this makes sense.)
Inside sp_main_helper I do call 1 function, and a thirst stored procedure. Both of the users has rights to execute the function and the third sp. Let me know if all of this was not helpful at all.
Thank you!
January 24, 2011 at 4:57 pm
Are the executions plans the same for both users?
Use profiler and compare the connection settings for both users, do they differ in anyway?
Gethyn Elliswww.gethynellis.com
January 24, 2011 at 4:57 pm
Incredibly helpful. It tells us that you have user contextual logic changes that may be occurring.
Here's your next step: Dig down to the deepest call. Find out if there's any differences between the two at that level, and work yourself out of the nested calls until you find the difference. That's the area we need to optimize. At a guess, it's not the top level procedure.
If it is, we'll probably need to see your script and some sample data to really dig into this.
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
January 25, 2011 at 9:39 am
To answer GRE about the execution plan first: I had to add additional rights to the non-windows user to allow it have sufficient rights to run the execution plan. Then when I ran both I get the same execution plans for both. Also, even after giving additional rights to the non-windows user, it still takes the same time ~20sec to execute the sp.
It is possible that I also don't know how to read the plans correctly.
In the profiler, I get the following, I hope I am looking at the right params here:
WinUser
CPU - 6845
Reads - 788679
Writes - 672
Duration - 5457
nonWinUser
CPU - 48032
Reads - 1618852
Writes - 672
Duration - 21767
Answering Craig, if I understand what you mean, I went and executed separately the sp which gets called from sp_main_helper, which I will call sp_calculations. I get different performance there also:
WinUser - 1 sec execution time
non-WinUser - 13 sec execution time.
At this point, both users are db_owner to the database.
I hope I didn't confuse everyone that is trying to help.
January 25, 2011 at 10:19 am
MB-400824 (1/25/2011)
Answering Craig, if I understand what you mean, I went and executed separately the sp which gets called from sp_main_helper, which I will call sp_calculations. I get different performance there also:WinUser - 1 sec execution time
non-WinUser - 13 sec execution time.
At this point, both users are db_owner to the database.
I hope I didn't confuse everyone that is trying to help.
We need to dig deeper into this component: sp_calculations.
Does this proc also have subcalls, or is it a single process? If it's a single process, does it have multiple steps? If it has either multiple steps or subcalls, can you reprocess at that level and find the difference in time?
You see where I'm going here? We need to find the core statement that's causing the difference. A difference of 10% we could leave up to variance in the system. Something of 1300%? That's an indicator of an issue.
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
January 25, 2011 at 11:36 am
Craig,
sp_calculations does not make any subcalls. The way I have the proc is that I create the T-SQL on the fly. I try to minimized code maintanence, so, I do something like this:
SET @strInsert = 'INSERT ........'
SET @strSelect = 'SELECT....'
If @EmpId = 'admin'
SET @strWhere = 'WHERE .... '
else
SET @strWhere = 'WHERE .... '
At the end I combine the above strings: SET @strSQL = @strInsert + @strSelect + @strWhere, and then EXEC (@strSQL)
This allows me to only have to update and maintain the @strWhere string.
I also use temp tables in my proc.
One last thing, I created a brand new non-Windows user. I wanted to see how this user will run the procs. It turns out that it takes this new user twice the time it did the only non-Windows user, which is three times the Windows user. Is there are difference between windows and non-windows users in SQL Server?
Thanks for your help. M
January 25, 2011 at 11:48 am
To SQL Server? Not really, not once you've opened the server's "door", so to speak.
Any chance you could post the query and execution plans for the windows and non-windows users? Maybe we can help you determine exactly where the issue is in the code.
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
January 25, 2011 at 12:48 pm
MB-400824 (1/25/2011)
Craig,sp_calculations does not make any subcalls. The way I have the proc is that I create the T-SQL on the fly. I try to minimized code maintanence, so, I do something like this:
SET @strInsert = 'INSERT ........'
SET @strSelect = 'SELECT....'
If @EmpId = 'admin'
SET @strWhere = 'WHERE ....
'else
SET @strWhere = 'WHERE ....
'At the end I combine the above strings: SET @strSQL = @strInsert + @strSelect + @strWhere, and then EXEC (@strSQL)
This allows me to only have to update and maintain the @strWhere string.
This puts a different light on things. Dynamic SQL is always a problem as you can't be sure the SQL Parser will actually use the cached query plan. Also you need to check the final exicuted code, that it is the same for both users. I would try avoid dynamic SQL if at all possible, and to really test you should work out what the final SELECT/INSERT command will be, create the appropriate stored procs and then execute these for each user. I'de also try setting STATISTICS IO ON and STATISTICS TIME ON. Also do a DBCC DROPCLEANBUFFERS between some initial tests to see how thw queries run without a cached execution plan.
Is there are difference between windows and non-windows users in SQL Server?
No difference in term of how SQL handles the query.
Cheers
Leo
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
January 25, 2011 at 1:03 pm
Craig,
I am working on getting some code in here, but the shortest proc is 195 lines, so I am not sure how much of it is useful to post here. Another issue is that I don't know how exactly to post the execution plan in here? Any advise?
Thanks. M
January 25, 2011 at 1:06 pm
Follow the link in my sig that reads:
For index/tuning help, follow these directions.
It'll show you how to extract your .sqlplans
You can post the code here in the code="sql" tags, or attach them as .txt files (.sql doesn't always behave).
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
January 25, 2011 at 1:23 pm
Leo,
I don't mind going thru the trouble of converting my code to non dynamic SQL, and I hear your concerns. But I don't think my issue is that. I check to see if the concatenated string is the same for both users, and it is. I get the same results and that's how it should be, but each user is different on how long it takes for the data to come back.
I am going to pay with the settings that you mentioned and see if I can make some sense.
Thanks, M.
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply