Sql-Server

在呼叫數據庫上下文中執行的中央儲存過程

  • April 28, 2017

我正在使用該sys.dm_db_index_physical_stats視圖開發定制的維護解決方案。我目前從儲存過程中引用它。現在，當該儲存過程在我的一個數據庫上執行時，它會執行我希望它執行的操作並拉下有關任何數據庫的所有記錄的列表。當我將它放在不同的數據庫上時，它會拉下僅與該數據庫相關的所有記錄的列表。

例如（底部的程式碼）：

  • 針對數據庫 6 執行的查詢顯示$$ requested $$數據庫 1-10 的資訊。
  • 針對數據庫 3 執行的查詢顯示$$ requested $$僅數據庫 3 的資訊。

我希望這個過程專門用於數據庫 3 的原因是因為我更願意將所有維護對象保存在同一個數據庫中。我想讓這份工作放在維護數據庫中，就像在那個應用程序數據庫中一樣工作。

程式碼：

ALTER PROCEDURE [dbo].[GetFragStats] 
   @databaseName   NVARCHAR(64) = NULL
   ,@tableName     NVARCHAR(64) = NULL
   ,@indexID       INT          = NULL
   ,@partNumber    INT          = NULL
   ,@Mode          NVARCHAR(64) = 'DETAILED'
AS
BEGIN
   SET NOCOUNT ON;

   DECLARE @databaseID INT, @tableID INT

   IF @databaseName IS NOT NULL
       AND @databaseName NOT IN ('tempdb','ReportServerTempDB')
   BEGIN
       SET @databaseID = DB_ID(@databaseName)
   END

   IF @tableName IS NOT NULL
   BEGIN
       SET @tableID = OBJECT_ID(@tableName)
   END

   SELECT D.name AS DatabaseName,
     T.name AS TableName,
     I.name AS IndexName,
     S.index_id AS IndexID,
     S.avg_fragmentation_in_percent AS PercentFragment,
     S.fragment_count AS TotalFrags,
     S.avg_fragment_size_in_pages AS PagesPerFrag,
     S.page_count AS NumPages,
     S.index_type_desc AS IndexType
   FROM sys.dm_db_index_physical_stats(@databaseID, @tableID, 
          @indexID, @partNumber, @Mode) AS S
   JOIN 
      sys.databases AS D ON S.database_id = D.database_id
   JOIN 
      sys.tables AS T ON S.object_id = T.object_id
   JOIN 
      sys.indexes AS I ON S.object_id = I.object_id
                       AND S.index_id = I.index_id
   WHERE 
       S.avg_fragmentation_in_percent > 10
   ORDER BY 
       DatabaseName, TableName, IndexName, PercentFragment DESC    
END
GO

一種方法是在其中創建一個系統過程，master然後在您的維護數據庫中創建一個包裝器。請注意，這一次僅適用於一個數據庫。

首先，在主人：

USE [master];
GO
CREATE PROCEDURE dbo.sp_GetFragStats -- sp_prefix required
 @tableName    NVARCHAR(128) = NULL,
 @indexID      INT           = NULL,
 @partNumber   INT           = NULL,
 @Mode         NVARCHAR(20)  = N'DETAILED'
AS
BEGIN
 SET NOCOUNT ON;

 SELECT
   DatabaseName    = DB_NAME(),
   TableName       = t.name,
   IndexName       = i.name,
   IndexID         = s.index_id,
   PercentFragment = s.avg_fragmentation_in_percent,
   TotalFrags      = s.fragment_count,
   PagesPerFrag    = s.avg_fragment_size_in_pages,
   NumPages        = s.page_count,
   IndexType       = s.index_type_desc
   -- shouldn't s.partition_number be part of the output as well?
 FROM sys.tables AS t
 INNER JOIN sys.indexes AS i
   ON t.[object_id] = i.[object_id]
   AND i.index_id = COALESCE(@indexID, i.index_id)
   AND t.name = COALESCE(@tableName, t.name)
 CROSS APPLY
   sys.dm_db_index_physical_stats(DB_ID(), t.[object_id], 
     i.index_id, @partNumber, @Mode) AS s
 WHERE s.avg_fragmentation_in_percent > 10
 -- probably also want to filter on minimum page count too
 -- do you really care about a table that has 100 pages?
 ORDER BY 
   DatabaseName, TableName, IndexName, PercentFragment DESC;
END
GO
-- needs to be marked as a system object:
EXEC sp_MS_MarkSystemObject N'dbo.sp_GetFragStats';
GO

現在，在您的維護數據庫中，創建一個使用動態 SQL 正確設置上下文的包裝器：

USE YourMaintenanceDatabase;
GO
CREATE PROCEDURE dbo.GetFragStats
 @DatabaseName SYSNAME,      -- can't really be NULL, right?
 @tableName    NVARCHAR(128) = NULL,
 @indexID      INT           = NULL,
 @partNumber   INT           = NULL,
 @Mode         NVARCHAR(20)  = N'DETAILED'
AS
BEGIN
 DECLARE @sql NVARCHAR(MAX);

 SET @sql = N'USE ' + QUOTENAME(@DatabaseName) + ';
   EXEC dbo.sp_GetFragStats @tableName, @indexID, @partNumber, @Mode;';

 EXEC sp_executesql 
   @sql,
   N'@tableName NVARCHAR(128),@indexID INT,@partNumber INT,@Mode NVARCHAR(20)',
   @tableName, @indexID, @partNumber, @Mode;
END
GO

（數據庫名稱不能真正成為NULL的原因是因為你不能加入類似的東西，sys.objects並且sys.indexes它們獨立存在於每個數據庫中。所以如果你想要實例範圍的資訊，可能會有不同的過程。）

