databasejournal.com databasejournal.com

SQL Sam and the Too-Slow Query - Part 1 (with clues)

Environment: SQL Server 6.5, SP4 Turn OFF clues A familiar shadow fell across the anti-glare screen of SQL Sam's workstation at Books-R-Us. "Hello, Tulsa," Sam grinned, looking up. "Haven't seen you in a while..." Tulsa Jones brushed back an errant lock of platinum-blonde hair. "Well, Sam, it's been pretty busy around here since we installed the new Bookworm 2 software upgrade," she said. Tulsa was the...

databasejournal.com databasejournal.com

SQL Sam and the Evil Twin - Part 1 - Page 3

SQL Sam and the Evil Twin - Solution! SQL Sam knew that the query optimizer generates a query plan for a stored procedure the first time it is executed- and then sticks with it. Thus, if the parameters passed in are not typical of most uses of the procedure, the query plan chosen may not be optimal. For example, with one set of parameters, the optimizer may decide that a table scan is the way to go. In another case, a clustered...

databasejournal.com databasejournal.com

SQL Sam and the Evil Twin - Part 1 - Page 2

SQL Sam and the Evil Twin - Part 2 Environment: SQL Server 6.5, SP4 Turn ON clues Sam and Fred examined the SQL Trace from both machines. "You're right, " said Sam. "On Beanie, sp_FindProducts is averaging about 50 milliseconds. On Cecil, though, the query is taking around 250 milliseconds. It's taking 5 times as long!" "See?" said Fred. "Cecil's gone bad!" "Let's take a look at your stored procedure," said Sam. Fred brought the...

databasejournal.com databasejournal.com

SQL Sam and the Phantom Connection - Part 1

Environment: SQL Server 6.5, SP5a Turn ON clues SQL Sam stood in front of the screaming crowd, with the game-show host urging him on. "Now Sam", he said, "You have to make a choice. Will it be Door Number 1, Door Number 2, or Door Number 3? Remember, behind one door is an incredible prize. Behind another door is a tiger. What's your choice?" "What's behind the door marked 'Null' ?" asked Sam. "Who knows???" shrugged the host...

databasejournal.com databasejournal.com

SQL Sam and the Too-Slow Query - Part 1 - Page 3

SQL Sam and the Too-Slow Query - Solution! "I don't see it," Tulsa admitted. "What are you looking at, Sam?" "Look at how the index is defined, Tulsa. You have a composite key on order_num and item_num, but look at the order of the column names in the index." "item_num, order_num. Both are needed to make the key unique. What's so special about the order of the columns in the key?" "Everything. Here's an example. The phone book is...

databasejournal.com databasejournal.com

SQL Sam and the Too-Slow Query - Part 1 - Page 2

SQL Sam and the Too-Slow Query - Part 2 Environment: SQL Server 6.5, SP4 Turn ON clues SQL Sam turned on SQL Trace and selected the options to include performance information and to log to a file. After letting it run for a few minutes, Sam stopped the trace. He and Tulsa then examined the trace file. "Look, here's one," said Sam, pointing at a query that took a long time: select * from order_details where...

databasejournal.com databasejournal.com

SQL Sam and the Evil Twin - Part 1 (with clues) - Page 3

SQL Sam and the Evil Twin - Solution! SQL Sam knew that the query optimizer generates a query plan for a stored procedure the first time it is executed- and then sticks with it. Thus, if the parameters passed in are not typical of most uses of the procedure, the query plan chosen may not be optimal. For example, with one set of parameters, the optimizer may decide that a table scan is the way to go. In another case, a clustered index...

databasejournal.com databasejournal.com

SQL Sam and the Evil Twin - Part 1 (with clues) - Page 2

SQL Sam and the Evil Twin - Part 2 Environment: SQL Server 6.5, SP4 Turn off clues Sam and Fred examined the SQL Trace from both machines. "You're right, " said Sam. "On Beanie, sp_FindProducts is averaging about 50 milliseconds. On Cecil, though, the query is taking around 250 milliseconds. It's taking 5 times as long!" "See?" said Fred. "Cecil's gone bad!" "Let's take a look at your...

databasejournal.com databasejournal.com

SQL Sam and the Phantom Connection - Part 1 (with clues)

Environment: SQL Server 6.5, SP5a Turn OFF clues SQL Sam stood in front of the screaming crowd, with the game-show host urging him on. "Now Sam", he said, "You have to make a choice. Will it be Door Number 1, Door Number 2, or Door Number 3? Remember, behind one door is an incredible prize. Behind another door is a tiger. What's your choice?" "What's behind the door marked 'Null' ?" asked Sam. ...

databasejournal.com databasejournal.com

The Bit Corner, Inc.

What's in a name? When I was attending Arizona State University, I had my first class in microprocessor programming. The teacher would challenge us by having us write a program that did something in the least number of instructions. He would tell us his solution "did it in 23 instructions"; extra credit was given to those who could beat it. For a select few of us, this wasn't just another college course to survive. This was our first...

databasejournal.com databasejournal.com

SQL Sam and the Never-Ending Delete - Part 1 - Page 3

SQL Sam and the Never-Ending Delete - Solution! "It's not alien abduction, psycho-kinesis, or some other unexplained phenomona?" said Nullder, sounding disappointed. "No, it's really much simpler than that," said Sam. "First, you said you were deleting about a million rows from a five-million row table. You're doing it with one DELETE, which makes it an implicit transaction that must succeed in its entirety. So, SQL Server has to log...

databasejournal.com databasejournal.com

SQL Sam and the Never-Ending Delete - Part 1 - Page 2

SQL Sam and the Never-Ending Delete - Part 2 Environment: SQL Server 6.5, SP5a Turn ON clues "Hmmm," said Sam. There's only one process in your accounts database, and it's not blocked. But there is something interesting here- the command shows up as "INSERT", not "DELETE". "Could be a bug in sp_who2," suggested Nully. "Maybe," said Sam, "but I doubt it. I think there's more going on here than meets the eye. Is it okay if we stop...

databasejournal.com databasejournal.com

SQL Sam and the Never-Ending Delete - Part 1 (with clues) - Page 3

SQL Sam and the Never-Ending Delete - Solution! "It's not alien abduction, psycho-kinesis, or some other unexplained phenomona?" said Nullder, sounding disappointed. "No, it's really much simpler than that," said Sam. "First, you said you were deleting about a million rows from a five-million row table. You're doing it with one DELETE, which makes it an implicit transaction that must succeed in its entirety. So, SQL Server has to log...

databasejournal.com databasejournal.com

Behind the SQL: The Making of SQL Sam

A lot of people have asked what goes into the making of a SQL Sam story. (Well, actually, only my wife has. And, after I started explaining it to her, her eyes glazed over and she suddenly found something else to do. But let's pretend that somebody might want to know...) Each story is based on something that really happened to me. I make a note of it, write down the details so I can reproduce it later on, and save it for one of the...