SQL Server 日常維護經典應用

来源:http://www.cnblogs.com/jearay/archive/2017/10/23/7715658.html
-Advertisement-
Play Games

SQL Server日常維護常用的一些腳本整理。 1.sql server開啟clr許可權： 2.查詢資料庫大小 3.資料庫日誌壓縮 4.查看資料庫連接用戶 5.查看當前占用 cpu 資源最高的會話和其中執行的語句(及時CPU) 6.查看緩存中重用次數少，占用記憶體大的查詢語句（當前緩存中未釋放的）-- ...


SQL Server日常維護常用的一些腳本整理。

1.sql server開啟clr許可權：

exec sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO
ALTER DATABASE HWMESTC SET TRUSTWORTHY ON
ALTER AUTHORIZATION ON Database::HWMESTC TO sa;

2.查詢資料庫大小

Exec sp_spaceused
select name, convert(float,size) * (8192.0/1024.0)/1024. from dbo.sysfiles

3.資料庫日誌壓縮

--選擇需要使用的資料庫
USE PIMS
--將資料庫模式設置為SIMPLE
ALTER DATABASE PIMS SET RECOVERY SIMPLE
-- 將日誌文件收縮到1M 
DBCC SHRINKFILE ('PIMS_log', 1)
-- 還原資料庫
ALTER DATABASE PIMS SET RECOVERY FULL

4.查看資料庫連接用戶

Select * From sys.dm_exec_connections

5.查看當前占用 cpu 資源最高的會話和其中執行的語句(及時CPU)

select spid,cmd,cpu,physical_io,memusage,
(select top 1 [text] from ::fn_get_sql(sql_handle)) sql_text
from master..sysprocesses order by cpu desc,physical_io desc

6.查看緩存中重用次數少，占用記憶體大的查詢語句（當前緩存中未釋放的）--全局

SELECT TOP 100 usecounts, objtype, p.size_in_bytes,[sql].[text] 
FROM sys.dm_exec_cached_plans p OUTER APPLY sys.dm_exec_sql_text (p.plan_handle) sql 
ORDER BY usecounts,p.size_in_bytes  desc

7.看BUFFER POOL中，都緩存了哪些表(當前資料庫)的數據

select OBJECT_NAME(object_id) 表名,COUNT(*) 頁數,COUNT(*)*8/1024.0 Mb                              
from   sys.dm_os_buffer_descriptors a,sys.allocation_units b,sys.partitions c                              
where  a.allocation_unit_id=b.allocation_unit_id   
       and b.container_id=c.hobt_id             
       and database_id=DB_ID()                              
group by OBJECT_NAME(object_id)                           
order by 2 desc

8.查詢SQLSERVER記憶體使用情況

select * from sys.dm_os_process_memory

9.查詢SqlServer總體的記憶體使用情況

select      type,
sum(virtual_memory_reserved_kb)*0.1*10/1024/1024 as vm_Reserved_gb,--保留的記憶體  
sum(virtual_memory_committed_kb)*0.1*10/1024/1024 as vm_Committed_gb,--提交的記憶體  
sum(awe_allocated_kb)*0.1*10/1024/1024 as awe_Allocated_gb,--開啟AWE後使用的記憶體  
sum(shared_memory_reserved_kb)*0.1*10/1024/1024 as sm_Reserved_gb,--共用的保留記憶體  
sum(shared_memory_committed_kb)*0.1*10/1024/1024 as sm_Committed_gb--共用的提交記憶體  
from    sys.dm_os_memory_clerks
group by type
order by type

10.查詢當前資料庫緩存的所有數據頁面，哪些數據表，緩存的數據頁面數量

-- 查詢當前資料庫緩存的所有數據頁面，哪些數據表，緩存的數據頁面數量
-- 從這些信息可以看出，系統經常要訪問的都是哪些表，有多大？
select p.object_id, object_name=object_name(p.object_id), p.index_id, buffer_pages=count(*) 
from sys.allocation_units a, 
    sys.dm_os_buffer_descriptors b, 
    sys.partitions p 
where a.allocation_unit_id=b.allocation_unit_id 
    and a.container_id=p.hobt_id 
    and b.database_id=db_id()
group by p.object_id,p.index_id 
order by buffer_pages desc

11.查詢緩存的各類執行計劃，及分別占了多少記憶體

-- 查詢緩存的各類執行計劃，及分別占了多少記憶體
-- 可以對比動態查詢與參數化SQL（預定義語句）的緩存量
select    cacheobjtype
        , objtype
        , sum(cast(size_in_bytes as bigint))/1024 as size_in_kb
        , count(bucketid) as cache_count
from    sys.dm_exec_cached_plans
group by cacheobjtype, objtype
order by cacheobjtype, objtype

