Development

Home
Development

Sit Down. We Need to Talk About Your Scalar Functions.

Last Updated June 25, 2020

Development, SQL Server 2019

Thanks for coming today. I apologize for baiting you in here with a picture of toilet paper. That’s not even in the US – that’s from my Iceland trip, before I flew back here to the US. Look, we all make mistakes. That’s not the point of this post. You see, I’ve been reading this…

[Video] How to Find Queries Using OPTION RECOMPILE (And Their Parameters)

Last Updated June 23, 2020

Brent Ozar

Development, Videos

For years, I hated troubleshooting servers with high CPU usage caused by queries constantly asking for new execution plans. Hated it. SQL Server just doesn’t make it easy to find queries with recompile hints. Then Erik Darling’s sp_HumanEvents came along. And now troubleshooting frequent compilations is as easy as this: Transact-SQL EXEC dbo.sp_HumanEvents @event_type =…

Updating Statistics Causes Parameter Sniffing.

Last Updated June 3, 2020

Brent Ozar

Parameter Sniffing, Statistics

In my free How to Think Like the Engine class, I explain that SQL Server builds execution plans based on statistics. The contents of your tables inform the decisions it makes about which indexes to use, whether to do seeks or scans, how many CPU cores to allocate, how much memory to grant, and much…

Parallelism Can Make Queries Perform Worse.

Last Updated May 26, 2020

Brent Ozar

Execution Plans, Parameter Sniffing

While I was building lab queries for my all-new Fundamentals of Parameter Sniffing course – first live one is next week, still time to get in – I ran across a query with delightfully terrible behavior. I’m always torn when I build the hands-on labs. I want to make the challenges easy enough that you…

Your Views Aren’t The Problem. Your Code Is.

Last Updated May 6, 2020

Brent Ozar

T-SQL

“I hate views,” the DBA said. “They kill performance. And nested views are even worse.” Wrong. I’ll prove it. I’m going to use the 50GB StackOverflow2013 database, and I’ll start by creating a couple of indexes on the Users table to help our queries, then create a view on the Users table: Transact-SQL USE StackOverflow2013;…

Free Webcast on Dynamic SQL Pro Tips

Last Updated May 1, 2020

Brent Ozar

GroupBy Conference, T-SQL

Dynamic SQL is one of the most powerful tools in the database developer’s arsenal. When you need a complex search stored procedure that takes all kinds of parameters (price, category, location, color), dynamic SQL can run extremely quickly by leveraging better indexes. However, when done wrong, it’s extremely painful to troubleshoot. I’ve been working with…

Can You Use NVARCHAR As a Universal Parameter? Almost.

Last Updated May 27, 2020

Brent Ozar

T-SQL

A perfect storm of unusual problems caused a client to ask, “What if we just used NVARCHAR(4000) as the default datatype parameter for any query, regardless of what datatype the table has in it – like numbers or dates?” It actually works pretty well in most situations, believe it or not. The Users table of the…

If You Have Foreign Keys, Don’t Update Fields That Aren’t Changing.

Last Updated December 29, 2021

Brent Ozar

Foreign Keys, T-SQL

If you update a row without actually changing its contents, does it still hurt? Paul White wrote in detail about the impact of non-updating updates, proving that SQL Server works hard to avoid doing extra work where it can. That’s a great post, and you should read it. But foreign keys add another level of…

How to Get Better Estimates for Modification Queries

Last Updated April 27, 2020

Brent Ozar

Execution Plans, T-SQL

When you’re doing DUI operations against tables with millions of rows, you have to be really careful about SQL Server’s estimates. Ideally, you want your delete/update/insert (what – what’d you think I meant?) queries to be as simple as possible – even a simple join can cause SQL Server to do wildly, wildly incorrect estimates,…

Why Ordering Isn’t Guaranteed Without an ORDER BY

Last Updated April 11, 2020

Brent Ozar

Indexing, T-SQL

If your query doesn’t have an ORDER BY clause, you can’t reliably predict the order of your results over time. Sure, it’s going to look predictable at first, but down the road, as things change – the indexes, the table, the server’s configuration, the size of your data – you can end up with some ugly…

[Video] Altering Datatypes With (Almost) No Downtime

Last Updated October 30, 2020

Brent Ozar

Development

Sometimes, your INT columns run out of space, and you need to alter them to be a larger datatype, like BIGINT. Usually, when you do this, you’re met with screams of agony from your end users because this will totally lock the table and rewrite the whole thing: Transact-SQL ALTER TABLE dbo.Posts ALTER COLUMN OwnerUserId…

How to Track Performance of Queries That Use RECOMPILE Hints

Last Updated May 26, 2020

Brent Ozar

Parameter Sniffing, Query Store (QDS), sp_BlitzCache

Say we have a stored procedure that has two queries in it – the second query uses a recompile hint, and you might recognize it from my parameter sniffing session: Transact-SQL CREATE OR ALTER PROC dbo.usp_SearchUsers @Reputation INT AS BEGIN /* Query 1, always the same: */ SELECT COUNT(*) FROM dbo.Users; /* Query 2, recompiles…

Making TRY_CAST and TRY_CONVERT Queries Faster with Indexed Computed Columns

Last Updated March 1, 2020

Brent Ozar

Computed columns, Execution Plans, T-SQL

I know this is gonna sound crazy, but let’s say you had a table where people stored all kinds of things in one column: dates, integers, file names, sale prices, file names, you name it. And let’s say your application frequently ran a query looking for dates in that column, like this: Transact-SQL SELECT *…

WHERE GETDATE() BETWEEN StartDate AND COALESCE(CancelDate, EndDate) Is Even Harder to Tune.

Last Updated February 12, 2020

Brent Ozar

Development, Indexing

In my last post, we started with a memberships table, and each membership had start & end dates. I’m going to create the table and populate it with everyone having an active membership – their StartDate is the same as their Stack Overflow account creation date, and their EndDate is around a year or two…

WHERE GETDATE() BETWEEN StartDate AND EndDate Is Hard to Tune.

Last Updated February 19, 2020

Brent Ozar

Development, Indexing

Say you’ve got a memberships (or policies) table, and each membership has start & end dates: Transact-SQL USE StackOverflow; GO DROP TABLE IF EXISTS dbo.UsersMemberships; CREATE TABLE dbo.UsersMemberships (Id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, UserId INT NOT NULL, StartDate DATETIME NOT NULL, EndDate DATETIME NOT NULL, CancelledEarlyDate DATETIME NULL); GO 1234567891011 USE StackOverflow;GODROP TABLE IF…

How to Pass a List of Values Into a Stored Procedure

Last Updated February 7, 2020

Brent Ozar

T-SQL

Say we have a stored procedure that queries the Stack Overflow Users table to find people in a given location. Here’s what the table looks like: And here’s what my starting stored procedure looks like: Transact-SQL CREATE OR ALTER PROC dbo.usp_SearchUsersByLocation @SearchLocation NVARCHAR(40) AS SELECT * FROM dbo.Users WHERE Location = @SearchLocation ORDER BY DisplayName;…

What Are Soft Deletes, and How Are They Implemented?

Last Updated February 4, 2020

Brent Ozar

Development

Normally when you run a DELETE statement in a database, the data’s gone. With the soft delete design pattern, you add a bit column like IsDeleted, IsActive, or IsArchived to the table, and instead of deleting rows, you flip the bit column. This can buy you a few advantages: Easier/faster undeletes History tracking (keeping deleted…

[Video] Watch Brent Tune Queries

Last Updated January 31, 2020

Brent Ozar

Execution Plans, Videos

Ever wonder somebody else does it? Watch over my shoulder as I spend 9 minutes in PowerPoint explaining the big picture, and then about 40 minutes working on this stored procedure in the StackOverflow2013 database: If you enjoy that, the Watch Brent Tune Queries page has another video and other query examples. Enjoy!

How to Remove Times from Dates in SQL Server

Last Updated January 27, 2020

Brent Ozar

T-SQL

Say that you’ve got a table with a datetime column in it, like the LastAccessDate column in the Stack Overflow Users table: And let’s say you wanna find the users who last accessed the site on September 9, 2018 – regardless of the time. Here are a few ways to do it: Transact-SQL SELECT *…

Cool Query Hints

Last Updated January 22, 2020

Brent Ozar

T-SQL

The SQL Server documentation has a pretty cool list of query hints: Yeah, I surf in dark mode. I’m a dark kinda guy. But wait – what’s that colored box? ENHANCE! I could make jokes here, but … every single thing in that caution is true. SQL Server really does typically select the best execution…

Development

Save your cart?