Microsoft.SQL.Server2012.Performance.Tuning.Cookbook Study Notes (i)

One, Creating a trace or workload

Note the point:

In the Trace Properties dialog box, there are a checkbox option in the "General tab" with the caption Server processes Trace Data, to specify whether
Trace data should is processed on the server. If not checked, trace data was processed at the client side.
When trace data was processed at the client side, it was possible for some events to be missed if the server load was high. If This option was checked, then trace
Data is processed on the server and all the events included in trace definition was guaranteed to be captured without miss . However, this guarantee comes
With performance penalty, because processing trace data on server have an impact on the performance of SQL Server, and Henc E Enabling this option is
Not recommended on production server.
Also, running SQL Server Profiler on production Server itself should BES avoided as running SQL Server Profiler is resource Consuming. Instead, you should run
SQL Server Profiler from the a client computer and connect it to the your SQL Server from there.

 when large amount of event data is captured, the trace file can grow very quickly and become very large. Enabling the Enable file rollover option can prevent a trace file from becoming very large by limiting a file to the Maximum file size specified. When the file size was reached to the maximum file size specified, SQL Server creates a new Roll-over file with the SA Me name appended with a suffix of a incremental number for the same trace. Thus, when we have this option enabled and the size of trace data are greater than maximum file, we have multiple trac E files for the same trace.

It's best-to-store the trace file on a separate disk other than the one which are used to store data files and log files of SQL Server databases. Storing the trace file on the same physical disk where database files is stored can degrade the performance of normal I/O operations of other databases.

Tips

Configuring a trace by enabling the Save to table checkbox in the Trace Properties dialog box and saving trace data direct ly to trace table was less
Efficient. If you want your trace data to being saved in a trace table then consider saving the trace data first in a trace file; Then export your trace data from
Trace file to trace table is opening the trace file in SQL Server Profiler and selecting the Save as command from the file Menu with the Trace Table ...
Option. When you want to save your trace in a trace table, always consider to save your trace in a separate database.

SQL Profiler

SQL Server Profiler is a graphical user interface tool for working with SQL Trace. Behind The scene, it uses the same SQL Trace engine, but additionally provides graphical user interface to the U Ser for working with traces. SQL Server Profiler provides functionalities, such as displaying collected event data on its graphical interface, SAV ing traces either in a file or in An sql Server table, opening previously saved traces, extracting T-SQL statements F Rom a trace,and many more. Finding and analyzing long running or costly queries, finding deadlocks And their related information, looking for WH Ich indexes is scanned, and looking for database connection requests is some of the practical applications of SQL S Erver Profiler.

To get list of all available event classes, you can query Sys.trace_events catalog view. You can join Sys.trace_events and sys.trace_categories

Catalog views on category_id column-to-make correlation between.

Select e.trace_event_id, c.name from  sys.trace_events ejoin sys.trace_categories c on e.category_id = c.category_id

Commonly-used event Classes
The following list gives brief descriptions of commonly used event classes:

• Audit Login:this event occurs when a user connects and logs in to SQL Server
• Audit Logout:this event occurs when a users disconnects and logs off from
• SQL Server
• RPC:Starting:This event occurs when a Remote Procedure call (RPC) starts executing
• RPC:Completed:This event occurs when a Remote Procedure call (RPC) completes its execution
• SQL:BatchStarting:This event occurs when a T-SQL batch starts executing
• SQL:StmtStarting:This event occurs when a statement inside a T-SQL batch starts executing
• SQL:StmtCompleted:This event occurs when a statement inside a T-SQL batch completes its execution
• SQL:BatchCompleted:This event occurs when a T-SQL batch completesits execution
• SP:Starting:This event occurs when a stored procedure starts executing
• SP:StmtStarting:This event occurs when a statement inside a stored procedure starts executing
• SP:StmtCompleted:This event occurs when a statement inside a stored procedure completes its execution
• SP:Completed:This event occurs when a stored procedure completes its execution

commonly-used Data Columns
The following list gives brief descriptions of commonly used event classes:

• Applicationname:this data column represents the name of the client application causing a trace event to occur
• Databaseid:this data column represents the internal system assigned ID of the database for which a trace event occurs
• Databasename:this data column represents the name of the database for which a trace event occurs
• Hostname:this data column represents the name of the host or computer where the client component connecting to SQL Server Causes a trace event to occur
• Loginname:this data column represents the name of the login under whose security context, particular T-SQL statement (s) E Xecutes that causes trace event to occur
• Objectid:this data column represents the internal system assigned ID of an object for which a trace event occurs
• Objectname:this data column represents the name of an object for which a trace event occurs
• Sessionloginname:this data column represents the name of the login who initiated the connection and under whose security Context A trace event occurs
• Spid:this data column represents the Server Process ID or Session ID of the connection which causes a trace event to OCCU R

