Mastering SQL Trace Using Profiler - Microsoft SQL Server 2012 Performance Tuning - page 14

Collecting large amount of trace data can affect the performance of SQL
Server. So, before creating a trace, we should identify the type of analysis we
want to perform on trace information. A single trace should not be created
for multiple types of analysis. For each analysis type, a separate trace should
be created until and unless different types of analysis explicitly need to be
combined into single trace for performing correlative analysis. For example,
rather than creating a single trace that collects both scan events and lock
events for index scan analysis and object locking analysis respectively, we
should consider creating two separate traces; one for collecting only scan
events and another for collecting lock events only.
Getting ready
In this recipe, we will see how to capture only those trace events that occurred for a specific
database and from a specific SQL Server login.
Let's assume that sample database AdventureWorks2012 is our production database
on our production server, which is hosting other databases also. One of the database
users James complains that he faces some problems while running queries against
database AdventureWorks2012. So, we want to trace his session only for database
AdventureWorks2012. Because there are also other databases hosted on the same
production server and many users are accessing AdventureWorks2012 database, we
need to filter trace events based on session login name and database name in order
to avoid any unwanted trace data from being collected.
To emulate this case practically, we need the following as prerequisites:
F An instance of SQL Server 2012 Developer or Enterprise Evaluation edition
F An SQL Server Login account with sysadmin rights
F The sample AdventureWorks2012 database on the instance of SQL Server. For
more details on how to install AdventureWorks2012 database, please refer the
Introduction section of this topic.
F Two SQL Server logins named James and Peter with some permission on
AdventureWorks2012 database.
How to do it...
We will be performing three main actions in this example. These are as follows:
F Creating the required logins and users in the AdventureWorks2012 database
( James and Peter )
F Creating a trace by applying filters on the DatabaseName and SessionLoginName
data columns