Configmgr SQL Server Sizing and Optimization

SQL-Calc-02

6/19/2017 – Docs. com is being retired.  Here is the link on OneDrive.

Update: Link for calculator updated.  Any problems email, tweet, or comment.  Thanks for letting me know.

Update II: Link updated after continued issues downloading and editing by some.  I moved the file to docs.com for sharing.  Docs.com does require you to sign in to download using an O365 account, Facebook account, Outlook.com / Live.com / Hotmail.com account.  I have also added it to Dropbox if you really don’t want to sign in to download the calculator, you can get it here.  I will be testing out the collection features and posting other files to docs.com, creating an article with every doc isn’t always feasible.  Any problems, just leave a comment, email, tweet, so on.

Several weeks ago I wrote a post about sizing for Configmgr and a few days later I was discussing it with two friends and MVPs when they pointed out that my information about using a single file for the Configmgr data file was incorrect.  I had gotten this information from another friend who works with SQL so I shared that info with them and they told me I (and he were) WRONG!  It is a good thing my wife never reads my blog because if she saw that I had admitted publicly, in writing, that I was wrong, it would probably be printed up as wallpaper and used in every room in our house with a large all weather banner hanging from our roof for added effect.

So I have been doing a lot of reading on SQL over the last several weeks and I have updated my Configmgr database sizing worksheet quite a bit.  Including tabs regarding disk configuration and server memory calculations.  For most people, this information will be nothing more than interesting, for others like me who architect, build, and remediate Configmgr this can be quite handy.

There is quite a bit of information I learned that isn’t included in this worksheet and I will briefly mention some of them below should you want to do your own research into them, or just send me an email and I will be happy to answer any questions and share my research notes, stored in OneNote of course.

And if you find any errors, please let me know.

Optimize for Ad Hoc Workloads – True
Set the “AUTO_CLOSE” option to OFF
Rebuild Indexes CMMonitor maintenance db via Steve Thompson
Update Statistics Scheduled Tasks or Auto
Adjust Power Savings Plan to MAX
HBA Drivers and Queue Depth
Virtualized SQL requires additional consideration over physical
Exclude SQL processes, folders, and files from AV just like Inbox folders
Enable instant file initialization
NIC performance and tweaks for RSS / vRSS, VMQ
WSUS and SUP db need love too
Performance Counters
SQLServer:Buffer Manager -> Buffer cache hit ratio(>90-95%)
SQLServer:Buffer Manager ->Free pages(>640)
SQLServer:Buffer Manager ->Lazy writes/sec (<20)
SQLServer:Buffer Manager ->Page life expectancy (>300)
SQLServer:Buffer Manager ->Page reads/sec (<90)
SQLServer:Buffer Manager ->Page writes/sec (<90)
SQLServer:Memory Manager -> Target Server Memory (KB) (Target >= Total)
SQLServer:Memory Manager -> Total Server Memory (KB) (Target >= Total)

Configmgr Sizing Worksheets

Hierarchy Planning Worksheet v2.2

DB Sizing Worksheet v2.2

Several years ago Kent A. and someone else put out a couple spreadsheets on database sizing and server sizing for Configmgr.  I used them off and on for a few years and over time I have revised them based on the newer SQL server best practices (SQL 2012 R2+).

In the days of SQL 2008 and earlier, the common db practice was to have multiple files for the configmgr database files (MDF & NDF files), while the TempDB was configured to use a single file (MDF file).  And all databse transaction log files (LDF Files) were configured to use single files alongside each database.

TempDB or tempdb database: Is a globally used file available and is used for holding temporary objects such as local and global tables, stored procedures, table variables, row versions, and query results. The tempdb is temporary, it gets recreated at each reboot or SQL service start.  It is literally empty at each start, all data is purged and the db cannot be backed up.  The tempdb.mdf is the data file, templog.ldf is the log file for the tempdb.  The tempdb autogrowth setting can cause serious performance issues if the size of the db is too small and it is constantly growing the db.  A common practice when running a SQL server in an Azure VM is to put the temdb files on the D drive since the drive is non-persistent during reboots.

The best practice for SQL 2012 R2 and beyond is to use a single database file for the Configmgr database and multiple files for the TempDB files (MDF & NDF files).  The transaction log files (LDF files) are still configured for a single file as before.  My buddy Steve Thompson points this out in a recent post where he discusses the proper tempdb creation practices.  Steve is also a former SQL MVP, now a Configmgr MVP so he knows both well.

The topic of using a CAS or multiple primaries for a customer came up on a discussion list today and when I replied I had screen shots of my sql sizing spreadsheet and my site sizing spreadsheet and a few people asked me for copies of them.  They can be downloaded now and as they get updated I will do my best to make the new versions available.

Any questions please let me know.

P.S. SQL should always be local.