jump to navigation

Looking into SQL Server page types November 18, 2013

Posted by fofo in Sql Server, Sql Server 2005, SQL Server 2008, SQL Server 2012.
Tags:
add a comment

I have been delivering a certified course in MS SQL Server 2012 recently and I was asked a very interesting question about SQL Server pages and what types of pages exist.

I had to explain to them about SQL Server architecture first. Each data file is divided internally into pages.Each page has a size of 8 kbytes.

In each of these pages (data,metadata,indexes) are stored. 8 continuous pages of 8kbytes make up one extent which is 64kbytes.
If you make the calculations,16 extents make up 1 mbyte.

Extents are divided into 2 categories,

  • Uniform – 8 pages belong to the same object e.g table
  • Mixed  – 8 pages do not belong to the same object

The first 96 bytes of the page are also reserved for the page header.
Page header includes information like

  • Page number
  • Page type
  • Free space per page

The greatest size in bytes I can have in a page is 8060 bytes
So the maximum record length I can have in a table is 8060 bytes

If I have a table that its record length is 100 bytes, that means I can have 80 records per page.
If I have 200 records I need 3 pages. Each record belongs to only one page. You cannot  have half record in one page and another half in another.

Ι was asked to provide an example where we will see the various SQL Server page types and what happens when the row size is larger than 8060 bytes.

I have installed SQL Server 2012 Enterprise edition in my machine but you can use the SQL Server 2012/2008 Express edition as well.

I am connecting to my local instance through Windows Authentication and in a new query window I type (you can copy paste)

USE tempdb
GO

CREATE TABLE largetable
( col1 VARCHAR(2000),
 col2 VARCHAR(8000),
 col3 VARCHAR(max)

 );

