SQL-Server-Performance.Com Newsletter -- February 3, 2004

Tue Feb 3 12:20:54 PST 2004

=================================================================

**SQL-Server-Performance.Com Newsletter**

=================================================================

February 3, 2004

=================================================================

Editor:     Brad M. McGehee, MVP, MCSE+I, MCSD
E-Mail:     mailto:webmaster at sql-server-performance.com

=================================================================

URL:        http://www.sql-server-performance.com/

=================================================================

Information on how to subscribe, unsubscribe, and to
change your e-mail address is at the bottom of this newsletter.
This is a 100% opt-in newsletter.

=================================================================

=================================================================
**Sponsor's Message**
=================================================================

COMPARE AND SYNCHRONIZE SQL SERVER DATABASES

Use Red Gate's tools for SQL Server databases for all your
comparison and synchronization tasks.  Tools include:

--SQL Compare: Compare and synchronize database structures

--SQL Data Compare: Compare and synchronize data in databases

--DTS Compare: Compare SQL Server settings, jobs, logins and
DTS packages

--SQL Toolkit: Automate SQL Compare and SQL Data Compare

Visit http://www.red-gate.com/sql/summary.htm for a FREE trial,
or contact mailto:sales at red-gate.com

=================================================================

=================================================================
**In This Issue**
=================================================================

-- New Information Published on SQL-Server-Performance.Com
-- New and Updated SQL Server Performance Tips
-- January Forum Contest Winners Announced
-- Learn & Win: February Forum Contest With FREE Software

=================================================================

=================================================================
**New Information Published on SQL-Server-Performance.Com**
=================================================================

Here are some new items that were recently posted to
SQL-Server-Performance.Com:

-- New Article: Transferring SQL Server Statistics From One
Database to Another

--New for Writers: If you submit an article to SQL-Server-
Performance.Com in 2004, and it is published, you will receive a
free copy of LeadByte Software's Network Performance Suite, worth
$977

If you haven't seen these yet, check them out at:

http://www.sql-server-performance.com/

=================================================================

=================================================================
**Sponsor's Message**
=================================================================

IDENTIFY BLOCKED SQL SERVER PROCESSES THAT HINDER PERFORMANCE

Try our new Stealth Blocked Process Monitor today and know what
SQL statements and database locks are occurring!

Product pinpoints database SQL-causing blocked process conditions
resulting in degrading end-user response time and poor
performance. The product analyzes, graphs and archives the
following:

-- Blocked process tree with blocked SPIDs, SQL text, locks and
more.

-- Ranks SQL statements and applications most frequently involved
in SQL transaction blocking.

-- Reconstruction of blocking incidents with complete blocked
process tree details.

-- Multi-level e-mail and pager alerts of blocked process
conditions for mixed OLTP, data warehousing, reporting and
application environments.

Download your free trial today at http://www.sqlpower.com

Sql Power Tools is the industry recognized leader in ZERO IMPACT
database monitoring of end-user service levels, SQL performance,
top N SQL analysis, blocked process analysis, and data
warehousing end-user data usage analysis.

=================================================================

=================================================================
**SQL Server Performance Tuning and Optimization Tips**

The SQL Server performance tips listed below were recently added
or updated on the website.
=================================================================

**Blocking**

To help identify and correct queries that cause blocking locks,
you need to find out what the query looks like that is causing
the blocking lock. Before you can do this, you must first
identity the SPID that is causing the blocking. Once you have
done that, there are two ways to view the query that is causing
the blocking lock

First, from Enterprise Manager, in the "Process Info" window
located under "Current Activity," right-click on the SPID causing
the blocking and choose "Properties." This will display the
query.

Second, from Query Analyzer, enter this code to reveal the query
causing the blocking lock:

DBCC INPUTBUFFER (<spid>)

Once you have identified the query that is causing the blocking
lock, you can begin researching it to see if there is anything
you can do to modify the query to avoid blocking in the future.
[7.0, 2000]

                           *****

To help identify the type of lock that a blocking lock is
holding, you must first identify the SPID that is causing the
blocking, Once you have that, there are two ways to view the type
of lock being held by the blocking lock:

First, from Enterprise Manager, in the "Locks/Process ID" window
located under "Current Activity," locate the SPID causing the
blocking lock, and click on it. The type of lock will be
displayed in the right-hand window.

Second, run the command:

sp_lock

