MS SQL 日常維護管理常用指令碼(下)

發表於2015-09-30

監控資料庫執行

下面是整理、收集監控資料庫執行的一些常用指令碼，也是MS SQL 日常維護管理常用指令碼(上)的續集，歡迎大家補充、提意見。

檢視資料庫登入名資訊

Code Snippet

SELECT name                                AS LoginName ,
       dbname                              AS DefaultDB ,
       createdate                          AS CreateDate,
       updatedate                          AS UpdateDate,
       language                            AS Language  ,
       CASE WHEN isntname = 1 THEN 'NT USER'
           ELSE 'SQL USER'    END          AS UserType
FROM syslogins;

SELECT name AS LoginName ,

dbname AS DefaultDB ,

createdate AS CreateDate,

updatedate AS UpdateDate,

language AS Language ,

CASE WHEN isntname = 1 THEN 'NT USER'

ELSE 'SQL USER' END AS UserType

FROM syslogins;

檢視資料庫使用者資訊

SELECT * FROM sysusers;

檢視使用者擁有的伺服器角色

方法1：用SSMS管理工具檢視
方法2：指令碼查詢

檢視使用者角色

SELECT name            ,
       CASE WHEN sysadmin     = 1       THEN 'yes'     ELSE '' END AS IsSysadmin        ,
       CASE WHEN dbcreator    = 1       THEN 'yes'     ELSE '' END AS IsDbCreate        ,
       CASE WHEN securityadmin= 1       THEN 'yes'     ELSE '' END AS IsSecurityadmin   ,
       CASE WHEN bulkadmin    = 1       THEN 'yes'     ELSE '' END AS IsBulkadmin       ,
       CASE WHEN diskadmin    = 1       THEN 'yes'     ELSE '' END AS IsDiskadmin       ,
       CASE WHEN processadmin = 1       THEN 'yes'     ELSE '' END AS IsProcessadmin    ,
       CASE WHEN serveradmin  = 1       THEN 'yes'     ELSE '' END AS IsServeradmin     ,
       CASE WHEN setupadmin   = 1       THEN 'yes'     ELSE '' END AS IsSetupadmin    
FROM syslogins
--WHERE NAME='loginname'

SELECT name ,

CASE WHEN sysadmin = 1 THEN 'yes' ELSE '' END AS IsSysadmin ,

CASE WHEN dbcreator = 1 THEN 'yes' ELSE '' END AS IsDbCreate ,

CASE WHEN securityadmin= 1 THEN 'yes' ELSE '' END AS IsSecurityadmin ,

CASE WHEN bulkadmin = 1 THEN 'yes' ELSE '' END AS IsBulkadmin ,

CASE WHEN diskadmin = 1 THEN 'yes' ELSE '' END AS IsDiskadmin ,

CASE WHEN processadmin = 1 THEN 'yes' ELSE '' END AS IsProcessadmin ,

CASE WHEN serveradmin = 1 THEN 'yes' ELSE '' END AS IsServeradmin ,

CASE WHEN setupadmin = 1 THEN 'yes' ELSE '' END AS IsSetupadmin

FROM syslogins

--WHERE NAME='loginname'

檢視最大工作執行緒數

Code Snippet

SELECT  max_workers_count
  FROM  sys.dm_os_sys_info

1 2	SELECT max_workers_count FROM sys.dm_os_sys_info

檢視當前使用者程式的會話ID

SELECT @@SPID

查詢當前會話使用哪種協議

Code Snippet

SELECT net_transport
 FROM sys.dm_exec_connections
 WHERE session_id = @@SPID;

SELECT net_transport

FROM sys.dm_exec_connections

WHERE session_id = @@SPID;

檢視當前連線的會話資訊

–程式號1–50是SQL Server系統內部用的

SELECT * FROM sys.dm_exec_sessions WHERE session_id >=51

–檢視某臺機器的連線會話資訊

SELECT * FROM sys.dm_exec_sessions WHERE session_id >=51 AND host_name=’PO130018801′

–檢視某個登入名的連線會話資訊

SELECT * FROM sys.dm_exec_sessions WHERE session_id >=51 AND login_name=’username’

–檢視活動的連線會話資訊

SELECT * FROM sys.dm_exec_sessions WITH(NOWAIT) WHERE session_id >=51 AND status =’running’

