SQL Server

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

Architecture, Licensing, Log Shipping, SQL Server

“We’d like to offload our reporting queries to a separate SQL Server.” The first costs are fairly obvious. Hardware and storage – even if you’re running it in a virtual machine, you need to account for the costs of say, 4 cores and 32GB RAM. Not only will you need storage for the databases, but…

Date Math In The WHERE Clause

Last Updated February 13, 2017

Oh, THAT SARGability I realize that not everyone has a Full Metal Pocket Protector, and that we can’t all spend our days plunging the depths of query and index tuning to eek out every CPU cycle and I/O operation from our servers. I mean, I don’t even do that. Most of the time I’m just…

Spills SQL Server Doesn’t Warn You About

Last Updated February 13, 2017

Don’t make me spill Table variables get a lot of bad press, and they deserve it. They are to query performance what the TSA is to air travel. No one’s sure what they’re doing, but they’ve been taking forever to do it. One particular thing that irks me about them (table variables, now) is that…

[Video] Office Hours 2016/11/23 (With Transcriptions)

Last Updated November 27, 2016

This week, Brent and Richie discuss their hardware setups, lazy spools, SQL 2016 Service Pack 1, Always On Availability Groups, Database IDs, GroupBy.org, SQL on Linux, and new features they would like to see in the new version of SQL. Here’s the video on YouTube: You can register to attend next week’s Office Hours, or subscribe to…

SQL Server 2016 SP1 Bug with Graphical Execution Plans

Last Updated February 13, 2017

Early adopters: if you’re rolling on SQL Server 2016 SP1, you may have noticed that when you click on query plans in sp_BlitzCache or sp_WhoIsActive, they sometimes render as ugly XML rather than the beautiful graphical showplan. Here’s a 3-minute video showing the bug: To work around it, save the XML plan as a file…

Index Scans: Not Nearly As Bad As You Might Think

Last Updated February 13, 2017