Blogs

How to Choose Between RCSI and Snapshot Isolation Levels

February 18, 2016

SQL Server offers two flavors of optimistic locking for traditional disk-based tables: Read Committed Snapshot Isolation (RCSI), and Snapshot Isolation. They are each great tools to reduce blocking and make applications faster, particularly in databases that have lots of tiny reads and writes that need to be quick, or mixed-use patterns (lots of little reads and writes + larger reporting queries).

Both isolation levels are available in SQL Server Standard Edition and Web Edition as well as Enterprise. That’s right, they’re cheap and easy. Each of them are controlled by database level settings, and default to disabled for new user databases when you install SQL Server and leave the default settings on the model database.

When should you pick one or the other? And when might you enable both?

Three Inspiring Articles to Read This Week

in sql

February 16, 2016

It’s mid-February. New Year’s is gone, but it’s not springtime yet. Don’t get downtrodden – get inspired. Browse these articles and pick out one way you’re going to finish winter in rockstar style.

The #1 Thing to Never Do to Fix a Performance Problem

in sql

February 11, 2016

Never, ever, disable backups to fix a performance problem.

If you’re not having a performance crisis, you might look at that sentence and say, “Wow, that’s super dumb! Who would ever do that?”

Well, a lot of people, actually. It’s pretty common.

Warning Signs That You Have a SQL Server Backup Problem

in sql

February 9, 2016

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.

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

in sql

February 4, 2016

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.

Sliding Window Table Partitioning: What to Decide Before You Automate

in sql

February 2, 2016

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 necessarily mean that partitioning will make queries faster.

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

in sql

January 28, 2016

It seems like this should be easy. But it’s not.

How to Fix Lopsided Partitioned Tables

in sql

January 26, 2016

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…

Does Query Store Regressed Queries View Catch Nasty Parameter Sniffing?

in sql

January 21, 2016

SQL Server 2016’s new Query Store feature has an option that looks for “regressed” query plans.

But does it catch “bad” parameter sniffing?

How to Find Missing Index Requests in Query Store

in sql

January 19, 2016

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.