|
Wanderer
Master Smack Fu Yak Hacker
1168 Posts |
 Posted - 2003-11-25 : 08:36:45
|
| A funny topic, I know...Our system (code purchased from vendor now) has an *interesting* login procedure, and we appear to have login performance issues. Any help/suggestions/cash (:-) kindly accepted ...some basics : SQL2K sp2+slammer (can't go nSP3 until app works with MDac 2.7); Win 2K sp 3. 160 G primary database, 4 smaller database (30 to 300 MB), as part of the system.Now what happens on a login, roughly, is this :User x111111 logins in (SQL login, not windows auth), accesses SECURITY database, and determines that the userid is valid and has a certain role. Then it runs a stored procedure that creates a SQL Login based on the userid that logged in, so X111111xxxx is created, and given right to the 2 primary databases. Then the connections to Security with userid X111111 is dropped.The application on the user's PC then connects with the newly created userds (last part is random, and it has a randomly created password), and then does their normal work against the production database. If the user sign-out of application, then when they login again, they start at userid creation section above, getting another userids created etc.What this effectively means is we have between 300 and 500 SQL Logins get created every day, and cleaned up at night.pseudo process for LOGIN :1> login to SQL with userids2> select from security metadata database3> select from security database4> random userid created5> disconnect from SQL6> connect to SQL with Random useridLOGIN PROCESS completed, user now doing normal work.Naturally, all the user sees is the inital lgoin, and then when he is presented with screen to work. The problem is that this can be from 2 seconds early in the morning to 30 seconds, or timeout, later, so obviously we need to figure out improvements,NOW : We are severally constrained by th e "applications arcgitecture" to stay with this random userid creation idea. Given that, I have conerns that SQL's system tables were never intended as "transactional tables" - we have maybe 60 000 inserted and deletes during the course of a year; also the code that runs the login creation - was it ever considering in terms of many iterations running and potentially locking each other ? The vendor reported that they had tracked the slow login to 2 SQL statements in the SQL DMO, namely :select DISTINCT o.name, loginname = (case when (o.sid = 0x00) then NULL else l.loginname end), user_name(o.gid), o.uid, o.hasdbaccess from dbo.sysusers o left join (select sid, loginname, 1 as matched from master.dbo.syslogins) l on l.sid = o.sid where ((o.issqlrole != 1 and o.isapprole != 1 and o.status != 0 and matched is not null) or (o.sid = 0x00) and o.hasdbaccess = 1) and o.isaliased != 1 and (o.name = N'tester$DMP')select DISTINCT o.name, loginname = (case when (o.sid = 0x00) then NULL else l.loginname end), user_name(o.gid), o.uid, o.hasdbaccess from dbo.sysusers o left join (select sid, loginname, 1 as matched from master.dbo.syslogins) l on l.sid = o.sid where ((o.issqlrole != 1 and o.isapprole != 1 and o.status != 0 and matched is not null) or (o.sid = 0x00) and o.hasdbaccess = 1) and o.isaliased != 1 order by o.nameI have traced production, and seen that the SQL statements for a SPid, during the login process, seem to run between 2875 ms and 9668 ms. I am looking into sysindexes to determine scnadensity and logical fragmentation on the system tables that get used. Can we dbcc reindex system tables ? I read somewhere that guys were having issues due to massive amounts of changes that SAP (I think) was making to system tables, which they couldn't reindex ?HOPEFULLY I haven't thrown too much blurb at you ...*#&#* *#&#* *#&#* *#&#* Chaos, Disorder and Panic ... my work is done here! |
|
SQL login creation performance...