‘sql server’ 分类下的所有文章
2013十月24

异常连接数据库

sql server 评论关闭

SELECT CONVERT(VARCHAR(50),CONNECTIONPROPERTY('local_net_address'))host ,client_net_address,program_name,db.NAME,convert(varchar(max),t.[text]) SQLText,CONVERT(varchar(20),login_time,120) login_time,
CONVERT(VARCHAR(20),last_request_end_time,120) last_request_end_time,login_name,s.session_id

FROM master.sys.dm_exec_sessions AS s (nolock)
INNER JOIN master.sys.dm_exec_connections AS c (nolock) ON s.session_id=c.session_id
INNER JOIN master.sys.databases db ON s.database_id = db.database_id
OUTER APPLY master.sys.dm_exec_sql_text(c.most_recent_sql_handle) AS t
LEFT JOIN dbo.mdSystemIP msi ON client_net_address = msi.IP
WHERE
NOT EXISTS( –非限定IP访问
SELECT 1 FROM dbo.mdSystemIP ms WHERE client_net_address = ms.IP
)
OR

( –服务器访问但是非合法客户端,包含用ssms直联也是不合法
EXISTS(SELECT 1 FROM dbo.mdSystemIP ms WHERE client_net_address = ms.IP AND VisitType='Server')
AND program_name NOT LIKE '.Net SqlClient Data Provider%' –程序连接
AND program_name NOT LIKE 'SqlQuery%' –缓存,数据库依赖
AND program_name NOT LIKE 'SQLAgent%' –job
AND program_name NOT LIKE 'Repl%' –复制分发
AND program_name NOT LIKE 'DATABASE%' –复制分发
AND program_name NOT LIKE 'SQLCLUSTER%' –复制分发
AND program_name != 'Microsoft SQL Server' –自身执行
AND program_name != 'Microsoft® Windows® Operating System'
AND login_name != 'NT AUTHORITY\SYSTEM'
AND db.NAME NOT IN('master','msdb','distribution')
)

2013十月5

注册服务器下空白

sql server 评论关闭

sqlserver在新增注册服务器时,发现框里空白,没有数据库引擎的选项。引起的原因是将其他服务器的注册配置文件导入过来引起,处理方法:
删除:C:\Users\Administrator\AppData\Roaming\Microsoft\Microsoft SQL Server\110\Tools\Shell\RegSrvr.xml
重启ssms

2013九月27

数据库压缩备份

sqlserver2008开始提供压缩备份,好处是备份与还原时间缩短,备份文件也很小,测试结果显示文件小了2/3,不过不好的地方在于CPU要大量计算,CPU要占不少资源,不过若放在晚上执行的话,cpu可以忽略。

SELECT name,backup_start_date,backup_finish_date,compressed_backup_size
FROM msdb..backupset;
 

备份压缩 (SQL Server)

2013九月27

触发器的替代-cdc数据变更记录更好的方式

sql server 评论关闭

SQL Server 2008提供了内建的方法“”变更数据捕获“”(Change Data Capture 即CDC)以实现异步跟踪用户表的数据修改,而且这一功能拥有最小的性能开销。可以用于其他数据源的持续更新,例如将OLTP数据库中的数据变更迁移到数据仓库数据库。

--开启cdc
EXEC sys.sp_cdc_enable_db

--net_changes说的是针对一行记录,如果有多个更改的话,那么以最后的一条为准。
EXEC sys.sp_cdc_enable_table @source_schema='dbo',@source_name='frmuser',@capture_instance='frmuser',@supports_net_changes=0,@role_name=null 

--确认表已经被跟踪  

SELECT is_tracked_by_cdc FROM sys.tables
WHERE name = 'frmuser' and schema_id = SCHEMA_ID('dbo')  

EXEC sys.sp_cdc_help_change_data_capture 'dbo', 'frmuser' 

--更改表数据查询效果
UPDATE a
SET a.isvalid  = 0
FROM frmuser a
WHERE Account ='sa'

SELECT TOP 50 *
FROM [cdc].[frmuser_CT]

DECLARE @FromLSN varbinary(10) =
sys.fn_cdc_map_time_to_lsn
( 'smallest greater than or equal' , '2013-09-27 08:30')  

DECLARE @ToLSN varbinary(10) =
sys.fn_cdc_map_time_to_lsn
( 'largest less than or equal' , '2013-09-27 23:59:59')  

--注意若在上面的日期范围内无数据的话,将提示参数不足
SELECT
CASE __$operation
WHEN 1 THEN 'DELETE'
WHEN 2 THEN 'INSERT'
WHEN 3 THEN 'Before UPDATE'
WHEN 4 THEN 'After UPDATE'
END   ,a.*
FROM [cdc].[fn_cdc_get_all_changes_frmuser]
(@FromLSN, @ToLSN, 'all')   a

--撤销CDC
EXEC sys.sp_cdc_disable_table 'dbo',
'FactInternetSales','All'

EXEC sys.sp_cdc_disable_db
更改是不会永远保存,会定期清除
捕获和清除作业都是使用默认参数创建的。将立即启动捕获作业。它连续运行,每个扫描周期最多可处理 1000 个事务,并在两个周期之间停顿 5 秒钟。清除作业在每天凌晨 2 点运行一次。它将更改表项保留三天(4320 分钟),可使用单个删除语句最多删除 5000 项。
 

 
 
 
 
2013八月29

隐藏数据库

sql server 评论关闭
USE MASTER
GO
GRANT VIEW ANY DATABASE TO PUBLIC; -- turn this back on if it was off
GO
DENY VIEW ANY DATABASE TO USER_A;
GO

How to hide SQL Server user databases in SQL Server Management Studio

2013八月23

全局存储过程sp_

sql server 评论关闭

以SP_开头的存储过程不是会先去找系统存储过程。 是先去master数据库中找这个存储过程。。。之后再去用户数据库中找这个存储过程,所以假如自己写了一个对index操作的SP。 直接放在master数据库中,以SP_开头。 这样在Job的定义中。 选择到用户数据库,然后执行这个SP即可。

这样就可实现在master建立一存储过程,在所有库中均可调用。
2013八月20

配置job管理权限

sql server 评论关闭