Filter Example:

Because-SQL Server logins named James and Peter with permissions on adventureworks2012 database is required, Create them by performing the following steps:
1. Open SQL Server Management Studio.
2. Connect to the instance of SQL Server with login account has sysadmin rights.
3. Execute the following T-SQL script to create the logins and their corresponding users

In the AdventureWorks2012 database for James and Peter:--creating Login and User in--adventureworks2012 database for James Use [Master]gocreate LOGIN [James] with password=n ' JamesPass123 ', default_database=[adventureworks2012],check_ Expiration=off,check_policy=offgouse [Adventureworks2012]gocreate USER [James] for LOGIN [James]goalter ROLE [db_owner]  ] ADD MEMBER [james]go--creating Login and User in AdventureWorks2012 database for Peteruse [master]gocreate login [Peter] With Password=n ' PeterPass123 ', default_database=[adventureworks2012],check_expiration=off,check_policy=offgouse [ Adventureworks2012]gocreate USER [Peter] for LOGIN [Peter]goalter ROLE [db_owner] ADD MEMBER [Peter]go

Now, we'll create a trace and capture only events that occur for AdventureWorks2012 the database from James ' session only. To does this, follow these steps:

1. Start SQL Server Profiler.
2. Select New Trace ... from the File menu. In the Connect to Server dialog box, provide connection details of SQL Server hosting the AdventureWorks2012 database and Click on Connect.
3. In the General tab of the trace Properties, enter filteringevents as the trace name and select Blank template for the use T He template:drop-down menu as shown
In following:

4. In Events Selection tab, check the checkbox for event class sql:batchcompleted under the TSQL event category as shown I N Following screenshot:

5. Click on Column Filters ... button.

6. In the Edit Filter dialog box, select DatabaseName from the list of available data columns on the left. Expand the like option and enter string value
AdventureWorks2012; Then press the OK button as shown in the following screenshot:

7. In the Edit Filter dialog box, select SessionLoginName from the list of available data columns on the left. Expand the like option and enter string value James; Then press the OK button as shown in following screenshot:

8. Click on the Organize Columns ... button in Events Selection tab of Trace Properties dialog box. Select TextData data column and then keep clicking on Up button repeatedly to move the column up the order in the list, UN Til the column appears as the second item, at the top of the list underneath EventClass data column. Do this same exercise also for the data columns DatabaseName and SessionLoginName so that the final order of the data Colu MNS should look like as shown in following screenshot. Press OK in the Organize Columns dialog box:

9. Click on the Run button to run the trace in the The Trace Properties dialog box.

Now, we'll open the instances of SQL Server Management Studio one by one, connect to SQL server with the logins Jame S and Peter respectively and run a few queries.
1. Open the first instance of SSMS and connect to SQL Server with the login credentials of James. In the Query window, type and execute the T-SQL statements as shown in following script:

Use [Adventureworks2012]goselect * from [Sales]. [Customer] Gouse [Master]goselect * from Sys.databasesgo

2. Open a second instance of SSMS and connect to SQL Server with the login credentials of Peter. In the Query window, type and execute the same T-SQL
Queries as shown in previous step.
3. Switch to SQL Server Profiler window, which is running the trace. Examine the trace data as shown in following screenshot:

Tips:use of DatabaseID
We can alternatively use DatabaseID data column instead of DatabaseName to specify a filter on a particular database. For this, we must know system
Assigned ID value for a specific database. This value can is retrieved by either calling db_id (' AdventureWorks2012 ') metadata function or querying sys.databases cata Log view.

The following section lists some of data columns that is commonly used in trace filters:

• APPLICATIONNAME:A filter can specified on the This data column so the only trace events raised by A particular client app Lication is captured
• DATABASEID:A filter can specified on the This data column so the only trace eventsraised for A specific database is Capt Ured
• DATABASENAME:A filter can specified on the This data column so the only trace events raised for A specific database is C Aptured
• HOSTNAME:A filter can specified on the This data column so the only trace events raised from A specific host or client Ma Chine is captured
• LOGINNAME:A filter can specified on the This data column so the only trace events raised by A specific login is captured
• OBJECTID:A filter can specified on the This data column so the only trace events raised for A specific object is capture D
• OBJECTNAME:A filter can specified on the This data column so the only trace events raised for A specific object is CAPTU Red
• SESSIONLOGINNAME:A filter can specified on the This data column so the only trace events raised by A specific login is C Aptured
• SPID:A filter can specified on the This data column so the only trace events raised from A specific session connection AR E Captured

Microsoft.SQL.Server2012.Performance.Tuning.Cookbook Study Notes (i)