–查詢連線到伺服器的使用者並返回每個使用者的會話數

SELECT login_name ,
COUNT(session_id) AS session_count
FROM sys.dm_exec_sessions
GROUP BY login_name ;

檢視正在執行的SQL語句

方法1：選擇資料庫例項，單擊右鍵，選擇”活動監視器“，監控/檢視正在執行的SQL
方法2： profile去跟蹤，比較耗費資源。

方法3：
Code Snippet

SELECT[Spid] = session_Id ,
      ecid ,
      [Database] = DB_NAME(sp.dbid) ,
      [User] = nt_username ,
      [Status] = er.status ,
      [Wait] = wait_type ,
      [Individual Query] = SUBSTRING(qt.text, er.statement_start_offset / 2,
                                     ( CASE WHEN er.statement_end_offset = -1
                                            THEN LEN(CONVERT(NVARCHAR(MAX), qt.text))
                                                 * 2
                                            ELSE er.statement_end_offset
                                       END - er.statement_start_offset ) / 2) ,
      [Parent Query] = qt.text ,
      Program = program_name ,
      Hostname ,
      nt_domain ,
      start_time
  FROMsys.dm_exec_requests er
      INNER JOIN sys.sysprocesses sp ON er.session_id = sp.spid
      CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) AS qt
 WHEREsession_Id >= 51

SELECT[Spid] = session_Id ,

ecid ,

[Database] = DB_NAME(sp.dbid) ,

[User] = nt_username ,

[Status] = er.status ,

[Wait] = wait_type ,

[Individual Query] = SUBSTRING(qt.text, er.statement_start_offset / 2,

( CASE WHEN er.statement_end_offset = -1

THEN LEN(CONVERT(NVARCHAR(MAX), qt.text))

* 2

ELSE er.statement_end_offset

END - er.statement_start_offset ) / 2) ,

[Parent Query] = qt.text ,

Program = program_name ,

Hostname ,

nt_domain ,

start_time

FROMsys.dm_exec_requests er

INNER JOIN sys.sysprocesses sp ON er.session_id = sp.spid

CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) AS qt

WHEREsession_Id >= 51

方法4：

Code Snippet

SELECT   m.session_id ,
         m.start_time ,
         m.command    ,
         m.wait_type  ,
         m.cpu_time   ,
        CAST(s.text AS VARCHAR(1000)) AS sqlText
 FROMmaster.sys.dm_exec_requests m WITH ( NOLOCK )
     CROSS APPLY fn_get_sql(m.sql_handle) s
 
SELECT  r.session_id,
        r.start_time      ,
        r.command         ,
        r.wait_type       ,
        r.cpu_time        ,
        s.text  
 FROMsys.dm_exec_requests r
     CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) s

SELECT m.session_id ,

m.start_time ,

m.command ,

m.wait_type ,

m.cpu_time ,

CAST(s.text AS VARCHAR(1000)) AS sqlText

FROMmaster.sys.dm_exec_requests m WITH ( NOLOCK )

CROSS APPLY fn_get_sql(m.sql_handle) s

SELECT r.session_id,

r.start_time ,

r.command ,

r.wait_type ,

r.cpu_time ,

s.text

FROMsys.dm_exec_requests r

CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) s

–檢視某個會話ID正在執行的SQL

Code Snippet

SELECT  m.session_id ,
        m.start_time ,
        m.command    ,
        m.wait_type  ,
        m.cpu_time   ,
      CAST(s.text AS VARCHAR(1000)) AS sqlText
  FROMmaster.sys.dm_exec_requests m WITH ( NOLOCK )
      CROSS APPLY fn_get_sql(m.sql_handle) s
 WHEREm.session_id = 342
 
    SELECT   r.session_id      ,
             r.start_time      ,
             r.command         ,
             r.wait_type       ,
             r.cpu_time        ,
             s.text  
  FROM sys.dm_exec_requests r
      CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) s
WHERE r.seesion_id =342

SELECT m.session_id ,

m.start_time ,

m.command ,

m.wait_type ,

m.cpu_time ,

CAST(s.text AS VARCHAR(1000)) AS sqlText

FROMmaster.sys.dm_exec_requests m WITH ( NOLOCK )

CROSS APPLY fn_get_sql(m.sql_handle) s

WHEREm.session_id = 342

SELECT r.session_id ,

r.start_time ,

r.command ,

r.wait_type ,

r.cpu_time ,

s.text

FROM sys.dm_exec_requests r

CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) s

WHERE r.seesion_id =342

檢視SQL SERVER程式執行的語句

Code Snippet

USE master
 
DECLARE @spid INT ;
 
DECLARE @sql_handle BINARY(20) ;
 
SET @spid = 56
 
  SELECT@sql_handle = sql_handle
    FROMsysprocesses AS A WITH ( NOLOCK )
   WHEREspid = @spid ;
 
  SELECTtext
    FROM::fn_get_sql(@sql_handle) ;

USE master

DECLARE @spid INT ;

DECLARE @sql_handle BINARY(20) ;

SET @spid = 56

SELECT@sql_handle = sql_handle

FROMsysprocesses AS A WITH ( NOLOCK )

WHEREspid = @spid ;

SELECTtext

FROM::fn_get_sql(@sql_handle) ;

查詢TOP N語句

按平均 CPU 時間返回排名前十個的查詢的相關資訊。此示例將根據查詢的查詢雜湊對查詢進行聚合，以便按照查詢的累積資源消耗來分組在邏輯上等效的查詢。
–注意：SQL 2005 某些版本，沒有sys.dm_exec_query_stats系統動態檢視沒有query_hash檢視。

Code Snippet

USE DBNAME;
GO
SELECT TOP 10 query_stats.query_hash             AS "Query Hash",
    SUM(query_stats.total_worker_time) /
    SUM(query_stats.execution_count)             AS "Avg CPU Time",
    MIN(query_stats.statement_text)              AS "Statement Text"
FROM
    (SELECT QS.*,
    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) AS statement_text
     FROM sys.dm_exec_query_stats AS QS
     CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) as ST) as query_stats
GROUP BY query_stats.query_hash
ORDER BY 2 DESC;
GO

USE DBNAME;

SELECT TOP 10 query_stats.query_hash AS "Query Hash",

SUM(query_stats.total_worker_time) /

SUM(query_stats.execution_count) AS "Avg CPU Time",

MIN(query_stats.statement_text) AS "Statement Text"

FROM

(SELECT QS.*,

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) AS statement_text

FROM sys.dm_exec_query_stats AS QS

CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) as ST) as query_stats

GROUP BY query_stats.query_hash

ORDER BY 2 DESC;

檢視會話阻塞/死鎖資訊

方法1：檢視那個引起阻塞，檢視blk不為0的記錄，如果存在阻塞程式，則是該阻塞程式的會話 ID。否則該列為零。

EXEC sp_who active

方法2：檢視那個引起阻塞，檢視欄位BlkBy，這個能夠得到比sp_who更多的資訊。

EXEC sp_who2 active

方法3：sp_lock 系統儲存過程，報告有關鎖的資訊，但是不方便定位問題
方法4：sp_who_lock儲存過程

Code Snippet

USE master;
GO
 
SET ANSI_NULLS ON;
GO
 
SET QUOTED_IDENTIFIER ON;
GO
 
IF EXISTS(SELECT 1 FROM sysobjects WHERE id=OBJECT_ID(N'sp_who_lock')
                                       AND OBJECTPROPERTY(id, 'IsProcedure') =1)
    DROP PROCEDURE sp_who_lock;
GO
 
--==================================================================================================
--            ProcedureName             :            sp_who_lock
--            Author                    :            作者不詳,出自網路
--            CreateDate                :            2013-05-13
--            Description               :            檢視阻塞和死鎖資訊
/**************************************************************************************************
        Parameters                    :              引數說明
***************************************************************************************************
                                                     無參儲存過程
***************************************************************************************************
        Modified Date            Modified User     Version                 Modified Reason
***************************************************************************************************
       2013-06-03                    Kerry        V01.00.01 調整儲存過程格式,程式碼部分修改以及增加註釋資訊
***************************************************************************************************/
--=================================================================================================
 
  CREATE PROCEDURE sp_who_lock
    AS
BEGIN
 
DECLARE @spid                            INT;
DECLARE @block                           INT;
DECLARE @RowCount                        INT;
DECLARE @RowIndex                        INT;
 
