SQL Server 运维常用sql语句（三）

查询返回当前配置的内存值和当前使用的值的相关信息

1. SELECT [name], [value], [value_in_use]
   
2. FROM sys.configurations
   
3. WHERE [name] = 'max server memory (MB)' OR [name] = 'min server memory (MB)';
   

修改内存的大小

1. sp_configure 'show advanced options', 1;
   
2. RECONFIGURE;
   
3. sp_configure 'max server memory (MB)', 4096; -- 设置最大内存限制为4GB
   
4. RECONFIGURE;
   

启用对表的压缩

1. EXEC sp_estimate_data_compression_savings 'Production', 'TransactionHistory', NULL, NULL, 'ROW';
   
2. 
   
3. ALTER TABLE Production.TransactionHistory REBUILD PARTITION = ALL
   
4. WITH (DATA_COMPRESSION = ROW);
   
5. GO
   

启用索引压缩

1. SELECT name, index_id
   
2. FROM sys.indexes
   
3. WHERE OBJECT_NAME (object_id) = N'TransactionHistory';
   
4. 
   
5. EXEC sp_estimate_data_compression_savings
   
6.    @schema_name = 'Production',
   
7.    @object_name = 'TransactionHistory',
   
8.    @index_id = 2,
   
9.    @partition_number = NULL,
   
10.    @data_compression = 'PAGE';
    
11. 
    
12. ALTER INDEX IX_TransactionHistory_ProductID ON Production.TransactionHistory REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE);
    
13. GO
    

读取错误日志

1. EXEC sp_readerrorlog 0, 1, 'database', 'start'
   

限制错误日志大小

1. USE [master];
   
2. GO
   
3. 
   
4. EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE',
   
5.    N'Software\Microsoft\MSSQLServer\MSSQLServer',
   
6.    N'ErrorLogSizeInKb', REG_DWORD, 5120;
   
7. GO
   

CHECKPOINT检查点

1. CHECKPOINT [ checkpoint_duration ]
   

查询是否有死锁

1. WITH    CTE_SID ( BSID, SID, sql_handle )        
   
2.  AS ( SELECT   blocking_session_id ,
   
3.                        session_id ,
   
4.                        sql_handle
   
5.               FROM     sys.dm_exec_requests
   
6.               WHERE    blocking_session_id <> 0
   
7.               UNION ALL
   
8.               SELECT   A.blocking_session_id ,
   
9.                        A.session_id ,
   
10.                        A.sql_handle
    
11.               FROM     sys.dm_exec_requests A
    
12.                       JOIN CTE_SID B ON A.SESSION_ID = B.BSID
    
13.             )
    
14.    SELECT  C.BSID ,  C.SID , S.login_name , S.host_name , S.status ,S.cpu_time ,
    
15.            S.memory_usage ,S.last_request_start_time , S.last_request_end_time ,S.logical_reads ,
    
16.            S.row_count ,
    
17.            q.text
    
18.    FROM    CTE_SID C
    
19.            JOIN sys.dm_exec_sessions S ON C.sid = s.session_id
    
20.            CROSS APPLY sys.dm_exec_sql_text(C.sql_handle) Q
    
21. ORDER BY sid
    

查看阻塞超时设置

1. SELECT @@LOCK_TIMEOUT
   

查看数据库的负载

1. SELECT substring (a.name,0,20) as [数据库名],
   
2. [连接数] = (SELECT COUNT(*) FROM master..sysprocesses b WHERE a.dbid = b.dbid), [阻塞进程] = (SELECT COUNT(*)
   
3. FROM master..sysprocesses b
   
4. WHERE a.dbid = b.dbid AND blocked <> 0),
   
5. [总内存] = ISNULL((SELECT SUM(memusage) FROM master..sysprocesses b WHERE a.dbid = b.dbid),0),
   
6. [总IO] = ISNULL((SELECT SUM(physical_io) FROM master..sysprocesses b WHERE a.dbid = b.dbid),0),
   
7. [总CPU] = ISNULL((SELECT SUM(cpu) FROM master..sysprocesses b
   
8. WHERE a.dbid = b.dbid),0), [总等待时间] = ISNULL((SELECT SUM(waittime)
   
9. FROM master..sysprocesses b
   
10. WHERE a.dbid = b.dbid),0) FROM master.dbo.sysdatabases a WITH (nolock)
    
11. WHERE  DatabasePropertyEx(a.name,'Status') = 'ONLINE'
    
12. ORDER BY [数据库名]
    

整理索引碎片

1. 第一步：查询表的索引碎片情况
   
