July 22, 2003 at 4:00 pm
Is there index or something that can be set to increase a users login time? When a user not the owner of the tables has SIUP on over 2000 tables, there is a 45 second wait to login. Just viewing the permissions for the user takes considerable time.
Thanks!
July 23, 2003 at 4:12 pm
Are you sure it's the number of tables and not something else? I dont have a db with 2k tables so you might be dead on, but good to check just to be sure.
Andy
July 25, 2003 at 7:07 am
Well, It looks like it is doing a full count of all permissions on login. If they only have select, it's better. If I through them in datareader or datawriter, thr problem goes away. Also, the owner doesn't have the problem. I'm not very good with Profiler, but the trace I ran looks like it is looping through a series of statements for each table.
Is 2000+ tables an uncommon practice?
Thanks again,
Jim
July 25, 2003 at 5:48 pm
Hard to say what is common. 2000 seems like a lot, but you should build what you need. Are you granting permissions to the user, or do you have them in a role?
Andy
July 26, 2003 at 12:26 pm
I tried putting the permissions on both the user and a role I create on the database individually. The user login behaved the same way in both attempts. I'm looking at a different design that will reduce tables but it would be good to know if this is a limitation.
-Jim
July 27, 2003 at 9:05 am
Hey Jim,
I've done a little more testing and still don't see the problem, let's see what other readers can come up with - article about it will run on Wed!
http://qa.sqlservercentral.com/columnists/awarren/lotsoftablesandalittledmo.asp
Andy
July 29, 2003 at 8:27 pm
jfrank,
it sounds to me that it is something specific to your application. you can investigate more and find out exactly where the login process is waisting so much time by profiling the server, save the trace and run it in QA; there you can see the query plan in detail and find out exactly where your app is hanging. from there you can go further and find out more.
xmas
p.s. why I can't see the all thread? i see 6 last postings only.
July 30, 2003 at 3:02 am
Andy - nice summary.
However, without knowing how the connections are being made from the client, it is difficult to explore further. Also, it would be interesting to find out why the users need access to the tables. If the application accesses tables via stored procedures [and both the procs and the tables have the same owner], the user needs only execute permissions on the procs, not access to the tables.
August 3, 2003 at 7:19 pm
Just a general idea, in such mysterious situations, I would trace Everything, because you really dont have a prime suspect. Try to do it when the database is quiet, if it has such times. You cannot always assume that the statements you see in a trace are the direct cause of performance problems - there could be other things going on. Also, use Windows Performance Monitor too, in case its something on the server that SQLServer itself doesnt recognise the costs of. Monitor: physical disk, memory allocation & page faults of Windows as well as SQLServer, cpu, etc. Low activity can be as significant as high, such as SQLServer may be waiting for something.
Is there anything else on the server besides SQLServer ? (eg IIS, multiple instances)
Is the response time as bad for repeated logins ? And for concurrent logins ?
Are there other databases on the same server ? There arent any Suspect or inoperable ones are there ?
Are you using Trusted/NT authentication ?
August 4, 2003 at 9:31 am
2000 tables in one database is a design problem. I hope this is a package and not something home grown.
If you have lots of db's on one server, this could also contribute (if you have a server with dozens of db's, it takes longer to log into the server than one with a few). Also, what else is loaded on the server.
SP 3 improved login time. I have also gone as far as rebuilding a server and reinstalling sql 2000. For some reason this also improved login time.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply