SQL Server

SQL Server learning materials seem to live at two extremes. Blog posts are short and to the point, but they don’t cover material in depth. Books are long and detailed, but to quote a famous philosopher, ain’t nobody got time for that. Enter two resources that have been out for quite a while. They aim…

Filtered Indexes vs. Table Partitioning

Last Updated February 13, 2017

Indexing, Partitioning, SQL Server

It was a dark and stormy… Oh, wrong story. It was actually a warm, sunny afternoon in Charlotte, NC. I was presenting “Index Methods You’re Not Using” at PASS Summit. In this talk, I discussed how indexed views, filtered indexes, and compressed indexes can improve your query performance by reducing I/O. From stage right, an…

Getting Started with SQL Server Sample Databases

Last Updated November 14, 2013

Jeremiah Peschka

The best way to improve your skills as a database professional is to practice. If you’re like me, you might even take that practice outside of the workplace and do a bit of homework on the side. You can’t take a copy of your production database home with you, so what can you do? Thankfully,…

Free Ebook: SQL Server DBA Training Plan

Last Updated June 24, 2017

Our Hierarchy of Database Needs training email plan has been a lot of fun. Thousands of SQL Server professionals have signed up to get an email in their in-box every Wednesday for 6 months. It’s like a part-time college course – the one you should have been given when you first got shuffled into this…

What You Can (and Can’t) Do With Indexed Views

Last Updated May 9, 2019

Views are logical objects in SQL Server databases that present you with a “virtual table”. Views are typically created for one of three reasons: security, simplification, or aggregation. Security: we create views so that a user can read specific columns out of certain tables, but not all the data. Simplification: sometimes, it’s easier to write…

How to Winterize Your Database

Last Updated February 13, 2017

CHECKDB and Corruption, SQL Server

In Michigan where I grew up, we pull the boats out of the water at the beginning of the fall. It’s a bit of a sad time, realizing we’re done having fun on the water, and the seasons are about to change. Winterized boats at Goose Island Boatyard – Daniel X. O’Neil To prepare a…

Filtered Indexes and Dynamic SQL

Last Updated February 13, 2017

Jeremiah Peschka

Brent Ozar Unlimited Team

I’ve been told that an attendee at the PASS Summit pre-conference event asked about using dynamic SQL to get around some of the problems with filtered indexes. I’m not entirely sure what the question was, but it did give me the opportunity to play around with filtered indexes and write some simple demo code to…

What You Can (and Can’t) Do With Filtered Indexes

Last Updated January 21, 2019

Filters aren’t just for instagram photos. When you write a query, you (usually) don’t want to return all the rows from the table(s) involved – so you add a WHERE clause to the statement. This ensures that fewer rows are returned to the client – but doesn’t reduce the amount of I/O done to get…

Building a Report to View Memory Usage

Last Updated June 27, 2021

Memory is one of the most-used resources in SQL Server. Generally, the more you have, the better query performance you’ll get. This blog isn’t about the magic of the buffer pool or the plan cache, but you should understand how important they are to your server’s performance. Since memory is such an important resource, you…

What Developers Need to Know About SQL Server

Last Updated February 13, 2017

Development, SQL Server

What Developers Need to Know About Designing Databases Understand that changing database design at a later date is harder than changing code. Getting it right the first time is important #dellsql — Gail Shaw (@SQLintheWild) October 24, 2013 One of my favorite tips, and I never thought of it that way. Code is agile, and…

“You get free videos! And you get free videos!” #SQLintersection

Last Updated January 17, 2019

This week, Jeremiah, Kendra, and I are presenting at SQL Intersection, a conference that also happens at the same place and time as DevIntersection and AngleBrackets. I like Intersections because there’s a bunch of different technologies covered – SQL Server, all kinds of development, SharePoint, cloud, and infrastructure. Kendra Polling the Audience For our pre-con,…

Upcoming User Group Sessions in Chicago and LA

Last Updated October 22, 2013

Coming soon to a user group near you, it’s…me. November 13: Chicago .NET User Group Brent’s How to Think Like the Engine: SQL Server Internals for Developers You’re comfortable writing queries to get the data you need, but you’re much less comfortable trying to design the right indexes for your database. In this 90-minute session…

Frequently Asked Questions About TempDB

Last Updated November 4, 2020

SQL Server, TempDB

The questions came fast and furious in one of my recent TempDB webcasts, so here’s the ones I wasn’t able to answer during the live session: Q: Virtualized OS, SAN, no dedicated LUNs, most likely scenario with no gotchas, theoretically: dump TempDB with everything else all on one virtual volume, including logs — or split…

Index Hints: Helpful or Harmful?

Last Updated February 13, 2017

Let me ask you a question: do you want to boss the SQL Server query optimizer around? If you answered no: good. You’re willing to let the query optimizer do its job, which is to find the least expensive way to run a query, as quickly as possible. If you answered yes: you’re a brave…

Announcing sp_BlitzFirst® for Troubleshooting Slow SQL Servers

Last Updated March 26, 2014

When someone tells you the SQL Server is slow, what do you do? Run sp_who looking for queries that might be blocking someone Check the SQL Agent jobs to see if there’s a backup job running Fire up Activity Monitor looking for problems Remote desktop into the server to look at CPU use Open Perfmon…

#SQLPASS #Summit2013 Professor DeWitt Keynote

Last Updated August 11, 2019

#SQLPass, In-Memory OLTP (Hekaton), SQL Server

This morning at the PASS Summit, we have the pleasure of listening to Professor David DeWitt talk about Hekaton internals. I’m actually not going to liveblog this – I’m going to sit back and take in the presentation as an attendee because it’s going to be so damn good, and I’m not going to be…

#SQLPASS #Summit2013 Microsoft Keynote Liveblog

Last Updated February 12, 2017

#SQLPass, SQL Server

Charlotte, NC – Your intrepid reporter is onsite at the convention center, settling in at the Blogger’s Table. At 8:15AM Eastern time, PASS President Bill Graziano will take the stage, discuss PASS, and then hand things over to Microsoft’s Quentin Clark for product announcements. Some of the things I’m looking for include: Attendance numbers on…

3 Signs Your DBA Skills Need an Upgrade (video)

Last Updated October 12, 2013

Kendra Little

You’re a Database Administrator who gets the job done day in and day out– but you’re worried that maybe your skills aren’t quite up to par. How do you know if you’ve fallen behind? Join Kendra in this free webcast for a simple test to learn if your DBA knives are as sharp as they…

Celebrating Two Years of sp_Blitz® (And a New Version)

Last Updated February 13, 2017