Using WQL with the WMI Provider for Server Events
Management applications access SQL Server events using the WMI Provider for Server Events by issuing WMI Query Language (WQL) statements. WQL is a simplified subset of structured query language (SQL), with some WMI-specific extensions. In using WQL, an application retrieves an event type against a specific instance of SQL Server, a database, or a database object (the only object currently supported is queue). The WMI Provider for Server Events translates the query into an event notification that is created in the target database for database-scoped or object-scoped event notifications, or in the master database for server-scoped event notifications.
For example, consider the following WQL query:
SELECT * FROM DDL_DATABASE_LEVEL_EVENTS WHERE DatabaseName = 'AdventureWorks2008R2'
From this query, the WMI Provider tries to produce the equivalent of this event notification on the target server:
USE AdventureWorks2008R2 ; GO CREATE EVENT NOTIFICATION SQLWEP_76CF38C1_18BB_42DD_A7DC_C8820155B0E9 ON DATABASE WITH FAN_IN FOR DDL_DATABASE_LEVEL_EVENTS TO SERVICE 'SQL/Notifications/ProcessWMIEventProviderNotification/v1.0', 'A7E5521A-1CA6-4741-865D-826F804E5135'; GO
The argument in the FROM clause of the WQL query (DDL_DATABASE_LEVEL_EVENTS) can be any valid event upon which an event notification can be created. The arguments in the SELECT and WHERE clauses can specify any event property associated with an event or its parent event. For a list of valid events and event properties, see WMI Provider for Server Events Classes and Properties.
The following WQL syntax is supported explicitly by the WMI Provider for Server Events. Additional WQL syntax may be specified, but it is not specific to this provider and is parsed instead by the WMI host service. For more information about the WMI Query Language, see the WQL documentation on the Microsoft Developer Network (MSDN).
The where_condition of the WMI Provider for Server Events syntax determines the following:
The scope by which the provider tries to retrieve the specified event_type: the server level, database level, or object level (the only object currently supported is queue). Ultimately, this scope determines the type of event notification created in the target database. This process called event notification registration.
The database, schema, and object, where appropriate, on which to register.
The WMI Provider for Server Events uses a bottom-up, first-fit algorithm to produce the narrowest possible scope for the underlying EVENT NOTIFICATION. The algorithm tries to minimize internal activity on the server and network traffic between the instance of SQL Server and the WMI host process. The provider examines the event_type specified in the FROM clause and the conditions in the WHERE clause, and tries to register the underlying EVENT NOTIFICATION with the narrowest possible scope. If the provider cannot register at the narrowest scope, it tries to register at successively higher scopes until a registration finally succeeds. If it reaches the highest scope the server-level) and fails, it returns an error to the consumer.
For example, if DatabaseName='AdventureWorks2008R2' is specified in the WHERE clause, the provider tries to register an event notification in the AdventureWorks2008R2 database. If the AdventureWorks2008R2 database exists and the calling client has the required permissions to create an event notification in AdventureWorks2008R2, the registration is successful. Otherwise, an attempt is made to register the event notification at the server level. The registration succeeds if the WMI client has the required permissions. However, under this scenario, events are not returned to the client until the AdventureWorks2008R2 database has been created.
The where_condition can also act as a filter to additionally limit the query to a specific database, schema, or object. For example, consider the following WQL query:
Depending on the outcome of the registration process, this WQL query may be registered either at the database or server level. However, even if it is registered at the server level, the provider ultimately filters any ALTER_TABLE events that do not apply to the AdventureWorks2008R2.Sales.SalesOrderDetail table. In other words, the provider returns only the properties of the ALTER_TABLE events that occur on that specific table.
If a compound expression such as DatabaseName='AW1' OR DatabaseName='AW2' is specified, an attempt is made to register a single event notification at the server scope instead of two separate event notifications. The registration succeeds if the calling client has permissions.
If SchemaName='X' AND ObjectType='Y' AND ObjectName='Z' are all specified in the WHERE clause, an attempt is made to register the event notification directly on object Z in schema X. The registration succeeds if the client has permissions. Note that currently, object-level events are supported only on queues, and only for the QUEUE_ACTIVATION event_type.
Note that not all events can be queried at any particular scope. For example, a WQL query on a trace event such as Lock_Deadlock, or a trace event group such as TRC_LOCKS, can only be registered at the server level. Similarly, the CREATE_ENDPOINT event and the DDL_ENDPOINT_EVENTS event group can also be registered only at the server level. For more information about the appropriate scope for registering events, see Designing Event Notifications. An attempt to register a WQL query whose event_type can only be registered at the server level is always made at the server level. Registration succeeds if the WMI client has permissions. Otherwise, an error is returned to the client. In some cases, however, you can still use the WHERE clause as a filter for server-level events based on the properties that correspond to the event. For example, many trace events have a DatabaseName property that can be used in the WHERE clause as a filter.
Server-scoped event notifications are created in the master database and can be queried for metadata by using the sys.server_event_notifications catalog view.
Database-scoped or object-scoped event notifications are created in the specified database and can be queried for metadata by using the sys.event_notifications catalog view. (You must prefix the catalog view with the corresponding database name.)
A. Querying for events at the server scope
The following WQL query retrieves all event properties for any SERVER_MEMORY_CHANGE trace event that occurs on the instance of SQL Server.
SELECT * FROM SERVER_MEMORY_CHANGE
B. Querying for events at the database scope
The following WQL query retrieves specific event properties for any event that occurs in the AdventureWorks2008R2 database and exists under the DDL_DATABASE_LEVEL_EVENTS event group.
SELECT SPID, SQLInstance, DatabaseName FROM DDL_DATABASE_LEVEL_EVENTS WHERE DatabaseName = 'AdventureWorks2008R2'
C. Querying for events at the database scope, filtering by schema and object
The following query retrieves all event properties for any ALTER_TABLE event that occurs on table AdventureWorks2008R2.Sales.SalesOrderDetail.