1. Home
  2. Development
  3. (Page 21)
Plan Guides are like duct tape

Stabilizing Execution Plans: Plan Guides and NORECOMPUTE

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…
Read More

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

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…
Read More

Are Table Variables as Good as Temporary Tables in SQL 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…
Read More

Refactoring T-SQL with Windowing Functions

SQL Server, T-SQL, Videos
You’ve been querying comparative numbers like Year To Date and Same Period Last Year by using tedious CTEs and subqueries. Beginning with SQL Server 2012, getting these numbers is easier than ever! Join Doug for a 30-minute T-SQL tune-up using window functions that will cut down dramatically on the amount of code you need to…
Read More

Finding Blocked Processes and Deadlocks using SQL Server Extended Events

A lot of folks would have you think that Extended Events need to be complicated and involve copious amounts of XML shredding and throwing things across the office. I’m here to tell you that it doesn’t have to be so bad. Collecting Blocked Process Reports and Deadlocks Using Extended Events When you want to find…
Read More

How to Cache Stored Procedure Results

SQL Server, T-SQL
Say you run an online store, and on each item’s page, you need to show related items that people purchased. Let’s take an Amazon page for my favorite rechargeable AA batteries: Frequently Bought Together In a perfect world, we would cache this data in the web/app tier – but back here in the real world,…
Read More

Optimize for… Mediocre?

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…
Read More