boB 'The Tool Man' Taylor

Registration for Advanced Certification Programs is easier than ever!

2012-08-09T14:48:40Z

The Advanced Certification team at Microsoft is pleased to announce a new,
streamlined registration process for individuals applying for the Microsoft
Certified Master (MCM) or Microsoft Certified Architect (MCA) training
programs.

In an ongoing effort to improve your overall registration experience, the
Advanced Certification team at Microsoft is transitioning our currently hosted
registration portal to a new system managed exclusively by our team.

If you have applied for our program and are currently in the process of
completing your application for either the Master or Architect training and/or
exam retakes through our previous site, please use the instructions provided
below to create a user account in our new registration
portal.

Once you have activated your account, our registration team will contact you
with next steps.

Instructions to create a User Account:

Access the Advanced Certification Learning Portal here
Select “External Candidate” or “Microsoft Employee” from the drop down menu located on the right side of the screen under “New User Registration”.
Complete all requested information, and select
“Submit”

If you have any questions, or if you need assistance during this transition
process, please do not hesitate to contact the registration team at advcert@microsoft.com.

Episode 2 Now Live!

2012-07-31T16:24:00Z

The second episode of the web reality show Be the Next Microsoft Employee #bethenext is now live!

Check it out here....

http://www.microsoft.com/learning/en/us/certification/bethenext.aspx

There are several other suppliemental videos here: (Two magic tricks and one interview)

The interview

http://www.youtube.com/watch?v=EX01IjM1B7Y&feature=youtu.be

Links to my magic stuff

http://borntolearn.mslearn.net/btl/b/bethenext/archive/2012/07/27/bob-taylor-making-magic-on-be-the-next.aspx

http://www.youtube.com/watch?v=Fx8v-a46Z6Q&list=PLD5747260918AE95C&index=11&feature=plpp_video

http://www.youtube.com/watch?v=Ekj0XTIEHPI&list=PLD5747260918AE95C&index=12&feature=plpp_video

Guest Judge For Be The Next Microsoft Employee

2012-07-10T18:40:33Z

I’m a guest judge on new reality show, #bethenext Microsoft Employee, for a #SQLserver challenge! Sneak-peek: aka.ms/bethenext

Tech Ed 2012 - A 20th Anniversary Celebration. Wish you were here!

2012-06-13T00:22:16Z

Tech Ed has been wonderful this year, a chance to meet up with old friends, make new old friends and talk about SQL Server.

My session DBI410 - SQL Power Tools: Enhance Your Effectiveness with Extended Events was well attended with much interest and great questions.

Thanks to those of you who attended. Please be sure an fill our you evaluations. As promised I am posting all the demo and source code from the session for your enjoyment.

Please be sure and blog or Twitter me (@SQLboBT) and let me know how your Extended Events journey is going.

boB

As announced today, SQLPie will be released to CodePlex after Tech Ed Europe.

Its been a while for a new tool...

2012-05-16T15:47:42Z

Watch for the announcement at Tech Ed and Tech Ed Europe!

The new tool will be named SQLPie.

I would love to hear thoughts on what it might be

boB

I have contributed to two new upcomgin MSPress Titles

2012-04-21T02:21:18Z

I was the technical reviewer for William Stanek's SQL Server 2012 Administrator's Pocket Consultant

I am currently the techmical reviewer fpr tje Tranining Kit for Exam 70-462 Administering Microsoft SQL Server 2012 Database. Due to an illnes I am also now an author for this title.

Be sure to check them out when they are available.

Tidying up my Twitter accounts

2012-04-21T01:04:35Z

In the past I have used one Twitter account to tweet about SQL Server, my magic and my mentalism. I am tidying up and here are the twitter accounts to follow:

@MajikbyboB - related to performances, news about my magic career

@ClscClairvoyant - realted to performances and and news about my Mentalism career

The one you will all be interested in

@SQL_MCA_MCSM - this is where I will tweet all my SQL related news

