Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Комрады, вопрос по Server Profiler to Monitor Analysis Services  [new]
Ken@t
Member

Откуда: 大地
Сообщений: 3265
собсно где табличку EventClassID взять аналогичной sys.trace_events, в доке описаний нет ?



朋友之间怎么能尔虞我诈、钩心斗角呢?
12 окт 11, 08:27    [11424376]     Ответить | Цитировать Сообщить модератору
 Re: Комрады, вопрос по Server Profiler to Monitor Analysis Services  [new]
Ken@t
Member

Откуда: 大地
Сообщений: 3265
Вот так оно должно выглядеть, хотя не нашёл некоторых субклассов для Notification
;WITH trace_events_subclass
AS
(
SELECT 19 trace_event_id,1 subclass_value,'Backup' subclass_name
UNION SELECT 19,2,'Restore'
UNION SELECT 19,3,'Sync'

UNION SELECT 5,1,'Process'
UNION SELECT 5,2,'Merge'
UNION SELECT 5,3,'Delete'
UNION SELECT 5,4,'DeleteOldAggregations'
UNION SELECT 5,5,'Rebuild'
UNION SELECT 5,6,'Commit'
UNION SELECT 5,7,'Rollback'
UNION SELECT 5,8,'CreateIndexes'
UNION SELECT 5,9,'CreateTable'
UNION SELECT 5,10,'InsertInto'
UNION SELECT 5,11,'Transaction'
UNION SELECT 5,12,'Initialize'
UNION SELECT 5,13,'Discretize'
UNION SELECT 5,14,'Query'
UNION SELECT 5,15,'CreateView'
UNION SELECT 5,16,'WriteData'
UNION SELECT 5,17,'ReadData'
UNION SELECT 5,18,'GroupData'
UNION SELECT 5,19,'GroupDataRecord'
UNION SELECT 5,20,'BuildIndex'
UNION SELECT 5,21,'Aggregate'
UNION SELECT 5,22,'BuildDecode'
UNION SELECT 5,23,'WriteDecode'
UNION SELECT 5,24,'BuildDMDecode'
UNION SELECT 5,25,'ExecuteSQL'
UNION SELECT 5,26,'NowExecutingSQL'
UNION SELECT 5,27,'ExecuteModifiedSQL'
UNION SELECT 5,28,'Connecting'
UNION SELECT 5,29,'BuildAggsAndIndexes'
UNION SELECT 5,30,'MergeAggsOnDisk'
UNION SELECT 5,31,'BuildIndexForRigidAggs'
UNION SELECT 5,32,'BuildIndexForFlexibleAggs'
UNION SELECT 5,33,'WriteAggsAndIndexes'
UNION SELECT 5,34,'WriteSegment'
UNION SELECT 5,35,'DataMiningProgress'
UNION SELECT 5,36,'ReadBufferFullReport'
UNION SELECT 5,37,'ProactiveCacheConversion'
UNION SELECT 5,38,'Backup'
UNION SELECT 5,39,'Restore'
UNION SELECT 5,40,'Synchronize'

UNION SELECT 6,1,'Process'
UNION SELECT 6,2,'Merge'
UNION SELECT 6,3,'Delete'
UNION SELECT 6,4,'DeleteOldAggregations'
UNION SELECT 6,5,'Rebuild'
UNION SELECT 6,6,'Commit'
UNION SELECT 6,7,'Rollback'
UNION SELECT 6,8,'CreateIndexes'
UNION SELECT 6,9,'CreateTable'
UNION SELECT 6,10,'InsertInto'
UNION SELECT 6,11,'Transaction'
UNION SELECT 6,12,'Initialize'
UNION SELECT 6,13,'Discretize'
UNION SELECT 6,14,'Query'
UNION SELECT 6,15,'CreateView'
UNION SELECT 6,16,'WriteData'
UNION SELECT 6,17,'ReadData'
UNION SELECT 6,18,'GroupData'
UNION SELECT 6,19,'GroupDataRecord'
UNION SELECT 6,20,'BuildIndex'
UNION SELECT 6,21,'Aggregate'
UNION SELECT 6,22,'BuildDecode'
UNION SELECT 6,23,'WriteDecode'
UNION SELECT 6,24,'BuildDMDecode'
UNION SELECT 6,25,'ExecuteSQL'
UNION SELECT 6,26,'NowExecutingSQL'
UNION SELECT 6,27,'ExecuteModifiedSQL'
UNION SELECT 6,28,'Connecting'
UNION SELECT 6,29,'BuildAggsAndIndexes'
UNION SELECT 6,30,'MergeAggsOnDisk'
UNION SELECT 6,31,'BuildIndexForRigidAggs'
UNION SELECT 6,32,'BuildIndexForFlexibleAggs'
UNION SELECT 6,33,'WriteAggsAndIndexes'
UNION SELECT 6,34,'WriteSegment'
UNION SELECT 6,35,'DataMiningProgress'
UNION SELECT 6,36,'ReadBufferFullReport'
UNION SELECT 6,37,'ProactiveCacheConversion'
UNION SELECT 6,38,'Backup'
UNION SELECT 6,39,'Restore'
UNION SELECT 6,40,'Synchronize'

UNION SELECT 7,1,'Process'
UNION SELECT 7,2,'Merge'
UNION SELECT 7,3,'Delete'
UNION SELECT 7,4,'DeleteOldAggregations'
UNION SELECT 7,5,'Rebuild'
UNION SELECT 7,6,'Commit'
UNION SELECT 7,7,'Rollback'
UNION SELECT 7,8,'CreateIndexes'
UNION SELECT 7,9,'CreateTable'
UNION SELECT 7,10,'InsertInto'
UNION SELECT 7,11,'Transaction'
UNION SELECT 7,12,'Initialize'
UNION SELECT 7,13,'Discretize'
UNION SELECT 7,14,'Query'
UNION SELECT 7,15,'CreateView'
UNION SELECT 7,16,'WriteData'
UNION SELECT 7,17,'ReadData'
UNION SELECT 7,18,'GroupData'
UNION SELECT 7,19,'GroupDataRecord'
UNION SELECT 7,20,'BuildIndex'
UNION SELECT 7,21,'Aggregate'
UNION SELECT 7,22,'BuildDecode'
UNION SELECT 7,23,'WriteDecode'
UNION SELECT 7,24,'BuildDMDecode'
UNION SELECT 7,25,'ExecuteSQL'
UNION SELECT 7,26,'NowExecutingSQL'
UNION SELECT 7,27,'ExecuteModifiedSQL'
UNION SELECT 7,28,'Connecting'
UNION SELECT 7,29,'BuildAggsAndIndexes'
UNION SELECT 7,30,'MergeAggsOnDisk'
UNION SELECT 7,31,'BuildIndexForRigidAggs'
UNION SELECT 7,32,'BuildIndexForFlexibleAggs'
UNION SELECT 7,33,'WriteAggsAndIndexes'
UNION SELECT 7,34,'WriteSegment'
UNION SELECT 7,35,'DataMiningProgress'
UNION SELECT 7,36,'ReadBufferFullReport'
UNION SELECT 7,37,'ProactiveCacheConversion'
UNION SELECT 7,38,'Backup'
UNION SELECT 7,39,'Restore'
UNION SELECT 7,40,'Synchronize'
UNION SELECT 7,41,'Build Processing Schedule'

UNION SELECT 9,0,'Multidimensional Expressions (MDX) Query'
UNION SELECT 9,1,'Data Mining Extensions (DMX) Query'
UNION SELECT 9,2,'SQL Query'

UNION SELECT 10,0,'Multidimensional Expressions (MDX) Query'
UNION SELECT 10,1,'Data Mining Extensions (DMX) Query'
UNION SELECT 10,2,'SQL Query'
UNION SELECT 11,1,'From cache'
UNION SELECT 11,2,'Not from cache'
UNION SELECT 11,3,'internal'
UNION SELECT 11,4,'SQL'


UNION SELECT 15,0,'CREATE'
UNION SELECT 15,1,'ALTER'
UNION SELECT 15,2,'DELETE'
UNION SELECT 15,3,'PROCESS'
UNION SELECT 15,4,'DESIGNAGGREGATIONS'
UNION SELECT 15,5,'WBINSERT'
UNION SELECT 15,6,'WBUPDATE'
UNION SELECT 15,7,'WBUPDATE'
UNION SELECT 15,8,'BACKUP' 
UNION SELECT 15,9,'RESTORE' 
UNION SELECT 15,10,'MERGEPARTITIONS'
UNION SELECT 15,11,'SUBSCRIBE'
UNION SELECT 15,12,'BATCH'
UNION SELECT 15,13,'BEGINTRANSACTION'
UNION SELECT 15,14,'COMMITTRANSACTION'
UNION SELECT 15,15,'ROLLBACKTRANSACTION' 
UNION SELECT 15,16,'GETTRANSACTIONSTATE'
UNION SELECT 15,17,'CANCEL'
UNION SELECT 15,18,'SYNCHRONIZE'
UNION SELECT 15,19,'IMPORT80MININGMODELS'
UNION SELECT 15,10000,'OTHER'

UNION SELECT 16,1,'CREATE'
UNION SELECT 16,2,'DELETE'
UNION SELECT 16,3,'ALTER'
UNION SELECT 16,5,'PROCESS'
UNION SELECT 16,6,'INSERT'
UNION SELECT 16,7,'UPDATE'
UNION SELECT 16,8,'DROP'
UNION SELECT 16,9,'BACKUP'
UNION SELECT 16,10,'RESTORE'
UNION SELECT 16,11,'MERGEPARTITIONS'
UNION SELECT 16,12,'SUBSCRIBE'
UNION SELECT 16,13,'BATCH'
UNION SELECT 16,14,'BEGINTRANSACTION'
UNION SELECT 16,15,'COMMITTRANSACTION'
UNION SELECT 16,16,'ROLLBACKTRANSACTION'
UNION SELECT 16,17,'GETTRANSACTIONSTATE'
UNION SELECT 16,18,'CANCEL'
UNION SELECT 16,19,'SYNCHRONIZE'
UNION SELECT 16,20,'INTERNAL'
UNION SELECT 16,21,'LOCK'
UNION SELECT 16,22,'UNLOCK'
UNION SELECT 16,23,'STATEMENT'
UNION SELECT 16,24,'DESIGNAGGREGATION'

UNION SELECT 39,1,'Proactive Caching Begin'
UNION SELECT 39,2,'Proactive Caching End'
UNION SELECT 39,3,'Flight Recorder Snapshot Begin'
UNION SELECT 39,4,'Server Config file update'
UNION SELECT 39,5,'Object changes'

)
,trace_events
AS
(
SELECT 1 trace_event_id, 'Audit Login' name
UNION SELECT 2,'Audit Logout'
UNION SELECT 4,'Audit Server Start / Stop'
UNION SELECT 18,'Audit Object Permission Event'
UNION SELECT 19,'Audit Backup / Restore'
UNION SELECT 5,'Progress Report Begin'
UNION SELECT 6,'Progress Report End'
UNION SELECT 7,'Progress Report Current'
UNION SELECT 8,'Progress Report Error'
UNION SELECT 9,'Query Begin (MDX, DMX, SQL)'
UNION SELECT 10,'Query Begin (MDX, DMX, SQL)'
UNION SELECT 15,'XMLA command begin (create / alter / delete / restore, etc)'
UNION SELECT 16,'XMLA command end (create / alter / delete / restore, etc)'
UNION SELECT 17,'Error'
UNION SELECT 33,'Server State Discover Begin'
UNION SELECT 34,'Server State Discover Data'
UNION SELECT 35,'Server State Discover End'
UNION SELECT 36,'Discover Begin'
UNION SELECT 38,'Discover End'
UNION SELECT 39,'Notification'
UNION SELECT 41,'Existing Connection'
UNION SELECT 42,'Existing Session'
UNION SELECT 43,'Session Initialize'
UNION SELECT 50,'Deadlock'
UNION SELECT 51,'Lock Timeout'
UNION SELECT 70,'Query Cube Begin'
UNION SELECT 71,'Query Cube End'
UNION SELECT 72,'Calculate Non Empty Begin'
UNION SELECT 73,'Calculate Non Empty Current'
UNION SELECT 74,'Calculate Non Empty End'
UNION SELECT 75,'Serialize Results Begin'
UNION SELECT 76,'Serialize Results Current'
UNION SELECT 77,'Serialize Results End'
UNION SELECT 78,'Execute MDX Script Begin'
UNION SELECT 79,'Execute MDX Script Current'
UNION SELECT 80,'Execute MDX Script End'
UNION SELECT 81,'Query Dimension'
UNION SELECT 11,'Query Subcube'
UNION SELECT 12,'Query Subcube Verbose'
UNION SELECT 60,'Get Data From Aggregation'
UNION SELECT 61,'Get Data from Cache'
)
ELECT 
 trace_events.name
 ,trace_events_subclass.subclass_name
 ,TRC.EventSubclass
 ,TRC.TextData
 ,TRC.IntegerData
 ,TRC.* FROM OLAP.trace TRC
 INNER JOIN trace_events on TRC.EventClass = trace_events.trace_event_id 
 LEFT JOIN trace_events_subclass on TRC.EventClass = trace_events_subclass.trace_event_id and TRC.EventSubclass = trace_events_subclass.subclass_value
ORDER BY RowNumber
13 окт 11, 08:16    [11431153]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить