QDPMA - SQL Server Performance Consultant - Joe Chang

About Joe, Contact: jchang6 @ yahoo.com


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:

Query Optimizer and Performance  formerly "The SQL Server Cost-Based Optimizer"
  Feature: kCura Relativity 2013-04, updated 2013-09
  Path to In-Memory Databases - Hekaton

Server Systems Updated 2014-02

Storage  Updated 2013-03
  New Storage Performance 2013

System Architecture  

Processor Architectures  
  Feature: Hyper-Threading performance on TPC-H queries.

Performance Benchmarks  
  2014 Q2 update.

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.

Performance Tools

ExecStats is my own SQL Server performance analysis and data collection tool. Please try it out and send feedback. SQL Exec Stats has been combined with SQL System as of 2012 Q4,

I am abandoning the specific build links with a generic that should point to the latest. Current build date is 2013-10-06 per date in the file name, and not necessarily the timestamp, which can vary by time zone.

The general link is normally for the latest build, but it will remain on the last .NET 3.5 for a while.

Download       Exec Stats (latest, .NET 3.5, 2014-07-22)

Exec Stats (3.5) 2014-10-20 First of new version, simultaneously grouping by query hash for SQL and batch for procedures.

Exec Stats (3.5) 2014-08-09 Last of the old version, grouping by query hash.

I have reverted back to .NET 3.5 because I have a client that cannot move to 4 or 4.5.

Exec Stats (4.0) 2014-07-08 1st attempt - log scale on IO

I am working on a significant overhaul, consolidating the 3 separate dm_exec_query_stat modes into a single option that displays execution statistics by Batch (sqlhandle), Statement (sqlhandle+offset) and by query hash on separate pages. For the moment, this involves processing up to 64K entries from dm_exec_query_stats, exiting when the combined plan size exceeds 300MB or there abouts. This version is probably stable enough for general use.

Below is the last build before the changes described above.

Exec Stats 2014-03-08 error handling for missing perf counter

New version - caution: watch memory usage from task manager while using this build

Documentation to follow. Feedback is desired.

My plan is to use JSON but I have not done so at this time.

SQLExecStats documentation, in the process of being updated, ok, the documentation is seriously out of date. Every time I update the documentation, I make further changes to ExecStats, obsoleting the documentation changes just made - oh well.

Sometime I will try allow reading execution plans from SQL Trace files as well. I am contemplating implementing a very basic graphical plan. I would rather not do this as SQL Sentry has a well reviewed Plan Explorer, but it would be useful.

Standalone Articles

 Climate, not my field, but I had to look into it.

 About 64-bit

 Gigabit Ethernet

 SIMD Extensions for the Database Storage Engine

SQL Performance Tools (Obsolete)

Older versions have been removed. Send me an email if older versions are desired for some reason.

Trace Analysis
  Trace Analysis Build 2012-10-26
  Trace Analysis Build 2009-01-11

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 Scripts

sp_helpindex3 is my version of extended index information.

Trace is my standard Profiler script. Be careful in collecting execution plan information, as this could have high overhead.

The New Table Spaceused Summary (2013-09)

Old version Table Spaceused Summary (2008?) reports size, indexes, and other info.
Spaceused Summary (2012-04)

Index Summary with key and include columns, size, and dmv index usage stats.

Events and Presentations

SQL Saturday 2014/15


Pending acceptance:

I might be in Italy late November, may stop by Parma. For 2015, Melbourne, Costa Rica and Lisbon are being considered.


Joe presented at SQL Saturday Oslo, 30 Aug 2014
Modern Performance 2014 Oslo

Redmond, 31 May 2014   Automating Execution Plan Analysis for Comprehensive (Whole DB?) Performance.

NOVA, 19 May 2014 Modern Performance 2014

SQL Saturday Atlanta, 3 May 2014

SQL Saturday Budapest, 1 Mar 2014
I was at SQL Saturday Boston on 29 Mar, but not presenting.

2013 Oct 6 at Silicon Valley Code Camp

Modern Performance 2013 (updated 2013-10-06, original 10-03)
Note: Modern Performance-SVCC, the SQL Performance 2013-Riyadh and the 2012 Automating Performance-Copenhagen slidedecks are all on the same topic.

2013 May at SQL Saturday Riyadh, Saudi Arabia

Storage 2013

SQL Performance 2013 (Special Topics)

2012 Oct 2 & 3 at SQL Rally Nordic, Copenhagen

Automating Performance Data Collection, Analysis etc.

Presented at SQL PASS Deutschland

I presented at Munich on 10 Jan 2013. Will put up slides soon?

SQL PASS Deutschland

SQL Server Query Optimizer Cost Formulas 2010-Dec?

2011 SQL Saturday Trinidad Presentations

Performance for Small/Medium Business

SQLBits 2010

I presented a session at SQLBits on 1 Oct 2010 in York, England. (if this 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

SQL Server 2008 Presentations

SQL Server Query Optimizer Cost Formulas

Storage Configuration, NOVA 2009

SQL Server 2005 Presentations

SQL Server 2005 Performance Enhancements for Large Queries

SQL Server 2000 Presentations from past conferences

Execution Plan Cost Formulas

Quantitative Performance Analysis, CMG2004

Loop Merge and Hash Joins

Insert Update Delete

Large Data Operations

Parallel Execution Plans

Storage Performance, Connections Spring 2005 ?


Joe on SQLblogs

Joe on SQL-Server-Performance

Solid Quality Journal

In case anyone is interested, the old frameset page is here wframeset.html. I never figured out how to code it.