SQL Server

Indexing, SQL Server

It Is Known That when you use filtered indexes, they get ignored when your queries are parameterized. This is a Plan Caching Thing©, of course. The simplest example is a bit column with a filtered index. If your index is on WHERE Bit = 1, it doesn’t have data for WHERE Bit = 0. That…

What Questions Would You Ask on a Salary Survey?

Last Updated February 14, 2017

Professional Development, SQL Server

104

Let’s run an anonymous database-industry-wide salary survey that you could actually use when talking to managers. Here’s the questions I’ve thought of so far – not unlimited, but just trying to strike a balance between questions that would actually give you some value, versus not so many questions that people won’t bother filling it out:…

[Video] Office Hours 2016/12/28 (With Transcriptions)

Last Updated January 1, 2017

This week, Brent and Richie discuss Azure VM, min server memory, backup solutions, autogrowth in tempdb data files, parameter sniffing, sp_whoisactive, the worst feature in SQL server, why you should never use SQL server for sending or receiving emails, and how many Cubs shirts Richie owns. Here’s the video on YouTube: You can register to…

Your 10 Favorite Blog Posts of 2016

Last Updated December 31, 2016

SQL Server

Here’s the stuff you clicked on this year: 1. When Shrinking TempDB Just Won’t Shrink – Tara Kizer – If there’s two things you people love, it’s TempDB and shrinking databases. Pair the two of them together, and it’s like peanut butter and jelly. 2. Cheat Sheet: How to Configure TempDB for SQL Server –…

Some Plans Are Wider Than Others

Last Updated February 13, 2017

Execution Plans, SQL Server

Using the publicly available Stack Overflow database (the 100GB 2016/03 version), here’s an execution plan for an update statement: The Narrow Plan (click to see PasteThePlan details) It’s a pretty simple query – it just scans the Posts table looking for any post with PostTypeId = 8, and then updates a single field for the first 250…

[Video] Office Hours 2016/12/21 (With Transcriptions)

Last Updated December 24, 2016

This week, Erik and Richie discuss latency, partition tables, SOS_SCHEDULER_YIELD, alerts, nolock, and what people look for in Senior DBA. Here’s the video on YouTube: You can register to attend next week’s Office Hours, or subscribe to our podcast to listen on the go. Enjoy the Podcast? Don’t miss an episode, subscribe via iTunes, Stitcher or RSS.…

GroupBy Registration is Open for 3 More Days

Last Updated December 22, 2016

GroupBy Conference, SQL Server

GroupBy is a new community-driven conference where your voting picks the lineup. Here’s the sessions you chose: Friday January 13th: 8AM Eastern: SQL Server 2016: It Just Runs Faster with Bob Ward 9:30: SQL Server and PowerShell: Let’s Get Serious with Drew Furgiuele 11:00: Keep It Simple, Make It Fast: The SQL Server Strategy at…

Filtered Statistics Follow-up

Last Updated May 26, 2020

Parameter Sniffing, SQL Server, Statistics

During our pre-con in Seattle A really sharp lady brought up using filtered statistics, and for a good reason. She has some big tables, and with just 200 histogram steps, you can miss out on a lot of information about data distribution when you have millions or billions of rows in a table. There’s simply…

Is NOLOCK Ever The Right Choice?

Last Updated February 13, 2017

Locking, Blocking, and Isolation Levels, SQL Server

I’ve poked a lot of fun at NOLOCK I mean, it sucks. You can get incorrect data, and it can throw errors if your data is particularly volatile. Generally, outside of testing a query or grabbing some sample data, I’m going to avoid it. Crappy in every language But what if… What if it’s about…

Hidden in SQL Server 2017 CTP v1.1: sys.dm_db_stats_histogram

Last Updated May 12, 2017

SQL Server 2017, Statistics

It’s Friday night, so I’m waiting for new CTP Releases As soon as I got the email, I started reading the release notes. Some interesting stuff, of course. Batch mode queries now support “memory grant feedback loops,” which learn from memory used during query execution and adjusts on subsequent query executions; this can allow more…

[Video] Office Hours 2016/12/14 (With Transcriptions)

Last Updated May 20, 2018

This week, Brent, Erik, and Richie discuss their craziest SQL server troubleshooting issues, common causes of SSPI handshake errors, setting deadlock priority “high” on a query, vmotion high availability, best practices on Windows Server 2012 page files, and whether a career as a Database Administrator will be good for the next 40 years. Here’s the…

Indexing Temp Tables

Last Updated February 13, 2017

Execution Plans, Indexing, SQL Server, TempDB

People often don’t give this thought Which is a shame, because I see people sticking fairly large amount of data into temp tables. On the rare occurrence that I do see them indexed, it’s a nonclustered index on a column or two. The optimzer promptly ignores this index while you select 10 columns and join…

Things I Would Love to Put In sp_BlitzCache

Last Updated April 21, 2017

Execution Plans, First Responder Kit, SQL Server

This is my favorite script to work on Getting stuff to work sp_BlitzCache is so rewarding because XML querying is so ridiculous. I wish Mikael Eriksson would write a book already; it’d make my life a whole lot easier. Along the way, I see a lot of things that I would love to warn people…

[Video] Office Hours 2016/12/07 (With Transcriptions)

Last Updated December 10, 2016

This week, Brent, Tara, Erik, and Richie discuss replicating TRUNCATE TABLE command between nodes, how to cope with social isolation that comes with working from home, database shrinking, how to improve log wait times, how to figure out the mac stop and threshold cost setting for servers, and laziest methods to deploy objects and run…

Should I Install Multiple Instances of SQL Server?

Last Updated April 9, 2017

Licensing, SQL Server

Marina Towers in Chicago Instance stacking is the technique of installing multiple instances of SQL Server on the same Windows instance. For example, you might have a VM or server named SQLPROD1, and have: SQLPROD1 – default instance of SQL Server, say SQL 2016 SQLPROD1\SQL2014 – for our older apps SQLPROD1\SharePoint – because it’s supposed…

Availability Groups: More Planned Downtime for Less Unplanned Downtime

Last Updated September 21, 2017

Always On Availability Groups, Breaking News, SQL Server

I often hear companies say, “We can never ever go down, so we’d like to implement Always On Availability Groups.” Let’s say on January 1, 2016, you rolled out a new Availability Group on SQL Server 2014. It’s the most current version available at the time, and you deploy Service Pack 1, Cumulative Update 4 (released…

Selective XML Indexes: Not Bad At All

Last Updated February 14, 2017

Indexing, SQL Server

Promises, Promises I said I’d follow up on this, and here it is. I’m by no means the world’s foremost authority on these, I just played with a simple example until I figured out what worked the best for my query. So you can all play along at home, I’m using XML we all have…

Test: The Top Two SQL Server Problems I Find Everywhere

Last Updated February 13, 2017

CHECKDB and Corruption, SQL Server

Companies call us for performance or high availability issues, but over and over, the very first two things we find are: They’re not taking backups to match the business’s RPO and RTO They’re not doing CHECKDB weekly, or at all, and don’t understand why that’s an issue So let’s walk through a simple scenario and…

The Cost of Adding a Reporting Server

Last Updated February 13, 2017