Search Results for “sp_blitzindex”

How to Think Like the SQL Server Engine: Adding a Nonclustered Index

Last Updated October 22, 2019

How to Think Like the SQL Server Engine, Indexing

When we left off in the last post, our users kept running this query, and they want it to be really fast: Transact-SQL SELECT Id FROM dbo.Users WHERE LastAccessDate > '2014/07/01' ORDER BY LastAccessDate; 1234 SELECT Id FROM dbo.Users WHERE LastAccessDate > '2014/07/01' ORDER BY LastAccessDate; Let’s pre-bake the data by creating a copy of…

Updated First Responder Kit and Consultant Toolkit for September 2019

Last Updated September 22, 2019

Brent Ozar

First Responder Kit Updates

This month’s release has a lot of new feature goodness: the Consultant Toolkit can automatically upload the results to Amazon S3, plus sp_BlitzCache adds a new sort order to better catch unparameterized queries burning up CPU, a new warning for selects that are doing writes (other than >500MB spills, which we’ve been warning you about…

Mitch Bottell is preparing to take an exclusive lock on some BBQ.

DBA Training Plan 18: Managing Concurrency

Last Updated September 3, 2019

Brent Ozar

DBA Training Plan, Locking, Blocking, and Isolation Levels

Locking isn’t a problem – queries are gonna lock data. As long as nobody else wants the lock, you can take your sweet time locking it, and it won’t show up as a problem. (That’s one of the many reasons queries work so well in development, and then hit performance issues in production.) Blocking, now…

Updated First Responder Kit and Consultant Toolkit for August 2019

Last Updated August 30, 2019

Brent Ozar

First Responder Kit, First Responder Kit Updates

sp_DatabaseRestore can now restore to Azure blobs and change the database owner after a restore, and everything else gets bug fixes. To get the new version: Download the updated FirstResponderKit.zip Azure Data Studio users with the First Responder Kit extension: ctrl/command+shift+p, First Responder Kit: Import. Download the updated Consultant Toolkit in your account EU customers:…

DBA Training Plan 10: Managing Index Fragmentation

Last Updated May 7, 2020

Brent Ozar

DBA Training Plan, Index Maintenance

In the last episode, we looked at your index designs with the output of sp_BlitzIndex. You might have gotten a little overwhelmed what with all the different warnings (and all the learning resources!) You might have thought, “Is there an easier way?” “Is there a way I can just run a job, and have something…

DBA Training Plan 9: My 3 Index Guidelines

Last Updated August 15, 2019

Brent Ozar

DBA Training Plan, Indexing

In our last episode, we used sp_BlitzIndex @Mode = 2 to get to know the contents of our database, sorted our indexes from biggest to smallest, and started asking questions about leftover backup tables that we probably didn’t need to keep around anymore. Now that you’ve taken out the trash, let’s have a closer look…

DBA Training Plan 8: What’s Inside Your Own Databases

Last Updated August 13, 2019

Brent Ozar

DBA Training Plan, Indexing

In the last episode of the DBA Training Plan, we talked about what’s inside the MDF file: a series of pages that store a database’s tables. Now, it’s time to check out the contents of YOUR database to understand what’s using the most space, what’s getting used the most often, and what you might be…

19 – Heaps of Sadness (11m)

Last Updated October 16, 2019

Brent Ozar Unlimited Team

Heaps are what you get when someone doesn’t even care enough about their data to configure a clustered index. Heaps are vulnerable to two big performance issues: deletes don’t, and updates can cause forwarded fetches. Learn the performance impact in this demo: Demo script: Transact-SQL /* Heaps Of Sadness v1.1 – 2019-04-09 Performance Tuning in…

To access this incredible, amazing content, you gotta get Recorded Class Season Pass, or log in if you already shelled out the cash.

Updated First Responder Kit and Consultant Toolkit for July 2019

Last Updated July 3, 2019

Brent Ozar

First Responder Kit Updates

Nothing too dramatic, mostly bug fixes this month. Been pretty quiet on the development front the last couple of months. Some stability is nice around here, eh? To get the new version: Download the updated FirstResponderKit.zip Azure Data Studio users with the First Responder Kit extension: ctrl/command+shift+p, First Responder Kit: Import. Download the updated Consultant…

Yet Another Way Missing Index Requests are Misleading

Last Updated July 2, 2019

Brent Ozar

Indexing

Graduates of my Mastering Index Tuning class will already be familiar with the handful of ways the missing index DMVs and plan suggestions are just utterly insane. Let’s add another oddity to the mix: the usage counts aren’t necessarily correct, either. To prove it, Let’s take MattDM’s Stack Exchange query, “Who brings in the crowds?” He’s…

Things to Think About When Your Databases Grow Quickly

Last Updated June 22, 2019

Brent Ozar

Production Database Administration

In SQL ConstantCare®, we’re adding a notification for when your data size grows >10% in a single week. When this happens, you want to stop for a second to ask yourself a few questions: Did someone just create a backup table? Folks often do a SELECT INTO to back up an important table right before…

Why Order Isn’t Guaranteed Without an ORDER BY

Last Updated May 15, 2019

Brent Ozar

Execution Plans

Start with the Stack Overflow database, with no nonclustered indexes. If you’ve been playing around with demos, run my DropIndexes proc just to clear things out, which leaves the clustered indexes in place. Say you run this query: Transact-SQL SELECT TOP 100 Id, DisplayName FROM dbo.Users; 12 SELECT TOP 100 Id, DisplayName FROM dbo.Users; By default,…

Which Queries Should You Cache in the Application?

Last Updated May 9, 2019

Brent Ozar

Development

I’ve talked about how the fastest query is the one you never make, and why when you’re scaling up a database application, you probably want to start caching query results in the app tier. Caching means: Less licensing costs for SQL Server Lower CPU usage both for query execution and for compilation time Lower locking/blocking…

Updated First Responder Kit and Consultant Toolkit for April 2019

Last Updated April 28, 2019

Brent Ozar

First Responder Kit Updates

This month, lots of improvements and bug fixes, including checking for paused resumable index operations, Evaluation Edition expiration dates, sp_BlitzCache shows the number of duplicated plans for each query, sp_BlitzIndex will skip a list of databases for you, and more. To get the new version: Download the updated FirstResponderKit.zip Azure Data Studio users with the…

Updated First Responder Kit and Consultant Toolkit for March 2019

Last Updated March 21, 2019

Brent Ozar

First Responder Kit Updates

This month, just half a dozen bug fixes, plus a new parameter for sp_BlitzCache to search the plan cache for specific strings. To get the new version: Download the updated FirstResponderKit.zip Azure Data Studio users with the First Responder Kit extension: ctrl/command+shift+p, First Responder Kit: Import. Download the updated Consultant Toolkit in your account EU customers:…

Can deleting rows make a table…bigger?

Last Updated February 15, 2019

Brent Ozar

Indexing

Michael J. Swart posted an interesting question: he had a large table with 7.5 billion rows and 5 indexes. When he deleted 10 million rows, he noticed that the indexes were getting larger, not smaller. Here’s one way that deletes can cause a table to grow: The rows were originally written when the database didn’t…

Updated First Responder Kit and Consultant Toolkit for February 2019

Last Updated February 20, 2019

Brent Ozar

First Responder Kit Updates

I hereby christen this the Rich Benner Memorial Release. He’s still alive, it’s just that we’ll always remember him for the work he put into this month’s version. (I’m kidding, of course. We won’t remember him. (I’m kidding. Rich will appreciate the humor in that.)) You can download the updated FirstResponderKit.zip here, and Consultant Toolkit customers…

What does Azure SQL DB Automatic Index Tuning actually do, and when?

Last Updated February 18, 2019

Brent Ozar

Azure SQL DB, Indexing

Azure SQL DB’s Automatic Tuning will create and drop indexes based on your workloads. It’s easy to enable – just go into your database in the Azure portal, Automatic Tuning, and then turn “on” for create and drop index: ENGAGE Let’s track what it does, and when. I set up Kendra Little‘s DDL trigger to log index changes, which…

What Queries Does Microsoft’s CEIP Service Run On Your SQL Server?

Last Updated August 9, 2020

Brent Ozar

Monitoring

You’ve seen the CEIP Service on your SQL Server, and you’re wondering what queries it runs and how it sends that information back to Microsoft. I was wondering too, because I started seeing queries running that I didn’t expect: sp_WhoIsActivelyRunningTelemetryQueries (click to see full size) Ah-ha, the telemetry service, also known as SQLCEIP! Starting with…

Updated First Responder Kit with Your January Code Resolutions

Last Updated January 28, 2019

Brent Ozar

First Responder Kit Updates

I have a sneaking suspicion that a bunch of you made New Year’s Resolutions to contribute to open source, because y’all got started with a bang this year. We’ve even got improvements from Microsoft folks for Azure Managed Instances! You can download the updated FirstResponderKit.zip here. sp_Blitz Changes Improvements: skipping checks on Azure SQL DB Managed…

Search Results for “sp_blitzindex”

Hi! I’m Brent Ozar.