Reducir tamaño de la Base de Datos en SQL Server

El siguiente código reduce el tamaño del archivo .ldf de la base de datos.

BD : Base de datos donde se reducirá espacio.

USE [master]

GO

ALTER DATABASE [BD] SET RECOVERY SIMPLE WITH NO_WAIT

GO

use [BD]

GO

DBCC SHRINKFILE (BD_log, 0)

GO

USE [master]

GO

ALTER DATABASE BD SET RECOVERY FULL WITH NO_WAIT

GO

Uso de SET NOCOUNT en SQLServer

En SQLServer se usa SET NOCOUNT para devolver o no la cantidad de filas afectadas por alguna sentencia o transacción ejecutada. SET NOCOUNT tiene dos estados:

SET NOCOUNT ON : Inhabilita la cuenta del número de filas afectadas por una instrucción Transact-SQL

SET NOCOUNT OFF: Habilita la cuenta del número de filas afectadas por una instrucción Transact-SQL

Cuando se usa SET NOCOUNT ON se mejora el rendimiento del servidor porque omite la devolución del numero de filas afectadas al cliente o terminal que ha realizado la consulta, pero hay casos en que es necesario obtener el número de registros afectados segun nuestra necesidad, por ello se recomienda analizar muy bien cuando usar SET NOCOUNT ON.

El siguiente ejemplo hace una consulta a la tabla customers de la base de datos Northwind usando los dos estados de SET NOCOUNT, se puede apreciar la diferencia.

Usando SET NOCOUNT ON

----- 1
GO
SET NOCOUNT ON

select * from dbo.customers
where country = 'France'
GO

Resultado:

Usando SET NOCOUNT OFF

---- 2
GO
SET NOCOUNT OFF

select * from dbo.customers
where country = 'France'
GO

Resultado:

Tablas temporales en Sql Server 2000: Optimizar su uso

Siguiendo con el post sobre las tablas temporales en SQL Server 2000 voy a dar una serie de posibles mejoras que se pueden aplicar para conseguir un mejor desempeño.

1. Es mejor crear la tabla temporal con comandos DDL y luego insertar los datos, por ejemplo:

CREATE TABLE #TABLA1
(   CODIGO INT,
 NOMBRE VARCHAR(20)
)

insert into #TABLA1(CODIGO,NOMBRE) values('1','LACTEOS')
insert into #TABLA1(CODIGO,NOMBRE) values('2','BEBIDAS')

y evitar crearlo en una instrucción SELECT INTO:

SELECT categoryID,CategoryName  
INTO #TABLA1 
FROM CATEGORIES

Debido a que en la ultima opción los bloqueos hacia los objetos del sistema duran mas tiempo.

2. Evitar el uso de SELECT * FROM, en su lugar se debe extraer las columnas que se necesitan.

3. Insertar solo la información necesaria.

4. Borrar la tabla temporal al finalizar el conjunto de sentencias o el procedimiento almacenado. Las tablas temporales se destruyen cuando se termina la conexión pero no tendría sentido dejarlas ocupar recursos del servidor si ya no se la va a usar.

5. Cuando la cantidad de datos que maneja una tabla temporal es grande se recomienda crearle un indice para que ayude a recuperar la información.

6. Colocar la base de datos tempdb en un disco dedicado solo para esta función aumentará el rendimiento global del sistema si se hace un uso intensivo de tablas temporales.

7. No crear tablas temporales dentro de transacciones y trigger porque la concurrencia a la base de datos se verá afectada.

Diagrama de Joins en SQL Server

Aqui dejo un útil diagrama que ayuda a comprender el funcionamiento de los Joins en Sql Server.

Tablas temporales en Sql Server 2000: alternativas recomendadas

En ocasiones el resultado que deseamos obtener de una tabla o un conjunto de tablas a travez de solo una sentencia de select resulta complicado e inviable porque los datos tienen que ser tratados y procesados a fin de mostrar la información de acuerdo a nuestra necesidad, por lo cual es inevitable el uso de tablas temporales dentro de los procedimientos almacenados.

Pero la solución en base a tablas temporales resulta perjudicial para el desempeño del servidor de base de datos por eso se recomienda no usarlas.

¿Y porque no usar tablas temporales? Las razones las menciono a continuación:

1. las tablas temporales se crean en el tempdb y al crearlas se producen bloqueos sobre esta base de datos como por ejemplo en las tablas sysobjects y sysindexes. Los bloqueos sobre el la tempdb afectan a todo el servidor.
2. Es necesario que se escriba sobre el disco.
3. Al insertar datos de nuevo se produce escritura al disco (Los accesos al disco suelen ser los cuellos de botella del sistema).
4. Al leer datos de nuevo hay que rrecurrir al disco
5. Al borrar la tabla de nuevo hay que adquirir bloqueos sobre la base de datos tempdb y realizar operaciones en disco.
6. Si la consulta dentro de un procedimiento almacenado tiene una tabla temporal difícilmente se reutilizará el plan de ejecución.

Siempre la mejor solución es obtener los datos a travez de una sola consulta.

Siempre es bueno saber en que momento usar tablas temporales, para ello debemos conocer bien su funcionamiento y limitaciones, tambien es bueno conocer las posibles alternativas y revisar el plan de ejecución a fin de obtener la consulta mas óptima.

Con SQL Server 2000 podemos usar variables de tablas. Las variables de tablas tienen una serie de ventajas frente a tablas temporales:

