Designing, evaluating, maintaining, and troubleshooting indexes and statistics.
568 associated posts270 primary posts
In the classic spirit of my How to Think Like the Engine class, let's go take a look at the StackOverflow.Users table and find all the users named Brent. There's a DisplayName field, and I'm going to be querying that a lot in this blog post, so I'll go ahead and create an index on it, then look for the Brents:
Forgotten, But Not Gone
Two of the best built-in tools to figure out if your query tuning efforts are headed in the right direction are SET STATISTICS TIME ON and SET STATISTICS IO ON. If you're really fancy, you can use SET STATISTICS TIME, IO ON. By fancy I mean lazy. By lazy I mean efficient. You get the idea. Magic.
This week, Brent, Richie, Tara, and Erik, discuss non-clustered indexes, dynamic SQL, replication, setups for servers dedicated to reporting services and warehouse databases, cross-cluster migrations, employment opportunities in the current market, and much, much more!
Here’s the video on YouTube:
This is not about table partitioning If you want to learn about that, there's a whole great list of links here, and the Best Blogger Alive has a tremendous post on why table partitioning won't make your queries any faster over here. With that out of the way, let's talk about partitioned views, and then…
This week, Brent, Richie, and Erik discuss execution plans for table value functions, how to prepare for the Senior DBA course, source control software, generating fragmentation, backups, and the new TV shows the team is looking forward to this season.
Here’s the video on YouTube:
There have been so many improvements lately to our First Responder Kit scripts that it was time to update our tutorial videos. Each of these is about ten minutes long and shows how to use 'em:
How to Use sp_Blitz for a Health Check
https://youtu.be/5QAL9itupMA
How to Use sp_BlitzCache to Find Slow Queries
https://youtu.be/EkLuXURMwso
How to Use sp_BlitzIndex to Check Your Indexes
https://youtu.be/8Wo5M7kYO20
This week, Brent, Richie, Erik, and Tara discuss parameter sniffing, database backups, referential integrity in databases, clustering, creating and using indexes, in-place upgrades, bench marking tools, and more.
Here’s the video on YouTube:
This week, Brent, Richie, and Tara discuss corruption, resetting query stats, fixing parameter sniffing issues, how to suggest features for our scripts, and why Brent is sporting an Oracle sweatshirt.
Here’s the video on YouTube:
Let's say, just theoretically, you wanted to demo a query that takes SQL Server a long time to compile.
And in order to make SQL Server's job tougher, you wanted to create the maximum number of indexes possible on a table.
This week, Brent, Richie, Erik, and Tara discuss speeding up performance, how to make SSRS highly available, what to do with SSIS packages when making upgrades, Windows Core, and how to hide data from DBAs when they have SA access.
Here’s the video on YouTube:
Some of our clients have very high forwarded record counts and aren't aware of it until they run sp_BlitzFirst and get an alert about high Forwarded Records per Second. Some of these clients are using Ola Hallengren's IndexOptimize stored procedure to maintain their indexes. This brought up a question of whether or not rebuilding a…
Be honest, here You don't care about most indexing problems. Duplicates, Borderline Duplicates, Unused, Wide, Non-aligned Partitions... All you're here for are the Missing Indexes. Wham, bam, your query finishes in less than a second, ma'am. Take this quarter, go downtown and have a rat gnaw that thing off your face. And I get it.…
This week, Erik, Tara, Jessica, Doug, and Angie discuss queries, installing multiple instances of SQL server on a Windows VM, using DENY Database rules, migrating databases to Amazon RDS, availability groups, using filtered indexes, and more!
Here’s the video on YouTube:
Here at Brent Ozar Unlimited We have a proud tradition of not blaming index fragmentation for everything. There are points you should deal with it, but they're probably not 5% and 30% and 1000 pages. But that's not what this blog post is about. I'm hoping to clarify why we're more interested in up to…
Using the StackOverflow database, let's check out Krock's query. He's a competitive fella, and he's looking to find users who signed up for StackOverflow after he did, but who have a higher reputation than he does. I'm going to simplify the query a little here: [crayon-6a723ee65a52c262958721/] The Users table has a clustered index on the Id…
Getting developers production data stinks DBAs are stuck in this annoying cycle where they need to give developers production, or production-like data, but... Production data can be huge. Hundreds of gigabytes if you're lucky, several terabytes if you're not. Then once it gets there, you have other considerations. Either you lock down Dev, or you…
Most of the time, when you run a query, SQL Server prefers to use just one index on a table to do its dirty work.
Let's query the Users table in the StackOverflow database (I'm using the March 2016 version today), looking for people with a certain reputation score OR a certain number of upvotes:
This week, Angie, Erik, Doug, Jessica, and Richie discuss DB migration, rebuilding large indexes, recommendation for SQL dev ops tools, best practices for disabling SA accounts, compression, and more!
Here’s the video on YouTube:
Even with lots of monitoring in place, we should perform periodic checks of our SQL Servers.
Think of this like "Spring Cleaning", except I would recommend that it be more frequently than just once a year. Doing it monthly might be a bit ambitious due to our busy schedules, but quarterly could be achievable.
This post is mostly a thought experiment The thought was something along the lines of: I have a table I want to keep temporal history of. I don't plan on keeping a lot of data in that table, but the history table can accumulate quite a bit of data. I want to partition the history…