現在您可以為任何其他數據庫呼叫它，例如

EXEC YourMaintenanceDatabase.dbo.GetFragStats 
 @DatabaseName = N'AdventureWorks2012',
 @TableName    = N'SalesOrderHeader';

而且您始終可以synonym在每個數據庫中創建一個，因此您甚至不必引用維護數據庫的名稱：

USE SomeOtherDatabase;`enter code here`
GO
CREATE SYNONYM dbo.GetFragStats FOR YourMaintenanceDatabase.dbo.GetFragStats;

另一種方法是使用動態 SQL，但是這也一次只適用於一個數據庫：

USE YourMaintenanceDatabase;
GO
CREATE PROCEDURE dbo.GetFragStats
 @DatabaseName SYSNAME,
 @tableName    NVARCHAR(128) = NULL,
 @indexID      INT           = NULL,
 @partNumber   INT           = NULL,
 @Mode         NVARCHAR(20)  = N'DETAILED'
AS
BEGIN
 SET NOCOUNT ON;

 DECLARE @sql NVARCHAR(MAX) = N'SELECT
   DatabaseName    = @DatabaseName,
   TableName       = t.name,
   IndexName       = i.name,
   IndexID         = s.index_id,
   PercentFragment = s.avg_fragmentation_in_percent,
   TotalFrags      = s.fragment_count,
   PagesPerFrag    = s.avg_fragment_size_in_pages,
   NumPages        = s.page_count,
   IndexType       = s.index_type_desc
 FROM ' + QUOTENAME(@DatabaseName) + '.sys.tables AS t
 INNER JOIN ' + QUOTENAME(@DatabaseName) + '.sys.indexes AS i
   ON t.[object_id] = i.[object_id]
   AND i.index_id = COALESCE(@indexID, i.index_id)
   AND t.name = COALESCE(@tableName, t.name)
 CROSS APPLY
   ' + QUOTENAME(@DatabaseName) + '.sys.dm_db_index_physical_stats(
       DB_ID(@DatabaseName), t.[object_id], i.index_id, @partNumber, @Mode) AS s
 WHERE s.avg_fragmentation_in_percent > 10
 ORDER BY 
   DatabaseName, TableName, IndexName, PercentFragment DESC;';

 EXEC sp_executesql @sql, 
   N'@DatabaseName SYSNAME, @tableName NVARCHAR(128), @indexID INT,
     @partNumber INT, @Mode NVARCHAR(20)',
   @DatabaseName, @tableName, @indexID, @partNumber, @Mode;
END
GO

另一種方法是創建一個視圖（或表值函式）來合併所有數據庫的表和索引名稱，但是您必須將數據庫名稱硬編碼到視圖中，並在添加時維護它們/刪除您希望允許包含在此查詢中的數據庫。與其他方法不同，這將允許您一次檢索多個數據庫的統計資訊。

一、觀點：

CREATE VIEW dbo.CertainTablesAndIndexes
AS
 SELECT 
   db = N'AdventureWorks2012',
   t.[object_id],
   [table] = t.name,
   i.index_id,
   [index] = i.name
 FROM AdventureWorks2012.sys.tables AS t
 INNER JOIN AdventureWorks2012.sys.indexes AS i
 ON t.[object_id] = i.[object_id]

 UNION ALL

 SELECT 
   db = N'database2',
   t.[object_id],
   [table] = t.name,
   i.index_id,
   [index] = i.name
 FROM database2.sys.tables AS t
 INNER JOIN database2.sys.indexes AS i
 ON t.[object_id] = i.[object_id]

 -- ... UNION ALL ...
 ;
GO

然後程序：

CREATE PROCEDURE dbo.GetFragStats
 @DatabaseName NVARCHAR(128) = NULL,
 @tableName    NVARCHAR(128) = NULL,
 @indexID      INT           = NULL,
 @partNumber   INT           = NULL,
 @Mode         NVARCHAR(20)  = N'DETAILED'
AS
BEGIN
 SET NOCOUNT ON;

 SELECT
   DatabaseName    = DB_NAME(s.database_id),
   TableName       = v.[table],
   IndexName       = v.[index],
   IndexID         = s.index_id,
   PercentFragment = s.avg_fragmentation_in_percent,
   TotalFrags      = s.fragment_count,
   PagesPerFrag    = s.avg_fragment_size_in_pages,
   NumPages        = s.page_count,
   IndexType       = s.index_type_desc
 FROM dbo.CertainTablesAndIndexes AS v
 CROSS APPLY sys.dm_db_index_physical_stats
   (DB_ID(v.db), v.[object_id], v.index_id, @partNumber, @Mode) AS s
 WHERE s.avg_fragmentation_in_percent > 10
   AND v.index_id = COALESCE(@indexID, v.index_id)
   AND v.[table] = COALESCE(@tableName, v.[table])
   AND v.db = COALESCE(@DatabaseName, v.db)
 ORDER BY 
   DatabaseName, TableName, IndexName, PercentFragment DESC;
END
GO

引用自：https://dba.stackexchange.com/questions/44764

相關問答

Sql-Server

Ola Hallengren 索引優化腳本不重建索引

  • May 6, 2019
檢視問答
Sql-Server

查找 <n> 天內未呼叫的過程

  • June 17, 2013
檢視問答
Sql-Server

為什麼需要此密鑰查找？

  • January 11, 2022
檢視問答
Sql-Server

Ola Hallengren 維護解決方案

  • November 28, 2021
檢視問答
Sql-Server

辨識所需索引的工具

  • September 16, 2020
檢視問答
Sql-Server

插入和更新的單獨儲存過程？

  • May 15, 2020
檢視問答