SQL

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

SELECT [name], [value], [value_in_use]

FROM sys.configurations

WHERE [name] = 'max server memory (MB)' OR [name] = 'min server memory (MB)';

修改内存的大小
sp_configure 'show advanced options', 1;

RECONFIGURE;

sp_configure 'max server memory (MB)', 4096; -- 设置最大内存限制为4GB

RECONFIGURE;

启用对表的压缩

EXEC sp_estimate_data_compression_savings 'Production', 'TransactionHistory', NULL, NULL, 'ROW';


ALTER TABLE Production.TransactionHistory REBUILD PARTITION = ALL

WITH (DATA_COMPRESSION = ROW);

GO

启用索引压缩

SELECT name, index_id

FROM sys.indexes

WHERE OBJECT_NAME (object_id) = N'TransactionHistory';


EXEC sp_estimate_data_compression_savings

    @schema_name = 'Production',

    @object_name = 'TransactionHistory',

    @index_id = 2,

    @partition_number = NULL,

    @data_compression = 'PAGE';


ALTER INDEX IX_TransactionHistory_ProductID ON Production.TransactionHistory REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE);

GO

读取错误日志
EXEC sp_readerrorlog 0, 1, 'database', 'start'
限制错误日志大小

USE [master];

GO


EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE',

    N'Software\Microsoft\MSSQLServer\MSSQLServer',

    N'ErrorLogSizeInKb', REG_DWORD, 5120;

GO

CHECKPOINT检查点
CHECKPOINT [ checkpoint_duration ]

查询是否有死锁

WITH    CTE_SID ( BSID, SID, sql_handle )        

  AS ( SELECT   blocking_session_id ,

                        session_id ,

                        sql_handle

               FROM     sys.dm_exec_requests

               WHERE    blocking_session_id <> 0

               UNION ALL

               SELECT   A.blocking_session_id ,

                        A.session_id ,

                        A.sql_handle

               FROM     sys.dm_exec_requests A

                       JOIN CTE_SID B ON A.SESSION_ID = B.BSID

             )

    SELECT  C.BSID ,  C.SID , S.login_name , S.host_name , S.status ,S.cpu_time ,

            S.memory_usage ,S.last_request_start_time , S.last_request_end_time ,S.logical_reads ,

            S.row_count ,

            q.text

    FROM    CTE_SID C

            JOIN sys.dm_exec_sessions S ON C.sid = s.session_id

            CROSS APPLY sys.dm_exec_sql_text(C.sql_handle) Q

ORDER BY sid

查看阻塞超时设置
SELECT @@LOCK_TIMEOUT
查看数据库的负载

SELECT substring (a.name,0,20) as [数据库名],

[连接数] = (SELECT COUNT(*) FROM master..sysprocesses b WHERE a.dbid = b.dbid), [阻塞进程] = (SELECT COUNT(*)

FROM master..sysprocesses b

WHERE a.dbid = b.dbid AND blocked <> 0),

[总内存] = ISNULL((SELECT SUM(memusage) FROM master..sysprocesses b WHERE a.dbid = b.dbid),0),

[总IO] = ISNULL((SELECT SUM(physical_io) FROM master..sysprocesses b WHERE a.dbid = b.dbid),0),

[总CPU] = ISNULL((SELECT SUM(cpu) FROM master..sysprocesses b

WHERE a.dbid = b.dbid),0), [总等待时间] = ISNULL((SELECT SUM(waittime)

FROM master..sysprocesses b

WHERE a.dbid = b.dbid),0) FROM master.dbo.sysdatabases a WITH (nolock)

WHERE  DatabasePropertyEx(a.name,'Status') = 'ONLINE'

ORDER BY [数据库名]

整理索引碎片

第一步：查询表的索引碎片情况

DBCC SHOWCONTIG WITH ALL_INDEXES；

第二步：删除并重建索引 

第三步：使用DROP_EXISTING子句重建索引 

第四步：执行DBCC DBREINDEX

第五步：执行DBCC INDEXDEFRAG


Select 'DBCC INDEXDEFRAG ('+DB_Name()+','+Object_Name(ID)+','+Cast(INDID As Varchar)+')'+Char(10)

From SysIndexes

Where ID Not IN (Select ID From SYSObjects Where xType='S')

查询索引

