SQL Server Archives - Page 9 of 53 - Brent Ozar Unlimited®

T-SQL & Development

Computed Columns: Reversing Data For Easier Searching

January 24, 2018

During Training
We were talking about computed columns, and one of our students mentioned that he uses computed columns that run the REVERSE() function on a column for easier back-searching.

What's back-searching? It's a word I just made up.

Performance Tuning

How Much Can One Row Change A Plan, Part 4

January 19, 2018

Monte Carlo Or Bust
In Part 3, I showed you how two queries with TOP clauses can produce wildly different plans.

To figure some stuff out about why the plans changed, let's focus on the Nested Loops Join plan.

How Much Can One Row Change A Plan, Part 3

January 18, 2018

All The Rage
We've asked how much one column and even one row can change a plan before.

The doctor says I'm not allowed to read blog comments anymore on account of my blood pressure, but the AI I wrote in PowerShell assures me they were well-received.

Performance Tuning

When Does Index Size Change Index Choice?

January 16, 2018

Size Matters?
One thing I hear a lot is that the optimizer will take the size of an index into account when choosing which one to use.

The tl;dr here is that it may make more of a difference when it comes to index width (think the number of columns in an index) than it does the actual size in MB or GB of the index.

[Video] Office Hours 2018/1/10 (With Transcriptions)

January 14, 2018

This week Brent, and Richie discuss validating backups using the backup set table, how to figure out if you're a Mid-Level or Senior DBA, rollbacks, compatibility modes while upgrading SQL Server versions, MAXDOP settings, and Brent's car purchasing tip.

Here’s the video on YouTube:

SQL Server 2017 CU3 adds optimizer row goal information in query plans

January 12, 2018

Row Whats?
Like it or not, the optimizer has row goals.

We're not going to go terribly in-depth here, because the point is just to make you aware that this is in executions plans, and I'll be adding it to the FRK in an upcoming release.

SQL Server 2017 CU3 adds tempdb spill diagnostics in DMVs and Extended Events

January 11, 2018

Where have you been all my life?
Whenever I'm teaching people about query tuning, two things come up almost without fail: parameter sniffing and tempdb spills.

They're not mutually exclusive -- some queries will seemingly always spill to tempdb, whether parameter sniffing is present of not, and some queries experiencing issues with parameter sniffing will never spill to tempdb.

Performance Tuning

SQL Server 2017 CU3 add a new CXCONSUMER wait type

January 10, 2018

SQL Server 2017's new CXCONSUMER wait type was originally announced by Microsoft's Pedro Lopes, and now it's out. Here's what it means for performance tuners. According to Pedro, this wait is the "safe" type of parallelism wait, as opposed to the CXPACKET wait type, which means work isn't evenly balanced across all of our cores. Pedro blogged…

Performance Tuning

SQL Server 2017 CU3 adds execution statistics for scalar-valued, user-defined functions

January 9, 2018

Update
This seems to finally be fixed as of CU12 for SQL Server 2017. If you're not already patched up, head over to the link to get there.
Estimated Plans Need Not Apply
This is, of course, only available in actual plans.

All together now:

SQL Server Patches for Meltdown and Spectre Attacks

January 4, 2018

The Meltdown and Spectre attacks are newly announced ways of hacking CPUs. They affect both Windows and Linux. If you're running under virtualization or cloud IaaS, any other running guest can try to leverage the attacks to compromise your unrelated guest.

These are a really big deal.

Performance Tuning

Heaps, Deletes, and Optimistic Isolation Levels

January 2, 2018

The Humble Heap
If you don't know this by now, I'm going to shovel it at you:

If you have a table with no clustered index (a Heap), and you delete rows from it, the resulting empty pages may not be deallocated.

Production DBA

Sizing A New Server? Start With Maintenance.

December 22, 2017

Clientele
Most of out customers are on slightly (ahem) older (AHEM) hardware, and they have big questions:

How do we know what kind of hardware we need?
How do we load test it?
How do we know what to change to make it better?

Maintenance Plans Enable Your Disabled Indexes

December 20, 2017

One of Brent's students in the Performance Tuning class, Jason M., told Brent that maintenance plans enable your disabled indexes. What the what?

Steps to reproduce:

What Your SQL Server Is Really Waiting On: YOU

December 12, 2017

Land of Confusion
Most SQL Servers out there have never had anyone do a thorough, targeted assessment on them, the way we do our Critical Cares.

This leads to a lot of astray troubleshooting, which may make sense for profiling other problems, but not SQL Server.

Performance Tuning

sp_BlitzQueryStore: A Gentle Introduction

December 7, 2017

Odds and ends
During the precon, we asked how many people were on 2016, how many people were aware of query store, how many people were using it, and how many people were using sp_BlitzQueryStore.

About 1/3 of the hands went up at first, then for each successive question, fewer and fewer hands stayed up.

Index Maintenance Madness

December 5, 2017

When we look at a new server, we check out their database maintenance jobs, making sure their server is in a safe place before we dig into their pain points. We verify if their backups and DBCC CHECKDB jobs can meet their RPO/RTO goals. We also check out their index maintenance jobs, if any.

Contest: Guess the SQL Server 2019 Release Date

December 1, 2017

When will Microsoft officially release SQL Server 2019 for download? The dust is just barely starting to settle on the 2017 box, so it's time to guess the next one. Leave one – and only one – comment here in YYYY/MM/DD format with your release date guess. If you leave multiple comments, only the first/earliest…

[Video] Office Hours 2017/11/15 (With Transcriptions)

November 20, 2017

This week Brent, Erik, Richie, and Tara discuss Docker, writing to different servers in different locations, SAN Replication, reducing the size of a database, patching Availability Groups, getting rid of failover cluster instances, long-term backup storage options, VLF numbers, and more.

Here’s the video on YouTube:

Coming in SQL Server 2019: Approximate_Count_Distinct

November 8, 2017

Last week at the PASS Summit in Seattle, Kevin Farlee & Joe Sack ran a vNext demo in the Microsoft booth and dropped a little surprise. SQL Server 2019 will let you trade speed for accuracy.

They're working on a new APPROXIMATE_COUNT_DISTINCT.

Performance Tuning

How to Get Live Query Plans with sp_BlitzWho

October 31, 2017

sp_BlitzWho is our open source replacement for sp_who and sp_who2. It has all kinds of really neat-o outputs like how many degrees of parallelism the query is using, how much memory it's been granted, how long it's been waiting for memory grants, and much more.

If you're on SQL Server 2016 SP1 or newer, it can show you a query's live execution plan from sys.dm_exec_query_statistics_xml.