December 3, 2010 at 8:09 am
Hi,
in our SQL2008 database we have stored procedures that join several tables.
I did some checks using 1 of those stored procs and have found some strange behaviour concerning the performance.
In the database there's a user (User A) and this user has db_datareader, db_datawriter and Execute permissions granted on the entire database, and no other permissions.
For no user are there table permissions set.
Another user (User B) has no permissions set on database level but is a SysAdmin.
When i run a stored proc as User B, the stored proc is completed in 1.8 seconds.
When i run the same proc with the same parameters but as User A the proc takes 3.8 seconds to complete. When i change User A (immediatly after running the proc) and give him SysAdmin permissions and run the proc again it also takes 1.8 seconds. Immediatly removing the SysAdmin permission and running the proc again and it takes 3.8 seconds again.
I can understand that checking permissions takes some time but 2 seconds is an extremly long time in my opinion.
Has anybody seen this behaviour before?
And if so can i change some setting or something so running a query as non sysadmin is still as fast as running it as sysadmin?
Giving all users sysadmin permissions isn't the way to go i reckon 😉
December 3, 2010 at 8:50 am
Check this, see if it maybe is what you're encountering. Shouldn't be on 2008, but never know
http://sqlinthewild.co.za/index.php/2008/05/02/token-and-perm-user-store-growth/
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
December 3, 2010 at 9:23 am
i have run this sql (copied it from the link you added) on the Server :
SELECT SUM(single_pages_kb + multi_pages_kb)/1024.0, type, name
FROM sys.dm_os_memory_clerks
GROUP BY type, name
ORDER BY SUM(single_pages_kb + multi_pages_kb)/1024.0 DESC
The TokenAndPermUserStore is around 1.5 Mb so that's not really big..so i don't think that's the problem.
When i look at all values in the memory_clerks dmv there's nothing really big at all..
169.859375 MEMORYCLERK_SQLGENERAL Default
56.078125 USERSTORE_SCHEMAMGR SchemaMgr Store
50.101562 CACHESTORE_OBJCP Object Plans
37.226562 MEMORYCLERK_SOSNODE SOS_Node
19.726562 MEMORYCLERK_SQLSTORENG Default
17.031250 CACHESTORE_SQLCP SQL Plans
8.710937 OBJECTSTORE_LOCK_MANAGER Lock Manager : Node 0
6.359375 CACHESTORE_PHDR Bound Trees
4.820312 MEMORYCLERK_XE XE Engine
4.296875 USERSTORE_DBMETADATA Servi
1.734375 OBJECTSTORE_SNI_PACKET SNIPacket
1.546875 USERSTORE_TOKENPERM TokenAndPermUserStore
The SQL Server has a total of 13 Gb in use, out of 16 Gb total in the server.
I've been tracing the server for some time but haven't been able to find anything out of the ordinary.
So if you have any more suggestions that would be appreciated!
Kind regards.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply