Development

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

Is Cost Threshold for Parallelism Measured in Seconds?

SQL Server automatically chooses when to divide your query’s work across multiple CPU cores. It makes that decision based on your query’s cost. To see it, let’s throw 1,000,000 tiny rows in a table: Transact-SQL CREATE TABLE dbo.Timeless(ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, Stuffing VARCHAR(20)); INSERT INTO dbo.Timeless (Stuffing) SELECT TOP 1000000 'Stuff' FROM sys.all_columns…
Read More

Skewing Parallelism For Fun And Profit

What Is Skewed Parallelism? When queries go parallel, some assumptions get made: There’s enough work to keep multiple threads busy Each thread will get an equal amount of work to do The ‘equal amount of work’ part is particularly important, because in a parallel plan, each thread gets an equal share of memory up front.…
Read More
Menu
{"cart_token":"","hash":"","cart_data":""}