若要配置用户以创建或执行 Microsoft SQL Server 代理作业,必须先将某个现有 SQL Server 登录名或 msdb 角色添加到 msdb 数据库中的下列 SQL Server 代理固定数据库角色之一:SQLAgentUserRole、SQLAgentReaderRole 或 SQLAgentOperatorRole。

配置帐户以创建和管理 SQL Server 代理作业

2013八月16

复制分发参数配置

通过sp_help_agent_profile查看代理类型所对应的profile_id,下面是此过程的返回结果 profile_id    profile_name    agent_type    type    description    def_profile
1    默认代理配置文件    1    0    NULL    1
2    默认代理配置文件    2    0    NULL    1
3    详细历史记录代理配置文件    2    0    用于详细历史记录日志的代理配置文件。    0
4    默认代理配置文件    3    0    NULL    1
5    详细历史记录代理配置文件    3    0    用于详细历史记录日志的代理配置文件。    0
6    默认代理配置文件    4    0    NULL    1
7    慢速链接代理配置文件    4    0    用于低带宽连接的代理配置文件。    0
8    详细历史记录代理配置文件    4    0    用于详细历史记录日志的代理配置文件。    0
9    Windows Synchronization Manager profile    4    0    Windows 同步管理器所用的配置文件。    0
10    Windows Synchronization Manager profile    3    0    Windows 同步管理器所用的配置文件。    0
11    默认代理配置文件    9    0    用于已复制的排队事务读取器的代理配置文件。    1
12    行计数验证配置文件    4    0    合并代理用于执行行计数验证的配置文件。    0
13    行计数和校验和验证配置文件    4    0    合并代理用于执行行计数和校验和验证的配置文件。    0
14    遇到数据一致性错误时继续。    3    0    用于跳过数据一致性错误的代理配置文件。它只能由 SQL Server 订阅服务器使用。    0
15    大容量服务器对服务器的配置文件    4    0    合并代理配置文件已经优化,适用于服务器之间的大容量同步。    0
16    用于 OLEDB 流式处理的分发配置文件    3    0    使用 OLEDB 流式处理为处理 LOB 数据启用的分发代理配置文件。    0


通常 快照代理 agent_type = 1,所对应的profile_id = 1

日志读取代理 agent_type = 2,所对应的profile_id = 2

分发代理 agent_type = 3,所对应的profile_id = 4

如下命令返回某个代理的配置信息,注意后面的参数是profile_id

sp_help_agent_parameter @profile_id

sp_help_agent_parameter 1 返回快照代理配置参数

profile_id parameter_name value

1 -BcpBatchSize 100000

1 -HistoryVerboseLevel 2

1 -LoginTimeout 15

1 -QueryTimeout 1800

在快照代理中增加MaxBcpThreads配置参数为20

sp_add_agent_parameter 1,'MaxBcpThreads',20

在日志读取代理中修改PollingInterval配置参数为3

sp_change_agent_parameter 2,'PollingInterval',3

2013八月16

复制分发跳过错误

sql server 评论关闭
 
1,临时跳过
–@publisher @@SERVERNAME,
–@publisher_db db_name(),
–@publication dbo.MSpublication publication,
–@subscriber sysname,
–@subscriber_db sysname
 
–在分发数据库上执行
sp_helpsubscriptionerrors 'SQLCLUSTER','brm_lvjian','brm_lvjian','DATABASED','brm_lvjian'
 
–跳过事务的命令(在订阅服务器上执行),对等数据不支持
sp_setsubscriptionxactseqno 'DATABASED','brm_lvjian','brm_lvjian',0x0005168B000054AA000500000000
 
2,遇到错误自动跳过
分发代理的 -SkipErrors 参数,可用来跳过某种类型的错误。
配置:本地发布-分发服务器属性-常规-右下默认配置文件-分发代理
-SkipErrors 参数
默认情况下,发布代理遇到错误时就会停止。 如果使用 -SkipErrors 参数,并指定了预期的或不想让其干扰复制的错误,则代理就会记录错误信息,然后继续运行。 例如,如果要指定分发代理,使其记录重复键违规但继续处理后续事务,就需要指定代理跳过错误 2601(不能在具有唯一索引 '%.*ls' 的对象 '%.*ls' 中插入重复键的行。)和 2627(违反了 %ls 约束 '%.*ls'。 不能在对象 '%.*ls' 中插入重复键):-SkipErrors 2601;2627 
-SkipErrors 参数的最常见用法是使用标题为“遇到数据一致性错误时继续”的分发代理配置文件。 这样,分发代理就会跳过错误 2601、2627 和 20598(应用复制的命令时在订阅服务器上找不到该行)。 有关详细信息,请参阅复制代理配置文件。 除了此预定义的配置文件之外,还可以在创建或修改的代理配置文件中,或在命令行中,指定该参数。 
 
 
 
sp_helpsubscriptionerrors

http://msdn.microsoft.com/zh-cn/library/ms173427.aspx

 
sp_setsubscriptionxactseqno

http://msdn.microsoft.com/zh-cn/library/ms188764.aspx 


 
2013八月16

修改数据库默认位置

sql server 评论关闭
–修改数据库默认位置
USE [master]
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultData', REG_SZ, N'D:\SQLDATA'
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultLog', REG_SZ, N'D:\SQLDATA'
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'BackupDirectory', REG_SZ, N'D:\SQLDATA'
GO
 
2013八月16

更改服务器名

sql server 评论关闭
exec sp_dropserver <(用select @@SERVERNAME显示出来的旧名)>
GO
exec sp_addserver <你现在的计算机名>, LOCAL
GO

SELECT @@SERVERNAME,SERVERPROPERTY('ServerName')
2013八月16

数据库账号权限迁移

sql server 评论关闭
如何解决在运行 SQL Server 的服务器之间移动数据库时的权限问题

思路:
1,账号:利用链接1脚本生成,主要是为了sid,以保证用用户数据库的一样
   注:链接1脚本支持sqlserver2005以上版本
2,权限:备份或附加数据库后,因为步骤1中的sid已经在新服务器上创立,故权限会自动匹配上。
 

2013八月16

只读账号设置-db_datareader

sql server 评论关闭

