Analyzing Performance Monitor Data With SQL Server Profiler

A really cool feature in SQL Server Profiler is the ability to import performance monitor (perfmon.exe) logs.  Prior to this, DBAs used to have to jump through hoops working w/ tables, excel, and whatever else we had on hand to determine exactly what database activities are causing the resource spikes.

1) Create a Counter Log.   Add only relevant counters, and remember that there is a resource overhead to any type of monitoring activity.  Also remember to set the interval to a number that will generate a manageable, but useful amount of data.

2)  Start a Profiler trace with a time span that overlaps that of the Counter Log.  Again, trace only those events that are relevant, to keep overhead as light as possible (Management Studio –> Tools Menu –> SQL Server Profiler, then File –> New Trace).

3) After your  Counter Log and Profiler trace have accumulated enough data for your needs (a single query’s worth, or a month’s worth, depending on your goal), stop both and make sure they are saved.  Then go to Profiler, File–> Import Performance Data…

You can filter down both your Perfmon counters and your SQL Server columns, to pare down what you are analyzing.  Also, we can highlight a certain range on the graph to localize the trace to that time span.

Index Rebuild versus Reorganize: What’s the difference?

In a nutshell, you should rebuild when an index is over 30% fragmented and reorganize when an index is between 10% and 30% fragmented. If fragmentation is below 10%, you can probably just leave that index alone. Rebuilding takes more server resources (and uses locks unless you use the ONLINE option available in 2005 Enterprise and Development editions), so reorg when possible. Syntax below to rebuild or reorg all indexes on a table named tTable follows…

REBUILD

ALTER INDEX ALL ON dbo.tTable REBUILD
GO

REORGANIZE

ALTER INDEX ALL ON dbo.tTable REORGANIZE
GO

SQL UPSERT

What is an UPSERT?

An UPSERT is a technique to perform inserts/updates in a database using the least amount of database reads. For maximum performance, you want to absolutely minimize the number of reads required of the database.

An UPSERT is really just an UPDATE combined with an INSERT. The classic example, is if you are writing a stored proc to look for a row and update it; if it does not exist, then insert. Most programmers will use an IF, EXISTS syntax to accomplish this. This technique will perform two I/O operations regardless of the existence of the row: one to see if the row exists, and a second to either update or insert. A basic UPSERT syntax is:
———————————–
UPDATE dbo.tObject
SET objectStatusCode = ‘Active’
WHERE objectNumber = 100

IF @@ROWCOUNT = 0

INSERT INTO dbo.tObject
(objectNumber,
objectName)
VALUES
(100,
‘Test Object’)
GO
–You can run the following before and after the statement to view I/O stats:
SET STATISTICS IO ON
–Statement here
SET STATISTICS IO OFF

———————————–

If an INSERT is required, then we will have 2 I/O operations, but if only an UPDATE is required, it would only be 1 I/O operation. The alternative with an IF, EXISTS syntax is to always have 2 I/O’s.