101 Things I Wish You Knew About SQL Server

bacon101Last week I was visiting the SolarWinds home office in Austin and hanging with my fellow Head Geeks. Our conversations often cover a wide range of topics and at times even turn into teaching lessons for each other. I get to learn about networks, storage, virtualization, monitoring, etc. and I get to teach my fellow Head Geeks a few things too. At some point last week I uttered the words “I’m going to write a blog post about the things I wish you knew about SQL Server”.

These aren’t absolute truths (except for number 44). They are just things I want everyone to know about SQL Server and being a SQL Server DBA.

So, here they are. As always, you’re welcome.

  1. SQL Server will do what you tell it to do. I find that PEBKAC is often the root cause for many issues.
  2. DBAs get paid for performance, but we keep our jobs with recovery.
  3. HA <> DR. If you can’t recover, you can’t keep your job. See previous item.
  4. Memory, CPU, disk, and network are all finite resources. Leave room for growth.
  5. 95% of all workloads will run just fine on modest hardware. Don’t listen to fools that architect crazy solutions for edge cases that won’t happen.
  6. Backups (AKA database dumps). You need them. Store them someplace safe, and on a different server. See number 2.
  7. Maintenance is mandatory. Find, or make, a window for rebuilding indexes, updating stats, running DBCC CHECKDB, and taking backups. See number 2.
  8. Don’t forget to backup security certificates, too. See number 2.
  9. Monitor for unused, misused, and duplicate indexes. These are just adding overhead you don’t need.
  10. Identity values can, and do, run out. Be prepared.
  11. Set the min and max memory configuration for your instance.
  12. Data and logs go on different disks. This isn’t necessarily about performance, it’s also about recovery. See number 2.
  13. One lane of traffic is not enough. Dual NICs and a big, fat pipe are what you want.
  14. But you can always blame the network anyway.
  15. Know the RTO and RPO for your applications. See number 2.
  16. When troubleshooting, sometimes the simplest answer is the right one.
  17. Focus on wait events and logical I/O when performance tuning. They help you find the root cause the fastest.
  18. The best clustered index keys are unique, narrow, static, and ever increasing.
  19. Just because you can create 999 indexes on a table doesn’t mean you should.
  20. Put system objects on a different filegroup than user objects. Oh, and you should be using filegroups. You are, right?
  21. Learn how to restore filegroups.
  22. Use SET NOCOUNT ON to reduce network traffic.
  23. Avoid SELECT * in production code. Look to return only the data that is necessary.
  24. Use schema and object owner when qualifying objects, it reduces lookup costs and makes you look like a smart developer.
  25. Enable the “optimize for ad-hoc workloads” option, unless you know you are the edge case such that this setting won’t help you.
  26. Adjust your cost threshold for parallelism BEFORE you consider adjusting your MAXDOP setting.
  27. Know your NUMA. When configuring memory and MAXDOP, keeping everything inside one NUMA node is a nice to have.
  28. Query governor is an easy way to stop bad queries before they happen.
  29. Optimize your tempdb for performance.
  30. The only way to know your backup succeeded is to test by doing a restore. See number 2.
  31. Don’t nest views. Just don’t.
  32. Enable backup compression for your server, it’s often worth the extra CPU.
  33. Row and Page compression are useful options as well, and often overlooked.
  34. Build a recovery strategy BEFORE you build a backup strategy. See number 2.
  35. Auto-shrink is the Peyton Manning of SQL Server. It looks like a great idea but you are often disappointed in the end result.
  36. Baseline for performance. Without baselines and metrics you have no idea if something is truly a problem or not.
  37. Don’t RDP to a server and launch SSMS, or Profiler, in an attempt to fix a production issue. Learn to work remotely.
  38. Learn how to use scripted installs.
  39. Server core is a great way to keep people away from your database servers that shouldn’t be touching them anyway.
  40. Use autogrowth but not the default growth values. Monitor for growth events and minimize their occurrence.
  41. If you don’t care about your CEO reporting on incorrect data, or your CIO going to jail, then NOLOCK is the query hint for you!
  42. Keep your transactions short.
  43. Triggers are awful, awful little creatures.
  44. But NULLs are far worse.
  45. Security should not be an afterthought when writing code. Assume that SQL injection is a virus and it will infect you at some point. Build accordingly.
  46. Instant file initialization is a good thing. You should be using this.
  47. Despite having zero enhancements since being introduced in SQL 2008, I still think Policy Based Management is a good thing that people should be using more.
  48. Then again, I think Powershell should be used by more, too. If your DBA can’t work a command line, don’t let them touch your data.
  49. SQL Server assumes a “cold cache” when building a query plan because disk storage is the last thing you should be worried about when it comes to performance.
  50. Object statistics are the most important piece of metadata in your database. Bad, or missing, stats will lead to bad query plans.
  51. Bad data leads to bad decisions.
  52. Great database performance starts with great database design.
  53. Enforce password policies for your SQL logins.
  54. Recycle your SQL Server error logs.
  55. Script SQL login and database user permissions nightly. You never know when you’ll need them during a DR event. See number 2.
  56. SQL Server Agent alerts are useful, and hardly used.
  57. Deadlocks are often the result of application logic and data access patterns. The engine doesn’t just get “tired” and start deadlocking.
  58. Testing against 10, 100, and 1000 rows is not an accurate test against a production workload.
  59. Table variables are not “in-memory” only, and are often not as good a choice as a temp table.
  60. For a performance boost learn how to stripe your backups. Then, learn how to restore a striped backup. See number 2.
  61. Resource governor is a great way to throttle workloads as needed, especially workloads that bloat your plan cache.
  62. Sometimes a scan is better than a seek. Learn how to read a query plan.
  63. AlwaysOn is a marketing term, not a feature. Availability Groups is what you meant to say.
  64. Learn how to build a cluster.
  65. Learn how to break a cluster.
  66. Learn how to repair a cluster.
  67. Application code is responsible for 100% of all performance issues. #hardtruth
  68. Know what trace flags are running on your system.
  69. Keep as many of your servers configured in the exact same way. This saves time troubleshooting.
  70. Before you write something yourself you should know there are many free scripts on the internet for you to use. But, sometimes, you get what you pay for.
  71. Data lasts longer than code. Treat it right.
  72. However, data will confess to anything if you torture it long enough.
  73. Know what the installer just did to your database server.
  74. Use Windows AD groups, not Windows Logins. There should be a separation of duties with regards to allowing data access.
  75. Custom database roles and server roles are a great way to provide custom permissions.
  76. Ordering and sorting of data consumes resources. Do it as few times as possible. Sometimes, it’s best done in the application layer.
  77. Task manager is a dirty, filthy liar.
  78. Understand how to work with large data sets without filling up the transaction log.
  79. Every now and then, go look at the system views, functions, and stored procedures. You will learn something new and useful.
  80. Multiple instances of SQL Server on a server know nothing of each other. It’s up to you to make sure they play nice.
  81. Don’t install services (SSRS, SSIS, SSAS) onto a server “just in case”. Only install the services that are needed.
  82. Assign a strong password to the ‘sa’ login, then disable it. You don’t need it, and neither does anyone else, especially a vendor. Use a different account for sysadmin activities.
  83. If your vendor requires the use of the ‘sa’ account, go find another vendor.
  84. If the vendor code creates a loopback linked server, go find another vendor.
  85. You can’t mirror a database using an actual mirror, no matter how hard you try.
  86. Implicit conversions can be avoided providing someone is willing to do the extra work.
  87. Over allocation of host resources leads to over commit of host resources, and that’s bad for everyone. Leave room for growth.
  88. Comments in code are notes to “future you”. Be nice to your future self and remind them what in the hell you were thinking.
  89. Learn how to make use of DNS aliases, it makes swapping servers around much easier when you don’t need to update connection strings.
  90. Practice recovering the master database. See number 2.
  91. Make sure you know the last time the SQL Server performance metrics have been reset, otherwise you may overlook the root cause of an issue.
  92. Junior DBAs know how to react. Senior DBAs know how to be proactive.
  93. Make sure you can rollback, when necessary.
  94. Practice upgrading SQL Server in a variety of ways, especially rolling upgrades.
  95. Capacity planning is often a worthless endeavor. You can’t predict the future. There’s always someone that decides to load 1TB of data without telling anyone.
  96. You’ll still get blamed for there not being enough disk space.
  97. You can’t fix stupid.
  98. Bad code and bad database design will bring good hardware to its knees, always.
  99. Empathy is the most important skill a DBA can have. Arrogance is the least important skill, yet often found in great abundance in the IT world.
  100. Everyone starts out with zero knowledge of SQL Server.
  101. The best DBA is a lazy DBA. Driven, but lazy.

 