2. DBCC SHOWCONTIG WITH ALL_INDEXES；
   
3. 第二步：删除并重建索引
   
4. 第三步：使用DROP_EXISTING子句重建索引
   
5. 第四步：执行DBCC DBREINDEX
   
6. 第五步：执行DBCC INDEXDEFRAG
   
7. 
   
8. Select 'DBCC INDEXDEFRAG ('+DB_Name()+','+Object_Name(ID)+','+Cast(INDID As Varchar)+')'+Char(10)
   
9. From SysIndexes
   
10. Where ID Not IN (Select ID From SYSObjects Where xType='S')
    

查询索引

1. SELECT CASE  WHEN t.[type] = 'U' THEN'表'
   
2.             WHEN t.[type] = 'V' THEN '视图'  END AS '类型',
   
3.       SCHEMA_NAME(t.schema_id) + '.' + t.[name] AS '(表/视图)名称',
   
4.       i.[name] AS 索引名称,
   
5.       SUBSTRING(column_names, 1, LEN(column_names) - 1) AS '列名',
   
6.       CASE WHEN i.[type] = 1 THEN '聚集索引'
   
7.            WHEN i.[type] = 2 THEN  '非聚集索引'
   
8.            WHEN i.[type] = 3 THEN  'XML索引'
   
9.           WHEN i.[type] = 4 THEN '空间索引'
   
10.           WHEN i.[type] = 5 THEN '聚簇列存储索引'
    
11.           WHEN i.[type] = 6 THEN '非聚集列存储索引'
    
12.       WHEN i.[type] = 7 THEN'非聚集哈希索引'
    
13.           END AS '索引类型',
    
14.      CASE  WHEN i.is_unique = 1 THEN'唯一' ELSE '不唯一' END AS '索引是否唯一'
    
15. FROM sys.objects t
    
16.    INNER JOIN sys.indexes i
    
17.        ON t.object_id = i.object_id
    
18.    CROSS APPLY
    
19. (
    
20.    SELECT col.[name] + ', '
    
21.    FROM sys.index_columns ic
    
22.        INNER JOIN sys.columns col
    
23.           ON ic.object_id = col.object_id
    
24.               AND ic.column_id = col.column_id
    
25.    WHERE ic.object_id = t.object_id
    
26.          AND ic.index_id = i.index_id
    
27.    ORDER BY col.column_id
    
28.    FOR XML PATH('')
    
29. ) D(column_names)
    
30. WHERE t.is_ms_shipped <> 1
    
31.      AND index_id > 0
    
32. ORDER BY i.[name];
    

整理索引

1. select 'drop index ' + index_name + ' on ' + tab_name + ';' ,a.tab_name, a.index_Type,a.index_colum FROM (
   
2. SELECT CASE  WHEN t.[type] = 'U' THEN'表'
   
3.             WHEN t.[type] = 'V' THEN '视图'  END AS '类型',
   
4.       SCHEMA_NAME(t.schema_id) + '.' + t.[name] AS 'tab_name',
   
5.       i.[name] AS index_name,
   
6.       SUBSTRING(column_names, 1, LEN(column_names) - 1) AS 'index_colum',
   
7.       CASE WHEN i.[type] = 1 THEN '聚集索引'
   
8.            WHEN i.[type] = 2 THEN  '非聚集索引'
   
9.            WHEN i.[type] = 3 THEN  'XML索引'
   
10.           WHEN i.[type] = 4 THEN '空间索引'
    
11.           WHEN i.[type] = 5 THEN '聚簇列存储索引'
    
12.           WHEN i.[type] = 6 THEN '非聚集列存储索引'
    
13.       WHEN i.[type] = 7 THEN'非聚集哈希索引'
    
14.           END AS index_Type,
    
15.      CASE  WHEN i.is_unique = 1 THEN'唯一' ELSE '不唯一' END AS 'index_only'
    
16. FROM sys.objects t
    
17.    INNER JOIN sys.indexes i
    
18.        ON t.object_id = i.object_id
    
19.    CROSS APPLY
    
20. (
    
21.    SELECT col.[name] + ', '
    
22.    FROM sys.index_columns ic
    
23.        INNER JOIN sys.columns col
    
24.           ON ic.object_id = col.object_id
    
25.               AND ic.column_id = col.column_id
    
26.    WHERE ic.object_id = t.object_id
    
27.          AND ic.index_id = i.index_id
    
28.    ORDER BY col.column_id
    
29.    FOR XML PATH('')
    
30. ) D(column_names)
    
31. WHERE t.is_ms_shipped <> 1
    
32.      AND index_id > 0
    
33. ) a
    