为防止正式环境数据库被篡改,但又要保证可以正常查询处理问题,可以将账号的数据库访问权限设置为db_datareader
数据库权限:单独设置的优先,如给了一账号只读权限,若别开一张表给于此账号写权限,那么单独设置的写权限优先级最高。
注:db_datareader没有存储过程访问权限,若想全开,如下:

GRANT EXEC TO datareader
GRANT ALTER TO datareader
 

固定数据库角色
db_owner 数据库所有者

db_accessadmin 数据库访问管理员

db_securityadmin 数据库安全管理员

db_ddladmin 数据库 DDL 管理员

db_backupoperator 数据库备份操作员

db_datareader 数据库数据读取者

db_datawriter 数据库数据写入者

db_denydatareader 数据库拒绝数据读取者

db_denydatawriter 数据库拒绝数据写入者

固定服务器角色 描述
sysadmin 在 SQL Server 中进行任何活动。该角色的权限跨越所有其它固定服务器角色。

serveradmin 配置服务器范围的设置,关闭服务器。

setupadmin 添加和删除链接服务器,并执行某些系统存储过程(如 sp_serveroption)。

securityadmin 管理服务器登录和 CREATE DATABASE 权限,还可以读取错误日志和更改密码。。

processadmin 管理在 SQL Server 实例中运行的进程。

dbcreator 创建、改变和除去数据库。

diskadmin 管理磁盘文件。

bulkadmin 执行 BULK INSERT 语句。

固定数据库角色的权限

2013八月16

数据库建立初步

sql server 评论关闭
1,数据文件等于CPU的数量数,不过要小于等于8
   Tempdb文件数量为cpu数目一半,不过大小要相等,否则自增长可能会发生在最大的文件上,(参考微软数据库支持组的:Tempdb怎么会成为性能瓶颈)
   文件大小可设置大一些如1G,自增长设置为1G,不要设置成百分比,数据会对不齐
   log文件不用,因为是顺序写,写满一个文件再写再一个
2,更改默认端口
3,如果条件允许(有3或3个以上的物理盘) ,日志和数据文件应该位于不同的磁盘
4,禁用SA
5,建立定时运行索引维护,以降低索引碎片,降低表空间。【索引维护1-索引碎片整理】

禁用sa用户.
create login [admin_sql] from windows;
go
sp_addsrvrolemember [admin_sql],sysadmin
go
ALTER LOGIN [sa] DISABLE
GO

根据cpu数目,更改tempdb数据文件,日志文件。
USE master
DECLARE @i int,@s varchar(4000),@c int
set @i=1
select @c=cpu_count from sys.dm_os_sys_info --给变量赋值,获取cpu数目
while (@i<@c/2 and @i<8)
begin
--获取创建tempdb文件的脚本
set
@s=
'alter database tempdb add file (name=tempdev'
+convert(varchar,@i)
+',filename=''d:\SQLData\tempdev'   --文件存放路径命名规则:如果是2块盘,次要数据文件放在d盘,其余不变.
+convert(varchar,@i)
+'.ndf'',SIZE = 1024000KB , FILEGROWTH = 512000KB )'
--执行脚本
exec(@s)
set @i=@i+1
end

--修改日志文件 ,数据文件的初始配置
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'tempdev', SIZE = 102400KB , FILEGROWTH = 102400KB)
GO
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'templog', SIZE = 1024000KB , FILEGROWTH = 102400KB)
GO

参考:SQL Server中的高可用性(2)—-文件与文件组

2013八月2

磁盘空间查看

sql server 评论关闭

Method 1:

EXEC MASTER..xp_fixeddrives

GO

 

Method 2:

