--微信号:wuyongsoftware 邮箱：wuyongsoftware@163.com

--1.Enterprise Edition (64-bit)

SELECT SERVERPROPERTY("Edition")

--2.Microsoft SQL Server 2014 - 12.0.2000.8 (X64) Feb 20 2014 20:04:26 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.3 (Build 9600: )

SELECT @@VERSION

--3.调整最大并行度，建议修改为1，也可在数据库直接执行，修改方法如下：

sp_configure "show advanced options", 1;

GO

RECONFIGURE WITH OVERRIDE;

GO

sp_configure "max degree of parallelism", 1;

GO

RECONFIGURE WITH OVERRIDE;

GO

--4.调整针对即席工作负荷进行优化=True，也可在数据库直接执行，修改方法如下：

sp_configure "show advanced options", 1;

GO

RECONFIGURE WITH OVERRIDE;

GO

sp_configure "Optimize for Ad hoc Workloads", 1;

GO

RECONFIGURE WITH OVERRIDE;

GO

--5.逻辑扫描碎片=35.85%大于30%，我们认为该数据库的索引碎片较多，影响到查询性能，建议重建索引和更新统计信息。

DBCC SHOWCONTIG("T_BD_MATERIAL")

--6.使用SQL Server的维护计划向导新建一个重新生成索引的维护任务。

--6.1.登录管理中心，使用数据中心列表的升级下拉功能：数据库优化，进行索引重建和更新统计信息。这个过程可能运行较长时间，并且会导致表阻塞，影响系统性能，请在非系统频繁使用期，进行优化.

--6.2.手工简单重建索引办法，--在对应的业务数据库执行下列的语句，时间长也没有成功，建议使用上面两种方法。

declare @sql varchar(max)

set @sql=""

select @sql=@sql+"dbcc dbreindex("+name+");" from sys.tables where name not like "tm%"

exec(@sql)

--7.临时表数量统计

SELECT count(T.NAME) AS COUNT_DEL

FROM

SYS.TABLES T

WHERE

EXISTS (SELECT 1

FROM

T_BAS_TEMPORARYTABLENAME

WHERE

FTABLENAME = T.NAME

AND (FCREATEDATE <= getdate() - 1

OR FPROCESSTYPE = 1))

--8.查看系统所有临时表占用的总空间

select cast(sum(a.total_pages)*8/1024 as varchar)+" MB" total

from sys.partitions p join sys.allocation_units a on p.partition_id = a.container_id

join sys.tables it on p.object_id = it.object_id

where it.name like "TMP%"

--9.查询系统中每个临时表占用的空间大小统计

select it.name, cast(sum(a.total_pages)*8 as varchar)+"KB" total

from sys.partitions p join sys.allocation_units a on p.partition_id = a.container_id

join sys.tables it on p.object_id = it.object_id

where it.name like "tm%"

group by  it.name

order by sum(a.total_pages)*8 desc

--10.删除所有已经标记为需要删除的临时表，如果临时表太多，超过2w，不建议使用该语句删除临时表

declare @sql as varchar(max)

set @sql=""

select @sql=@sql+"drop table "+name+";" from sys.tables u

join T_BAS_TEMPORARYTABLENAME v on u.name=v.FTABLENAME and

( v.FPROCESSTYPE=1 or v.FCREATEDATE

exec(@sql);

delete u from T_BAS_TEMPORARYTABLENAME u where

not exists(select 1 from sys.tables where u.ftablename=name );

--11.如果语句1的记录很多(正常值一般1000内)，那么后台删除临时表的作业肯定有一段时间没有运行；

--如果语句2的记录很多(正常值一般10000内)，那么说明有临时表残留；

--如果语句3的记录很多(正常值一般10000内)，并且如果语句3远大于语句2的记录数，说明很多生成的临时表没有记录到登记表中，属于异常情况，一般是二开或其他原因导致的临时表问题，如果这个记录数一直增长，那么需要手工执行删除临时表的语句。

select count(*) from T_BAS_TEMPORARYTABLENAME where FPROCESSTYPE=1 or FCREATEDATE

select count(*) from T_BAS_TEMPORARYTABLENAME

select count(*) from sys.tables t where name like "tmp%"

--11.1.第一步：删除登记表中的可删除的临时表登记记录

delete from T_BAS_TEMPORARYTABLENAME where FPROCESSTYPE=1 or FCREATEDATE

--11.2.第二步：删除临时表，每次删除50个临时表释放一次资源，可在业务运行期间执行语句

if object_id("temptb","table")>0 drop table temptb;

declare @sql varchar(max)

declare @icount int

declare @I int

set @sql="drop table "

set @i=1

select name,IDENTITY(int,1,1) id into temptb from sys.tables t where name like "tmp%" and len(name)=30 and name not like "tmp[_]%"

and not exists(select 1 from T_BAS_TEMPORARYTABLENAME where FTABLENAME=t.name) and create_date<=DATEADD(n,-5, GETDATE())

select @icount=@@ROWCOUNT

while @i

begin

select @sql=@sql+name+"," from temptb where id between @i and @i+49

if @@ROWCOUNT>0

set @sql=substring(@sql,1,len(@sql)-1)+";"

set @i=@i+50

exec(@sql)

set @sql="drop table "

end

if object_id("temptb","table")>0 drop table temptb;

--12.清理视图定义

declare @sql varchar(max)

set @sql=""

select @sql=@sql+"drop view "+name+char(13) from sys.views where name like "tmp%" and len(name)=30 and name not like "tmp[_]%"

if len(@sql)>0

exec(@sql)

--13.清理记录表

truncate table t_bas_temporarytablename

DBCC DBREINDEX("t_bas_temporarytablename");

--14.查看是否存在阻塞语句,返回记录说明存在阻塞,如果隔几秒再次执行，没有记录返回，那可能属于正常的阻塞

select * from master..sysprocesses where blocked<>0

--15.查询死锁信息语句

SELECT XEventData.XEvent.value("@timestamp", "datetime2(3)"),

cast(XEventData.XEvent.value("(>

FROM (SELECT CAST (target_data AS XML) AS TargetData

FROM sys.dm_xe_session_targets st

JOIN sys.dm_xe_sessions s ON s.address = st.event_session_address

WHERE [name] = "system_health" AND st.target_name = N"ring_buffer") AS Data

CROSS APPLY TargetData.nodes ("//RingBufferTarget/event") AS XEventData (XEvent)

WHERE XEventData.XEvent.value("@name", "varchar(4000)") = "xml_deadlock_report";