Quantitative Database Performance, Modeling and Analysis

 

Joe Chang, SQL Server Consultant

jchang6@yahoo.com

Note: I am still working on this site, some links are broken or work in progress

Articles

NEC Express5800/A1080a (2010-06)

Note: In previous articles, I have discussed processor and system architecture, server systems, and performance benchmarks, often mixing more than one topic. Going forward, I will consolidate each topic into separate collections:

   Performance Benchmarks  (Updated 2010-07)

   Server Systems  (Updated 2010-07)

   Systems Architecture  (Updated 2010-07)

   Processors (Architecture)  (Updated 2010-07)

Original:

Intel Xeon 5600 and 7500 series (2010-04)

Storage Configuration (2010-04)

The SQL Server Cost-Based Optimizer (2009-07)

Big Iron Revival (2009)

Execution Plan Cost Model (2008)

Storage Performance

About 64-bit

Large Query Performance

Logical IO as a Metric

IO Cost Structure

Server Hardware Sizing

HP StorageWorks 2000 sa and fc G2 Modular Storage Arrays

Quantitative Performance Analysis

Database Architecture for minimum memory fit, most efficient and highly stable execution plans

Best Primary Key, Clustered Index and minimum set of nonclustered indexes

Writing SQL to force a specific execution plan

Predicting impact of execution plan changes with data distribution change over time

Direct-Attach and SAN Storage system configuration and performance verification

NUMA system specific and high call volume tuning techniques

Load Testing

Interim Material

I have not had time to do this properly, but for now here they are.

Interim material on TPC-H

SQL Performance Tools

SQLExecStats documentation, in the process of being updated

Some time soon, I will move SQL ExecStats from .NET Framework 2.0 to 3.5. For people who are SQL Server 2005, this just means it will be necessary to install .NET 3.5. I am certain that in .NET 2.0/SQL Server 2005, the SMO libraries were 32-bit only. Now there are 64-bit versions as well. Some I will try allow reading execution plans from SQL Trace files as well.

Downloads

Later versions will be .NET 3.0 or higher
SQL Exec Stats, .NET Framework 2.0
  Build 2010-06-21 Minor stuff, will document later.
  Build 2010-05-30 Console mode, run with -c all parameters in config file must be correct.
  Build 2010-04-13 Interim build, With interim documentation. Many try-catch blocks disabled so errors are not unexpected.
  Build 2010-03-22
  Build 2010-03-14
  Build 2010-03-10
  Build 2010-02-25
  Build 2010-02-10
  Build 2010-02-03
Some new columns were added to the ShowPlans tab. I will document the changes later. The next version will move the StatementOptmLevel and StmtOptmEarlyAbortReason from the ShowPlan tab to the ExecStats tab. This data structure change will mean that the new version will not work with data files form earlier versions.

SQL Clone
  Build 2010-03-04
This is an interim update for scripting Roles. A more thorough update is due, when I get a chance.

  Build 2009-01-18

SQL System Build 2008-10-29

Trace Analysis Build 2009-01-11

Links

Joe on SQLblogs

Joe on SQL-Server-Performance

Articles on SQL-Server-Performance

Presentations from past SQL Server Connections, CMG and HP World

Execution Plan Cost Formulas

Quantitative Performance Analysis, CMG2004

Loop Merge and Hash Joins

Insert Update Delete

Large Data Operations

Parallel Execution Plans

SQL Server 2005 Performance Enhancements for Large Queries

Storage Performance, Connections Spring 2005?

Storage Configuration, NOVA 2009