34. where a.index_TYpe = '非聚集索引' and a.index_only = '不唯一';
    
35. 
    
36. select 'create index ' + 'idx_' + tab_name + '_' + a.index_colum + ' on ' + tab_name + '('  + a.index_colum + ');',
    
37.       a.tab_name, a.index_Type,a.index_colum FROM (
    
38. SELECT CASE  WHEN t.[type] = 'U' THEN'表'
    
39.             WHEN t.[type] = 'V' THEN '视图'  END AS '类型',
    
40.       SCHEMA_NAME(t.schema_id) + '.' + t.[name] AS 'tab_name',
    
41.       i.[name] AS index_name,
    
42.       SUBSTRING(column_names, 1, LEN(column_names) - 1) AS 'index_colum',
    
43.       CASE WHEN i.[type] = 1 THEN '聚集索引'
    
44.            WHEN i.[type] = 2 THEN  '非聚集索引'
    
45.            WHEN i.[type] = 3 THEN  'XML索引'
    
46.           WHEN i.[type] = 4 THEN '空间索引'
    
47.           WHEN i.[type] = 5 THEN '聚簇列存储索引'
    
48.           WHEN i.[type] = 6 THEN '非聚集列存储索引'
    
49.       WHEN i.[type] = 7 THEN'非聚集哈希索引'
    
50.           END AS index_Type,
    
51.      CASE  WHEN i.is_unique = 1 THEN'唯一' ELSE '不唯一' END AS 'index_only'
    
52. FROM sys.objects t
    
53.    INNER JOIN sys.indexes i
    
54.        ON t.object_id = i.object_id
    
55.    CROSS APPLY
    
56. (
    
57.    SELECT col.[name] + ', '
    
58.    FROM sys.index_columns ic
    
59.        INNER JOIN sys.columns col
    
60.           ON ic.object_id = col.object_id
    
61.               AND ic.column_id = col.column_id
    
62.    WHERE ic.object_id = t.object_id
    
63.          AND ic.index_id = i.index_id
    
64.    ORDER BY col.column_id
    
65.    FOR XML PATH('')
    
66. ) D(column_names)
    
67. WHERE t.is_ms_shipped <> 1
    
68.      AND index_id > 0
    
69. ) a
    
70. where a.index_TYpe = '非聚集索引' and index_only = '不唯一'
    

查看哪些表占用了比较大的磁盘空间

1. select o.name, SUM(p.reserved_page_count) as reserved_page_count,
   
2.               SUM(p.used_page_count) as used_page_count,
   
3.              SUM( case when(p.index_id<2) then (p.in_row_data_page_count+ p.lob_used_page_count+p.row_overflow_used_page_count) else p.lob_used_page_count+p.row_overflow_used_page_count end ) as DataPages,
   
4.              SUM( case when (p.index_id<2) then row_count else 0 end ) as rowCounts
   
5. from sys.dm_db_partition_stats p
   
6. inner join sys.objects o on p.object_id=o.object_id group by o.name order by rowCounts desc
   

查看表的占用情况

1. SELECT
   
2. name '表名',
   
3. convert (char(11), row_Count) as '数据条数',
   
4. (reservedpages * 8) '已用空间(KB)',
   
5. (pages * 8) '数据占用空间(KB)',
   
6. (CASE WHEN usedpages > pages THEN (usedpages - pages) ELSE 0 END) * 8 '索引占用空间(KB)',
   
7. (CASE WHEN reservedpages > usedpages THEN (reservedpages - usedpages) ELSE 0 END) * 8 '未用空间(KB)',
   
8. LTRIM (STR (reservedpages * 8/1024/1024, 15, 0) + ' GB') as '已用空间(GB)'
   
9. from(
   
10. SELECT name,
    
11. SUM (reserved_page_count) as reservedpages ,
    
12. SUM (used_page_count) as usedpages ,
    
13. SUM (
    
14.    CASE
    
15.        WHEN (index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)
    
16.        ELSE lob_used_page_count + row_overflow_used_page_count
    
17.    END
    
18.    ) as pages,
    
19. SUM (
    
20.    CASE
    
21.        WHEN (index_id < 2) THEN row_count
    
22.        ELSE 0
    
23.    END
    
24.    )  as row_Count
    
25. FROM sys.dm_db_partition_stats
    
26. inner join sys.objects on sys.dm_db_partition_stats.object_id=sys.objects.object_id
    
27. where type='U'
    
28. group by sys.objects.name
    
29. union
    
30. SELECT sys.objects.name,
    
31. sum(reserved_page_count) as reservedpages,
    
32. sum(used_page_count) as usedpages,
    
33. 0 as pages,
    
34. 0 as row_count
    
35. from sys.objects inner join sys.internal_tables on
    
36. sys.objects.object_id = sys.internal_tables.parent_id
    
37. inner join sys.dm_db_partition_stats on sys.dm_db_partition_stats.object_id=sys.internal_tables.object_id
    
38. where sys.internal_tables.internal_type IN (202,204,211,212,213,214,215,216)
    
39. group by sys.objects.name) t
    