SELECT CASE  WHEN t.[type] = 'U' THEN'表'

             WHEN t.[type] = 'V' THEN '视图'  END AS '类型',

       SCHEMA_NAME(t.schema_id) + '.' + t.[name] AS '(表/视图)名称',

       i.[name] AS 索引名称,

       SUBSTRING(column_names, 1, LEN(column_names) - 1) AS '列名',

       CASE WHEN i.[type] = 1 THEN '聚集索引'

            WHEN i.[type] = 2 THEN  '非聚集索引'

            WHEN i.[type] = 3 THEN  'XML索引'

           WHEN i.[type] = 4 THEN '空间索引'

           WHEN i.[type] = 5 THEN '聚簇列存储索引'

           WHEN i.[type] = 6 THEN '非聚集列存储索引'

       WHEN i.[type] = 7 THEN'非聚集哈希索引'

           END AS '索引类型',

      CASE  WHEN i.is_unique = 1 THEN'唯一' ELSE '不唯一' END AS '索引是否唯一'

FROM sys.objects t

    INNER JOIN sys.indexes i

        ON t.object_id = i.object_id

    CROSS APPLY

(

    SELECT col.[name] + ', '

    FROM sys.index_columns ic

        INNER JOIN sys.columns col

           ON ic.object_id = col.object_id

               AND ic.column_id = col.column_id

    WHERE ic.object_id = t.object_id

          AND ic.index_id = i.index_id

    ORDER BY col.column_id

    FOR XML PATH('')

) D(column_names)

WHERE t.is_ms_shipped <> 1

      AND index_id > 0

ORDER BY i.[name];

整理索引

select 'drop index ' + index_name + ' on ' + tab_name + ';' ,a.tab_name, a.index_Type,a.index_colum FROM (

SELECT CASE  WHEN t.[type] = 'U' THEN'表'

             WHEN t.[type] = 'V' THEN '视图'  END AS '类型',

       SCHEMA_NAME(t.schema_id) + '.' + t.[name] AS 'tab_name',

       i.[name] AS index_name,

       SUBSTRING(column_names, 1, LEN(column_names) - 1) AS 'index_colum',

       CASE WHEN i.[type] = 1 THEN '聚集索引'

            WHEN i.[type] = 2 THEN  '非聚集索引'

            WHEN i.[type] = 3 THEN  'XML索引'

           WHEN i.[type] = 4 THEN '空间索引'

           WHEN i.[type] = 5 THEN '聚簇列存储索引'

           WHEN i.[type] = 6 THEN '非聚集列存储索引'

       WHEN i.[type] = 7 THEN'非聚集哈希索引'

           END AS index_Type,

      CASE  WHEN i.is_unique = 1 THEN'唯一' ELSE '不唯一' END AS 'index_only'

FROM sys.objects t

    INNER JOIN sys.indexes i

        ON t.object_id = i.object_id

    CROSS APPLY

(

    SELECT col.[name] + ', '

    FROM sys.index_columns ic

        INNER JOIN sys.columns col

           ON ic.object_id = col.object_id

               AND ic.column_id = col.column_id

    WHERE ic.object_id = t.object_id

          AND ic.index_id = i.index_id

    ORDER BY col.column_id

    FOR XML PATH('')

) D(column_names)

WHERE t.is_ms_shipped <> 1

      AND index_id > 0

) a

where a.index_TYpe = '非聚集索引' and a.index_only = '不唯一';


select 'create index ' + 'idx_' + tab_name + '_' + a.index_colum + ' on ' + tab_name + '('  + a.index_colum + ');',

       a.tab_name, a.index_Type,a.index_colum FROM (

SELECT CASE  WHEN t.[type] = 'U' THEN'表'

             WHEN t.[type] = 'V' THEN '视图'  END AS '类型',

       SCHEMA_NAME(t.schema_id) + '.' + t.[name] AS 'tab_name',

       i.[name] AS index_name,

       SUBSTRING(column_names, 1, LEN(column_names) - 1) AS 'index_colum',

       CASE WHEN i.[type] = 1 THEN '聚集索引'

            WHEN i.[type] = 2 THEN  '非聚集索引'

            WHEN i.[type] = 3 THEN  'XML索引'

           WHEN i.[type] = 4 THEN '空间索引'

           WHEN i.[type] = 5 THEN '聚簇列存储索引'

           WHEN i.[type] = 6 THEN '非聚集列存储索引'

       WHEN i.[type] = 7 THEN'非聚集哈希索引'

           END AS index_Type,

      CASE  WHEN i.is_unique = 1 THEN'唯一' ELSE '不唯一' END AS 'index_only'

FROM sys.objects t

    INNER JOIN sys.indexes i

        ON t.object_id = i.object_id

    CROSS APPLY

(

    SELECT col.[name] + ', '

    FROM sys.index_columns ic

        INNER JOIN sys.columns col

           ON ic.object_id = col.object_id

               AND ic.column_id = col.column_id

    WHERE ic.object_id = t.object_id

          AND ic.index_id = i.index_id

    ORDER BY col.column_id

    FOR XML PATH('')

) D(column_names)

WHERE t.is_ms_shipped <> 1

      AND index_id > 0

) a

