QDPMA.com - SQL Server Consultant - Joe Chang
About Joe, Contact: jchang6 at yahoo.com or jchang61 at outlook.com
Joe's travel plans 2018
I am currently in the process of restructuring the original articles advocating 1) single processor system over 2-way and up multi-processors and 2) SRAM as Main Memory. However, having looked into the DRAM, it does looked like a low latency form of DRAM demultiplexing the row and column addresses could work as well.
Not an article, just the processor die diagrams Intel processor die images (mainline desktop)
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
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:
Server Systems 2018-Feb
System Architecture Asymmetric Processor Cores 2018-02
Processor Architectures Updated 2016-12 new die images scaled
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.
Parallel Execution in SQL Server 2016 (2017-01).
Hyper-Threading performance on TPC-H queries (2013-03).
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,
Exec Stats (latest, .NET 4.61, 2018-06-23)
Exec Stats 2018-07-19 test version, console mode -c
Exec Stats 2018-06-25 minor
Exec Stats 2018-06-23 now tracking open xml handles and cursors.
Also getting procedure, function (2016) and trigger exec stats,
tbd: get trigger plans?
An alternate to the index level space usage query is a system stored procedure, extending sp_helpindex: sp_helpindex2 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?
partition level view: sp_partitions (2018-02-23)
One of the problems in SQL Server has to do with statistics sampling being based on all rows in a random sample of pages. There is a mechanism to try to correct for the errors that could happen but it does work in some cases. The most serious problems affects indexes in which the lead column is not unique. My sp_updatestats2 system procedure identifies such indexes and applies a full scan update.
sp_vas (2018-07): sp_vas for tracking virtual_address_space_reserved.
Climate, not my field, but I had to look into it.
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.