WIN2012安装SQL2016

      【1】 安装SQL2016的前置条件
               需要安装KB2919355-x64补丁。  而KB2919355又需要前置安装KB2919442-X64补丁。
      【2】 安装KB2919355补丁，报错：更新失败
               第一步。移除失败更新（对所有更新补丁可用），OS下命令行使用
               dism /online /remove-package /packagename:Package_for_KB2919355~31bf3856ad364e35~amd64~~6.3.1.14
               第二步。清除Windows Update缓存
                dism /online /cleanup-image /startcomponentcleanup
               第三步。重启计算机后，再次进行安装。

SqlServer T-SQL

一、T-SQL 概述

此语言为Microsoft公司设计开发的结构化查询语言，是对SQL的扩展，具备标准SQL的主要特点，该语言可以实现图形界面能够完成的所有功能！

1、注释

代码中不执行的部分，一般是对功能的讲解说明，以 -- 开头到结尾部分的行 ，处于不执行阶段，单行注释，以/**/ 包裹的文本块，一般多行注释使用

2、变量

局部变量：作用在程序内部，以@开头的名称，且必须以 declare定义后才可以使用

#定义
declare {@l_v1 data_type}[,...n]
#赋值
set @l_v1=expression
select {@l_v1=expression} [,...n]
#显示
print @l_v1
select @l_v1
declare @name varchar(8),@sex varchar(2),@age smallint
declare @address varchar(100) 
set @address='宣城'
select @address

全局变量：非用户定义，是服务器级别的，以@@开头

常用的全局变量

@@connections 返回最近一次启动SQL Server，尝试连接的次数
@@error    返回最后执行SQL 语句的错误代码
@@rowcount    返回上次语句影响的行数
@@servername    返回允许SQL Server本地服务器的名称
@@version    返回SQL Server当前安装的日期、版本和处理器类型
@@language    返回SQL Server服务器的语言

3、运算符

算数运算符、赋值运算符、位运算符、比较运算符、逻辑运算符、字符串串联运算符

二、 T-SQL语句

1、DML
        数据操作语言：查询、插入、删除、更新

select
insert
delete
update

2、DDL
        数据定义语言：定义结构和数据关系等

create
alter
drop

grant
revoke
deny

Alter 扩展：

在修改Sql Server表结构时，常用到Alter语句，把一些常用的alter语句列举如下

1：向表中添加字段

Alter table [表名] add [列名] 类型

2:  删除字段

Alter table [表名]  drop column [列名]

3:  修改表中字段类型 （可以修改列的类型，是否为空）

Alter table [表名] alter column [列名] 类型

4：添加主键

Alter table [表名] add constraint [ 约束名] primary key( [列名])

5：添加唯一约束

Alter table [表名] add constraint [ 约束名] unique([列名])

6：添加表中某列的默认值

Alter table [表名] add constraint [约束名] default(默认值) for [列名]

7：添加约束

Alter table [表名] add constraint [约束名] check (内容)

8:添加外键约束

Alter table [表名] add constraint [约束名]  foreign key(列名) referencese 另一表名(列名)

9:删除约束

Alter table [表名] drop constraint [约束名]

10:重命名表

exec sp_rename '[原表名]','[新表名]'

11：重命名列名

exec sp_rename '[表名].[列名]','[表名].[新列名]'

删除主键，以及主键上的索引

alter table table_name drop constraint clusteredName

三、流程控制

1、流程控制关键字

        declare ： 声明变量
        begin...end：定义语句块
        berak  ：退出while循环
        continue  ：重新开始循环
        goto label  ： 从label之后的语句处继续进行处理
        if...else   ： 双分支判断
        case...when...then...end ：多分支判断
        return  ：退出
        waitfor ：等待延迟
        while  ：循环
        print ：输出

2、语法格式

begin
    {
    select、insert、update...
    }
end
if xxx
    xxx
     
     
if xxx
    xxx
else xxx
    xxx
     
case  xxx
when  xxx  then  xxx
...
when  xxx  then  xxx
else xxx
end
while xxx
    xxx
    [bread]
    xxx
    [continue]

问题追踪

线上SQLServer数据库的CPU被打爆了，紧急情况下，分析了数据库阻塞、连接分布、最耗CPU的TOP10 SQL、查询SQL并行度配置、查询SQL 重编译的原因等等

