databasejournal.com databasejournal.com

SQL Sam and the Too-Slow Query - Part 1 (with clues) - 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

Automated Database Schema Scripting (DMO) - Page 8

CREATE procedure dmoScriptServerCollection @serverName varchar(255), @dmoServer int, @collectionName varchar(255), @path varchar(255), @style bit = 0 as declare @dmoMethod varchar(255) declare @dmoProperty varchar(255) declare @file varchar(255) declare @hr int declare @objectCount int declare @curObjectNb int declare @objectName varchar(256) declare @osCmd varchar(255) set nocount on /* build the file name */ if @style...

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

Automated Database Schema Scripting (DMO) - Page 15

create procedure ScriptCreateDatabase @serverName varchar(255), @databaseName varchar(255) as declare @groupid varchar(20) declare @fileid varchar(20) declare @script varchar(2000) declare @cmd varchar(255) set nocount on /* hold semaphore */ delete admin..databaseFileGroupInfo delete admin..databaseFileInfo select @cmd = @serverName + '.' + @databaseName + '.dbo.sp_helpfilegroup' insert admin..databaseFileGroupInfo exec...

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 Too-Slow Query - Part 1 (with clues) - Page 2

SQL Sam and the Too-Slow Query - Part 2 Environment: SQL Server 6.5, SP4 Turn OFF 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)

SQL Sam and the Evil Twin - Part 1 Environment: SQL Server 6.5, SP4 Turn off 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...

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

SQL Sam and the Too-Slow Query - Part 1 Environment: SQL Server 6.5, SP4 Turn ON 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...

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...