Category: query-performance

Using YouTube Captions & Transcript to Find Content in a Long Video

on February 23, 2018

This week, I was pretty pumped to see that PASS published Erin Stellato (twitter) and Dejan Krakovic’s (linked in) excellent session on Query Store to the public.

I attended this session live at the conference, and I remembered that there was one specific part on memory limits in Query Store that I wanted to watch again. I’d jotted down a note about it, but things were flying by and my note was gibberish.

Does a Clustered Index Give a Default Ordering?

By Kendra Little on February 2, 2018

Category: query-performance , sql-server

Tags: clustered-indexes , order-by

I recently got a great question: if I order by a column where all rows in that column have the same value, will SQL Server then order the results by the clustered index key?

Auto-Tuning and the #1 Mindset Problem I Had as a DBA

By Kendra Little on December 4, 2017

Category: query-performance , sql-server

Tags: adaptive-query-processing , auto-tuning , career , dear-sql-dba , query-store

A few folks have asked: will auto-tuning and adaptive query plans mean the end of performance tuning jobs for SQL Server? In this week’s episode, I talk about why I’m excited about those features rather than afraid of them.

Digging into this problem, I share the #1 mindset problem I had as a DBA, why this mindset is so common among database professionals, and a daily habit that can change your approach to new technology.

Slowing Down Your Motor Mouth - Presentation Tips for Fast Talkers

on November 20, 2017

Category: query-performance

Tags: dear-sql-dba

Are you worried that you talk too fast when you give a speech, talk, or presentation?

In this episode, I give you simple, practical tips that I’ve used to successfully improve the way I give presentations. Four years ago, I got frustrated feedback from listeners who had a hard time keeping up with my mouth; now I get congratulations that the pace was great.

How Do I Analyze a SQL Server Execution Plan?

on September 22, 2017

Category: query-performance

Tags: dear-sql-dba

A query is slow, and you figure out how to collect the query execution plan. Now what?

In this video, I talk “big picture” about what execution plans are, what “cost” is, why to collect “compiled for” values, and the steps I take to analyze execution plans while performance tuning queries.

Do Index Changes Remove Execution Plans from Cache?

By Kendra Little on August 9, 2017

Category: query-performance

Tags: execution-plans , plan-cache , recompilation

When you modify the indexes on a table, SQL Server needs to reconsider how it executes queries that reference that table. But the way this appears when you’re looking at your execution plan cache is far from obvious:

Impacted query plans are NOT removed from cache at the time you change an index. The next time the query is executed, it will be recompiled, and the new plan and related execution information will be placed in cache.
This is true whether or not the index is used by the query.
Even if the index is on columns in the table which are not referenced by the query, the query will recompile on the next run.

To see this in action, you can play along with this sample script.