in Query Analyzer. You will then have to match the SPID of the
block lock to the SPID listed in the results of this command.
Knowing the type of lock held by the blocking lock can help you
figure out why the query in question is causing a blocking lock.
[7.0, 2000]

                           *****

One way to help identify blocking locks is to use the SQL Server
Profiler. The Profiler is useful for capturing blocking locks
because it can capture blocking locks over time, unlike the
Enterprise Manager, which only shows blocking locks as of the
current instant. In addition, the query that is available from
the INPUTBUFFER may not be enough information to diagnose a
blocking problem. Sometimes, queries that run just before the
query that is causing the blocking is related to the blocking
problem. By performing a Profiler Trace, you can see all the
queries and other activity that precede a blocking lock. In order
to use the trace data, you will have to know the SPID that caused
the blocking lock, and then look up the data from the trace for
this one particular SPID.

Below are two different Profiler configurations, one for SQL
Server 7.0 and one for SQL Server 2000.

SQL Server 7.0 Profiler Configuration

Events:

Error and Warnings: Exception
Misc: Attention
Misc: Execution Plan
Sessions: Connect
Sessions: Disconnect
Sessions: Exiting Connection
TSQL: RPC:Starting
TSQL: RPC:Completed
TSQL: SQL:BatchStarting
TSQL: SQL:BatchCompleted
Stored Procedures: SP:StmtStarting
Stored Procedures: SP:StmtCompleted
Transactions: SQLTransaction

Data Columns:

Group By: SPID
Event Class
Text
Integer Data
Binary Data
Application Name
NT User Name
SQL User Name
Start Time
End Time
Connection ID

Filters:

Trace Event Criteria: Severity (type 24 in the Maximum box)
Add other filters as desired to reduce a flood of too much data

SQL Server 2000 Profiler Configuration

Events:

Error and Warnings: Exception
Error and Warnings: Attention
Performance: Execution Plan
Sessions: Exiting Connection
Stored Procedures: RPC: Starting
Stored Procedures: RPC: Completed
Stored Procedures: SP: Starting
Stored Procedures: SP: Completed
Stored Procedures: SP: StmtStarting
Stored Procedures: SP: StmtCompleted
Transactions: SQLTransaction
TSQL: SQL:BatchStarting
TSQL: SQL:BatchCompleted

Data Columns

Group: SPID
EventClass
TextData
IntegerData
BinaryData
ApplicationName
NTUserName
LoginName
StartTime
EndTime

Filters:

Trace Event Criteria: Severity (type 24 in "Less than or equal"
box)
Add other filters as desired to reduce a flood of too much data

One of the best ways to use the Profiler is to begin a trace,
duplicate the activity that causes the blocking lock, identify
the SPID of the blocking lock in Enterprise Manger, and then stop
the trace. Next, look up the SPID in the trace, viewing all of
the activity that occurred up to the blocking lock occurring.
[7.0, 2000]

=================================================================
**Sponsor's Message**
=================================================================

FREE White Paper on Data Auditing Techniques from Lumigent

Auditing data on a Microsoft SQL Server database is of paramount
concern for any database professional. You may be called upon to
identify the source of data modifications, or changes in
permissions or schema and verify how changes were made. Or report
on who accessed certain tables and modified them over a period of
time. Triggers and applications modifications have known
limitations and can result in performance overhead. This white
paper outlines the shortcomings of traditional auditing
techniques and presents a comprehensive solution for monitoring
and auditing your database activity - all while avoiding
performance issues on your critical systems.

Download it now, compliments of Lumigent Technologies:

http://www.lumigent.com/go/sd15

=================================================================

**Cursors**

If you need to perform a JOIN as part of your cursor, keyset and
static cursors are generally faster than dynamic cursors, and
should be used when possible. [6.5, 7.0, 2000]

                           *****

If a transaction you have created contains a cursor (try to avoid
this if at all possible), ensure that the number of rows being
modified by the cursor is small. This is because the modified
rows may be locked until the transaction completes or aborts. The
greater the number of rows being modified, the greater the locks,
and the higher the likelihood of lock contention on the server,
hurting performance. [6.5, 7.0, 2000]

                           *****

Consider using asynchronous cursors if you expect your result set
to be very large. This allows you to continue processing while
the cursor is still being populated. While it may not actually
speed up your application, it should give the appearance to your
end users that something is happening sooner that if they have to
wait until the entire cursor is populated. [6.5, 7.0, 2000]

                           *****

