Search Results for &#8220;parameter sniffing&#8221;

Trace flags are special switches that, when you flip them, enable different behaviors in your SQL Server. Back in the SQL Server 2000-2005 days, these were super-rare undocumented tricks that were handed out by Microsoft support to fix rare cases. Over time, some trace flags have become so commonplace that they worked their way into…

Why is SQL Server Slow Sometimes – But Only Sometimes?

Last Updated November 12, 2015

Have you got a SQL Server that usually performs just fine, but every now and then, everything falls to pieces? Users complain about performance, and then minutes – or hours – later, the problem just mysteriously solves itself? Here’s a few things to look for: Are data or log files growing? When SQL Server needs to…

Five Interview Questions to Ask SQL Server Developers

Last Updated March 9, 2015

Doug Lane

Interviewing

“If you were a tree, what kind of tree would you be?”“A tree with a job.” When it comes to hiring a SQL Server developer, we can pepper candidates with HR-type questions (“Tell me about a time when you had a conflict with a co-worker and how you resolved it.”) but that doesn’t give us…

Developers: Who Needs a DBA? [Video]

Last Updated June 21, 2016

Execution Plans, SQL Server

You store data in SQL Server, but you don’t have enough work to keep a full-time DBA busy. In just half an hour, you’ll learn the basics of performance troubleshooting and index tuning from me – a recovering developer myself. I’ll show you the basic care and feeding of a Microsoft SQL Server instance and give…

Comparing Estimated and Actual Execution Plans in SQL Server

Last Updated May 12, 2019

Jes Schultz

Let’s say you want to have an addition built on your house. You contact a contractor, who comes to your house, looks at your lot, looks at what exists, and asks you questions about exactly what you want. He then gives you an estimate of the work – approximately how long he thinks it will…

Stabilizing Execution Plans: Plan Guides and NORECOMPUTE

Last Updated May 26, 2020

Execution Plans, Parameter Sniffing, SQL Server

What could go wrong? Sometimes you end up in a good plan / bad plan situation: an important query runs just fine most of the time. The query is parameterized, a good execution plan gets re-used, everything is cool. But sometimes, a “bad plan” gets compiled and starts to be reused. This is “bad” parameter sniffing. “Bad plans” can…

Performance Tuning SQL Server Change Tracking

Last Updated August 11, 2015

Change Tracking is a developer tool introduced in SQL Server 2008 to help you sync data between SQL Servers, or between SQL Servers and devices. It’s designed to help you answer the request, “I need to get the most recent value for rows that have been modified since I last grabbed data.” First, I’ll give you…

Why Index Fragmentation and Bad Statistics Aren’t Always the Problem (Video)

Last Updated February 13, 2017

Index Maintenance, SQL Server

Do you rely on index rebuilds to make queries run faster? Or do you always feel like statistics are “bad” and are the cause of your query problems? You’re not alone– it’s easy to fall into the trap of always blaming fragmentation or statistics. Learn why these two tools aren’t the answer to every problem…

RECOMPILE Hints and Execution Plan Caching

Last Updated April 2, 2021

Execution Plans, Parameter Sniffing

The DBA version of an XKCD classic. When you identify that parameter sniffing is a problem, you need to test whether implementing ‘recompile’ hints will help plan quality. (Not sure what parameter sniffing is? Learn from this blog post or this 50 minute free video.) You must decide: what hint or command do you use,…

Frequently Asked Questions About TempDB

Last Updated November 4, 2020

SQL Server, TempDB

The questions came fast and furious in one of my recent TempDB webcasts, so here’s the ones I wasn’t able to answer during the live session: Q: Virtualized OS, SAN, no dedicated LUNs, most likely scenario with no gotchas, theoretically: dump TempDB with everything else all on one virtual volume, including logs — or split…

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…

3 Things You Need to Stop Doing to Your Database Server

Last Updated April 9, 2013

Jes Schultz

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…

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…

Trace Flags Enabled Globally

Last Updated April 24, 2019

sp_Blitz Result: Trace Flags Enabled Globally SQL Server has configuration knobs called trace flags that you can turn off and on – either at the session level for experimentation, or at the server level. Trace flags are typically used for diagnostic purposes and they may need to be disabled once the issue is corrected. This…

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…

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…

Simon Sabin’s talk on query performance

Last Updated March 10, 2021

Execution Plans

At #SQLbits, Simon Sabin (Twitter) talked about car crash queries: queries that suddenly have bad performance out of nowhere, yet if you copy/paste them into SQL Server Management Studio, they run quickly. The culprit is often parameter sniffing: the engine looks at the query and builds a plan, but if the parameters in the WHERE…

Scaling SQL Server

Last Updated January 13, 2023

Scale Up or Scale Out? Scaling SQL Server I would like to scale up my coffee cup. Want your app to be able to handle more users? Here are tips, tricks, and links to comprehensive references on how to scale SQL Server– either Scale Up or Scale Out. Do You Really Have a Scale Problem? It…

Index Fragmentation Findings: Part 2, Size Matters

Last Updated July 13, 2017