40. order by '已用空间(KB)' desc
    

查看物理读高的100条SQL

1. SELECT TOP 100
   
2. a.session_id,a.client_net_address,
   
3. qs.total_physical_reads,qs.execution_count,
   
4. qs.total_physical_reads /qs.execution_count as avg_io,
   
5. qt.text, db_name(qt.dbid) as dbname, qt.objectid
   
6. FROM sys.dm_exec_query_stats qs
   
7.  CROSS apply sys.dm_exec_sql_text(qs.sql_handle) as qt
   
8.  Left join (select a.session_id, a.sql_handle sql_handle,b.client_net_address  client_net_address
   
9.             From sys. dm_exec_requests  a, sys.dm_exec_connections b
   
10.             where a.session_id = b.session_id
    
11.             ) a on qs.sql_handle = a. sql_handle
    
12.  ORDER BY qs.total_physical_reads desc
    

查看逻辑读高的100条SQL

1. SELECT TOP 100
   
2. a.session_id,a.client_net_address,
   
3. qs.total_logical_reads,qs.execution_count,
   
4. qs.total_logical_reads /qs.execution_count as avg_io,
   
5. qt.text, db_name(qt.dbid) as dbname
   
6. FROM sys.dm_exec_query_stats qs
   
7. cross apply sys.dm_exec_sql_text(qs.sql_handle) as qt
   
8. Left join (select a.session_id, a.sql_handle sql_handle,b.client_net_address  client_net_address
   
9.             From sys. dm_exec_requests  a, sys.dm_exec_connections b
   
10.             where a.session_id = b.session_id
    
11.             ) a on qs.sql_handle = a. sql_handle
    
12. ORDER BY qs.total_logical_reads desc
    

查看CPU高的100条SQL

1. SELECT TOP 20
   
2.    total_worker_time/1000 AS [总消耗CPU 时间(ms)],execution_count [运行次数],
   
3.    qs.total_worker_time/qs.execution_count/1000 AS [平均消耗CPU 时间(ms)],
   
4.    last_execution_time AS [最后一次执行时间],max_worker_time /1000 AS [最大执行时间(ms)],
   
5.    SUBSTRING(qt.text,qs.statement_start_offset/2+1,
   
6.        (CASE WHEN qs.statement_end_offset = -1
   
7.        THEN DATALENGTH(qt.text)
   
8.        ELSE qs.statement_end_offset END -qs.statement_start_offset)/2 + 1)
   
9.    AS [使用CPU的语法], qt.text [完整语法],
   
10.    qt.dbid, dbname=db_name(qt.dbid),
    
11.    qt.objectid,object_name(qt.objectid,qt.dbid) ObjectName
    
12. FROM sys.dm_exec_query_stats qs WITH(nolock)
    
13. CROSS apply sys.dm_exec_sql_text(qs.sql_handle) AS qt
    
14. WHERE execution_count>1
    
15. ORDER BY  total_worker_time DESC
    

查看内存消耗高的SQL

1. SELECT a.session_id,a.client_net_address,SS.SUM_EXECUTION_COUNT,
   
2. T.TEXT,
   
3. SS.SUM_TOTAL_ELAPSED_TIME,
   
4. SS.SUM_TOTAL_WORKER_TIME,
   
5. SS.SUM_TOTAL_LOGICAL_READS,
   
6. SS.SUM_TOTAL_LOGICAL_WRITES
   
7. FROM (SELECT S.PLAN_HANDLE,
   
8. SUM(S.EXECUTION_COUNT)SUM_EXECUTION_COUNT,
   
9. SUM(S.TOTAL_ELAPSED_TIME)SUM_TOTAL_ELAPSED_TIME,
   
10. SUM(S.TOTAL_WORKER_TIME)SUM_TOTAL_WORKER_TIME,
    
11. SUM(S.TOTAL_LOGICAL_READS)SUM_TOTAL_LOGICAL_READS,
    
12. SUM(S.TOTAL_LOGICAL_WRITES)SUM_TOTAL_LOGICAL_WRITES
    
13. FROM SYS.DM_EXEC_QUERY_STATS S
    
14. GROUP BY S.PLAN_HANDLE
    
15. ) AS SS
    
