Di recente mi è capitato di dover gestire un problema banale ma curioso, uno di quelli su cui pensi una volta risolto: “ecco questo per il futuro devo davvero ricordarmelo!”. Siccome è una di quelle cose che può essere utile a tutti nella vita di tutti i giorni ho pensato di condividerlo brevemente.
Il Problema: In breve questo quanto accaduto. Stavo provando ad aggiornare un’applicazione di tipo universal, basata su pacchetti, fatta con Visual Studio di cui ho pubblicato anche una versione sullo store. In passato avevo provato ad installarla anche in locale sul pc di casa che uso per sviluppare. Poi l’avevo rimossa. Dopo quest’azione mi era diventato impossibile avviare la versione di sviluppo in locale, che per funzionare deve essere distribuita localmente, proprio come fosse stata installata. Questo con errori anomali come l’errore 1104 che, da visual studio, indica che non è stato possibile accedere al percorso di delivery. Anche da Microsoft Store la versione dell’app pubblicata era diventata impossibile da installare
La Causa Per ragioni non ben definite l’applicazione in pratica risultava installata da un’utente diverso dal mio, da cui però era diventato impossibile rimuoverla. Forse si trattava di qualche prova fatta in passato con un’utenza non piu’ disponibile. I due strumenti quindi (Visual Studio e Microsoft Store) non avevano modo di rimuoverla in modo corretto
La Soluzione La soluzione piu’ rapida per il problema, una volta capito, è stata quella di forzare la rimozione dell’applicazione installata tramite PowerShell, usando utenza e console amministrative. Il comandi Powershell riportati di seguito in pratica consentono di rimuovere il pacchetto indicato per tutti gli utenti.
Procedura
Aprire una console PowerShell con diritti amministrativi
Trovare il nome del pacchetto da rimuovere. Questo lo si puo recuperare in diversi modi:
Dal messaggio di errore ricevuto
Cercando il suo nome nella cartella %USERPROFILE%\AppData\Local\Packages Ad ogni nome cartella corrisponde un pacchetto con lo stesso nome
Tramite la query PowerShell Get-AppxPackage -AllUsers
Una volta identificato il nome del pacchetto rimuoverlo semplicemente tramite il comando PowerShell Get-AppxPackage -Name "[NOMEPACCHETTO]" -AllUsers |Remove-AppxPackage -AllUsers
Oggi l’elemento principale, oltre che il piu costoso, di qualsiasi PC da Gaming e’ sicuramente la GPU… ed e’ purtroppo anche il piu rumoroso durante i carichi di lavoro elevati.
Nel mio caso, avendo il PC in salotto, questa dava non poco fastidio a chi mi stava intorno quando lo usavo a lungo, pertanto ho deciso di procedere ad un intervento drastico per abbassarne la rumorosita’.
Come tutti sappiamo le schede grafiche, almeno nella loro versione stock, dispongono in genere di dissipatore ad aria corredato da ottime ventole di raffreddamento. Queste ultime sono progettate per raffreddare il dissipatore, e quindi la scheda, mantenendola costantemente entro il giusto range termico operativo…. tuttavia lo fanno spesso generando non poco rumore.
Tale rumore e’ dovuto alla rotazione delle ventole che, per raggiungere lo scopo, devono raggiungere una velocita’ sufficente a muovere l’aria necessaria a raffreddare la scheda. Nel mio caso la mia Gigabyte RTX 2080 OC ne aveva ben 3, grazie al dissapatore WindForce…. ben fatto ed efficace, certo…. ma secondo me rumoroso sui carichi elevati.
Gigabyte RTX 2080 OC – Foto presa dall’unboxing al montaggio del 2019
Come risolvere quindi il problema?
La soluzione che ho scelto e’ stata quella di sostitiuire il dissipatore della scheda con un dissipatore a liquido di tipo all-in-one, l’NZXT Kraken X63 corredato dall’adattatore NZXT Kraken G12, necessario per il montaggio sulla scheda. In ultimo, per l’applicazione del dissipatore, ho scelto la pasta termica Artic MX-5
NZXT Kraken X63NZXT Kraken G12Artic MX-5
Questo approfittando del fatto che la scheda stava andando fuori garanzia pertanto potevo arrischiarmi a smontarla sapendo che, in ogni caso, il supporto in caso di guasto futuro al prodotto era comunque gia scaduto.
E’ importante infatti tenere presente questo punto proseguendo nell’articolo… Tutti i danni dovuti ad operazioni di smontaggio della scheda NON rientrano tra quelli coperti dalla garanzia del prodotto…. pertanto e’ bene ricordare che tutti i passaggi descritti di seguito, se vorrete anche voi applicare una soluzione simile, sono da farsi a vostro rischio e pericolo. Alcuni produttori sono piu tolleranti di altri in quanto vanno a constatare il tipo di danno prima di rifiutare un eventuale rimborso in garanza per malfunzionamento…. ma e’ bene chiarirsi le idee prima di applicare questo tipo di soluzione. Anzi…. scriviamolto anche in un bel disclaimer molto chiaro, giusto per chiarirci bene l’idea….
ATTENZIONE: Lo smontaggio di una scheda video implica la caduta di tutti i diritti di garanzia e supporto.
Chiarito questo punto, come dicevo, passare ad un dissipatore a liquido e’ stata la mia scelta per ridurre la rumorosita’. Perche’? Un dissipatore a liquido e’ costituito da un blocco da montarsi in prossimita del processore da raffreddare con pompa integrata (nel caso dei modelli all-in-one come questo). Questo blocco fa scorrere liquido di raffreddamento verso un radiatore. Il radiatore e’ a sua volta raffreddato da una serie di ventole…. ma queste hanno il grosso vantaggio, trovandosi in una zona ben piu ampia del case, di poter essere molto piu grandi di quelle che si potrebbero mettere su una scheda video. Il vantaggio e’ che possono girare molto piu lentamente garantendo quindi una rumorosita complessiva ridotta rispetto alla soluzione stock. La rumorosita’ quindi e’ azzerata? No, non lo e’ attenzione…. teniamolo bene presente da subito. Non si tratta di una soluzione a rumore zero in quanto la pompa e le ventole producono comunque un rumore…. ma quest’ultimo e’ tendezialmente inferiore in caso di carichi elevati rispetto a quello che avreste dalle ventole standard di una scheda video. Certo….ovviamente questo vale se avete ben dimensionato la soluzione con un radiatore di dimensioni adeguate. Per questo punto pero’, prima di fare scelte, vi raccomando comunque sempre di controllare i valori di rumorosita’ indicati dal produttore sia per la vostra scheda che per il dissipatore a liquido che state prendendo in considerazione.
Una volta ricevuti tutti i componenti sono quindi passato allo smontaggio del precedente dissipatore… la parte che probabilmente temevo maggiormente con la paura di fare qualche danno. Tuttavia, lavorando con delicatezza e calma, l’operazione e’ stata piu semplice del previsto. Rimosse le viti, facendo attenzione a staccare il vecchio dissipatore un po’ alla volta facendo solo minimamente leva dove necessario e staccando il connettore di alimentazione del dissipatore, i due componenti si sono separati senza troppi problemi.
Scheda e dissipatore separati
Il passo successivo e’ stato quello di rimuovere tutti gli adesivi (facendo attenzione a riposizionarli sulla scheda per eventuale rimontaggio del dissipatore, senza perderli) per poi passare alla pulizia della GPU con alchool e cotone idrofilo. I puristi mi scuseranno in quanto, nei laboratori che fanno questo genere di lavori, ci sono strumenti sicuramente piu appropriati (alchool isopropilico, strumenti di rimozione migliori…e sicuramente anche sistemi antistatici del caso) tuttavia questo era quello che avevo a disposizione e ha comunque fatto molto bene il suo mestiere
Scheda ripulita……e GPU ripulita
A questo punto quindi ho messo la pasta termica sul processore della scheda e sono passato al montaggio del dissipatore sulla scheda con l’adattatore G12. Qui il timore era inferiore ma comunque l’attenzione e’ stata molto alta temendo comunque di non farli aderire bene l’uno sull’altro.
Verifica di contatto corretto e uniforme tra blocco e GPU
Messe e tirate un po’ le viti pero’…. il grosso era fatto!
Scheda e AIO finalmente montati
Fatto questo sono passato al montaggio nel case (il mio NZXT H500) che seguendo bene le istruzioni non e’ stato difficile…. tuttavia raccomando anche qui di fare le cose con molta calma a chi volesse cimentarsi, perche’ il posizionamento richiede un po’ di attenzione. I tubi che portano al radiatore non sono lunghissimi…. hanno diciamo “la misura giusta”. Pertanto farli passare nel modo corretto e’ indispensabile per riuscire a posizionare il radiatore all’interno del case.
Vista interna del case. Radiatore montato sulla destra in posizione anteriore con ventole girate verso l’esterno. A sinistra la scheda con il blocco orientato verso il basso.
Una volta connessi i vari cavi delle pompa oltre che i cavetti delle ventole ho provato ad avviare il tutto….che fortunosamente e’ partito senza probblemi facendomi tirare il classico sospiro di sollievo. A quel punto il passo successivo e’ stata l’installazione e la configurazione del software NZXT CAM per gestire e monitorare il Kraken.
Di seguito pero’ non era tutto finito. E’ importante ricordare che ho dovuto passare un bel po’ di tempo per regolare bene la velocita’ delle ventole del case (collegate alla motherboard), e la velocita ‘dell’AIO (il Kraken) anch’esso collegato alla Mobo per adeguare tutto il sistema al nuovo componente aggiunto. Riducendo la temperatura complessiva nel case infatti ho potuto quindi ridure anche la velocita’ delle altre ventole raggiungendo quindi temperature di esercizio basse associate a rumorosita’ ridotta. Un buon esercizio fatto tenendo bene d’occhio i valori di temperatura dei vari componenti tramite la soluzione di monitoring che gia vi ho descritto nell’articolo che trovate QUI. Un altro punto di attenzione e’ stato configurare il Kraken in modo che la sua velocita’ fosse legat alla temperatura della GPU e non a quella della CPU (che e’ li default). Questo in particolare e’ una configurazione abbastanza immediata che (ora che sapete che esiste… :)) trovate nel pannello di controllo del software NZXT CAM.
E il rumore? Nei test fatti con il mio fonometro ho registrato una riduzione di almeno -4dB… nella rumorosita’ complessiva (che non sono pochi!!) che migliora ulteriormente nelle situazioni di alto carico. Questo si associa ad un drastico abbassamento della temperatura di almeno 20 gradi per la GPU. Sono quelle cose che ti fanno dire…. Perche’ non l’ho fatto prima???
Conclusioni Se siete arrivati fin qui a leggere questo articolo la conclusione sembra ovvia. Abbattimento di rumore e temperatura sono tutti scopi pienamente raggiunti. Ma lo consiglierei a tutti? La risposta un po’ meno scontata e che va ovviamente argomentata e’…“dipende”. Nel mio caso la scheda video era uscita dalla garanzia pertanto non avevo timore di perdere il supporto in caso di problemi col prodotto. Ma se cosi non fosse stato magari avrei quantomeno aspettato…anche perche’ le operazioni di smontaggio e montaggio che avete visto non sono poche ed era la prima volta che facevo questo lavoro (anche se ho altre esperienze del genere alle spalle). Poi, come per tutte le cose, dipende da quanto siete confidenti nel fare questo tipo di operazioni rispetto al vostro portafogli. Rompere una scheda da 700 euro (quando l’ho presa… ormai vale molto meno….) mentre montate un dissipatore e’ una cosa che non vi fa stare tranquilli? Allora non fate un intervento del genere. Non avete dimestichezza ma avete un fornitore che fa per voi questo lavoro e magari vi garantisce anche la garanzia? Allora fatelo, i vantaggi sono indiscussi! Fatte tutte queste premesse, comunque, la mia personale esperienza che condivido e’ che sono sicuramente estremamente soddisfatto sia dei prodotti che del risultato raggiunto.
Come vedete i fattori da valutare sono diversi ma spero comunque di avervi dato, con la descrizione della mia esperienza, tutti gli elmenti utili per poter pianificare la vostra scelta. Ma sopratutto…. se doveste decidere di imbarcarvi in questa attivita’….divertitevi perche’ il tuning e’ una di quelle cose che, se siete arrivati fin qui a leggere, sono sicuro da estremamente soddisfazione anche a voi come ne da a me….e questo non a lavoro finito ma proprio mentre lo pianificate, fate le vostre scelte e poi magari decidete di procedere.
Quindi, in ogni caso… buon viaggio 🙂
p.s. un grazie all’amico Goozo (al secolo Daniele :)) che si e’ lanciato per primo in questa attivita’ sul suo pc e mi ha convinto che era fattibile senza troppi rischi.
Alcuni articoli del supporto Ms sono sempre utili e conviene davvero averli pronti sottomano in caso di bisogno. Nel mio caso, come purtroppo a volte accade, il “caso” si è verificato il giorno in cui la mia workstation (Windows 10 pro x64) ha cominciato a bloccarsi in modo completamente inaspettato.
Il blocco si manifestava in modi diversi che sono andati peggiorando con il tempo. In alcuni casi si trattava del completo congelamento dell’interfaccia, senza alcuna attività apparente della macchina. L’unica cosa fattibile era spegnere e riaccendere il pc.
In altri casi invece, diventati via via più frequenti, la macchina si bloccava completamente con la purtroppo famigerata schermata blu contenenti poche informazioni utili al troubleshooting.
I codici di errore riportati non erano oltre tutto costanti e rimbalzavano dal:
IRQ NOT LESS OR EQUAL
al
PAGE FAULT IN NONPAGED AREA
Chi è avvezzo al troubleshooting dei sistemi Windows sa che purtroppo questi due messaggi sono tipici di due macro tipologie di problemi dietro i quali può essere successo più o meno di tutto: problemi di driver o problemi di memoria.
Da qui quindi è partita la mia indagine.
1) Sono state fatte installazioni/aggiornamenti di recente?
Purtroppo windows 10 fa aggiornamenti in autonomia pertanto la risposta a questa domanda non è così immediata. Per verificarlo comunque è bastato un passaggio su gestione aggiornamenti dove ho verificato che non vi erano stati aggiornamenti particolari oltre alle ultime definizioni di Windows Defender
2) Il sistema riporta errori o problemi inattesi nei log?
Secondo passaggio: verifica in Event viewver. Anche qui nulla di particolare se non degli errori ricorrenti dovuti forse a qualche disinstallazione non andata proprio a buon fine. Questo è forse uno dei punti più “ambigui” dell’analisi in quanto i messaggi potenzialmente preoccupanti possono essere davvero molti. Ho perso molto tempo a risolvere messaggi di errore che nulla avevano a che fare con il mio problema. Male certo non fa… ma è frustrante ritrovarsi a distanza di qualche ora dopo il lavoro di nuovo con lo stesso problema. Unico suggerimento: ignorate gli errori ricorrenti precedenati al problema o, quanto meno, lasciateli in secondo piano e verificateli solo in un secondo momento
3) Il sistema riporta anomalie a livello di periferiche/driver?
Un bel giro in gestione risorse risolve rapidamente questo dubbio. Se nessuna periferica riporta anomalie è comunque consigliabile una verifica della presenza di eventuali driver aggiornati che coprano eventuali bug delle versioni precedenti. Sopratutto se di recente avete aggiunto qualche periferica!
4) Il sistema operativo è integro o riporta anomalie a livello di file, grant, etc… ?
Questo è il punto più difficile da verificare ma Ms ci viene in aiuto con 2 tool: sfc.exe (System File Check utility) e dism.exe (Distributed Image System Maintenance) entrambi disponibili nel sistema operativo. Eccone le descrizioni.
Questa è un’ipotesi remota ma è comunque meglio verificarla in modo da scartarla a priori. Windows usa come memoria ausiliaria il file di paging che a sua volta è ospitato su uno dei dischi fissi della macchina. Un problema sul disco che ospita questo file potrebbe tradursi in uno degli errori sopra indicati. Ipotesi remota ma lanciare un check dei dischi, in particolare quello di sistema dove c’è il file di paging, male non fa. Possiamo farlo con tramite gli strumenti grafici (pulsante destro sul disco, “Strumenti”, “Controllo disco”) oppure tramite il vetusto ma sempre affidabile chkdsk
chkdsk [volume] /scan
Maggiori dettagli sul comando anche qui potete trovarli sul supporto Ms:
6) Il problema permane? Allora purtroppo potrebbe trattarsi davvero di un problema di RAM hardware…
Per questa verifica Ms ci mette a disposizione un ulteriore too di diagnostica predisposto per lo scopo: MdSched.exe (Strumento Diagnostica Memoria Windows). Il check della memoria per ovvie ragioni può essere verificato solo da tool appositi eseguiti prima dell’avvio del sistema operativo pertanto, con questo strumento, sarà possibile schedulare un checò imediato al prossimo riavvio di windows. Il tool ha diverse modalità di funzionamento (Base,Standard e Advanced) ma la modalità di deafult (Standard) da documentazione risulta già sufficente alla nostra verifica.
Sempre da un promth con privilegi elevati (o tramite la ricerca) lanciamo MdSched.exe
In ogni caso il check parte automaticamente e basta lasciarlo andare. Se tutto va bene, dopo la conclusione e successivo riavvio, troverete in Event Viewver un messaggio con il risultato del test.
Se invece qualcosa non va il software potrebbe indicarvi un problema su uno dei moduli di memoria oppure addirittura bloccarsi durante il test.
Cosa si fa a quel punto? Come nel mio caso (*sigh*) l’unica cosa che potete fare è togliere i moduli di memoria rilanciando il test fino a quando non riuscirete a capire quale sia il modulo guasto. Consigliabile in particolare fare tentativi di riavvio togliendo prima le memorie di un canale e poi quelle dell’altro. A seguire scambiate i moduli finchè non capite qual’è quello guasto.
Nel mio caso, alla fine dell’analisi, ho salutato con tutti gli onori del caso, un glorioso ma ormai vetusto e difettoso modulo RAM Samsung da 1Gb.
In this procedure we’ll manage one of the worst situation a DBA has to manage: corrupted files and data loss. When this heppen usually the common way is restoring but we’ll use sql server features to reduce stop time (avoiding a complete restore) and data loss.
Possible starting problems:
Corrupted logfile
Corrupted logfile during a long transaction
Logfile volume corrupted or lost during transactions
At this point there are different solutions following current database settings:
SCENARIO 1: No transactions running during crash. Solution:
If no transactions were running at crash point the solution is easy.This because SQL server rebuild automatically lost log file during database startup. So:
1) Detach corrupted database
2) Rename the old corrupted logfile in *.OLD
3) Attach database using:
CREATE DATABASE [MYDATABASE] ON ( FILENAME = N'D:Microsoft SQL ServerYourDataPathDataDatabase.mdf' ) FOR ATTACH_REBUILD_LOG GO
Notes:
- SQL Server will try to rebuild log file in the ORIGINAL path.
SCENARIO 2: Transactions running during crash Solution:
ATTACH_REBUILD_LOG in this situation *IS NOT* allowed because SQL Server find open transactions in the database and pending rollback/rollforward operations. So you’ll find the following error trying:
“File activation failure. The physical file name “D:Microsoft SQL ServerYourDataPathDataLogfile.ldf” may be incorrect.
The log cannot be rebuilt because there were open transactions/users when the database was shutdown, no checkpoint occurred to the database, or the database was read-only. This error could occur if the transaction log file was manually deleted or lost due to a hardware or environment failure. Msg 1813, Level 16, State 2, Line 1
Could not open new database ‘MYDATABASE’. CREATE DATABASE is aborted. “
So, follow this procedure:
1) DETACH DATABASE MyDatabase
2) Rename datafile and logfile in MDF.OLD and LDF.OLD
3) Create a new database with THE SAME name and identical original datafile and logfile position. I
4) ALTER DATABASE MyDatabase SET OFFLINE
5) Now you can put the original datafile in the original position
6) ALTER DATABASE MyDatabase SET ONLINE. This will fail but now we’ll can rebuild the log file
7) ALTER DATABASE [MyDatabase ] REBUILD LOG ON (NAME=’MyDatabaseLog’,FILENAME=’D:Microsoft SQL ServerYourDataPathDataLogfile.ldf’)
At this point the database will be usable but SQL Server at the end will show this warning: Warning: The log for database ‘MyDatabase’ has been rebuilt. Transactional consistency has been lost. The RESTORE chain was broken, and the server no longer has context on the previous log files, so you will need to know what they were. You should run DBCC CHECKDB to validate physical consistency. The database has been put in dbo-only mode. When you are ready to make the database available for use, you will need to reset database options and delete any extra log files.
8) Final Step: open the database to users: ALTER DATABASE [nomedb] SET MULTI_USER
Notes:
– In recovery model FULL make a new FULL BACKUP as soon as possible because the RESTORE chain is broken and you need a new baseline for log backup.
– *Ask to double-check application consistency* because data recovered could be NOT consistent at application level. (we have done an uncomplete recover). If applicaton checks fails and nothing is fixable rapidly at application levele you have to consider, at the end, only a complete restore.
Using sys.dm_os_wait_stat is not useful for deep troubleshooting because this view contains wait events for ALL processes/queries running on your instance since last restart. Using command “DBCC SQLPERF (‘sys.dm_os_wait_stats’, CLEAR)” we can reset this view with no restart but on big and stressed instances with many processes and applications running this isn’t a real good solution.
SQL 2008 introduce a new powerful and flexible way to collect performance data: Extended Events.
With EE we can collect performance data for a wide range of counters with almost any filter we want.
The following procedure collect and manage asyncronously data of a SINGLE session.
Using this solution you can collect wait events infos of your session while testing or wait events infos about a single user or application.
NOTE: Stats are collected in a fs folder, so watch carefully space used growing while collecting.
--- 1) Drop the monitor session if it exists.
IF EXISTS (SELECT * FROM sys.server_event_sessions WHERE name = 'WaitMon')
DROP EVENT SESSION WaitMon ON SERVER
GO
--- 2) Create the new monitor session.
CREATE EVENT SESSION WaitMon ON SERVER
ADD EVENT sqlos.wait_info
(WHERE sqlserver.session_id = 79 ) ---INSERT HERE the session id to monitor
ADD TARGET package0.asynchronous_file_target
(SET FILENAME = N'S:tempEE_WaitMonStats.xel', ---INSERT HERE the correct path for collected data stats
METADATAFILE = N'S:tempEE_WaitMonStats.xem')
WITH (max_dispatch_latency = 1 seconds);
GO
--- 3) Start the Monitor session
ALTER EVENT SESSION WaitMon ON SERVER STATE = START;
GO
--- 4) >>>>>>...run your query or wait for data collection from spid session....<<<<<<
--- 5) Stop the Monitor session
ALTER EVENT SESSION WaitMon ON SERVER STATE = STOP;
GO
--- 6) Load collected data in a temp table
CREATE TABLE #RawEventData (
Rowid INT IDENTITY PRIMARY KEY,
event_data XML);
GO
INSERT INTO #RawEventData(event_data)
SELECT
CAST (event_data AS XML) AS event_data
FROM sys.fn_xe_file_target_read_file
('S:tempEE_WaitMonStats*.xel', ---INSERT HERE the correct path for collected data stats
'S:tempEE_WaitMonStats*.xem', null, null);
GO
--- 7) Query data to analize wait events
SELECT
waits.[Wait Type],
COUNT (*) AS [Wait Count],
SUM (waits.[Duration]) AS [Total Wait Time (ms)],
SUM (waits.[Duration]) - SUM (waits.[Signal Duration]) AS [Total Resource Wait Time (ms)],
SUM (waits.[Signal Duration]) AS [Total Signal Wait Time (ms)]
FROM
(SELECT
event_data.value ('(/event/@timestamp)[1]', 'DATETIME') AS [Time],
event_data.value ('(/event/data[@name=''wait_type'']/text)[1]', 'VARCHAR(100)') AS [Wait Type],
event_data.value ('(/event/data[@name=''opcode'']/text)[1]', 'VARCHAR(100)') AS [Op],
event_data.value ('(/event/data[@name=''duration'']/value)[1]', 'BIGINT') AS [Duration],
event_data.value ('(/event/data[@name=''signal_duration'']/value)[1]', 'BIGINT') AS [Signal Duration]
FROM #RawEventData
) AS waits
WHERE waits.[op] = 'End'
GROUP BY waits.[Wait Type]
ORDER BY [Total Wait Time (ms)] DESC;
GO
--- 8) Cleanup
DROP TABLE #RawEventData;
GO
Quering default trace is the best way to investigate unusual or critical events heppened in SQL server and not logged in errorlog files.
It’s not difficult to find useful informations there but the trace is full of codes to translate to make it more readable and useful. This is my query, based on sys.fn_trace_gettable function and sys.trace_events system table.
DECLARE @TraceFileName NVARCHAR(512) --Find the trace file name
SELECT @TraceFileName = path
FROM sys.traces
WHERE id = 1
SELECT StartTime, --then we can quering, translating the EventClass
TE.name as EventClass ,
TextData,
HostName,
ApplicationName,
LoginName,
SPID, --this is the SPID of the session:
ObjectName --can be used for following analysis
FROM sys.fn_trace_gettable(@TraceFileName,default) TG
left join
sys.trace_events TE on TG.EventClass=TE.trace_event_id
where TE.name is not null
If you are looking for the query that generated an event (log file grow, deleted object and so on…) you can look for it quering the inputbuffer for the SPID.
NOTE: the SPID can be re-used during the instance life and the inputbuffer is cleaned and re-used for following queries
dbcc inputbuffer([SPID])
The following is the full list of events available to filter in the previous query if you need to monitor a particular kind of event.
Not all are used in the default trace. If you need not traced event you have to create a custom trace.
select trace_event_id,name from sys.trace_events
trace_event_id name
-------------- -------------------------------
10 RPC:Completed
11 RPC:Starting
12 SQL:BatchCompleted
13 SQL:BatchStarting
14 Audit Login
15 Audit Logout
16 Attention
17 ExistingConnection
18 Audit Server Starts And Stops
19 DTCTransaction
20 Audit Login Failed
21 EventLog
22 ErrorLog
23 Lock:Released
24 Lock:Acquired
25 Lock:Deadlock
26 Lock:Cancel
27 Lock:Timeout
28 Degree of Parallelism
33 Exception
34 SP:CacheMiss
35 SP:CacheInsert
36 SP:CacheRemove
37 SP:Recompile
38 SP:CacheHit
40 SQL:StmtStarting
41 SQL:StmtCompleted
42 SP:Starting
43 SP:Completed
44 SP:StmtStarting
45 SP:StmtCompleted
46 Object:Created
47 Object:Deleted
50 SQLTransaction
51 Scan:Started
52 Scan:Stopped
53 CursorOpen
54 TransactionLog
55 Hash Warning
58 Auto Stats
59 Lock:Deadlock Chain
60 Lock:Escalation
61 OLEDB Errors
67 Execution Warnings
68 Showplan Text (Unencoded)
69 Sort Warnings
70 CursorPrepare
71 Prepare SQL
72 Exec Prepared SQL
73 Unprepare SQL
74 CursorExecute
75 CursorRecompile
76 CursorImplicitConversion
77 CursorUnprepare
78 CursorClose
79 Missing Column Statistics
80 Missing Join Predicate
81 Server Memory Change
82 UserConfigurable:0
83 UserConfigurable:1
84 UserConfigurable:2
85 UserConfigurable:3
86 UserConfigurable:4
87 UserConfigurable:5
88 UserConfigurable:6
89 UserConfigurable:7
90 UserConfigurable:8
91 UserConfigurable:9
92 Data File Auto Grow
93 Log File Auto Grow
94 Data File Auto Shrink
95 Log File Auto Shrink
96 Showplan Text
97 Showplan All
98 Showplan Statistics Profile
100 RPC Output Parameter
102 Audit Database Scope GDR Event
103 Audit Schema Object GDR Event
104 Audit Addlogin Event
105 Audit Login GDR Event
106 Audit Login Change Property Event
107 Audit Login Change Password Event
108 Audit Add Login to Server Role Event
109 Audit Add DB User Event
110 Audit Add Member to DB Role Event
111 Audit Add Role Event
112 Audit App Role Change Password Event
113 Audit Statement Permission Event
114 Audit Schema Object Access Event
115 Audit Backup/Restore Event
116 Audit DBCC Event
117 Audit Change Audit Event
118 Audit Object Derived Permission Event
119 OLEDB Call Event
120 OLEDB QueryInterface Event
121 OLEDB DataRead Event
122 Showplan XML
123 SQL:FullTextQuery
124 Broker:Conversation
125 Deprecation Announcement
126 Deprecation Final Support
127 Exchange Spill Event
128 Audit Database Management Event
129 Audit Database Object Management Event
130 Audit Database Principal Management Event
131 Audit Schema Object Management Event
132 Audit Server Principal Impersonation Event
133 Audit Database Principal Impersonation Event
134 Audit Server Object Take Ownership Event
135 Audit Database Object Take Ownership Event
136 Broker:Conversation Group
137 Blocked process report
138 Broker:Connection
139 Broker:Forwarded Message Sent
140 Broker:Forwarded Message Dropped
141 Broker:Message Classify
142 Broker:Transmission
143 Broker:Queue Disabled
144 Broker:Mirrored Route State Changed
146 Showplan XML Statistics Profile
148 Deadlock graph
149 Broker:Remote Message Acknowledgement
150 Trace File Close
151 Database Mirroring Connection
152 Audit Change Database Owner
153 Audit Schema Object Take Ownership Event
154 Audit Database Mirroring Login
155 FT:Crawl Started
156 FT:Crawl Stopped
157 FT:Crawl Aborted
158 Audit Broker Conversation
159 Audit Broker Login
160 Broker:Message Undeliverable
161 Broker:Corrupted Message
162 User Error Message
163 Broker:Activation
164 Object:Altered
165 Performance statistics
166 SQL:StmtRecompile
167 Database Mirroring State Change
168 Showplan XML For Query Compile
169 Showplan All For Query Compile
170 Audit Server Scope GDR Event
171 Audit Server Object GDR Event
172 Audit Database Object GDR Event
173 Audit Server Operation Event
175 Audit Server Alter Trace Event
176 Audit Server Object Management Event
177 Audit Server Principal Management Event
178 Audit Database Operation Event
180 Audit Database Object Access Event
181 TM: Begin Tran starting
182 TM: Begin Tran completed
183 TM: Promote Tran starting
184 TM: Promote Tran completed
185 TM: Commit Tran starting
186 TM: Commit Tran completed
187 TM: Rollback Tran starting
188 TM: Rollback Tran completed
189 Lock:Timeout (timeout > 0)
190 Progress Report: Online Index Operation
191 TM: Save Tran starting
192 TM: Save Tran completed
193 Background Job Error
194 OLEDB Provider Information
195 Mount Tape
196 Assembly Load
198 XQuery Static Type
199 QN: Subscription
200 QN: Parameter table
201 QN: Template
202 QN: Dynamics
212 Bitmap Warning
213 Database Suspect Data Page
214 CPU threshold exceeded
215 PreConnect:Starting
216 PreConnect:Completed
217 Plan Guide Successful
218 Plan Guide Unsuccessful
235 Audit Fulltext
Those queries are useful to admin MySQL instances using mysql client.
1) Check active processes/connections running
mysql> show processlist;
the previous statement doesn’t show all queries text running. To see the complete statements running use:
mysql> show processlist;
--or--
mysql> show full processlist G;
the running queries statements could bee quite long. Use the “G” modifier to see processes in different output paragraphs
2) Check UPTIME
MySql store main infos under metadata views querable using “SHOW STATUS” command. Those are some of the most quick and useful.
mysql> show status like '%uptime%';
+---------------------------+--------+
| Variable_name | Value |
+---------------------------+--------+
| Uptime | 880329 |
| Uptime_since_flush_status | 8347 |
+---------------------------+--------+
2 rows in set (0.01 sec)
4) Check Caching Status
Caching mechanism are managed using startup parameters. First you have to check if caching is enabled:
mysql> show variables like '%query_cache%';
+------------------------------+----------+
| Variable_name | Value |
+------------------------------+----------+
| have_query_cache | YES |
| query_cache_limit | 2097152 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 33554432 |
| query_cache_type | ON |
+------------------------------+----------+
5 rows in set (0.00 sec)
If query caching is enabled caching metadata infos are visible simply in this way.
mysql> show status like 'QCache%';
+-------------------------+----------+
| Variable_name | Value |
+-------------------------+----------+
| Qcache_free_blocks | 957 |
| Qcache_free_memory | 15523704 |
| Qcache_hits | 1978478 |
| Qcache_inserts | 132298 |
| Qcache_lowmem_prunes | 6037 |
| Qcache_not_cached | 5969 |
| Qcache_queries_in_cache | 3389 |
| Qcache_total_blocks | 8396 |
+-------------------------+----------+
8 rows in set (0.00 sec)
5) Check Instance Wait Time using PERFORMANCE_SCHEMA
Starting with release 5.5 MySql introduce the new performance_schema. Now performance critical conditions can be analized quering a group of system tables realtime dinamically updated.
First we’ll check if performance_schema is turned on. This option can be turned on using startup parameter file.
mysql> SHOW VARIABLES LIKE 'performance_schema';
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| performance_schema | ON |
+--------------------+-------+
If performance_schema is started there are many tables available to check your instance status (use SHOW TABLES under performance schema to see the complete list). Here we’ll start with one of the most simple, used to analize instance wait conditions in realtime:
A simple query to check rapidly your job’s status and duration.
Useful for fast monitoring on many instances.
No more thing s to say: this is the code based on msdb..sysjobs and msdb..sysjobhistory. It’s easy if necessary filtering a single job id or jobs durations too long.
select job_id,
job_name,
run_datetime,
SUBSTRING(run_duration, 1, 2) + ':' + SUBSTRING(run_duration, 3, 2) + ':' +
SUBSTRING(run_duration, 5, 2) AS run_duration,
convert(int,SUBSTRING(run_duration, 1, 2))*60 +
convert(int,SUBSTRING(run_duration, 3, 2)) as [min],
convert(float,SUBSTRING(run_duration, 5, 2)) as [sec]
from
(
select j.job_id,
job_name,
DATEADD(hh, -7, run_datetime) as run_datetime,
run_duration = RIGHT('000000' + CONVERT(varchar(6), h.run_duration), 6)
from
(
select
j.name as job_name,
run_datetime = max(CONVERT(DATETIME, RTRIM(run_date)) +
(run_time * 9 + run_time % 10000 * 6 + run_time % 100 * 10) / 216e4)
from msdb..sysjobhistory h
inner join msdb..sysjobs j
on h.job_id = j.job_id
group by j.name
) t
inner join msdb..sysjobs j
on t.job_name = j.name
inner join msdb..sysjobhistory h
on j.job_id = h.job_id and
t.run_datetime = CONVERT(DATETIME, RTRIM(h.run_date)) + (h.run_time * 9 + h.run_time % 10000 * 6 + h.run_time % 100 * 10) / 216e4
) dt
--where job_Id=@job_id
It’s a boring job but sometimes a good DBA has to do it.
Applications change and you have to understand what become unuseful in your databases: we are talking about unused indexes. In any SQL server database indexes take up a lot of space and have to be updated every time an application runs an update on an indexed table. You have to rebuild and reorganize them… and you have to backup them, every day.
SQL Server gives you a good instrument to understand which indexes are really used. SYS.DM_DB_INDEX_USAGE_STATS is a dynamic management view used by SQL engine to collect information about this.
The first time a new index is used SQL server adds a new line to this table with many different counters. Those counters are used to collect data about the index every time it’s used. On every instance restart SQL reset index counters.
Querying this view is easy to understad which indexes are really used:
– Indexes not listed here are never used
– Indexes with blank counters are never used since the last instance restart
Those are a queries ready to make this kind of analisys. Remembar that not every application use alway ALL their indexes. Some indexes are used only when specific application functions are turned on. For this reason talk ALWAYS with application support guys before dropping anything.
--Indexes Never Used
DECLARE @dbid INT
SELECT @dbid = DB_ID(DB_NAME())
SELECT OBJECTNAME = OBJECT_NAME(I.OBJECT_ID),
INDEXNAME = I.NAME,
I.INDEX_ID
FROM SYS.INDEXES I
JOIN SYS.OBJECTS O
ON I.OBJECT_ID = O.OBJECT_ID
WHERE OBJECTPROPERTY(O.OBJECT_ID,'IsUserTable') = 1
AND I.INDEX_ID NOT IN (
SELECT S.INDEX_ID
FROM SYS.DM_DB_INDEX_USAGE_STATS S
WHERE S.OBJECT_ID = I.OBJECT_ID
AND I.INDEX_ID = S.INDEX_ID
AND DATABASE_ID = @dbid)
ORDER BY OBJECTNAME,
I.INDEX_ID,
INDEXNAME ASC
GO
--Indexes never used since the last restart
DECLARE @dbid INT
SELECT @dbid = DB_ID(DB_NAME())
SELECT u.*
FROM [sys].[indexes] i
JOIN [sys].[objects] o
ON (i.OBJECT_ID = o.OBJECT_ID)
LEFT JOIN [sys].[dm_db_index_usage_stats] u
ON (i.OBJECT_ID = u.OBJECT_ID)
AND i.[index_id] = u.[index_id]
AND u.[database_id] = @dbid
WHERE o.[type] <> 'S'
AND i.[type_desc] <> 'HEAP'
AND u.[user_seeks] + u.[user_scans] + u.[user_lookups] = 0
ORDER BY i.indexname asc
1) Blocked And Blocking queries.
If this query returns no rows you have no blocked queries in this moment. Run it more then once to see any few-seconds blocking queries. NOTE: This exclude ONLY problems with long-locking running queries. Cumulative short-term locking contentions need other kinds of debug (see point 2)
SELECT 'BLOCKING STATUS' as Controllo,
BlockedSPID=left(blocked.session_id,5) ,
BlockedQuery=convert(varchar(50),blockedsql.text),
BlockingSPID=convert(varchar(50),blocking.session_id),
BlockingQuery=convert(varchar(50),blockingsql.text)
FROM sys.dm_exec_requests blocked
JOIN sys.dm_exec_requests blocking
ON blocked.blocking_session_id = blocking.session_id
CROSS APPLY
(
SELECT *
FROM sys.dm_exec_sql_text(blocked.sql_handle)
) blockedsql
CROSS APPLY
(
SELECT *
FROM sys.dm_exec_sql_text(blocking.sql_handle)
) blockingsql
GO
2) Time-Wait analysis
SQL Server collects informations about time wait events of your instance for every session. Every event (IO,CPU Processing,Locking and so on) is collected and showed in some dynamic management views from instance start/restart. To see what’s heppening now you can reset one af this views and collect for a short time windows events details for debug purpose. To understand the meaning of every SQL Wait Events see: http://msdn.microsoft.com/it-it/library/ms179984.aspx.
Following you can see a good wait analysis script to cross informations for a fast debug (source: http://www.sqlskills.com/blogs/paul/advanced-performance-troubleshooting-waits-latches-spinlocks/)
DBCC SQLPERF ('sys.dm_os_wait_stats', CLEAR); --reset DM view
GO
SELECT
[owt].[session_id],
[owt].[exec_context_id],
[owt].[wait_duration_ms],
[owt].[wait_type],
[owt].[blocking_session_id],
[owt].[resource_description],
[es].[program_name],
[est].1,
[est].[dbid],
[eqp].[query_plan],
[es].[cpu_time],
[es].[memory_usage]
FROM sys.dm_os_waiting_tasks [owt]
INNER JOIN sys.dm_exec_sessions [es] ON
[owt].[session_id] = [es].[session_id]
INNER JOIN sys.dm_exec_requests [er] ON
[es].[session_id] = [er].[session_id]
OUTER APPLY sys.dm_exec_sql_text ([er].[sql_handle]) [est]
OUTER APPLY sys.dm_exec_query_plan ([er].[plan_handle]) [eqp]
WHERE [es].[is_user_process] = 1
ORDER BY [owt].[session_id], [owt].[exec_context_id];
GO
3) Open transactions with plan and sql texts
It’s really simple to see informations about current sessions using the old and trusty exec sp_who2 or the dynamic management view sys.dm_exec_requests
But if you need exactly what statements are running and wich plan are they using you need a more complicate query.
This is a good script from http://www.sqlskills.com/blogs/paul/script-open-transactions-with-text-and-plans/ useful to see current transactions with detailed informations about every sessions running.
SELECT s_tst.[session_id],
s_es.[login_name] AS [Login Name],
DB_NAME (s_tdt.database_id) AS [Database],
s_tdt.[database_transaction_begin_time] AS [Begin Time],
s_tdt.[database_transaction_log_record_count] AS [Log Records],
s_tdt.[database_transaction_log_bytes_used] AS [Log Bytes],
s_tdt.[database_transaction_log_bytes_reserved] AS [Log Rsvd],
s_est. AS [Last T-SQL Text],
s_eqp.[query_plan] AS [Last Plan]
FROM sys.dm_tran_database_transactions s_tdt
JOIN sys.dm_tran_session_transactions s_tst
ON s_tst.[transaction_id] = s_tdt.[transaction_id]
JOIN sys.[dm_exec_sessions] s_es
ON s_es.[session_id] = s_tst.[session_id]
JOIN sys.dm_exec_connections s_ec
ON s_ec.[session_id] = s_tst.[session_id]
LEFT OUTER JOIN sys.dm_exec_requests s_er
ON s_er.[session_id] = s_tst.[session_id]
CROSS APPLY sys.dm_exec_sql_text (s_ec.[most_recent_sql_handle]) AS s_est
OUTER APPLY sys.dm_exec_query_plan (s_er.[plan_handle]) AS s_eqp
ORDER BY [Begin Time] ASC;
GO