SQL Saturday Edinburgh Agenda
11 May 13 09:13 PM | MartinBell | with no comments

The agenda for SQL Saturday Scotland is now published

  Track 1Track 2
StartEndSession TitleSpeakerSession TitleSpeaker
08:0009:00

Registration

09:0010:00SQL Azure from Plan, to Backup, to CloudTobiasz KoprowskiIncreasing Business and IT collaboration with SQLChris Testa-O'Neill
10:0011:00Troubleshooting Query Plan Quality IssuesJoseph SackSQL Server 2012 Integration with MDS & DQSSanjaya Padhi
11:0011:15

Coffee Break

11:1512:15Thinking Big & Understanding PDWJames Rowland-JonesData Explorer - The future of selfservice ETLOliver Engels Tillmann Eitelberg
12:1513:30

Lunch Break

13:3014:30Layered Partitioning - manage very large data setsDavid Peter HansenMicrosoft Access Power HourBob Duffy
14:3015:30Solving Complex Problems with Extended EventsJonathan KehayiasAnalyzing Twitter DataNiko Neugebauer
15:3016:00

Coffee Break

16:0017:00SQL Server AlwaysOn for DummiesMark BroadbentHadoop for the Business Intelligence ProfessionalAllan Mitchell
17:0017:15

Raffle

 

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

Leeds Usergroup Slides and Scripts
10 May 13 08:53 AM | MartinBell | with no comments

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

Walking the walk at SQL Saturday Edinburgh
21 April 13 08:44 PM | MartinBell | 1 comment(s)

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

Filed under:
SQL Relay 2013 dates announced
25 February 13 08:54 PM | MartinBell | with no comments

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/

 

Filed under:
SQL Saturday #202 Edinburgh
11 December 12 08:08 PM | MartinBell | with no comments

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

Filed under:
Leeds User Group Slide Decks
14 September 12 10:34 AM | MartinBell | with no comments

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.

Filed under:
SQL Server 2012 and New Perfmon Counters
15 July 12 07:14 AM | MartinBell | with no comments

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:

Objects Counter Name
SQLServer:Buffer Manager AWE lookup maps/sec
SQLServer:Buffer Manager AWE stolen maps/sec
SQLServer:Buffer Manager AWE unmap calls/sec
SQLServer:Buffer Manager AWE unmap pages/sec
SQLServer:Buffer Manager AWE write maps/sec
SQLServer:Buffer Manager Free pages
SQLServer:Buffer Manager Reserved pages
SQLServer:Buffer Manager Stolen pages
SQLServer:Buffer Manager Total pages
SQLServer:Buffer Node Foreign pages
SQLServer:Buffer Node Free pages
SQLServer:Buffer Node Stolen pages
SQLServer:Buffer Node Target pages
SQLServer:Buffer Node Total pages
SQLServer:Buffer Partition Free list empty/sec
SQLServer:Buffer Partition Free list requests/sec
SQLServer:Buffer Partition Free pages

New counters are:

