TodoSQL Blog de SQL Server en español

Que tal!, hoy les voy a contar una solicitud que tuve con respecto a la cardinalidad de tablas en MSSQLServer2000.
Les paso a contar, necesitábamos obtener la cardinalidad y tamaño de 15000 tablas de una app. que posee módulos de RRHH y Finanzas muy conocido en el mercado, porque?, pues estábamos teniendo problemas de performance y una de las medidas iniciales era informar a la analista de la aplicación, cuales eran las tablas mas grandes tanto en cantidad de registros como en tamaño en MB y de esta manera encarar una depuración de tablas (siempre y cuando fuera factible ) para evitar table scan, búsquedas en indices, join’s, demasiados costosos a nivel disco, ram y procesador.
No solo nos sirvió para encarar el problema de performance, sino también para tener una estadística de crecimiento de las tablas.
Uds. se preguntaran cual es el problema? , bueno, el resultado lo necesitábamos en una tabla con campos conteniendo, tanto, ROWS como MB, imagínense ustedes, al tratarse de 15000 tablas, no puede hacerse por el enterprise manager o administrador corporativo, en tanto si puede hacerse con el sp_spaceused, pero editándolo para que la info que nos devuelva la almacenemos en KB y ROWS y luego directamente realicemos la consulta vía stored procedure que realice el calculo a MB.
Para ello, fue necesario realizarlo vía script’s, pues, deben ser 15000 líneas de ejecución y luego editar el sp_spaceused y crear un sp_spaceused2.

A continuación les describo un poco de código:

Modificación de stored procedure sp_spaceused a sp_spaceused2

use master
go
--Crea Stored para ver tamaño en KB
create procedure sp_spaceused2 --- 1996/08/20 17:01
@objname nvarchar(776) = null, -- The object we want size on.
@updateusage varchar(5) = false -- Param. for specifying that
-- usage info. should be updated.
as

declare @id int -- The object id of @objname.
declare @type character(2) -- The object type.
declare @pages int -- Working variable for size calc.
declare @dbname sysname
declare @dbsize dec(15,0)
declare @logsize dec(15)
declare @bytesperpage dec(15,0)
declare @pagesperMB dec(15,0)

/*Create temp tables before any DML to ensure dynamic
** We need to create a temp table to do the calculation.
** reserved: sum(reserved) where indid in (0, 1, 255)
** data: sum(dpages) where indid < 2 + sum(used) where indid = 255 (text)
** indexp: sum(used) where indid in (0, 1, 255) - data
** unused: sum(reserved) - sum(used) where indid in (0, 1, 255)
*/
create table #spt_space
(
rows int null,
reserved dec(15) null,
data dec(15) null,
indexp dec(15) null,
unused dec(15) null
)

/*
** Check to see if user wants usages updated.
*/

if @updateusage is not null
begin
select @updateusage=lower(@updateusage)

if @updateusage not in ('true','false')
begin
raiserror(15143,-1,-1,@updateusage)
return(1)
end
end
/*
** Check to see that the objname is local.
*/
if @objname IS NOT NULL
begin

select @dbname = parsename(@objname, 3)

if @dbname is not null and @dbname <> db_name()
begin
raiserror(15250,-1,-1)
return (1)
end

if @dbname is null
select @dbname = db_name()

/*
** Try to find the object.
*/
select @id = null
select @id = id, @type = xtype
from sysobjects
where id = object_id(@objname)

/*
** Does the object exist?
*/
if @id is null
begin
raiserror(15009,-1,-1,@objname,@dbname)
return (1)
end

if not exists (select * from sysindexes
where @id = id and indid < 2)

if @type in ('P ','D ','R ','TR','C ','RF') --data stored in sysprocedures
begin
raiserror(15234,-1,-1)
return (1)
end
else if @type = 'V ' -- View => no physical data storage.
begin
raiserror(15235,-1,-1)
return (1)
end
else if @type in ('PK','UQ') -- no physical data storage. --?!?! too many similar messages
begin
raiserror(15064,-1,-1)
return (1)
end
else if @type = 'F ' -- FK => no physical data storage.
begin
raiserror(15275,-1,-1)
return (1)
end
end

/*
** Update usages if user specified to do so.
*/

if @updateusage = 'true'
begin
if @objname is null
dbcc updateusage(0) with no_infomsgs
else
dbcc updateusage(0,@objname) with no_infomsgs
print ' '
end

set nocount on

/*
** If @id is null, then we want summary data.
*/
/* Space used calculated in the following way
** @dbsize = Pages used
** @bytesperpage = d.low (where d = master.dbo.spt_values) is
** the # of bytes per page when d.type = 'E' and
** d.number = 1.
** Size = @dbsize * d.low / (1048576 (OR 1 MB))
*/
if @id is null
begin
select @dbsize = sum(convert(dec(15),size))
from dbo.sysfiles
where (status & 64 = 0)

select @logsize = sum(convert(dec(15),size))
from dbo.sysfiles
where (status & 64 <> 0)

select @bytesperpage = low
from master.dbo.spt_values
where number = 1
and type = 'E'
select @pagesperMB = 1048576 / @bytesperpage

