Development

Never Judge A Query By Its Cost

Signs and Numbers When tuning queries, or even finding queries to tune, there’s a rather misguided desire to look for queries with a high cost, or judge improvement by lowering query cost. The problem is that no matter what you’re looking at, costs are estimates, and often don’t reflect how long a query runs for…
Read More

What Is SQL Injection?

Say we have a stored procedure that queries the Stack Overflow database. We have two separate parameters, @DisplayName and @Location, so folks can search for people by name, location, or both. For performance reasons, we decide to build dynamic SQL: Transact-SQL CREATE OR ALTER PROC dbo.UserSearch @DisplayName NVARCHAR(40) = NULL, @Location NVARCHAR(100) = NULL AS…
Read More
Brent getting caffeinated

How to Troubleshoot Blocking and Deadlocking with Scripts and Tools

When you need to find which queries are blocking other queries, your decision comes down to when the blocking happened. Is it happening now, recently, or coming up soon? During a live emergency, start with sp_WhoIsActive. Adam Machanic’s excellent free sp_WhoIsActive replaces Activity Monitor, sp_who, and sp_who2, and it’s way more powerful. The documentation is extensive –…
Read More

Not So Forced Parameterization

Asking The Wrong Question Sometimes, when you wanna turn on a feature, you spend so much time wondering if you should, you don’t bother asking if it’ll even work when you do. There are a long list of things that are incompatible with Forced Parameterization, on a page that’s pretty hard to find. Now, there’s…
Read More

The Curse of Cursor Options

Red Skies At Night I know it’s hard to believe, but I still see a lot of people using cursors when they shouldn’t. Other times, there’s some scary dungeon part of the code that someone wrote eons ago that no one wants to go anywhere near to fix. Sometimes there’s a decent reason, something like:…
Read More

SUM, AVG, and arithmetic overflow

You Shoulda Brought A Bigger Int Sometimes you run a query, and everything goes fine. For a while. For example, if I run this query in the 2010 copy of Stack Overflow, it finishes pretty quickly, and without error. Transact-SQL SELECT u.Id, u.DisplayName, SUM(p.Score) AS SumPostScore, AVG(c.Score) AS SumCommentScore FROM dbo.Users AS u JOIN dbo.Posts…
Read More
Menu
{"cart_token":"","hash":"","cart_data":""}