SQL Server

Recently, I wanted to play around with the auto_stats event against a test system running SQL Server 2014. I ran through the session setup GUI and added the auto_stats event. I configured it with a filter (predicate) to only show me auto_stats event in a database named AutoStatsTest. There’s a cost to events that may fire frequently and a…

Using “OR” and “IN” with SQL Server’s Filtered Indexes

Last Updated August 22, 2018

Indexing, SQL Server

You can’t do everything with filtered indexes in SQL Server. For instance, you can’t create the following index: Transact-SQL CREATE INDEX IX_Votes_filter ON dbo.Votes (PostId) WHERE (VoteTypeId = 1 OR VoteTypeId = 2); GO 123 CREATE INDEX IX_Votes_filter ON dbo.Votes (PostId) WHERE (VoteTypeId = 1 OR VoteTypeId = 2);GO If you try, you’ll get the error…

Measuring Performance Can Slow SQL Server Down

Last Updated January 22, 2015

I’ve written a terrible query. It might be the worst query in the world. Sure, there are other queries that may be slower, but this query is terrible and elegantly short. The query is gloriously bad for a two reasons: It’s incredibly inefficient in the way it uses a scalar function Observing the query’s performance can make…

Dropping an Offline Database in SQL Server

Last Updated January 22, 2015

When you drop a database in SQL Server, the files are normally removed from the file system right away. POOF! Hope you had a backup if you ran DROP DATABASE against the wrong instance. However, things are a little different if you take the database offline before you drop it. Consider the following code: Transact-SQL…

How Many Databases Can I Put on One SQL Server?

Last Updated February 9, 2017

Architecture, Backup and Recovery, ISV and SaaS Database Design, SQL Server

I’ve seen servers with thousands of databases on a single SQL Server, and it works. Sure, opening the databases list in SQL Server Management Studio is painful, and a lot of third party monitoring tools fall over, but it’s not so bad once you know how to work around these issues. But there’s two issues…

Announcing Row-Level Security in Azure SQL Database

Last Updated February 13, 2017

Microsoft Azure, SQL Server

Your users probably shouldn’t be able to view all of the data. You might have regional sales managers who should only see sales for their region, or human resource staff who should be able to see employee details but not salaries. This is usually challenging with databases. How We’ve Historically Built Row-Level Security in SQL Server…

Three reasons to use Extended Events

Last Updated January 22, 2015

Jes Schultz

I have a new mission: to convince you, the long-time Profiler user, to switch to Extended Events. I realize I have a long, difficult task ahead of me. Profiler has been around for a long time. You’re comfortable with it. You know it’s not perfect, but you have learned to live with its imperfections. Now I want…

Reporting From a Log Shipping Secondary in STANDBY mode

Last Updated February 13, 2017

Log Shipping, SQL Server

You can build a lot with simple tools. Carefully. I’m a fan of SQL Server’s transaction log shipping. It works in Standard Edition, it’s relatively simple to set up, and you can even make your log shipping secondary readable using STANDBY mode. I’ve worked with some pretty cool, complex log shipping environments over the years.…

Are You Getting the Benefits of Virtualization?

Last Updated February 9, 2017

Licensing, SQL Server, Virtualization

Here’s some of the reasons companies usually virtualize their SQL Servers: Cost savings on hardware Cost savings on Windows OS licensing Cost savings on SQL Server licensing Protect against the failure of a single hardware element Leverage extended features for Disaster Recovery Automatic load balancing across multiple hosts Easier hardware replacement/migration When we perform a SQL Critical…

Urgent AlwaysOn Availability Groups Bug

Last Updated September 21, 2017

Always On Availability Groups, Breaking News, SQL Server

If you’re using AGs, don’t apply these patches: SQL 2012 SP2 CU3 SQL 2012 SP2 CU4 SQL 2014 CU5 until you read this Microsoft post about a breaking bug. Your AG may stop synchronizing due to blocking between user queries and a system session. The fix is to disable automatic failover, restart the primary, and…

How to Query Extended Events Target XML

Last Updated January 15, 2015

Jes Schultz

When Extended Events (XE) were released with SQL Server 2008, I was excited – something new to learn! I read Microsoft articles about them, read blog posts about them, and tried to use them. At that time, there was no GUI for XE. I had to create the sessions with T-SQL. That wasn’t awful – I…

Woohoo, We Won Two 2014 Tribal Awards!

Last Updated January 20, 2015

This year’s Simple Talk Tribal Awards are out, and the video announcements are pretty funny: We won in two categories: BrentOzar.com won the Best Blog, and the Twitter Account of the Year goes to @BrentO. I’ll let Sally Fields take over from here:

How to Use SQL Server’s Dedicated Admin Connection (DAC) – Video

Last Updated January 14, 2015

You may have heard that there’s a secret back door into your SQL Server– but have you enabled access to it properly? Do you know when you might need to use it, and how you can connect? Kendra Little explains why the Dedicated Admin Connection (or “DAC”) is so important and how to configure remote…

Testing ALTER INDEX REBUILD with WAIT_AT_LOW_PRIORITY in SQL Server 2014

Last Updated August 15, 2024

Index Maintenance, Locking, Blocking, and Isolation Levels, SQL Server

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…

We’re Hiring a Salesperson

Last Updated February 12, 2017

Company News, SQL Server

When we first started our company, we laid out our goals in a shared Google Doc. We wrote about what we wanted to do, how we wanted to treat our employees and customers, and the growth numbers we wanted to achieve. One of our original goals was to hire one consultant per year. In 2012, it…

Eight Things A DBA Should Always Do with SQL Server

Last Updated January 4, 2015

I started tweeting various single-words you could start an argument with the other day. I really liked this suggestion from @SQL_Kiwi: @Kendra_Little One word ways to start an argument: always (or never) — Paul White (@SQL_Kiwi) December 31, 2014 It got me thinking. What are the things that I would say you a DBA should…

How to Change SQL Server Settings with the Scientific Method

Last Updated January 4, 2015

Ask your scientist if Service Broker is right for you. When you’re trying to fix a problem (or even just researching something you’re curious about), do what scientists do. 1. Ask a question. “Why is this database so slow?” 2. Do background research. Hit Google, check Stack Exchange, maybe even read Books Online. 3. Construct a hypothesis.…

Does Updating Statistics Cause a Recompile if No Data Has Changed?

Last Updated August 7, 2018