Bufferpool leeren

DBCC DROPCLEANBUFFERS Verwenden Sie DBCC DROPCLEANBUFFERS, um Abfragen mit einem frischen Puffercache zu testen, ohne den Server herunterzufahren und neu zu starten.​

Plancache löschen

Löschen eines Abfrageplans im Plancache

USE AdventureWorks2008R2;

GO
  SELECT * FROM Person.Address;

GO
  SELECT plan_handle, st.text

FROM sys.dm_exec_cached_plans

CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st

WHERE text LIKE N'SELECT * FROM Person.Address%';

GO   
Dies ist das Resultset.
plan_handle                                         text
--------------------------------------------------  -----------------------------
0x060006001ECA270EC0215D05000000000000000000000000 SELECT * FROM Person.Address;
(1 Zeile(n) betroffen)
 
 

-- Remove the specific plan from the cache.
DBCC FREEPROCCACHE (0x060006001ECA270EC0215D05000000000000000000000000);

GO
 
Löschen aller Pläne im Plancache
SELECT * FROM sys.dm_resource_governor_resource_pools;

GO
   DBCC FREEPROCCACHE ('default');
GO

TOP 25 teuersten Abfragen

SELECT TOP (25)

qs.sql_handle,

qs.execution_count,

qs.total_worker_time AS Total_CPU,

total_CPU_inSeconds = --Converted from microseconds

qs.total_worker_time/1000000.00,

average_CPU_inSeconds = --Converted from microseconds

(qs.total_worker_time/1000000.00) / qs.execution_count,

qs.total_elapsed_time,

total_elapsed_time_inSeconds = --Converted from microseconds

qs.total_elapsed_time/1000000.00,

st.text,

qp.query_plan

FROM sys.dm_exec_query_stats AS qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st

CROSS apply sys.dm_exec_query_plan (qs.plan_handle) AS qp

ORDER BY qs.total_worker_time DESC OPTION (RECOMPILE);

TOP 25 teuersten Prozeduren

SELECT TOP (25)
p.name AS [SP Name],
qs.total_worker_time AS [TotalWorkerTime],
qs.total_worker_time/qs.execution_count AS [AvgWorkerTime],
qs.execution_count,
ISNULL(qs.execution_count/DATEDIFF(Second, qs.cached_time, GETDATE()), 0)
AS [Calls/Second],
qs.total_elapsed_time,
qs.total_elapsed_time/qs.execution_count AS [avg_elapsed_time],
qs.cached_time
FROM sys.procedures AS p WITH (NOLOCK)
INNER JOIN sys.dm_exec_procedure_stats AS qs WITH (NOLOCK)
ON p.[object_id] = qs.[object_id]
WHERE qs.database_id = DB_ID()
ORDER BY qs.total_worker_time DESC OPTION (RECOMPILE);

SET Einstellung von Stored Procedures abfragen (ANSI_NULLS & QUOTED_IDENTIFIER)

Wenn man sich mit dem SSMS nicht den Code für jede Prozedur generieren lassen will können diese Eigenschaften auch wie folgt abgefragt werden.
 
SELECT name = OBJECT_NAME(m.[object_id]), m.uses_ansi_nulls, m.uses_quoted_identifier
FROM sys.sql_modules m
order by OBJECT_NAME(m.[object_id])

Datafile Spaceusage

Liefert die Datafile Auslastung aller Datenbanken einer SQL-Server Instanz
 
DECLARE @PGSize_Byte int
DECLARE @DBName nvarchar(100)
DECLARE @SQLString nvarchar(4000);
DECLARE @Tab_DBSpaces TABLE (
      Instanz varchar(256),
      DBName varchar(256),
      name varchar(256),
      type_desc varchar(256),
      filename varchar(256),
      FileSizeMB varchar(50),
      SpaceUsedMB varchar(50),
      FreeSpaceMB varchar(50),
      NextMB varchar(50),
      is_percent_growth tinyint,
      MaxSizeMB varchar(50)
)
 
SELECT @PGSize_Byte = low FROM master..spt_values WHERE number = 1 AND type = 'E'
 
DECLARE Cur_DB Cursor fast_forward for
Select name from sys.databases where state = 0 order by name
 
OPEN Cur_DB
Fetch NEXT FROM Cur_DB into @DBName
 
WHILE @@FETCH_STATUS = 0
BEGIN
      set @SQLString = N'USE [' + @DBName + ']
      select
      @@SERVERNAME
      ,''' + @DBName +''' as DBName
    , a.name
      , b.type_desc
      , filename
      , replace(convert(decimal(12,2),round(a.size/128.000,2)),''.'','','') as FileSizeMB
      , replace(convert(decimal(12,2),round(fileproperty(a.name,''SpaceUsed'')/128.000,2)),''.'','','') as SpaceUsedMB
      , replace(convert(decimal(12,2),round((a.size-fileproperty(a.name,''SpaceUsed''))/128.000,2)),''.'','','') as FreeSpaceMB
      , replace(convert(decimal(12,2),CASE WHEN b.is_percent_growth = 0 THEN round(b.growth/128.000,2) ELSE (round(a.size/128.000,2)) * b.growth / 100.00 END),''.'','','') as NextMB
      , b.is_percent_growth
      , replace(convert(decimal(12,2),round(a.maxsize/128.000,2)),''.'','','') as MaxSizeMB
      from dbo.sysfiles a
      inner join sys.database_files b
            on a.fileid = b.file_id'
     
      INSERT INTO @Tab_DBSpaces
      execute sp_executesql @SQLString
     
      Fetch NEXT FROM Cur_DB into @DBName