--建立臨時表，保持被阻塞或正阻塞其他SQL的SQL語句資訊
CREATE TABLE #tmp_lock_who
(
      id    INT IDENTITY(1, 1) ,
      spid    SMALLINT ,
      block SMALLINT
)
    
    
IF @@ERROR<>0 RETURN @@ERROR;
 
INSERT INTO #tmp_lock_who
        (
          spid ,
          block
        )
        SELECT  0 ,
                blocked
        FROM    ( SELECT    *
                  FROM      sysprocesses
                  WHERE     blocked > 0
                ) a
        WHERE   NOT EXISTS( SELECT *
                             FROM   ( SELECT    *
                                      FROM      sysprocesses
                                      WHERE     blocked > 0
                                    ) b
                             WHERE  a.blocked = spid )
        UNION
        SELECT  spid ,
                blocked
        FROM    sysprocesses
        WHERE   blocked > 0;
        
        
IF @@ERROR<>0 RETURN @@ERROR;
 
 
-- 找到臨時表的記錄數
  SELECT@RowCount = COUNT(1) ,
        @RowIndex = 1
    FROM#tmp_lock_who
 
 
IF @@ERROR<>0 RETURN @@ERROR;
 
  IF@RowCount=0
  SELECT  N'現在沒有阻塞和死鎖資訊' AS MESSAGE;
 
 
-- -- 迴圈開始
WHILE @RowIndex <= @RowCount
BEGIN
    -- 取第一條記錄
    SELECT     @spid    = spid,
               @block   = block
    FROM #tmp_lock_who
    WHERE Id = @RowIndex
 
    IF @spid = 0
        SELECT N'引起資料庫死鎖的是: ' + CAST(@block AS VARCHAR(10))
            +  N'程式號,其執行的SQL語法如下';
    ELSE
        SELECT N'程式號SPID：' + CAST(@spid AS VARCHAR(10)) + N'被程式號SPID：'
            +  CAST(@block AS VARCHAR(10)) +'阻塞,其當前程式執行的SQL語法如下';
        
    DBCC INPUTBUFFER(@block )
 
 
    SET @RowIndex = @RowIndex + 1;
 
END;
 
    DROP TABLE #tmp_lock_who;
 
    RETURN 0;
END

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

USE master;

SET ANSI_NULLS ON;

SET QUOTED_IDENTIFIER ON;

IF EXISTS(SELECT 1 FROM sysobjects WHERE id=OBJECT_ID(N'sp_who_lock')

AND OBJECTPROPERTY(id, 'IsProcedure') =1)

DROP PROCEDURE sp_who_lock;

--==================================================================================================

-- ProcedureName : sp_who_lock

-- Author : 作者不詳,出自網路

-- CreateDate : 2013-05-13

-- Description : 檢視阻塞和死鎖資訊

/**************************************************************************************************

Parameters : 引數說明

***************************************************************************************************

無參儲存過程

***************************************************************************************************

Modified Date Modified User Version Modified Reason

***************************************************************************************************

2013-06-03 Kerry V01.00.01 調整儲存過程格式,程式碼部分修改以及增加註釋資訊

***************************************************************************************************/

--=================================================================================================

CREATE PROCEDURE sp_who_lock

BEGIN

DECLARE @spid INT;

DECLARE @block INT;

DECLARE @RowCount INT;

DECLARE @RowIndex INT;

--建立臨時表，保持被阻塞或正阻塞其他SQL的SQL語句資訊

CREATE TABLE #tmp_lock_who

(

id INT IDENTITY(1, 1) ,

spid SMALLINT ,

block SMALLINT

)

IF @@ERROR<>0 RETURN @@ERROR;

INSERT INTO #tmp_lock_who

(

spid ,

block

)

SELECT 0 ,

blocked

FROM ( SELECT *

FROM sysprocesses

WHERE blocked > 0

) a

WHERE NOT EXISTS( SELECT *

FROM ( SELECT *

FROM sysprocesses

WHERE blocked > 0

) b

WHERE a.blocked = spid )

UNION

SELECT spid ,

blocked

FROM sysprocesses

WHERE blocked > 0;

IF @@ERROR<>0 RETURN @@ERROR;

-- 找到臨時表的記錄數

SELECT@RowCount = COUNT(1) ,

@RowIndex = 1

FROM#tmp_lock_who

