QDPMA - SQL Server Performance Consultant - Joe Chang
About Joe, Contact: jchang6 @ yahoo.com
Joe's travel plans 2017
25 Feb Boston
18 Mar Montreal
I made this for a talk a few years ago. I liked is so much that it is now in all my slides.
Of course, that does not mean it is complete, so please send feedback via Joe on SQLblogs
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"
Scaling Project Overview 2016-12
Server Systems 2016-12 (interim)
Memory Latency and NUMA 2016-12
Processor Architectures Updated 2016-12 new die images scaled
Feature: Hyper-Threading performance on TPC-H queries.
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.
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. The general link should normally be for the latest build, but it will be the last .NET 3.5 for the time being.
Exec Stats (latest, .NET 4.00, 2017-01-16)
For the last several months, the principle mode of dm_exec_query_stats grouping was a hybrid of query hash and batch via sql_handle. The issue was that the same SQL/query hash would be assigned to the first batch encountered. The new version reintroduces batch mode, which is also a hybrid, first using the sql_handle + offset. However, any query hash for the the end is -1 is grouped by query hash. This should work for most cases, but please advise if not.
Now using Visual Studio 2017 RC. For unknown reason, VS2017 does not like config files generated by previous versions. So copy your setting from previous versions to the current config file.
Exec Stats 2017-01-16 debug flag in config file,
Exec Stats 2016-11-12 latest Gembox 3.9,
Exec Stats 2016-10-30 Some Azure fixes, more needed.
Exec Stats 2016-10-08 latest Gembox 3.9, VS15 preview 5.
Exec Stats 2016-03-03 now using dm_os_performance_counters instead of Windows API calls for SQL Server performance counters. Windows Server 2012 did not return values, and I could not find any discussion of this matter.
Exec Stats 2015-12-16 SQL Server 2008R2 RTM fix, exception handling
Exec Stats 2015-11-04 New batch mode, query hash option
The version below might work with 2005. The recent versions were meant to simultaneously group by both query_hash and sql_handle (using the first sql_handle if a query_hash has multiple sql_handles). SQL Server 2005 does not have query_hash, so I am just inputting a fake value, losing this part of the functionality.
Exec Stats 2015-10-06 Update Gembox dll
Exec Stats 2015-06-15 fix for Azure, and other circumstances when full network address (host.domain.com) is required. Also, using a hot-fix version of Gembox.
Exec Stats 2015-05-27 trap if no printer installed
Exec Stats 2015-05-25 special test build - VAS committed
Exec Stats 2015-05-20 There have been many changes since the previous build,
please provide feedback and report any bugs
There is a problem, possibly with Windows Server 2012, any version of SQL Server
The SQL Server performance counters that should have instances report back as not having any.
The Windows Performance Monitor sees the instances, so something is going on.
Exec Stats 2015-03-17 perf ctr auto print to xps
2015-03-11 partition key not lead annotated
2015-03-09 additional general DMVs
2015-03-07 minor - SQL Source + tick marks
2015-02-18 perf ctr font size in config.
2015-02-15 fixed bug in opening previously generated *.dat files.
should also be able to open previously saved perf ctr files (*.bin).
2015-02-04 now on Gembox 3.9.
2015-01-11 (.NET 4.51), fixed: Mx CPU was previously inadvertently reported also as Mx Dur.
I am hoping this fixes the problem for SQL Server 2005. The previous build had references to columns not in the 2005
DMV's, typically with regard to compressed data, but others as well.
2014-11-10 A problem has been reported with the above (and probably other recent versions against SQL Server 2005. I would appreciate assistance in the tracking down the specific SQL statement that is not supported on 2005.
2014-11-03 New version, simultaneously grouping by query hash for SQL and batch for procedures.
2014-08-09 Last of the old version, grouping by query hash.
2014-08-07 connection parks in tempdb I have reverted back to .NET 3.5 because I have a client that cannot move to 4 or 4.5.
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.
interim build intended for Windows Server 2008 R2 using the new Processor Information object instead of Processor. Has not been tested on W2K8 original yet. Later builds will report the processor frequency once issues are worked out.
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.
Climate, not my field, but I had to look into it.
SQL Performance Tools (Obsolete)
Older versions have been removed. Send me an email if older versions are desired for some reason.
Trace Analysis Build 2015-10-06
Trace Analysis Build 2015-09-01
Trace Analysis Build 2015-03-17
Trace Analysis Build 2015-02-04
Trace Analysis Build 2014-12-24
As you can see, it has been many years since I worked on SQL Clone. SQL Server 2014 SP2 now has this functionality, see
This is an interim update for scripting Roles. A more thorough update is due, when I get a chance.
Below are some helpful scripts I have built up over the years.
The first is an extension of sp_spaceused to provide table level summary.
spaceused with table level details (2013-09)
The next is spaceused at the index level
Index Summary with key and include columns, size, and dmv index usage stats.
An alternate to the index level space usage query is a system stored procedure, extending sp_helpindex.
sp_helpindex3 is my version of extended index information. It would be desired if the table and index sp_spaceused extensions could be made into system views, but apparently SQL Server does not allow modifications to system resource database anymore?
Trace is my standard Profiler script. Be careful in collecting execution plan information, as this could have high overhead.
Events and Presentations
Joe presented at SQL Saturday Oslo, 30 Aug 2014
Modern Performance 2014 Oslo
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
SQL Performance 2013 (Special Topics)
2012 Oct 2 & 3 at SQL Rally Nordic, Copenhagen
Presented at SQL PASS Deutschland
I presented at Munich on 10 Jan 2013. Will put up slides soon?
SQL Server Query Optimizer Cost Formulas 2010-Dec?
2011 SQL Saturday Trinidad Presentations
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 2005 Presentations
SQL Server 2000 Presentations from past conferences
In case anyone is interested, the old frameset page is here wframeset.html. I never figured out how to code it.