Monday, May 10, 2010

Benchmarking best practices

Along with having the right tools, you also need to know what you are hoping to achieve. The makeup of your workload is critical:
# IO/sec
% read vs % write
avg read size
avg write size
% random vs sequential
% cache hit

many of these numbers can be identified from a live environment using tools such as perfmon or San tools. For new environments, some creative estimating is needed.

Once you have your estimated workload, you can begin testing against the live environment. It is critical to monitor this testing inultuple locations: computer, fc network, cache, and disk access. All these monitors should tell the same performance story. If they don't, look for caching, block size changes, filesystem/driver modifications and other similar points.

Benchmarking storage performance

One common question is, is the storage performing properly? One way to tell is to benchmark it.

Benchmarking tools:
there are lots of tools to run benchmarks, but you want a tool that will allow you to simulate you production workload as close as possible. This includes read/write mixture, %random vs sequential, block size, cache hit ratio and even wait time. To achieve this, there are a few good tools to use.
IOMeter is an open source tool that provides a lot of flexibility
IORate is a tool by EMC that gives you even more options, including adjusting the cache hit ratio

Monday, May 03, 2010

IF EXISTS in SQL

For some reason, I always forget the syntax to using IF EXISTS in SQL. Here is a posting that spells it out nicely (http://blogs.msdn.com/miah/archive/2008/02/17/sql-if-exists-update-else-insert.aspx)


SQL: If Exists Update Else Insert

This is a pretty common situation that comes up when performing database operations.  A stored procedure is called and the data needs to be updated if it already exists and inserted if it does not.  If we refer to the Books Online documentation, it gives examples that are similar to:
IF EXISTS (SELECT * FROM Table1 WHERE Column1='SomeValue')
    UPDATE Table1 SET (...) WHERE Column1='SomeValue'
ELSE
    INSERT INTO Table1 VALUES (...)
This approach does work, however it might not always be the best approach.  This will do a table/index scan for both the SELECT statement and the UPDATE statement.  In most standard approaches, the following statement will likely provide better performance.  It will only perform one table/index scan instead of the two that are performed in the previous approach.

UPDATE Table1 SET (...) WHERE Column1='SomeValue'
IF @@ROWCOUNT=0
    INSERT INTO Table1 VALUES (...)
The saved table/index scan can increase performance quite a bit as the number of rows in the targeted table grows.