• Graphical views of current user connectoins and locks
  • Process Info
  • Locks by Process
  • Locks by Object

• Provide information about the current state of a server, for example
  • Locks held, threads, memory usage
• Listed in <Database name>\Views\System Views folder

• Use System Monitor to view system metrics
  • Objects
  • Counters
  • Instances

• SQL Server-specific objects enable you to monitor each instance of SQL Server
• SQL Server-specific objects include:

^ Object ^ Description ^

• Key areas to monitor
  • Disk system
  • Memory
  • CPU

• 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

• Specify trace template
  • Predefined
  • User defined
• Save trace data
  • Save to table
  • Save to file
• Specify trace stop time

• Categories
  • Groups of related events
• Events
  • The occurrence of an action within SQL Server
• Columns
  • The attributes of events
  • Managed by using column filters

• Triggers to trap DDL statement execution
• Database or server scope
• Process:
  1. DDL statement executed
  2. DDL action performed
  3. Trigger fires

• Define the trigger name, scope, and event
• Retrieve event information using eventdata()
• Extract event data by using XQuery

  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)')
  ......

• Viewing triggers

  SELECT name FROM sys.triggers
  SELECT definition FROM sys.sql_modules ...

• Modifying triggers

  ALTER TRIGGER CreateTable ON DATABASE FOR
  CREATE_TABLE
  AS ......

• Deleting triggers

  DROP TRIGGER CreateTable ON DATABASE

• 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

• Define the event notification
• Specify the scope
• Specify the event
• Specify the service

  CREATE EVENT NOTIFICATION UpdateStats
  ON SERVER
  FOR UPDATE_STATISTICS
  TO SERVICE 'NotifyService', 'current database'

• Receive the message
• Extract event data bye using XQuery

  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)')

• Viewing event notifications and queues

  SELECT name FROM sys.event_notifications
  SELECT definition FROM sys.service_queues

• Deleting event notifications

  DROP EVENT NOTIFICATION UpdateStats ON SERVER