Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQLServerCentral Best Practices Clinic: Part 2

By Brad McGehee,

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.

Total article views: 4968 | Views in the last 30 days: 3
 
Related Articles
FORUM

Restoring database - Filegroups

Restoring database - Filegroups

FORUM

Filegroup vs performance

filegroup

FORUM

FileGroups

FileGroups RESTORE

FORUM

Autogrowth out of control

every so often, the Autogrowth on one of databases goes to 12800%

FORUM

Backup FileGroup and Restore on Secondary Database

Backup FileGroup and Restore on Secondary Database with same filegroups

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones