Desde hace algún tiempo, siempre que lo veo factible intento exprimir el rendimiento de SQL Server mediante SQLCLR. Es una característica que bien usada aporta muchísimos beneficios (aunque mal usada es un auténtico desastre y fuente de numerosos problemas de rendimiento).
En este caso, voy a comentar un uso de SQLCLR muy recomendable para optimizar consultas con múltiples operadores LIKE a columnas que contienen mucho texto (nvarchar(max)). Para realizar el ejemplo voy a utilizar la función ::fn_trace_gettable(…), ya que es un ejemplo muy sencillo de realizar y que estoy seguro que aportará valor a cualquiera de los lectores que utilice SQL Server Profiler.
La función fn_trace_gettable(), sirve para poder visualizar los resultados capturados con SQLProfiler o SQLTrace. Lo habitual que se suele hacer con esta función es tratar de encontrar algo que se ha lanzado contra el servidor y que priori no sabemos cuántas veces ocurre, ni cuando ni por qué. Por tanto, queramos o no, acabamos aplicando filtros para extraer información de nuestra traza…y cómo no, acabamos haciéndolo sobre el contenido de la columna “TextData”.
El problema de rendimiento viene cuando intentamos filtrar la columna “TextData”, que puede contener un texto realmente grande (tipo de datos nvarchar(max)), ya que cuando el tipo de texto que queremos buscar es muy variable, acabamos necesitando introducir múltiples cláusulas LIKE concatenadas con un operador OR:
select
DatabaseID,
ISNULL(DatabaseName, DatabaseID) AS DatabaseName,
ISNULL(LoginName ,'') as LoginName,
ISNULL(NTUserName,'') as NTUserName,
ISNULL(NTDomainName,'') as NTDomainName,
ISNULL(HostName,'') as HostName,
ISNULL(ApplicationName,'') as ApplicationName,
TextData,
StartTime,
EndTime,
case
when TextData like '%openrowset%' then 'openrowset'
when TextData like '%opendatasource%' then 'opendatasource'
when TextData like '%sp_addlinkedserver%' then 'add_linkedserver'
else 'null'
end as pattern_match
FROM fn_trace_gettable ( 'D:\Profiler\traza_migragion_2008_2016-01-21 124137_5.trc' , DEFAULT)
WHERE textdata IS NOT NULL AND EventClass = 12
and( TextData like '%openrowset%'
or TextData like '%opendatasource%'
or TextData like '%sp_addlinkedserver%'
)
Explicar la consulta anterior sale de la temática de este post, pero he de decir que es una consulta muy típica que se lanza sobre un volumen de trazas generalmente importante (del orden de más de 100Gb de datos). Típicamente es una operación que, mal optimizada, puede llevar muchas horas de procesamiento y obviamente nos interesará conseguir que vaya lo mas rápido posible. Si analizásemos el rendimiento de la consulta anterior, acabaríamos viendo que hay un cuello de botella en la CPU que está produciendo un freno a la hora de leer los datos. Este freno se ve muy fácilmente cuando miramos la E/S y vemos que está funcionando a una velocidad mucho menor de lo que puede ir:
Como vemos, SQL Server no es capaz de procesar los filtros LIKE que hemos puesto a más de 12Mb/s. Esta velocidad va descendiendo a medida que introduzcamos mayor nº de filtros sobre TextData, debido al coste CPU que le supone al motor relacional, que no está pensado para procesar texto. Precisamente para esto apareció SQLCLR en SQL Server2005, para entre muchas otras cosas, dotar de la potencia de .NET para el procesamiento de texto. Si nosotros nos desplegamos una función CLR, que reciba por parámetro el texto que deseamos buscar, y dejamos a nuestra rutina CLR que sea la encargada de buscar sobre el texto que se recibe, múltiples valores a buscar…el rendimiento de la query crecerá en varios ordenes de magnitud(cuantos mas filtros quieras aplicar, mayor rendimiento obtendrás comparado con LIKE).
En este caso, la optimización será tan simple como llamar a la función “SqlContainsMultiple”, pasándole los valores que queremos buscar:
select
DatabaseID,
ISNULL(DatabaseName, DatabaseID) AS DatabaseName,
ISNULL(LoginName ,'') as LoginName,
ISNULL(NTUserName,'') as NTUserName,
ISNULL(NTDomainName,'') as NTDomainName,
ISNULL(HostName,'') as HostName,
ISNULL(ApplicationName,'') as ApplicationName,
TextData,
StartTime,
EndTime,
case
when TextData like '%openrowset%' then 'openrowset'
when TextData like '%opendatasource%' then 'opendatasource'
when TextData like '%sp_addlinkedserver%' then 'add_linkedserver'
else 'null'
end as pattern_match
FROM fn_trace_gettable ( 'D:\Profiler\traza_migragion_2008_2016-01-21 124137_5.trc' , DEFAULT)
WHERE textdata IS NOT NULL AND EventClass = 12
and SQLCLRUtils.dbo.SqlContainsMultiple(TextData,'openrowset|opendatasource|sp_addlinkedserver','|') =1
Si lanzamos esta nueva versión, veremos que pasaremos a poder leer a una velocidad que se acerca bastante al límite de lo que es capaz de dar tu E/S (en mi caso lo estoy lanzando sobre un disco mecánico externo).
¿Dónde está la magia?
Pues la magia reside en la función CLR SqlContainsMultiple, que es el encargado de procesar precisamente lo que antes estábamos pidiéndole al motor mediante cláusulas LIKE:
/// <summary>
/// Esto es porque para evitar multiples llamadas, si sabemos que vamos a pedir comparar con muchas cosas, me interesa crear una llamada que
/// encapsule varias expresiones a comparar.
/// la idea es que en texto a buscar se pase texto | texto2 | texto3,... y se indique como separador = '|' de forma que se esté indicando indirectamente
/// como un conjunto de cadenas
/// A dia de hoy, en SQL Server 2014 no se permite TVP como parámetros de entrada
/// </summary>
/// <param name="querystring"></param>
/// <param name="texto_a_buscar"></param>
/// <param name="separador"></param>
/// <returns></returns>
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlBoolean SqlContainsMultiple(SqlString querystring,
SqlString texto_a_buscar,
[SqlFacet(IsFixedLength = true, IsNullable = false, MaxSize = 1)]SqlChars separador)
{
if (querystring.IsNull)
return SqlBoolean.Null;
/// Si no encuentro el texto, sigo buscando...
foreach (string tmp_texto_a_buscar in texto_a_buscar.Value.Split(separador[0]))
{
if (querystring.Value.Contains(tmp_texto_a_buscar))
return (SqlBoolean.True);
}
/// Si despues de estar buscando por todo lo que le he pasado no encuentro nada...no hay nada
return (SqlBoolean.False);
}
Material complementario
Puedes obtener el código fuente de ésta y otras funciones CLR útiles, en mi paquete opensource SQLCLRUtils en GitHub: https://github.com/enriquecatala/SQLCLRUtils
En 2015 tuve la suerte de impartir una sesión sobre SQLCLR en #dotNetSpain, te recomiendo que si tienes interés en aprender más sobre SQLCLR me preguntes y te pegues una vuelta por este post http://www.enriquecatala.com/2015/03/sqlclr-net-en-el-core-de-sql-server.html