Obtaining actual Request Durations with the StorPort driver logging HotFix

2012-04-05T21:08:38Z

Here is a common scenario - you are performing a benchmark operation. You run your PerfMon file through the PAL tool (http://pal.codeplex.com).

You are seeing massive disk queuing, high latency, high privleged processor time. You talk to your SAN engineers and they tell you that all is good on the SAN - it must be an issue in SQL Server.

To the rescue:

A hotfix is available that improves the logging capabilities of the Storport.sys driver to troubleshoot poor performance issues for the disk I/O in Windows Server 2008 (http://support.microsoft.com/kb/979764/j) or Windows 2008 R2 (http://support.microsoft.com/kb/978000/en-us).

Once installed you can follow the direcdtions to turn this on and off as described in the hotfix documentation.

Howwever if you capture to an .etl file there is still the little matter of translating the binary format into something usable like this

<Data Name="MiniportExtension">0xFFFFFA806DBFE008</Data>

<Data Name="RequestDuration"> 92</Data> <-- Note this is in milliseconds!

</EventData>

After a little poking around i found documentation for TraceRpt.exe which will take the input file and output a nicely formatted XML, txt, or csv file for you.

TraceRpt <etlFilename> -of CSV

TraceRpt StorPort.etl -of XML

The XML format is simplier to scan through looking for RequestDuration threshold violations. Be sure to set a filter when you create your .etl file or the resultant XML file will be extremely large.

Hope this helps someone.

Wow - how could I forget?

2012-03-06T22:22:00Z

I was in Atlanta uyesterday speaking as part of the SQL Server Special Ops Tour - we had overflow audience. We had about 275 registered and 340 showed up!

If you have to live in Columbus, OH I will be there on March 13th and in Boston, MA on March 28th...

Check it all out at http://specialops.sqlpass.org

Plesae come by and say hi...

boB

The holidays are over - back to work!

2012-01-05T16:01:06Z

I plan on releasing the next installment of The Extended Events series on Monday so watch for that.

I finished up my work as the technical reviewer on William Stanek's book Microsoft SQL Server 2012 Pocket Consultant - the ISBN is 978-0-7356-6376-3 which will be released at the end of February.

Power Tools: Instruction Manual

2011-12-12T15:00:00Z

Just like any good tool man, whenever you get a new tool

The first thing you should do is to read the instruction manual and familiarize yourself with the general operation of the tool.

During this first installment, we will do just that.

As mentioned we are going to dive into the components that make up Extended Events.

First, what are Extended Events?

Extended Events (XEvents) has a highly scalable and highly configurable architecture that allows users to collect as much or as little information as is necessary to troubleshoot or identify a performance problem. For the .NET programmer you can think of extended events as analogous to Trace statements.

As various parts of the SQL Server executable code gets executed, the execution passes through an extended event definition. If no one is listening, it is essentially a no-op. If there is a listener, the requested information is gathered and made available to the engine for consumption.

Extended Events are based on Event Tracing for Windows (ETW). In a future version of SQL Server, SQL Tracing / Profiling will be deprecated and Extended Events will be your only SQL Server collection tool for troubleshooting and performance tuning.

There are two default Event Sessions (more on sessions later) defined by SQL Server

1. System Health - for more information please visit the CSS Engineer blog describing the System Health session.

2. Deadlock detection - yes, you will now have deadlock information available even if you have not enabled the deadlock trace flags or defined a SQL Trace with Deadlock Events.

What makes up the Extended Events System?

There are three system components defined by the SQL Server Product Group, and one that you the DBA will define (per collection actually).

Packages

Packages are defined by the SQL Server team and are containers for the various objects that make up the sets of data that can be collected.

In SQL Server 2008 there were three public (and one private) packages defined. The SecAudit package is the private package

In SQL Server 2012, the product group added four new packages and a secondary definition of an existing package. An interesting technique was used to allow the product group to extend the originally packages.

Notice below that there are two entries for sqlserver. However, since we always refer to the packages by name rather than by GUID, the new objects defined in the SQL Server 2012 sqlserver package are available to us along with the objects originally defined in the sqlserver package.

As we go through this series we will examine many of these packages, detailing when they might be useful and how to effectively use them.

Packages are defined in modules (a module being either a specific dll or an executable such as sqlserver.exe). Packages contain many different object definitions, which we will discuss in future posts.

Perhaps this picture will help...

We will examine most of these objects in the course of this series. Just remember, that the package is where these objects are defined by the product group.

Targets

So, where packages are object definitions, targets are event consumers.

Targets can write to a file, aggregate event data, or start a task that is related to the event.
Targets can process data synchronously or asynchronously.
Targets can be either file-based or in memory

If we examine the system dynamic management views, we can see what targets are defined and whether or not they are available for our use (any target with a private capabilites description is not available to us).

In a future post, we will discuss the various types of targets and how they are used to consume events.

Engine

The SQL Server Extended Events engine is a collection of services and objects that:

•Enable the definition of events.

•Enable processing event data.

•Manage Extended Events services and objects in the system.

•Maintain a list of Extended Events sessions and manage access to that list.

Sessions

As this is the user defined portion of the Extended Events system, we will be devoting many posts to this topic.

That's it for now... Next week we will start drilling into each of these components.

Power Tools: SQL Server 2012 Extended Events

2011-12-02T17:16:00Z

In this series I will examine an important tool in the DBA's toolbox - Extended Events

While Extended Events were present in SQL Server 2008 and SQL Server 2008 R2, they were not widely adopted as a DBA tool.

This was partially due to the fact that prior to SQL Server 2012, there was no UI provided in Management Studio to help ease the learning curve. (Note: for earlier versions there is a SSMS Addin on CodePlex which provides a UI).

However with SQL Server 2012 we now have a Session Wizard.

Please note: In a future version of SQL Server, SQL Profiler / SQL Trace will be removed and you will need to use Extended Events for your troubleshooting / performance tuning!

Extended Events Topic Breakdown

Extended Event Architecture
How to use Packages, Targets and Actions
Creating Extended Evetnt Sessions by using Transact-SQL
Creating Extended Event Sessions by using the New Session Wizard
Converting your SQL Trace to Extended Events
Common troubleshooting scenarios where Extended Events will make you a 'Rock Star'

I plan to release one topic per week (some topics such as architecture will require multiple weeks to cover)

If you have specific troubleshooting scenarios that you would like to see addressed, please leave a comment and I will try to include them.

All Transact-SQL scripts will be available for download at the end of the series.

The Tool Man brings you Power Tools...

2011-11-28T16:03:54Z

Starting on December 1st 2011, I will be publishing a series of blog posts that are deep dives into the 'Power Tools' that ship with SQL Server 2012.

The first series will focus on Extended Events (XEvents) which allows the DBA to perform tasks, troubleshoot problems, and understand the internal workings of SQL Server like never before.

This multi-part series will introduce the architecture of XEvents, how all the pieces of the puzzle fit together, and will end with Event Session scripts to solve many common problems.

See you on the first!

Compression Estimator to be featured in the September issue of SQL Server Magazine / back from Holiday

2011-08-22T15:35:46Z

Kevin Kline of Quest Software writes a regular column titled 'Tool Time' (how is that for synergy). In the September issue he highlights my Compression Estimator.

My wife and I spent almost three weeks touring Ireland and Scotland for our 20th anniversary.

Now back on the job, and looking forward to producing some new tools.

Any requests?

Compression Estimator Version 1.0.3.0 fixes a script generation bug

2011-05-23T17:52:00Z

Thanks to CodePlex member masteroz who pointed out conidtions where the resulting SQL script generation resulted in syntax errors.

It's all fixed now and available in V1.0.3.0 on http://ssce.codeplex.com

boB

SQL Diag Configuration Tool Version 1.0.1.0 has been released

2011-05-20T19:24:35Z

This release fixes a potentially serious bug which could produce mal-formed XML. Please get the latest version from CodePlex - http://sdct.codeplex.com

If you are interested in the gory details follow along.

I use a DataSet object to read the xml file by using the DataSet.ReadXml method. This makes it easy to map the hierarchical structure of the Perfmon and Profiler nodes to the TreeView control.

The problem could be demonstrated by loading a configuration such as SQL Server 2008 R2 and then navigating to the Profiler Options pane. Once enabled, if you selected a template from the combobox that had a different number of child records than the main configuration file, I incorrectly merged their contaents.

This led to the incorrectly formed XML in the output.

In order to solve this I had to implement a full match /merge operation which would not only get the new default values from the template, but in the case where there were new rows I had to add them to the collection.

Thank you for the feedback from the Microsoft Premier Field Engineers in Portugal and Germany who helped discover this bug.

Compression Estimator V1.0.2.0 is now available

2011-05-18T01:58:49Z

Thanks to feedback from you, I have released version 1.0.2.0 of Compression Estimator. This is really a minor release.

I was filtering databases in the Database drop-down list based on compatability level. this is not necessary, since even databases in 9.0 compatability level can take advantage of compression.

So instead, when I list available SQL Servers, or you enter a Server name, I validate that it is at least a SQL Server 2008 or later version (compression was introducted in SQL 2008).

Thanks again for the feedback.

Till next time...

Compression Estimator V1.0.1.0 Now Available

2011-05-13T14:33:28Z

Thanks to all for your feedback. Version 1.0.1.0 is now released and it contains the following changes:

The Connect To SQL Server dialog now supports typing in a server instance name. You can also select Browse for more to do the original network search for available SQL Servers
The Connect To SQL Server dialog now supports setting the Connection Timeout as well as the Execution Timeout
The results form has been reduced in size (1024x768) and scroll-bars appear as appropriate. This will fix reported issues of not being able to view the entire results pane.

See screenshots below

(as always you can download the latest version from http://ssce.codeplex.com)

My apologies

2011-05-09T17:53:03Z

I had missed a checkbox on CodePlex which prevented the download from being visible. It has been corrected.

boB

Now Available! A smaller, leaner database

2011-05-09T15:55:00Z

As promised, I have released my SQL Server Compression Estimator tool to http://ssce.codeplex.com/

One of the most exciting additions to SQL Server 2008 was the capability to apply data compression. The benefits are many including reduced disk I/O and memory utilization, with a small cost of CPU overhead.

In a database that may contain thousands of objects, determining which objects and / or partitions of those objects may benefit from data compression can be time consuming and labor intensive.

Not anymore! SQL Server Compression Estimator (SSCE) will allow you to estimate the best compression for your objects. It will estimate both ROW and PAGE compression for all objects, and will optionally report the Index Maintenance Ratio (a ratio that indicates how often an index is updated versus how often it is used to satisfy queries). You can then use that information to perhaps choose a less aggressive compression algorithm for heavily updated indexes.

For more information on the benefits of data compression please visit the SQL Customer Advisory Team site and view some in depth information regarding data compression

Data Compression: Strategy, Capacity Planning and Best Practices - http://sqlcat.com/top10lists/archive/2009/01/30/top-10-sql-server-2008-features-for-the-database-administrator-dba.aspx

Top 10 SQL Server 2008 Features for the Database Administrator (DBA) - http://sqlcat.com/top10lists/archive/2009/01/30/top-10-sql-server-2008-features-for-the-database-administrator-dba.aspx

PLEASE NOTE: This tool leverages the SQL Managment Objects to build the list of available SQL Servers. If you have named instances, ensure that a SQL Server Browser service is running.

If you have the SQL Server Browser service running, or you are executing this tool on a default instance you should see the following Authentication request

Once you have provided authentication information you will be presented with the main screen where you should

Select the database to process
Decide the percentage of compression you would like, in order for an option to be displayed in the resuits (i.e. if you select 75, only compression that would result in a 75% savings will be displayed)
Decide if you want Index Maintenance Ratios to be calculated

When the processing is complete (and this could take awhile in a database with a large number of objects) you will be presented with the results

You then have two options for saving the results of the analysis

Save Results will save the entire contents of the results window to a comma separated value (CSV) file
Create Script will create a Transact-SQL script that contains the appropriate syntax to implment those items selected

Well that's it for now...

Until next time this is

boB 'The Tool Man' Taylor

Shifting to UTC time by using the datetimeoffset data type

2011-05-09T14:11:00Z

As the world moves to a global economy, it becomes more and more important to capture time information with respect to Coordinated Universal Time (UTC). This format captures the date and time along with a time zone offset (offset values range from -14:00 to +14:00).

Many implementations prior to SQLServer 2008 do not have that information (The new variable precision datetime, datetimeoffset, DATE, and TIME data types were introduced in SQL Server 2008)..

As a request from one of our Independent Software Vendors (ISV) I created a Transact-SQL user-defined function to allow the conversion from the datetime data type to the datetimeoffset data type.

To make this conversion we must find the difference between UTC time and the current server time. We can then find the difference in hours and minutes (some daylight savings time rules allow for 15 minute increments) and append this information to the original datetime data. Please note that this is a point in time snapshot. Timezone information is subject to rules in each locality, and these rules can and do change on a irregular basis. This technique will not allow for historical look-back to calculate the daylight savings time rules.

So for example in March 2005 the United States daylight savings time rule changed. So a date prior to that (say March 2004) would only have the current rule applied by this technique. To solve the larger problem of historical accuracy of time zone information would require a database of all historical changes and is beyond the scope of this example.

I will outline the basic steps to do so, and will provide a complete sample for you to download.

CREATE FUNCTION [dbo].[ConvertToTZOffset](@current DATETIME)

RETURNS VARCHAR (34)

AS

BEGIN

    -- if the input has full precision we can emit up to 34 characters of data

    DECLARE @withTZ AS VARCHAR (34);

    DECLARE @hour AS INT;

    DECLARE @minute AS INT;



    -- calculate the difference between UTC time and current server time

    -- note: some daylight savings time rules are incremented in 15 minute intervals

    SET @hour = CAST (DATEPART(hh, GETUTCDATE()) - DATEPART(hh, GETDATE()) AS INT);

    SET @minute = CAST (DATEPART(mm,GETUTCDATE()) - DATEPART(mm, GETDATE()) AS INT);



    -- get the current date and time

    SET @withTZ = CONVERT (VARCHAR (34), @current, 121);



    -- format with plus /minus sign which is required by datetimeoffset

    IF @hour >= 0

        SET @withTZ += '+';

    ELSE

        SET @withTZ += '-';



    -- add leading zero, if required	

    IF ABS(@hour) < 10

        SET @withTZ += '0';

    SET @withTZ += CAST (ABS(@hour) AS VARCHAR (2));

    SET @withTZ += ':';



    -- same with minutes

    IF @minute < 10

        SET @withTZ += '0';

    SET @withTZ += CAST (@minute AS VARCHAR (2));

    RETURN @withTZ;

END

Once his function has been created, you can simply use the ALTER TABLE syntax to add a computed column that creates the datetimeoffset value by providing the function call as the default value like this:

ALTER TABLE dbo.timeZoneTest

    ADD TZDate AS dbo.ConvertToTZOffset(rowDate);

Please see the attached sample for all the details.

boB 'The Tool Man' Taylor

Coming Soon! A Smaller, Leaner Database

2011-05-05T22:30:36Z

Watch for the announcement of the soon to be released Compression Estimator Tool. This tool will simplfy the examination of a database, and the selection of objects that would benefit the most from the application of compression techniques. I expect to release this to CodePlex by early next week...

Until then!

SQL Iterators: Making the Life of a SQL Server DBA That Much Easier

2011-05-02T18:04:58Z

Many Microsoft SQL Server DBAs automate as many of their day to day processes as possible, freeing them to attend to higher impact activities. To do so, many DBAs leveraged two undocumented system stored procedures, namely sp_MSForEachDatabase and sp_MSForEachTable. The upside of using these stored procedures are tremendous. For example, in a database with 1,500 tables, creating a script to update the statistics for all 1,500 tables is time consuming and must be modified each time a table is added to the database. Enter sp_MSForEachTable. By using this undocumented system stored procedure you could create a script that looked something like this, which automatically performs the task with the current set of tables.

EXECUTE sp_MSForEachTable
N'UPDATE STATISTICS ? WITH FULLSCAN,ALL';

This is a great savings, however there is a downside that we all know. Those SQL Server system objects that are undocumented are also unsupported for user code. Hence, any scripts built upon these stored procedures could be deprecated at any time.

Knowing that DBAs want to automate these types of tasks, I decided to leverage the power of SQL CLR stored procedures to implement a custom assembly that provides equivalent functionality.

So, let’s examine the interface of these two stored procedures and then examine the SQLCLR equivalents.

[sys].[sp_MSforeachTable]

@command1 NVARCHAR (2000)

, @replacechar NCHAR (1)=N'?'

, @command2 NVARCHAR (2000)=NULL

, @command3 NVARCHAR (2000)=NULL

, @precommand NVARCHAR (2000)=NULL

, @postcommand NVARCHAR (2000)=NULL



[sys].[sp_MSforeachdb]

@command1 NVARCHAR (2000)

, @replacechar NCHAR (1)=N'?'

, @command2 NVARCHAR (2000)=NULL

, @command3 NVARCHAR (2000)=NULL

, @precommand NVARCHAR (2000)=NULL

, @postcommand NVARCHAR (2000)=NULL

@replacechar is the replacement character in your command that will be replaced by the table name as the procedure iterates over the list of all tables.
@command2 and @command3 – since this procedure has been around A LONG time there were two purposes for these commands. In earlier versions of SQL Server, there were limits to the length of characters strings. So, @command2 and @command3 could be separate commands or if they began with’ +++’ then their text would be appended to the end of @command1.
@precommand and @postcommand were the opportunity to execute singleton commands prior to and after iterating though and processing @command1 for all the tables.

SQLIterators assembly implements both of these stored procedures with slightly different interfaces. In both cases, since we can now support large character strings I do not support the ‘+++’ convention to concatenate @command1 with @command2 and @command3. Also, due to the way that ADO.NET server side context connections work, I don’t have a database context available for the sp_MSForEachTable replacement, so that must be provided as a parameter.

So that leaves us with the two following stored procedures:

[dbo].[ExecuteForEachDB]

@command1 NVARCHAR (4000)=N''               --required parameter

, @replacechar NVARCHAR (1)=N'?'            --optional parameter

, @command2 NVARCHAR (4000)=N''             --optional parameter

, @command3 NVARCHAR (4000)=N''             --optional parameter

, @precommand NVARCHAR (4000)=N''           --optional parameter

, @postcommand NVARCHAR (4000)=N''          --optional parameter

, @setnocounton BIT = true                  --optional parameter

, @messageOptions NVARCHAR (8) =N'Errora'   --optional parameter

WITH EXECUTE AS CALLER

AS EXTERNAL NAME [SqlIterators].[SqlIterators.MSForEachReplacements].[ExecuteForEachDB]

GOCREATE PROCEDURE [dbo].[ExecuteForEachTable]

@command1 NVARCHAR (4000)=N''                --required parameter

, @database NVARCHAR (128)=N''               --required parameter

, @replacechar NVARCHAR (1)=N'?'             --optional parameter

, @command2 NVARCHAR (4000)=N''              --optional parameter

, @command3 NVARCHAR (4000)=N''              --optional parameter

, @precommand NVARCHAR (4000)=N''            --optional parameter

, @postcommand NVARCHAR (4000)=N''           --optional parameter

, @setnocounton BIT = true                   --optional parameter

, @messageOptions NVARCHAR (8) =N'Errora'    --optional parameter

WITH EXECUTE AS CALLER

AS EXTERNAL NAME [SqlIterators].[SqlIterators.MSForEachReplacements].[ExecuteForEachTable]

EXECUTE master.dbo.ExecuteForEachDB

'SELECT ?, OBJECT_NAME(object_id) FROM sys.dm_db_partition_stats ; SELECT ?'

, @messageOptions = 'Progress';



exec master.dbo.ExecuteForEachTable

N'UPDATE STATISTICS ? WITH FULLSCAN,ALL'

, @database = N'AdventureWorks'

, @messageOptions = 'Errors'

Valid choices for the @messageOptions parameter are:

Option:
Errors	Only return errors
Progress	Errors, and progress messages
All	Verbose – debugging messages
Invalid	If the user passed an invalid option

So go to SQLIterators , grab the binaries and experiment. The CodePlex site will also host any user submitted scripts that DBAs care to share with the community. To be clear, please note that the assembly, full source code and documentation for this tool are available on www.CodePlex.com here: SQLIterators.

That’s all for now. Watch for my next post where we will discuss the SQL Diag Configuration Tool – a utility that makes it easy to setup the SQLDiag configuration file, without having to edit an XML file.

Until the next time,

this is boB 'The Tool Man' Taylor

Example usage:

@command1 is the command you would like to execute for each table.

The Wait is Finally Over! SQL Diag Configuration Tool released on CodePlex

2011-05-02T18:01:04Z

The wait is finally over! The SQLDiag Configuration Tool has been released. One of the most heavily used tools in the SQL Server Premier Field Engineer’s tool belt is PSSDiag. It is maintained by Microsoft’s Customer Service and Support organization (CSS) and has a very nice configuration tool which allows a PFE to configure the items to be captured when investigating performance issues – for example you may enable or disable specific SQL Server Profiler Trace Events, Windows Performance Monitor counter collection, Event Log collection and so on. Starting with SQL Server 2000 and enhanced in SQL Server 2005, 2008, and 2008 R2 a productized version of PSSDiag is shipped with every installation of SQL Server. It is named SQLDiag. The executable can be found in the following location if the user did not change the installation path: C:\Program Files\Microsoft SQL Server\100\Tools\Binn\SQLDiag.exe. This tool will perform almost all of the same collection activities as its sibling PSSDiag. However, the configuration utility that is used by PFEs to configure PSSDiag is not available for SQLDiag. In order to configure SQLDiag you must edit by hand (think notepad or xmlNotePad) which is error prone and tedious. For more information about SQLDiag please visit the SQL Server Books Online Topic – SQLDiag.

Installing and Setting up the SQLDiag Configuration Tool

So, enter SQLDiag Configuration Tool! Your one stop shop for configuring SQLDiag captures. After you install the tool and execute it, you will be presented with the following welcome screen

On this opening screen you have the option of selecting an SQLDiag Configuration file pre-configured for SQL Server 2005, 2008, or 2008 R2. Or if you have been working on a file that you want to modify you can press the Open button to select your custom file.

Each subsequent page will allow you to configure a portion of the configuration file.

The Authentication Page lets you specify Server and Instance Name and whether you want to use Windows Authentication or SQL Server Authentication

Next you specify the Machine Wide Diagnostics options, such as whether or not to capture Event Log Information and whether or not to do so at startup, shutdown, or both.

After Machine options are configured you can specify Instance specific options such as capturing blocking information and whether or not SQLDiag captures configuration data at start up, shutdown or both.

Next we select the Profiler Events that we would like to capture. Note we can support predefined Profiler configuration files as well.

Next we configure Perfmon options

We can also enable Producers and Analyzers

And finally we can save the results of our hard work….

All the source code for the SQLDiag Configuration Tool is available on CodePlex.

Until next time,

boB ‘The Toolman’ Taylor