INSERT INTO largetable VALUES
(
REPLICATE('SQL Server 2012 will provide Mission Critical
Confidence with greater uptime blazing-fast performance and enhanced
security features for mission critical workloads',12), REPLICATE('SQL Server 2012', 533),
REPLICATE('SQL Server indexes', 1000))



SELECT * FROM largetable
-- Shows types of pages available inside SQL Server

SELECT OBJECT_NAME (object_id) AS tablename, rows,
 type_desc AS page_types_SQL
FROM sys.partitions part
INNER JOIN sys.allocation_units alloc
ON part.partition_id = alloc.container_id
WHERE object_id = object_id('largetable');

Let  me explain what I am doing in this bit of code

  • I make the tempdb the current database
  • I create a new table called largetable in the tempdb database. As you can see the record length is much larger than 8060 bytes.
  • Insert some values into the table. I insert thousands of rows using the REPLICATE function.
  • Finally I execute a SELECT JOIN statement from the sys.partitions and sys.allocation_units table.

The results are:

tablename    rows               page_types_SQL
largetable       1                            IN_ROW_DATA
largetable       1                            LOB_DATA
largetable       1                            ROW_OVERFLOW_DATA

As you can see I have one row of data.Each of these types of pages is called an Allocation Unit.

IN_ROW_DATA refers to the situation where we have the whole row size staying within the 8,060-byte limit.

LOB_DATA refers to LOB data type columns, in this case  the NVARCHAR(MAX) column and its values.

ROW_OVERFLOW_DATA refers to the data that could not be stored to single page for a single row and that is why it is using an overflow page.

Hope it helps!!!

Advertisements

Significance of order of columns in an index November 18, 2013

Posted by fofo in Sql Server, Sql Server 2005, SQL Server 2008, SQL Server 2012.
Tags:
add a comment

I have been delivering a certified course in MS SQL Server 2012 recently and I was asked a very interesting question “Does the order of the column in an index matter?”

Let’s give some basic definitions first and make some analogies.

A single column index is straightforward. Think of it as an index in the back of the book.
Let’s say you want to learn about “DBCC FREEPROCCACHE” . You look for that command in the index of the book. The index does not have the information. It has a pointer to the page where the command is described.You turn to that page and read about it. This is a good analogy for a single column,non-clustered index

In SQL Server you can also create an index that contains more than one column.This is known as the composite index. A good analogy for a composite index is the telephone book.
A telephone book lists everyone who has publicly available a telephone number in an area.It is organised not by one column but by two:last name and first name.To look up someone in the telephone book , we first navigate to the last name and then the first name. To find John Smith you first locate Smiths and then John. Composite indexes contain more than 1 column and can reference up to 16 columns from a single table or view.

Back to our question.I have answered this question with the demo below.

I have installed SQL Server 2012 Enterprise edition in my machine but you can use the SQL Server 2012/2008 Express edition as well.

I am connecting to my local instance through Windows Authentication and in a new query window I type (you can copy paste)

USE tempdb
GO

--create a sample table

CREATE TABLE Customers
(
Customer_ID INT NOT NULL IDENTITY(1,1),
Last_Name VARCHAR(20) NOT NULL,
First_Name VARCHAR(20) NOT NULL,
Email_Address VARCHAR(50) NULL

)
--create a clustered index on Customer_ID and
--a non-clustered composite index on the Last_Name and First_Name

CREATE CLUSTERED INDEX ix_Customer_ID ON Customers(Customer_ID)

CREATE NONCLUSTERED INDEX ix_Customer_Name ON Customers(Last_Name,First_Name)
--when we issue a query to SQL Server that retrieves data from the Customers table, the
--SQL Server query optimiser will consider the various retrieval methods at its disposal
--and select one it deems most appropriate

-- insert test row

INSERT INTO customerS VALUES('Smith','John','js@smith.com')

--use SQL Data Generator to create sample data 10000 rows

SELECT * FROM customers

-- DBCC DROPCLEANBUFFERS removes all buffers from the buffer pool
-- DBCC freeproccache removes all entries from the procedure cache

DBCC dropcleanbuffers
DBCC freeproccache

-- when we run this query and see the execution plan
-- we have an index seek on the nonclustered index
-- to locate the rows selected by the query
-- we also have a key lookup to find the values for the email
-- to retrieve the non-indexed columns
SELECT last_name ,
 first_name ,
 email_address
FROM customers
WHERE Last_Name = 'Smith'
 AND First_Name = 'John'

-- what happens in this case?
--where we have the WHERE statement in different order than the index order?
-- it will use the same execution plan

SELECT last_name ,
 first_name ,
 email_address
FROM customers
WHERE First_Name = 'John'
 AND Last_Name = 'Smith'

-- and what happens in this case?
--where we use only last name?
--the same plan is used

SELECT last_name ,
 first_name ,
 email_address
FROM customers
WHERE Last_Name = 'Smith'
-- what happens in this case?
-- when we use only first_name
--the index cannot be used
-- an index cannot be used to seek rows of data when the first column
-- of the index is not specified in the WHERE clause

INSERT INTO customers VALUES ('kantzelis','nikos','nikolaosk@hotmail.com')

SELECT last_name ,
 first_name ,
 email_address
FROM customers
WHERE First_Name = 'nikos'

 SET STATISTICS IO on

 DBCC dropcleanbuffers
DBCC freeproccache

 SELECT last_name ,
 first_name ,
 email_address
 FROM customers
 WHERE Last_Name = 'kantzelis'

-- now lets drop the index to see what happens

DROP INDEX ix_Customer_Name ON customers

--and rerun the query
 --we see a huge increase in logical reads without the index
 --we have in this case, in the absence of the index a clustered index scan
 --which means that each row of the table had to be read
 SELECT last_name ,
 first_name ,
 email_address
 FROM customers
 WHERE Last_Name = 'kantzelis'

Let  me explain what I am doing in this bit of code

  • I make the tempdb the current database
  • I create a new table called Customers
  • I create a clustered index on the table ix_Customer_ID on Customer_ID
  • I insert a test row to the table
  • Then I use a third party generator tool to create 10.000 records
  • Then I just use a simple statement to make sure all the rows were inserted
  • Then I remove all buffers from the buffer pool and all entries from the procedure cache
  • Then (I activate the actual execution plan) and execute the Select  query in line 43.
  • We have an index seek on the non clustered index and a key lookup to get the values for the email column.
  • Then I execute the Select statement in lines 54. The order of the columns in the WHERE clause are different than the index order.
  • Still the same execution plan is used
  • Then I execute the Select statement in lines 65.  We have just the last_name column in the  WHERE clause.The same plan is used
  • Then in line 76 I insert a new value in the table
  • Then I execute another Select statement (lines 78) where just the first_name column is in the WHERE clause.In this case the index cannot be used. An index cannot be used to seek rows of data when the first column of the index is not specified in the WHERE clause.
  • Then in line 84 I set statistics IO on so i can investigate the logical reads when having or not the index.
  • In line 89 I run a Select query again and make a note of the logical reads , which is 4 in my case.
  • Then I drop the index and rerun the query (line 103). This time the logical reads are 77. So you can see that the performance of the query without the index has been dramatically decreased.

Hope it helps!!!

SQL Server SET options and index creation November 18, 2013

Posted by fofo in Sql Server, Sql Server 2005, SQL Server 2008, SQL Server 2012.
Tags:
add a comment

I have been delivering a certified course in MS SQL Server 2012 recently and I have found something that was really impressive regarding SET options in SQL Server and index creation.

I will look into the SET QUOTED_IDENTIFIER and SET ANSI_NULLS options and how their values can affect index creation.

I have installed SQL Server 2012 Enterprise edition in my machine but you can use the SQL Server 2012/2008 Express edition as well.

I am connecting to my local instance through Windows Authentication and in a new query window I type (you can copy paste)

USE tempdb
GO

--create a sample table

CREATE TABLE Customers
(
Customer_ID INT NOT NULL IDENTITY(1,1),
Last_Name VARCHAR(20) NOT NULL,
First_Name VARCHAR(20) NOT NULL,
Email_Address VARCHAR(50) NULL,
Fullname AS first_name + '' + last_name
)

SET QUOTED_IDENTIFIER OFF;
SET ANSI_NULLS OFF;
--create a clustered index on Customer_ID and
--a non-clustered composite index on the fullname

--index failed
CREATE NONCLUSTERED INDEX ix_Customer_fName ON Customers(Fullname)

SET QUOTED_IDENTIFIER ON;
SET ANSI_NULLS ON;

-successful index creation
CREATE NONCLUSTERED INDEX ix_Customer_fName ON Customers(Fullname)

Let  me explain what I am doing in this bit of code

  • I make the tempdb the current database
  • I create a new table called Customers
  • I have a computed column in Fullname in the table Customers
  • Then I set QUOTED_IDENTIFIER and ANSI_NULLS to OFF
  • Then I create a non clustered index on the computed column. It failed with this error “CREATE INDEX failed because the following SET options have incorrect settings: ‘ANSI_NULLS, QUOTED_IDENTIFIER’. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.”
  • Then I set QUOTED_IDENTIFIER and ANSI_NULLS to ON
  • Then I create a non clustered index on the computed column again. This time works just fine.

Hope it helps!!!

An introduction to Performance Monitor – part 1 November 15, 2013

Posted by fofo in Sql Server, Sql Server 2005, SQL Server 2008, SQL Server 2012.
Tags: ,
add a comment

In my last SQL Server 2012 administration seminar, I used Performance Monitor to analyse and troubleshoot issues regarding SQL Server.

It is built into Windows and many administrators used it a lot in the old days when some of the tools we have now in our disposal did not exist.

So this is a free tool that you do not need to download.

Performance Monitor or PerfMon for short, can be used to monitor performance real-time, capture various metrics and you can select what you want to monitor and for how long.You can capture information about the hardware, the operating system, SQL Server and more.So it is not a tool for troubleshooting SQL Server only. The whole process is automated and so is the data collection.

With PerfMon we can track nearly every type of system performance

  • Disk
  • Memory
  • Cpu
  • network

The overhead of using PerfMon is minimal in most cases but you should be careful when selecting the sampling interval.

One good advice is not to use too many counters and not sampling intervals less than one second.

Sometimes it is better to use DMVs,Trace, SQL Server profiler and Extended Events.

You should use PerfMon when you need to collect OS and hardware resource counters as well as SQL Server counters.

The performance data generated by a system component is represented by a performance object
A performance object provides counters that represent specific aspects of a component such as % Processor Time for a Processor object

PerfMon allows real-time data to be viewed and analysed in multiple ways.

In this post I am going to present some of the main SQL Server counters that can be monitored through Performance Monitor and some none SQL Server related counters.

You do not require to have any previous knowledge.

You can start PerfMon by going to Start->Run->perfmon or you can go Control Panel\All Control Panel Items\Administrative Tools and then start Performance Monitor.

When I start PerfMon I see the Processor counter counter. I will add some more.

Have a look at the picture below

perfornance-monitor

I click on the green cross icon, and I add some counters for the PhysicalDdisk object.

I will add the Avg. Disk Sec/Read counter and the Avg. Disk Sec/Write, select them and add them to the counters area.

Have  a look at the picture below

perfornance-monitor-1

Avg. Disk Sec/Read = average time in ms to read from disk
a good value for this counter is average value < 10 ms
Avg. Disk Sec/Write = average time in ms to write to disk
a good value for this counter is average value < 10 ms

Now I am going to add some more counters from the Memory object.

I will add Available bytes (free physical memory). 

I will also add an SQL Server related counter (Batch Requests per second) from the SQL Server:SQL Statistics. 

Then I install/attach the AdventureWorks2012 database. You can use any database you want.

I need to generate some workload so I can get values for the counters.

I use the free tool , SQL Load Generator, from codeplex to generate multiple reads and writes.

Once more I observe the counters and see/analyse the results.

I will show you some common options in the PerfMon GUI.

You can change the colour of the counter.

Have a look at the picture below

monitor-6

You can also change the view that you see the results. You can select e.g the Report view.

Have a look at the picture below

monitor-7

You can also change the Sample interval (1 second) and the Duration (default 100 sec)/

Have a look at the picture below

monitor-8

You can also add a counter, highlight a counter and freeze the display.

monitor-9

There will be more posts on Performance monitor.

Hope it helps!!!

Looking into SQL Traces in SQL Server November 15, 2013

Posted by fofo in Sql Server, Sql Server 2005, SQL Server 2008, SQL Server 2012.
Tags:
add a comment

In this post I will talk about SQL Server Traces, what they are and how we can use them.

An SQL Trace is a low-level server-side event inside SQL Server that can be used to audit activity,troubleshoot performance debug T-SQL statements and stored procedures.With SQL Traces we can have a real-time or offline analysis of data.We can capture more than 150 events. It was introduced back in SQL Server 6.0.

We can create a SQL Trace using the SQL Server Profiler.

Alternatively you can use the sp_trace_create with the required parameters to create a new trace. Then you need to setup sp_trace_setevent with the required parameters to select the events and columns to trace.

We can start traces manually or as a part of a scheduled job.

Obviously as with all tools of such nature there is an overhead so you have to be careful. You should not add too many events in the same trace.

I have installed SQL Server 2012 Enterprise edition in my machine but you can use any other version of SQL Server.

I will connect to the local instance of SQL Server with windows authentication.

Then I go to Tools–>SQL Server Profiler i connect to my local instance of SQL Server

I start with a blank template.

Have a look at the picture below

profiler-1

Then I select some events for the trace.

From the Stored Procedures event group I select

  • RPC:Completed
  • SP:Completed
  • SP:StmtCompleted

From the TSQL event group I select

  • SQL:BatchCompleted
  • SQL:StmtCompleted

Have  a look at the picture below

profiler-2

Then I hit the Run button and immediately stop the trace (hitting the red square).

Then under File->Export I select Script Trace definition->For SQL Server 2005-SQL11 and save the file in folder in my hard disk.

Have a look at the picture below.

profiler-3

This will be an .sql file. Then I open the file in new query window in SSMS.


In my case this is the trace script that was generated.

/****************************************************/
/* Created by: SQL Server 2012 Profiler */
/* Date: 11/14/2013 10:48:57 PM */
/****************************************************/
-- Create a Queue
declare @rc int

declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 5

-- Please replace the text InsertFileNameHere, with an appropriate
-- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extension
-- will be appended to the filename automatically. If you are writing from
-- remote server to local drive, please use UNC path and make

sure server has
-- write access to your network share

exec @rc = sp_trace_create @TraceID output, 0, N'InsertFileNameHere', @maxfilesize, NULL
if (@rc != 0) goto error

-- Client side File and Table cannot be scripted

-- Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 10, 1, @on
exec sp_trace_setevent @TraceID, 10, 9, @on
exec sp_trace_setevent @TraceID, 10, 2, @on
exec sp_trace_setevent @TraceID, 10, 66, @on
exec sp_trace_setevent @TraceID, 10, 10, @on

exec sp_trace_setevent @TraceID, 10, 3, @on
exec sp_trace_setevent @TraceID, 10, 4, @on
exec sp_trace_setevent @TraceID, 10, 6, @on
exec sp_trace_setevent @TraceID, 10, 7, @on
exec sp_trace_setevent @TraceID, 10, 8, @on
exec sp_trace_setevent @TraceID, 10, 11, @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, 10, 15, @on
exec sp_trace_setevent @TraceID, 10, 16, @on
exec sp_trace_setevent @TraceID, 10, 17, @on
exec sp_trace_setevent @TraceID, 10, 18, @on
exec sp_trace_setevent @TraceID, 10, 25, @on
exec sp_trace_setevent @TraceID, 10, 26, @on
exec sp_trace_setevent @TraceID, 10, 31, @on

exec sp_trace_setevent @TraceID, 10, 34, @on
exec sp_trace_setevent @TraceID, 10, 35, @on

exec sp_trace_setevent @TraceID, 10, 41, @on
exec sp_trace_setevent @TraceID, 10, 48, @on
exec sp_trace_setevent @TraceID, 10, 49, @on
exec sp_trace_setevent @TraceID, 10, 50, @on
exec sp_trace_setevent @TraceID, 10, 51, @on
exec sp_trace_setevent @TraceID, 10, 60, @on
exec sp_trace_setevent @TraceID, 10, 64, @on
exec sp_trace_setevent @TraceID, 43, 1, @on
exec sp_trace_setevent @TraceID, 43, 9, @on
exec sp_trace_setevent @TraceID, 43, 2, @on
exec sp_trace_setevent @TraceID, 43, 66, @on
exec sp_trace_setevent @TraceID, 43, 3, @on
exec sp_trace_setevent @TraceID, 43, 4, @on
exec sp_trace_setevent @TraceID, 43, 5, @on
exec sp_trace_setevent @TraceID, 43, 6, @on
exec sp_trace_setevent @TraceID, 43, 7, @on
exec sp_trace_setevent @TraceID, 43, 8, @on

exec sp_trace_setevent @TraceID, 43, 10, @on

exec sp_trace_setevent @TraceID, 43, 11, @on
exec sp_trace_setevent @TraceID, 43, 12, @on
exec sp_trace_setevent @TraceID, 43, 13, @on
exec sp_trace_setevent @TraceID, 43, 14, @on
exec sp_trace_setevent @TraceID, 43, 15, @on
exec sp_trace_setevent @TraceID, 43, 22, @on
exec sp_trace_setevent @TraceID, 43, 26, @on
exec sp_trace_setevent @TraceID, 43, 28, @on
exec sp_trace_setevent @TraceID, 43, 29, @on
exec sp_trace_setevent @TraceID, 43, 34, @on
exec sp_trace_setevent @TraceID, 43, 35, @on
exec sp_trace_setevent @TraceID, 43, 41, @on
exec sp_trace_setevent @TraceID, 43, 48, @on
exec sp_trace_setevent @TraceID, 43, 49, @on
exec sp_trace_setevent @TraceID, 43, 50, @on
exec sp_trace_setevent @TraceID, 43, 51, @on
exec sp_trace_setevent @TraceID, 43, 60, @on

exec sp_trace_setevent @TraceID, 43, 62, @on
exec sp_trace_setevent @TraceID, 43, 64, @on

exec sp_trace_setevent @TraceID, 45, 1, @on
exec sp_trace_setevent @TraceID, 45, 9, @on
exec sp_trace_setevent @TraceID, 45, 3, @on
exec sp_trace_setevent @TraceID, 45, 4, @on
exec sp_trace_setevent @TraceID, 45, 5, @on
exec sp_trace_setevent @TraceID, 45, 6, @on
exec sp_trace_setevent @TraceID, 45, 7, @on
exec sp_trace_setevent @TraceID, 45, 8, @on
exec sp_trace_setevent @TraceID, 45, 10, @on
exec sp_trace_setevent @TraceID, 45, 11, @on
exec sp_trace_setevent @TraceID, 45, 12, @on
exec sp_trace_setevent @TraceID, 45, 13, @on
exec sp_trace_setevent @TraceID, 45, 14, @on
exec sp_trace_setevent @TraceID, 45, 15, @on
exec sp_trace_setevent @TraceID, 45, 16, @on
exec sp_trace_setevent @TraceID, 45, 17, @on

exec sp_trace_setevent @TraceID, 45, 18, @on
exec sp_trace_setevent @TraceID, 45, 22, @on
exec sp_trace_setevent @TraceID, 45, 25, @on

exec sp_trace_setevent @TraceID, 45, 26, @on
exec sp_trace_setevent @TraceID, 45, 28, @on
exec sp_trace_setevent @TraceID, 45, 29, @on
exec sp_trace_setevent @TraceID, 45, 34, @on
exec sp_trace_setevent @TraceID, 45, 35, @on
exec sp_trace_setevent @TraceID, 45, 41, @on
exec sp_trace_setevent @TraceID, 45, 48, @on
exec sp_trace_setevent @TraceID, 45, 49, @on
exec sp_trace_setevent @TraceID, 45, 50, @on
exec sp_trace_setevent @TraceID, 45, 51, @on
exec sp_trace_setevent @TraceID, 45, 55, @on
exec sp_trace_setevent @TraceID, 45, 60, @on
exec sp_trace_setevent @TraceID, 45, 61, @on
exec sp_trace_setevent @TraceID, 45, 62, @on
exec sp_trace_setevent @TraceID, 45, 64, @on

exec sp_trace_setevent @TraceID, 45, 66, @on
exec sp_trace_setevent @TraceID, 12, 1, @on
exec sp_trace_setevent @TraceID, 12, 9, @on

exec sp_trace_setevent @TraceID, 12, 3, @on
exec sp_trace_setevent @TraceID, 12, 11, @on
exec sp_trace_setevent @TraceID, 12, 4, @on
exec sp_trace_setevent @TraceID, 12, 6, @on
exec sp_trace_setevent @TraceID, 12, 7, @on
exec sp_trace_setevent @TraceID, 12, 8, @on
exec sp_trace_setevent @TraceID, 12, 10, @on
exec sp_trace_setevent @TraceID, 12, 12, @on
exec sp_trace_setevent @TraceID, 12, 13, @on
exec sp_trace_setevent @TraceID, 12, 14, @on
exec sp_trace_setevent @TraceID, 12, 15, @on
exec sp_trace_setevent @TraceID, 12, 16, @on
exec sp_trace_setevent @TraceID, 12, 17, @on
exec sp_trace_setevent @TraceID, 12, 18, @on
exec sp_trace_setevent @TraceID, 12, 26, @on

exec sp_trace_setevent @TraceID, 12, 31, @on
exec sp_trace_setevent @TraceID, 12, 35, @on
exec sp_trace_setevent @TraceID, 12, 41, @on
exec sp_trace_setevent @TraceID, 12, 48, @on
exec sp_trace_setevent @TraceID, 12, 49, @on
exec sp_trace_setevent @TraceID, 12, 50, @on
exec sp_trace_setevent @TraceID, 12, 51, @on
exec sp_trace_setevent @TraceID, 12, 60, @on
exec sp_trace_setevent @TraceID, 12, 64, @on
exec sp_trace_setevent @TraceID, 12, 66, @on
exec sp_trace_setevent @TraceID, 41, 1, @on
<pre></pre>
exec sp_trace_setevent @TraceID, 41, 9, @on
exec sp_trace_setevent @TraceID, 41, 3, @on
<pre></pre>
exec sp_trace_setevent @TraceID, 41, 4, @on
exec sp_trace_setevent @TraceID, 41, 5, @on
exec sp_trace_setevent @TraceID, 41, 6, @on
exec sp_trace_setevent @TraceID, 41, 7, @on
exec sp_trace_setevent @TraceID, 41, 8, @on
exec sp_trace_setevent @TraceID, 41, 10, @on
exec sp_trace_setevent @TraceID, 41, 11, @on
exec sp_trace_setevent @TraceID, 41, 12, @on
exec sp_trace_setevent @TraceID, 41, 13, @on
exec sp_trace_setevent @TraceID, 41, 14, @on
exec sp_trace_setevent @TraceID, 41, 15, @on
exec sp_trace_setevent @TraceID, 41, 16, @on
exec sp_trace_setevent @TraceID, 41, 17, @on

exec sp_trace_setevent @TraceID, 41, 18, @on
exec sp_trace_setevent @TraceID, 41, 25, @on
exec sp_trace_setevent @TraceID, 41, 26, @on
exec sp_trace_setevent @TraceID, 41, 29, @on
exec sp_trace_setevent @TraceID, 41, 35, @on
exec sp_trace_setevent @TraceID, 41, 41, @on
exec sp_trace_setevent @TraceID, 41, 48, @on
exec sp_trace_setevent @TraceID, 41, 49, @on
exec sp_trace_setevent @TraceID, 41, 50, @on
exec sp_trace_setevent @TraceID, 41, 51, @on

exec sp_trace_setevent @TraceID, 41, 55, @on
exec sp_trace_setevent @TraceID, 41, 60, @on
exec sp_trace_setevent @TraceID, 41, 61, @on
exec sp_trace_setevent @TraceID, 41, 64, @on

exec sp_trace_setevent @TraceID, 41, 66, @on
-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint

-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1

-- display trace id for future references

select TraceID=@TraceID
goto finish

error:
select ErrorCode=@rc

finish:
go

You can set the set @maxfilesize = 5 to some other value.

In this line exec @rc = sp_trace_create @TraceID output, 0, N’InsertFileNameHere’, @maxfilesize, NULL 

You can set the path where the trace will be stored.In my case I chose “C:\Users\Nikos\Desktop\traces\daily“.The extension is always .trn

Then I run the script and I get a TraceID of 3.

In a new query window I type

SELECT * FROM ::fn_trace_getinfo(0)

and I can see all the traces that are currently running.

Have a look at the picture below.

trace-4

If I want to stop the trace I type

exec sp_trace_setstatus 3, 0

You can create SQL Server jobs that will start and stop the trace so you do not have to start and it stop it.

Then I execute a series of T-SQL statements and stored procedures and the data is gathered in the trace file.

I use the “sys.fn_trace_gettable” to get the content of one or more trace files in tabular form.

SELECT *

FROM fn_trace_gettable('c:\traces\daily.trc', DEFAULT)

In general do not use SQL Server traces if you can get the same information though DMVs and DMFs. DMVs and DMFs are supported since SQL Server 2005.

You should use SQL Server Extended events when you can get the same information with SQL traces and when you have an SQL Server version like SQL Server 2008 or 2012 that support SQL Server Extended events.

You can learn more about SQL Server Extended Events in this post

Finally make sure you have a good look at these free tools that help us greatly with trace analysis

Hope it helps!!!