In the first installment of this article series, we talked about how SQL Monitor 2.2 was being used to remotely monitor the active/active SQL Server cluster hosting the two SQL Server instances that run the back end SQLServerCentral and Simple-Talk databases.
As an experiment in getting the SQL Server community to participate in configuring and tuning the servers for optimum performance and availability, we asked you to review the properties of the two instances (as revealed by SQL Monitor at monitor.red-gate.com), along with their databases, and provide us with your feedback. While SQL Monitor reveals a lot more than just instance and database properties, we decided to start out small, focusing on those properties, and to branch out into different topics in later articles.
In this second installment, I am going to review some of the feedback that we received on the instance and database properties, in addition to adding my own feedback on what I saw. I want to thank everyone who contributed feedback for the first article, and I hope everyone will continue to offer feedback throughout this article series.
Little Use of Filegroups and Multiple Files
Of the 11 user databases on both instances, only one has filegroups and multiple files, and several people asked why. The honest answer is because nobody has put any thought into how filegroups and multiple files might best be put to use, in order to increase the database’s performance and availability. The only user database that uses filegroups and multiple files was delivered that way by the vendor who supplied the forum software for SQLServerCentral.com.
Generally speaking, I recommend that every database should have at least two filegroups with at least two files. The first filegroup and file should only contain the system objects, while the second filegroup and file should contain the user objects. The reason for doing this is availability. Depending on the situation, should the file with the data become corrupt, but the file with the system objects remain uncorrupted, the database may be able to remain online, aiding in the fixing of any corruption problems. If both the system objects and data are in the same file and filegroup, and the single file becomes corrupt, then quite often the corrupted database is no longer accessible.
Other common reasons to use multiple filegroups and files are performance and manageability. Currently, the performance and size of the databases is “under control”, but this doesn’t mean this will always be the case. We should take a closer look at how we use filegroups and multiple files.
Database Autogrowth Setting
We received feedback that the autogrowth settings of many of the databases where not ideal from a best practices point of view. I have summarized the database information in the following chart, to better see what the settings were, and what they are now that I have reviewed them and made changes.
| Servers & Databases | Old Setting | Old Setting | New Setting | New Setting |
ins1 | MDF Autogrowth | Log Autogrowth | MDF Autogrowth | Log Autogrowth |
master | 10% | 10% | 10% | 10% |
tempdb | 100MB | 250MB | 128MB | 256MB |
model | 1MB | 10% | 1MB | 10% |
msdb | 10% | 10% | 10% | 10% |
ips_notifications | 1MB | 10% | 1MB | 1MB |
Prettifier | 1MB | 10% | 1MB | 1MB |
SQLServerCentralForums | 10% | 10% | 512MB | 128MB |
Community Server | 10% | 10% | 128MB | 128MB |
Database Weekly | 1MB | 10% | 128MB | 128MB |
SQLServerCentral | 1MB | 10% | 1024MB | 512MB |
SingleSignOn | 1MB | 10% | 128MB | 2MB |
ws_ftp_server | 1MB | 10% | 1MB | 1MB |
ins2 | ||||
master | 10% | 10% | 10% | 10% |
tempdb | 100MB | 100MB | 128MB | 128MB |
model | 1MB | 10% | 1MB | 10% |
msdb | 10% | 10% | 10% | 10% |
apps_excuse | 10% | 10% | 1MB | 1MB |
apps_prettifier | 10% | 10% | 1MB | 1MB |
distribution | 1MB | 10% | 1024MB | 128MB |
Simple-Talk | 10% | 10% | 1024MB | 128MB |
It’s always been my philosophy that autogrowth should not be used to manage the size of your database and log files. Instead, I think the DBA needs to proactively pre-size each database when it is first created to its estimated future size a year or so in the future. Of course, such a guess might be wrong, and because of this, I feel the DBA should proactively watch the amount of space being used in each database. If it appears that more room will soon be needed, the DBA should manually increase the size to an appropriate number, again based on the expected growth of the database over the upcoming twelve months or so. If a DBA is proactive, then autogrowth should never have to kick it, other than perhaps when a guestimate was off, or some unexpected event occurred that caused growth.
Of course, many databases aren’t proactively managed in the real world, and this has traditionally included most of the databases on the SSC cluster. For example, notice that many of the older MDF autogrowth numbers were 1MB. Because of this low setting, and the lack of proactive DBA management of the files, some have auto-grown many times. More specifically, the SQLServerCentral database was set to 1MB autogrowth, but the MDF is now 22,468MB in size, which means this database has probably auto-grown about 22,468 times over its lifespan. Just think of the resource hit (over time) the database took each time it grew, and think of all the physical file fragmentation this has contributed to, further hurting performance.
I’m not going to review each of the databases, but if you look at the chart above, you can see what the settings were before I made changes, and what changes, if any, I made. You are probably wondering why I chose the numbers that I did, and that is a fair question. First of all, there is no ideal number for the autogrowth setting, and if I had made different choices, I doubt it would affect performance. Assuming the databases will proactively be managed in the future, autogrowth should seldom, if ever, kick in. You may notice that I have used a fixed growth amount for the user databases, not a percentage amount. This a personal preference of mine, and I make this choice because I prefer to know exactly what size autogrowth will be, rather than a percentage amount, which results in different autogrowth sizes each time. The number I assigned for autogrowth appears to vary a lot. I selected the numbers based on my knowledge of the current sizes of each database and log file, along with my knowledge of how fast I expect the database to grow. Some of the databases are essentially static in size, while others are growing rapidly. The numbers I chose reflect this information.
The system databases need a special mention. As a general policy, I don’t change the defaults on master, msdb or model, as I have never ever seen a good reason to change them. Tempdb and the distribution databases are another situation altogether. The default tempdb and distribution database size and autogrowth settings for SQL Server are inappropriate for virtually every instance, and I always manually pre-size them to what I guestimate they need to be, along with adding a more appropriate autogrowth size. For the SSC servers, the tempdb settings were good, but I did tweak them a little bit. On the other hand, the distribution database was created with default database settings, so I changed them appropriately.
Now that I have set more appropriate autogrowth setting, if these databases do experience an unexpected growth spurt, the autogrowth overhead will be minimized.
Summary
The point of this clinic was to look at obvious settings that could be changed to improve the performance of the database servers behind Simple-Talk and SQLServerCentral. As you can see, we found out a lot of things about the server, some of which needed to be corrected. While I was able to fix some of the problems immediately, many still remain, and will require additional follow-up and research before the decision is made to correct them.
In a future article, I will examine a few more of the items that were presented as feedback and include my comments on those items as well.
If you have visited monitor.red-gate.com, you will have noticed that the two server instances regularly have resource contention. In a future installment of this clinic, I will also focus on some of the performance counters to get to the bottom of these resource problems.