IF @@ERROR<>0 RETURN @@ERROR;

IF@RowCount=0

SELECT N'現在沒有阻塞和死鎖資訊' AS MESSAGE;

-- -- 迴圈開始

WHILE @RowIndex <= @RowCount

BEGIN

-- 取第一條記錄

SELECT @spid = spid,

@block = block

FROM #tmp_lock_who

WHERE Id = @RowIndex

IF @spid = 0

SELECT N'引起資料庫死鎖的是: ' + CAST(@block AS VARCHAR(10))

+ N'程式號,其執行的SQL語法如下';

ELSE

SELECT N'程式號SPID：' + CAST(@spid AS VARCHAR(10)) + N'被程式號SPID：'

+ CAST(@block AS VARCHAR(10)) +'阻塞,其當前程式執行的SQL語法如下';

DBCC INPUTBUFFER(@block )

SET @RowIndex = @RowIndex + 1;

END;

DROP TABLE #tmp_lock_who;

RETURN 0;

END

方法5：右鍵伺服器-選擇“活動和監視器”，檢視程式選項。注意“任務狀態”欄位。

方法6：右鍵服務名稱-選擇報表-標準報表-活動-所有正在阻塞的事務。

小結：總結之後，才發現居然有這麼多方法，MGD，系統的整理、梳理知識點是非常有必要的，你能更全面、深入的瞭解。

檢視記憶體狀態

dbcc memorystatus
具體如何分析，請檢視官方文件http://support.microsoft.com/kb/907877/zh-cn

檢視指令碼執行時間

方法1：檢視SSMS管理器，查詢視窗右下角
方法2:

Code Snippet

DECLARE @exectime DATETIME
  SELECT@exectime = GETDATE()
--SQL 語句
PRINT N'SQL執行耗時:' + CONVERT(VARCHAR(10), DATEDIFF(ms, @exectime, GETDATE()))

DECLARE @exectime DATETIME

SELECT@exectime = GETDATE()

--SQL 語句

PRINT N'SQL執行耗時:' + CONVERT(VARCHAR(10), DATEDIFF(ms, @exectime, GETDATE()))

方法3：

SET STATISTICS TIME ON

–SQL 語句

檢視程式正在執行的SQL語句

dbcc inputbuffer ()

檢視那些表缺少索引

下面語句功能強大，執行結果受統計資訊的影響

Code Snippet

SELECT sys.objects.name table_name,
       mid.statement full_name,
    (migs.avg_total_user_cost * migs.avg_user_impact) *(migs.user_seeks + migs.user_scans) AS Impact,
    migs.avg_user_impact *(migs.user_seeks + migs.user_scans) Avg_Estimated_Impact,
    'CREATE NONCLUSTERED INDEX IDX_' + sys.objects.name + '_N ON '
        + sys.objects.name COLLATE DATABASE_DEFAULT
        + ' ( ' + IsNull(mid.equality_columns, '')
        + CASE WHEN mid.inequality_columns IS NULL
                THEN ''
            ELSE
                CASE WHEN mid.equality_columns IS NULL
                    THEN ''
                ELSE ','
                END + mid.inequality_columns
            END + ' ) '
        + CASE WHEN mid.included_columns IS NULL
                THEN ''
            ELSE 'INCLUDE (' + mid.included_columns + ')' END
        + ';' AS CreateIndexStatement,
    mid.equality_columns,
    mid.inequality_columns,
    mid.included_columns
FROM sys.dm_db_missing_index_group_stats AS migs
    INNER JOIN sys.dm_db_missing_index_groups AS mig ON migs.group_handle = mig.index_group_handle
    INNER JOIN sys.dm_db_missing_index_details AS mid ON mig.index_handle = mid.index_handle
        AND mid.database_id = DB_ID()
    INNER JOIN sys.objects WITH (nolock) ON mid.OBJECT_ID = sys.objects.OBJECT_ID
WHERE (migs.group_handle IN
        (
            SELECT TOP (500) group_handle
            FROM sys.dm_db_missing_index_group_stats WITH (nolock)
            ORDER BY (avg_total_user_cost * avg_user_impact) *(user_seeks + user_scans) DESC))
    AND OBJECTPROPERTY(sys.objects.OBJECT_ID, 'isusertable')=1
--ORDER BY [Impact] DESC, [full_name] DESC
ORDER BY [table_name], [Impact] desc

SELECT sys.objects.name table_name,

mid.statement full_name,

(migs.avg_total_user_cost * migs.avg_user_impact) *(migs.user_seeks + migs.user_scans) AS Impact,

migs.avg_user_impact *(migs.user_seeks + migs.user_scans) Avg_Estimated_Impact,

'CREATE NONCLUSTERED INDEX IDX_' + sys.objects.name + '_N ON '

+ sys.objects.name COLLATE DATABASE_DEFAULT

+ ' ( ' + IsNull(mid.equality_columns, '')

+ CASE WHEN mid.inequality_columns IS NULL

THEN ''

ELSE

CASE WHEN mid.equality_columns IS NULL

THEN ''

ELSE ','

END + mid.inequality_columns

END + ' ) '

+ CASE WHEN mid.included_columns IS NULL

THEN ''

ELSE 'INCLUDE (' + mid.included_columns + ')' END

+ ';' AS CreateIndexStatement,

mid.equality_columns,

mid.inequality_columns,

mid.included_columns

FROM sys.dm_db_missing_index_group_stats AS migs

INNER JOIN sys.dm_db_missing_index_groups AS mig ON migs.group_handle = mig.index_group_handle

INNER JOIN sys.dm_db_missing_index_details AS mid ON mig.index_handle = mid.index_handle

AND mid.database_id = DB_ID()

INNER JOIN sys.objects WITH (nolock) ON mid.OBJECT_ID = sys.objects.OBJECT_ID

WHERE (migs.group_handle IN

(

SELECT TOP (500) group_handle

FROM sys.dm_db_missing_index_group_stats WITH (nolock)

ORDER BY (avg_total_user_cost * avg_user_impact) *(user_seeks + user_scans) DESC))

AND OBJECTPROPERTY(sys.objects.OBJECT_ID, 'isusertable')=1

--ORDER BY [Impact] DESC, [full_name] DESC

ORDER BY [table_name], [Impact] desc

檢視應該被移除的索引

檢視那些多餘的、應該被移除的索引

SQL 1：

Code Snippet

SELECT OBJECTNAME = OBJECT_NAME(I.OBJECT_ID),
    INDEXNAME = I.NAME,
    I.INDEX_ID
FROM SYS.INDEXES I
    JOIN SYS.OBJECTS O ON I.OBJECT_ID = O.OBJECT_ID
WHERE OBJECTPROPERTY(O.OBJECT_ID,'IsUserTable') = 1
    AND I.INDEX_ID NOT IN(
                            SELECT S.INDEX_ID
                            FROM SYS.DM_DB_INDEX_USAGE_STATS S
                            WHERE S.OBJECT_ID = I.OBJECT_ID
                                AND I.INDEX_ID = S.INDEX_ID
                                AND DATABASE_ID = DB_ID())
ORDER BY OBJECTNAME, I.INDEX_ID, INDEXNAME ASC

SELECT OBJECTNAME = OBJECT_NAME(I.OBJECT_ID),

INDEXNAME = I.NAME,

I.INDEX_ID

FROM SYS.INDEXES I

JOIN SYS.OBJECTS O ON I.OBJECT_ID = O.OBJECT_ID

WHERE OBJECTPROPERTY(O.OBJECT_ID,'IsUserTable') = 1

AND I.INDEX_ID NOT IN(

SELECT S.INDEX_ID

FROM SYS.DM_DB_INDEX_USAGE_STATS S

WHERE S.OBJECT_ID = I.OBJECT_ID

AND I.INDEX_ID = S.INDEX_ID

AND DATABASE_ID = DB_ID())

ORDER BY OBJECTNAME, I.INDEX_ID, INDEXNAME ASC

SQL 2: 分析下面語句，移除那些沒有必要的索引

Code Snippet

SELECT DB_NAME(database_id)         AS  N'DataBaseName'  ,
       OBJECT_NAME(U.object_id)     AS  N'Table_Name'    ,
       I.name                       AS  N'Index_Name'    ,
       user_seeks                   AS  N'使用者索引查詢次數',
       user_scans                   AS  N'使用者索引掃描次數',
       last_user_seek               AS  N'最後查詢時間'   ,
       last_user_scan               AS  N'最後掃描時間'   ,
       rows                         AS  N'表中的行數'
