Query Optimization and Execution Plans Archives - Page 27 of 29

Performance Tuning

Are SQL Server Functions Dragging Your Query Down?

October 14, 2014

In most coding languages, functions are often-used blocks of code that can be reused from multiple locations, leading to less code – and cleaner code. SQL Server also lets us create functions that can be used the same way. They are reusable blocks of code that can be called from multiple locations. So, if you need to format phone numbers a certain way, or parse for specific characters, you can do so using a function.

Is it Cheating to Solve A Performance Problem By Adding Memory?

September 24, 2014

We had a comment recently on Five Things That Fix Bad SQL Server Performance that got me thinking. The comment came from a frustrated system administrator, who wrote: Chucking resources at a problem is not solving that problem, it’s just temporarily masking the symptoms of that problem. Funnily enough, I've heard the exact same thing from…

Performance Tuning

Comparing Estimated and Actual Execution Plans in SQL Server

July 16, 2014

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 take his crew, what supplies will be needed, and the cost of those supplies.

Performance Tuning

Stabilizing Execution Plans: Plan Guides and NORECOMPUTE

July 10, 2014

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 come in a few…

Performance Tuning SQL Server Change Tracking

June 25, 2014

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…

Performance Tuning

Temp Tables vs Table Variables vs Memory Optimized Table Variables [Video]

June 7, 2014

Should you use temp tables or table variables in your code? Join Microsoft Certified Master Kendra Little to learn the pros and cons of each structure, and take a sneak peek at new Memory Optimized Table Variables in SQL Server 2014.

This video is a recording of a live webcast, so please excuse any audio and video imperfections. Want the scripts from the webcast? Scroll on down.

Performance Tuning

Why You’re Tuning Stored Procedures Wrong (the Problem with Local Variables)

June 3, 2014

There's an important rule for tuning stored procedures that's easy to forget: when you're testing queries from procedures in SQL Server Management Studio, execute it as a stored procedure, a temporary stored procedure, or using literal values.

Don't re-write the statemet you're tuning as an individual TSQL statement using local variables!
Where it goes wrong
Stored procedures usually have multiple queries in them. When you're tuning, you usually pick out the most problematic statement, maybe from the query cache), and tune that.

Curious About the SQL Server 2014 Cardinality Estimator?

May 15, 2014

If you've been following the blogosphere, or heading to SQL Saturdays, you'll know that many folks are excited about the brand new SQL Server cardinality estimator (CE for short). As Kendra showed in her article The SQL 2014 Cardinality Estimator Eats Bad TSQL for Breakfast, the new CE provides dramatically improved query plans. What if…

Collecting Detailed Performance Measurements with Extended Events

April 10, 2014

Analyzing a workload can be difficult. There are a number of tools on the market (both free and commercial). These tools universally reduce workload analysis to totals and averages - details and outliers are smeared together. I’m against using just averages to analyze workloads; averages and totals aren’t good enough, especially with the tools we…

Performance Tuning

Five Reasons a Production DBA Should Learn to Read Execution Plans

April 9, 2014

While chatting with attendees before my Don’t Fear the Execution Plan webcast, a question was posed: “As a DBA who mainly worries about backups, index fragmentation and checkdb, should I also be looking at execution plans?”

YES! Here’s why.

The SQL 2014 Cardinality Estimator Eats Bad TSQL for Breakfast

April 8, 2014

Sometimes I run into a query plan that just shocks me. It's like looking up and suddenly seeing an adult dressed as a squirrel riding a unicycle down a busy city street. You have to stop and ask yourself, "Did that really just happen?" (I live in Portland, Oregon, so yeah, I've seen that.) A…

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

April 5, 2014

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…

Performance Tuning

Are Table Variables as Good as Temporary Tables in SQL 2014?

April 2, 2014

There's a couple of new features in SQL Server 2014 that provide options for how you work with temporary objects. Will inline index creation or memory optimized temporary tables forever change the way you code? Let's take a look! Inline Index Creation SQL Server 2014 brings us a TSQL improvement called "inline specification of CLUSTERED…

How to count the number of rows in a table in SQL Server

February 18, 2014

“How many rows exist in a table?” It seems like such an innocent request. It isn’t too hard to get this information out of SQL Server. But before you open SSMS and whip out a quick query, understand that there are multiple methods to get this information out of SQL Server – and none of…

Using SQL Server’s Table Valued Parameters

February 6, 2014

Table valued parameters (TVPs) are nothing new - they were introduced in SQL Server 2008. TVPs are a great way to get data into SQL Server, and yet very few developers use them. Getting Started with TVPs In order to get started, we need to create a user defined type. This type is necessary so…

Performance Tuning

Statistics Matter on Temp Tables, Too

February 5, 2014

Temp tables are like real tables, just a little tricker.

When you're starting out writing TSQL, it's easy to want to do all your work in a single query. You learn about derived sub-queries, CROSS APPLY statements, and common table expressions. Suddenly, each of your queries is so complex that you hardly know what you're doing.

How to Query the StackExchange Databases

January 17, 2014

Update - October 2015 - You can download a Torrent of a database (rather than a data dump) now.

Most of my demos involve my favorite demo database: Stack Overflow. The Stack Exchange folks are kind enough to make all of their data available via BitTorrent for Creative Commons usage as long as you properly attribute the source.

Dynamic Sorting

January 9, 2014

While working on some DMV scripts, I came up with a lazy way to have a user definable sort order in the query that seemed like pure genius. I showed it to the team and they’d never seen anything like it before. The Situation Users like to be in control. They want to define custom…

Q: Can High MaxDOP make a query SLOWER?

December 23, 2013

Answer: Yep, sometimes it can.I used to think that higher degrees of parallelism followed a law of diminishing returns-- you could add more threads, but the benefits would taper off. But it's a bit more complicated than that. Microsoft's recommendation to be careful when setting maxdop to values over 8 is a warning worth heeding.Lowering maxdop…

Performance Tuning

RECOMPILE Hints and Execution Plan Caching

December 17, 2013

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, and where do you put it? What trade-offs are you making when it comes to being able to performance tune your SQL Server in the future?