SQL Server – Tablas más utilizadas

Quizás nos hemos preguntado cuales tablas en la base de datos son usadas con mayor frecuencia. Ya sea para buscar optimizar o entender el comportamiento de nuestro sistema. A continuación se presenta un script de SQL Server (2005/2008) que permite determinar la cantidad de veces que un usuario accede a una tabla ya sea para leer o escribir datos.

[code language=»sql»]

–SQL Script begin

IF OBJECT_ID(‘tempdb..#DBTableStats ‘) IS NOT NULL

DROP TABLE #DBTableStats

GO

CREATE TABLE #DBTableStats

(Table NVARCHAR(255), UsrSeeks DEC, UsrScans DEC, UsrUpdates DEC)

INSERT INTO #DBTableStats

EXEC sp_MSForEachDB ‘USE [?]; IF DB_ID(»?») > 4

BEGIN

SELECT DB_NAME() + ».» + OBJECT_NAME(b.object_id), a.user_seeks, a.user_scans, a.user_updates

FROM sys.dm_db_index_usage_stats a

RIGHT OUTER JOIN [?].sys.indexes b ON a.object_id = b.object_id AND a.database_id = DB_ID()

WHERE b.object_id > 100

END’

SELECT [Table] AS ‘Tabla’, SUM(UsrSeeks + UsrScans + UsrUpdates) AS ‘Total Accesos’,

SUM(UsrUpdates) AS ‘Escritura’,

CONVERT(DEC(25,2),(SUM(UsrUpdates)/SUM(UsrSeeks + UsrScans + UsrUpdates)*100)) AS ‘% Escritura’,

SUM(UsrSeeks + UsrScans) AS ‘Lectura’,

CONVERT(DEC(25,2),(SUM(UsrSeeks + UsrScans)/SUM(UsrSeeks + UsrScans + UsrUpdates)*100)) AS ‘% Lectura’,

SUM(UsrSeeks) AS ‘Seeks’, CONVERT(DEC(25,2),(SUM(UsrSeeks)/SUM(UsrSeeks + UsrScans)*100)) AS ‘% Index Seeks’,

SUM(UsrScans) AS ‘Scans’, CONVERT(DEC(25,2),(SUM(UsrScans)/SUM(UsrSeeks + UsrScans)*100)) AS ‘% Index Scans’

FROM #DBTableStats

GROUP by [Table]

HAVING SUM(UsrSeeks + UsrScans) > 0

ORDER by SUM(UsrSeeks + UsrScans + UsrUpdates) DESC

DROP table #DBTableStats

–SQL Script end

[/code]

Al ejecutarse obtenemos el número de veces que la table ha sido utilizada entre operaciones de lectura y escritura, luego vemos el número de operaciones de escritura y que porcentaje representa del total.  A continuación aparece el número de operaciones de lectura y que porcentaje representa del total, al lado de este dato encontraremos el detalle de las operaciones de lectura cuantas corresponden a operaciones SEEK y cuantas a SCAN con su respectivo porcentaje.

Las tablas mas utilizadas podrían ser candidatas a ser particionadas o a ser movidas a un filegroup aparte con el fin de lograr mayor rendimiento de la aplicación.

Para finalizar tenga en cuenta que el script hace uso de la información en sys.dm_db_index_usage_stats que se acumula desde el momento en que el motor de base de datos se inicia. En caso de que el servicio se reinicie todos los valores volverán a cero.