SQL Sam and the Evil Twin - Part 1

Tuesday Aug 24th 1999 by Steve Hontz
Share:

Being a wanna-be writer at heart, and having a love of solving SQL Server problems, I decided to combine some of my real-life SQL Server experiences with the fictional character of SQL Sam, SQL Detective.

Environment: SQL Server 6.5, SP4
Turn ON clues

SQL Sam strolled into the war room of MegaCorp, Inc., and was immediately accosted by Fred, the head of the Do-It-All project. "Sam, I'm glad you're here," he said, sweat beading up on his brow. "We've got big problems."

"What's up?" said SQL Sam.

"As you know, we have two identical servers running the Do-It-All application, Beanie and Cecil. They're identical hardware, identical software, identical service packs, identical software configuration- exact twins. But not any more!" Fred threw his arms up in desparation. "Something has happened to Cecil. It's taking much longer to run the exact same query!"

"Hmmm... a twin gone bad," said Sam. "How do you know it's taking longer to run the query?"

"As you know, our servers process the exact same data in parallel. Beanie's keeping up, but Cecil isn't. I've played with the task priority of the application, tried stopping some other services... nothing helped. Do you think we should reinstall SQL Server? Should I get the hardware manufacturer out here? What about..."

"Hang on, hang on," said Sam. "First, let's verify what you are seeing."

"Okay," agreed Fred. "Our application does several things, but the one thing that it does most often is call the stored procedure sp_FindProducts. That procedure takes some parameters, and returns a result set of the products most likely to match the request."

"Okay," said Sam. "Let's do a SQL Trace on both machines and see what we get."

Continue with SQL Sam and the Evil Twin

Go to the solution now!

Back to SQL Sam Cases


Steve Hontz is President of The Bit Corner, Inc., a Phoenix-based consulting firm specializing in Windows NT, C++, and SQL Server development. You can reach him at steve@bitcorner.com.


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 procedure up in Enterprise Manager. It looked something like this:

CREATE PROCEDURE sp_FindProducts
@TYPE varchar(20),
@ARG1 varchar(20),
          
...detail omitted...
          
IF @TYPE = 'HOUSE'
BEGIN
SELECT * FROM products WHERE color = @ARG1
END
ELSE -- Must be of type 'BUSINESS'
BEGIN
SELECT * FROM products WHERE price < convert(money(8), @ARG1)
END

"Interesting," said Sam. "Why is the procedure constructed this way? How is ARG1 used?"

"Actually, it's pretty clever. We found that when people are looking for household products, the most important option is the color. When people are looking for business products, the most important option is the price. So, we combined the two into one procedure, passing either a color or a price as ARG1."

"Did you by any chance try this procedure on Cecil before starting the Do-It-All application?" asked Sam

"Why yes," said Fred. "I wanted to make sure that the 'BUSINESS' products were working properly. Why?"

"I think I know what the problem is now," said Sam. "Let's check."

What did SQL Sam see? Go to the solution now!

Review SQL Sam and the Evil Twin - Part 1

Back to SQL Sam Cases


Steve Hontz is President of The Bit Corner, Inc., a Phoenix-based consulting firm specializing in Windows NT, C++, and SQL Server development. You can reach him at steve@bitcorner.com.


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 might be a better choice.

In Fred's case, it's even worse. Fred essentially combined two unrelated queries into one procedure. The optimizer generates the query plan based on the first execution - the one Fred did with the product type as 'BUSINESS'. The optimizer made a query plan for each SELECT statement, based on the value of ARG1 and the index distribution statistics. The optimizer couldn't make use of an index on color for the SELECT statement that handles @TYPE='HOUSE' because the value of ARG1 was a price the first time the procedure was called. So, it opted to use a table scan for any time it had to do a HOUSE product lookup. All subsequent users of the procedure paid the price for Fred's cleverness.

Sam suggested that the procedure be split into three separate procedures, with the main one calling either a HOUSE product lookup procedure or a BUSINESS lookup procedure. Sam also mentioned that Fred should look into the WITH RECOMPILE option. If the values that Fred uses in stored procedures are widely varying, and could result in different query plans, the WITH RECOMPILE option could save time by insuring that the optimizer picks a plan that is most appropriate for the current data.

Go read more exciting SQL Sam Cases!

See the story behind the story in Behind the SQL: The Making of SQL Sam!


Steve Hontz is President of The Bit Corner, Inc., a Phoenix-based consulting firm specializing in Windows NT, C++, and SQL Server development. You can reach him at steve@bitcorner.com.


Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved