Maintenance and Monitoring - Microsoft SQL Server 2008 R2 Administration - page 311

F Ensure that SQL Server Agent is started and running
F Ensure that MSDB system database has at least 100 MB free space available as all
XE-based events are created in this system database
F Ensure that the login used to create XE must have CONTROL SERVER permission to
manage the Extended Events objects
F Ensure that the login used to monitor XE has VIEW SERVER STATE permission to
execute system catalogs and DMVs

There are a total of 258 events built-in with the database engine. The processes to monitor
the SQL server instance using Extended Events is similar to creating an audit process to use
CREATE or ALTER statements. XE follows these steps to create an Extended Events session.
Alter it to start and stop the monitoring session, and then use the system catalogs and DMVs
for monitoring and viewing the event information.

How to do it...

The following steps are required to set up monitoring resource usage with Extended Events
using SQL Server 2008 R2.

1. To monitor using XE events setup, we will follow the process to set up and define a
new extended events session object that includes operation system request and SQL
Server channel.

2. In this recipe, we will monitor the long-running query events that occur in addition to

non_yielding errors that occur from query execution.
--Chapter 8: Extended Events
--Long Running Queries execution demo
DECLARE @db_id int
set @db_id=DB_ID(N'AdventureWorks2008R2')
select @db_id

3. In this recipe, we get the @db_id value for AdventureWorks2008R2 is 5.

CREATE EVENT SESSION LongRunningQueryExecution ON SERVER
ADD EVENT sqlserver.sp_statement_completed
( ACTION (sqlserver.sql_text)
WHERE sqlserver.database_id = 5 AND duration > 30000) ,
ADD EVENT sqlserver.error_reported (
ACTION (package0.callstack, sqlserver.session_id,
sqlserver.sql_text, sqlserver.tsql_stack)
WHERE ([severity]>=(20)

OR ([error]=(17803)

OR [error]=(701)

OR [error]=(802)

OR [error]=(8645)