QDPMA.com - Joe Chang
former SQL Server Consultant, now at Deloitte
Contact: jchang6 at yahoo.com or jchang61 at outlook.com
Categories and Feature Articles
Query Optimizer and Performance formerly "The SQL Server Cost-Based Optimizer"
Execution Plan Cost Model 2019-09
Server Systems and Architecture 2019-Oct
ExecStats (2015+) 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, (old ExecStats page)
Download Exec Stats (somewhat latest, .NET 4.6x, 2020-02-27)
There is an issue in ExecStats when handling very large datasets. Note the size of the database is not the topic. The issue has to do with the number and complexity of objects of interest: tables, indexes, statistics, stored procedures, functions, and execution plans. This may be observed when ExecStats memory usage reaches 2GB+.
Exec Stats 2020-02-27a Includes 7z.dll+
Exec Stats 2020-02-27 Tables Avg Row Sz overflow fix+
Exec Stats 2020-02-04 Sql DataReader use COMPRESS function for 2016+
Exec Stats 2019-10-07 Gembox ver 4.5, minor changes,
ExecStatsC 2019-10-07 .NET Core,
I would like to hear feedback on this.
2019-04-19 back to .NET 4.6.0 per client requirement
2019-02-26 .NET 4.7.2, contemplating .NET Core in future release
2019-02-01 GC large memory
2018-12-08 SQL source and actual plan fixes, Runtime Ctrs & WaitStats
2018-10-23 summary for connections, sessions, parallel
2018-09-24 dm_os_memory_node_access_stats , requires trace flag 842?
2018-07-23 incorporates 7zr.exe, auto-compresses to 7z
2018-07-19 test version, console mode -c
2018-06-23 now tracking open xml handles
Also getting procedure, function (2016) and trigger exec stats,
tbd: get trigger plans?
spaceused with table level details (2018-02)
sp_helpindex2 (update pending)
sp_updatestats2 system procedure identifies such indexes and applies a full scan update.
sp_vas (2018-07) for tracking virtual_address_space_reserved.
How to approach SQL Server performance
I made this for a talk a few years ago. I liked it 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
SQL Blog unfortunately seems to be dying, please send comment to emails at top.
Also on Linkedin
(these are older versions of the two above articles):
Multi-Processors Must Die Intel 10nm Delay Assessment
Mysterious Performance Symptoms in SQL Server
SRAM as Main Memory Cost Benefit Rethink Server Sizing 2017
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
Efficiently Clone Databases by Kevin Kline
Optimizing Power for SQL Server ... by Jimmy May
Logical I/Os – Not a useful performance metric? by Joe Sack
An Introduction to Cost Estimation Benjamin Nevarez
I posted this HealthCare.Gov Fiasco back in 2013.
In case anyone is interested, the old frameset page is here wframeset.html. I never figured out how to code it.