That’s all I came up with during my flight(s) home last week. I’m sure I could add more to the list and likely will over time.

Enjoy!

27 thoughts on “101 Things I Wish You Knew About SQL Server”

    • Thanks! Duly noted and adding it to my extended list 1,001 things I want people to know about SQL Server.

      Reply
    • Kenneth, Do you use TFS for your source control needs? What is the process you follow to ensure checked in code is in the environment? Do you have any good resources? Thank you.

      Reply
  1. Great list…Some of them made me laugh hard 🙂

    For 18 – “The best clustered index keys are unique, narrow, static, and ever increasing.”

    This might not be a good idea for a highly concurrent workload though.

    Reply
    • Anup,

      Thanks for the comment. And you are right, a highly concurrent workload would likely need a different structure. But I think there are many, many examples of poor indexing choices that advocating the above is a better place for most to start, and adjust as needed.

      Reply
  2. Great list Tom! I use loopback linked servers for a number of reasons, especially to discover the resultset definition of DBCC commands. Too bad it stopped working in SQL Server 2012…

    Reply
    • Yes, but you are using them for a specific functional testing purpose. But Microsoft doesn’t support their use, and the GUI throws an error message if you try to create one. That’s why I made my statement above. If your vendor is advocating for building something that Microsoft has said “please don’t do this”, then you should go find another vendor.

      Reply
  3. If Paul Randall, Kimberley Tripp, Thomas Kejser or Thomas Larock (and a few other too) say something probably is not a good idea just LISTEN TO THEM don’t argue (unless your name is Bob W., then maybe argue….;-) )

    Reply
  4. like the list and the links. I knew most of those from various webinars and online readings and am still in the process of implementing some of them where I work. Thanks for the list.

    Reply
  5. Great list. I wish there was a poster of this (including the 101 bacon). I would hang it on my wall and highlight 97. Thanks for sharing!

    Reply
  6. > Use Windows AD groups, not Windows Logins.

    It would be nice. Do you though?

    It’s my perception that in most enterprises that have a separation of duties like that, the DBAs aren’t the ones given the power to create those logins, nor the power to force others to use them at the exclusion of other accounts they may have. Meanwhile the actual gatekeepers have little interest in maintaining AD all day and throw meaningless paperwork at you until you give up and go home – or farm it out to juniors who will screw it up anyway.

    I’ve made it a policy to check each account afterwards to make sure service accounts don’t still have a 90 day password expiry set. That’s fun the 2nd, 5th, and 10th times it happens, despite filling out that said paperwork correctly.

    And I’m not against paperwork. Or process. However they are often not designed with any kind of automation or long-term master data management in mind. Oh how nice it would be to have a web API and page, so that you can fill it out and get the record created (or automate it with a script), while they get all the data they want. But no, that never seems to happen. Instead it’s a poorly formatted Word document with incorrect titles, designed for a single account, and with fields that don’t make sense and are mostly left blank.

    Frankly when I find myself in that kind of environment I just thank heavens SQL still maintains mixed mode. At least we as DBAs can a) create logins, b) secure and cycle the passwords, c) document the owners to our satisfaction (in a database and not a folder of forgotten/discarded Word documents and paper files), and d) disable them when we can see they’re no longer in use.

    I just wanted to point out that this item feels like a wish list. While the best of us DBAs thrive on security, efficiency, process, documentation, AND automation, I feel like we are on the cutting edge in IT. The rest of the departments are stuck in a 1980s IBM era mentality of trying to maintain as much grip on Windows infrastructure to the exclusion of all other teams that might possibly somehow take some of their work away.

    Reply
    • Hi Cody, thanks for the comment.

      My experience with larger enterprises is that a separation of duties is necessary. The DBA should not be managing security. I believe it is best to have a defined security team reviewing the members of the AD groups, and this should not be a concern for the DBA.

      Service accounts should have passwords that expire. There are 3rd party apps out there that help to manage the password generation and rotation. I think we rotated passwords every year at a minimum, not every 90 days. But that was because of volume. With hundreds of servers, having to rotate every 90 days was extra work, and the audit team was comfortable with once a year.

      Like you said, these should be staples for any modern infrastructure.

      And yeah, I realize I am outlining what seems like Nirvana here, but having seen it done, I know it is possible.

      (But I love SQL logins, too.)

      Reply
    • Yep. The other issue is that these companies need to provide software that “just works” for 80+% of all shops out there. It’s not easy, and I think it’s why we see a lot of companies having success by scaling back on features offered. Fewer features means less support and greater chance it works on as many systems as possible.

      Reply
  7. Great list. Should be read again and again at regular intervals, not only the list but also the links.

    Reply

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.