Tuning Indexes - Professional SQL Server 2005 Performance Tuning - page 371

begin
select spid, kpid, blocked, waittime, lastwaittype, waitresource
from master..sysprocesses
where program_name = 'SQLCMD'
waitfor delay '00:00:00.05'
end

These are the results of the query (cleaned up to save space):

spid kpid blocked waittime lastwaittype

55 3336 0 0 WRITELOG

55 3336 0 0 WRITELOG

55 3336 0 0 WRITELOG

55 3336 0 0 WRITELOG

Not surprisingly, on such a simple insert on a very basic slow disk, most of the time is spent waiting on
the log write. What the information here has told you is that most of the stats are meaningless except
for the raw write rate that comes out of the usp_peopleInsert stored procedure.

One final check before going on to DTA is to take a look at the output of SHOWPLAN_TEXT to see what the
query plan looks like. You can then compare this with the query plan after applying any recommenda-
tions from DTA and see how it changes.

SET SHOWPLAN_TEXT ON;
GO
exec usp_peopleInsert
go
SET SHOWPLAN_TEXT OFF;
GO

I find it easier to read the output for SHOWPLAN_TEXT in text mode rather than the default grid mode. To
do this go to the Query menu in Management Studio select Results To Results to text. The output is

too verbose to include here in full. The key elements of interest of the plan are shown here:

➪

|--Table Scan(OBJECT:([People].[dbo].[BoysNames]))
|--Table Scan(OBJECT:([People].[dbo].[GirlsNames]))
|--Table Scan(OBJECT:([People].[dbo].[lastNames]))

This shows that you are using a table scan to get the names from the lookup tables. In most cases this
works just fine as the tables are so small (boysNames and girlsNames), but this isn't so optimal on last-
Names, where the table has 2000 rows and occupies 7 or 8 database pages.

Now see what DTA recommends for you. Running DTA against the sample query is very simple. Go to
the tools menu and select Database Engine Tuning Advisor. This brings up the Database Engine Tuning
Advisor, shown in Figure 11-3.

First you have to load the DTApeopleInsert.sql script as the workload. Next change the database for
workload analysis from master to people . Last of all, select which database you want to tune by selecting
the people database. Now you are ready to start the analysis session by clicking the Start Analysis button
at the left end of the toolbar. When you start the analysis session, DTA adds a new Progress tab and
updates its analysis progress as shown in Figure 11-4.