Category: Indexing

Performance Tuning

Adventures In Foreign Keys 2: Common Foreign Key Errors

November 13, 2018

This week, we're all about foreign keys. Yesterday's post covered scripts to set up the Stack Overflow database to get ready, eliminating data that would violate FK relationships.
You Had Two Jobs!
Let's say I wanted to implement Foreign Keys to do two things

If a user deletes their account, all of their badges, comments, and posts will also get deleted
If a user deletes their post, all of the comments and votes will also get deleted

Read more about Adventures In Foreign Keys 2: Common Foreign Key Errors 4 comments — Join the discussion
Performance Tuning

Adventures In Foreign Keys 1: Setting Up Foreign Keys in Stack Overflow

November 12, 2018

In A Foreign Key, In A Foreign Table Much of what people want from foreign keys, like referential integrity and join elimination, are only as guaranteed as much as SQL Server can trust your constraints (and even then...). The same goes for check constraints, too. Thankfully, things like Primary Keys and Unique Constraints are sort…

Read more about Adventures In Foreign Keys 1: Setting Up Foreign Keys in Stack Overflow 11 comments — Join the discussion
Performance Tuning

How Check Constraints MIGHT Improve Your Queries and Missing Index Requests

October 31, 2018

The more SQL Server knows about your data, the better your query plans can get.

Say you've got an app that's designed to store multiple companies in a single database - but you don't actually use it that way. All of the data in a given database is actually for the same company.

Read more about How Check Constraints MIGHT Improve Your Queries and Missing Index Requests 14 comments — Join the discussion
Performance Tuning

Filtered Indexes vs Parameterization (Again)

October 26, 2018

At First I Was Like... This won't work at all, because parameters are a known enemy of filtered indexes. But it turns out that some filtered indexes can be used for parameterized queries, whether they're from stored procedures, dynamic SQL, or in databases with forced parameterization enabled. Unfortunately, it seems limited to filtering out NULLs…

Read more about Filtered Indexes vs Parameterization (Again) 2 comments — Join the discussion
Performance Tuning

Batch Mode For Row Store: Does It Fix Parameter Sniffing?

October 25, 2018

Snorting The Future
SQL Server 2019 introduced batch mode over row store, which allows for batch mode processing to kick in on queries when the optimizer deems it cost effective to do so, and also to open up row store queries to the possibility of Adaptive Joins, and Memory Grant Feedback.

These optimizer tricks have the potential to help with parameter sniffing, since the optimizer can change its mind about join strategies at run time, and adjust memory grant issues between query executions.

Read more about Batch Mode For Row Store: Does It Fix Parameter Sniffing? 4 comments — Join the discussion
Performance Tuning

Index Tuning Week: Getting Blocking? Play “Hot or Not.”

October 11, 2018

This week, we're all about tuning indexes. So far, we've covered Brent's 5 and 5 Rule and The D.E.A.T.H. Method. Today, let's talk about reducing blocking and deadlocking. Normally, when we think about the causes of blocking or deadlocks, we use badly written queries that modify different tables in the wrong order, resulting in a Mexican…

Read more about Index Tuning Week: Getting Blocking? Play “Hot or Not.” 9 comments — Join the discussion
Performance Tuning

Index Tuning Week: My D.E.A.T.H. Method for Tuning Indexes

October 9, 2018

This week I'm running a series of posts introducing the fundamentals of index tuning. Yesterday, I used Brent's 5 and 5 Rule to explain why you want to aim for around 5 indexes per table, and 5 columns (or less) per index. Now, how do we get from our current database situation to a better place?

When someone hands me a database and tells me to make it go faster without changing the code, I start with my D.E.A.T.H. Method:

Read more about Index Tuning Week: My D.E.A.T.H. Method for Tuning Indexes 2 comments — Join the discussion
Performance Tuning

Unused Indexes – Are they really unused or have they just not been used YET?

October 2, 2018

During our Critical Care® sessions with clients, we often see unused indexes (reads=0) with high writes. Sometimes these unused indexes have very high writes. If an index is never being used to help with performance but SQL Server is having to maintain it for INSERT, UPDATE and DELETE operations, then why keep the index around?

Read more about Unused Indexes – Are they really unused or have they just not been used YET? 12 comments — Join the discussion
Performance Tuning

Should Index Changes Require Change Control?

September 14, 2018

We got a phenomenal series of questions from a client, and I wanted to encapsulate the answers into a blog post to help more folks out: Should all index changes require testing in a staging environment, no matter how big or small? What would be a reasonable timeline duration from index identification to deployment? What…

Read more about Should Index Changes Require Change Control? 9 comments — Join the discussion
Performance Tuning

Forwarded Fetches and Bookmark Lookups

September 7, 2018

Base Table
When you choose to forgo putting  a clustered index on your table, you may find your queries utilizing forwarded fetches -- SQL Server's little change of address form for rows that don't fit on the page anymore.

This typically isn't a good thing, though. All that jumping around means extra reads and CPU that can be really confusing to troubleshoot.

Read more about Forwarded Fetches and Bookmark Lookups 4 comments — Join the discussion
Performance Tuning

It’s Okay If You Don’t Create Statistics.

September 6, 2018

Along with the ability to create indexes (which you most definitely should be doing), SQL Server gives you the ability to create statistics. This helps SQL Server guess how many rows will come back for your searches, which can help it make better decisions on seeks vs scans, which tables to process first, and how much memory a query will need.

Read more about It’s Okay If You Don’t Create Statistics. 9 comments — Join the discussion
Performance Tuning

Single-Column-Key Missing Index Recommendations are Usually Wrong.

September 4, 2018

When you're looking at an index recommendation - whether it's in an execution plan or the missing index DMVs - it helps to understand Clippy's blind spots. Let's start with the small StackOverflow2010 database so you can follow along. (It's just a 1GB direct download, and it expands to a 10GB database that reflects StackOverflow.com…

Read more about Single-Column-Key Missing Index Recommendations are Usually Wrong. 2 comments — Join the discussion
Performance Tuning

[Video] Interpreting Missing Index Recommendations

July 11, 2018

This week, we're sharing Instant Replay videos from our training classes. Next up is Pinal Dave talking about missing index requests from the dynamic management views. It doesn't matter whose script you use - they're all built atop SQL Server's missing-index DMVs, and they all have the same limitations. Treat the missing index requests the same way you would treat appetizers in a restaurant - if you order an appetizer and it's no good, don't order more dishes.

Read more about [Video] Interpreting Missing Index Recommendations Be the first to comment
Performance Tuning

Does It Matter Which Field Goes First in an Index?

June 28, 2018

Let's take the dbo.Users table from the Stack Overflow database, which holds exactly what you think it holds - the list of users:

Say I want to count up all of the people with 1 reputation point:
[crayon-6a637c12ac734101452336/]
Without any indexes, that would scan the whole table. So to make it go faster, let's create an index on Reputation:
[crayon-6a637c12ac739595915333/]
SQL Server uses the Reputation index for the count query:

Read more about Does It Matter Which Field Goes First in an Index? 9 comments — Join the discussion