Software development, .Net, SQL Server, TDD, Agile, Community and other Odds and Sods
Mitch Wheat has been working as a professional programmer since 1984, graduating with a honours degree in Mathematics from Warwick University, UK in 1986. He moved to Perth in 1995, having worked in software houses in London and Rotterdam. He has worked in the areas of mining, electronics, research, defence, financial, GIS, telecommunications, engineering, and information management. Mitch has worked mainly with Microsoft technologies (since Windows version 3.0) but has also used UNIX. He holds the following Microsoft certifications: MCPD (Web and Windows) using C# and SQL Server MCITP (Admin and Developer). His preferred development environment is C#, .Net Framework and SQL Server. Mitch has worked as an independent consultant for the last 10 years, and is currently involved with helping teams improve their Software Development Life Cycle. His areas of special interest lie in performance tuning
Wednesday, August 20, 2008
Determining Poorly Performing Queries for Tuning from SQL Server Workload Trace Files
Whenever you gather workload traces to identify poorly performing queries, you need to import this data into a database table, and to "normalise" and aggregate this information to identify the worst offenders. This can be done in a variety of ways. One way is to define a regular expression such as this SQL CLR method based on work done by Itzik Ben-Gan and modified by Adam Machanic:
[Microsoft.SqlServer.Server.SqlFunction(IsDeterministic = true)]
public static SqlString sqlsig(SqlString querystring)
@"([\s,(=<>!](?![^\]]+[\]]))(?:(?:(?:(?:(?# expression coming
)(?:[-+]?(?:(?:[\d]*\.[\d]*[\d]+)(?# precise number
)(?:[eE]?[\d]*)))(?# imprecise number
Recently I’ve been trying out ClearTrace, a free tool based around Read80Trace (described and downloadable here). Read80Trace was originally part of a Microsoft PSS engineer’s internal toolkit, but was released to the public in Dec 2007 (RML Utilities for SQL Server). ClearTrace is extremely simple to use, imports files (including rollover) very quickly and the results are good. The project is being supported so if you find a SQL statement that isn’t normalised/parameterised correctly, you can click a button and report it.
The larger RML Utilities toolkit for Microsoft SQL Server was released here.
If you decide to install and experiment with the RML Utilities toolkit, be warned that the tools are provided as is, and the install process is neither easy nor particularly pleasant!
MSN, Email: mitch døt wheat at gmail.com