2013年7月 的存档
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七月5

jQuery

参考:jQuery设计思想
对于网页开发者来说,学会jQuery是必要的。因为它让你了解业界最通用的技术,为将来学习更高级的库打下基础,并且确实可以很轻松地做出许多复杂的效果。

虽然jQuery上手简单,比其他库容易学会,但是要全面掌握,却不轻松。因为它涉及到网页开发的方方面面,提供的各种方法和内部变化有上千种之多。初学者常常感到,入门很方便,提高很困难。

目前,互联网上最好的jQuery入门教材,是Rebecca Murphey写的《jQuery基础》(jQuery Fundamentals)。在Google里搜索"jQuery 培训",此书排在第一位。jQuery官方团队已经同意,把此书作为官方教程的基础。

这本书虽然是入门教材,但也足足有100多页。我对它做了一个详细的笔记,试图理清jQuery的设计思想,找出学习的脉络。我的目标是全面掌握jQuery,遇到问题的时候,心里有底,基本知道使用它的哪一个功能,然后可以迅速从手册中找到具体的写法。

下面就是我的笔记,它应该是目前网上不多的jQuery中文教程之一。你只需要一点javascript语言的基本知识,就能看懂它,在最短的时间里,掌握jQuery的所有主要方面(除了ajax插件开发)。

===========================================

jQuery设计思想

原文网址:http://jqfundamentals.com/book/

【目录】

  一、选择网页元素

  二、改变结果集

  三、链式操作

  四、元素的操作:取值和赋值

  五、元素的操作:移动

  六、元素的操作:复制、删除和创建

  七、工具方法

  八、事件操作

  九、特殊效果

【正文】

一、选择网页元素

jQuery的基本设计思想和主要用法,就是"选择某个网页元素,然后对其进行某种操作"。这是它区别于其他Javascript库的根本特点。

使用jQuery的第一步,往往就是将一个选择表达式,放进构造函数jQuery()(简写为$),然后得到被选中的元素。

选择表达式可以是CSS选择器

  $(document) //选择整个文档对象



  $('#myId') //选择ID为myId的网页元素



  $('div.myClass') // 选择class为myClass的div元素



  $('input[name=first]') // 选择name属性等于first的input元素

也可以是jQuery特有的表达式

  $('a:first') //选择网页中第一个a元素



  $('tr:odd') //选择表格的奇数行



  $('#myForm :input') // 选择表单中的input元素



  $('div:visible') //选择可见的div元素



  $('div:gt(2)') // 选择所有的div元素,除了前三个



  $('div:animated') // 选择当前处于动画状态的div元素

二、改变结果集

jQuery设计思想之二,就是提供各种强大的过滤器,对结果集进行筛选,缩小选择结果。

  $('div').has('p'); // 选择包含p元素的div元素

  $('div').not('.myClass'); //选择class不等于myClass的div元素

  $('div').filter('.myClass'); //选择class等于myClass的div元素

  $('div').first(); //选择第1个div元素

  $('div').eq(5); //选择第6个div元素

有时候,我们需要从结果集出发,移动到附近的相关元素,jQuery也提供了在DOM树上的移动方法

  $('div').next('p'); //选择div元素后面的第一个p元素

  $('div').parent(); //选择div元素的父元素

  $('div').closest('form'); //选择离div最近的那个form父元素

  $('div').children(); //选择div的所有子元素

  $('div').siblings(); //选择div的同级元素

三、链式操作

jQuery设计思想之三,就是最终选中网页元素以后,可以对它进行一系列操作,并且所有操作可以连接在一起,以链条的形式写出来,比如:

  $('div').find('h3').eq(2).html('Hello');

分解开来,就是下面这样:

  $('div') //找到div元素

   .find('h3') //选择其中的h3元素

   .eq(2) //选择第3个h3元素

   .html('Hello'); //将它的内容改为Hello

这是jQuery最令人称道、最方便的特点。它的原理在于每一步的jQuery操作,返回的都是一个jQuery对象,所以不同操作可以连在一起。

jQuery还提供了.end()方法,使得结果集可以后退一步:

  $('div')

   .find('h3')

   .eq(2)

   .html('Hello')

   .end() //退回到选中所有的h3元素的那一步

   .eq(0) //选中第一个h3元素

   .html('World'); //将它的内容改为World