FROM sys.dm_db_index_usage_stats AS U
   INNER JOIN sys.indexes I ON U.index_id= I.index_idAND U.object_id= I.object_id
  INNER JOIN sysindexesT ON I.object_id = T.id
WHERE database_id= DB_ID('DbName')
AND OBJECT_NAME(U.object_id)='TableName'
ORDER BY user_seeks, user_scans, object_name(U.object_id);

SELECT DB_NAME(database_id) AS N'DataBaseName' ,

OBJECT_NAME(U.object_id) AS N'Table_Name' ,

I.name AS N'Index_Name' ,

user_seeks AS N'使用者索引查詢次數',

user_scans AS N'使用者索引掃描次數',

last_user_seek AS N'最後查詢時間' ,

last_user_scan AS N'最後掃描時間' ,

rows AS N'表中的行數'

FROM sys.dm_db_index_usage_stats AS U

INNER JOIN sys.indexes I ON U.index_id= I.index_idAND U.object_id= I.object_id

INNER JOIN sysindexesT ON I.object_id = T.id

WHERE database_id= DB_ID('DbName')

AND OBJECT_NAME(U.object_id)='TableName'

ORDER BY user_seeks, user_scans, object_name(U.object_id);

MS SQL 日常維護管理常用指令碼(上)
2015-09-29
SQL指令碼
DBA日常維護SQL指令碼
2016-12-13
SQL指令碼
ORACLE DG 日常維護常用SQL
2016-08-31
OracleSQL
DBA日常維護SQL指令碼_自己編寫的
2018-05-11
SQL指令碼
史上最全近百條Oracle DBA日常維護SQL指令碼指令
2019-08-08
OracleSQL指令碼
DBA日常維護SQL整理
2015-05-28
SQL
nginx日常維護常用命令
2017-11-27
Nginx
EBS DBA日常維護使用的sql
2018-02-07
SQL
dba 常用維護sql
2016-05-24
SQL
oracle 維護常用SQL
2009-11-24
OracleSQL
一些常用的SQL(05版以上)資料庫維護指令碼
2015-01-12
SQL資料庫指令碼
Mysql常用的維護 SQL
2021-04-07
MySql
你必須知道的python運維常用指令碼!(日常更新)
2018-12-24
Python運維指令碼
Oracle維護常用SQL語句
2012-04-13
OracleSQL
自動儲存管理ASM日常維護（一）
2010-11-29
ASM
Oracle RAC 日常維護
2010-11-24
Oracle
RAC日常維護命令
2009-11-03
PG日常維護（一）
2024-04-14
ORACLE DBA常用SQL指令碼工具->管理篇(zt)
2005-01-25
OracleSQL指令碼
【PDB】Oracle pdb維護常用sql命令
2021-09-03
OracleSQL
轉:維護常用SQL語句收集！
2008-02-28
SQL
oracle維護管理指令（不斷更新）
2017-04-17
Oracle
自己總結的ORACLE日常運維常用的SQL
2016-09-22
Oracle運維SQL
ORACLE DBA常用SQL指令碼工具->管理篇(1) (轉)
2007-11-10
OracleSQL指令碼
Oracle資料庫維護常用的SQL程式碼示例(zt)
2012-07-01
Oracle資料庫SQL
Linux下MySQL安裝和日常維護(1)
2012-08-30
LinuxMySql
Linux下MySQL安裝和日常維護(2)
2012-08-30
LinuxMySql
Linux下MySQL安裝和日常維護(3)
2012-08-30
LinuxMySql
Oracle OGG日常維護
2022-12-02
Oracle
Gerrit日常維護記錄
2016-07-15
【MHA】mha日常維護命令
2016-07-29
DataGuard 日常維護命令整理
2012-07-16
針對oracle日常維護
2010-08-19
Oracle
轉貼：Oracle維護常用SQL語句
2005-02-17
OracleSQL
Informix Online資料庫日常管理及維護
2006-05-13
ORM資料庫
ORACLE常用SQL指令碼2
2007-03-14
OracleSQL指令碼
日常常用SQL命令
2010-08-20
SQL
rac叢集日常維護命令
2024-02-06

MS SQL 日常維護管理常用指令碼(下)

相關文章