SELECT DISTINCT DB_NAME(dovs.database_idDBName,

mf.physical_name PhysicalFileLocation,

dovs.logical_volume_name AS LogicalName,

dovs.volume_mount_point AS Drive,

CONVERT(INT,dovs.available_bytes/1048576.0AS FreeSpaceInMB

FROM sys.master_files mf

CROSS APPLY sys.dm_os_volume_stats(mf.database_idmf.FILE_IDdovs

ORDER BY FreeSpaceInMB ASC

GO

引自:SQL SERVER – Disk Space Monitoring – Detecting Low Disk Space on Server

2013七月25

字符串分解

sql server 评论关闭
DECLARE @s VARCHAR(50)
SET @s = '42:1:550'

SELECT PARSENAME(replace(@s,':','.'),3),PARSENAME(replace(@s,':','.'),2),PARSENAME(replace(@s,':','.'),1)
//PARSENAME:返回指定位置的对象,对象以.分开
2013七月12

存储过程加解密

sql server 评论关闭
 
加密
ALTER  PROCEDURE [dbo].[MyProce]
(
@TranType VARCHAR(50)
)
with encryption
AS
IF @TranType='select'
BEGIN
SELECT '111'
END
RETURN

解密

--exec dbo.sp_windbi$decrypt 'MyProce',0 

Create PROCEDURE [dbo].[sp_windbi$decrypt]
(@procedure sysname = NULL, @revfl int = 1)
AS
/**//*
王成辉翻译整理,转贴请注明出自微软BI开拓者www.windbi.com
调用形式为:
exec dbo.sp__windbi$decrypt @procedure,0
如果第二个参数使用1的话,会给出该存储过程的一些提示。
--版本4.0  修正存储过程过长解密出来是空白的问题
*/
SET NOCOUNT ON
IF @revfl = 1
BEGIN
PRINT '警告:该存储过程会删除并重建原始的存储过程。'
PRINT ' 在运行该存储过程之前确保你的数据库有一个备份。'
PRINT ' 该存储过程通常应该运行在产品环境的一个备份的非产品环境下。'
PRINT ' 为了运行这个存储过程,将参数@refl的值更改为0。'
RETURN 0
END
DECLARE @intProcSpace bigint, @t bigint, @maxColID smallint,@procNameLength int
select @maxColID = max(subobjid) FROM
sys.sysobjvalues WHERE objid = object_id(@procedure)
--select @maxColID as 'Rows in sys.sysobjvalues'
select @procNameLength = datalength(@procedure) + 29
DECLARE @real_01 nvarchar(max)
DECLARE @fake_01 nvarchar(max)
DECLARE @fake_encrypt_01 nvarchar(max)
DECLARE @real_decrypt_01 nvarchar(max),@real_decrypt_01a nvarchar(max)
declare @objtype varchar(2),@ParentName nvarchar(max)
select @real_decrypt_01a = ''
--提取对象的类型如是存储过程还是函数,如果是触发器,还要得到其父对象的名称
select @objtype=type,@parentname=object_name(parent_object_id)
from sys.objects where [object_id]=object_id(@procedure)
-- 从sys.sysobjvalues里提出加密的imageval记录
SET @real_01=(SELECT top 1 imageval FROM sys.sysobjvalues WHERE objid =
object_id(@procedure) and valclass = 1 order by subobjid)  

--创建一个临时表
create table #output ( [ident] [int] IDENTITY (1, 1) NOT NULL ,
[real_decrypt] NVARCHAR(MAX) )
--开始一个事务,稍后回滚
BEGIN TRAN
--更改原始的存储过程,用短横线替换
if @objtype='P'
  SET @fake_01='ALTER PROCEDURE '+ @procedure +' WITH ENCRYPTION AS select 1
  /**//*'+REPLICATE(cast('*'as nvarchar(max)), datalength(@real_01) /2 - @procNameLength)+'*/'
else if @objtype='FN'
  SET @fake_01='ALTER FUNCTION '+ @procedure +'() RETURNS INT WITH ENCRYPTION AS BEGIN RETURN 1
  /**//*'+REPLICATE(cast('*'as nvarchar(max)), datalength(@real_01) /2 - @procNameLength)+'*/ END'
else if @objtype='V'
  SET @fake_01='ALTER view '+ @procedure +' WITH ENCRYPTION AS select 1 as col
  /**//*'+REPLICATE(cast('*'as nvarchar(max)), datalength(@real_01) /2 - @procNameLength)+'*/'
else if @objtype='TR'
  SET @fake_01='ALTER trigger '+ @procedure +' ON '+@parentname+'WITH ENCRYPTION AFTER INSERT AS RAISERROR (''N'',16,10)
  /**//*'+REPLICATE(cast('*'as nvarchar(max)), datalength(@real_01) /2 - @procNameLength)+'*/'
EXECUTE (@fake_01)
--从sys.sysobjvalues里提出加密的假的
SET @fake_encrypt_01=(SELECT top 1 imageval FROM sys.sysobjvalues WHERE objid =
object_id(@procedure) and valclass = 1 order by subobjid )
if @objtype='P'
  SET @fake_01='Create PROCEDURE '+ @procedure +' WITH ENCRYPTION AS select 1
  /**//*'+REPLICATE(cast('*'as nvarchar(max)), datalength(@real_01) /2 - @procNameLength)+'*/'
else if @objtype='FN'
  SET @fake_01='CREATE FUNCTION '+ @procedure +'() RETURNS INT WITH ENCRYPTION AS BEGIN RETURN 1
  /**//*'+REPLICATE(cast('*'as nvarchar(max)), datalength(@real_01) /2 - @procNameLength)+'*/ END'
else if @objtype='V'
  SET @fake_01='Create view '+ @procedure +' WITH ENCRYPTION AS select 1 as col
  /**//*'+REPLICATE(cast('*'as nvarchar(max)), datalength(@real_01) /2 - @procNameLength)+'*/'
else if @objtype='TR'
  SET @fake_01='Create trigger '+ @procedure +' ON '+@parentname+'WITH ENCRYPTION AFTER INSERT AS RAISERROR (''N'',16,10)
  /**//*'+REPLICATE(cast('*'as nvarchar(max)), datalength(@real_01) /2 - @procNameLength)+'*/'
--开始计数
SET @intProcSpace=1
--使用字符填充临时变量
SET @real_decrypt_01 = replicate(cast('A'as nvarchar(max)), (datalength(@real_01) /2 ))
--循环设置每一个变量,创建真正的变量
--每次一个字节
SET @intProcSpace=1
--如有必要,遍历每个@real_xx变量并解密
WHILE @intProcSpace<=(datalength(@real_01)/2)
BEGIN
--真的和假的和加密的假的进行异或处理
SET @real_decrypt_01 = stuff(@real_decrypt_01, @intProcSpace, 1,
NCHAR(UNICODE(substring(@real_01, @intProcSpace, 1)) ^
(UNICODE(substring(@fake_01, @intProcSpace, 1)) ^
UNICODE(substring(@fake_encrypt_01, @intProcSpace, 1)))))
SET @intProcSpace=@intProcSpace+1
END  

--通过sp_helptext逻辑向表#output里插入变量
insert #output (real_decrypt) select @real_decrypt_01
--select real_decrypt AS '#output chek' from #output --测试
-- -------------------------------------
--开始从sp_helptext提取
-- -------------------------------------
declare @dbname sysname
,@BlankSpaceAdded int
,@BasePos int
,@CurrentPos int
,@TextLength int
,@LineId int
,@AddOnLen int
,@LFCR int --回车换行的长度
,@DefinedLength int
,@SyscomText nvarchar(max)
,@Line nvarchar(255)
Select @DefinedLength = 255
SELECT @BlankSpaceAdded = 0 --跟踪行结束的空格。注意Len函数忽略了多余的空格
CREATE TABLE #CommentText
(LineId int
,Text nvarchar(255) collate database_default)
--使用#output代替sys.sysobjvalues
DECLARE ms_crs_syscom CURSOR LOCAL
FOR SELECT real_decrypt from #output
ORDER BY ident
FOR READ ONLY
--获取文本
SELECT @LFCR = 2
SELECT @LineId = 1
OPEN ms_crs_syscom
FETCH NEXT FROM ms_crs_syscom into @SyscomText
WHILE @@fetch_status >= 0
BEGIN
SELECT @BasePos = 1
SELECT @CurrentPos = 1
SELECT @TextLength = LEN(@SyscomText)
WHILE @CurrentPos != 0
BEGIN
--通过回车查找行的结束
SELECT @CurrentPos = CHARINDEX(char(13)+char(10), @SyscomText,
@BasePos)
--如果找到回车
IF @CurrentPos != 0
BEGIN
--如果@Lines的长度的新值比设置的大就插入@Lines目前的内容并继续
While (isnull(LEN(@Line),0) + @BlankSpaceAdded +
@CurrentPos-@BasePos + @LFCR) > @DefinedLength
BEGIN
SELECT @AddOnLen = @DefinedLength-(isnull(LEN(@Line),0) +
@BlankSpaceAdded)
INSERT #CommentText VALUES
( @LineId,
isnull(@Line, N'') + isnull(SUBSTRING(@SyscomText,
@BasePos, @AddOnLen), N''))
SELECT @Line = NULL, @LineId = @LineId + 1,
@BasePos = @BasePos + @AddOnLen, @BlankSpaceAdded = 0
END
SELECT @Line = isnull(@Line, N'') +
isnull(SUBSTRING(@SyscomText, @BasePos, @CurrentPos-@BasePos + @LFCR), N'')
SELECT @BasePos = @CurrentPos+2
INSERT #CommentText VALUES( @LineId, @Line )
SELECT @LineId = @LineId + 1
SELECT @Line = NULL
END
ELSE
--如果回车没找到
BEGIN
IF @BasePos <= @TextLength
BEGIN
--如果@Lines长度的新值大于定义的长度
While (isnull(LEN(@Line),0) + @BlankSpaceAdded +
@TextLength-@BasePos+1 ) > @DefinedLength
BEGIN
SELECT @AddOnLen = @DefinedLength -
(isnull(LEN(@Line),0) + @BlankSpaceAdded)
INSERT #CommentText VALUES
( @LineId,
isnull(@Line, N'') + isnull(SUBSTRING(@SyscomText,
@BasePos, @AddOnLen), N''))
SELECT @Line = NULL, @LineId = @LineId + 1,
@BasePos = @BasePos + @AddOnLen, @BlankSpaceAdded =
0
END
SELECT @Line = isnull(@Line, N'') +
isnull(SUBSTRING(@SyscomText, @BasePos, @TextLength-@BasePos+1 ), N'')
if LEN(@Line) < @DefinedLength and charindex(' ',
@SyscomText, @TextLength+1 ) > 0
BEGIN
SELECT @Line = @Line + ' ', @BlankSpaceAdded = 1
END
END
END
END
FETCH NEXT FROM ms_crs_syscom into @SyscomText
END
IF @Line is NOT NULL
INSERT #CommentText VALUES( @LineId, @Line )
select Text from #CommentText order by LineId
CLOSE ms_crs_syscom
DEALLOCATE ms_crs_syscom
DROP TABLE #CommentText
-- -------------------------------------
--结束从sp_helptext提取
-- -------------------------------------
--删除用短横线创建的存储过程并重建原始的存储过程
ROLLBACK TRAN
DROP TABLE #output
go
2013七月2

从wait角度调优

sql server 评论关闭
WITH [Waits] AS
    (SELECT
        [wait_type],
        [wait_time_ms] / 1000.0 AS [WaitS],
        ([wait_time_ms] – [signal_wait_time_ms] ) / 1000.0 AS [ResourceS],
        [signal_wait_time_ms] / 1000.0 AS [SignalS],
        [waiting_tasks_count] AS [WaitCount],
        100.0 * [wait_time_ms] / SUM ( [wait_time_ms]) OVER() AS [Percentage],
        ROW_NUMBER() OVER(ORDER BY [wait_time_ms] DESC ) AS [RowNum]
    FROM sys.dm_os_wait_stats
    WHERE [wait_type] NOT IN (
        N'CLR_SEMAPHORE',    N'LAZYWRITER_SLEEP',
        N'RESOURCE_QUEUE',   N'SQLTRACE_BUFFER_FLUSH',
        N'SLEEP_TASK',       N'SLEEP_SYSTEMTASK',
        N'WAITFOR',          N'HADR_FILESTREAM_IOMGR_IOCOMPLETION',
        N'CHECKPOINT_QUEUE', N'REQUEST_FOR_DEADLOCK_SEARCH',
        N'XE_TIMER_EVENT',   N'XE_DISPATCHER_JOIN',
        N'LOGMGR_QUEUE',     N'FT_IFTS_SCHEDULER_IDLE_WAIT',
        N'BROKER_TASK_STOP', N'CLR_MANUAL_EVENT',
        N'CLR_AUTO_EVENT',   N'DISPATCHER_QUEUE_SEMAPHORE',
        N'TRACEWRITE',       N'XE_DISPATCHER_WAIT',
        N'BROKER_TO_FLUSH',  N'BROKER_EVENTHANDLER',
        N'FT_IFTSHC_MUTEX',  N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
        N'DIRTY_PAGE_POLL', &nbsp; N'SP_SERVER_DIAGNOSTICS_SLEEP')
    )
SELECT
    [W1]. [wait_type] AS [WaitType],
    CAST ([W1]. [WaitS] AS DECIMAL( 14, 2 )) AS [Wait_S],
    CAST ([W1]. [ResourceS] AS DECIMAL( 14, 2 )) AS [Resource_S],
    CAST ([W1]. [SignalS] AS DECIMAL( 14, 2 )) AS [Signal_S],
    [W1]. [WaitCount] AS [WaitCount],
    CAST ([W1]. [Percentage] AS DECIMAL( 4, 2 )) AS [Percentage],
    CAST (([W1]. [WaitS] / [W1]. [WaitCount]) AS DECIMAL (14, 4)) AS [AvgWait_S],
    CAST (([W1]. [ResourceS] / [W1]. [WaitCount]) AS DECIMAL (14, 4)) AS [AvgRes_S],
    CAST (([W1]. [SignalS] / [W1]. [WaitCount]) AS DECIMAL (14, 4)) AS [AvgSig_S]
FROM [Waits] AS [W1]
INNER JOIN [Waits] AS [W2]
    ON [W2].[RowNum] <= [W1].[RowNum]
GROUP BY [W1]. [RowNum], [W1].[wait_type] , [W1] .[WaitS],
    [W1]. [ResourceS], [W1].[SignalS] , [W1] .[WaitCount], [W1].[Percentage]
HAVING SUM ([W2] .[Percentage]) – [W1].[Percentage] < 95 ; — percentage threshold
GO
 
you can very easily come up with a way to persist the results every few hours or every day and do some time-series analysis to figure out trends or automatically spot problems as they start to happen. You can also use Performance Dashboard to see these graphically in 2005 and Data Collector in 2008. On SQL Server 2000 you can use DBCC SQLPERF (N’waitstats’).
 
这个sql可以用来产看95%以上的等待。

–查看等待类型对应的sql

if exists (select * from sys.objects where object_id = object_id(N'[dbo].[get_statements_from_waiter_list]') and OBJECTPROPERTY(object_id, N'IsProcedure') = 1)
    drop procedure [dbo].[get_statements_from_waiter_list]
go 

create proc get_statements_from_waiter_list (@wait_type nvarchar(60)=NULL)
as
select
        r.wait_type
        ,r.wait_time
        ,SUBSTRING(qt.text,r.statement_start_offset/2,
            (case when r.statement_end_offset = -1
            then len(convert(nvarchar(max), qt.text)) * 2
            else r.statement_end_offset end -r.statement_start_offset)/2)
        as query_text
        ,qt.dbid, dbname=db_name(qt.dbid)
        ,qt.objectid
        ,r.sql_handle
        ,r.plan_handle
FROM sys.dm_exec_requests r
cross apply sys.dm_exec_sql_text(r.sql_handle) as qt
where r.session_id > 50
  and r.wait_type = isnull(upper(@wait_type),r.wait_type)
go 

exec get_statements_from_waiter_list
DBCC SQLPERF (N'sys.dm_os_wait_stats' , CLEAR );用来清空等待信息
作者对经常碰到的等待类型做出了解释:
CXPACKET:在并发查询中,某个线程等待其他线程完成时出现。可以使用cost threshold for parallelism,max degree of parallelism2个参数的配置,或者设置资源调控器来减少等待的发送,但往往不是解决问题的根本方法。
这意味着
•发生了并行操作
•发生了并行执行,或是并行执行中的一个worker被阻塞
不要望文生义
•不要将服务器级别的MAXDOP设置为1,也就是禁用并行
当您配置的 MAXDOP 值时,请遵循以下准则。

SQL Server 2005 及更高版本

  • 对于使用超过八个处理器的服务器,请使用下列配置:

    MAXDOP = 8
  • 对于使用 8 个或更少的处理器的服务器,请使用下列配置:

    MAXDOP = 0 到N



    注意在此配置中, N表示处理器的数目。
  • 对于具有 NUMA 配置的服务器,MAXDOP 不应超过分配给每个 NUMA 节点的 Cpu 的数量。
  • 对于已启用超线程的服务器,MAXDOP 值不应超过物理处理器的数量。
  • 对于服务器具有 NUMA 配置和启用超线程后,MAXDOP 值不应超过每个 NUMA 节点的物理处理器的数目。
 
更多分析症状和解决方案-PAGEIOLATCH_XX
•是否存在PAGEIOLATCH_SH等待,这意味着大范围SCAN
•同时也观察一下ACCESS_METHODS_DATASET_PARENTLatch和ACCESS_METHODS_SCAN_RANGE_GENERATOR LATCH
•检查导致CXPACKET的请求来查看执行计划是否合理
•其中某个并行线程执行时间过长(也就是其中某个线程不是由于CXPACKET阻塞)
可能的原因
•仅仅是由于发生了并行
•由于缺少聚集索引或是不准确的执行计划导致扫描
•过期的统计信息
•Distinct结果集无法预估执行计划,导致不合适执行计划,从而产生CXPACKET等待,解决办法是临时表(王成辉)
如果的确是问题
•确保统计信息是最新的,并且存在适当的索引
•设置查询的MAXDOP
•考虑MAXDOP=NUMA的物理CPU核数
•在考虑到负载类型(混合)的前提下,再设置实例的MAXDOP
•考虑设置将”cost threshold parallelism”的值设置的更高
 
 
PAGEIOLATCH_XX:从磁盘读入到内存时发送,不一定是io问题,可能是执行计划问题。或者内存压力问题。
这意味着:
•等待页由磁盘被读取到
•最常见的是SH和EX
•SH意味着页被用于读取
•EX意味着页会被修改
避免望文生义
•不要直接判断是IO系统和IO通道的问题
更多分析
•决定哪个表/索引被读取(通过DBCC Page)
•使用sys.dm_io_virtual_file_stats和Avg Disk Secs/Read性能计数器判断IO
•对应的CXPACKET等待,是否存在并行扫描
•通过执行计划查看并行扫描
•通过执行计划查看是否存在隐式转换(可能导致扫描)
•通过Page Life Expectancy查看是否存在缓存区内存压力
创建非聚集索引来减少扫描
更新统计信息
将受影响的数据转移到更快的IO子系统
考虑增加内存
 
 
ASYNC_NETWORK_IO:通常在sql server等待客户端取走数据时发送,客户端生产大量数据,导致取数据很慢,往往是程序设计不合理造成。
这意味着
•SQL Server等待客户端获取数据的ACK反馈
避免望文生义
•不要简单认为是网络延迟
•只有再考虑其他所有因素之后,再考虑是不是网络延迟
更多分析
•分析客户端程序
•分析网络延迟
解决方案
•客户端程序RBAR(Row-By-Agonizing-Row)
•分析网络硬件,TCP配置等
 
 
WRITELOG:日志管理系统等待日志刷新到磁盘时发送。往往说明io子系统的问题,1.把符合分散到多个数据库上或者缩小长事务。可以使用sys.dm_io_virtual_file_stats检查日志的io问题
这意味着
•等待将日志块flush到日志
避免望文生义
•不要一开始就以为是IO问题
•不要直接增加日志文件
更多分析
•查看sys.dm_io_virtual_file_stats
•查看LOGBUFFER等待,看是否存在对日志缓冲区的争抢
•查看日志所在磁盘的磁盘等待队列
•查看事务的平均大小
•查看是否有大量的页分裂(页分裂会导致大量日志)
将日志转移到更快的IO系统(一定要和数据分开)
增加事务的大小来避免大量日志写入(比如说批量写入)
删除没用的非聚集索引,来避免日志开销
修改索引键或使用填充哎减少页分裂
修改程序架构,将负载分布到多个数据库或服务器
 
MSQL_XP: sql server等待扩展存储过程完成时发送,检查扩展存储过程代码
LCK_M_XX:线程等待锁的分配,说明线程堵塞
这意味着:
•由于另一个线程对某个资源加锁,该线程不能对资源加不兼容的锁
避免望文生义
•不要以为锁是Root Cause
更多分析
•通过sys.dm_os_waiting_tasks来找到最开始被阻塞的线程,而阻塞该线程的原因可能是IO、网络、内存等
•使用阻塞进程报表来捕捉等待信息
解决方案基于最开始被阻塞进程的等待类型
一个查范围更新或扫描造成的锁升级
•如果可能,使用分区锁
•尝试创建索引,使得扫描变为非聚集索引查找
•将大批量更新事务分解成多个小事务
•尝试使用不同的隔离等级或是快照隔离
•避免不必要的锁
读写不应该互相阻塞,可以尝试修改隔离等级或使用乐观并发
其它阻止事务释放锁的情况,寻找基本原因
 
IO_COMPLETION:等待io完成时出现,往往说明io问题
SOS_SCHEDULER_YIELD:在等待spinlock时发现可能会浪费很多cpu因此,线程确定自动让出cpu
这意味着
•线程用完4毫秒的时间片,主动放弃CPU
•存在旋锁
避免望文生义
•不一定是CPU问题(CPU问题往往体现在长Runnable队列或大量signal wait)
更多分析
•通过执行计划查看是否存在大量扫描
•查看等待类型
注意:该方式没有Resource_wait等待类型,因此一些查询等待类型的语句可能无法捕获
•无法在sys.dm_os_waiting_tasks中看到
 
PAGELATCH_XX:在访问page时出现(buf闩)的等待。可能是热点页,GAM,SGAM,PFS可能会引起这个问题
这意味着
•等待访问内存中的数据文件页
•常见的是SH和EX
•SH意味着页将被读取
•EX意味着页会被修改
避免望文生义
•不要额PAGEIOLATCH_XX混淆
•不意味着需要增加IO和内存
更多分析
•找出等待页的类型
 
 
SELECT TOP 50 * 
FROM sys.dm_os_waiting_tasks
 
SELECT wt.session_id, wt.wait_type, wt.wait_duration_ms
, s.name AS schema_name
, o.name AS object_name
, i.name AS index_name
FROM sys.dm_os_buffer_descriptors bd
JOIN (
    SELECT  session_id, wait_type,wait_duration_ms,resource_description,
PARSENAME(replace(resource_description,':','.'),1) database_id,
PARSENAME(replace(resource_description,':','.'),2) file_id,PARSENAME(replace(resource_description,':','.'),3)page_id
    FROM sys.dm_os_waiting_tasks
    WHERE wait_type LIKE 'PAGELATCH%'
)wt
ON bd.database_id = wt.database_id
AND bd.file_id = wt.file_id
AND bd.page_id = wt.page_id
JOIN sys.allocation_units au ON bd.allocation_unit_id = au.allocation_unit_id
JOIN sys.partitions p ON au.container_id = p.partition_id
JOIN sys.indexes i ON  p.index_id = i.index_id AND p.object_id = i.object_id
JOIN sys.objects o ON i.object_id = o.object_id
JOIN sys.schemas s ON o.schema_id = s.schema_id
 
处理方法:http://sqlcat.com/sqlcat/b/technicalnotes/archive/2009/09/22/resolving-pagelatch-contention-on-highly-concurrent-insert-workloads-part-1.aspx
 
最经典的TempDB争抢
•添加TempDB文件
•4个起,如果还有争抢,再增加4个
•启用跟踪标记1118
•减少TempDB的使用(比如说减少临时表)
•减少临时表的使用,不要显式的drop掉临时表(非BOOT页TempDB争抢)(高继伟)
过多的页分裂
•修改索引键(经典的GUID)
•避免更新太长的记录
•使用填充因子
插入递增表产生插入热点
•使用随机或组合键并结合填充因子来减少页分裂
•修改程序架构,插入分布到多个表、数据库、服务器中
 
 
 
 
BACKUPXX:
可能是
•BACKUPBUFFER
•BACKUPIO
•BACKUPTHREAD
这意味着
•等待数据或是数据的缓存
•读取数据库文件
•第三种通常是由于数据或磁盘的填0初始化
更多分析
•第一种,备份是基于慢速的的IO系统或网络,或是远程服务器的IO系统缓慢
•数据文件所在的IO系统缓慢
•会产生PREEMPTIVE_OS_WRITEFILEGATHER等待
 
OLEDB
这意味着
•使用了OLE DB机制
避免望文生义
•不要直接猜想是因为使用了链接服务器
更多分析
•等待OLE DB的查询是什么
•如果使用了链接服务器,那么什么导致了链接服务器的延时
可能的解决方案
•DBCC CHECKDB这类内部使用了OLEDB的命令
•很多DMV内部使用了OLEDB,因此可能是一些监测工具导致的问题
•低性能的链接服务器
 
 
LATCH_XX:非buf闩的等待(闩分为2种,buf闩和非buf闩,SQL Server 2008内部剖析与故障分析一书的6.6中有详细介绍)
PREEMPTIVE_XX:切换到抢占模式通过windows调度做相关操作时出现的等待
PREEMPTIVE_OS_XX
这意味着
•线程直接调用OS
•线程切换到抢占式调度模式
•线程的状态是RUNNING,而不是SUSPENDED
更多分析
•SQL Server 2012中有194个该类事件
•这类事件文档非常少
•一个小技巧,在MSDN搜索PREEMPTIVE_OS_XX中的XX部分,这部分内容其实就是WINDOWS API
可能的解决方案
•要基于不同种类的等待类型来判断
 
PREEMPTIVE_OS_CREATEFILE
这意味着
•线程会调用Windows来创建文件
•如果使用了FileStream,当FileStream创建新的NTFS文件时,可能会导致该问题
更多分析
•查看不断增长的等待时间
可能的解决方案
•承载FileStream的IO性能不行
•使用FileStream的IO负载过重
•参考WIN32 API:http://msdn.microsoft.com/en-us/library/windows/desktop/aa363858(v=vs.85).aspx
 
PREEMPTIVE_OS_WRITEFILEGATHER
这意味着
•线程会调用Windows来写入文件
避免望文生义
•不要只认为是IO问题
更多分析
•正在进行的数据库操作
•比如说还原数据库,数据库文件的创建、增长和自动增长
可能的解决办法
•在还原数据库或日志增长时对日志填零初始化
•对数据文件填零初始化
•启用快速文件初始化
•在进行数据库还原时,不要删除现有的文件
•参考WIN32 API:http://msdn.microsoft.com/en-us/library/windows/desktop/aa365749(v=vs.85).aspx
 
PREEMPTIVE_OS_WRITEFILEGATHER
这意味着
•一个线程调用Windows来等待同步对象的改变
•通常和NETWORK_IO以及ASYNC_NETWORK_IO一起出现
更多分析
•按照ASYNC_NETWORK_IO处理方式处理
•查看是否存在事务日志复制
可能的解决方案
•ASYNC_NETWORK_IO
•当APP服务器和数据库服务器在同一台时,使用共享内存
•当和NETWORK_IO一起时,很可能是事务日志复制
 
PREEMPTIVE_OS_DBMIRRORXX
示例
•DBMIRROR_EVENT_QUEUE
•DBMIRROR_SEND
•DBMIRRORING_CMD
•DBMIRROR_DBR_MUTEX
这意味着
•等待镜像资源
避免望文生义
•不要仅仅直接移除镜像或选择高性能模式
更多分析
•分析DBMIRROR_DBR_MUTEX的平均等待时间
可能的原因
•如果DBMIRROR_DBR_MUTEX的等待时间过多,则可能是由于镜像的数据库过多,或太多需要镜像的内容
•可能是由于常见的系统瓶颈
 
SQLTRACE_XX
这意味着
•线程等待写入SQLTRACE文件
避免望文生义
•不一定非要停止SQLTRACE
更多分析
•使用sys.traces和sys.fn_trace_geteventinfo是否跟踪了一些非常频繁的事件
•分析跟踪文件所在所在的IO
可能的原因
•跟踪捕获了太多的事件
•行集没有快速消费结果集
•第三方产品在扫描跟踪
 
LATCH_XX
这意味着
•存在非页闩锁
更多分析
•使用sys.dm_os_latch_stats来分析哪一个闩锁等待时间过长
•和其它同时发生的等待类型结合查看
•比如说CXPACKET和LATCH_EX与ACCESS_METHODs_SCAN_RANGE_GENERATOR往往意味着存在大量扫描
可能的解决方案
•这类锁是没有文档支持的,需要自行Google
•接下来探讨几页常见的锁
•微软白皮书:http://sqlcat.com/sqlcat/b/whitepapers/archive/2011/07/05/diagnosing-and-resolving-latch-contention-on-sql-server.aspx
 
 
THREADPOOL:等待可用的workthreads
DBMIRROR_DBM_MUTEX:发送buffer时出现的等待,可能是镜像回话过多
RESOUCE_SEMAPHORE:查询语句等待分配内存时出现,可能是查询语句过大或者需求的内存过大。
MSQL_DG: sql server等待分布式查询完成时出现,说明分布式查询有问题
RESOUCE_SEMAPHORE_QUERY_COMPLIE:过大的并发编译,主要是重编译和无缓冲plan造成
MSSEARCH:全文查询等待
 
参考:Wait statistics, or please tell me where it hurtd 
waitstats,latch,spinlock相关文章
等待类型bol
 
2013七月1

merge合并与output输出

sql server 评论关闭

一、merge
1,利用merge不用再写update…insert,可以直接合并。
2,可以将更改的结果与更改前的结果输出
3,在sqlserver2008后引用。
4,源表中不能有重复的记录

参考:
MERGE (Transact-SQL)
SQL Server中的Merge关键字

二、output,在插入,更新,删除时记录

DELETE Sales.ShoppingCartItem
OUTPUT DELETED.*
WHERE ShoppingCartID = 20621;

OUTPUT 子句 (Transact-SQL)

2013六月19

修改SQLServer默认端口

sql server 评论关闭
修改SQLServer默认端口
1,在TCP/IP协议属性的全部侦听为“是”时,修改IPALL的端口即可
2,在TCP/IP协议属性的全部侦听为“否”时,单独设置每个IP的端口
 
默认情况下:
TCP/IP协议属性的全部侦听值为“是”
意为:在IP地址的配置中,由IPALL统管一切,
IP地址中的TCP端口、活动、启用都是无效的,只要配置上IP,都会启用的,端口是IPALL配置的端口
即:IP地址中只配置IP地址。
 
当将TCP/IP协议属性的全部侦听值为“否”时
在IP地址的配置中,自己配置自己的,决定自己的端口,是否启用等。
 
注:SQL Server 数据库引擎可以侦听同一 IP 地址的多个端口,端口以逗号分隔的格式列出:1433,1500,1501。本字段最多允许 2047 个字符。
若要配置单个 IP 地址以侦听多个端口,还必须将“TCP/IP 属性”对话框的“协议”选项卡上的“全部侦听”参数设置为“否”。
 
静态端口与动态端口
SQL Server 的默认实例侦听端口 1433 的传入连接。可以出于安全性原因或根据客户端应用程序的请求来更改该端口。
默认情况下,命名实例(包含 SQL Server Express)被配置为侦听动态端口。若要配置静态端口,请将“TCP 动态端口”框保留为空,并在“TCP 端口”框中提供一个可用的端口号。
 
动态端口

如果未启用动态端口,则为空。若要使用动态端口,请设置为 0。
对于“IPAll”,将显示所用动态端口的端口号。
如果某个 SQL Server 实例已配置为侦听动态端口,则在启动时,该实例将检查操作系统中的可用端口,并为该端口打开一个端点。传入连接必须指定要连接的端口号。
由于每次启动 SQL Server 时端口号都可能会改变,因此 SQL Server 提供 SQL Server Browser 服务监视端口,
并将传入连接指向该实例的当前端口。使用动态端口会增加通过防火墙连接 SQL Server 的复杂性,因为重新启动 SQL Server 时端口号可能会改变,从而需要更改防火墙设置。
若要避免通过防火墙连接的问题,请将 SQL Server 配置为使用静态端口。