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:
The SQL Server Cost-Based Optimizer (Update in progress - Parallel Execution Plans)
Performance Benchmarks Updated 2011-07
Server Systems Updated 2011-07
System Architecture Update 2011-11 Sandy-Bridge in progress
Processor Architectures Updated 2010-07
Storage Updated 2011-08
TPC-H Studies
SQL Server scaling and other characteristics are investigated using the TPC-H data generator and test queries.
The test conditions are not identical with publication requirements.
I presented a session at SQLBits on 1 Oct 2010 in York, England (apparently that where New York was named after). Anyways, I tried to squeeze 4-5 hours of material into 1 hour. So I will try to write up everything and collect it here.
1) NUMA System Architecture
2) Storage Performance for Data Warehouse
3) Parallel Execution Plans Part I,
Part II,
Part III
4) TPC-H Benchmarks,
SF100,
SF300,
SF1000,
SF3000
5) TPC-H Studies at SF10
Slidedecks: SQLBits Presentations
SIMD Extensions for the Database Storage Engine
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.
Later versions will be .NET 3.0 or higher
SQL Exec Stats, .NET Framework 2.0
This link should redirect to the latest version (1.40), which is really work-in-progress: Exec Stats (Requires .NET Framework 3.5, Build 2010-12-05 and later will require SQL Server 2008 Feature Pack - Management Objects) The more stable 1.3 version (2011-06-15) is still available below.
Build 2012-02-10 Version 1.4, work in progress
Build 2011-06-15 Last of version 1.30
Build 2010-09-01 Last version for .NET 2.0
Older versions have been removed.
Send me an email if older versions are desired for some reason.
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.
SQL System
This link should redirect to the latest version:
Sql System Monitor
SQL System Build 2012-02-12, fixed bug in connecting to cluster, file io rework
SQL System Build 2011-12-30, ??
SQL System Build 2011-04-27, ignore disks w/o partitions
Trace Analysis
Trace Analysis Build 2009-01-11
Trace is my standard Profiler script.
Be careful in collecting execution plan information,
as this could have high overhead.
Table Spaceused Summary
reports size, indexes, and other info.
Index Summary with key and include columns, size, and dmv index usage stats.
Articles on SQL-Server-Performance
Performance for Small/Medium Business
SQL Server Query Optimizer Cost Formulas
Storage Configuration, NOVA 2009
SQL Server 2005 Performance Enhancements for Large Queries