SQL Server Wait Types - Professional SQL Server 2005 Performance Tuning - page 79

The transaction log for the people database is on the C: drive on my laptop (as well as everything else) so
it's not surprising that it's slowing the workload down. I have an external hard disk connected by USB to
my laptop that I'm going to move the transaction log to see if that helps.

Run the following script to relocate the people database transaction log file, replacing H: with your

\

drive letter:

USE master;
GO
ALTER DATABASE people
MODIFY FILE(NAME = people_log,FILENAME = N'h: \ people_log.ldf')
GO
ALTER DATABASE people SET OFFLINE
GO
ALTER DATABASE people SET ONLINE

And run the workload script again:

waitType waitingTasks waitTimeMs maxWaitTimeMs signalWaitTimeMs
---------------- ------------ ---------- ------------- ----------------
WRITELOG 11796 26379 421 2761

SOS_SCHEDULER_YIELD 244447 3650 31 3556

PAGEIOLATCH_UP 1807 3432 546 0

session_id cpu_time total_elapsed_time
---------- ----------- ------------------
52 857615 965589 (15:25)

You can see that we've taken over a minute off the elapsed time and we've reduced the wait on
WRITELOG from 81 seconds down to 26 seconds. On a server with faster disks this will be much
lower again.

The next step in tuning this system would be to look at the indexing strategy.

Locking and Blocking
No chapter on waiting is complete without a discussion on locking and blocking. To understand why,
you need to understand a few core concepts: Concurrency, transactions, isolation levels, blocking locks,
and deadlocks.

Concurrency
Concurrency is the ability for multiple processes to access the same piece of data at the same time. There
are two approaches to managing this effectively: Pessimistically and optimistically. The pessimistic
approach takes the view that different processes will try to simultaneously read and write to the same
data and acquires locks to prevent conflicts from occurring. When a process reads data, a lock is placed
to prevent another process from writing to it, and when a write occurs a lock is placed to prevent another
process reading it. Thus, readers block writers and writers block readers. The optimistic approach takes
the view that it's unlikely that readers and writers will cross paths and doesn't lock the data. This means
that readers don't block writers and writers don't block readers.