by Martin
24. August 2010 13:49
Ett ständigt problem när man jobbar med databaser är prestanda. Ofta handlar det om att hitta rätt index, något som inte alltid är så lätt.
Nedanstående script kan användas för att hitta saknade index i en databas. Den tar hjälp av tidigare exekverade frågor för att hitta index som kan tänkas saknas.
Ju högre siffra i kolumnen Score (längst till höger) desto viktigare index. Upp till 100 är oftast ingen större fara.
För att få köra koden krävs att man har rättigheten _VIEW SERVER STATE_
Koden är ursprungligen funnen på [url:SQL Server Central|http://www.sqlservercentral.com/scripts/Index+Management/63937/] men har sedan modifierats lite för läsbarhetens skull.
{code:sql}
DECLARE @SchemeName sysname
, @TableName sysname
, @Sort Tinyint
, @Delimiter VarChar(1)
, @crlf char(2)
SELECT @SchemeName = ''
, @TableName = ''
, @Sort = 1
, @Delimiter = ','
, @crlf = char(13) + char(10)
SELECT sch.schema_id
, 'schema_name' = sch.name
, so.object_id
, 'object_name' = so.name
, so.type
, partitions.Rows
, partitions.SizeMB
, 'equality_columns' = CASE WHEN @Delimiter =',' THEN mid.equality_columns ELSE REPLACE(mid.equality_columns, ',', @Delimiter) END
, 'inequality_columns' = CASE WHEN @Delimiter = ',' THEN mid.inequality_columns ELSE REPLACE(mid.inequality_columns, ',', @Delimiter) END
, 'included_columns' = CASE WHEN @Delimiter = ',' THEN mid.included_columns ELSE REPLACE(mid.included_columns, ',', @Delimiter) END
, migs.unique_compiles
, migs.user_seeks
, migs.user_scans
, migs.avg_total_user_cost
, migs.avg_user_impact
, migs.last_user_seek
, migs.last_user_scan
, migs.system_seeks
, migs.system_scans
, migs.avg_total_system_cost
, migs.avg_system_impact
, migs.last_system_seek
, migs.last_system_scan
, 'Score' = (CONVERT(Numeric(19, 6), migs.user_seeks) + CONVERT(Numeric(19, 6), migs.unique_compiles)) * CONVERT(Numeric(19, 6), migs.avg_total_user_cost) * CONVERT(Numeric(19, 6), migs.avg_user_impact / 100.0)
FROM sys.objects so
JOIN (
SELECT object_id
, 'Rows' = SUM(CASE WHEN index_id BETWEEN 0 AND 1 THEN row_count ELSE 0 END)
, 'SizeMB' = CONVERT(numeric(19,3), CONVERT(numeric(19,3), SUM(in_row_reserved_page_count+lob_reserved_page_count+row_overflow_reserved_page_count)) / CONVERT(numeric(19, 3), 128))
FROM sys.dm_db_partition_stats ddps
WHERE ddps.index_id BETWEEN 0 AND 1 --0=Heap; 1=Clustered; only 1 per table
GROUP BY object_id
) AS partitions
ON so.object_id = partitions.object_id
JOIN sys.schemas sch
ON so.schema_id = sch.schema_id
JOIN sys.dm_db_missing_index_details mid
ON so.object_id = mid.object_id
JOIN sys.dm_db_missing_index_groups mig
ON mid.index_handle = mig.index_handle
JOIN sys.dm_db_missing_index_group_stats migs
ON mig.index_group_handle = migs.group_handle
WHERE mid.database_id = DB_ID()
AND sch.name LIKE CASE WHEN @SchemeName = '' THEN sch.name ELSE @SchemeName END
AND so.name LIKE CASE WHEN @TableName = '' THEN so.name ELSE @TableName END
ORDER BY
CASE @Sort
WHEN 1 THEN
(CONVERT(Numeric(19, 6), migs.user_seeks) + CONVERT(Numeric(19, 6), migs.unique_compiles)) * CONVERT(Numeric(19, 6), migs.avg_total_user_cost) * CONVERT(Numeric(19, 6), migs.avg_user_impact / 100.0) * -1
WHEN 2 THEN
(CONVERT(Numeric(19, 6), migs.user_seeks) + CONVERT(Numeric(19, 6), migs.unique_compiles)) * CONVERT(Numeric(19, 6), migs.avg_total_user_cost) * CONVERT(Numeric(19, 6), migs.avg_user_impact / 100.0)
ELSE NULL
END,
CASE @Sort
WHEN 3 THEN sch.name
WHEN 4 THEN sch.name
WHEN 5 THEN sch.name
ELSE NULL
END,
CASE @Sort
WHEN 1 THEN migs.user_seeks * -1
WHEN 2 THEN migs.user_seeks
END,
CASE @Sort
WHEN 3 THEN so.name
WHEN 4 THEN so.name
WHEN 5 THEN so.name
ELSE NULL
END,
CASE @Sort
WHEN 1 THEN migs.avg_total_user_cost * -1
WHEN 2 THEN migs.avg_total_user_cost
WHEN 4 THEN
(CONVERT(Numeric(19, 6), migs.user_seeks)+CONVERT(Numeric(19, 6), migs.unique_compiles)) * CONVERT(Numeric(19, 6), migs.avg_total_user_cost) * CONVERT(Numeric(19, 6), migs.avg_user_impact / 100.0) * -1
WHEN 5 THEN
(CONVERT(Numeric(19, 6), migs.user_seeks)+CONVERT(Numeric(19, 6), migs.unique_compiles)) * CONVERT(Numeric(19, 6), migs.avg_total_user_cost) * CONVERT(Numeric(19, 6), migs.avg_user_impact / 100.0)
ELSE NULL
END,
CASE @Sort
WHEN 3 THEN mid.equality_columns
ELSE NULL
END
{code:sql}
by Martin
2. June 2010 11:54
Nedanstående skript städar index på en 2005 databas (OBS måste vara i 90-mode på kompabilitet, (dvs SQL2005)
Se till att rätt databas är vald innan man startar skriptet, det är generellt och kan köras på alla 2005 databaser.
Innan man kör detta kan det vara en poäng att kolla artikeln [kontrollera fragmenteringsgrad på index]
{code:sql}
SET
NOCOUNT ON;
DECLARE @objectid int;
DECLARE @indexid int;
DECLARE @partitioncount bigint;
DECLARE @schemaname sysname;
DECLARE @objectname sysname;
DECLARE @indexname sysname;
DECLARE @partitionnum bigint;
DECLARE @partitions bigint;
DECLARE @frag float;
DECLARE @command varchar(8000);
-- ensure the temporary table does not exist
IF EXISTS (SELECT name FROM sys.objects WHERE name = 'work_to_do')
DROP TABLE work_to_do;
-- conditionally select from the function, converting 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.
FETCH NEXT
FROM partitions
INTO @objectid, @indexid, @partitionnum, @frag;
WHILE @@FETCH_STATUS = 0
BEGIN;
SELECT @objectname = o.name, @schemaname = 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 = 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 < 30.0
BEGIN;
SELECT @command = 'ALTER INDEX ' + @indexname + ' ON ' + @schemaname + '.' + @objectname + ' REORGANIZE';
IF @partitioncount > 1
SELECT @command = @command + ' PARTITION=' + CONVERT (CHAR, @partitionnum);
EXEC (@command);
END;
IF @frag >= 30.0
BEGIN;
SELECT @command = 'ALTER INDEX ' + @indexname +' ON ' + @schemaname + '.' + @objectname + ' REBUILD';
IF @partitioncount > 1
SELECT @command = @command + ' PARTITION=' + CONVERT (CHAR, @partitionnum);
EXEC (@command);
END;
PRINT 'Executed ' + @command;
FETCH NEXT FROM partitions INTO @objectid, @indexid, @partitionnum, @frag;
END;
-- Close and deallocate the cursor.
CLOSE partitions;
DEALLOCATE partitions;
-- drop the temporary table
IF EXISTS (SELECT name FROM sys.objects WHERE name = 'work_to_do')
DROP TABLE work_to_do;
GO
{code:sql}
by Martin
22. May 2010 12:02
Nedanstående skript lista index fysiska fragmenteringsgrad, mest fragmenterad först
Om fragementeringsgrad i procent är mindre än 10% tas den ej med
Rekommendationen är att om frag är mellan 10-30% kör en REORGANIZE, om >30% kör REBUILD (kan göras med detta script: [Bygg om alla index i databasen])
OBS på små tabeller (dvs om Page_count är lågt, <10) så kan man få höga procentsiffror på fragmenteringen ändå, men då är fragmentering ändå inget problem
{code:sql}
SELECT 'objectname' = o.Name
, 'indexname' = i.NAME
, phys.avg_fragmentation_in_percent
, phys.fragment_count
, phys.page_count
, 'record_count' = (SELECT sum(row_count) FROM sys.dm_db_partition_stats ps WHERE ps.index_id < 2 AND ps.object_id =phys.object_id)
, phys.index_type_desc
, 'schemaname' = s.NAME
, 'partitionnum' = phys.partition_number
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED') AS phys
INNER JOIN sys.objects AS o
ON o.OBJECT_ID=phys.object_id
INNER JOIN sys.schemas as s
ON s.schema_id = o.SCHEMA_ID
INNER JOIN sys.indexes AS i
ON i.OBJECT_ID=phys.OBJECT_ID AND i.index_id=phys.index_id
WHERE phys.index_id > 0
AND phys.avg_fragmentation_in_percent > 10.0
ORDER BY phys.avg_fragmentation_in_percent desc;
{code:sql}