Tag Archives: SQL Server trace

Extended Events in SQL Server 2012 – Part 2

Extended Events, SQL Server 2012 February 22, 2014 Leave a comment

In my earlier post on Extended events we discussed how we can use the SSMS GUI in SQL Server 2012 to create new extended events. In this article we will take a deeper dive into the SQL Server 2012 SSMS GUI and explore other extended events features.

How to add fields to a session

Step 1 -> Right click on the session we created. Click on properties
ee14Step 2 -> Click on Events -> Click on Configure
ee15Step 3 -> When we click on configure, the below window will appear
ee16Step 4 -> Now click on the first event ‘query_post_execution_showplan‘ and we will find a list of Actions on the right hand pane being displayed. These are the bunch of global fields from which we can choose and add to the events and the relevant data will be captured by the event trace. For this event we will choose ‘cpu_id’ and ‘database_name’.
ee17ee17Repeat the same for the next event
ee19Step 5 -> Now Click on the ‘Events Fields‘ tab and select the below as depicted. Click OK.
ee20ee21Now lets start the session and examine the Live data. For generating activity on the SQL Server we will run the below queries

USE AdventureWorks2012
go
SELECT * FROM Production.Product pp
WHERE pp.ProductID > 40
go
SELECT * FROM Production.BillOfMaterials Pb
WHERE pb.BillOfMaterialsID > 100
go

Now lets watch the Live data
ee22

How to add filters to a session

In this section we will add filters to the session we created. Filters allow you to fetch events based on the criteria provided.
Step 1 -> Right click on the session -> Click on Properties -> Click on Events -> Choose an event -> Click on the ‘Filter(Predicate)’ tab
ee23Step 2 -> From the Field tab choose a filter from the drop down. In our case we will choose duration and set a value in the Value field. Click on OK.
ee24
Step 3 -> Start the session and fire some queries and observe the Live data in action.

Advertisement

How to create a trace from sql server profiler

Trace September 6, 2013 Leave a comment

In this post i would like to demonstrate how to create and run a trace on a SQL Server.
Let us start by understanding what a trace does for us. It helps us gather events that are happening on the instance. These events gathered, help us analyze and understand the state of the SQL server or help us understand any specific issue that we are troubleshooting. Let us understand this with an example:

Step 1: Open SQL Server Management Studio(SSMS)
Step 2: Click on Tools -> Click on SQL Server Profiler
CT1Step 3: Once Profiler opens it will ask for the name of the SQL server on which you want to run the trace
CT2Step 4: In the next window we need to feed the profiler with what we want to capture such as events,template. We can also set a stop time for the trace to stop automatically. The general tab also allows to decide where we want to save the trace file i.e we can either save the trace file as a file or save the data as a table in a database. Apart from that the template drop down helps us decide what kind of data we would like to capture. In our example we will save the trace in a file. We also need to set the maximum file size and if a file would roll over in case the maximum limit is breached.
CT3Step 5: Now let us click on next tab “Events Selection”
CT4Step 6: Check the “Show all events” and “Show all columns” options. Scroll down to the “Stored procedures” events and check the below marked options. Then scroll down to the “TSQL” events and check the following options as below
CT5CT6Step 7: On the same tab we would see to buttons “Column Filters” and “Organize Columns”. Click on “Column Filters” and browse through the options it brings along. you might want to filter out any particular aspect of the system on which you want to collect data. In this example we would select the below
CT8Step 8: Click on OK and our trace is ready to run. Click on Run and you would see the trace capturing data
CT9Step 9 : To Stop the trace click on the stop button
CT11If we want we can save the definition of the trace as a T-SQL script. This is how we would achieve the same.
Step 1: Click on File -> Export -> Script Definition -> For SQL Server 2005 – 2008 R2
CT12Step 2: Save the SQL script in a desired Location
CT13In the next post i will demonstrate how to use this SQL script for later use for capturing data. I hope this demo was useful.

Related articles

How to read the content of one or more trace files

Trace September 6, 2013 Leave a comment

To read the content of one or ore trace files we need to use the following system provided function by SQL Server:

fn_trace_gettable ( ‘filename’ , number_files )
Where
‘filename’: is the initial trace file to be read. Filename is nvarchar(256), with no default.

number_files:Indicates the number of rollover files to be read. This number includes the initial file specified in filename.The value is an integer.

Let us see how it works with the help of an example:
Step 1: let us first find the traces running in the system
SELECT * FROM sys.traces
Go
Output gave us:
Trace1Step 2: Choose the trace that you want to open. In this case we have only one trace running. Copy the value from the path column and pass it on as a parameter in the below function

SELECT * FROM sys.fn_trace_gettable(‘E:\MSSQL10.ORCH_INST_A\MSSQL\Log\log_13083.trc’,0) WHERE TextData IS NOT NULL
Output gave us:
Trace2We can save the output in a temporary table if we want look further into the data:
Use Databasename;
GO
SELECT * INTO Temp_table
FROM fn_trace_gettable(‘E:\MSSQL10.ORCH_INST_A\MSSQL\Log\log_13083.trc’,0);
GO

Related articles

How to find if there is a trace running in the server

Scripts, Trace September 6, 2013 Leave a comment

What is Server-side tracing?
Server-side tracing is the process of having your SQL Server machine save events to a physical file on that machine without using the Profiler client tool.  Server-side tracing is enabled and controlled by using SQL Server system-supplied stored procedures and functions. With these system-supplied processes, you can identify what to trace, when to start and stop tracing, what traces are running, and view trace information stored in the trace file.

Here is how you view the number of traces currently in the system and the details of the trace:

Number of traces(active and stopped) currently in the system:
select * from sys.traces

Here is how you view the active(trace is running) traces in the system:

SELECT * FROM :: fn_trace_getinfo(default) WHERE property = 5 and value = 1

You can stop and terminate a trace by executing the following:

EXEC sp_trace_setstatus @traceid , @status = 0 — Stops the trace
EXEC sp_trace_setstatus @traceid , @status = 2 — Eliminates the definition of the trace from the server.

 

Related articles
%d bloggers like this: