Home > FAQ > Posso importare un file di traccia del Profiler in una tabella SQL?

Posso importare un file di traccia del Profiler in una tabella SQL?

Davide Mauri 9 Gennaio 2008 FAQ 2,082 Views

Per non inficiare le performance di un server SQL, è sempre buona norma NON salvare la traccia del SQL Profiler direttamente in una tabella del SQL Server che si è intenti a monitorare, bensì salvarla in un file su disco che sarà poi possibile interrogare con T-SQL dopo averlo portato in una tabella SQL generata e popolata con l’istruzione

SELECT *
INTO my_table_name
FROM fn_trace_gettable ( <filename> , <number_files> )

dove:
– <filename> è il nome file di traccia, con tanto di percorso;
– <numeber_files> è un numero intero che identifica il numero di sequenza del file di traccia generato con l’opzione RollOver, la quale provoca uno split della traccia in tanti file sequenziali (filename.trc, filename_1.trc, filename_2.trc, …) di dimensione pari al massimo indicato in fase di impostazione del profilo di traccia (minimo 1 MB).
Alternativamente, <number_files> può essere valorizzato con la parola chiave ‘default’, che legge tutti i file della sequenza a partire al primo.

Una volta importata la traccia in tabella, viene persa l’informazione descrittiva della Classe dell’Evento registrata poichè convertita in un valore numerico intero il cui significato è però rintracciabile qui.
Di seguito uno script che genera una tabella il cui scopo è quello di ospitare tutte le descrizioni degli EventClass numerici.

 

 

CREATE TABLE [dbo].[EventClassDescription](
    [EventNumber] [int] PRIMARY KEY NOT NULL,
    [EventName] [nvarchar](255) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO

INSERT INTO EventClassDescription VALUES (10, ‘RPC:Completed’)
INSERT INTO EventClassDescription VALUES (11, ‘RPC:Starting’)
INSERT INTO EventClassDescription VALUES (12, ‘SQL:BatchCompleted’)
INSERT INTO EventClassDescription VALUES (13, ‘SQL:BatchStarting’)
INSERT INTO EventClassDescription VALUES (14, ‘Audit Login’)
INSERT INTO EventClassDescription VALUES (15, ‘Audit Lout’)
INSERT INTO EventClassDescription VALUES (16, ‘Attention’)
INSERT INTO EventClassDescription VALUES (17, ‘ExistingConnection’)
INSERT INTO EventClassDescription VALUES (18, ‘Audit Server Starts and Stops’)
INSERT INTO EventClassDescription VALUES (19, ‘DTCTransaction’)
INSERT INTO EventClassDescription VALUES (20, ‘Audit Login Failed’)
INSERT INTO EventClassDescription VALUES (21, ‘EventLog’)
INSERT INTO EventClassDescription VALUES (22, ‘ErrorLog’)
INSERT INTO EventClassDescription VALUES (23, ‘Lock:Released’)
INSERT INTO EventClassDescription VALUES (24, ‘Lock:Acquired’)
INSERT INTO EventClassDescription VALUES (25, ‘Lock:Deadlock’)
INSERT INTO EventClassDescription VALUES (26, ‘Lock:Cancel’)
INSERT INTO EventClassDescription VALUES (27, ‘Lock:Timeout’)
INSERT INTO EventClassDescription VALUES (28, ‘Degree of Parallelism Event (7.0 Insert)’)
INSERT INTO EventClassDescription VALUES (33, ‘Exception’)
INSERT INTO EventClassDescription VALUES (34, ‘SP:CacheMiss’)
INSERT INTO EventClassDescription VALUES (35, ‘SP:CacheInsert’)
INSERT INTO EventClassDescription VALUES (36, ‘SP:CacheRemove’)
INSERT INTO EventClassDescription VALUES (37, ‘SP:Recompile’)
INSERT INTO EventClassDescription VALUES (38, ‘SP:CacheHit’)
INSERT INTO EventClassDescription VALUES (39, ‘Obsoleto’)
INSERT INTO EventClassDescription VALUES (40, ‘SQL:StmtStarting’)
INSERT INTO EventClassDescription VALUES (41, ‘SQL:StmtCompleted’)
INSERT INTO EventClassDescription VALUES (42, ‘SP:Starting’)
INSERT INTO EventClassDescription VALUES (43, ‘SP:Completed’)
INSERT INTO EventClassDescription VALUES (44, ‘SP:StmtStarting’)
INSERT INTO EventClassDescription VALUES (45, ‘SP:StmtCompleted’)
INSERT INTO EventClassDescription VALUES (46, ‘Object:Created’)
INSERT INTO EventClassDescription VALUES (47, ‘Object:Deleted’)
INSERT INTO EventClassDescription VALUES (50, ‘SQL Transaction’)
INSERT INTO EventClassDescription VALUES (51, ‘Scan:Started’)
INSERT INTO EventClassDescription VALUES (52, ‘Scan:Stopped’)
INSERT INTO EventClassDescription VALUES (53, ‘CursorOpen’)
INSERT INTO EventClassDescription VALUES (54, ‘TransactionLog’)
INSERT INTO EventClassDescription VALUES (55, ‘Hash Warning’)
INSERT INTO EventClassDescription VALUES (58, ‘Auto Stats’)
INSERT INTO EventClassDescription VALUES (59, ‘Lock:Deadlock Chain’)
INSERT INTO EventClassDescription VALUES (60, ‘Lock:Escalation’)
INSERT INTO EventClassDescription VALUES (61, ‘OLE DB Errors’)
INSERT INTO EventClassDescription VALUES (67, ‘Execution Warnings’)
INSERT INTO EventClassDescription VALUES (68, ‘Showplan Text (Unencoded)’)
INSERT INTO EventClassDescription VALUES (69, ‘Sort Warnings’)
INSERT INTO EventClassDescription VALUES (70, ‘CursorPrepare’)
INSERT INTO EventClassDescription VALUES (71, ‘Prepare SQL’)
INSERT INTO EventClassDescription VALUES (72, ‘Exec Prepared SQL’)
INSERT INTO EventClassDescription VALUES (73, ‘Unprepare SQL’)
INSERT INTO EventClassDescription VALUES (74, ‘CursorExecute’)
INSERT INTO EventClassDescription VALUES (75, ‘CursorRecompile’)
INSERT INTO EventClassDescription VALUES (76, ‘CursorImplicitConversion’)
INSERT INTO EventClassDescription VALUES (77, ‘CursorUnprepare’)
INSERT INTO EventClassDescription VALUES (78, ‘CursorClose’)
INSERT INTO EventClassDescription VALUES (79, ‘Missing Column Statistics’)
INSERT INTO EventClassDescription VALUES (80, ‘Missing Join Predicate’)
INSERT INTO EventClassDescription VALUES (81, ‘Server Memory Change’)
INSERT INTO EventClassDescription VALUES (82, ‘User Configurable (0-9)’)
INSERT INTO EventClassDescription VALUES (83, ‘User Configurable (0-9)’)
INSERT INTO EventClassDescription VALUES (84, ‘User Configurable (0-9)’)
INSERT INTO EventClassDescription VALUES (85, ‘User Configurable (0-9)’)
INSERT INTO EventClassDescription VALUES (86, ‘User Configurable (0-9)’)
INSERT INTO EventClassDescription VALUES (87, ‘User Configurable (0-9)’)
INSERT INTO EventClassDescription VALUES (88, ‘User Configurable (0-9)’)
INSERT INTO EventClassDescription VALUES (89, ‘User Configurable (0-9)’)
INSERT INTO EventClassDescription VALUES (90, ‘User Configurable (0-9)’)
INSERT INTO EventClassDescription VALUES (92, ‘Data File Auto Grow’)
INSERT INTO EventClassDescription VALUES (93, ‘Log File Auto Grow’)
INSERT INTO EventClassDescription VALUES (94, ‘Data File Auto Shrink’)
INSERT INTO EventClassDescription VALUES (95, ‘Log File Auto Shrink’)
INSERT INTO EventClassDescription VALUES (96, ‘Showplan Text’)
INSERT INTO EventClassDescription VALUES (97, ‘Showplan All’)
INSERT INTO EventClassDescription VALUES (98, ‘Showplan Statistics Profile’)
INSERT INTO EventClassDescription VALUES (100, ‘RPC Output Parameter’)
INSERT INTO EventClassDescription VALUES (102, ‘Audit Statement GDR Event’)
INSERT INTO EventClassDescription VALUES (103, ‘Audit Object GDR Event’)
INSERT INTO EventClassDescription VALUES (104, ‘Audit AddLogin Event’)
INSERT INTO EventClassDescription VALUES (105, ‘Audit Login GDR Event’)
INSERT INTO EventClassDescription VALUES (106, ‘Audit Login Change Property Event’)
INSERT INTO EventClassDescription VALUES (107, ‘Audit Login Change Password Event’)
INSERT INTO EventClassDescription VALUES (108, ‘Audit Add Login to Server Role Event’)
INSERT INTO EventClassDescription VALUES (109, ‘Audit Add DB User Event’)
INSERT INTO EventClassDescription VALUES (110, ‘Audit Add Member to DB Role Event’)
INSERT INTO EventClassDescription VALUES (111, ‘Audit Add Role Event’)
INSERT INTO EventClassDescription VALUES (112, ‘Audit App Role Change Password Event’)
INSERT INTO EventClassDescription VALUES (113, ‘Audit Statement Permission Event’)
INSERT INTO EventClassDescription VALUES (114, ‘Audit Schema Object Access Event’)
INSERT INTO EventClassDescription VALUES (115, ‘Audit Backup/Restore Event’)
INSERT INTO EventClassDescription VALUES (116, ‘Audit DBCC Event’)
INSERT INTO EventClassDescription VALUES (117, ‘Audit Change Audit Event’)
INSERT INTO EventClassDescription VALUES (118, ‘Audit Object Derived Permission Event’)
INSERT INTO EventClassDescription VALUES (119, ‘OLEDB Call Event’)
INSERT INTO EventClassDescription VALUES (120, ‘OLEDB QueryInterface Event’)
INSERT INTO EventClassDescription VALUES (121, ‘OLEDB DataRead Event’)
INSERT INTO EventClassDescription VALUES (122, ‘Showplan XML’)
INSERT INTO EventClassDescription VALUES (123, ‘SQL:FullTextQuery’)
INSERT INTO EventClassDescription VALUES (124, ‘Broker:Conversation’)
INSERT INTO EventClassDescription VALUES (125, ‘Deprecation Announcement’)
INSERT INTO EventClassDescription VALUES (126, ‘Deprecation Final Support’)
INSERT INTO EventClassDescription VALUES (127, ‘Exchange Spill Event’)
INSERT INTO EventClassDescription VALUES (128, ‘Audit Database Management Event’)
INSERT INTO EventClassDescription VALUES (129, ‘Audit Database Object Management Event’)
INSERT INTO EventClassDescription VALUES (130, ‘Audit Database Principal Management Event’)
INSERT INTO EventClassDescription VALUES (131, ‘Audit Schema Object Management Event’)
INSERT INTO EventClassDescription VALUES (132, ‘Audit Server Principal Impersonation Event’)
INSERT INTO EventClassDescription VALUES (133, ‘Audit Database Principal Impersonation Event’)
INSERT INTO EventClassDescription VALUES (134, ‘Audit Server Object Take Ownership Event’)
INSERT INTO EventClassDescription VALUES (135, ‘Audit Database Object Take Ownership Event’)
INSERT INTO EventClassDescription VALUES (136, ‘Broker:Conversation Group’)
INSERT INTO EventClassDescription VALUES (137, ‘Blocked Process Report’)
INSERT INTO EventClassDescription VALUES (138, ‘Broker:Connection’)
INSERT INTO EventClassDescription VALUES (139, ‘Broker:Forwarded Message Sent’)
INSERT INTO EventClassDescription VALUES (140, ‘Broker:Forwarded Message Dropped’)
INSERT INTO EventClassDescription VALUES (141, ‘Broker:Message Classify’)
INSERT INTO EventClassDescription VALUES (142, ‘Broker:Transmission’)
INSERT INTO EventClassDescription VALUES (143, ‘Broker:Queue Disabled’)
INSERT INTO EventClassDescription VALUES (146, ‘Showplan XML Statistics Profile’)
INSERT INTO EventClassDescription VALUES (148, ‘Deadlock Graph’)
INSERT INTO EventClassDescription VALUES (149, ‘Broker:Remote Message Acknowledgement’)
INSERT INTO EventClassDescription VALUES (150, ‘Trace File Close’)
INSERT INTO EventClassDescription VALUES (152, ‘Audit Change Database Owner’)
INSERT INTO EventClassDescription VALUES (153, ‘Audit Schema Object Take Ownership Event’)
INSERT INTO EventClassDescription VALUES (155, ‘FT:Crawl Started’)
INSERT INTO EventClassDescription VALUES (156, ‘FT:Crawl Stopped’)
INSERT INTO EventClassDescription VALUES (157, ‘FT:Crawl Aborted’)
INSERT INTO EventClassDescription VALUES (158, ‘Audit Broker Conversation’)
INSERT INTO EventClassDescription VALUES (159, ‘Audit Broker Login’)
INSERT INTO EventClassDescription VALUES (160, ‘Broker:Message Undeliverable’)
INSERT INTO EventClassDescription VALUES (161, ‘Broker:Corrupted Message’)
INSERT INTO EventClassDescription VALUES (162, ‘User Error Message’)
INSERT INTO EventClassDescription VALUES (163, ‘Broker:Activation’)
INSERT INTO EventClassDescription VALUES (164, ‘Object:Altered’)
INSERT INTO EventClassDescription VALUES (165, ‘Performance statistics’)
INSERT INTO EventClassDescription VALUES (166, ‘SQL:StmtRecompile’)
INSERT INTO EventClassDescription VALUES (167, ‘Database Mirroring State Change’)
INSERT INTO EventClassDescription VALUES (168, ‘Showplan XML For Query Compile’)
INSERT INTO EventClassDescription VALUES (169, ‘Showplan All For Query Compile’)
INSERT INTO EventClassDescription VALUES (170, ‘Audit Server Scope GDR Event’)
INSERT INTO EventClassDescription VALUES (171, ‘Audit Server Object GDR Event’)
INSERT INTO EventClassDescription VALUES (172, ‘Audit Database Object GDR Event’)
INSERT INTO EventClassDescription VALUES (173, ‘Audit Server Operation Event’)
INSERT INTO EventClassDescription VALUES (175, ‘Audit Server Alter Trace Event’)
INSERT INTO EventClassDescription VALUES (176, ‘Audit Server Object Management Event’)
INSERT INTO EventClassDescription VALUES (177, ‘Audit Server Principal Management Event’)
INSERT INTO EventClassDescription VALUES (178, ‘Audit Database Operation Event’)
INSERT INTO EventClassDescription VALUES (180, ‘Audit Database Object Access Event’)
INSERT INTO EventClassDescription VALUES (181, ‘TM: Begin Tran starting’)
INSERT INTO EventClassDescription VALUES (182, ‘TM: Begin Tran completed’)
INSERT INTO EventClassDescription VALUES (183, ‘TM: Promote Tran starting’)
INSERT INTO EventClassDescription VALUES (184, ‘TM: Promote Tran completed’)
INSERT INTO EventClassDescription VALUES (185, ‘TM: Commit Tran starting’)
INSERT INTO EventClassDescription VALUES (186, ‘TM: Commit Tran completed’)
INSERT INTO EventClassDescription VALUES (187, ‘TM: Rollback Tran starting’)
INSERT INTO EventClassDescription VALUES (188, ‘TM: Rollback Tran completed’)
INSERT INTO EventClassDescription VALUES (189, ‘Lock:Timeout (timeout > 0)’)
INSERT INTO EventClassDescription VALUES (190, ‘Progress Report: Online Index Operation’)
INSERT INTO EventClassDescription VALUES (191, ‘TM: Save Tran starting’)
INSERT INTO EventClassDescription VALUES (192, ‘TM: Save Tran completed’)
INSERT INTO EventClassDescription VALUES (193, ‘Background Job Error’)
INSERT INTO EventClassDescription VALUES (194, ‘OLEDB Provider Information’)
INSERT INTO EventClassDescription VALUES (195, ‘Mount Tape’)
INSERT INTO EventClassDescription VALUES (196, ‘Assembly Load’)
INSERT INTO EventClassDescription VALUES (198, ‘XQuery Static Type’)
INSERT INTO EventClassDescription VALUES (199, ‘QN: subscription’)
INSERT INTO EventClassDescription VALUES (200, ‘QN: parameter table’)
INSERT INTO EventClassDescription VALUES (201, ‘QN: template’)
INSERT INTO EventClassDescription VALUES (202, ‘QN: dynamics’)

 

Una volta creata e popolata con le varie INSERT, può essere interrogata comodamente con una JOIN esterna con la tabella di traccia importata con la condizione di JOIN:

SELECT [EventClassDescription].[EventName], [my_table_name].*

FROM [my_table_name]

LEFT OUTER JOIN [EventClassDescription]

ON [my_table_name].[EventClass] = [EventClassDescription].[EventNumber]

Tags

Chi è Davide Mauri

Microsoft Data Platform MVP dal 2007, Davide Mauri si occupa di Data Architecture e Big Data nel mondo dell'IoT. Attualmente ricopre il ruolo di "Director Software Development & Cloud Infrastructure" in Sensoria, societa specializzata nella creazione di Wearables e sensori per l'IoT applicati a tessuti ed oggetti sportivi.

Leggi Anche

Usare json su SQLServer 2005, 2008, 2012 e 2014 .. è possibile!

Vedere il supporto nativo di JSON su SQLServer 2016 (e anche su Azure SQLDatabase) mi …

Lascia un commento

Il tuo indirizzo email non sarà pubblicato. I campi obbligatori sono contrassegnati *

twenty nine − twenty seven =

Questo sito usa Akismet per ridurre lo spam. Scopri come i tuoi dati vengono elaborati.

User Group Italiano SQL Server - Via Del Capanno 26, Lodi - P.IVA: 03015800968
Tutti i contenuti (c) UGISS, 2024