四、元素的操作:取值和赋值

操作网页元素,最常见的需求是取得它们的值,或者对它们进行赋值。

jQuery设计思想之四,就是使用同一个函数,来完成取值(getter)和赋值(setter),即"取值器"与"赋值器"合一。到底是取值还是赋值,由函数的参数决定。

  $('h1').html(); //html()没有参数,表示取出h1的值

  $('h1').html('Hello'); //html()有参数Hello,表示对h1进行赋值

常见的取值和赋值函数如下:

  .html() 取出或设置html内容

  .text() 取出或设置text内容

  .attr() 取出或设置某个属性的值

  .width() 取出或设置某个元素的宽度

  .height() 取出或设置某个元素的高度

  .val() 取出某个表单元素的值

需要注意的是,如果结果集包含多个元素,那么赋值的时候,将对其中所有的元素赋值;取值的时候,则是只取出第一个元素的值(.text()例外,它取出所有元素的text内容)。

五、元素的操作:移动

jQuery设计思想之五,就是提供两组方法,来操作元素在网页中的位置移动。一组方法是直接移动该元素,另一组方法是移动其他元素,使得目标元素达到我们想要的位置。

假定我们选中了一个div元素,需要把它移动到p元素后面。

第一种方法是使用.insertAfter(),把div元素移动p元素后面:

  $('div').insertAfter($('p'));

第二种方法是使用.after(),把p元素加到div元素前面:

  $('p').after($('div'));

表面上看,这两种方法的效果是一样的,唯一的不同似乎只是操作视角的不同。但是实际上,它们有一个重大差别,那就是返回的元素不一样。第一种方法返回div元素,第二种方法返回p元素。你可以根据需要,选择到底使用哪一种方法。

使用这种模式的操作方法,一共有四对:

  .insertAfter().after():在现存元素的外部,从后面插入元素

  .insertBefore().before():在现存元素的外部,从前面插入元素

  .appendTo().append():在现存元素的内部,从后面插入元素

  .prependTo().prepend():在现存元素的内部,从前面插入元素

六、元素的操作:复制、删除和创建

除了元素的位置移动之外,jQuery还提供其他几种操作元素的重要方法。

复制元素使用.clone()

删除元素使用.remove().detach()。两者的区别在于,前者不保留被删除元素的事件,后者保留,有利于重新插入文档时使用。

清空元素内容(但是不删除该元素)使用.empty()

创建新元素的方法非常简单,只要把新元素直接传入jQuery的构造函数就行了:

  $('<p>Hello</p>');

  $('<li class="new">new list item</li>');

  $('ul').append('<li>list item</li>');

七、工具方法

jQuery设计思想之六:除了对选中的元素进行操作以外,还提供一些与元素无关的工具方法(utility)。不必选中元素,就可以直接使用这些方法。

如果你懂得Javascript语言的继承原理,那么就能理解工具方法的实质。它是定义在jQuery构造函数上的方法,即jQuery.method(),所以可以直接使用。而那些操作元素的方法,是定义在构造函数的prototype对象上的方法,即jQuery.prototype.method(),所以必须生成实例(即选中元素)后使用。如果不理解这种区别,问题也不大,只要把工具方法理解成,是像javascript原生函数那样,可以直接使用的方法就行了。

常用的工具方法有以下几种:

  $.trim() 去除字符串两端的空格。

  $.each() 遍历一个数组或对象。

  $.inArray() 返回一个值在数组中的索引位置。如果该值不在数组中,则返回-1。

  $.grep() 返回数组中符合某种标准的元素。

  $.extend() 将多个对象,合并到第一个对象。

  $.makeArray() 将对象转化为数组。

  $.type() 判断对象的类别(函数对象、日期对象、数组对象、正则对象等等)。

  $.isArray() 判断某个参数是否为数组。

  $.isEmptyObject() 判断某个对象是否为空(不含有任何属性)。

  $.isFunction() 判断某个参数是否为函数。

  $.isPlainObject() 判断某个参数是否为用"{}"或"new Object"建立的对象。

  $.support() 判断浏览器是否支持某个特性。

八、事件操作

jQuery设计思想之七,就是把事件直接绑定在网页元素之上。

  $('p').click(function(){

    alert('Hello');

  });

