Launch week: the Season Pass & Fundamentals Week are 50% off — ends in 20d 16h 18m — See the sale
BRENT OZARUnlimited

Category: SQL Server

Woohoo! We Won a Red Gate Tribal Award: Best Blog of 2014 (Again)

February 20, 2015

For the past two years, Red Gate's Tribal Awards have let you, the SQL Server community, nominate and vote on their favorite community voices. Here's the 2014 winners: Best New Community Voice: Cathrine Wilhelmsen (@CathrineW) Best Presentation at a Technical Conference: Dr. Rimma Nehme (@RimmaNehme) Best New SQL Book: SQL Server Query Performance Tuning by Grant Fritchey…

Read more about Woohoo! We Won a Red Gate Tribal Award: Best Blog of 2014 (Again) 3 comments — Join the discussion

Extended Events Sessions: Messing Up Filters

February 17, 2015

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 cost to filters, but this is my test box and I was just using this to learn-- so no biggie, right?

Read more about Extended Events Sessions: Messing Up Filters 8 comments — Join the discussion
Performance Tuning

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

February 12, 2015

You can't do everything with filtered indexes in SQL Server. For instance, you can't create the following index:
[crayon-6a3643e66370e500776434/]
If you try, you'll get the error message:
[crayon-6a3643e66371b977288351/]
Instead, you can use 'IN' and create the index this way:
[crayon-6a3643e66371f332182134/]
That works-- and good news, even queries written with 'OR' can use that filtered index, because SQL Server is clever like that. Here's an execution plan that shows it in action.

Read more about Using “OR” and “IN” with SQL Server’s Filtered Indexes 4 comments — Join the discussion

Dropping an Offline Database in SQL Server

February 5, 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:
[crayon-6a3643e665117564273013/]
Here's what S:\MSSQL\Data looks like after I run the whole script, including DROP DATABASE...

Read more about Dropping an Offline Database in SQL Server 23 comments — Join the discussion
Production DBA

How Many Databases Can I Put on One SQL Server?

February 4, 2015

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 you can't work around: our old archenemies, RPO and RTO. A quick reminder:

Read more about How Many Databases Can I Put on One SQL Server? 22 comments — Join the discussion

Three reasons to use Extended Events

January 29, 2015

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 you to step outside your comfort zone and learn a new tool, with new terminology, a new interface and new capabilities.

Read more about Three reasons to use Extended Events 19 comments — Join the discussion

Reporting From a Log Shipping Secondary in STANDBY mode

January 28, 2015

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. In one case, we had multiple log shipping secondaries and a load balancer involved to support a full fledged reporting application. It worked pretty well-- with a lot of careful scripting and support.

Read more about Reporting From a Log Shipping Secondary in STANDBY mode 65 comments — Join the discussion

Are You Getting the Benefits of Virtualization?

January 27, 2015

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

Read more about Are You Getting the Benefits of Virtualization? 11 comments — Join the discussion

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

January 19, 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 access.

Read more about How to Use SQL Server’s Dedicated Admin Connection (DAC) – Video 11 comments — Join the discussion
Performance Tuning

Testing ALTER INDEX REBUILD with WAIT_AT_LOW_PRIORITY in SQL Server 2014

January 14, 2015

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:

Read more about Testing ALTER INDEX REBUILD with WAIT_AT_LOW_PRIORITY in SQL Server 2014 14 comments — Join the discussion