THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Linchi Shea

Checking out SQL Server via empirical data points

Sysadmin and Performance

I was reading this excellent post on "Diagnosing Plan Cache Related Performance Problems and Suggested Solutions” by the Microsoft SQL programmability & API development team, and was struck by the following statements:

 

If the numbers of collisions (and spins) for SPL_MUTEX is high, then examine your application code to see if queries are being executed under a security context other than the sysadmin.”

 

 

“Changes to the application code that avoid this problem include executing all queries under the context of sysadmin to bypass security checks.”

 

Now, this particular problem has been fixed in SQL Server 2005 SP2. But these statements jogged my memory and I could swear that I had seen similar reports/articles on sysadmin having material performance advantage over non-sysadmin accounts beyond the fact that there is no permission check on the former. But I wasn't able to find any evidence to support that. So it’s possible I was just hallucinating.

 

Have you seen any cases where sysadmin has definite performance advantage?

Published Tuesday, January 08, 2008 12:05 AM by Linchi Shea
Filed under:

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Andrew Kelly said:

It must be so Linchi since I see SA used everywhere these days :).  The lack of a password also seems to help :).

January 8, 2008 9:15 AM
 

JasonM said:

Linchi,

The problem is still being reported past sp2 and rollups. However, there is a KB's that say it is fixed post sp2.

http://support.microsoft.com/kb/933564

I would not assume that you will hit this issue. I am 50/50 on x64 and I have not seen it on x86.

Here is some more info:

http://sqlblogcasts.com/blogs/sqldbatips/archive/2007/07/18/troubleshooting-performance-issues-with-tokenandpermuserstore-in-sql2005.aspx

January 8, 2008 2:09 PM
 

Linchi Shea said:

Jason;

I'm not as worried about this particular problem as I'm about what's implied in the message. That is, sysadmin is given a special treatment inadvertently or on purpose to have some performance advantage. Now, I'm not saying that is the case. I thought I came across some cases before, but my memory is failing me. Thus, I want to do a sanity check.

January 8, 2008 3:37 PM
 

Denis Gobo said:

>>So it’s possible I was just hallucinating.

People pay good money for that experience   :-)

January 8, 2008 5:15 PM
 

Gail said:

I've seen a case (with SP2) where sysadmin's queries ran instantly, but all others had wait times on CMEMThread of 500ms or highter.

Traced it down to an overly large TokenAndPermUserStore cache. From what I can tell, since sysadmin has access to everthing, it gets a short-cut through that cache, but queries from everyone have to search through the cache for their token.

When the token store is large (2GB or so) that does tend to take a while.

From what I've seen it's mainly an issue on 64 bit servers with lots of memory that get lots of ahd-hoc SQL requests

January 14, 2008 2:56 AM
 

Ji Village News said:

Yes, just like Gail, I've seen this happening on x64 Windows 2003 R2 SP2 with Sql Server 2005 Enterprise SP2 with 32 GB of memory.

I scheduled dbcc freesystemcache ('TokenAndPermUserStore') daily to solve this problem.

January 30, 2008 1:18 PM

Leave a Comment

(required) 
(required) 
Submit

About Linchi Shea

Checking out SQL Server via empirical data points

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement