/************************************************************** Bitte als SYSDBA anmelden. DEFRAGMENTATION von Indizes - es ist nicht notwendig zusätzliche Bibliotheken aufzurufen ACHTUNG: HIER SIND ZWEI STELLEN FREIZUSCHALTEN!!! HINWEIS: die richtige DB festlegen ***************************************************************/ use --FranksDB go --Hier ist bewusst ein Fehler eingebaut, damit die DB korrekt festgelegt ist --DEFRAGMENTATION: 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; --diesen parameter "DB_ID()" darf man nicht benutzten - wieso auch immmer DECLARE @DBID bigint; select @DBID=DB_ID(); -- 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 (@DBID, 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 <= 10.0 BEGIN; SELECT @command = 'Index: [' + @indexname + '] ON [' + @schemaname + '].[' + @objectname + ']...OK'; END; IF @frag > 10.0 AND @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); -----HIER FREISCHALTEN!!!!! 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); -----HIER FREISCHALTEN!!!!! 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 --Select * from work_to_do IF EXISTS (SELECT name FROM sys.objects WHERE name = 'work_to_do') DROP TABLE work_to_do; -- FERTIG Meldung PRINT 'All Indices was reorganized or rebuilded. '
T-SQL: Fehlende Indizes…
Häufig scheinen Abfragen überraschend langsam sein, oder immer langsam werden…. Als Tunings-Maßnahme lohnen sich meist als erster Versuch Indizes in der Datenbank. Lediglich sind bestimmte Rechte für der u. g. TSQL-Code notwendig
-- Missing Index Script
-- Original Author: Pinal Dave (C) 2011
-- verändert von Frank Odenbreit 2011/2013/2016
/*
Es werden zwar die 50 wichtigsten Indizes geholt und diese nachher sortiert nach ihrem Namen
(Ähnlichkeiten sollte man erkennen können, ggf. anpassen bzw. aus kommentieren)
*/
Select *
from (
SELECT TOP 50
dm_mid.database_id AS DatabaseID,
dm_migs.user_seeks as user_seeks,
dm_migs.avg_user_impact as avg_user_impact,
dm_migs.avg_user_impact * (dm_migs.user_seeks+dm_migs.user_scans) AS Avg_Estimated_Impact,
dm_migs.last_user_seek AS Last_User_Seek, object_name(dm_mid.object_id) AS [TableName],
--*,
'CREATE NONCLUSTERED INDEX [ndx' + object_name(dm_mid.object_id) + '__'
+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.equality_columns,''),', ','_'),'[',''),']','') +
CASE
WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns IS NOT NULL THEN '_'
ELSE ''
END
+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.inequality_columns,''),', ','_'),'[',''),']','')
+ ']'
+ ' ON ' + dm_mid.statement + ' (' + ISNULL (dm_mid.equality_columns,'')
+ CASE WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns IS NOT NULL THEN ',' ELSE
'' END
+ ISNULL (dm_mid.inequality_columns, '')
+ ')'
+ ISNULL (' INCLUDE (' + dm_mid.included_columns + ')', '')
+ ' WITH (pad_index=OFF,Fillfactor=90,ignore_dup_key=OFF,allow_row_locks=ON,allow_page_locks=ON,DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]'
AS Create_Statement
FROM sys.dm_db_missing_index_groups dm_mig
INNER JOIN sys.dm_db_missing_index_group_stats dm_migs ON dm_migs.group_handle = dm_mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details dm_mid ON dm_mig.index_handle = dm_mid.index_handle
WHERE dm_mid.database_ID = DB_ID()
ORDER BY Avg_Estimated_Impact DESC
) as t
order by Create_Statement asc
Tabelle AsyncOperationBase: Viele Sperren und viele Sätze
Gelegentlich kommt es vor, dass das MSCRM hängt und die Anwender auch nicht benutzten können und asynchrone Jobs sich selber sperren.
Es hilft meist, den „Async Job“ einfach Neustarten…
/*Anzahl der Sätze Total*/
Select Count(1) from [dbo].[AsyncOperationBase] with (nolock)
/*Benutzung*/
Select Sum(Coalesce(Len([Data]),0)) as [Data]
,Sum(Coalesce(Len([WorkflowState]),0)) as [WorkflowState]
,Sum(Coalesce(Len([Message]),0)) as [Message]
,Sum(Coalesce(Len([RegardingObjectIdName]),0)) as [RegardingObjectIdName]
,Sum(Coalesce(Len([RegardingObjectIdYomiName]),0)) as [RegardingObjectIdName]
,Sum(Coalesce(Len([FriendlyMessage]),0)) as [FriendlyMessage]
,Sum(Coalesce(Len([OwningExtensionIdName]),0)) as [OwningExtensionIdName]
from [dbo].[AsyncOperationBase] with (nolock)
/*Interner Zustand der Tabelle... z.B. falls diese riesig ist*/
SELECT 'AsyncOperationBase' Tablename, type_desc, total_pages * 8 as total_pages_in_KB, used_pages * 8 as used_pages_in_KB, total_pages, used_pages, data_pages,container_id, allocation_unit_id
FROM sys.allocation_units
WHERE container_id in (SELECT partition_id FROM sys.partitions WHERE OBJECT_ID = OBJECT_ID('AsyncOperationBase') )
/*Verteilung bezüglich des StatusCode*/
Select StatusCode, COUNT(1) Anzahl, Case when StatusCode=0 Then 'Ready/WaitingForResources'
when StatusCode=10 Then 'Suspended/Waiting'
when StatusCode=20 Then 'Locked/InProgress'
when StatusCode=21 Then 'Locked/Pausing'
when StatusCode=22 Then 'Locked/Canceling'
when StatusCode=30 Then 'Completed/Succeeded'
when StatusCode=31 Then 'Completed/Failed'
when StatusCode=32 Then 'Completed/Canceled'
Else 'unbekannter Status'
END StatusCode_Readable
FROM dbo.[AsyncOperationBase] with (nolock)
Group by StatusCode
Wenn es mehrere Millionen Sätze gibt, dann lohnt sich die Missing Indizees einmal anzuschauen…
Es gibt normalerweise eine regelmäßigen Bereinigungs-Job, der alte Sätze löscht.
Die langsamsten SQL Statements
/*
-----------------------------------------------------
-- Infos zu den langsamsten SQL-Statement
-- use master
-- Es muss gewisse Rechte bestehen (z.B. "SA") "sysdba" reicht nicht aus"
Hinweis: Durations können z.B. aus dem Einlesen von vielen Millionen von Sätzen stammen
Ebenso bitte beachten, dass AvgDuration[s] meist aus "Warten" von gesperrten Objekten stammen.
Daher sollte die AvgCpuTime und die Executions besonders bewertet werden!
-----------------------------------------------------
Änderungen:
2016-02-19 ORDER BY "AvgCpuTime[s]"
*/
use master
go
SELECT TOP(200)
qs.execution_count AS "Executions",
--CAST(CAST(qs.total_elapsed_time AS NUMERIC(20,4))/1000/qs.execution_count AS NUMERIC(20,4))
--CAST(CAST(qs.total_worker_time AS NUMERIC(20,4))/1000/qs.execution_count AS NUMERIC(20,4))
--AS "AvgCpuTime[ms]", /*Umrechung_in_Millisekunden*/
cast(CAST(CAST(qs.total_worker_time AS NUMERIC(20,4))/1000/qs.execution_count AS NUMERIC(20,4)) / 1000 as Numeric (20,1))
AS "AvgCpuTime[s]", /*Umrechung_in_Sekunden*/
SUBSTRING(st.text,(qs.statement_start_offset+2)/2, /*Offset wird in Bytes angegeben*/
(CASE WHEN qs.statement_end_offset = -1
THEN LEN(CAST(st.text AS NVARCHAR(MAX)))*2
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2)
AS "SqlStatement",
--AS "AvgDuration[ms]", /*Umrechung_in_Millisekunden*/
CAST(CAST(CAST(qs.total_elapsed_time AS NUMERIC(20,4))/1000/qs.execution_count AS NUMERIC(20,4)) / 1000 as Numeric (20,1))
AS "AvgDuration[s]", /*Umrechung_in_Sekunden*/
CAST (CAST(CAST(qs.last_elapsed_time AS NUMERIC(20,4))/1000 AS NUMERIC(20,4)) / 1000 as Numeric (20,1))
AS "LastDuration[s]", /*Umrechung in Sekunden*/
CAST( CAST(CAST(qs.last_worker_time AS NUMERIC(20,4))/1000 AS NUMERIC(20,4)) / 1000 as Numeric (20,1))
AS "LastCpuTime[s]", /*Umrechung in Sekunden*/
CAST( CAST(CAST(qs.total_elapsed_time AS NUMERIC(20,4))/1000 AS NUMERIC(20,4)) / 1000 as Numeric (20,1))
AS "TotalDuration[s]", /*Umrechung in Sekunden*/
CAST ( CAST(CAST(qs.total_worker_time AS NUMERIC(20,4))/1000 AS NUMERIC(20,4)) / 1000 as Numeric (20,1))
AS "TotalCpuTime[s]", /*Umrechung in Sekunden*/
qs.total_logical_reads AS "TotalLogicalReads",
qs.total_physical_reads AS "TotalPhysicalReads",
qs.total_logical_writes AS "TotalLogicalWrites",
qs.creation_time AS "FirstExecution",
qs.last_execution_time AS "LastExecution",
db_name(st.dbid) AS "Database"
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS st
WHERE qs.total_elapsed_time > 0
-- and qs.last_execution_time > '2016-02-18 19:00:00'
-- and (qs.last_execution_time < '2016-02-18 19:00:00' or qs.creation_time < '2016-02-18 19:00:00')
--ORDER BY "AvgDuration[ms]" DESC, qs.execution_count DESC
--ORDER BY "AvgDuration[s]" DESC, qs.execution_count DESC
ORDER BY "AvgCpuTime[s]" DESC, qs.execution_count DESC
/*
Select * FROM sys.dm_exec_query_stats AS qs
*/