The Skinny on Statistics

Bad or missing statistics can lead to poor choices by the query optimizer. Statistics are created when an index is created, using a full scan of the data, and table statistics are created when a column is referenced in a filtering clause such as WHERE, HAVING, JOIN, etc.

Statistics are updated based on the following formula (if set to auto-update):

* When a table with no rows gets a row
* When 500 rows are changed to a table that is less than 500 rows
* When 20% + 500 are changed in a table greater than 500 rows

DBCC SHOW_STATISTICS displays information about the statistic in question:

Automatic statistics updates work well in most situations, except when you have outlier tables in your database that are irregularly updated and may not trigger a statistics update. In these cases, manual updates will be necessary. There are two ways to update statistics:

sp_updatestats – will update based on a random data sampling; less accurate than those built with a full scan.
UPDATE STATISTICS WITH FULL SCAN – performs a full scan of data. Localized to a single table, index, or set of stats. The WITH FULL SCAN portion is optimal.

Index reorganizations do not modify statistics.

How do I know when to update stats?
By tracking the behavior of your queries to determine when the optimizer begins making suboptimal decisions.- not always an easy task.

The NORECOMPUTE option when creating or updating statistics prevents them from being automatically updated. STATISTICS_NORECOMPUTE option can be used when you create an index.

max worker threads Options

What does it do?

The max worker threads option controls the number of worker threads available to SQL Server.  One thread typically handles one connection, and it used to handle a batch of SQL statements as the arrive from the client.

Viewing current settings:

Run EXEC sp_configure, and look at the ‘max worker threads’ option.  If you only see a limited list of options, you need to enable the ‘show advanced option’ setting, followed by RECONFIGURE.

EXEC sp_configure ‘show advanced option’, ’1′;
RECONFIGURE;
EXEC sp_configure;

As long as the number of connections is less than or equal to the number of threads available, each connection will get a single thread.  After that,  SQL Server will create a pool of worker threads to handle a larger number of clients.

Suggested settings:

For a 32 bit system, Microsoft recommends:

<= 4 processors = 256 max worker threads
8 processors = 288 max worker threads
16 processors = 352 max worker threads
32 processors = 480 max worker threads

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.

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.

Indexes – to Rebuild or Reorganize?

Index maintenance is one of the best “bang for the buck” performance tuning activities a DBA can perform. Due to factors such as page splits and updates to index and branch leaf pages, index become fragmented, resulting the in index data no longer being physically contiguous. Looking at our available options as far as index maintenance goes, there may be some confusion as whether we should be rebuilding our indexes, or performing an index reorganization. It really depends on their degree of fragmentation.

Microsoft recommends rebuilding if fragmentation is over 30%, reorganizing if it’s between 5% and 30% and leaving it alone if fragmentation is below 5%. In SQL Server 2005, you can use the sys.dm_db_index_physical_stats function to view the fragmentation of an index.

This will get detail for all tables in your DB, returning the three key fields first and ordering by most fragmented:

—————————————————————————————-

SELECT OBJECT_NAME(i.object_id) AS [Table Name],
i.name AS [Index Name],
dm.avg_fragmentation_in_percent, *
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, ‘DETAILED’) dm
INNER JOIN sys.indexes i ON i.object_id = dm.object_id
AND i.index_id = dm.index_id
order by avg_fragmentation_in_percent desc

—————————————————————————————-

To reorganize: ALTER INDEX REORGANIZE –reorgs and repacks the leaf pages of the index; this makes DBCC INDEX DEFRAG obsolete.

To rebuild: ALTER INDEX REBUILD WITH (ONLINE = ON) –drops the index and recreates it; this makes
DBCC DBREINDEX and DBCC INDEX REBUILD obsolete.

Rebuild syntax: ALTER INDEX PKtTable1
ON dbo.tTable1
REBUILD
GO

The (ONLINE = ON) operator allows the index to be rebuilt while the underlying data is accessed by other users (new to SQL 2005).

The following script will rebuild indexes for all tables in your database:

—————————————————————————————-

DECLARE @Database VARCHAR(255)
DECLARE @Table VARCHAR(255)
DECLARE @cmd NVARCHAR(500)
DECLARE @fillfactor INT

SET @fillfactor = 90

DECLARE DatabaseCursor CURSOR FOR
SELECT name FROM master.dbo.sysdatabases
WHERE name = ‘YourDatabaseName’
ORDER BY 1

OPEN DatabaseCursor

FETCH NEXT FROM DatabaseCursor INTO @Database
WHILE @@FETCH_STATUS = 0
BEGIN

SET @cmd = ‘DECLARE TableCursor CURSOR FOR SELECT table_catalog + ”.” + table_schema + ”.” + table_name as tableName
FROM ‘ + @Database + ‘.INFORMATION_SCHEMA.TABLES WHERE table_type = ”BASE TABLE”’

– create table cursor
EXEC (@cmd)
OPEN TableCursor

FETCH NEXT FROM TableCursor INTO @Table
WHILE @@FETCH_STATUS = 0
BEGIN

– SQL 2000 command
–DBCC DBREINDEX(@Table,’ ‘,@fillfactor)

– SQL 2005 command
SET @cmd = ‘ALTER INDEX ALL ON ‘ + @Table + ‘ REBUILD WITH (FILLFACTOR = ‘ + CONVERT(VARCHAR(3),@fillfactor) + ‘)’
EXEC (@cmd)

FETCH NEXT FROM TableCursor INTO @Table
END

CLOSE TableCursor
DEALLOCATE TableCursor

FETCH NEXT FROM DatabaseCursor INTO @Database
END
CLOSE DatabaseCursor
DEALLOCATE DatabaseCursor

—————————————————————————————-

Now, you may notice that even after rebuilding your indexes, many still show a large percentage of fragmentation. Hint: check out the page_count column of sys.dm_db_index_physical_stats. If it is a small number, SQL Server may have just skipped over this index. This is fine, as fragmentation in small tables/indexes has a minimal hindrance on performance.