If you have to use a cursor, break out of the cursor loop as soon
as you can. If you find that a problem has occurred, or
processing has ended before the full cursor has been processed,
then exit immediately. [6.5, 7.0, 2000]

                           *****

If you are using the same cursor more than once in a batch of
work, (or within more than one stored procedure), then define the
cursor as a global cursor by using the GLOBAL keyword. By not
closing or deallocating the cursor until the whole process is
finished, a fair amount of time will be saved, as the cursor and
the data contained will already be defined, ready for you to use.
[7.0, 2000]

=================================================================
**Sponsor's Message**
=================================================================

IntelliVIEW -- Interactive Reporting Tool for SQL Server

IntelliVIEW is an easy-to-use reporting solution that allows you
to create rich & interactive reports from your SQL Server
databases and integrate them into your applications. Design
interactive reports with ease; integrate reporting into .NET,
Java, and COM applications; analyze information in real-time; and
make faster, better-informed decisions.

Integrate Reporting into your SQL Server applications:

--Create virtually any type of report--summary, cross-tabs,
charts, etc.

--Manipulate data using convenient drag & drop facilities.

--Slash development time for creating reports by over 75%!

--Publish reports easily across the web.

--Export reports to popular formats like xls, pdf, rtf, etc.

--Print Professional looking reports using the WYSIWYG printing
features.

--Absolutely No Client Licensing Fees!

Download FREE client at http://www.intelliview.com/go/sqlperf

=================================================================

**Database Options**

If a database will be used for read-only purposes only, such as
being used for reporting, consider changing the "read-only"
setting to on (the default setting is off).  This will eliminate
the overhead of locking, and in turn, potentially boost the
performance of queries that are being run against it. If you need
to modify the database, you can also turn the setting off, make
your change, then turn it back on. [6.5, 7.0, 2000]

                           *****

When "auto create statistics" is turned on for a database (which
it is by default), statistics are automatically created on all
columns used in the WHERE clause of a query. This occurs when a
query is optimized by the Query Optimizer for the first time,
assuming the column doesn't already have statistics created for
it. The addition of column statistics can greatly aid the Query
Optimizer so that it can create an optimum execution plan for the
query.

If this option is turned off, then missing column statistics are
not automatically created, when can mean that the Query Optimizer
may not be able to produce the optimum execution plan, and the
query's performance may suffer. You can still manually create
column statistics if you like, even when this option is turned
off.

There is really no down-side to using this option. The very first
time that column statistics are created, there will be a short
delay as they are created before the query runs the first time,
causing the query to potentially take a little longer to run. But
once the column statistics have been created, each time the same
query runs, it should now run more efficiently than if the
statistics did not exist in the first place. [7.0, 2000]

                           *****

To provide the up-to-date statistics the query optimizer needs to
make smart query optimization decisions, you will generally want
to leave the "auto update statistics" database option on (the
default setting). This helps to ensure that the optimizer
statistics are valid, helping to ensure that queries are properly
optimized when they are run.

But this option is not a panacea. When a SQL Server database is
under very heavy load, sometimes the auto update statistics
feature can update the statistics on large tables at
inappropriate times, such as the busiest time of the day.
If you find that the auto update statistics feature is running at
inappropriate times, you may want to turn it off, and then
manually update the statistics (using UPDATE STATISTICS) when the
database is under a less heavy load.

But again, consider what will happen if you do turn off the auto
update statistics feature. While turning this feature off may
reduce some stress on your server by not running at inappropriate
times of the day, it could also cause some of your queries not to
be properly optimized, which could also put extra stress on your
server during busy times.

Like many optimization issues, you will probably need to
experiment to see if turning this option on or off is more
effective for your environment. But as a rule of thumb, if your
server is not maxed out, then leaving this option on is probably
the best decision. [7.0, 2000]

                           *****

Many databases need to be shrunk periodically in order to free up
disk space as older data is deleted from the database. But don't
be tempted to use the "auto shrink" database option, as it can
waste SQL Server resources unnecessarily.

By default, the auto_shrink option is turned off, which means
that the only way to free up empty space in a database is to do
so manually. If you turn it on, SQL Server will then check every
30 minutes to see if it needs to shrink the database. Not only
does this use up resources that could better be used elsewhere,
it also can cause unexpected bottlenecks in your database when
the auto_shrink process kicks in and does its work.

If you need to shrink databases periodically, perform this step
manually using the DBCC SHRINKDATABASE or DBCC SHRINKFILE
commands, or you can use the SQL Server Agent or create a
Database Maintenance Plan to schedule regular file shrinking.
[7.0, 2000]

                           *****