END
 
CLOSE Cur_DB
DEALLOCATE Cur_DB
 
 
Select * from @Tab_DBSpaces
 
 
Ergebnis:
 

Instanz	DBName	name	type_desc	filename	FileSizeMB	SpaceUsedMB	FreeSpaceMB	NextMB	is_percent_growth	MaxSizeMB
LOCALHOST	DM_Main	DM_Main	ROWS	C:\Program Files\Microsoft SQL Server\MSSQL10_50.DEV\MSSQL\DATA\DM_Main.mdf	10,25	8,69	1,56	1,00	0	-               0,01
LOCALHOST	DM_Main	DM_Main_log	LOG	C:\Program Files\Microsoft SQL Server\MSSQL10_50.DEV\MSSQL\DATA\DM_Main_log.ldf	14,69	1,94	12,75	1,47	1	2.097.152,00
LOCALHOST	DWH	Data01	ROWS	C:\Program Files\Microsoft SQL Server\MSSQL10_50.DEV\MSSQL\DATA\DWH_Data01.mdf	15,00	8,75	6,25	1,00	0	-               0,01
LOCALHOST	DWH	Log01_log	LOG	C:\Program Files\Microsoft SQL Server\MSSQL10_50.DEV\MSSQL\DATA\DWH_Log01.ldf	14,69	1,77	12,91	1,47	1	2.097.152,00
LOCALHOST	master	master	ROWS	C:\Program Files\Microsoft SQL Server\MSSQL10_50.DEV\MSSQL\DATA\master.mdf	4,00	2,94	1,06	0,40	1	-               0,01
LOCALHOST	master	mastlog	LOG	C:\Program Files\Microsoft SQL Server\MSSQL10_50.DEV\MSSQL\DATA\mastlog.ldf	1,25	0,55	0,70	0,13	1	-               0,01
LOCALHOST	model	modeldev	ROWS	C:\Program Files\Microsoft SQL Server\MSSQL10_50.DEV\MSSQL\DATA\model.mdf	2,25	1,44	0,81	1,00	0	-               0,01
LOCALHOST	model	modellog	LOG	C:\Program Files\Microsoft SQL Server\MSSQL10_50.DEV\MSSQL\DATA\modellog.ldf	0,75	0,28	0,47	0,08	1	-               0,01
LOCALHOST	msdb	MSDBData	ROWS	C:\Program Files\Microsoft SQL Server\MSSQL10_50.DEV\MSSQL\DATA\MSDBData.mdf	28,94	27,31	1,63	2,89	1	-               0,01
LOCALHOST	msdb	MSDBLog	LOG	C:\Program Files\Microsoft SQL Server\MSSQL10_50.DEV\MSSQL\DATA\MSDBLog.ldf	5,56	1,57	3,99	0,56	1	2.097.152,00
LOCALHOST	ReportServer	ReportServer	ROWS	C:\Program Files\Microsoft SQL Server\MSSQL10_50.DEV\MSSQL\DATA\ReportServer.mdf	3,25	3,25	-	1,00	0	-               0,01
LOCALHOST	ReportServer	ReportServer_log	LOG	C:\Program Files\Microsoft SQL Server\MSSQL10_50.DEV\MSSQL\DATA\ReportServer_log.LDF	0,81	0,34	0,47	0,08	1	2.097.152,00
LOCALHOST	ReportServerTempDB	ReportServerTempDB	ROWS	C:\Program Files\Microsoft SQL Server\MSSQL10_50.DEV\MSSQL\DATA\ReportServerTempDB.mdf	2,25	1,50	0,75	1,00	0	-               0,01
LOCALHOST	ReportServerTempDB	ReportServerTempDB_log	LOG	C:\Program Files\Microsoft SQL Server\MSSQL10_50.DEV\MSSQL\DATA\ReportServerTempDB_log.LDF	0,81	0,49	0,32	0,08	1	2.097.152,00
LOCALHOST	Stage	Stage	ROWS	C:\Program Files\Microsoft SQL Server\MSSQL10_50.DEV\MSSQL\DATA\Stage.mdf	9.837,25	286,81	9.550,44	1,00	0	-               0,01
LOCALHOST	Stage	Stage_log	LOG	C:\Program Files\Microsoft SQL Server\MSSQL10_50.DEV\MSSQL\DATA\Stage_log.ldf	894,88	14,34	880,54	89,49	1	2.097.152,00
LOCALHOST	tempdb	tempdev	ROWS	C:\Program Files\Microsoft SQL Server\MSSQL10_50.DEV\MSSQL\DATA\tempdb.mdf	8,00	2,88	5,13	0,80	1	-               0,01
LOCALHOST	tempdb	templog	LOG	C:\Program Files\Microsoft SQL Server\MSSQL10_50.DEV\MSSQL\DATA\templog.ldf	0,75	0,55	0,20	0,08	1	-               0,01
LOCALHOST	Test	Test	ROWS	C:\Program Files\Microsoft SQL Server\MSSQL10_50.DEV\MSSQL\DATA\Test.mdf	170,00	169,13	0,88	1,00	0	-               0,01
LOCALHOST	Test	Test_log	LOG	C:\Program Files\Microsoft SQL Server\MSSQL10_50.DEV\MSSQL\DATA\Test_log.ldf	2,75	0,59	2,16	0,28	1	2.097.152,00