1. Home
  2. Development
  3. (Page 20)

Enabling Query Store in Azure SQL Database

Enter Query Store Query Store, in short, is a way to track query performance over time. In Microsoft’s words, “The feature automatically captures a history of queries, plans, and runtime statistics, and retains these for your review”. It’s like sys.dm_exec_query_stats but it persists across reboots! And it has execution plans! The Query Store is a…
Read More

Logical Query Processing

You can’t do that on management studio Recently, while working with a client, I did something in a query that they were mystified by. I didn’t think much of it, but I thought it might be useful to you, dear readers, as well. Along with an explanation. Here’s a sample query that takes advantage of…
Read More

Replication Won’t Refresh Your Dev and Pre-Production Environments

Development, Replication
It’s almost like the real thing, except it’s totally different. At first glance, SQL Server’s transactional replication seems like it’s useful for moving data around in all sorts of situations: it works in Standard Edition, it’s not synchronous, and you can have multiple subscribers. Why People Want Replication to Test and Pre-Production Environments Setting up…
Read More

Indexing for Windowing Functions

Indexing, SQL Server, T-SQL
Hooray Windowing Functions They do stuff that used to be hard to do, or took weird self-joins or correlated sub-queries with triangular joins to accomplish. That’s when there’s a standalone inequality predicate, usually for getting a running total. With Windowing Functions, a lot of the code complexity and inefficiency is taken out of the picture,…
Read More


One of the blocking scenarios I find most interesting is related to online index rebuilds. Index rebuilds are only mostly online. In order to complete they need a very high level of lock: a schema modification lock (SCH-M). Here’s one way this can become a big problem: An online index rebuild starts against a large index A…
Read More

Read Committed Snapshot Isolation: Writers Block Writers (RCSI)

When learning how Read Committed Snapshot Isolation works in SQL Server, it can be a little tricky to understand how writes behave. The basic way I remember this is “Readers don’t block writers, writers don’t block readers, but writers still block writers.” But that’s not so easy to understand. Let’s take a look at a simple test…
Read More

Announcing sp_Blitz v36: New Database Checks, Hekaton, Azure Files, More

The latest version of our free SQL Server health check adds some nifty new stuff: Checks for non-default database configurations like enabling forced parameterization or delayed durability Looks in the default trace for long file growths or serious errors like memory dumps Checks Hekaton memory use and transaction errors Warns about database files on network…
Read More