This "auto close" database option is designed for use with the
Desktop version of SQL Server 7.0 and 2000, not for the server
versions. Because of this, it should not be turned on. What this
option does is to close the database when the last database user
disconnects from the database. When a connection requests access
to the database, then the database has to be reopened, which
takes time and overhead.

The problem with this is that if the database is accessed
frequently, which is the most likely case, then the database may
have to close and reopened often, which puts a large performance
drag on SQL Server and the applications or users making the
connection. [7.0, 2000]

=================================================================
**Sponsor's Message**
=================================================================

VISUAL JOB MANAGEMENT FOR SQL SERVER IS HERE!

Introducing SQL SENTRY, a new look at job management that
brings you unprecedented scheduling, monitoring, alerting
and reporting:

SCHEDULING:
Intuitive, Outlook-style calendar view of your job schedules.
With 10-minute, hour, 4-hr, day and week views, job conflicts
are clearly highlighted and easily resolved.

MONITORING:
Link Windows performance counters directly to jobs and guard
against failures before they occur by knowing how jobs are
impacting server performance.

ALERTING:
Robust notification capabilities for a wide variety of events,
including min/max runtime, job conflicts, job queueing, and
linked performance counter threshholds.

REPORTING:
3-D runtime and performance charts provide unsurpassed
analysis and resolution capabilities for job-related issues.

For more information on SQL SENTRY, visit our website today:
http://www.sqlSentry.net/

=================================================================

=================================================================
Winners of the January 2004 Forum Contest
=================================================================

Here are the winners of the January 2004 forum contest:

--frettmaestro
--lazy_dba
--holyterror_1
--negative
--boldhead
--pavankan
--tdong
--chappy
--briant

While the following people were ineligible for prizes this month,
I also want to acknowledge them for their contributions to the
forum in January 2004:

--LuisMartin
--satya
--twan
--FrankKalis
--ChrisFretwell
--bambola
--gaurav_bindlish
--joechang
--Argyle

I want to thank everyone above for their hard work and dedication
to the SQL-Server-Performance.Com forum.

If you haven't taken the time to check out the forum, you are
missing a out on a valuable resource. Visit it today at:

http://www.sql-server-performance.com/forum

=================================================================

=================================================================
Win & Learn: FREE SOFTWARE in the February Forum Contest!
=================================================================

SQL-Server-Performance.Com, along with 9 companies (see
below) have teamed up to give away FREE SQL Server software to
each of 9 WINNERS (one prize per winner) of the February
2004 Forum Posting Contest.

Participating in the forum is not only a great way to win free
software, but to learn a lot more about how to get the most out
of SQL Server.

--ApexSQL Code Clean ($149) from ApexSQL Software
http://www.apexsql.com

--NetworkSmart 2003 ($449) from LeadByte Software
http://www.leadbyte.com

--SQL Scribe Documentation Builder ($400) from A&G Software
http://www.ag-software.com

--DbNetGrid ($599) from DBNetLink
http://www.dbnetgrid.com

--myLittleAdmin ($490) from myLittleTools.net
http://www.mylittletools.net

--SQLZip ($500) from SQLZip Software
http://www.sqlzip.com

--mssqlXpress ($199) from XpressApps
http://www.xpressapps.com

RapTier Professional ($299) from SharpPower.Com
http://www.sharppower.com

Find Duplicates Wizard for SQL Server ($397) from Azlexica
http://www.findduplicates.com

The first place winner will get the first pick of the above free
software, the second place winner will get the second pick, the
third place winner will get the third pick, and so on.

We have also changed the rules for the forum contest, making it
even easier for participants to win.

To find out about this contest, and how you can participate,
please visit this webpage:

http://www.sql-server-performance.com/

=================================================================

=================================================================

There are only three ways you could have received this e-mail,
and that is to have subscribed to it, joined our forum, or to
have received it from a friend who forwarded it to you. This is a
100% opt-in newsletter.

To learn how to advertise in this publication, visit:
http://www.sql-server-performance.com/sponsor_information.asp

To subscribe to this newsletter, visit:
http://www.sql-server-performance.com/subscribe_newsletter.asp

To unsubscribe to this newsletter, or to change your e-mail
address, click on this URL.

=================================================================

No part of this newsletter may be reproduced in whole or in part
without written permission.

=================================================================