101 Things I Wish You Knew About SQL Server

Last 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.

SQL Server will do what you tell it to do. I find that PEBKAC is often the root cause for many issues.
DBAs get paid for performance, but we keep our jobs with recovery.
HA <> DR. If you can't recover, you can't keep your job. See previous item.
Memory, CPU, disk, and network are all finite resources. Leave room for growth.
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.
Backups (AKA database dumps). You need them. Store them someplace safe, and on a different server. See number 2.
Maintenance is mandatory. Find, or make, a window for rebuilding indexes, updating stats, running DBCC CHECKDB, and taking backups. See number 2.
Don't forget to backup security certificates, too. See number 2.
Monitor for unused, misused, and duplicate indexes. These are just adding overhead you don't need.
Identity values can, and do, run out. Be prepared.
Set the min and max memory configuration for your instance.
Data and logs go on different disks. This isn't necessarily about performance, it's also about recovery. See number 2.
One lane of traffic is not enough. Dual NICs and a big, fat pipe are what you want.
But you can always blame the network anyway.
Know the RTO and RPO for your applications. See number 2.
When troubleshooting, sometimes the simplest answer is the right one.
Focus on wait events and logical I/O when performance tuning. They help you find the root cause the fastest.
The best clustered index keys are unique, narrow, static, and ever increasing.
Just because you can create 999 indexes on a table doesn't mean you should.
Put system objects on a different filegroup than user objects. Oh, and you should be using filegroups. You are, right?
Learn how to restore filegroups.
Use SET NOCOUNT ON to reduce network traffic.
Avoid SELECT * in production code. Look to return only the data that is necessary.
Use schema and object owner when qualifying objects, it reduces lookup costs and makes you look like a smart developer.
Enable the "optimize for ad-hoc workloads" option, unless you know you are the edge case such that this setting won't help you.
Adjust your cost threshold for parallelism BEFORE you consider adjusting your MAXDOP setting.
Know your NUMA. When configuring memory and MAXDOP, keeping everything inside one NUMA node is a nice to have.
Query governor is an easy way to stop bad queries before they happen.
Optimize your tempdb for performance.
The only way to know your backup succeeded is to test by doing a restore. See number 2.
Don't nest views. Just don't.
Enable backup compression for your server, it's often worth the extra CPU.
Row and Page compression are useful options as well, and often overlooked.
Build a recovery strategy BEFORE you build a backup strategy. See number 2.
Auto-shrink is the Peyton Manning of SQL Server. It looks like a great idea but you are often disappointed in the end result.
Baseline for performance. Without baselines and metrics you have no idea if something is truly a problem or not.
Don't RDP to a server and launch SSMS, or Profiler, in an attempt to fix a production issue. Learn to work remotely.
Learn how to use scripted installs.
Server core is a great way to keep people away from your database servers that shouldn't be touching them anyway.
Use autogrowth but not the default growth values. Monitor for growth events and minimize their occurrence.
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!
Keep your transactions short.
Triggers are awful, awful little creatures.
But NULLs are far worse.
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.
Instant file initialization is a good thing. You should be using this.
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.
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.
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.
Object statistics are the most important piece of metadata in your database. Bad, or missing, stats will lead to bad query plans.
Bad data leads to bad decisions.
Great database performance starts with great database design.
Enforce password policies for your SQL logins.
Recycle your SQL Server error logs.
Script SQL login and database user permissions nightly. You never know when you'll need them during a DR event. See number 2.
SQL Server Agent alerts are useful, and hardly used.
Deadlocks are often the result of application logic and data access patterns. The engine doesn't just get "tired" and start deadlocking.
Testing against 10, 100, and 1000 rows is not an accurate test against a production workload.
Table variables are not "in-memory" only, and are often not as good a choice as a temp table.
For a performance boost learn how to stripe your backups. Then, learn how to restore a striped backup. See number 2.
Resource governor is a great way to throttle workloads as needed, especially workloads that bloat your plan cache.
Sometimes a scan is better than a seek. Learn how to read a query plan.
AlwaysOn is a marketing term, not a feature. Availability Groups is what you meant to say.
Learn how to build a cluster.
Learn how to break a cluster.
Learn how to repair a cluster.
Application code is responsible for 100% of all performance issues. #hardtruth
Know what trace flags are running on your system.
Keep as many of your servers configured in the exact same way. This saves time troubleshooting.
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.
Data lasts longer than code. Treat it right.
However, data will confess to anything if you torture it long enough.
Know what the installer just did to your database server.
Use Windows AD groups, not Windows Logins. There should be a separation of duties with regards to allowing data access.
Custom database roles and server roles are a great way to provide custom permissions.
Ordering and sorting of data consumes resources. Do it as few times as possible. Sometimes, it's best done in the application layer.
Task manager is a dirty, filthy liar.
Understand how to work with large data sets without filling up the transaction log.
Every now and then, go look at the system views, functions, and stored procedures. You will learn something new and useful.
Multiple instances of SQL Server on a server know nothing of each other. It's up to you to make sure they play nice.
Don't install services (SSRS, SSIS, SSAS) onto a server "just in case". Only install the services that are needed.
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.
If your vendor requires the use of the 'sa' account, go find another vendor.
If the vendor code creates a loopback linked server, go find another vendor.
You can't mirror a database using an actual mirror, no matter how hard you try.
Implicit conversions can be avoided providing someone is willing to do the extra work.
Over allocation of host resources leads to over commit of host resources, and that's bad for everyone. Leave room for growth.
Comments in code are notes to "future you". Be nice to your future self and remind them what in the hell you were thinking.
Learn how to make use of DNS aliases, it makes swapping servers around much easier when you don't need to update connection strings.
Practice recovering the master database. See number 2.
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.
Junior DBAs know how to react. Senior DBAs know how to be proactive.
Make sure you can rollback, when necessary.
Practice upgrading SQL Server in a variety of ways, especially rolling upgrades.
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.
You'll still get blamed for there not being enough disk space.
You can't fix stupid.
Bad code and bad database design will bring good hardware to its knees, always.
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.
Everyone starts out with zero knowledge of SQL Server.
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!