16. CROSS APPLY SYS.dm_exec_sql_text(SS.PLAN_HANDLE)T
    
17. Left join (select a.session_id, a.plan_handle plan_handle,b.client_net_address  client_net_address
    
18.             From sys. dm_exec_requests  a, sys.dm_exec_connections b
    
19.             where a.session_id = b.session_id
    
20.             ) a on ss.plan_handle = a. plan_handle
    
21. ORDER BY SUM_TOTAL_LOGICAL_READS DESC
    

查找执行慢的SQL

1. SELECT a.session_id,a.client_net_address,
   
2. (total_elapsed_time / execution_count)/1000 N'平均时间ms'
   
3. ,total_elapsed_time/1000 N'总花费时间ms'
   
4. ,total_worker_time/1000 N'所用的CPU总时间ms'
   
5. ,total_physical_reads N'物理读取总次数'
   
6. ,total_logical_reads/execution_count N'每次逻辑读次数'
   
7. ,total_logical_reads N'逻辑读取总次数'
   
8. ,total_logical_writes N'逻辑写入总次数'
   
9. ,execution_count N'执行次数'
   
10. ,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'执行语句'
    
11. ,st.text
    
12. ,creation_time N'语句编译时间'
    
13. ,last_execution_time N'上次执行时间'
    
14. FROM sys.dm_exec_query_stats AS qs
    
15. CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
    
16. Left join (select a.session_id, a.sql_handle sql_handle,b.client_net_address  client_net_address
    
17.             From sys. dm_exec_requests  a, sys.dm_exec_connections b
    
18.             where a.session_id = b.session_id
    
19.             ) a on qs.sql_handle = a. sql_handle
    
20. ORDER BY total_elapsed_time / execution_count DESC
    

查看正在执行的SQL语句

1. SELECT   spid,
   
2.         blocked,
   
3.         DB_NAME(sp.dbid) AS DBName,
   
4.         program_name,
   
5.         waitresource,
   
6.         lastwaittype,
   
7.         sp.loginame,
   
8.         sp.hostname,
   
9.         a.[Text] AS [TextData],
   
10.         SUBSTRING(A.text, sp.stmt_start / 2,
    
11.         (CASE WHEN sp.stmt_end = -1 THEN DATALENGTH(A.text) ELSE sp.stmt_end
    
12.         END - sp.stmt_start) / 2) AS [current_cmd]
    
13. FROM     sys.sysprocesses AS sp OUTER APPLY sys.dm_exec_sql_text (sp.sql_handle) AS A
    
14. WHERE    spid > 50
    

日志收缩注意问题

1. select * from sys.databases
   
2. 当 log_reuse_wait_desc 为nothing装态，直接收缩无限制。为log_backup时，再作一次备份，然后进行收缩。
   
3. SELECT role_desc, state_desc FROM sys.database_mirroring_endpoints
   
4. SELECT mirroring_safety_level_desc, mirroring_witness_name, mirroring_witness_state_desc FROM sys.database_mirroring
   
5. 为active_transaction 表示有大事务，为database_mirroring 镜像状态异常，需要resume。
   

检查日志空间占用及不能截断原因

1. DBCC SQLPERF(LOGSPACE)  
   
2. GO
   
3. SELECT name,recovery_model_desc,log_reuse_wait,log_reuse_wait_desc
   
4. FROM sys.databases
   
5. GO
   
6. log_reuse_wait_desc 为REPLICATION时，多为cdc相关，use Xxx; 重新开启cdc再关闭
   

查询是否开启CDC

1. SELECT   IS_CDC_ENABLED
   
2.        ,CASE WHEN IS_CDC_ENABLED = 0
   
3.              THEN 'CDC功能禁用'
   
4.              ELSE 'CDC功能启用'END 描述
   
5. FROM     SYS.DATABASES
   
6. WHERE   NAME  = 'XXXX'
   

开启CDC和关闭CDC

1. 对当前数据库启用CDC
   
2. USE xxx
   
3. GO
   
4. EXECUTE sys.sp_cdc_enable_db
   
5. GO
   
6. 禁用CDC
   
7. USE xxx
   
8. GO
   
9. EXECUTE sys.sp_cdc_disable_db
   
10. GO
    

开启表的异常捕获

1. exec sys.sp_cdc_enable_table  @source_schema='模式名称 ',
   
2. @source_name='表名称',
   
3. @role_name= 'CDC角色名称'