The agenda for SQL Saturday Scotland is now published

It’s a great line out and I am looking forward to having a wonderful conference.

Don't forget after SQL Saturday I will be doing the Edinburgh Moonwalk in aid of *** Cancer charities. To sponsor me go to

http://www.walkthewalkfundraising.org/martin_bell

Here are the slides and scripts from Andrew Whettam’s presentation to the usergroup on Tuesday 7 May. Here is the presentation and the example scripts can be found here.

The presentation by Aaron Bertrand can be found on the SQLBits website here.

People wishing to support my Full Moon walk for BreastCancer after SQL Saturday Edinburgh can sponsor me here

I found out there was a Walk the Walk Moon Walk following SQL Saturday Edinburgh on June 8th. So after 2 days of conference organising, I will donning a bra to join the ladies for the full moon walk. To see why I’m doing this read my fund raising blog http://www.walkthewalkfundraising.org/martin_bell

This year SQL Relay will be taking place between the 17th and 27th June at 8 venues throughout the country from Glasgow to Southampton.

Glasgow - Monday 17th June
Leeds - Tuesday 18th June
Birmingham - Wednesday 19th June
Norwich – Thursday 20th June

Cardiff – Monday 24th June
Southampton – Tuesday 25th June
Reading – Wednesday 26th June
London - Thursday 27th June

If you are interested in attending these events, register an interest at http://sqlrelay.co.uk/

The countdown to SQL Saturday #202 Edinburgh has started!

Jen Stirrup with help from Allan Mitchell and myself are organising a SQL Saturday event at the University of Edinburgh on 8th June with a Pre-con day on the preceding Friday. Today the call for speakers was issued, anyone interested in speaking can register at http://bit.ly/RZw7ir. Sponsors can also register at http://bit.ly/LdHIjD

Last week we had a great user group meeting  with talks from Alex Whittles and Andrew Whettam. The slide deck and examples from Andrew’s talk can be found here.

Details of Alex’s presentation on Slowly Changing Dimensions and SSIS can be found here.

Many thanks to Alex and Andrew for putting on two great talks.

As well as introducing some new performance counters SQL Server 2012 has also deprecated a few. To see what has changed I compared the values in sys.dm_os_performance_counters for a SQL Server 2008 R2 installation and a SQL Server 2012 RTM version. The perfmon counters no longer in SQL Server 2012 are:

New counters are:

I have incorporated these changes into the SQLDiag templates mentioned in my previous post.

The other week I wrote a post about Changing Server Side Traces and commented on the Default Profiler Trace template having unused variables which I have raised a a bug on connect. I checked this again and was very disappointed to find they had already closed it as “won’t fix”. I know SQL Trace is being deprecated so there is limited life for it, but if this was my code I would have corrected it just because of the embarrassment and the pride I have in producing quality code. Well that is not quite true; if that was my code I would hope it wouldn’t make version control in that form.

I have subsequently taken the two minutes to correct the issue and attached a new Default Profiler Trace template to the connect item. You can find it here. When you first open Template Explorer the template files are put into your

C:\Users\<UserName>\AppData\Roaming\Microsoft\Microsoft SQL Server\100\Tools\Shell\Templates\Sql\SQL Trace

directory. If your replace the existing “Default Profiler Trace.sql” file with the attached version, you can then use it from Template Explorer.

We’ve had a great week at SQL Relay so far I have organised one event and helped run a second and spoken at 2 others. I still have presentations at the final legs in London to go and I'm looking forward to them. As part of my presentation I talk about running PAL with SQL Server 2012. The SQL 2005/2008 threshold file can be used against a server running SQL Server 2012, so your Performance Data Collection Set that is imported from the PAL threshold file is still ok.

If you don’t like using logman to start your Performance Counter collection (real DBA don’t use the Perfmon GUI ), then you may look towards SQLDiag to collect your SQL Trace and Performance Counter data. Anyone brave enough to hand craft their own SQLDiag configuration files are hopefully already seeking counselling. I use the SQLDiag Configuration Tool (SDCT) available on codeplex. Unfortunately the last time the project was updated was 20 May 2011 and does not list SQL Server 2012 as a supported option. Luckily the only issue I have found with the generated configuration files is the need to change the SQL Server version number for (say) 10.50 to 11. i.e. the line like:

<Instance name="*" windowsauth="true" ssver="10.50" user="">

changes to:

<Instance name="*" windowsauth="true" ssver="11" user="">

PAL will collect around 71 performance counters for a single instance of SQL Server. Configuring these with SDCT is still quite a tedious job, so I created a template which includes all the counters in the PAL Threshold file. A copy of a template for SQL Server 2008 _ss10_PAL.xml can be found here. Copy this file into the appropriate perform template directory e.g. C:\Program Files (x86)\Taylor Made Enterprises\SQLDiag Configuration Tool\PerfmonTemplates\10 and the file will appear in the list of templates available in the drop down box.



Unfortunately I don’t know of a way to add additional buttons to the front screen, but you can change the underlying configuration file e.g. SQLDiag1050.XML for SQL Server 2008 R2 in C:\Program Files (x86)\Taylor Made Enterprises\SQLDiag Configuration Tool\Configurations. If you wish to generate a SQL Server 2012 configuration files without having edit the configuration file afterwards you will need to replace one of the existing configuration files. A default SQL Server 2012 configuration file can be found here, this contains all the new Perfmon counters for SQL Server 2012.

Because this is a new version you will need to create two template directories e.g.:
      
C:\Program Files (x86)\Taylor Made Enterprises\SQLDiag Configuration Tool\PerfmonTemplates\11
C:\Program Files (x86)\Taylor Made Enterprises\SQLDiag Configuration Tool\TraceTemplates\11

You may just want to copy the corresponding …\10 directories. A SQL Server 2012 specific Perfmon template (_ss11_PAL.xml) can be found here.

If you get the following error dialog, then you need to create the appropriate template directories:



This configuration will also collect the SQL Trace completion events which are useful when aggregating information from trace files when pinpointing poorly performing code.



It's interesting that the new configuration file is recognised as being for SQL Server Denali.

The profiler template (_Performance_Aggregation.xml) to collect this information can be found here place it in the appropriate TraceTemplates directory.

Although SQL Trace is marked started it’s deprecation stage, it will be around for quite some time to come. Therefore I thought I would share a couple of changes I make to server side traces  when I use them. (It also means I will always have somewhere to find the code without writing it from scratch!)

In the past when you scripted a service side trace in SQL Profiler, the script generated would contain a comment containing the statements required to stop and close the trace.

This may have been removed because you needed to change the script and put in the correct TraceID parameters, therefore limiting the value of the suggested code. If it fits your circumstances (i.e. no other Server Side traces being run on the system) you could use the following generic code to stop the most recent trace.~

DECLARE @TraceID int ;
SET @TraceID  = ( SELECT MAX(traceid)/
FROM sys.fn_trace_getinfo(0) )  ;
SELECT @TraceID ;
IF @TraceID IS NOT NULL
BEGIN
    EXEC sp_trace_setstatus @TraceID, 0
    EXEC sp_trace_setstatus @TraceID, 2
END ;
GO

If you are using the Default Trace then you may get the following message if you try to stop it.

Msg 19070, Level 16, State 1, Procedure sp_trace_setstatus, Line 1
The default trace cannot be stopped or modified. Use SP_CONFIGURE to turn it off.

Therefore you may want to amend the above script to avoid the trace with a TraceID of 1.

DECLARE @TraceID int ;
SET @TraceID  = ( SELECT MAX(traceid)
FROM sys.fn_trace_getinfo(DEFAULT)
WHERE traceid > 1 ) ;
SELECT @TraceID ;
IF @TraceID IS NOT NULL
BEGIN
    EXEC sp_trace_setstatus @TraceID, 0 ;
    EXEC sp_trace_setstatus @TraceID, 2 ;
END ;
GO       

An alternate method to identify the Trace to stop could be to identify a unique part of the filename. Filename is property 2 returned by fn_trace_getinfo.

DECLARE @TraceID int ;
SET @TraceID = ( SELECT traceid
FROM sys.fn_trace_getinfo(DEFAULT)
WHERE Property = 2
AND CAST(VALUE as varchar(max)) LIKE 'T:\TraceFiles\Trace_%' )
SELECT @TraceID ;
IF @TraceID IS NOT NULL
BEGIN
    EXEC sp_trace_setstatus @TraceID, 0 ;
    EXEC sp_trace_setstatus @TraceID, 2 ;
END ;
GO       
 

Ideally the trace files will be places on a drive on the server that is not going to cause resource conflicts when writing to any of the database files. When you script the trace definition the filename is one of the things you have to enter yourself. The command that is scripted is:

exec @rc = sp_trace_create @TraceID output, 0, N'InsertFileNameHere', @maxfilesize, NULL


If you hard code this to be a specific file then the trace will fail to start if the file already exists e.g.

With the following line in your script:

exec @rc = sp_trace_create @TraceID output, 0, N'T:\TraceFiles\Trace_Test', @maxfilesize, NULL

If you run the trace script a second time you will see the error:

Windows error occurred while running SP_TRACE_CREATE. Error = 0x80070050(failed to retrieve text for this error. Reason: 15100).
Msg 19062, Level 16, State 1, Procedure sp_trace_create, Line 1
Could not create a trace file.

To overcome this you can make the file unique by adding a date and time.

DECLARE @tracefilename nvarchar(245) ;
SET @tracefilename = N'T:\TraceFiles\Trace_' + REPLACE(REPLACE(REPLACE(CONVERT(varchar(19),  GETDATE(), 120 ),' ','_'),':',''),'-','') ;
EXEC @rc = sp_trace_create @TraceID output, 0, @tracefilename, @maxfilesize, NULL ;~

The scripted value for @maxfilesize is a paltry 5MB. You may want to change this to be a little bigger, especially as the default options (second parameter is 0) does not to use trace file rollover.

Anyone who has looked up what the fifth parameter is (set to NULL) will know this is a stop time. If you know how long the script should run, you will not need to stop the trace yourself if you set the parameter to an absolute date and time.

DECLARE @tracefilename nvarchar(245) ;
DECLARE @stoptime datetime ;
DECLARE @timeintervalsecs int ;
SET @timeintervalsecs = 600 ; /* 10 minutes */
SET @stoptime  = DATEADD( ss, @timeintervalsecs, getdate() ) ;
SET @tracefilename = N'T:\TraceFiles\Trace_' + REPLACE(REPLACE(REPLACE(CONVERT(varchar(19),  GETDATE(), 120 ),' ','_'),':',''),'-','') ;

EXEC @rc = sp_trace_create @TraceID output, 0, @tracefilename, @maxfilesize, @stoptime ;
if (@rc != 0) goto error ;

Whilst writing this article I have noticed a “bug” with the Default Profiler Template, it seems to have always been present and noone has bothered to log it. To see it go to http://tinyurl.com/DefaultProfilerTraceBug*