Microsoft.SQL.Server2012.Performance.Tuning.Cookbook学习笔记(一)

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, until 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 columns 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 Trace Properties dialog box.

Now, we will open two instances of SQL Server Management Studio one by one that connect to SQL Server with the logins James 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]
GO
SELECT * FROM [Sales].[Customer]
GO
USE [master]
GO
SELECT * FROM sys.databases
GO

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 that 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 be retrieved by either calling DB_ID(‘AdventureWorks2012‘) metadata function or querying sys.databases catalog view.

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

• ApplicationName: A filter can be specified on this data column so that only trace events raised by a particular client application are captured
• DatabaseID: A filter can be specified on this data column so that only trace eventsraised for a specific database are captured
• DatabaseName: A filter can be specified on this data column so that only trace events raised for a specific database are captured
• HostName: A filter can be specified on this data column so that only trace events raised from a specific host or client machine are captured
• LoginName: A filter can be specified on this data column so that only trace events raised by a specific login are captured
• ObjectID: A filter can be specified on this data column so that only trace events raised for a specific object are captured
• ObjectName: A filter can be specified on this data column so that only trace events raised for a specific object are captured
• SessionLoginName: A filter can be specified on this data column so that only trace events raised by a specific login are captured
• SPID: A filter can be specified on this data column so that only trace events raised from a specific session connection are captured

Microsoft.SQL.Server2012.Performance.Tuning.Cookbook学习笔记(一)

标签：state   compute   spec   peter   nec   cli   dial   spi   other