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)