June 1, 2007 at 8:41 am
We've moved most of our databases from SQL 2000 to SQL 2005 (64 bit, SP3, 16 GB RAM). Under certain situations we've seen a decrease in performance though and it seems to be related to the number of databases. Servers with 200 databases perform extremely better than servers with 600 databases, regardless of sql activity. The 2 areas that we have seen the performance hit the most is 1) procedure recompiles and 2) database ddl operations (dropping and recreating stored procedures, views, functions, etc.). For example, we saw this when running database upgrades for our application. All procedures, views, and functions were dropped and recreated. We did this off hours, no activity other than the upgrades. About 300 databases on a server. One upgrade took 5 minutes on average. However, if we detached all the other databases, one upgrade took less than 2 minutes.
Has anyone seen this behaviour before? Are there any standards to how many databases to put on a server? Are there particular performance counters that I should be looking at?
thanks in advance
Regards,
Rubes
June 1, 2007 at 10:06 am
Are any of the databases using autoclose or autoshrink?
June 1, 2007 at 10:20 am
No.
Auto Create Statistics and Auto Update Statistics are on. Auto Close and Auto Shrink are off.
Regards,
Rubes
June 1, 2007 at 10:26 am
Do you have a lot of noise on the netwrok traffic on 2005 compared to 2000?
June 1, 2007 at 10:30 am
No, not really. The application has stayed the same, so the database hits would be identical.
Regards,
Rubes
June 1, 2007 at 1:52 pm
That's not what I meant. I mean network traffic where application like EM or SSIS checks to see if the server is still online. That can be one cause of your problem but I doubt it.
June 1, 2007 at 2:09 pm
Not that I am aware of.
Regards,
Rubes
June 4, 2007 at 12:36 am
June 4, 2007 at 8:00 am
Do you have any inter-database dependencies in your objects? If so, this could be a problem as sql server checks other dbs as appropriate.
are you explicitly interacting with your objects using 1, 2, 3 or 4 part naming? (server.database.scheme.objectname)
What type of security set up are you using for both users and logins?
I have a client with 6400+ databases on 1 low-end server. I have not seen object compiles take an extraordinary amount time for a single database. Note that they don't use many true database objects due to having lots of existing code from their Access days and no opportunity to rearchitect the app.
ms_foreachdb was taking quite some time to chew threw all the dbs so I had a developer build a multi-threaded "database shotgun" that will execute a script/file against the databases using a configurable number of threads. 12-15 seems to be the optimum number for their hardware. You may wish to build the same type of tool.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply