SQL Profiler Trace Events in .NET

Making QR Code in .NET SQL Profiler Trace Events

8 Enter the following script to create a little activity on the server:

NOTE It s very possible, based on previous exercises, that you have additional activity on your server that will be captured by SQL Profiler This could include maintenance plans or SQL Server Agent jobs or alerts 9 Stop the trace You can select File | Stop Trace or click the red square Stop Trace button on the toolbar 10 Scroll toward the top of the trace and find the query you executed How long did the query take on your system Looking at Figure 13-34, you can see this query had a duration of 2978 on my system This is given in milliseconds, so it equates to almost three seconds

11 By scrolling to the right, you can see the Login Name of the user that executed the query NOTE Since this trace was already named, it has been saved We could now use it as a workload file in Database Tuning Advisor if desired However, our trace is very small with very little activity, so it wouldn t be very useful A trace that has captured a full day s worth of activity would be much more valuable (and much larger) 12 You can also filter out extra information that you re not interested in For example, in the bottom-right corner of Figure 13-34, you may have noticed my trace had over 1700 rows Due to other activity on my server, SQL Profiler captured a lot of activity If I were just interested in the amount of time taken by queries from a specific application, I could use a filter Select File | Properties or click the Properties icon on the SQL Profiler toolbar 13 Click the Events Selection tab Click the Show All Columns check box to allow all the columns to appear Select the ApplicationName for each of the events Deselect the Show All Columns check box Your display should look similar to Figure 13-35 14 Click the Column Filters button

15 In the Edit Filter dialog box, ensure ApplicationName is selected and click the plus sign next to Like Enter Microsoft SQL Server Management Studio Query in the blank box under Like Spelling counts here Any typo will result in zero rows returned Your display should look similar to Figure 13-36 Click OK

EXAM TIP Filters can be implemented within SQL Profiler to capture specific data This includes filtering by application name, database name, duration (measured in milliseconds), login name, and more 16 Click Run to start the trace, and return to SSMS and rerun the query:

17 Return to SQL Profiler You should see two lines and only two lines The first line is from the USE command, and the second line is from the SELECT command TIP If you don t have any lines displayed, check your spelling in the filter A single letter mistyped will result in zero lines of data being captured in the trace Stop the trace, correct anything that is wrong, and then restart it

By default, each template holds a specific set of Trace events However, any template can be slightly modified, creating a completely different template Additionally, templates can easily be exported to be used on other SQL Server computers For example, let s say we want to monitor a specific application and especially pay attention to disk reads and writes from this application The Standard template may have many of the events we want (including reads and writes), but lacks the application name We can start with the Standard template, modify it by adding an application filter, save it with a different name, and then export it In the following exercise, we will create a template, export it, and then import it While we ll do this on a single server in the exercise, you would do it between different servers on the job In a production environment, you may create a template on one server and want to share it with other database developers You can export it from your server, give it someone else, and let them import it Exercise 1312: Create, Export, and Import a Template Watch video 13-12 to see how to create a SQL Server Profiler template