Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Aug 25, 1999

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

By Steve Hontz

SQL Sam and the Too-Slow Query - Part 2

Environment: SQL Server 6.5, SP4

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 order_num='42758025'
-- 4/15/99 07:53:37.326 Duration 35.297, CPU 0.125, Reads 1558, Writes 0
"Yep," agreed Tulsa. "That one definitely exceeded the 30 second time-out. Now how do we fix it?"

"Let's find out why the query takes so long to run, first. We need to see what the optimizer did with the query." Sam opened a query window in the Enterprise Manager and pasted the query into it. He turned on the Show Query Plan, Show Stats Time, and Show Stats I/O options. Then he ran the query. This is what they saw:

The type of query is SELECT
Nested iteration
Table Scan
...table results omitted...
Table: order_details scan count 1,  logical reads: 20490,  physical reads: 0,  read ahead reads: 0
Server Execution Times:  cpu time = 0 ms.  elapsed time = 703 ms.
"Oh oh," said Sam. "The optimizer did a A CLUE!table scan, when it should have used the clustered index from the key."

"Okay, I know a table scan on a large table is generally a bad thing. But look - this time the query took less than a second," exclaimed Tulsa.

"I can explain that. But first let's A CLUE!see if the optimizer had good distribution statistics to work with." Sam ran dbcc update_statistics (order_details, order_details_PK) and got this result:

Updated              Rows        Steps       Density
-------------------- ----------- ----------- ------------------------
Apr 14 1999  2:00AM  297184      89          0.00161452
All density              Columns
------------------------ ------------------------------
0.073217                 item_num
3.36492e-006             item_num, order_num
"Well, the distribution statistics are up-to-date," sighed Tulsa. "I guess we still don't know what's wrong."

"On the contrary," announced Sam. "It's right there."

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

Review SQL Sam and the Too-Slow Query - 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.

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM