SQLServerCentral Best Practices Clinic: Part 1

  • We see two quad core processors in the VM. (8 virtual CPUs)

  • One of the other things I noticed was that the collations are different for some of the databases. SQLServerCentral, as well as others, use Latin1_General_CI_AS as where some of the others use SQL_Latin1_General_CI_AS like CommunityServer and SQLServerCentralForums.

  • I also noticed that there are a lot of unlimited max file size settings for the databases. I personally prefer to set those limits to something reasonable and monitor the filegrowth and adjust them accordingly.

  • Can you tell us more about the hardware configuration?

  • Steven O (3/9/2011)

    I realize you wanted us to focus on settings we can see from the monitoring tool, but I am curious about instant file initialization, ad-hoc optimization, asynchronous update stats, etc.

    Is there a way to customize what the tool shows you?

    Regarding TempDB and the number of files, I was not able to find how many cores are on the servers.

    What about separating TempDB MDF and LDF?

    Also regarding separate filegroups for non-clustered indexes. In addition to the possible IO related benefits, I find that there are several administrative benefits, as well. For example, you then have more flexibility in where you locate your files, you can take filegroup backups, you can do filegroup dbcc checkdb commands, etc.

    The forums database seems to be using full text filegroups. What about locating them on a separate file system from the regular data files?

    The server has Dual Quad Core Xeon L5520 (2.66GHz) CPUs, for a total of eight cores.

    MDFs and LDFs are on separate RAID 10 arrays. Tempdb is also on its own array. The arrays are part of a SAN where we have 20 dedicated disks.

    Later in the series, I will go review the rest of the settings you asked about.

    Brad M. McGehee

  • I noticed when I first opened the tool that the INS1 page splits ratio to batch requests was running close to 20%, pushing the limits for my preferences, although it dropped back to about 10% after I started going back to check on it.

    I normally have several more memory counters, too. I am guessing that you don't look at Page Lookups/sec because you have close to zero adhoc access to the data and are not worried about poorly performing queries?

    Pages Reads/sec and writes/sec not being used because you know that your indexing is already as good as it can be? (or plenty of memory?)

    It's a nice tool, though.

    Peter Trast
    Microsoft Certified ...(insert many literal strings here)
    Microsoft Design Architect with Alexander Open Systems

Viewing 6 posts - 16 through 20 (of 20 total)

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