SQL Sam and the Too-Slow Query - Part 1 (with clues)
August 25, 1999Environment: SQL Server 6.5, SP4
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 manager in charge of all the Books-R-Us customer service representatives. She had provided much of the information behind the database design for Bookworm 2, the application the agents used to handle customer's book orders.
"Yes, there were a few unexpected glitches, but the database seems to running smoothly now," said Sam.
"Thanks to you, Sam. But you know, there is still one problem that pops up every now and then," said Tulsa. "Sometimes the agents report time-out errors when they're waiting to look at the order details for a particular order number. The time-out is thirty seconds, and it doesn't happen often, but none of the agents remember it ever happening before the upgrade."
"Let's take a look," said Sam. "What's the table involved?"
"It's called 'order_details'. There- that's it," she said, pointing at the window Sam had brought up:
"It's keyed by the order number and the item number. When the agent selects a particular order number, the details window shows the individual items in the order."
"Okay, it's time to do a SQL Trace," said Sam.