Query Runs much faster as sysadmin!
-
Evening,
I have a Stored procedure. It sits in a database and accesses data from another and a TableValuedFunction in another.
So, on 1 server using 2 different logins, it takes less than a second when run as sysadmin.
When run using login 2 (db_datareader and execute rights in all 3 dbs) it takes 5 seconds
I'm running Microsoft SQL Server 2019 (RTM-CU8) and have plenty of cores and ram.
Execution plans are identical, I've run it once in each session in the picture to show the waits I am seeing.
The execution plan is here
As per the post here I have followed Gail's advice and flushed the token store (even though I'm on SQL 2019).
I've even restarted the sql services and it does exactly the same cold, the window using my reporting login takes longer.
Obviously providing the reporting login with sysadmin cannot happen.
Any thoughts most appreciated
Rich
-
Are you sure the execution plans are identical? Did you run a compare on them to validate that? Just because they look the same, doesn't mean they are the same. The details can matter. Although, running them both from the same connection within SSMS I'd think they would be, but double checking can't hurt.
Try setting up Extended Events to capture the query metrics and the wait statistics specific to the query. Here's some basics on how to do that. You can get more accurate details on exactly what's happening that way.
This is a mystery.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
Was the system less busy when you ran it as Sysadmin ?
Are you getting consistent results with multiple tests ?
-
What's the chance of you making the reporting login sysadmin for long enough to run and test the query?
It it runs fast, then the mystery deepens.
If if runs in the same manner, then, like Grant said, you are likely not making an exact comparison
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/ -
I have a similar problem. When the user is a member of syѕdba, program execution is noticeably faster.When I remove syѕdba it runs slower.
Any solution?
- skrstic wrote:
I have a similar problem. When the user is a member of syѕdba, program execution is noticeably faster.When I remove syѕdba it runs slower.
Any solution?
You'd be better served by posting your own question. The only people likely to see this are the ones who already posted in this question.
Take a look at the execution plans for each. Probably there are differences in the ANSI settings resulting in different execution plans. You can capture that in plans, in the first operator. Use the plan comparison in SSMS to see the differences. Also, could be something to do with Resource Governor. Worth a check.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
Check what the default database is for the two users. If it is different, it could explain the difference in performance (he says, based on bitter experience....).
-
Is the SQL process setup with the re-compile option (adding more time for execution)? Yes, as the gentleman stated, please compare the execution plans could be different. Please verify the statistics.
DBASupport
-
Hi
have you ever testet a execute as the normal user in the same SPID from the sysadmin?
- open a new query from sysadmin
- and run this query:
EXECUTE AS USER = 'your sample user';
SELECT SYSTEM_USER AS 'current Login';
execute your code or your stored procedure
REVERTAre you using some kind of resource govenor?
Kind regards and good luck,
Andreas
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply