Product has been added to your cart.

Search Results for “first responder kit”

Building SQL ConstantCare®: Refining Our Recommendations

Last Updated April 29, 2018

SQL ConstantCare

One of the fun things about building SQL ConstantCare® as a cloud-based service is that we can iterate fast, making improvements based on the data we find. We can assess the population as a whole, figure out what people are doing well, and look more closely at the rules they’re struggling with. What checks are…

How Computed Columns Can Cause Blocking

Last Updated May 1, 2021

Locking, Blocking, and Isolation Levels

The short story: when you add a computed column that references another table, like with a scalar user-defined function, you can end up causing concurrency problems even when people didn’t really want to go see that other table, and that table is locked by someone else. Here’s my query: Transact-SQL SELECT Id, u.DisplayName, u.BadgeCount FROM…

Why sp_BlitzLock Can’t Show Complete Columnstore Deadlock Information

Last Updated June 25, 2021

People seem to care about deadlocks That’s why I wrote sp_BlitzLock. Why not sp_BlitzDeadlock? Well, I had this song stuck in my head. But enough about that. While poking around with things recently, I created a pretty typical deadlock on a table, but this one had a clustered column store index on it. Of course,…

SQL Server 2017 CU5: Finding Problems With Parallelism

Last Updated March 22, 2018

Execution Plans, SQL Server 2017

Snow Is Temporary Cumulative Update 5 dropped for SQL Server 2017. Hooray. Poor Taryn. Nooooooooo, I just patched! — Taryn (@onlybluefeet) March 20, 2018 These new servicing models sure do make me glad I don’t have a real job. I’m also happy because I get to write weird queries to test interesting new functionality. This SQL…

Azure SQL DB Managed Instances: We’re All GUIDs

Last Updated March 18, 2018

Managed Instances, SQL Server

Six is having problems adjusting to his clone status Some funny things happen when you create databases up in a Managed Instance. For Instance (HEH!), you may expect this to yield some fruitful results, but it Manages (HAH!) to defy logic. Transact-SQL CREATE DATABASE YourMom; SELECT * FROM sys.master_files WHERE name = 'YourMom'; 12345 CREATE DATABASE YourMom; SELECT…

Two Code Patterns That Don’t Virtualize Well

Last Updated January 19, 2018

Virtualization used to be a really Big Deal™ for database admins: we had to do a lot of careful planning to get a virtualization project done right. These days, virtualization is more and more of a no-brainer: most apps make the transition just fine. Every now and then, though, an exception pops up – usually…

SQL Server 2017 CU3 adds optimizer row goal information in query plans

Last Updated March 22, 2018

Execution Plans, SQL Server 2017

Row Whats? Like it or not, the optimizer has row goals. We’re not going to go terribly in-depth here, because the point is just to make you aware that this is in executions plans, and I’ll be adding it to the FRK in an upcoming release. I am going to give you a round up…

SQL Server 2017 CU3 adds tempdb spill diagnostics in DMVs and Extended Events

Last Updated May 26, 2020

Execution Plans, Memory Grants, Parameter Sniffing, SQL Server 2017

Where have you been all my life? Whenever I’m teaching people about query tuning, two things come up almost without fail: parameter sniffing and tempdb spills. They’re not mutually exclusive — some queries will seemingly always spill to tempdb, whether parameter sniffing is present of not, and some queries experiencing issues with parameter sniffing will never spill…

SQL Server 2017 CU3 adds execution statistics for scalar-valued, user-defined functions

Last Updated December 9, 2018

Execution Plans, SQL Server 2017

Update This seems to finally be fixed as of CU12 for SQL Server 2017. If you’re not already patched up, head over to the link to get there. Estimated Plans Need Not Apply This is, of course, only available in actual plans. All together now: If a query uses one or more user-defined scalar functions…

Automating Complacency

Last Updated January 5, 2018

Processes and Practices, Professional Development

There’s no Latin word for Robot I’m all for automation, especially when it comes to the boring stuff. As awesome as the automation routines we have for that are, I still see people messing it up. Right now, there’s no Clippy/HAL mashup glaring at you, telling you that what you’re doing is a bad idea.…

SQL Server Patches for Meltdown and Spectre Attacks

Last Updated January 5, 2018

SQL Server, SQL Server 2016, SQL Server 2017

The Meltdown and Spectre attacks are newly announced ways of hacking CPUs. They affect both Windows and Linux. If you’re running under virtualization or cloud IaaS, any other running guest can try to leverage the attacks to compromise your unrelated guest. Meltdown (yes, it has a logo) These are a really big deal. Cloud providers…

Introducing sp_BlitzLock: For Troubleshooting SQL Server Deadlocks

Last Updated September 26, 2023

Deadlocks, First Responder Kit, sp_BlitzLock

Deadlocks are hard – especially when there are a lot of them. When there are dozens or more, looking at each graph is a pain. sp_BlitzLock gives you a slice-and-dice analysis of which tables, indexes, and queries are involved in most of your deadlocks so you can quickly get to the root cause. sp_BlitzLock works…

Pocket Square

sp_BlitzQueryStore: A Gentle Introduction

Last Updated May 26, 2020

Execution Plans, First Responder Kit, Parameter Sniffing, Query Store (QDS), sp_BlitzQueryStore, SQL Server 2016, SQL Server 2017

Odds and ends During the precon, we asked how many people were on 2016, how many people were aware of query store, how many people were using it, and how many people were using sp_BlitzQueryStore. About 1/3 of the hands went up at first, then for each successive question, fewer and fewer hands stayed up.…

SQL Server 2017 Showplan Schema Is Available

Last Updated September 28, 2017

Execution Plans, SQL Server, SQL Server 2017

File this under… Things probably only I care about. But the Showplan Schema for SQL Server 2017 popped up… well, I caught it last night. I probably could have written this sooner, huh? So what’s new? Well, if I compare the 2016 SP1 schema using KDiff, a few things stand out. Some I’ve blogged about,…

Live Query Plans and Blocking

Last Updated September 21, 2017

Execution Plans, Indexing, Locking, Blocking, and Isolation Levels, SQL Server

You’d think this would be cool After all, you’ve got a query plan being executed LIVE ON NATIONAL TELEVISION! But it’s not. It’s actually one of those weird reasons why you still need other ways of looking at DMVs for issues. Demo Library Let’s take a closer look! First, we need an index. Indexes are…

Why Columnstore Indexes May Still Do Key Lookups

Last Updated June 25, 2021

Columnstore Indexes, Execution Plans, Indexing, SQL Server

I was a bit surprised that key lookups were a possibility with ColumnStore indexes, since “keys” aren’t really their strong point, but since we’re now able to have both clustered ColumnStore indexes alongside row store nonclustered indexes AND nonclustered ColumnStore indexes on tables with row store clustered indexes, this kind of stuff should get a…

The Ghosts of Temp Tables Past

Last Updated September 6, 2017

Humor, SQL Server

True story You may find it hard to believe, but I recently had to fix a small bug in sp_BlitzCache and sp_BlitzQueryStore. Since both stored procedures have similar functions, they also share some temp table names (mainly the ones for parsing down XML nodes to more manageable chunks). In the window where I was making…

SQL Server 2017: Potentially Interesting New Extended Events

Last Updated August 21, 2017

SQL Server 2017

You still won’t use them And I predict that Extended Events will remain as niche-only use for specific troubleshooting scenarios. Very few monitoring tools use them, and the ones that do still rely on traces and DMV queries for the bulk of their collection. That’s not a good sign, in 2017, considering how long Extended…

sp_AllNightLog: Creating Jobs Like I’m The President

Last Updated August 11, 2017

Backup and Recovery, Clients and Case Studies, ISV and SaaS Database Design, kCura Relativity, Log Shipping, SQL Server

Look, we need these things The setup for sp_AllNightLog creates jobs for four separate activities 1 job to poll for new databases to back up (primary) 1 job to poll for new databases to restore (secondary) 10 jobs to poll for backups to take (primary) 10 jobs to poll for restores to… whatever (secondary) And,…

Locking When There’s Nothing To Lock

Last Updated July 27, 2017

Development, Execution Plans, Indexing, Locking, Blocking, and Isolation Levels

Demo Day We use StackOverflow for demos a lot. For all the reasons Brent mentions in his Great Post, Brent©, it’s pretty awesome. Where things get tricky is with locking demos. Sometimes the modifications can take a long time. This may be by design if you need to show long-held locks by sessions that aren’t…