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)

24 thoughts on “Configmgr SQL Server Sizing and Optimization”

  1. seems to be a very useful thing (I used to use an old one for SCCM 2007 🙂 ). Unfortunately cannot download it – the link open the spreadsheet in O365 and does not allow to save it locally or edit as a copy in the cloud…. wondering how other people use it?

    Reply
  2. Hi Anthony, nice work 🙂
    Just wondering though, are you supposed to be dividing by 1024 instead of multiplying by 1024 in your F7 cell?
    You have the size per client as MB and the Total DB size in MB, but your calculating it as GB…

    Also, what are you classing as “Other” DB files as 30Gb of “other” stuff is not clear.

    Regards,
    Steve

    Reply
    • Actually, ignore that. I can see what you’re attempting to do, as a comparison against the HW Inv which is in KB, but… it doesn’t seem to produce a usable DB size for client numbers less than 1024.

      Would it also be worth taking into consideration the initial size of a fresh CM database, or do you feel the 5Mb per client allocation takes that into consideration?

      Reply
      • Sigh, really ignore me. For some reason on first opening the spreadsheet the HWInv was set to just 5k. Now I’ve found the drop down selection it all makes so much more sense!

        Good work, really like it.

        Reply
  3. Hello
    First of all, a huge thank you for your work.
    I have a few questions about the tab (db sizing).
    If I put the cell corresponding to wsus locally, is it normal that nothing changes on the other side? (DB WSUS always visible)
    Knowing that Microsoft does not recommend using a wsus database on the same sql instance as sccm, and assuming that I install the WSUS role on another server, can I follow a wsus configuration on a DB of your xls file with a version of sql server express and 30Gb for the database?

    Another question, what is the db “Other”? I do not understand the use.

    Another point the 1118 trace flag for SQL Server 2016 is obsolete.

    Another question. Since the core CPU is set to 16, the number of files returned by the xls file is set to 12 (4 + 4 + 2 + 1 + 1) with 2 files for wsus, whereas it is set to non-local. Is this normal? See screenshot.
    Concretely, I have trouble understanding the assignment of files for a vm with 16 cpu and no role wsus installed.

    Thank you for your help. https://uploads.disquscdn.com/images/a0217999f19389053e043ce304a67c915ff2568ae8ef705a7bd431031d0747b5.png

    Reply
    • The WSUS calcs are based on MSFT saying to make a 30GB mdf file. While I may or may not agree with it is another conversation.

      The “other” can be for 3rd party apps like 1E or 2Pint. Anything that is yellow can be adjusted so if you are not hosting any other db’s you can blank those out.

      Reply
  4. Hi there, I cant access your spreadsheet via any of the provided links…any chance you can email it to me?

    Reply

Leave a Reply to Zak Godwin Cancel reply