922
Part VII: Monitoring and Auditing
■ (^) Errors and Warnings (16): Any abnormal event or error can trigger these events;
useful when watching for errors in an active system.
■ (^) Full text (3): The Full text events track only Full-Text Search crawl activity. There
are no events for Full-Text Search configuration changes. More information about
Full-Text Search queries is hidden in the Performance: FullTextQuery events.
■ Locks (9): Although enabling lock events can be great for learning about locks in
a controlled setting, using these events in a production environment can instantly
fill Profiler with thousands of events, so be careful with this category. A notewor-
thy exception is the Deadlock Graph event, which captures a full set of data about
the deadlock and even displays a graph.
■ (^) OLEDB (5): These events track OLE-DB distributed query calls made by SQL Server
to other providers.
■ (^) Objects (3): DDL events (CREATE, ALTER, DROP) can be tracked with these events.
■ Performance (14): These events track data about query plans and plan guides. A
notable event is the ShowPlan XML event, which can display the query execution
plan.
■ (^) Progress Report (1): This event category tracks the progress of online reindexing.
■ Query Notifications (4): These show information about query notification activity,
including subscriptions activity.
■ Scans (2): When watching for performance issues, this category can trace index
scans and table scans.
■ Security Audit (42): With a nod to the Hitchhiker’s Guide to the Galaxy, the Security
Audit category includes numerous events to support C2 and Common Criteria
compliance.
■ (^) Server (3): This odd category includes mount tape, memory change, and trace stop
events.
■ (^) Sessions (1): This event fires when a trace is started and returns an event for
every existing connection, including its properties.
■ (^) Stored Procedures (15): This rich category includes a number of events related to
stored procedure execution, compilation, and cache hits.
■ (^) TSQL (9): These events fire for individual T-SQL statements.
■ Transactions (13): SQL transactions events at the level of begin transaction,
commit transaction, and rollback transactions are traced with this category.
Playing with this event reveals how much activity actually happens with SQL
Server. Unfortunately, there’s no event to capture changing the transaction
isolation level.
■ (^) User Configurable (10): To gather custom data about the environment or applica-
tion’s activity, the application can call the sp_trace_generateevent system
stored procedure to fire an event and pass custom data to SQL Trace.
c38.indd 922c38.indd 922 7/31/2012 10:04:19 AM7/31/2012 10:04:19 AM
http://www.it-ebooks.info