Sysadmin fast , user logins slow

Question

Post reply

Sysadmin fast , user logins slow

matthew.lowe 19651

Ten Centuries

Points: 1192
More actions
January 9, 2012 at 9:05 am

#254704

Hi All,
i have a production database that is showing strangness between running queries via the app as a sysadmin , and also the same query in the app , running under a user login(Public)
Quick question:-
Would SQL server ever 'boost' or give priority to sysadmin logins over user logins in the same instance?
If you then make the public user a sysadmin in SQL server , the performance improves dramatically and works as per normal.
Thanks in advance
Matt

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

This?

http://sqlinthewild.co.za/index.php/2007/10/21/memory-and-sql-2005-sp2/

http://sqlinthewild.co.za/index.php/2008/05/02/token-and-perm-user-store-growth/

The blog posts are for SQL 2005, but the issue can come up on 2008 as well (just a lot less likely). See if you have the rest of the symptoms described.

Edit: Also check out chapter 3 of this http://www.simple-talk.com/books/sql-books/troubleshooting-sql-server-a-guide-for-the-accidental-dba/

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 2

As a side note, you should avoid external applications to hit to your database as sysadmin user. It could invite major security breach in future.

Also, please verify if it’s the result of Resource Governor Configurations. If not, you may use it as a tool to prioritise few of the users for given workload.

Introducing Resource Governor

http://msdn.microsoft.com/en-us/library/bb895232.aspx

matthew.lowe 19651 Ten Centuries Points: 1192 More actions · Answer 3

RG is set as default , and not been configured at all.

Thanks for the advice as well bioth of you 😀

Going to look at :-

SELECT plan_handle, ecp.memory_object_address AS CompiledPlan_MemoryObject,

omo.memory_object_address, pages_allocated_count, type, page_size_in_bytes

FROM sys.dm_exec_cached_plans AS ecp

JOIN sys.dm_os_memory_objects AS omo

ON ecp.memory_object_address = omo.memory_object_address

OR ecp.memory_object_address = omo.parent_address

WHERE cacheobjtype = 'Compiled Plan';

GO

Cheers

Matt

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

First step, don't look at the cached plans (that won't give a difference between sysadmin and normal users unless there's something very strange been done), don't look at resource governor. Look at my two blog posts. They describe a situation where, due to the increased size of a specific cache, sysadmin queries are fast and normal user queries are slow. If it's not that, then we can debug further.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SQLRNNR SSC Guru Points: 281334 More actions · Answer 5

GilaMonster (1/9/2012)
This?
http://sqlinthewild.co.za/index.php/2007/10/21/memory-and-sql-2005-sp2/
http://sqlinthewild.co.za/index.php/2008/05/02/token-and-perm-user-store-growth/
The blog posts are for SQL 2005, but the issue can come up on 2008 as well (just a lot less likely). See if you have the rest of the symptoms described.
Edit: Also check out chapter 3 of this http://www.simple-talk.com/books/sql-books/troubleshooting-sql-server-a-guide-for-the-accidental-dba/

Very good resource Gail - thanks a ton.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

matthew.lowe 19651 Ten Centuries Points: 1192 More actions · Answer 6

OK Gail.

Ran the command (DBCC FREESYSTEMCACHE (‘TokenAndPermUserStore’))

Which we *think* may have fixed it .

but the cahce file seems to be growing big time .

Is there a simple way to check the raw size of this cache?

Cheers

Matt

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

Yes, it's described in chapter 3 (the last 2 pages of it) of the book I listed, along with all the options (short term and long term) for fixing the problem.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

matthew.lowe 19651 Ten Centuries Points: 1192 More actions · Answer 8

Ok working now , but very slow.

SA is nice and quick.

Hmm , thinking profile SA and then user and then comparing SQL profile trace

Then trying to run this code , in SQL under SMS , running execution plan

Whadday think?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

Nope, if it's token store it's not an execution plan-related problem. The queries will be exactly the same, will perform fast for sysadmin and slow for any other user showing high CMEMTHREAD waits and an above-average CPU usage

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 10

Check and monitor to see if it is a token store problem first, if not, we can try and debug further.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

matthew.lowe 19651 Ten Centuries Points: 1192 More actions · Answer 11

Gail

Thanks

So to confirm , monitoring the situtaion if it is this issue will show :-

User running query , with a high CMEMTHREAD wait time .

If so , ill check this out .

And post back the results.

Thanks for all your help.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 12

Please check the links in the blog posts I mentioned, as well as chapter 3 in the book I recommended. I'm not saying this because I'm lazy, the symptoms and potential solutions (short term and long term for 2005 and 2008) are included in the section in the book. I know, I wrote that section of the chapter.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

matthew.lowe 19651 Ten Centuries Points: 1192 More actions · Answer 13

Dear Gail,

Thanks for your ongoing help on this matter.

I have checked those

Short term

Make them a sysadmin (Cant happen , sorry)

Clear cache on a regular basis (Done this , still slow , but has improved)

Long term fixes

Re architecture the application and or database (this wont happen any time soon 🙁 )

I think all i need to confirm that this is actaully a token issue and not something else.

Cheers

SQLRNNR SSC Guru Points: 281334 More actions · Answer 14

Continue to monitor cache size over time and watch how fast it grows. Keep track of that and you will be able to determine if it is a TokenAndPermUserStore issue or not. If it takes 30 min to grow to 1GB and the users complain of slowness but flushing it makes it go faster for another 30 min, then you likely have a TokenAndPermUserStore issue.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events