SQLCLR, SSMS messages, SET Statistics IO
Le informazioni che SQL Server invia sulla finestra dei messaggi del Management Studio non sono direttamente catturabili.
Questo significa che non è possibile, in maniera nativa, sfruttare qualche meccanismo per recuperare queste informazioni e, ad esempio, memorizzarle al fine di interrogarle in un momento successivo.
Con il SQLCLR la cosa diventa possibile.
Obiettivo: memorizzare le statistiche di I/O di query e procedure:
Vediamo come risolvere il problema in SQL Server 2012 sviluppando una procedura SQLCLR con Visual Studio 2010 ed i SQL Server Data Tools.
- Creo un nuovo progetto SQL Server – SQL Server Database Project
- Creo, all’interno del progetto, la mia stored procedure che chiamerò up_getIOMessages
La procedura avrà in ingresso tre parametri:
- l’istruzione TSQL da mandare in esecuzione per poterne catturare i messaggi
- il nome del server ed il nome del database per poter aprire una connessione all’istanza SQL (questa connessione è necessaria poichè non è sufficiente, per raggiungere il nostro obiettivo, utilizzare la connessione all’interno della quale viene invocata la procedura stessa)
Il codice, invece, si preoccuperà di:
- aprire una connessione verso l’istanza
- impostare la SET STATISTICS IO ON
- eseguire l’istruzione passata come parametro
- recuperare i messaggi generati dal database engine
Una volta ottenuti i messaggi, la procedura costuirà un resultset con i valori delle varie attività fatte (scansioni effettuate, letture logiche, letture fisiche, …).
Questo il codice C# (è poco commentato perchè mi sembra che si auto-commenti da solo):
[SqlProcedure]
public static void up_getIOMessages(SqlString server, SqlString database, SqlString tSql)
{
var sqlPipe = SqlContext.Pipe;
var sqlMetaData = new SqlMetaData[10];
sqlMetaData[0] = new SqlMetaData("Guid", SqlDbType.NVarChar, 36);
sqlMetaData[1] = new SqlMetaData("tSql", SqlDbType.NVarChar, 1024);
sqlMetaData[2] = new SqlMetaData("TableName", SqlDbType.NVarChar, 256);
sqlMetaData[3] = new SqlMetaData("ScanCount", SqlDbType.Int);
sqlMetaData[4] = new SqlMetaData("LogicalReads", SqlDbType.Int);
sqlMetaData[5] = new SqlMetaData("PhysicalReads", SqlDbType.Int);
sqlMetaData[6] = new SqlMetaData("ReadAheadReads", SqlDbType.Int);
sqlMetaData[7] = new SqlMetaData("LobLogicalReads", SqlDbType.Int);
sqlMetaData[8] = new SqlMetaData("LobPhysicalReads", SqlDbType.Int);
sqlMetaData[9] = new SqlMetaData("LobReadAheadReads", SqlDbType.Int);
var dr = new SqlDataRecord(sqlMetaData);
if (sqlPipe != null)
{
sqlPipe.SendResultsStart(dr);
if (tSql.IsNull || tSql.Value == "")
{
sqlPipe.SendResultsEnd();
return;
}
}
/*
* La struttura del messaggio relativo alle statistiche:
*
* Table 'Person'. Scan count 0, logical reads 915, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
*/
const string strTable = "Table '";
const string strScanCount = "'. Scan count ";
const string strLogRead = ", logical reads ";
const string strPhyRead = ", physical reads ";
const string strRaRead = ", read-ahead reads ";
const string strLobLogRead = ", lob logical reads ";
const string strLobPhyRead = ", lob physical reads ";
const string strLobRaRead = ", lob read-ahead reads ";
const string setStatsIoOn = "SET STATISTICS IO ON";
/*
* TransactionScopeOption.Suppress per evitare il coinvolgimento del MSDTC
* I comandi che lanceremo, in questa maniera, non verranno eseguiti in transazione
*/
using (new TransactionScope(TransactionScopeOption.Suppress))
{
using (
var cn =
new SqlConnection("Server=" + server.ToString() + ";Database=" + database.ToString() +
";Integrated Security=SSPI;"))
{
cn.Open();
cn.FireInfoMessageEventOnUserErrors = true;
var cmd = new SqlCommand { Connection = cn, CommandType = CommandType.Text, CommandText = setStatsIoOn };
/*
* Imposto la SET STATISTICS IO ON
*/
cmd.ExecuteNonQuery();
var messages = new List<string>();
cn.InfoMessage += delegate(object sender, SqlInfoMessageEventArgs e)
{
if (
String.CompareOrdinal(e.Message.Substring(0, strTable.Length), strTable) ==
0)
messages.Add(e.Message);
};
cmd.CommandText = tSql.Value;
cmd.ExecuteNonQuery();
var guid = Guid.NewGuid();
foreach (var message in messages)
{
var scanCount = message.IndexOf(strScanCount, StringComparison.Ordinal);
var logRead = message.IndexOf(strLogRead, scanCount + strScanCount.Length, StringComparison.Ordinal);
var phyRead = message.IndexOf(strPhyRead, logRead + strLogRead.Length, StringComparison.Ordinal);
var raRead = message.IndexOf(strRaRead, phyRead + strPhyRead.Length, StringComparison.Ordinal);
var lobLogRead = message.IndexOf(strLobLogRead, raRead + strRaRead.Length, StringComparison.Ordinal);
var lobPhyRead = message.IndexOf(strLobPhyRead, lobLogRead + strLobLogRead.Length,
StringComparison.Ordinal);
var lobRaRead = message.IndexOf(strLobRaRead, lobPhyRead + strLobPhyRead.Length,
StringComparison.Ordinal);
var endDot = message.IndexOf('.', lobRaRead + strLobRaRead.Length);
if (
!(scanCount > 0 && logRead > 0 && phyRead > 0 && raRead > 0 && lobLogRead > 0 && lobPhyRead > 0 &&
lobRaRead > 0 && endDot > 0))
continue;
dr.SetSqlString(0, guid.ToString());
dr.SetSqlString(1, tSql.ToString());
dr.SetSqlString(2, message.Substring(strTable.Length, scanCount - strTable.Length));
dr.SetSqlInt32(3,
Convert.ToInt32(message.Substring(scanCount + strScanCount.Length,
logRead - scanCount - strScanCount.Length)));
dr.SetSqlInt32(4,
Convert.ToInt32(message.Substring(logRead + strLogRead.Length,
phyRead - logRead - strLogRead.Length)));
dr.SetSqlInt32(5,
Convert.ToInt32(message.Substring(phyRead + strPhyRead.Length,
raRead - phyRead - strPhyRead.Length)));
dr.SetSqlInt32(6,
Convert.ToInt32(message.Substring(raRead + strRaRead.Length,
lobLogRead - raRead - strRaRead.Length)));
dr.SetSqlInt32(7,
Convert.ToInt32(message.Substring(lobLogRead + strLobLogRead.Length,
lobPhyRead - lobLogRead - strLobLogRead.Length)));
dr.SetSqlInt32(8,
Convert.ToInt32(message.Substring(lobPhyRead + strLobPhyRead.Length,
lobRaRead - lobPhyRead - strLobPhyRead.Length)));
dr.SetSqlInt32(9,
Convert.ToInt32(message.Substring(lobRaRead + strLobRaRead.Length,
endDot - lobRaRead - strLobRaRead.Length)));
if (sqlPipe != null) sqlPipe.SendResultsRow(dr);
}
cn.Close();
}
}
if (sqlPipe != null) sqlPipe.SendResultsEnd();
}
E’ fondamentale aggiungere, alle referenze del progetto, l’assembly System.Transactions:
Una volta terminato il codice e verificato che esegua una build senza errori è necessario qualche altro veloce passaggio.
- Impostiamo il livello del nostro assembly a EXTERNAL_ACCESS per poter aprire una connessione verso SQL.
Dalle proprietà del progetto:
- Firmiamo il nostro assembly con uno Strong Name Key File.
Dalla stessa videata, tramite il pulsante “Signing”:
Teniamo traccia del percorso dove viene memorizzato il file SNK appena creato poichè ci servirà in seguito.
A questo punto siamo pronti per fare il deploy sul nostro server.
Prima di lanciare la pubblicazione, però, sono necessarie ancora tre operazioni da fare direttamente sull’istanza SQL:
- creare una chiave asimmetrica dal file SNK creato con Visual Studio poco sopra
- creare una login da questa chiave asimmetrica
- associare il permesso di EXTERNAL ACCESS al nostro assembly tramite la login appena creata
In T-SQL:
USE MASTER;
GO
/* se non è già presente una MASTER KEY deve essere creata */
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'MyStr0ngP@ssw0rd1';
GO
CREATE ASYMMETRIC KEY myAsymmetricKey FROM FILE = 'C:\data\asymKey.snk';
GO
CREATE LOGIN loginAysmKey FROM ASYMMETRIC KEY myAsymmetricKey;
GO
GRANT EXTERNAL ACCESS ASSEMBLY TO snloginAysmKey;
GO
Adesso, possiamo procedere con il rilascio del nostro assembly e della stored procedure.
Questa attività, con i SQL Server Data Tools, si chiama Publish (nel Solution Explorer di Visual Studio, tasto DX sul nostro progetto, “Publish”).
Impostiamo il server ed il database su cui fare la pubblicazione e procediamo:
Terminata la pubblicazione avremo, all’interno del nostro database, gli oggetti appena rilasciati:
A questo punto siamo pronti ad eseguire la nostra procedura .Net:
USE [SQLCLRTest];
GO
EXEC dbo.up_getIOMessages '.\SQL2012' , 'SQLCLRTest' , 'SELECT * FROM person';
GO
Ed ecco il risultato:
Non ci resta che costruire la tabella che possa contenere il resultset uscente dalla stored procedure .Net (quindi la cattura delle statistiche) ed eseguire nuovamente la procedura.
- Creo la tabella:
CREATE TABLE IOLog( idRecord int PRIMARY KEY
IDENTITY( 1 , 1 ) ,
idGuid uniqueidentifier ,
tSql varchar( 1024 ) ,
tableName varchar( 256 ) ,
scanCount int ,
logicalReads int ,
physicalReads int ,
readAheadReads int ,
lobLogicalReads int ,
lobPhysicalReads int ,
lodReadAheadReads int );
GO
- Eseguo la procedura, questa volta “complicando” il comando andando a richiedere una vista di un altro database:
INSERT INTO dbo.IOLog( idGuid ,
tSql ,
tableName ,
scanCount ,
logicalReads ,
physicalReads ,
readAheadReads ,
lobLogicalReads ,
lobPhysicalReads ,
lodReadAheadReads )
EXEC dbo.up_getIOMessages '.\SQL2012' , 'SQLCLRTest' ,
'SELECT * FROM [AdventureWorks2012].[HumanResources].[vEmployeeDepartmentHistory]';
GO
Questo il risultato:
SELECT *
FROM dbo.IOLog;
GO
Sulla nostra tabella:
- La prima colonna [idGuid] ci permette di identificare i messaggi appartenenti alla stessa istruzione eseguita, mentre la seconda [tSql] ci mostra il comando eseguito.
- Le altre colonne sono il risultato del parsing delle informazioni inviate dall’istanza verso la finestra messaggi del Management Studio.
Naturalmente, se il CLR fosse disabilitato (lo è per default) va abilitato :-)
EXEC sp_configure 'clr enabled' , 1;
GO
RECONFIGURE;
GO