Objects Counter Name 
SQLServer:Access Methods InSysXact waits/sec 
SQLServer:Availability Replica Bytes Received from Replica/sec 
SQLServer:Availability Replica Bytes Sent to Replica/sec 
SQLServer:Availability Replica Bytes Sent to Transport/sec 
SQLServer:Availability Replica Flow Control Time (ms/sec) 
SQLServer:Availability Replica Flow Control/sec 
SQLServer:Availability Replica Receives from Replica/sec 
SQLServer:Availability Replica Resent Messages/sec 
SQLServer:Availability Replica Sends to Replica/sec 
SQLServer:Availability Replica Sends to Transport/sec 
SQLServer:Batch Resp Statistics Batches >=000000ms & <000001ms 
SQLServer:Batch Resp Statistics Batches >=000001ms & <000002ms 
SQLServer:Batch Resp Statistics Batches >=000002ms & <000005ms 
SQLServer:Batch Resp Statistics Batches >=000005ms & <000010ms 
SQLServer:Batch Resp Statistics Batches >=000010ms & <000020ms 
SQLServer:Batch Resp Statistics Batches >=000020ms & <000050ms 
SQLServer:Batch Resp Statistics Batches >=000050ms & <000100ms 
SQLServer:Batch Resp Statistics Batches >=000100ms & <000200ms 
SQLServer:Batch Resp Statistics Batches >=000200ms & <000500ms 
SQLServer:Batch Resp Statistics Batches >=000500ms & <001000ms 
SQLServer:Batch Resp Statistics Batches >=001000ms & <002000ms 
SQLServer:Batch Resp Statistics Batches >=002000ms & <005000ms 
SQLServer:Batch Resp Statistics Batches >=005000ms & <010000ms 
SQLServer:Batch Resp Statistics Batches >=010000ms & <020000ms 
SQLServer:Batch Resp Statistics Batches >=020000ms & <050000ms 
SQLServer:Batch Resp Statistics Batches >=050000ms & <100000ms 
SQLServer:Batch Resp Statistics Batches >=100000ms 
SQLServer:Buffer Manager Background writer pages/sec 
SQLServer:Buffer Manager Integral Controller Slope 
SQLServer:Database Replica File Bytes Received/sec 
SQLServer:Database Replica Log Bytes Received/sec 
SQLServer:Database Replica Log remaining for undo 
SQLServer:Database Replica Log Send Queue 
SQLServer:Database Replica Mirrored Write Transactions/sec 
SQLServer:Database Replica Recovery Queue 
SQLServer:Database Replica Redo blocked/sec 
SQLServer:Database Replica Redo Bytes Remaining 
SQLServer:Database Replica Redone Bytes/sec 
SQLServer:Database Replica Total Log requiring undo 
SQLServer:Database Replica Transaction Delay 
SQLServer:Databases Log Flush Write Time (ms) 
SQLServer:Databases Log Pool Cache Misses/sec 
SQLServer:Databases Log Pool Disk Reads/sec 
SQLServer:Databases Log Pool Requests/sec 
SQLServer:FileTable Avg time delete FileTable item 
SQLServer:FileTable Avg time FileTable enumeration 
SQLServer:FileTable Avg time FileTable handle kill 
SQLServer:FileTable Avg time move FileTable item 
SQLServer:FileTable Avg time per file I/O request 
SQLServer:FileTable Avg time per file I/O response 
SQLServer:FileTable Avg time rename FileTable item 
SQLServer:FileTable Avg time to get FileTable item 
SQLServer:FileTable Avg time update FileTable item 
SQLServer:FileTable FileTable db operations/sec 
SQLServer:FileTable FileTable enumeration reqs/sec 
SQLServer:FileTable FileTable file I/O requests/sec 
SQLServer:FileTable FileTable file I/O response/sec 
SQLServer:FileTable FileTable item delete reqs/sec 
SQLServer:FileTable FileTable item get requests/sec 
SQLServer:FileTable FileTable item move reqs/sec 
SQLServer:FileTable FileTable item rename reqs/sec 
SQLServer:FileTable FileTable item update reqs/sec 
SQLServer:FileTable FileTable kill handle ops/sec 
SQLServer:FileTable FileTable table operations/sec 
SQLServer:FileTable Time delete FileTable item BASE 
SQLServer:FileTable Time FileTable enumeration BASE 
SQLServer:FileTable Time FileTable handle kill BASE 
SQLServer:FileTable Time move FileTable item BASE 
SQLServer:FileTable Time per file I/O request BASE 
SQLServer:FileTable Time per file I/O response BASE 
SQLServer:FileTable Time rename FileTable item BASE 
SQLServer:FileTable Time to get FileTable item BASE 
SQLServer:FileTable Time update FileTable item BASE 
SQLServer:Memory Broker Clerks Internal benefit 
SQLServer:Memory Broker Clerks Memory broker clerk size 
SQLServer:Memory Broker Clerks Periodic evictions (pages) 
SQLServer:Memory Broker Clerks Pressure evictions (pages/sec) 
SQLServer:Memory Broker Clerks Simulation benefit 
SQLServer:Memory Broker Clerks Simulation size 
SQLServer:Memory Manager Database Cache Memory (KB) 
SQLServer:Memory Manager External benefit of memory 
SQLServer:Memory Manager Free Memory (KB) 
SQLServer:Memory Manager Log Pool Memory (KB) 
SQLServer:Memory Manager Reserved Server Memory (KB) 
SQLServer:Memory Manager Stolen Server Memory (KB) 
SQLServer:Memory Node Database Node Memory (KB) 
SQLServer:Memory Node Foreign Node Memory (KB) 
SQLServer:Memory Node Free Node Memory (KB) 
SQLServer:Memory Node Stolen Node Memory (KB) 
SQLServer:Memory Node Target Node Memory (KB) 
SQLServer:Memory Node Total Node Memory (KB) 
SQLServer:Query Execution Local data access/sec 
SQLServer:Query Execution Rem Req Cache Hit Ratio 
SQLServer:Query Execution Rem Req Cache Hit Ratio Base 
SQLServer:Query Execution Remote activations/sec 
SQLServer:Query Execution Remote requests/sec 
SQLServer:Query Execution Remote resend requests/sec 

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

Filed under: ,
A New Default Profiler Template
03 June 12 08:54 PM | MartinBell | with no comments

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.

Filed under: ,
PAL and SQLDiag with SQL Server 2012
24 May 12 11:56 PM | MartinBell | 2 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) )  ;
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*

More Posts Next page »

This Blog

SQL Blogs

Syndication