where a.index_TYpe = '非聚集索引' and index_only = '不唯一'

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

select o.name, SUM(p.reserved_page_count) as reserved_page_count,

               SUM(p.used_page_count) as used_page_count, 

              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, 

              SUM( case when (p.index_id<2) then row_count else 0 end ) as rowCounts

from sys.dm_db_partition_stats p

inner join sys.objects o on p.object_id=o.object_id group by o.name order by rowCounts desc

查看表的占用情况

SELECT

name '表名', 

convert (char(11), row_Count) as '数据条数', 

(reservedpages * 8) '已用空间(KB)', 

(pages * 8) '数据占用空间(KB)', 

(CASE WHEN usedpages > pages THEN (usedpages - pages) ELSE 0 END) * 8 '索引占用空间(KB)', 

(CASE WHEN reservedpages > usedpages THEN (reservedpages - usedpages) ELSE 0 END) * 8 '未用空间(KB)', 

LTRIM (STR (reservedpages * 8/1024/1024, 15, 0) + ' GB') as '已用空间(GB)' 

from( 

SELECT name, 

SUM (reserved_page_count) as reservedpages , 

SUM (used_page_count) as usedpages , 

SUM ( 

    CASE

        WHEN (index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count) 

        ELSE lob_used_page_count + row_overflow_used_page_count

    END 

    ) as pages, 

SUM ( 

    CASE

        WHEN (index_id < 2) THEN row_count

        ELSE 0 

    END 

    )  as row_Count

FROM sys.dm_db_partition_stats

inner join sys.objects on sys.dm_db_partition_stats.object_id=sys.objects.object_id

where type='U' 

group by sys.objects.name

union 

SELECT sys.objects.name, 

sum(reserved_page_count) as reservedpages, 

sum(used_page_count) as usedpages, 

0 as pages, 

0 as row_count

from sys.objects inner join sys.internal_tables on

 sys.objects.object_id = sys.internal_tables.parent_id

inner join sys.dm_db_partition_stats on sys.dm_db_partition_stats.object_id=sys.internal_tables.object_id

where sys.internal_tables.internal_type IN (202,204,211,212,213,214,215,216) 

group by sys.objects.name) t

order by '已用空间(KB)' desc

查看物理读高的100条SQL

SELECT TOP 100 

 a.session_id,a.client_net_address,

 qs.total_physical_reads,qs.execution_count,

 qs.total_physical_reads /qs.execution_count as avg_io,

 qt.text, db_name(qt.dbid) as dbname, qt.objectid

 FROM sys.dm_exec_query_stats qs

  CROSS apply sys.dm_exec_sql_text(qs.sql_handle) as qt

  Left join (select a.session_id, a.sql_handle sql_handle,b.client_net_address  client_net_address

             From sys. dm_exec_requests  a, sys.dm_exec_connections b

             where a.session_id = b.session_id

             ) a on qs.sql_handle = a. sql_handle

  ORDER BY qs.total_physical_reads desc

查看逻辑读高的100条SQL

SELECT TOP 100

a.session_id,a.client_net_address,

 qs.total_logical_reads,qs.execution_count,

 qs.total_logical_reads /qs.execution_count as avg_io,

 qt.text, db_name(qt.dbid) as dbname

 FROM sys.dm_exec_query_stats qs

 cross apply sys.dm_exec_sql_text(qs.sql_handle) as qt

 Left join (select a.session_id, a.sql_handle sql_handle,b.client_net_address  client_net_address

             From sys. dm_exec_requests  a, sys.dm_exec_connections b

             where a.session_id = b.session_id

             ) a on qs.sql_handle = a. sql_handle

 ORDER BY qs.total_logical_reads desc

查看CPU高的100条SQL

SELECT TOP 20

    total_worker_time/1000 AS [总消耗CPU 时间(ms)],execution_count [运行次数],

    qs.total_worker_time/qs.execution_count/1000 AS [平均消耗CPU 时间(ms)],

    last_execution_time AS [最后一次执行时间],max_worker_time /1000 AS [最大执行时间(ms)],

    SUBSTRING(qt.text,qs.statement_start_offset/2+1, 

        (CASE WHEN qs.statement_end_offset = -1 

        THEN DATALENGTH(qt.text) 

        ELSE qs.statement_end_offset END -qs.statement_start_offset)/2 + 1) 

    AS [使用CPU的语法], qt.text [完整语法],

    qt.dbid, dbname=db_name(qt.dbid),

    qt.objectid,object_name(qt.objectid,qt.dbid) ObjectName

FROM sys.dm_exec_query_stats qs WITH(nolock)

CROSS apply sys.dm_exec_sql_text(qs.sql_handle) AS qt

WHERE execution_count>1

ORDER BY  total_worker_time DESC

