The first SQL Azure and SQL Server Performance Baseline Tool!!!

Overview

Welcome to the first Pyn Logic community release of Enzo SQL Baseline. Have you ever wondered how a SQL Statement behaves from one database to another, or from one database server to another? Enzo SQL Baseline is built on top of an API that allows you to execute performance tests against multiple databases and view the collected performance results side-by-side, within the tool or in Excel for further analysis. Enzo SQL Baseline supports SQL Server 2005 and higher, including SQL Azure. That's right!!! You can run SQL statements or stored procedures in SQL Azure and see how they perform! To tool captures Logical and Physical Reads, Duration, CPU Time, Writes and many other metrics, including the number of packets received by the Library.

Features

Run Multiple SQL Statements
You can run the same statement multiple times, and specify a wait time between each execution

Run inline SQL or RPC Calls
You can choose to run a SQL statement or make an RPC call for stored procedures.

Run statements in parallel
You can also run the same statement on multiple threads, simulating call concurrency. When running multiple threads you can also request to start each thread at slightly different times to avoid hitting the database server all at once.

Run pre, between and post Statements
You can run a prelimiary command before the test begins, run a command after each individual test and one at the very end once all tests have been run.

View Performance Results
You can view performance results in three ways:
  • By looking at the last test's results
  • By looking at multiple tests in a comparative window
  • By exporting the performance metrics into Excel for further analysis

View Errors
If errors are detected during the test execution, such as a database timeout or an invalid SQL statement, errors will be captured and made available for review in a window. You can also decide to abort a test if too many errors have been captured.

Metrics Captured

Multiple SQL Server metrics are being captured, such as CPU time in milliseconds on the database server, and the memory usage which represents the number of 8K pages loaded in memory. These metrics are taken from the sys.dmexecsessions view and are carefully extracted so that the metrics are not affected by other statements being executed on the database. You will also be able to review the performance metrics reported by the ADO.NET Library, including the number of packets being transferred. Finally a few additional metrics are being calculated on the fly, such as the estimated number of transactions per second the statement being tested is able to yield (a transaction, in this context, means the statement being run).

WARNING

While it is academically interesting to compare the performance of a SQL Server database you have in your network to SQL Azure, and view the performance characteristics of both side-by-side, you should note that SQL Azure's results will yield very different metrics that may not be directly comparable to your internal database. For example, the duration metric encapsulates both the time a statement takes to execute, and the time it takes to return all the selected data to the client. Since SQL Azure is a remote database, on which all packets are encrypted, you will notice a significant increase in the duration metric when comparing with a local instance of SQL Server. Comparing the CPU metric may be more valuable when looking at SQL Azure vs. SQL Server performance metrics.

Additional information about Enzo SQL Baseline can be found here: Pyn Logic Website.

Last edited Apr 22, 2010 at 1:53 AM by hroggero, version 4