SQLServerCentral Best Practices Clinic: Part 1

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 720887

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

  • tgarland

    Hall of Fame

    Points: 3367

    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.

  • tgarland

    Hall of Fame

    Points: 3367

    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.

  • newsqldba

    SSC Veteran

    Points: 256

    Can you tell us more about the hardware configuration?

  • Brad McGehee

    SSCertifiable

    Points: 5272

    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
    DBA

  • Peter Trast

    SSCarpal Tunnel

    Points: 4332

    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 21 (of 21 total)

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