12.查詢緩存中具體的執行計劃，及對應的SQL

-- 查詢緩存中具體的執行計劃，及對應的SQL
-- 將此結果按照數據表或SQL進行統計，可以作為基線，調整索引時考慮
-- 查詢結果會很大，註意將結果集輸出到表或文件中
SELECT  usecounts ,
        refcounts ,
        size_in_bytes ,
        cacheobjtype ,
        objtype ,
        TEXT
FROM    sys.dm_exec_cached_plans cp
        CROSS APPLY sys.dm_exec_sql_text(plan_handle)
ORDER BY objtype DESC ;
GO

13.查詢sql server記憶體整體使用情況

--查詢sql server記憶體整體使用情況
  SELECT object_name, cntr_value*0.1*10/1024/1024 ,cntr_value,cntr_type,t.counter_name,t.instance_name
  FROM sys.dm_os_performance_counters t
  WHERE counter_name = 'Total Server Memory (KB)';

14.一次性清楚資料庫所有表的數據

CREATE PROCEDURE sp_DeleteAllData  
AS  
EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'  
EXEC sp_MSForEachTable 'ALTER TABLE ? DISABLE TRIGGER ALL'  
EXEC sp_MSForEachTable 'DELETE FROM ?'  
EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'  
EXEC sp_MSForEachTable 'ALTER TABLE ? ENABLE TRIGGER ALL'  
EXEC sp_MSFOREACHTABLE 'SELECT * FROM ?'  
GO

15.SQL優化相關、執行時間

SELECT creation_time  N'語句編譯時間'  
        ,last_execution_time  N'上次執行時間'  
        ,total_physical_reads N'物理讀取總次數'  
        ,total_logical_reads/execution_count N'每次邏輯讀次數'  
        ,total_logical_reads  N'邏輯讀取總次數'  
        ,total_logical_writes N'邏輯寫入總次數'  
        ,execution_count  N'執行次數'  
        ,total_worker_time/1000 N'所用的CPU總時間ms'  
        ,total_elapsed_time/1000  N'總花費時間ms'  
        ,(total_elapsed_time / execution_count)/1000  N'平均時間ms'  
        ,SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,  
         ((CASE statement_end_offset   
          WHEN -1 THEN DATALENGTH(st.text)  
          ELSE qs.statement_end_offset END   
            - qs.statement_start_offset)/2) + 1) N'執行語句'  
FROM sys.dm_exec_query_stats AS qs  
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st  
WHERE SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,  
         ((CASE statement_end_offset   
          WHEN -1 THEN DATALENGTH(st.text)  
          ELSE qs.statement_end_offset END   
            - qs.statement_start_offset)/2) + 1) NOT LIKE '%fetch%'  
ORDER BY  total_elapsed_time / execution_count DESC;

16.truncate外鍵表存儲過程

USE PIMS
GO

CREATE PROCEDURE [dbo].[usp_Truncate_Table]
  @TableToTruncate VARCHAR(64)
AS 

BEGIN

SET NOCOUNT ON

--==變數定義
DECLARE @i int
DECLARE @Debug bit
DECLARE @Recycle bit
DECLARE @Verbose bit
DECLARE @TableName varchar(80)
DECLARE @ColumnName varchar(80)
DECLARE @ReferencedTableName varchar(80)
DECLARE @ReferencedColumnName varchar(80)
DECLARE @ConstraintName varchar(250)

DECLARE @CreateStatement varchar(max)
DECLARE @DropStatement varchar(max)   
DECLARE @TruncateStatement varchar(max)
DECLARE @CreateStatementTemp varchar(max)
DECLARE @DropStatementTemp varchar(max)
DECLARE @TruncateStatementTemp varchar(max)
DECLARE @Statement varchar(max)

 SET @Debug = 0--(0:將執行相關語句|1:不執行語句)
 SET @Recycle = 0--(0:不創建/不清除存儲表|1:將創建/清理存儲表)
 set @Verbose = 1--(1:每步執行均列印消息|0:不列印消息)

 SET @i = 1
    SET @CreateStatement = 'ALTER TABLE [dbo].[<tablename>]  WITH NOCHECK ADD  CONSTRAINT [<constraintname>] FOREIGN KEY([<column>]) REFERENCES [dbo].[<reftable>] ([<refcolumn>])'
    SET @DropStatement = 'ALTER TABLE [dbo].[<tablename>] DROP CONSTRAINT [<constraintname>]'
    SET @TruncateStatement = 'TRUNCATE TABLE [<tablename>]'

-- 創建外鍵臨時表
IF OBJECT_ID('tempdb..#FKs') IS NOT NULL
    DROP TABLE #FKs

