SQL Server Bulk Trace .

31442Code

One of my many roles at Sagittarius is to manage our clients databases on SQL Server 2012. Keeping everything running in tip top performance is always a battle. One of the many tools I use for analysing the databases performance at a server level is a custom tracing database set up.

You can use the SQL Profiler tool for watching the data on the server in real time. But sometimes it’s good to collect a ton of data over a period. It’s nice to do this by recording that data to disk without using SQL Profilers UI hogging system resources.

So what I did is to create a database called TraceImporter. We would then record data to disk for a time period and then import this data into the TraceImporter database for mining. To build this I create a table called TraceResults.

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[TraceResults](

      [TextData] [varchar](max) NULL,

      [Duration] [int] NULL,

      [Reads] [int] NULL,

      [Writes] [int] NULL,

      [CPU] [int] NULL,

      [StartTime] [datetime] NULL,

      [ProcedureName] [varchar](max) NULL

) ON [PRIMARY]

 

GO

SET ANSI_PADDING OFF

This is going to be used as a store for the data to be added to so we can analyse it. Then a stored procedure called ImportTraceResults whose job it is to read a trace file and put the data into the above TraceResults table.

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author:        Richard Brisley

-- Create date: 20/11/2014

-- Description:   Imports a sql server trace file into a table for analysis

-- =============================================

CREATE PROCEDURE [dbo].[ImportTraceResults]

      @TraceFile VARCHAR(8000)

AS

BEGIN

      -- SET NOCOUNT ON added to prevent extra result sets from

      -- interfering with SELECT statements.

      SET NOCOUNT ON;

 

      -- Import the trace results to the database

    INSERT INTO TraceResults

      (TextData, Duration, Reads, Writes, CPU, StartTime)

      SELECT TextData, Duration/1000, Reads, Writes, CPU, StartTime

      FROM fn_trace_gettable(@TraceFile,1)

     

      -- Populate the procedure name column where applicable

      UPDATE TraceResults

      SET ProcedureName =

      LEFT

      (

        RIGHT(TextData, LEN(TextData) - CHARINDEX(' ',TextData, CHARINDEX('Exec',TextData))),

        CHARINDEX(' ', RIGHT(TextData, LEN(TextData) - CHARINDEX(' ',TextData, CHARINDEX('Exec',TextData))) + ' ')

      )

      WHERE TextData like '%exec%' AND ProcedureName IS NULL

 

END

When we are running traces its good to have a quick and simple way to return a list of them. So this is a simple wrapper for fn_trace_getinfo called RunningTraces.

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author:        Richard Brisley

-- Create date: 20/11/2014

-- Description:   Returns a list of running traces

-- =============================================

CREATE PROCEDURE [dbo].[RunningTraces]

AS

BEGIN

      SET NOCOUNT ON;

 

      SELECT * FROM :: fn_trace_getinfo(default)

 

END

This is the big piece of the puzzle, it uses a stored procedure to start a new trace. You simply pass it the Output file name, so the default will create C:\Databases\Data\Traces\Trace20141120-1847.trc using the current date and time. The second parameter is the amount of time you want the trace to run. If no parameter is provided the default is 30 minutes. On timeout this procedure will automatically call ImportTraceResults to import the data into the table for analysis.

-- =============================================

-- Author:        Richard Brisley

-- Create date: 20/11/2014

-- Description:   Record a trace file to disk for analysing later on

-- =============================================

CREATE PROCEDURE [dbo].[StartTrace]

      @OutputFileName NVARCHAR(256) = 'C:\Databases\Data\Traces\Trace',

      @EndTime DATETIME = NULL

AS

BEGIN

      -- SET NOCOUNT ON added to prevent extra result sets from

      -- interfering with SELECT statements.

      SET NOCOUNT ON;

 

      IF @EndTime IS NULL BEGIN

            SET @EndTime = DATEADD(mi,30,getdate())

      END

 

      PRINT 'Ending at'

      PRINT @EndTime

 

      DECLARE @rc INT

 

      DECLARE @TraceID INT

      DECLARE @MaxFileSize BIGINT

 

      SET @MaxFileSize = 500

      SET @OutputFileName = @OutputFileName +

            CONVERT(VARCHAR(20), GETDATE(),112) +

            REPLACE(CONVERT(VARCHAR(20), GETDATE(),108),':','')

 

      exec @rc = sp_trace_create @TraceID output, 0, @OutputFileName, @MaxFileSize, @EndTime

 

      -- Set the events and columns

      declare @on bit

      set @on = 1

      exec sp_trace_setevent @TraceID, 10, 16, @on

      exec sp_trace_setevent @TraceID, 10, 1,  @on

      exec sp_trace_setevent @TraceID, 10, 17, @on

      exec sp_trace_setevent @TraceID, 10, 18, @on

      exec sp_trace_setevent @TraceID, 10, 12, @on

      exec sp_trace_setevent @TraceID, 10, 13, @on

      exec sp_trace_setevent @TraceID, 10, 14, @on

      exec sp_trace_setevent @TraceID, 12, 16, @on

      exec sp_trace_setevent @TraceID, 12, 1,  @on

      exec sp_trace_setevent @TraceID, 12, 17, @on

      exec sp_trace_setevent @TraceID, 12, 14, @on

      exec sp_trace_setevent @TraceID, 12, 18, @on

      exec sp_trace_setevent @TraceID, 12, 12, @on

      exec sp_trace_setevent @TraceID, 12, 13, @on

 

      -- Set the trace status to start

      exec sp_trace_setstatus @TraceID, 1

 

      PRINT 'TraceID'

      PRINT @TraceID

 

      SET @OutputFileName = @OutputFileName + '.trc'

      PRINT 'Trace File'

      PRINT @OutputFileName

 

      DECLARE @Count INT

      SELECT @Count = COUNT(*) FROM :: fn_trace_getinfo(default) WHERE TraceID = @TraceID

      WHILE @Count > 0

      BEGIN

            WaitFor Delay '00:00:10'

            SELECT @Count = COUNT(*) FROM :: fn_trace_getinfo(default) WHERE TraceID = @TraceID

      END

     

      PRINT 'Importing Results'

      EXEC dbo.ImportTraceResults @TraceFile = @OutputFileName

 

