Stuff People Tell Me Is The Default


Playing The Odds

Assuming that life carries on as usual for a couple more weeks, I’ll have been a consultant here for three years.

In that time, I’ve had some really interesting conversations with people, mostly about why things are the way they are on their servers.

My favorite response is “isn’t that the default?”, because it’s never the default.

I think that’s called an unforced error.

Here are some of my favorites:
Oh you
  1. Nonclustered Primary Keys
  2. Fill factor < 100
  3. MAXDOP 1
  4. Fifteen minute log backups
  5. Single column indexes
  6. Indexes including every column
  7. Database shrink tasks at the end of maintenance plans
  8. Read uncommitted
  9. Priority Boost

Thanks for reading!

Brent says: Ooo, yeah, my favorite: “SQL Server has built-in emails that alert me when there’s a problem, right?”

Previous Post
How to Create Deadlocks and Troubleshoot Them
Next Post
[Video] Office Hours 2018/5/16 (With Transcriptions)

30 Comments. Leave new

  • Autoshrink & Autoclose

  • emanuele.meazzo1
    May 18, 2018 8:56 am

    Once I found “Automatically create statistics” off, they said it was the default ¯\_(?)_/¯

  • Luke Salter
    May 18, 2018 9:18 am

    1mb data file growth… oh wait.

  • What’s wrong with 15 minute log backups. Not a default, but what I use on one server only.

    • There’s nothing WRONG with it – just like there’s nothing WRONG with a single column index, necessarily. It’s just not the DEFAULT. That’s what this post is about.

      I will say though, that when we’re with clients, and I ask, “Why not 10 minutes? Or 5 minutes? Do you want to lose less data?” Then management usually suddenly turns to the DBA and says, “Wait – yeah, why would you pick 15 when 5 or 10 minutes would let us lose less data? Why 15?”

      • so… what’s an Optimial selection for this?

        • Luke Salter
          May 18, 2018 11:33 am

          @Wing it’s entirely dependant on the system, for example if you have a CRM system you probably want more frequent t-log backups than some ETL/BI System. imo you cannot have a 1rule fits all for your backup solution.

          • We use CommVault. The discussion with our backup engineer when I said, “Oh crap no. We’re using Ola Hallengren scripts and a file share. This once-a-day backup crap is not going to cut it,” was… interesting.

        • Micheal Hunt
          May 18, 2018 12:14 pm
          • Luke Salter
            May 18, 2018 12:36 pm

            Pitch this idea to a Central Backup Solution like lets say, Commvault – they would cry haha

          • (Really replying to Luke Salter, but it seems I can’t)
            Yeah, I’m forced to have our server team backup my databases with Commvault, it has a hard enough time getting my 2x weekly fulls done before the differentials the next day…

            I can’t even imagine it keeping up with 15 minute log backups…

      • Gavin Harris
        May 18, 2018 11:41 am

        DBA back to Management – Because that’s what you told my predecessor you wanted.
        Manager – You mean my processor told your predecessor…. 😉

        More seriously, keep reviewing your infrastructure & settings, its never set in stone.

  • Curtis Krumel
    May 18, 2018 11:07 am

    Full backups truncate the transaction log, don’t they?

  • oscar valle
    May 18, 2018 11:13 am

    Maximum server memory

  • database settings I love to stumble across in Production:
    – < 5 MB Autogrowth settings.
    – All the database's files in the C:\Program Files\… folder path. log files and backup files included!
    – databases left in Simple recovery.
    – Last backup? We backup the Server / VM weekly or daily or… isn't that good enough?!?

    And then there are the wonderful vendor-specific database designs that I constantly see. And die a little inside each time:
    – We don't need to define Primary Keys in the database. The application knows what the keys are!
    – Foreign Keys? See above!!
    – Clustered indexes? What's that? We LOVE big HEAPS of data!!
    – Naming schemes? No way! It's so much more fun when those undefined foreign key fields change names with each and every table throughout the system! Like a Where's Waldo or a Choose your Own Adventure anytime you try to write that custom report!
    – And we may be using five or six varchar fields to make that implied Primary Key. Or not. You'll never be certain!

    (Teeth gritted)

    I swear, just the clustered index thing? Yeah, the Blitz scripts let me turn around a business-critical system at a previous job that was crashing weekly. To the point of rebooting the database each time and logging tickets with the vendor. Simply by creating about 3 indexes, per Blitz's excellent recommendations. Those clustered indexes ENDED the problem. But the vendor didn't have sense enough to do any of that. "It's your data, not ours!" yeah. But it's YOUR schema, vendor. Yours. Sigh.


  • Kevin Fries
    May 18, 2018 11:38 am

    “Isn’t Instant File Initialization a default for SQL Server? ”
    “Isn’t backup compression a default?”

  • lightweight pooling = 1

  • Mark Horninger
    May 18, 2018 11:47 am

    Oh man I had one client argue about backup time – they insisted it had to be noon; “thats what microsoft recommends” and were wondering why the system was slow then.

  • thundercougarfalcon
    May 18, 2018 11:51 am

    “Our last DBA said to save on space we should delete records past X days, and if we need to query them we just restore the last backup and apply the logs prior to the deletion… That’s what everyone does, right? By the way, what are logs? And why does the database continue to grow?”
    This was on a ~300GB mission critical database…
    I didn’t work there long…

  • 4kb Disk Blocksize… Hey… Why the restore took so much time?

  • Clustered index (or any index) on a GUID identity column aka NEWID() with a fill factor of 100. This was on very busy session state database for a web app. Index maintenance nightly and app was responsive first thing in the morning, but was at a crawl by EOD. Banana splits good. Page splits not so good.

  • Alvaro Costa-Neto
    May 18, 2018 3:51 pm

    Windows Fibers Lightweight Pooling!! Because this is lightweight and brings fiber for my SQL Server diet!!!! LOL.

  • … all new features are enabled by default, especially Compressed backups

  • Max server memory makes my list. Following that is the default for putting all database files on the same disk.

  • Collation of SQL Server Reporting Services databases (ReportServer and ReportServerTempdb) is Latin1_General_CI_AS.
    Collation of Integration Services database SSISDB is Latin1_General_CI_AS.

  • I feel fine totally admitting this…. I built an agent job that I have running in all environments every week that turns auto shrink OFF…..

  • REORGANIZE and “Best Practice Index Maintenance”, both of which are “defaults” in the minds of most and should never be. ‘Nuff said.


Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.