Database Reference
In-Depth Information
These steps will generate the script that you need to create a session and output it to a file.
To manually create this new trace, use Management Studio as follows:
1.
Open the script file or navigate to the Query window.
2.
Modify the path and file location for the server you're creating this session on.
3.
Execute the script.
Once the session is created, you can use the following command to start it:
ALTER EVENT SESSION [Query Performance Metrics]
ON SERVER
STATE = START;
You may want to automate the execution of the last step through the SQL Agent, or you can even run the script
from the command line using the
sqlcmd.exe
utility. Whatever method you use, the final step will start the session.
To stop the session, just run the same script with the STATE set to stop. I'll show how to do that in the next section.
Defining a Session Using T-SQL
If you look at the script defined in the previous section, you will see a single command that was used to define the
session,
CREATE EVENT SESSION
.
Once the session has been defined, you can activate it using
ALTER EVENT
.
Once a session is started on the server, you don't have to keep Management Studio open any more. You can identify
the active sessions by using the dynamic management view sys.dm_xe_sessions, as shown in the following query:
SELECT dxs.name,
dxs.create_time
FROM sys.dm_xe_sessions AS dxs;
Figure
6-9
shows the output of the view.
Figure 6-9.
Output of sys.dm_xe_sessions
The number of rows returned indicates the number of sessions active on SQL Server. I have two other sessions
running in addition to the one I created in this chapter. You can stop a specific session by executing the stored
procedure
ALTER EVENT SESSION
.
ALTER EVENT SESSION [Query Performance Metrics]
ON SERVER
STATE = STOP;
To verify that the session is stopped successfully, reexecute the query against the catalog view sys.dm_xe_sessions,
and ensure that the output of the view doesn't contain the named session.
