SQL Server

I recently came across a curious case where a SQL Server was suffering a number of long-running queries coming from an application written in Entity Framework. When I measured the average query execution times, I got some unexpected results: CPU time: 12 milliseconds Reads: 273 Elapsed time: 6800 milliseconds Wait, what? Looking at the wait stats…

Logical Query Processing

Last Updated March 12, 2018

Execution Plans, SQL Server

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…

13 Questions to Ask Before You Touch a Database Server

Last Updated July 2, 2015

I’ve touched a lot of SQL Servers over the years. After my share of trips to HR for inappropriate touching, here’s the questions I ask first these days: Is this in production now? If this goes down, what apps go down with it? When those apps go down, is there potential for loss of life…

Creating a branch in the GitHub client for Windows

Pull Request 101 for DBAs Using GitHub

Last Updated July 10, 2015

Kendra Little

I’ve worked with source control quite a bit over the years — everything from ye olde Visual Source Safe to Subversion to TFS. I even supported the Source Depot system at Microsoft as an engineer for a year back in the day! These days I don’t use source control a ton. We keep repositories of…

The sp_rename follies

Last Updated June 29, 2015

Before we get started… I know. I know. BOL. It’s documented. They even show you how to rename a table. Thanks, sp_rename! But sometimes… You just forget. And as with most simple mistakes, fixing them is… Weird. Here’s what happened to me recently, when I was working on a table swapping demo. Transact-SQL CREATE TABLE…

Three Easy Tweaks to Tune Up Your SQL Server

Last Updated July 2, 2015

Doug Lane

I’ve been doing SQL Critical Care® work with clients for over a year now. It’s absolutely true that every client’s situation is different and needs special attention. However, I’ve found that there’s a short list of issues that nearly all clients have in common. Drawing from that list, here are the top three high-impact, low-effort areas you can work…

Want us to run training classes at your office?

Last Updated July 6, 2015

You’re working with SQL Server, and you really want to learn how to make it faster and more reliable, but your boss just won’t let you go off to our upcoming training classes. Good news – we’ll bring the training to you. Just download our training catalog PDF and choose from modules on how to diagnose…

An Update to SQL Server Management Studio is Available

Last Updated July 3, 2015

Cloud Computing, SQL Server

That’s the message you get in your tray if you open SSMS 2016 today: SSMS Update Available Then click Tools, Check for Updates, and you get: Check for Updates What’s awesome is that I didn’t even install SQL Server Management Studio from the new standalone installer – this is my full CTP2.1 installation of SQL Server,…

The Fastest Way to Reconfigure a Bunch of Servers

Last Updated June 20, 2015

Jeremiah Peschka

… is to use a SQL Server where a robot does it for you! I stole that from Kendra – someone else already wrote the sentence for me and why not keep re-using things, right? Configuring SQL Servers the Usual Way What’s the usual way to do this? There are a few commercial tools on…

Indexing for GROUP BY

Last Updated March 12, 2018

Indexing, SQL Server

It’s not glamorous And on your list of things that aren’t going fast enough, it’s probably pretty low. But you can get some pretty dramatic gains from indexes that cover columns you’re performing aggregations on. We’ll take a quick walk down demo lane in a moment, using the Stack Overflow database. Query outta nowhere! Transact-SQL…

Should You Put Things in the master Database?

Last Updated May 16, 2019

Always On Availability Groups, Backup and Recovery, SQL Server

Growing up as a young boy, my elementary teachers always warned me about the perils of putting stuff in master. But what’s the real deal? Why is this such a bad idea? System databases are often located on space-limited drives. The default installation parameters for SQL Server throw master, model, and msdb all on the…

New SQL Server Management Studio 2015/June – with Check for Updates!

Last Updated June 24, 2015

This morning, Microsoft announced a new standalone SQL Server Management Studio download: This is our first effort to release SQL Server Management Studio (SSMS) in a mechanism outside of the SQL Engine releases. Our goal is to update this frequently with new features, fixes and support for the newest SQL Server features in SQL Server…

New Updates for sp_Blitz®, sp_BlitzCache™

Last Updated June 19, 2015

We’ve just updated our First Responder Kit to include new versions of these tools: sp_Blitz® v41 – June 18, 2015: Added check 162 for CMEMTHREAD waits on servers with >= 8 logical processors per NUMA node. Added check 159 for NUMA nodes reporting dangerously low memory in sys.dm_os_nodes. Added check 161 for a high number of cached…

Indexing for Windowing Functions

Last Updated March 12, 2018

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,…

Announcing the Dell DBA Days: August 25-28

Last Updated August 18, 2015

If you want to work on the cutting edge, you can’t just read best practices – you have to go create them. Somebody’s gotta teach you how to use SQL Server 2016, right? We asked Dell if we could fly out to their headquarters in Round Rock, Texas and borrow a bunch of hardware and storage…

PASS Summit #FreeCon2015: What We’re Talking About

Last Updated February 13, 2017

#SQLPass, Blogging, SQL Server

FreeCon SQLSaturday Chicago 2012 On the Monday before the PASS Summit in Seattle this year, we’re putting on another one of our FreeCons. The FreeCon is different from a typical conference pre-con – it’s free, and it’s about the SQL Server community, presenting, marketing, branding, networking, and yes, some technical stuff too. In the past,…

New sp_BlitzFirst® Reports on Waits, Files, Perfmon Since Startup

Last Updated June 11, 2015

When your SQL Server is slow right now, you can run sp_BlitzFirst® to find out what’s going on. It checks for things like long-running queries, backups, blocking, files growing, and will tell you the top wait stats right now. You can use the @Seconds parameter to control the sample length – by default, it samples…

Faster queries using narrow indexes and CROSS APPLY

Last Updated February 13, 2017

Jeremiah Peschka

Indexing, SQL Server

It’s common to try to build the perfect index for a query. This index only uses the exact right columns for the key columns and other, presentational, data is pushed to the INCLUDE section of the index. While you can create a narrow key this way, the entire index row can become wide. A Sample…

Careful Testing the 2014 Cardinality Estimator with Trace Flags

Last Updated May 28, 2015