Entradas populares

Mejorar rendimiento SQLSERVER // Improve SQLSERVER performance

13:36 | Publicado por Killiam | Editar entrada


Buenos vamos a ello, la primera entrada de verdad. Como ya dije hare algunos artículos para la gente más técnica, y que me imagino a alguno ayudaran. Son de esas cosas que a uno le cuesta encontrar por internet, y que son bastante útiles.

En este articulo voy a copiar un script que ayudará a toda aquella gente que es administrador de sqlserver o que ha desarollado aplicaciones para SQLSERVER. Este script en T-SQL hace los siguiente:
  1. Busca todos los indices de la BBDD en la que se ejecute el script
  2. Mira el estado de fragmentación de los Indices
  3. Si el indice está fragmentado menos o igual de un 10% no hace nada
  4. Si el indice en cuestión está fragmentado de un 10% - 30% lo reindexa
  5. Si el indice tiene un estado de fragmentación mayor a un 30% lo reconstruye
Una vez ejecutado el script notareis un mejora importante en el rendmiento de vuestras bases de datos y vuestras aplicaciones.

(El script es la parte en rojo)
--------------------------------------------

Well lets go, honestly this is the first article. As I told, I will blog some technical entries, that I suppose that will help somebody (or at least this is my intention). Things that are difficult to find in the Web, but they are really useful.

This article is about a T-SQL script for SQLSERVER that will help database administrator, or even the people who have an application running on a SQLSERVER database. This T-SQL scripts do the following:

  1. Find all the indexes on the selected database
  2. Check the state of fragmentation of the indexes
  3. If the index is fragmented equal o below 10% makes nothing
  4. It the fragmentation of the index is between 10% - 30% it will be reindexed
  5. If the fragmentation of the index is above 30% it will be rebuild
Once you do this you will notice a dramatically improvement in the database and your application.

(The script is the part in RED)
-----------------------------------------------


USE --DATABASENAME
SET NOCOUNT ON;
DECLARE @objectid int;
DECLARE @indexid int;
DECLARE @partitioncount bigint;
DECLARE @schemaname nvarchar(130);
DECLARE @objectname nvarchar(130);
DECLARE @indexname nvarchar(130);
DECLARE @partitionnum bigint;
DECLARE @partitions bigint;
DECLARE @frag float;
DECLARE @command nvarchar(4000);
-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function
-- and convert object and index IDs to names.
SELECT
object_id AS objectid,
index_id AS indexid,
partition_number AS partitionnum,
avg_fragmentation_in_percent AS frag
INTO #work_to_do
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')
WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;

-- Declare the cursor for the list of partitions to be processed.
DECLARE partitions CURSOR FOR SELECT * FROM #work_to_do;

-- Open the cursor.
OPEN partitions;

-- Loop through the partitions.
WHILE (1=1)
BEGIN;
FETCH NEXT
FROM partitions
INTO @objectid, @indexid, @partitionnum, @frag;
IF @@FETCH_STATUS <>
SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)
FROM sys.objects AS o
JOIN sys.schemas as s ON s.schema_id = o.schema_id
WHERE o.object_id = @objectid;
SELECT @indexname = QUOTENAME(name)
FROM sys.indexes
WHERE object_id = @objectid AND index_id = @indexid;
SELECT @partitioncount = count (*)
FROM sys.partitions
WHERE object_id = @objectid AND index_id = @indexid;

-- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding.
IF @frag <>
SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';
IF @frag >= 30.0
SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD';
IF @partitioncount > 1
SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10));
EXEC (@command);
PRINT N'Executed: ' + @command;
END;

-- Close and deallocate the cursor.
CLOSE partitions;
DEALLOCATE partitions;

-- Drop the temporary table.
DROP TABLE #work_to_do;
GO
Etiquetas: , , , , ,

2 comentarios:

Anónimo dijo...

Leer el mundo blog, bastante bueno

28 de septiembre de 2010, 13:07:00 GMT-7
Anónimo dijo...

tres interessant, merci

18 de febrero de 2011, 7:03:00 GMT-8

Publicar un comentario

Suscribirse a: Enviar comentarios (Atom)

Blogger Template created with Artisteer.