查看内存消耗高的SQL

SELECT a.session_id,a.client_net_address,SS.SUM_EXECUTION_COUNT,

T.TEXT,

SS.SUM_TOTAL_ELAPSED_TIME,

SS.SUM_TOTAL_WORKER_TIME,

SS.SUM_TOTAL_LOGICAL_READS,

SS.SUM_TOTAL_LOGICAL_WRITES

FROM (SELECT S.PLAN_HANDLE,

SUM(S.EXECUTION_COUNT)SUM_EXECUTION_COUNT,

SUM(S.TOTAL_ELAPSED_TIME)SUM_TOTAL_ELAPSED_TIME,

SUM(S.TOTAL_WORKER_TIME)SUM_TOTAL_WORKER_TIME,

SUM(S.TOTAL_LOGICAL_READS)SUM_TOTAL_LOGICAL_READS,

SUM(S.TOTAL_LOGICAL_WRITES)SUM_TOTAL_LOGICAL_WRITES

FROM SYS.DM_EXEC_QUERY_STATS S

GROUP BY S.PLAN_HANDLE

) AS SS

CROSS APPLY SYS.dm_exec_sql_text(SS.PLAN_HANDLE)T

 Left join (select a.session_id, a.plan_handle plan_handle,b.client_net_address  client_net_address

             From sys. dm_exec_requests  a, sys.dm_exec_connections b

             where a.session_id = b.session_id

             ) a on ss.plan_handle = a. plan_handle

ORDER BY SUM_TOTAL_LOGICAL_READS DESC

查找执行慢的SQL

SELECT a.session_id,a.client_net_address,

(total_elapsed_time / execution_count)/1000 N'平均时间ms' 

,total_elapsed_time/1000 N'总花费时间ms' 

,total_worker_time/1000 N'所用的CPU总时间ms' 

,total_physical_reads N'物理读取总次数' 

,total_logical_reads/execution_count N'每次逻辑读次数' 

,total_logical_reads N'逻辑读取总次数' 

,total_logical_writes N'逻辑写入总次数' 

,execution_count N'执行次数' 

,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'执行语句' 

,st.text

,creation_time N'语句编译时间' 

,last_execution_time N'上次执行时间' 

FROM sys.dm_exec_query_stats AS qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st

Left join (select a.session_id, a.sql_handle sql_handle,b.client_net_address  client_net_address

             From sys. dm_exec_requests  a, sys.dm_exec_connections b

             where a.session_id = b.session_id

             ) a on qs.sql_handle = a. sql_handle

ORDER BY total_elapsed_time / execution_count DESC

查看正在执行的SQL语句

SELECT   spid,

         blocked,

         DB_NAME(sp.dbid) AS DBName,

         program_name,

         waitresource,

         lastwaittype,

         sp.loginame,

         sp.hostname,

         a.[Text] AS [TextData],

         SUBSTRING(A.text, sp.stmt_start / 2,

         (CASE WHEN sp.stmt_end = -1 THEN DATALENGTH(A.text) ELSE sp.stmt_end

         END - sp.stmt_start) / 2) AS [current_cmd]

FROM     sys.sysprocesses AS sp OUTER APPLY sys.dm_exec_sql_text (sp.sql_handle) AS A

WHERE    spid > 50

日志收缩注意问题

select * from sys.databases

当 log_reuse_wait_desc 为nothing装态，直接收缩无限制。为log_backup时，再作一次备份，然后进行收缩。

SELECT role_desc, state_desc FROM sys.database_mirroring_endpoints

SELECT mirroring_safety_level_desc, mirroring_witness_name, mirroring_witness_state_desc FROM sys.database_mirroring

为active_transaction 表示有大事务，为database_mirroring 镜像状态异常，需要resume。

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

DBCC SQLPERF(LOGSPACE)  

GO

SELECT name,recovery_model_desc,log_reuse_wait,log_reuse_wait_desc

FROM sys.databases

GO

log_reuse_wait_desc 为REPLICATION时，多为cdc相关，use Xxx; 重新开启cdc再关闭

查询是否开启CDC
SELECT   IS_CDC_ENABLED

        ,CASE WHEN IS_CDC_ENABLED = 0 

              THEN 'CDC功能禁用'

              ELSE 'CDC功能启用'END 描述

FROM     SYS.DATABASES

WHERE   NAME  = 'XXXX'

开启CDC和关闭CDC
对当前数据库启用CDC

USE xxx

GO

EXECUTE sys.sp_cdc_enable_db

GO

禁用CDC

USE xxx

GO

EXECUTE sys.sp_cdc_disable_db

GO

开启表的异常捕获

exec sys.sp_cdc_enable_table  @source_schema='模式名称 ',

@source_name='表名称',

@role_name= 'CDC角色名称'