What is SQL SERVER Profiler, Custom Templates & Default Templates

What:

SQL Server Profiler is a rich interface to create and manage traces and analyze and replay trace results of SQL Trace for monitoring an instance of the Database Engine or Analysis Services. Results can be saved in trace files or table for further investigation.

When:

SQL Server Profiler is used for activities such as:

·         Stepping through problem queries to find the cause of the problem.
·         Finding and diagnosing slow-running queries.
·         Capturing the series of Transact-SQL statements that lead to a problem.
·         Monitoring the performance of SQL Server to tune workloads.
·         Correlating performance counters to diagnose problems.
·         Finding problems for table locks and dead lock information
·         Group all slow running queries for applications.
·         Use results in Tuning Advisor for optimization
·         Finding XML and deadlock execution plan to understand where exactly it is occurring.
·         Audits record and security-related actions

How:

There are predefined trace templates that allow you to easily configure the event classes that you will most likely need for specific traces.

SQL Server Profiler can trace a variety of server types. For example you can trace Analysis Services and SQL Server 2005 (and later versions). Therefore, SQL Server Profiler maintains different templates for different servers, and makes available the specific template that matches the selected server type.

Templates:

Some templates and there output are as follows;

Template name	Template purpose
SP_Counts	Captures stored procedure execution behavior over time.
Standard	Generic starting point for creating a trace. Captures all stored procedures and Transact-SQL batches that are run. Use to monitor general database server activity.
TSQL	Captures all Transact-SQL statements that are submitted to SQL Server by clients and the time issued. Use to debug client applications.
TSQL_Duration	Captures all Transact-SQL statements submitted to SQL Server by clients, their execution time (in milliseconds), and groups them by duration. Use to identify slow queries.
TSQL_Grouped	Captures all Transact-SQL statements submitted to SQL Server and the time they were issued. Groups information by user or client that submitted the statement. Use to investigate queries from a particular client or user.
TSQL_Locks	Captures all of the Transact-SQL statements that are submitted to SQL Server by clients along with exceptional lock events. Use to troubleshoot deadlocks, lock time-out, and lock escalation events.
TSQL_Replay	Captures detailed information about Transact-SQL statements that is required if the trace will be replayed. Use to perform iterative tuning, such as benchmark testing.
TSQL_SPs	Captures detailed information about all executing stored procedures. Use to analyze the component steps of stored procedures. Add the SP:Recompile event if you suspect that procedures are being recompiled.
Tuning	Captures information about stored procedures and Transact-SQL batch execution. Use to produce trace output that Database Engine Tuning Advisor can use as a workload to tune databases.

 Custom Templates:

You can also define your own custom template to capture data of your own needs.

Step1:  I have opend Sql server Profiler from SSMS Sql server management studio-- > tools tab.
Step2: From Pic1 you can view how to create New template
                You can save your new template with your own name as I have set name to [My Template] using default sp_lock profiler template. If You want to create your own uncheck existing profiler check box and in Event Selection, select class events of your own requirement.

Step3:  Once Template saved, it is listed in other templates as you can see in pic2. There are severl other usefull options for profiler as I have enables all

• You can save profiler results into trace file
• You can save profiler results into database table
• Trace can be stopped at given time
• Trace can be set in table as how many rows you want to store and many others

Procedure generate Insert update Statement

create procedure [dbo].[DynamicInsertUpdate_Test]
@tablename varchar(800)
--,@DbName nvarchar(200)

AS

declare @vtblcolumns varchar(max)
declare @vcolumns1 varchar(max)
declare @vcolumns2 varchar(max)
declare @vtblDtypes varchar(max)
Declare @vstr varchar(max)
declare @vColumns varchar(max)
Declare @vPKcol varchar(250)
declare @Sid varchar(200)
SELECT @sid = TABLE_NAME FROM information_schema.tables WHERE table_name = @tablename AND OBJECTPROPERTY(OBJECT_ID(TABLE_NAME), 'TableHasIdentity') =1

Select @vPKcol = c.COLUMN_NAME
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
where pk.TABLE_NAME = @tableName
and CONSTRAINT_TYPE = 'PRIMARY KEY'
and c.TABLE_NAME = pk.TABLE_NAME
and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME

select @vtblcolumns = COALESCE(@vtblcolumns+',','')+ COLUMN_NAME + '='+'@P'+ COLUMN_NAME + ' 'from information_schema.columns where TABLE_NAME= @tablename AND COLUMN_NAME NOT IN (@vPKcol)

if @sid = null
select @vcolumns1 = COALESCE(@vcolumns1+',','')+ COLUMN_NAME,@vColumns2 = COALESCE(@vColumns2+',','') + '@p'+ Column_NAME from information_schema.columns where TABLE_NAME= @tablename
else
select @vcolumns1 = COALESCE(@vcolumns1+',','')+ COLUMN_NAME,@vColumns2 = COALESCE(@vColumns2+',','') + '@p'+ Column_NAME from information_schema.columns where TABLE_NAME= @tablename and COLUMN_NAME <> @vPKcol
-- Select from information_schema.columns where TABLE_NAME =@tablename



Select @vColumns = COALESCE(@vColumns+',','') +
'@p'+ Column_NAME + ' ' +
Data_Type +
case when Character_Maximum_length > 0 then
'(' + cast(Character_Maximum_length as varchar(10)) + ')'
else ''
end from information_schema.columns where TABLE_NAME =@tablename




Select @vstr =
'
IF Object_ID ( ''UP_'+@tablename+'_InsertUpdate '') IS NOT NULL
DROP Procedure UP_'+@tablename+'_InsertUpdate
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author: '+system_user+'
-- Create date: '+convert (varchar (50),getdate(),103)+'
-- Description: This is Auto Generated Code for '+@tablename+' Insert and Update Record for Single Primary Key Table
-- =============================================
CREATE PROCEDURE UP_'+@tablename+'_InsertUpdate
('+@vColumns+ '
)
As
BEGIN
SET NOCOUNT ON;

IF EXISTS (Select '+@vpkcol+' from '+@tablename+ ' where '+@vpkcol+'='+'@p'+@vpkcol+ ')
BEGIN
UPDATE '+@tablename+'
SET '+@vtblcolumns+'
WHERE '+ @vPKcol + '='+'@p'+@vpkcol+ '
END
ELSE
BEGIN
INSERT INTO '+@tablename+ '('+@vcolumns1+')
VALUES ('+@vColumns2+')

SET ' +'@p'+@vPKcol+' = Scope_Identity()
END

RETURN ' +'@p'+@vPKcol+'

End
GO
'

Print @vstr