整理了一些常用的SQL

1. 查询数据库阻塞

SELECT * FROM  sys.sysprocesses WHERE blocked<>0　

查询结果中，重点看Blocked这一列，先找出最多的SID，然后循环找出Root的阻塞根源SID

查询阻塞根源Session的SQL

DBCC Inputbuffer(sid)

2. 查询SQL连接分布

SELECT Hostname FROM  sys.sysprocesses WHERE hostname<>''

3. 查询最消耗CPU的SQL Top10   

select top(10) st.text as Query, qs.total_worker_time, qs.execution_count from
sys.dm_exec_query_stats as qs CROSS Apply sys.dm_exec_sql_text(qs.sql_handle) AS st
order by qs.total_worker_time desc

4. 查看SQLServer并行度

SELECT value_in_use  FROM sys.configurations WHERE name = 'max degree of parallelism'

并行度如果设置为1，To suppress parallel plan generation, set max degree of parallelism to 1

将阻止并行编译生成SQL执行计划，最大并行度设置为1

设置策略和具体设置方法，请参考：https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-max-degree-of-parallelism-server-configuration-option?view=sql-server-2017

USE DatabaseName ; 
GO  
EXEC sp_configure 'show advanced options', 1; 
GO 
RECONFIGURE WITH OVERRIDE; 
GO 
EXEC sp_configure 'max degree of parallelism', 16; 
GO 
RECONFIGURE WITH OVERRIDE; 
GO

5. 查询SQL Server Recompilation Reasons

select dxmv.name, dxmv.map_key,dxmv.map_value from
sys.dm_xe_map_values as dxmv where dxmv.name='statement_recompile_cause' order by dxmv.map_key

6. 将SQL Trace文件存入一张表，做聚合分析（CPU、IO、执行时间等）

SELECT * INTO TabSQL
FROM fn_trace_gettable('C:\Users\***\Desktop\Trace\sql05trace20180606-业务.trc', default);
GO

对上述表数据进行聚合分析最耗时的SQL

 select  top 100    
        replace(replace(replace(  substring(Textdata,1,6600) ,char(10),' '),char(13),' ') ,char(9),' ')  as '名称',
        --substring(Textdata,1,6600)  as old,
       count(*) as '数量',
       sum(duration/1000) as '总执行时间ms',
       avg(duration/1000) as '平均执行时间ms',
       avg(cpu) as '平均CPU时间ms',
       avg(reads) as '平均读次数',
       avg(writes) as '平均写次数', LoginName
from TabSQL   t
group by   replace(replace(replace(  substring(Textdata,1,6600) ,char(10),' '),char(13),' ') ,char(9),' ') , LoginName
order by sum(duration) desc

最耗IO的SQL

select  TOP 100 replace(replace(replace(  substring(Textdata,1,6600) ,char(10),' '),char(13),' ') ,char(9),' ') as '名称' ,LoginName,
       count(*) as '数量',
       sum(duration/1000) as '总执行时间ms',
       avg(duration/1000) as '平均执行时间ms',
       sum(cpu) as '总CPU时间ms',
       avg(cpu) as '平均CPU时间ms',
       sum(reads) as '总读次数',
       avg(reads) as '平均读次数',
       avg(writes) as '平均写次数'
from TabSQL
group by replace(replace(replace(  substring(Textdata,1,6600) ,char(10),' '),char(13),' ') ,char(9),' ')  ,LoginName
order by  sum(reads) desc

最耗CPU的SQL

SELECT TOP 100 replace(replace(replace(  substring(Textdata,1,6600) ,char(10),' '),char(13),' ') ,char(9),' ')  as '名称',LoginName,
       count(*) as '数量',
       sum(duration/1000) as '总执行时间ms',
       avg(duration/1000) as '平均执行时间ms',
       sum(cpu) as '总CPU时间',
       avg(cpu) as '平均CPU时间',
       avg(reads) as '平均读次数',
       avg(writes) as '平均写次数'
from TabSQL
group by replace(replace(replace(  substring(Textdata,1,6600) ,char(10),' '),char(13),' ') ,char(9),' ')   ,LoginName
order by avg(cpu) desc