Search Results for &#8220;execution plan&#8221;

Problem: your database server’s performance stinks like Limburger cheese. Photo courtesy of http://en.wikipedia.org/wiki/File:Wisconsin_Limburger_Cheese.JPG Solution: find the queries that are making that smell. Hiding in Plain Sight Your database server knows exactly which queries are performing the worst. When queries are executed, the execution plans – and associated execution statistics – are stored in memory (with…

Performance Tuning kCura Relativity

Last Updated February 9, 2017

kCura Relativity, SQL Server

In the e-discovery business, kCura Relativity is the big gorilla, and I’ve been working with kCura and their clients since 2011. I think it’s one of the most interesting businesses I’ve ever worked with – up there with StackOverflow – and like Stack, they’re really open with their customers. George Nedwick and I staffing the…

My Five Favorite That Conference Memories

Last Updated August 22, 2013

Last week, I went to summer camp for geeks, and I had a great time! That Conference is a three-day, developer-focused, family-friendly technology conference. Camp was held for the first time in 2012. I heard such great things about it from last year’s campers that I decided to go this year! The sessions focused on…

Memory Pressure Affecting Queries

Last Updated July 27, 2020

When your query starts, it asks – begs, really – for a certain amount of memory to store its results, do sorts, join things together, and contemplate life. The amount of memory is determined by how much data will be pulled back from each table, how big the rows are, and so forth. SQL Server…

Trivial Optimization and Missing Indexes in SQL Server

Last Updated May 12, 2019

Indexing, SQL Server

Recently, Jes asked the team an index tuning question: “If a query has an index hint in it, will the optimizer ever suggest a missing index for that query?” I immediately loved the question because I’d never really thought about it before. I typically think of index hints as being a very risky game and…

The Elephant and the Mouse, or, Parameter Sniffing in SQL Server

Last Updated January 5, 2019

Development

Imagine you work for a zoo, and you need to ship an animal to another zoo. If you are told one will be a mouse and one will be an elephant, you will need to handle them differently. The Detroit Zoo used a moving van to move two elephants, while mice can be put in…

New DMVs in SQL Server 2014 CTP1

Last Updated June 25, 2013

When you download and start playing with SQL Server 2014 CTP1, here’s a few of the new instrumentation tables and views: New Internal Tables plan_persist_context_settings plan_persist_plan – Hey, lookie there! Implies that we can persist execution plans beyond a SQL Server service restart. plan_persist_query plan_persist_query_text plan_persist_runtime_stats plan_persist_runtime_stats_interval New Views column_store_row_groups dm_db_merge_requests dm_db_xtp_checkpoint – Note that both…

Optimize for… Mediocre?

Last Updated May 26, 2020

Parameter Sniffing, SQL Server

Some query hints sound too good to be true. And, unfortunately, usually they aren’t quite as magical as they might seem. Frustration with unpredictable execution times People often learn about parameter sniffing when query execution times stop being predictable. Occasionally you’ll hear about a stored procedure taking much longer than normal, but the next time…

The Index Your Queries will Never Request (A Clustered Index!)

Last Updated April 10, 2017

Indexing, SQL Server

When it comes to indexes, SQL Server is really helpful. It lets you see what indexes queries are asking for both in execution plans, and missing index dynamic management views (“DMVs”). I like to look at the DMV missing index requests using sp_BlitzIndex®. When you look at missing index requests, it’s always important to remember…

3 Things You Need to Stop Doing to Your Database Server

Last Updated April 9, 2013

No one knows everything, especially about SQL Server. When you encounter a problem, especially for the first time, you may not know how to solve it. I’ve seen some crazy approaches taken to fix problems – like duct tape on a car. Many times, instead of fixing the root cause of a problem, such as…

Get Top Resource-Consuming Queries

Last Updated December 31, 2018

sp_BlitzCache™ will get the top resource-intensive queries in the plan cache. It’s safe to run on heavily loaded servers – it’s going to take a few seconds to run, but it won’t block execution of other queries. This will not work on SQL Server 2005. You can download a legacy version of this script for your…

How Does SQL Server Store Data?

Last Updated July 13, 2017

Let’s step back and take a look at the big picture. (Today, I’m writing for beginners, so you advanced gurus can go ahead and close the browser now. I’m going to simplify things and leave a lot out in order to get some main points across. Don’t well-actually me.) Microsoft SQL Server databases are stored…

Stored Procedure Created WITH RECOMPILE Option

Last Updated September 16, 2018

sp_Blitz Result: Stored Procedure WITH RECOMPILE Option When you create a stored procedure using the WITH RECOMPILE option, it gets a brand new execution plan every time it runs. This can be good for high performance queries because they get a plan perfect for the variables that are passed in. However, this causes increased CPU…

SQL Server Management Studio: “Include Client Statistics” Button

Last Updated November 28, 2012

I’m curious. I like to know how things work. I have to read the user manual of everything I buy so I know all of its features. I like clicking all the buttons in applications I work with. However, SQL Server Management Studio (SSMS) has so many I haven’t gotten through all of them. At…

User-Defined Functions Slowing Down SQL Server

Last Updated December 10, 2012

Blitz Result: User-Defined Functions Good development practices dictate building reusable code, and user-defined functions seem like a good way to promote code reuse. Unfortunately, it turns out that SQL Server doesn’t perform well with most scalar user-defined functions. This part of our SQL Server sp_Blitz script checks the plan cache looking for resource-intensive queries that…

Missing Index

Last Updated December 31, 2018

Blitz Result: Missing Index Found in the Plan Cache SQL Server can use indexes to make queries run much faster, and while it’s executing queries, it tracks which indexes it wishes would have been around. We can query the DMVs to discover what indexes SQL Server recommends, and we can also check the plan cache…

Implicit Conversion

Last Updated July 27, 2020

This is when you mismatch data types in a WHERE clause or JOIN condition, and SQL Server needs to convert one on the fly. The penalty you pay here is that indexes won’t be used efficiently, you’ll burn CPU in the conversion process, and in the case of inadequate indexing, no missing index request will…

SQL Server Training Classes by Brent Ozar

Last Updated March 8, 2021

One-Day Fundamentals You need to make SQL Server fast and reliable. I do this stuff in real life every week, and I can teach you how to make it happen. Start with the top left box (How I Use the First Responder Kit) and then only take classes that border classes you’ve already taken. For…

The Use and Abuse of RECOMPILE in SQL Server (Video)

Last Updated June 26, 2012