Product has been added to your cart.

Development

How to Create a Table with a Partitioned Clustered Columnstore Index

September 16, 2020

If you need to create a table and you want it to be partitioned right from the start, AND you want it to have a clustered columnstore index, here’s a creation T-SQL example: CREATE TABLE dbo.MyTableName( Id int IDENTITY(1,1), SalesDate datetime NOT NULL, INDEX MyIndexName CLUSTERED COLUMNSTORE ) ON ps_MyPartitionFunction(SalesDate); GO 123456 CREATE TABLE dbo.MyTableName(…

“Not to write any procedure over 50 lines”

September 10, 2020

In Joe Celko’s Stairway to Database Design series, he writes: The rules of thumb for T-SQL are not to write any procedure over 50 lines (one page) This seems so radical at first, but it has precedence in NASA’s 10 Rules for Developing Safety-Critical Code: Avoid complex flow constructs, such as goto and recursion. All loops must have fixed…

Bonfire of the vanities

“UPDATE, INSERT, and DELETE are not normally processed in parallel”

September 8, 2020

Execution Plans

Years ago, when troubleshooting performance, I stumbled across this Microsoft documentation on parallel query processing that says: Certain types of statements cannot be processed in parallel unless they contain clauses, however. For example, UPDATE, INSERT, and DELETE are not normally processed in parallel even if the related query meets the criteria. But if the UPDATE…

Watch Brent Tune Queries at SQLSaturday Oslo

August 29, 2020

Execution Plans, Videos

This morning, I had the honor of presenting virtually at SQLSaturday Oslo. The session title, Watch Brent Tune Queries, explains it all. Enjoy! In the session, I used the 2018-06 Stack Overflow database, but any recent full-sized copy (not the 2010-2013 copies) will work. Here’s the query I worked with: /* Watch Brent Tune Queries:…

Half Of You Don’t Understand Variables and Transactions.

August 20, 2020

Do table variables and variables respect transactions? If I set the value of a variable during a transaction, and I roll it back, what happens? DECLARE @MySalary INT = 100000; BEGIN TRAN SET @MySalary = @MySalary * 2; ROLLBACK; SELECT @MySalary; 1234567 DECLARE @MySalary INT = 100000; BEGIN TRANSET @MySalary = @MySalary * 2;ROLLBACK; SELECT @MySalary; I…

Explaining SQL Server Statistics with Playing Cards

August 17, 2020

When you run a query, how does SQL Server estimate the number of rows? I’ll explain in this half-hour video demo using playing cards, then showing the same issues in SQL Server Management Studio: Here’s the demo script I use in the video. To learn more, check out our free statistics class on YouTube.

How Do I Know If My Query Is Good Enough for Production?

August 12, 2020

When I ask that question, here are some of the things I think about: How many times am I going to run it? Is it a one-off task, or is it going to run thousands of times per second on the front page of a popular web site? What time of the day/week will it…

Date Tables are Great for Users, but Not So Great for Performance

August 4, 2020

Execution Plans, Videos

Date tables help users filter data based on day of week, holiday, this year vs last year, and other kinds of filters that business users usually wanna add to their reports. If you haven’t used a date table before, here are a couple of primers on how to create one and use it: Creating a…

[Video] The Top 10 Developer Mistakes That Won’t Scale on Microsoft SQL Server

August 3, 2020

Development, Videos

You’re a C# or Java developer building an application that stores data in Microsoft SQL Server, and you want to avoid common pitfalls that will cause you scalability problems down the road. In this session from my live stream, you’ll learn why you probably want to avoid triggers, SELECT *, user-defined functions, dynamic SQL, big…

A One-Slide Summary of the Differences Between TDE and Always Encrypted

July 31, 2020

The folks on Twitter liked this, so sharing it here: It’s a one-slide summary from a SQL Critical Care client’s deck, so obviously it’s abridged, but I think it does a pretty good job of summing things up. Some highlights of the conversation: A solid 20 minutes of my talk from last year’s Summit, summed…

Remember “Nothing Stops a Hekaton Transaction?” Yeah, About That.

July 29, 2020

In-Memory OLTP (Hekaton)

When In-Memory OLTP first came out, Microsoft liked to go around bragging at conferences that “Nothing stops a Hekaton transaction.” I remember hearing that and kinda chuckling at the time, and I was reminded of it by a recent client who was running into blocking issues with…yep, Hekaton. To reproduce it: Do a large insert…

The 201 Buckets Problem, Part 2: How Bad Estimates Backfire As Your Data Grows

July 8, 2020

In the last post, I talked about how we don’t get accurate estimates because SQL Server’s statistics only have up to 201 buckets in the histogram. It didn’t matter much in that post, though, because we were using the small StackOverflow2010 database. But what happens as our data grows? Let’s move to a newer Stack…

The 201 Buckets Problem, Part 1: Why You Still Don’t Get Accurate Estimates

July 7, 2020

I’ll start with the smallest Stack Overflow 2010 database and set up an index on Location: Transact-SQL USE StackOverflow2010 GO CREATE INDEX Location ON dbo.Users(Location); GO SELECT COUNT(*) FROM dbo.Users; GO 123456 USE StackOverflow2010GOCREATE INDEX Location ON dbo.Users(Location);GOSELECT COUNT(*) FROM dbo.Users;GO There are about 300,000 Users – not a lot, but enough that it will…

You Can Disable Parameter Sniffing. You Probably Shouldn’t.

June 30, 2020

Parameter Sniffing

During my parameter sniffing classes, people get a little exasperated with the complexity of the problem. Parameter sniffing is totally hard. I get it. At some level, it’d be great to just hit a magic button and make the whole thing go away. So inevitably, somebody will ask, “What about the database-level setting called Parameter…

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

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)

June 15, 2020

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.

June 2, 2020

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.

May 20, 2020

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.

May 6, 2020

“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

May 1, 2020

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…