This is part three of an article series  on the incremental tuning process for the active/active SQL Server cluster which hosts the two SQL Server instances that run the backend databases for SQLServerCentral.com and Simple-Talk.com.

In the Part One, we asked members of the SQL Server community to participate in reviewing the configuration of the two servers. We asked community members to check out the instances at monitor.red-gate.com and to offer specific feedback related to some of the properties that are revealed using SQL Monitor 2.2.

In the Part Two, we discussed some of the original feedback we received. In Part Three, I’m going to talk about additional feedback we received. In later articles, we’ll delve much deeper, but for now we’re taking it slow and methodical, allowing members of the community to offer feedback each step of the way.

SQL Server Build Level

It was pointed out that the current SQL Server build level for both instances is Build 2714, which is old (SP1, CU2). Currently, Build 4272, (SP2, CU2) is the most recent version. These instances were built in November 2010, and as far as I know, they have not been updated since. I will have to investigate why they have not been updated, but I imagine it is because these servers are being hosted by RackSpace, and performing the update is not an easy task. I will find out more about this and share it with you later in this series.

Restricted File Growth Sizes

It was pointed out that a few of the database and log files had been capped to a maximum file size. When I found these, I changed the setting back to unrestricted growth. While I know some DBAs prefer to restrict growth, I never do. My philosophy is to always leave file growth unrestricted, but to carefully monitor my servers for available disk space. I prefer this method of managing my disk space instead of using an arbitrary, restricted file size limit to manage disk space.

Database Compatibility Levels

While we are running SQL Server 2008, it was pointed out that not all of the databases have been set to the 10 compatibility level. One database is set to 9 and another three are set to 8. I am not familiar enough with these particular databases to know why this is the case, so I asked our current developer, who works on the website and with the SQL Server code, why these four databases (two from external vendors and two written in-house) are still using the older compatibility levels. Unfortunately, he didn’t know the answer, as he has only been in this position for six months and was not involved in writing any of the original code. He said that he would research this and get back with me with an answer.

Page Verify Setting

It was pointed out that two databases’ Page Verify settings were set to Torn Page Detection instead of Checksum. I imagine that this was a result of an upgrade, and these settings were never set correctly. I changed both of these databases to Checksum.

Database Collation Settings

While most of the databases use the Latin1_General_CI_AS collation, it was pointed out that five of them use the SQL_Latin1_General_CP1_CI_AS collation. It appears that at least three out of these five are third-party designed databases and the vendors, for whatever reason, chose SQL_Latin1_General_CP1_CI_AS. The other two databases that use SQL_Latin1_General_CP1_CI_AS were designed in-house, but I don’t know why this collation was selected for them, and the original developers are not around to ask. On the other hand, there doesn’t appear to be any reason why these collations should be changed, as they aren’t affecting performance.

Why Does Tempdb Have Eight Physical Files?

One of the questions that were asked about our server configuration was why does tempdb on each instance have eight physical files. Before the SQLServerCentral.com databases were moved to the current cluster, one of the biggest bottlenecks on the older server was tempdb. With the continued growth of SQLServerCentral and Simple-Talk, we assumed this problem would only get worse.

Because of this, when we built the new cluster, we provisioned each server to have an array dedicated to tempdb. We also divided the tempdb files to equal the number of cores in each system, which is eight. We did this as a best guess, as we knew tempdb was a huge problem, but we didn’t know what would happen after the databases were moved to the new system. In other words, without the ability to perform proper testing, we intentionally overprovisioned tempdb just in case. Did we make the right decision? I don’t know, as we have never followed up to check current tempdb. I hope to devote an entire SSC clinic to this in the near future.

Summary

The point of this article was to take a quick look at some of the obvious settings on the two SQL Server instances and see if anything might be amiss.  As you can see, we found a few things that needed changing, and we still have some outstanding questions that need to be followed-up. In the next article in this series, I’m going to take a look at some of performance monitor counters on the servers to help us identify any potential resource contention issues.