END

Here at Sagittarius everything is run through stored procedures. So these two Views let us separate out the stored procedures from the ad hoc sql. Which makes it easier to spot the major speed and resource offenders.

SETANSI_NULLSON

GO

SETQUOTED_IDENTIFIERON

GO

CREATEVIEW[dbo].[v_NonStoredProcedures]

AS

SELECTTextData,SUM(DURATION)ASTimeImpact,SUM(reads)ASIOImpact,SUM(CPU)ASCpuImpact,COUNT(*)ASExecutionCount

FROMTraceResults

WHEREProcedureNameISNULL

Whatever your business, be it a regional or global brand, the content you produce plays a vital role in your success. You know that… hence you’re reading this.

A well formulated and executed content strategy not only drives more traffic, at the core, it defines what your business is and helps build a strong connection between you and your audiences.

So let's quickly look at why developing a coherent content strategy is important and how setting clear goals and understanding your audience will elevate your online performance. 

What is a Content Strategy?

It's basic right? Content is at the core of how you define the way your business presents itself and an effective strategy should look to ensure that tone of voice, messaging and the core values are surfaced across all channels, from service or product pages on your website, to blog posts, through social media updates blah blah blah.

But let's keep it simple - your content strategy should be a clear roadmap that connects your marketing activities to your business goals. Align to your customer’s wants and needs and engage them at every interaction point and boom, you're in business. 

Who are my Audience?

You likely start all your projects with this chalked on the wall because your business knows “exactly” who its customers are right? Sounds obvious but we often find its not been done forensically enough (not based on data), is too old (more than 12 months ago - forget it) or its a spin off from some brand work that was legitimately aspirational but doesn’t face the reality of who you your business is actually engaging today.

So start (or circle back) with audience research, building out those personas to understand their ambitions, their lifestyle, their pain points or concerns, and crucially their wants and needs - in your context. 

Do I need to tailor content?

As part of your research find out where your audiences spend their time online and how they interact with content: Some may spend time thoroughly researching a product or service, whereas other audiences may want their content to be quick, snappy or easily digestible in the form of a video, infographic or short blog posts.

 

Ultimately, the key is to produce a strategy that creates the type of content your customers want to see:

  • What are the problems that your product or service will help them solve?

  • Who are they most influenced by?

  • What voices influence their behaviour?

  • What type of content do they consume?

  • Where do they consume content and engage with brands?

Different Content, Different Objectives

 All content is not born equal: When producing your strategy, it is important that the objectives for each individual piece are defined, that these fulfil your marketing objectives and tie to the overarching goals for your business.

There are various content frameworks that exist to aid content development in this way, but one that is popular and effective is Google’s hero, hub and hygiene method: It provides a framework on developing content to achieve different goals and gives guidance on the effort needed to create each type of content.

Hero Content

Hero content is essentially campaign content, it is big splash ideas designed to appeal to a large audience with the aim of telling your brand’s story at scale. 

Ways of measuring hero include the amount of PR mentions or links from authoritative domains plus social interactions and mentions of your brand across all channels. 

Considering the scale of hero campaigns, this content is not regularly produced and is reserved for peak promotional times where it’s important for a business to stand out from their competitors.

Hub Content

Hub content is the stuff that keeps your audience engaged, it expands on the themes of product or service level content, educates users and helps create a connection between themselves and your brand.

Hygiene Content

Hygiene content is the bread and butter of any website, it is the BAU content for products and services, it is SEO focused and targets important keywords at a product, service or guide level.

How do I manage all this?

Content development is only one part of the ongoing work needed when working with an effective content strategy. We call it “feeding the beast” because it really is the fuel in your brand vehicle and once you start you really can’t stop (if it’s delivering results) but that’s where performance measurement comes in.

Your greatest gift in managing the outputs from your hero/hub/hygiene style efforts is to understand If your content is working. To truly deliver results your business must first understand the objectives and goals of each piece of content to effectively measure its success. That as a guiding light from day 1 will let you slow down, speed up, stop or start new content briefs and projects.

Remember - content strategies are not set in stone. They are living breathing things and should adapt and pivot as insights become available and your brand naturally evolves.

If ever you want to chat content and explore new initiatives we’re always here to help.

want to speak to one of our experts?

 
Richard Brisley
Richard Brisley
Tech Lead
Richard is the longest standing member of the Sagittarius team, he works tirelessly to support the development and side-facing team with problem solving and pitches alike. His skills as a .NET programmer and database administrator have been paramount to the success of Sagittarius and our continued success. 

In 2016 and again in 2019 Richard was recognise in the BIMA 100 awards for his outstanding work in Tech, his passion for digital and his contribution to the industry. 

Richard Brisley

Richard Brisley

19 Dec 2014 - 7 minute read
share this

stay in the know, stay ahead.

Get the latest from the agency, including news, events and expert content.
explore services in the article
find out what we can do for you
read some of our case studies