1. Solo existe en el ámbito en que fue definido.
2. Producen menos recompilación de procedimientos almacenados.
3. No necesitan de bloqueos ni de tantos recursos.
4. se crean en memoria lo cual produce menos overhead que la tabla temporal

Pero también tiene una serie de inconvenientes:

1. Una vez declara no se puede modificar la estructura de la tabla. Por ejemplo si dentro de procedimiento almacenado hemos definido una variable de tabla con los campos código y nombre y luego de declararla queremos alterar la tabla para agregar la columna importe no nos va a permitir.
2. Los indices tienen que ser agrupados
3. No se pueden usar en una sola sentencia Insert into o select into. Primero se deve declarar la variable de tabla y luego insertar los datos correspondientes.
4. No se puede usar funciones en las restricciones.

Para finalizar, viendo las ventajas y desventajas es mejor usar variables de tablas frente a tablas temporales, pero en el caso en que se procesen grandes cantidades de datos y se necesite la creación de indices es mejor una tabla temporal.

Se debe evaluar que opción es la mas recomendable para nuestro procedimiento almacenado, en el siguiente post sobre tablas temporales colocare algunas formas de optimar su uso.

Consultas dinámicas en Sqlserver 2000

Hace algún tiempo atrás tuve que implementar en la aplicación que estuve desarrollando, consultas configurables para que el usuario puede filtrar la información de acuerdo a su necesidad, pongamos como ejemplo a la base de datos Northwind y la tabla Products, la aplicación debía consultar por cualquier columna que el usuario seleccione y filtrar la información según las coincidencias encontradas.

A mi no me pareció complejo hacer algo asi, porque solo debía armar la consulta en la aplicación y ejecutarla, pero luego me di cuenta que hacerlo de esta manera tenia dos desventajas:

- Al armar las consultas en la aplicación, esta se convertía en poco escalable y muy estática porque cualquier cambio o actualización que quisiese hacer tenia que modificar el código del programa y crear otro instalador o en todo caso reemplazar la dll donde se ha realizado el cambio.

- Las consultas que se envían desde la aplicación consumen mas recursos del servidor porque sql server tiene que cargar en memoria la sentencia a ejecutar, luego analizarla, compilarla y por ultimo ejecutarla, ocasionando que el servidor consuma mas recursos.

Los procedimientos almacenados están pre compilados en el servidor, por lo cual ejecutarlos tiene un menor costo para el servidor.

Para no armar y concatenar la sentencia en la aplicación debía ser capaz de armar la consulta en el servidor a travez de un procedimiento almacenado.

Para mi satisfacción encontré que desde la versión Sql Server 2000 existe el comando sp_executesql, capaz de ejecutar cualquier tipo de consulta que se le especifique.

El siguiente ejemplo usa sp_executesql y consiste en crear un procedimiento almacenado que consulte a la tabla Products sujeto a la condición que se envia al procedimiento.

GO
drop function [fGetCondicion]
GO
create function [dbo].[fGetCondicion]
(
@Condicion varchar(20),
@value varchar(100)
)
RETURNS  VARCHAR(20)
AS
-- =============================================
-- Author:  Milton Baltazar Valenzuela
-- Description: Obtiene el tipo de condicion y el valor
--       de la condicion para devolver la condicion
--       en el formato requerido
-- =============================================
BEGIN
RETURN (
SELECT
CASE @Condicion
WHEN 'like' THEN @Condicion + '''%' + @value + '%'''
WHEN '='  THEN @Condicion + ' ' + @value
WHEN '<>' THEN @Condicion + ' ' + @value
WHEN '>'  THEN @Condicion + ' ' + @value
WHEN '<'  THEN @Condicion + ' ' + @value  ELSE null END) END GO  GO drop procedure usp_get_products GO create procedure usp_get_products (   @productID            int  ,@ProductName          varchar(40)  ,@CategoryID           int  ,@UnitPrice            money  ,@Condicion     varchar(100) ) AS BEGIN Declare @ls_query nvarchar(4000) Declare @li_error int Declare @ls_error  varchar(1000)   set @ls_query='select productID, ProductName, CategoryID, UnitPrice from dbo.Products'     if @productID is not null    begin      set @ls_query = @ls_query + ' where productID ' + dbo.fGetCondicion(@Condicion,@productID)  end   else if @ProductName is not null    begin      set @ls_query = @ls_query + ' where ProductName ' + dbo.fGetCondicion(@Condicion,@ProductName)  end   else if @CategoryID is not null    begin      set @ls_query = @ls_query + ' where CategoryID ' + dbo.fGetCondicion(@Condicion,@CategoryID)  end        exec @li_error = sp_executesql   @ls_query,N' @productID            int     ,@ProductName          varchar(40)     ,@CategoryID           int     ,@Condicion      varchar(100)'     ,@productID       ,@ProductName      ,@CategoryID       ,@Condicion ;      if @li_error <> 0
begin
set @ls_error = 'Error en la consulta. '
RaisError(@ls_error,16,1)
end
END
GO


----------------------------------------------------
--ejecutamos el procedimiento almacenado
----------------------------------------------------

exec usp_get_products 10,null,null,null,'='

exec usp_get_products null,'',null,null,'like'

exec usp_get_products null,'Chai',null,null,'Like'


GO

En resumen he creado un procedimiento almacenado que me permita consultar
cualquier campo de acuerdo a la condición que le envío, es cierto que al código no esta al 100% perfeccionado pero solo quiero dar la idea de que se puede hacer con sp_executesql.