Does the amount of DB reduces the speed of the server if DBs are not in use?

Question

Does the amount of DB reduces the speed of the server if DBs are not in use?

jbeclapez

SSC-Addicted

Points: 405
More actions
January 13, 2020 at 7:56 am

#3714828

Hello,
I am running a server and I was telling my colleagues that the amount of database has hardly nothing to do with the speed of the server. Basically we have a server with around 20 DBs but we are only using 2 at a time and we make calculations on them. Those calculations can drastically slow down the server because of massive CPU usage. They tell me to delete the other databases to make the server faster. I refuse as I think it would have hardly no impact...
Would you agree with that?
Thanks

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

muthukkumaran Kaliyamoorthy SSCoach Points: 19026 More actions · Answer 1

jbeclapez wrote:

Hello,
I am running a server and I was telling my colleagues that the amount of database has hardly nothing to do with the speed of the server. Basically we have a server with around 20 DBs but we are only using 2 at a time and we make calculations on them. Those calculations can drastically slow down the server because of massive CPU usage. They tell me to delete the other databases to make the server faster. I refuse as I think it would have hardly no impact...
Would you agree with that?
Thanks

If you do not use the database you can delete it.

In term of resource usage - cpu, ram, IOPS, as along as the db does not have activity, then it will not use much resource, make sure this databases are excluded from DBA maintenance job.

Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 2

If the database is sitting there, not being accessed, not being connected to, zero activity, nah, it's not hurting performance. It can't. It will affect storage of course, but again, not in a way that affects performance. Now, any maintenance you run across all databases means it takes longer. Backups, etc., will be negatively affected by having to manage things that are not in use. However, query performance will not, cannot, be impacted by a database that is not being used. You can measure this and test it to prove it.

However, why keep them if they're not used?

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 3

jbeclapez wrote:

Hello,
I am running a server and I was telling my colleagues that the amount of database has hardly nothing to do with the speed of the server. Basically we have a server with around 20 DBs but we are only using 2 at a time and we make calculations on them. Those calculations can drastically slow down the server because of massive CPU usage. They tell me to delete the other databases to make the server faster. I refuse as I think it would have hardly no impact...
Would you agree with that?
Thanks

It's not totally clear to me. Is there ANY work at all being done on the 18 unused databases when the "2 at a time" calculations being done?

If not, you can easily prove it's just their code causing the issue. Rather than delete the other databases, just DETACH them and have them run their stuff. The immediate truth realized will be rather sad for them. Then, ATTACH the databases and tell them to fix their code.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

jbeclapez SSC-Addicted Points: 405 More actions · Answer 4

Thank you all for your feedback. Like always. Best community ever. I will close this ticket as it is confirming my own opinion. Have a good day.