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