-- 獲取外鍵
SELECT ROW_NUMBER() OVER (ORDER BY OBJECT_NAME(parent_object_id), clm1.name) as ID,
       OBJECT_NAME(constraint_object_id) as ConstraintName,
       OBJECT_NAME(parent_object_id) as TableName,
       clm1.name as ColumnName, 
       OBJECT_NAME(referenced_object_id) as ReferencedTableName,
       clm2.name as ReferencedColumnName
  INTO #FKs
  FROM sys.foreign_key_columns fk
       JOIN sys.columns clm1 ON fk.parent_column_id = clm1.column_id AND fk.parent_object_id = clm1.object_id
       JOIN sys.columns clm2 ON fk.referenced_column_id = clm2.column_id AND fk.referenced_object_id= clm2.object_id
 --WHERE OBJECT_NAME(parent_object_id) not in ('//tables that you do not wont to be truncated')
 WHERE OBJECT_NAME(referenced_object_id) = @TableToTruncate
 ORDER BY OBJECT_NAME(parent_object_id)

-- 外鍵操作(刪除|重建)表
IF Not EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Internal_FK_Definition_Storage')
BEGIN
    IF @Verbose = 1
        PRINT '1. 正在創建表(Internal_FK_Definition_Storage)...'
    CREATE TABLE [Internal_FK_Definition_Storage] 
    (
        ID int not null identity(1,1) primary key,
        FK_Name varchar(250) not null,
        FK_CreationStatement varchar(max) not null,
        FK_DestructionStatement varchar(max) not null,
        Table_TruncationStatement varchar(max) not null
    ) 
END 
ELSE
BEGIN
    IF @Recycle = 0
    BEGIN
        IF @Verbose = 1
        PRINT '1. 正在清理表(Internal_FK_Definition_Storage)...'
        TRUNCATE TABLE [Internal_FK_Definition_Storage]    
    END
    ELSE
        PRINT '1. 正在清理表(Internal_FK_Definition_Storage)...'
END

IF @Recycle = 0
BEGIN
    IF @Verbose = 1
        PRINT '2. 正在備份外鍵定義...'           
    WHILE (@i <= (SELECT MAX(ID) FROM #FKs))
    BEGIN
        SET @ConstraintName = (SELECT ConstraintName FROM #FKs WHERE ID = @i)
        SET @TableName = (SELECT TableName FROM #FKs WHERE ID = @i)
        SET @ColumnName = (SELECT ColumnName FROM #FKs WHERE ID = @i)
        SET @ReferencedTableName = (SELECT ReferencedTableName FROM #FKs WHERE ID = @i)
        SET @ReferencedColumnName = (SELECT ReferencedColumnName FROM #FKs WHERE ID = @i)

        SET @DropStatementTemp = REPLACE(REPLACE(@DropStatement,'<tablename>',@TableName),'<constraintname>',@ConstraintName)
        SET @CreateStatementTemp = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@CreateStatement,'<tablename>',@TableName),'<column>',@ColumnName),'<constraintname>',@ConstraintName),'<reftable>',@ReferencedTableName),'<refcolumn>',@ReferencedColumnName)
        SET @TruncateStatementTemp = REPLACE(@TruncateStatement,'<tablename>',@TableName) 

        INSERT INTO [Internal_FK_Definition_Storage]
        SELECT @ConstraintName, @CreateStatementTemp, @DropStatementTemp, @TruncateStatementTemp
        
        SET @i = @i + 1
        
        IF @Verbose = 1
            PRINT '  > 已備份外鍵:[' + @ConstraintName + '] 所屬表: [' + @TableName + ']'
    END   
END   
ELSE 
    PRINT '2. 正在備份外鍵定義...'

IF @Verbose = 1
    PRINT '3. 正在刪除外鍵...'
BEGIN TRAN    
BEGIN TRY
SET @i = 1
WHILE (@i <= (SELECT MAX(ID) FROM [Internal_FK_Definition_Storage]))
BEGIN
    SET @ConstraintName = (SELECT FK_Name FROM [Internal_FK_Definition_Storage] WHERE ID = @i)
    SET @Statement = (SELECT FK_DestructionStatement FROM [Internal_FK_Definition_Storage] WITH (NOLOCK) WHERE ID = @i)
    IF @Debug = 1 
        PRINT @Statement
    ELSE
        EXEC(@Statement)
    SET @i = @i + 1
    IF @Verbose = 1
        PRINT '  > 已刪除外鍵:[' + @ConstraintName + ']'
END     

IF @Verbose = 1
    PRINT '4. 正在清理數據表...'
--先清除該外鍵所在表(由於外鍵所在表仍可能又被其他外鍵所引用，因此需要迴圈遞歸處理)(註：本處理未實現)
--請不要使用下麵註釋代碼
/*    
SET @i = 1
WHILE (@i <= (SELECT MAX(ID) FROM [Internal_FK_Definition_Storage]))
BEGIN
    SET @Statement = (SELECT Table_TruncationStatement FROM [Internal_FK_Definition_Storage] WHERE ID = @i)
    IF @Debug = 1 
        PRINT @Statement
    ELSE
        EXEC(@Statement)
    SET @i = @i + 1
    IF @Verbose = 1
        PRINT '  > ' + @Statement
END
*/

IF @Debug = 1 
    PRINT 'TRUNCATE TABLE [' + @TableToTruncate + ']'
ELSE
    EXEC('TRUNCATE TABLE [' + @TableToTruncate + ']')
IF @Verbose = 1
    PRINT '  > 已清理數據表[' + @TableToTruncate + ']'
    
IF @Verbose = 1
    PRINT '5. 正在重建外鍵...'
SET @i = 1
WHILE (@i <= (SELECT MAX(ID) FROM [Internal_FK_Definition_Storage]))
BEGIN
    SET @ConstraintName = (SELECT FK_Name FROM [Internal_FK_Definition_Storage] WHERE ID = @i)
    SET @Statement = (SELECT FK_CreationStatement FROM [Internal_FK_Definition_Storage] WHERE ID = @i)
    IF @Debug = 1 
        PRINT @Statement
    ELSE
        EXEC(@Statement)
    SET @i = @i + 1
    IF @Verbose = 1
    PRINT '  > 已重建外鍵:[' + @ConstraintName + ']'
END
    COMMIT
END TRY
BEGIN CATCH
    ROLLBACK 
    PRINT '出錯信息:'+ERROR_MESSAGE()
END CATCH
IF @Verbose = 1
    PRINT '6. 處理完成！'
END

 


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • mysql load數據第一列丟失
    mysql load數據第一列丟失 問題描述 MySQL表的結構如下： txt文件的格式如下，以tab作為劃分： 使用load導入數據，命令如下： 查詢數據發現，第一列的uuid都是phone_number，而phone_number這一列都是空的，也就是說uuid這一列全部丟失了 原因定位 找到了 ...
  • spark快速上手
    spark快速上手 前言 基於Spark 2.1版本 僅僅是快速上手，沒有深究細節 主要參考是官方文檔 代碼均為官方文檔中代碼，語言為Scala 進入spark shell 終端輸入 ，進入的是Scala環境的終端，也可以輸入 進入Python環境的終端 創建一個SparkSession 創建Dat ...
  • Spark中的IsNotNull函數怎麼用
    Spark中的IsNotNull函數怎麼用 在 "這裡" 看到的這個函數，就是判斷是否為空，但是開始不知道怎麼用，後來找到了，要在View中用，也就是SparkSQL中。如下： PS:開始谷歌了半天沒找到，結果百度了一番找到了答案… ...
  • 實驗測試之------創建DBlink，查詢遠端的yang用戶下的abcdedfa表，創建同義詞
    --測試環境，20遠端，30本地； --準備1,20遠端建立表的同義詞，用戶信息； 1.11 準備階段，有表有用戶可以忽略此步驟 SQL> conn yang/y ERROR: ORA-01017: invalid username/password; logon denied --無效的用戶名密碼 ...
  • mysql千萬級資料庫插入速度和讀取速度的調整
    mysql上百萬數據讀取和插入更新一般沒什麼問題，但上千萬後速度會很慢，如何調整配置，提高效率。如下： 1.儘量將數據一次性寫入DataFile和減少資料庫的checkpoint操作，調整如下參數： （1）將innodb_flush_log_at_trx_commit 配置為0；按過往經驗設定為0， ...
  • Sql Server 2008R2升級 Sql Server 2012 問題
    環境： Windows server 2008 r2 Standard +SqlServer2008R2 內網環境需要升級為SQL server 2012 升級安裝時提示版本不支持 網上查詢相關問題， 必須是SQL server2008 r2 sp1以上及需要安裝Sp2補丁包才能升級為SQL ser ...
  • 查詢資料庫中指定資料庫所有表中是否包含指定欄位
    今天遇到了一個問題 就是要批量更新資料庫中 某個欄位的值，需要首先需要找到都哪些表裡包含了這個欄位 ，整理了下麵這個語句 方便查詢 SELECT DISTINCT t.table_name, c.COLUMN_NAMEFROM information_schema.TABLES tINNER JOI ...
  • 解決mysql登錄警告問題
    一、前言 我們在登錄mysql的時候經常會看到一句警告： Warning: Using a password on the command line interface can be insecure. 這讓人看著很不舒服，並且當在寫腳本的時候這個警告輸出到屏幕上更讓人感覺難受。 二、解決辦法 這個 ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類