Uncategorized

Determining Poorly Performing Queries for Tuning from SQL Server Workload Trace Files

by

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)
{
    return (SqlString)Regex.Replace(
       querystring.Value,
       @"([\s,(=!](?![^\]]+[\]]))(?:(?:(?:(?:(?# expression coming
       )(?:([N])?(')(?:[^']'')*('))(?# character
       )(?:0x[\da-fA-F]*)(?# binary
       )(?:[-+]?(?:(?:[\d]*\.[\d]*[\d]+)(?# precise number
       )(?:[eE]?[\d]*)))(?# imprecise number
       )(?:[~]?[-+]?(?:[\d]+))(?# integer
       )(?:[nN][uU][lL][lL])(?# null
       ))(?:[\s]?[\+\-\*\/\%\&\\^][\s]?)?)+(?# operators
       )))",
       @"123#4");
}

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.

The RML Utilities can help you answer the following questions:

  • Which application, database or login is consuming the most resources, and which queries are responsible for that.
  • Whether there were any plan changes for a batch during the time when the trace was captured and how each of those plans performed.
  • What queries are running slower in today’s data as compared to a previous set of data.

You can also test how the system will behave with some change (different service pack or hot fix build, changing a stored procedure or function, modifying or adding indexes, and so forth) by using the provided tools to replay the trace files against another instance of SQL Server. If you capture trace during this replay you can use the tools to directly compare to the original baseline capture.

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!

All your volcano are belong to us!

by

Brian Madsen recently blogged enthusiastically about Diskeeper ‘rocking’ the virtualisation world. Did you know that the CEO of Diskeeper Corporation, Craig Jensen is an active member of the Church of Scientology? In fact, Craig attributes the success of Diskeeper Corporation to the management system created by L Ron Hubbard.

Scientology have been notoriously heavy-handed in silencing any criticism, they even got Amazon to (temporarily) take down a link to a book critical of Scientology, before free speech prevailed. Head over to xenu.net if you’re interested in finding out about some of the facts surrounding the CoS.

Premature Optimisation

by

Scott Allen has recently posted a blog entry on the pitfalls of premature optimisation; in this specific case, optimizing LINQ queries, but the advice is timeless and technology agnostic:

“The first step in optimizing any code is to take some measurements and
make sure you really have a problem.”

It’s worth reading just for the image! (Now, if only that was Donald Knuth in the devil suit…If you’re curious, check out Knuth’s story of optimising the system idle loop…).

Donald Knuth popularised a quote which is sometime attributed to him, but is actually due to Sir Tony Hoare (probably best known for the development of Quicksort), “…premature optimization is the root of all evil.” The Fallacy of Premature Optimization by Randall Hyde is worth a look over at the ACM site.

Perth .NET User Group: Agile Software Development

by

We had another great turnout at the Perth .NET User Group presentation last Thursday:

(A handful more people arrived after I took this photo)

Thanks to Dwayne Read for presenting on the topic of Agile and to everyone who attended, and helped out. Keeping to the theme of Agile software development, here are a few resources that you might be interested in:

The following books are worth reading:

Meeting Reminder: Thurs 7th Aug: Agile in a .NET Environment

by

Join us at the Perth .NET Community of Practice, August 7th to hear Dwayne Read compare two of the more dominant agile methodologies, XP and FDD, highlighting the strengths and weaknesses of both. Examples of the application of these techniques at Snowden Technologies in a .NET/TFS environment will be discussed. This presentation is aimed at highlighting the importance of shaping the process to fit the type of applications a company develops, the people/communication side of development and the technologies they utilise.

TOPIC: Agile in a .Net Environment with Dwayne Read
DATE: Thursday, August 7th, 5:30pm
VENUE: Excom, Level 2, 23 Barrack Street, Perth
COST: Free. All welcome.

More details here: http://perthdotnet.org/blogs/events/archive/2008/07/12/agile-in-a-net-environment.aspx.

There will be a door prize of a ReSharper license (courtesy of JetBrains), and a copy of the book “Practices of an Agile Developer” (courtesy of O’Reilly publishing).