目前,jQuery主要支持以下事件:

  .blur() 表单元素失去焦点。

  .change() 表单元素的值发生变化

  .click() 鼠标单击

  .dblclick() 鼠标双击

  .focus() 表单元素获得焦点

  .focusin() 子元素获得焦点

  .focusout() 子元素失去焦点

  .hover() 同时为mouseenter和mouseleave事件指定处理函数

  .keydown() 按下键盘(长时间按键,只返回一个事件)

  .keypress() 按下键盘(长时间按键,将返回多个事件)

  .keyup() 松开键盘

  .load() 元素加载完毕

  .mousedown() 按下鼠标

  .mouseenter() 鼠标进入(进入子元素不触发)

  .mouseleave() 鼠标离开(离开子元素不触发)

  .mousemove() 鼠标在元素内部移动

  .mouseout() 鼠标离开(离开子元素也触发)

  .mouseover() 鼠标进入(进入子元素也触发)

  .mouseup() 松开鼠标

  .ready() DOM加载完成

  .resize() 浏览器窗口的大小发生改变

  .scroll() 滚动条的位置发生变化

  .select() 用户选中文本框中的内容

  .submit() 用户递交表单

  .toggle() 根据鼠标点击的次数,依次运行多个函数

  .unload() 用户离开页面

以上这些事件在jQuery内部,都是.bind()的便捷方式。使用.bind()可以更灵活地控制事件,比如为多个事件绑定同一个函数:

  $('input').bind(

    'click change', //同时绑定click和change事件

    function() {

      alert('Hello');

    }

  );

有时,你只想让事件运行一次,这时可以使用.one()方法。

  $("p").one("click", function() {

    alert("Hello"); //只运行一次,以后的点击不会运行

  });

.unbind()用来解除事件绑定。

  $('p').unbind('click');

所有的事件处理函数,都可以接受一个事件对象(event object)作为参数,比如下面例子中的e:

  $("p").click(function(e) {

    alert(e.type); // "click"

  });

这个事件对象有一些很有用的属性和方法:

  event.pageX 事件发生时,鼠标距离网页左上角的水平距离

  event.pageY 事件发生时,鼠标距离网页左上角的垂直距离

  event.type 事件的类型(比如click)

  event.which 按下了哪一个键

  event.data 在事件对象上绑定数据,然后传入事件处理函数

  event.target 事件针对的网页元素

  event.preventDefault() 阻止事件的默认行为(比如点击链接,会自动打开新页面)

  event.stopPropagation() 停止事件向上层元素冒泡

在事件处理函数中,可以用this关键字,返回事件针对的DOM元素:

  $('a').click(function(e) {



    if ($(this).attr('href').match('evil')) { //如果确认为有害链接

      e.preventDefault(); //阻止打开

      $(this).addClass('evil'); //加上表示有害的class

    }

  });

有两种方法,可以自动触发一个事件。一种是直接使用事件函数,另一种是使用.trigger().triggerHandler()

  $('a').click();

  $('a').trigger('click');

九、特殊效果

最后,jQuery允许对象呈现某些特殊效果

  $('h1').show(); //展现一个h1标题

常用的特殊效果如下:

  .fadeIn() 淡入

  .fadeOut() 淡出

  .fadeTo() 调整透明度

  .hide() 隐藏元素

  .show() 显示元素

  .slideDown() 向下展开

  .slideUp() 向上卷起

  .slideToggle() 依次展开或卷起某个元素

  .toggle() 依次展示或隐藏某个元素

除了.show().hide(),所有其他特效的默认执行时间都是400ms(毫秒),但是你可以改变这个设置。

  $('h1').fadeIn(300); // 300毫秒内淡入

  $('h1').fadeOut('slow'); // 缓慢地淡出

在特效结束后,可以指定执行某个函数。

  $('p').fadeOut(300, function() { $(this).remove(); });

更复杂的特效,可以用.animate()自定义。

  $('div').animate(

    {

      left : "+=50", //不断右移

      opacity : 0.25 //指定透明度

    },

    300, // 持续时间

    function() { alert('done!'); } //回调函数

  );

.stop().delay()用来停止或延缓特效的执行。

$.fx.off如果设置为true,则关闭所有网页特效。

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)