select database_name = db_name(),
database_size =
ltrim(str((@dbsize + @logsize) / @pagesperMB,15,2) + ' MB'),
'unallocated space' =
ltrim(str((@dbsize -
(select sum(convert(dec(15),reserved))
from sysindexes
where indid in (0, 1, 255)
)) / @pagesperMB,15,2)+ ' MB')

print ' '
/*
** Now calculate the summary data.
** reserved: sum(reserved) where indid in (0, 1, 255)
*/
insert into #spt_space (reserved)
select sum(convert(dec(15),reserved))
from sysindexes
where indid in (0, 1, 255)

/*
** data: sum(dpages) where indid < 2
** + sum(used) where indid = 255 (text)
*/
select @pages = sum(convert(dec(15),dpages))
from sysindexes
where indid < 2
select @pages = @pages + isnull(sum(convert(dec(15),used)), 0)
from sysindexes
where indid = 255
update #spt_space
set data = @pages

/* index: sum(used) where indid in (0, 1, 255) - data */
update #spt_space
set indexp = (select sum(convert(dec(15),used))
from sysindexes
where indid in (0, 1, 255))
- data

/* unused: sum(reserved) - sum(used) where indid in (0, 1, 255) */
update #spt_space
set unused = reserved
- (select sum(convert(dec(15),used))
from sysindexes
where indid in (0, 1, 255))

select reserved = ltrim(str(reserved * d.low / 1024.,15,0) +
' ' + 'KB'),
data = ltrim(str(data * d.low / 1024.,15,0) +
' ' + 'KB'),
index_size = ltrim(str(indexp * d.low / 1024.,15,0) +
' ' + 'KB'),
unused = ltrim(str(unused * d.low / 1024.,15,0) +
' ' + 'KB')
from #spt_space, master.dbo.spt_values d
where d.number = 1
and d.type = 'E'
end

/*
** We want a particular object.
*/
else
begin
/*
** Now calculate the summary data.
** reserved: sum(reserved) where indid in (0, 1, 255)
*/
insert into #spt_space (reserved)
select sum(reserved)
from sysindexes
where indid in (0, 1, 255)
and id = @id

/*
** data: sum(dpages) where indid < 2
** + sum(used) where indid = 255 (text)
*/
select @pages = sum(dpages)
from sysindexes
where indid < 2
and id = @id
select @pages = @pages + isnull(sum(used), 0)
from sysindexes
where indid = 255
and id = @id
update #spt_space
set data = @pages

/* index: sum(used) where indid in (0, 1, 255) - data */
update #spt_space
set indexp = (select sum(used)
from sysindexes
where indid in (0, 1, 255)
and id = @id)
- data

/* unused: sum(reserved) - sum(used) where indid in (0, 1, 255) */
update #spt_space
set unused = reserved
- (select sum(used)
from sysindexes
where indid in (0, 1, 255)
and id = @id)
update #spt_space
set rows = i.rows
from sysindexes i
where i.indid < 2
and i.id = @id

select name = object_name(@id),
rows = convert(char(11), rows),
reserved = ltrim(str(reserved * d.low / 1024.,15,0)),
CONVERT(CHAR(8), GETDATE(),112) as fecha,
'KB' as tipo

from #spt_space, master.dbo.spt_values d
where d.number = 1
and d.type = 'E'
end

return (0) -- sp_spaceused2
GO
--sp_spaceused2

--**************************FIN*******************************************

2) Creamos una tabla llamada Cardinalidad para contener la info.

USE MiBase
GO
CREATE TABLE Cardinalidad (nombre varchar(50), tamaño int,fecha varchar(8),tipo char(4)DEFAULT 'MB')
GO

3) Ejecutamos el siguiente query:

SELECT 'INSERT INTO Cardinalidad EXEC sp_spaceused2 '+ name FROM sysobjects WHERE xtype ='U'

GO

4) La salida del paso anterior, la volvemos a ejecutar, por ej:

INSERT INTO Cardinalidad EXEC sp_spaceused2 Orders
INSERT INTO Cardinalidad EXEC sp_spaceused2 Products
INSERT INTO Cardinalidad EXEC sp_spaceused2 Order Details
INSERT INTO Cardinalidad EXEC sp_spaceused2 CustomerCustomerDemo
INSERT INTO Cardinalidad EXEC sp_spaceused2 CustomerDemographics
.
.
.
Hasta la tabla 15000.

5) Luego creamos un stored procedure que realice la consulta y trasforme los KB a MB.

CREATE PROCEDURE TamañoTablasEnMB
@nombre as VARCHAR(255)
as
IF @nombre IS NOT NULL
BEGIN
SELECT nombre,(tamaño)/1024 as 'Tamaño en MB',fecha FROM Cardinalidad
WHERE tamaño > 1035 AND nombre = @nombre
ORDER BY 'Tamaño en MB' DESC
END
ELSE
BEGIN
SELECT nombre,(tamaño)/1024 as 'Tamaño en MB',fecha FROM Cardinalidad
WHERE tamaño > 1035
ORDER BY 'Tamaño en MB' DESC
END

Todo este proceso se automatizo por DTS package y el resultado se grabo en un archivo excel o renderizarlo via reporting services (en SQL2005 con SSIS y SSRS).
Una observacion: OJO con la cantidad de registros que almacenen en excel, pues, tiene un límite de 65536.

Y como siempre, un abrazo virtual para todos.