A Quantitative Analysis of SQL Server 2008 Constructs
Justin Denning
May 1, 2009
Committee
Dr. Doug Kline (chair)
Abstract
Given the large investment companies place on database servers and database management systems, it is vital to keep the database infrastructure operating at peak efficiency. One attractive option for improving efficiency is SQL query tuning as it requires no hardware investment and does not adversely impact the system as other non-hardware tuning such as adding additional indexes. Using an adaptation of the TPC-C benchmarking framework, three phrasings and their alternatives will be tested with control dependent variables of size, indexing, and distribution. The queries to be tested are Join, Subquery, Date Between, Date Part, Merge, and a custom “Upsert” query. The results of the trials will be statistically analyzed in order to provide advice to practitioners on what query phrasing is most appropriate given a specific data scenario.
FULL TEXT (pdf)