Availability Groups with infrequent log backups: what could go wrong?

Warning Signs That You Have a SQL Server Backup Problem

Availability Groups with infrequent log backups: what could go wrong? Your backups seem fine. They weren’t failing, the last time you checked. But trouble may be lurking. Here’s the top 5 warning signs I’ve seen that backups haven’t been thought through: 5. Your log backups run every 30 minutes. I have yet to find a company with log backups running every 30 minutes who was actually OK with… (Read Me)

Why ROWLOCK Hints Can Make Queries Slower and Blocking Worse in SQL Server

 Sometimes when SQL Server gets slow, developers and DBAs find that the problem is blocking. After lots of work to identify the query or queries which are the blockers, frequently one idea is to add ROWLOCK hints to the queries to solve the problem or to disable PAGE locks on the table. This often backfires – here’s why. The Theory: ROWLOCK Makes Locks More Granular The idea behind the change is that… (Read Me)

Sliding Window Table Partitioning: What to Decide Before You Automate

Before you do all the work to map out a complex sliding window table partitioning scheme for your SQL Server tables, here’s the top five questions I’d think through carefully: 1) Do You Need Table Partitioning? Some folks think they need partitioning for performance– but it really shines as a data management feature. Just because you’ve got tables with millions of rows in them doesn’t… (Read Me)

How to Check if an Index Exists on a Table in SQL Server

It seems like this should be easy. But it’s not. Code Should be Rerunnable – So You Need to Check if Indexes Exist Whenever you set up a script to create or drop an index, you want a safety check in there. Drop the index if it exists. Create the index if it doesn’t. Or do something else programmatically. Checking if an index exists is a pretty frequent task. But there’s no simple function to test if… (Read Me)

How to Fix Lopsided Partitioned Tables

Over the years I’ve gotten lots of emails and questions from students that start like this: Help! My partitioned table has the wrong data in a partition! It’s lopsided. I started trying to fix it, but… The next sentence is one of a few things. Sometimes it’s all of them.It was taking forever, so I stopped it. It was blocking users, so I stopped it. My transaction log blew up and ran out of space… (Read Me)

Does Query Store’s “Regression” Always Catch Nasty Parameter Sniffing?

SQL Server 2016’s new Query Store feature has an option that looks for “regressed” query plans. Plan choice regression is explained in Books Online this way: During the regular query execution Query Optimizer may decide to take a different plan because important inputs became different: data cardinality has changed, indexes have been created, altered or dropped, statistics have been recompiled,… (Read Me)

How to Find Missing Index Requests in Query Store

SQL Server 2016’s new Query Store feature makes it easier than ever for DBAs and developers to identify the most important queries to tune– and perhaps apply a quick fix by pinning an execution plan. But how does the new Query Store feature work with SQL Server’s existing “missing index” request feature? When the query optimizer generates a plan, it’s frequently able to… (Read Me)
Back in my day, we used a courier for log shipping.

3 Things I Wish I’d Learned Earlier as a SQL Server DBA

Back in my day, we used a courier for log shipping. Hindsight is everything. I was lucky to be trained by a great team of DBAs back when I first started with SQL Server. But it’s hard to know exactly what you really need to know, particularly as new tools are becoming available. Here’s the three things I wish I’d caught on to sooner. 3. How to See What’s Running in SQL Server (and How Long It’s… (Read Me)