Lots of Tables and a Little DMO
-
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/awarren/lotsoftablesandalittledmo.asp>http://www.sqlservercentral.com/columnists/awarren/lotsoftablesandalittledmo.asp
-
Interesting. We've got over 1,400 tables in each of our databases (20+) in a Sage Enterprise system, and we don't see 45 second delays at login, ever, even when it's being hammered. On the live system, which is a quad 1.4Ghz P4 box, 2Gb RAM, 10/100 NIC, you're logged in through Query Analyzer in less than a second. Going into Enterprise Manager and opening up the "Tables" node on one of the databases, you notice approx. a 3-4 second delay whilst it's gathering information on the tables. Likewise, if you right click and select Properties and open up the Permissions dialog for a table (picked the biggest, just over 1.8million rows), it again takes 3-4 seconds, maximum. This is a table that's not owned by the logged in user.
So I did an experiment to confirm what Andy suggested about selecting the permissions on 2,000 objects, and created 20,000 unpopulated tables in a new database called TEST, each with a single integer column on our test box, a lowly 200Mhz PII with 256Mb RAM, standard 10/100 NIC running SQL Server 7.0. These were created with the sa account.
Logged out, logged back in through QA and EM, back in within a couple of seconds.
Then I created a new user, sqltest, with SQL Server authentication, default database set to 'TEST', and logged in using QA. Still less than second to connect.
Finally, I went into Enterprise Manager using sa this time, and things got a bit more interesting. Opened up the server node, opened the new database, everything very responsive. Then I clicked on the 'Tables' node again and it took 22 seconds for all of the tables to appear in the right hand pane of MMC. Noted that whilst this was running, the CPU on the server maxed out to 100% for 5-6 seconds, but with no noticeable increase in memory usage, then dropped to zero just as the CPU started to hit 100% on the local machine (a PII 300Mhz) and memory usage began to climb. So it seemed that SQL Server was doing a lot of work gathering the info, then SQLDMO was doing a lot of work storing it on the client side, iterating through the object collection, as Andy stated, and building up a big structure in memory to hold it all. (The time taken fits in with the 9-10 seconds I saw on an application I once worked on where SQLDMO was being used to iterate through a table collection, also on a PII 300, but with 10,000 tables, so the timing's at least consistent.)
As an aside, I disconnected EM from the server, re-connected using the sqltest user and the same process of opening up the tables node took over a minute this time (not affected by load conditions as it was unused at the time of testing).
Then the big test - reconnected with sa, right clicked on a table, selected 'Properties', then clicked on the Permissions button. Server CPU hit 100% for 10 seconds, with very little extra memory usage, and with nothing on my local CPU, then server CPU dropped to zero, and at the same time the local machine's CPU hit 100% but this time for over five minutes. Also noted that from the moment the local CPU went to 100%, memory usage slowly crept up, using over 30Mb in total by the time Enterprise Manager displayed the permissions for the table (just two users, dbo and sqltest had permissions on the database/tables).
Interestingly, I selected another table from the drop down and its permissions were displayed immediately, which again implies that the permissions information is gathered for all the tables in the database and cached locally, which means lots of client RAM and CPU usage when the information is gathered.
Exited the Permissions dialog, which returned me back to the Properties dialog, then clicked on Permissions again, another five minute wait...
The only thing I couldn't reproduce was the extra-slow login, though I'm not sure Query Analyser uses SQLDMO to connect (would have thought it would, but...)
So in summary, it looks like
a) SQL DMO DOES noticeably slow down with a large number of tables/objects, as expected from Andy's original statement.
-and-
b) It's going to be worse on a low spec client PC - although it does take an initial hit, the server seems to have less bearing on the delay, as the SQLDMO object is being populated on the box where Enterprise Manager is running (or your application - whatever's using SQLDMO). So a decent local CPU and plenty of RAM will help - keep an eye on usage of both in task manager before you attempt to open up the Tables node, or before you iterate through the tables collection in your code.
May also be worth considering that disk and/or index fragmentation might increase the time taken on the server to gather this information, but unfortunately I didn't have time to set up a test for this.
Hope this gives you some useful pointers
Edited by - jonreade on 07/30/2003 05:40:30 AM
Jon -
Couldnt ask for a better reply! I think QA probably does direct calls to get the data rather than going through DMO, it's definitely more responsive (with fewer features to support of course).
Andy
-
Andy, just to confirm you're right. I ran ISQLW.exe through Dependency Walker (see Herve Roggero's article at
http://www.sqlservercentral.com/columnists/hroggero/doevents.asp) - great tool.
SQLDMO.dll does not appear to be loaded by QA (unlike Enterprise Manager, which definitely does load it), so it must be using some alternative method.
Jon Reade
Jon -
For those who havent tried DW, more info here:
http://www.sqlservercentral.com/columnists/awarren/dependencywalker.asp
Im sure it's just querying system tables and skipping the object idea, almost always faster - but there are tradeoffs!
Andy
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply