Guillermo Valencia - Blog 
Este sitio web utiliza JavaScript. Por favor, habilítalo en tu navegador para que todo funcione correctamente.


Auditoría de un Modelo de Datos

Guillermo Valencia

Publicado el 02 de julio de 2018

Tags: SQL Server

Me solicitaron realizar la auditoría del modelo de datos de una aplicación interna de la compañía, desarrollada en ASP.NET MVC (.Net Framework 4.6) con persistencia en BD SQL Server. En principio el tamaño de la aplicación era pequeño (apenas 100.000 líneas de código fuente en C#), cuando descubrí que la mayor parte de la capa de negocio de la aplicación estaba implementada en el propio modelo de datos, mediante el uso de procedimientos almacenados.
En concreto, descubrí más de 800 tablas y 1200 procedimientos almacenados. Aquello de pequeño tenía poco. Realmente el modelo de datos que me encontré era bastante inmantenible.
Para obtener el número de tablas, vistas y procedimientos almacenados utilicé la siguiente consulta:


SELECT 
    CASE TYPE 
        WHEN 'U' 
            THEN 'User Defined Tables' 
        WHEN 'P'
            THEN 'Stored Procedures'        
	 WHEN 'V'
            THEN 'Views'
    END, 
    COUNT(*)     
FROM SYS.OBJECTS
WHERE TYPE IN ('U', 'P', 'V')
AND Left(name, 3) NOT IN ('sp_', 'xp_', 'ms_')
GROUP BY TYPE

 

Otra cosa que me llamó la atención fue la gran cantidad de tablas que no disponían de clave primaria, más de 80 tablas.
Para ello utilicé la siguiente consulta.


SELECT SCHEMA_NAME(sys.tables.schema_id) + '.' + OBJECT_NAME(sys.tables.object_id) AS TableName
FROM sys.tables
WHERE objectproperty(OBJECT_ID,'TableHasPrimaryKey') = 0

 

También quise echarle un ojo a las claves foráneas, la mayor parte de ellas no tenían un índice asociado, lo que repercutía negativamente en el rendimiento a la hora de realizar consultas.
Usé la siguiente consulta:


SELECT SCHEMA_NAME(ST.schema_id) + '.' + OBJECT_NAME(ST.object_id) AS TableName 
, string_agg(Keys.Name, ', ') AS NombleForeignKey
FROM sys.foreign_keys keys
INNER JOIN sys.foreign_key_columns TheColumns
ON Keys.Object_ID=constraint_object_id  
INNER JOIN sys.tables ST
ON ST.object_id = keys.parent_Object_ID
LEFT OUTER JOIN sys.index_columns ic
ON ic.object_ID=TheColumns.parent_Object_Id
AND ic.column_ID=TheColumns.parent_Column_Id
AND TheColumns.constraint_column_ID=ic.key_ordinal
WHERE ic.object_ID IS NULL
group by SCHEMA_NAME(ST.schema_id) + '.' + OBJECT_NAME(ST.object_id)
ORDER BY TableName

 

Más tarde me pidieron la optimización de varios planes de ejecución de consultas y procedimientos almacenados que habían observado que tenían graves problemas de rendimiento. Aparte del propio SQL Server Management Studio, utilicé una herramienta gratuita que recomiendo y que me fue de gran ayuda. Directamente la herramienta te propone aquellos índices necesarios para mejorar los tiempos de respuesta de las consultas. Le herramienta se llama SentryOne Plan Explorer, y puede ser descargada gratuitamente desde su página web: https://www.sentryone.com/plan-explorer