May 2012 - Posts

PAL and SQLDiag with SQL Server 2012
24 May 12 11:56 PM | MartinBell | 3 comment(s)

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 Smile), 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.

Filed under: , ,
Changing Server Side Trace Scripts
20 May 12 07:38 PM | MartinBell | 1 comment(s)

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) )  ;
    EXEC sp_trace_setstatus @TraceID, 0
    EXEC sp_trace_setstatus @TraceID, 2

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 ) ;
    EXEC sp_trace_setstatus @TraceID, 0 ;
    EXEC sp_trace_setstatus @TraceID, 2 ;

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_%' )
    EXEC sp_trace_setstatus @TraceID, 0 ;
    EXEC sp_trace_setstatus @TraceID, 2 ;

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*

Changing SQL Server Port with Powershell
07 May 12 05:48 PM | MartinBell | 1 comment(s)

The other week, we were fortunate to have Mark Broadbent (blog\twitter) as a guest speaker at the Leeds SQL Server User Group. he did a great presentation on using Windows Server Core and SQL Server. If you haven’t taken time to learn Powershell then this could be the best reason to do so. Of course if you are already have your own toolbox of Powershell scripts to administer SQL Server on other editions of Windows, moving to Windows Core will be a doddle!

I have previously posted blogs about stopping/starting SQL Server using Powershell, and also enabling protocols with a Powershell script so when Mark mentioned Powershell as one option to change the TCP/IP port for an SQL Server Instance I thought I would write my own.

I incorporated into the scripts a check to make sure the instigator was a “real administrator” and also to prompt the user to start/restart the SQL Server instance to allow the changes to take effect.

The script can be found here.

Filed under: ,

This Blog

SQL Blogs