====== Lesson 1: Viewing Current Activity ======
===== What Is Activity Monitor? =====
  * Graphical views of current user connectoins and locks
    * Process Info
    * Locks by Process
    * Locks by Object
              
===== What Are Dynamic Management View and Functions? =====
  * Provide information about the current state of a server, for example
    * Locks held, threads, memory usage
  * Listed in <Database name>\Views\System Views folder

====== Lesson 2: Using System Monitor ======
===== Introduction to System Monitor =====
  * Use System Monitor to view system metrics
    * Objects
    * Counters
    * Instances
               
===== SQL Server Performance Objects =====
  * SQL Server-specific objects enable you to monitor each instance of SQL Server
  * SQL Server-specific objects include:
^  Object  ^  Description  ^
^ SQLServer:Buffer Manager | Provides information about the memory buffers used by SQL Server |
^ SQLServer:Databases | Provides information about a SQL Server database |
^ SQLServer:Locks | Provides information about individual lock requests |
^ SQLServer:Memory Manager | Provides information about SQL Server memory usage |

===== Considerations for Monitoring SQL Server =====
  * Key areas to monitor
    * Disk system
    * Memory
    * CPU
                

====== Lesson 3: Using SQL Server Profiler ======
===== What Is SQL Server Profiler? =====
  * Graphical tool for tracing server and database activity
    * Create a trace that is based on a reuseable template
    * Watch the trace results as the trace runs
    * Store the trace results in a table or file for further analysys
    * Start, stop, pause, and modify the trace results as necessary
    * Replay the trace results
                       
===== SQL Server Profiler Trace Options =====
  * Specify trace template
    * Predefined
    * User defined
  * Save trace data
    * Save to table
    * Save to file
  * Specify trace stop time
                        
===== Trace Categories, Events, and Columns =====
  * Categories
    * Groups of related events
  * Events
    * The occurrence of an action within SQL Server
  * Columns
    * The attributes of events
    * Managed by using column filters
                           
====== Lesson 4: Using DDL Triggers ======
===== What Are DDL Triggers? =====
  * Triggers to trap DDL statement execution
  * Database or server scope
  * Process:
    - DDL statement executed
    - DDL action performed
    - Trigger fires

===== How to Create DDL Triggers =====
  * Define the trigger name, scope, and event
  * Retrieve event information using eventdata()
  * Extract event data by using XQuery <code sql>
CREATE TRIGGER CreateTable
ON DATABASE
FOR CREATE_TABLE
AS
DECLARE @data XML
DECLARE @database NVARCHAR (100)
SET @data = eventdata()
SET @database =
    @data.value('(/EVENT_INSTANCE/DatabaseName)[1]',
    'nvarchar(100)')
......
</code>
      
===== How to Manage DDL Triggers =====
  * Viewing triggers <code sql>
SELECT name FROM sys.triggers
SELECT definition FROM sys.sql_modules ...
</code>  
  * Modifying triggers <code sql>
ALTER TRIGGER CreateTable ON DATABASE FOR
CREATE_TABLE
AS ......
</code>
  * Deleting triggers <code>
DROP TRIGGER CreateTable ON DATABASE
</code>
  
====== Lesson 5: Using Event Notifications ======
===== What Are Event Notifications? =====
  * Message containing event data
    * DDL events
    * DML events
    * Trace events
  * Send to an event processing service by using Service Broker
    * A message type and contract are predefined
    * You must create a queue, a service, and a route
                           
===== How to Create Event Notifications =====
  * Define the event notification
  * Specify the scope
  * Specify the event
  * Specify the service <code sql>
CREATE EVENT NOTIFICATION UpdateStats
ON SERVER
FOR UPDATE_STATISTICS
TO SERVICE 'NotifyService', 'current database'
</code>        

===== How to Process Event Notifications =====
  * Receive the message
  * Extract event data bye using XQuery <code sql>
DECLARE @cmd nvarchar(1000)
DECLARE @posttime nvarchar(24)
DECLARE @spid nvarchar(6)

SET @cmd = @messagebody.value
    ('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]',
    'nvarchar(100)')
SET @messagebody.value
    ('(/EVENT_INSTANCE/PostTime)', 'nvarchar(24')
SET @spid = @messagebody.value
    ('(/EVENT_INSTANCE/SPID)[1]', 'nvarchar(6)')
</code>    

===== How to Manage Event Notifications =====
  * Viewing event notifications and queues <code sql>
SELECT name FROM sys.event_notifications
SELECT definition FROM sys.service_queues
</code>
  * Deleting event notifications <code sql>
DROP EVENT NOTIFICATION UpdateStats ON SERVER
</code>