SQL应用总结.docx

SQL应用总结.docx

《SQL应用总结.docx》由会员分享，可在线阅读，更多相关《SQL应用总结.docx（102页珍藏版）》请在冰豆网上搜索。

SQL应用总结

一,使用sqlprofiler跟踪sql语句

1、导入模板

将sqlprofiler跟踪模板u9tracetemplate.tdf导入到执行跟踪操作的sqlprofiler中。

将word文档中的u9tracetemplate.tdf保存成文件

从word中复制模板文件：

粘贴到文件系统中：

将模板文件导入到sqlprofiler中

打开sqlprofiler：

单击“导入模板”：

选择已经保存为文件的模板文件，单击“打开”：

提示导入成功：

模板导入完成。

2、建立跟踪

新建跟踪：

填入需要跟踪的数据库实例名或IP地址，单击连接：

选择我们导入的模板“u9tracetemplate”：

选择“保存到文件”，为跟踪文件输入文件名，单击保存：

设置跟踪文件属性：

单击“事件选择”tab页：

单击“列筛选器”，选择“DatabaseName”，输入想要跟踪的数据库名（此举主要目的是缩小跟踪的范围），点击确定：

单击运行：

默认情况是启用了“自动滚动窗口”，我们需要将其关闭，单击一下下面图标：

需要跟踪的动作完成后，单击“停止所选跟踪”：

至此，跟踪结果已经收集完毕。

本例中D盘trace_20100518.trc就是我们sqlprofiler的跟踪

二,最完整的数据库碎片整理脚本

SETNOCOUNTON;

SETQUOTED_IDENTIFIERON;

DECLARE@objectidint;

DECLARE@indexidint;

DECLARE@partitioncountbigint;

DECLARE@schemanamenvarchar（130）;

DECLARE@objectnamenvarchar（130）;

DECLARE@indexnamenvarchar（130）;

DECLARE@partitionnumbigint;

DECLARE@partitionsbigint;

DECLARE@fragfloat;

DECLARE@commandnvarchar（4000）;

--Conditionallyselecttablesandindexesfromthesys.dm_db_index_physical_statsfunction

--andconvertobjectandindexIDstonames.

SELECT

object_idASobjectid,

index_idASindexid,

partition_numberASpartitionnum,

avg_fragmentation_in_percentASfrag

INTO#work_to_do

FROMsys.dm_db_index_physical_stats（DB_ID（）,NULL,NULL,NULL,'LIMITED'）

WHEREindex_id>0

andobject_name（object_id）<>'UBF_Assemble_Page'

andobject_name（object_id）<>'UBF_Sys_ExtEnumValue';

--Declarethecursorforthelistofpartitionstobeprocessed.

DECLAREpartitionsCURSORFORSELECT*FROM#work_to_do;

--Openthecursor.

OPENpartitions;

--Loopthroughthepartitions.

WHILE（1=1）

BEGIN;

FETCHNEXT

FROMpartitions

INTO@objectid,@indexid,@partitionnum,@frag;

IF@@FETCH_STATUS<0BREAK;

SELECT@objectname=QUOTENAME（o.name）,@schemaname=QUOTENAME（s.name）

FROMsys.objectsASo

JOINsys.schemasassONs.schema_id=o.schema_id

WHEREo.object_id=@objectid;

SELECT@indexname=QUOTENAME（name）

FROMsys.indexes

WHEREobject_id=@objectidANDindex_id=@indexid;

SELECT@partitioncount=count（*）

FROMsys.partitions

WHEREobject_id=@objectidANDindex_id=@indexid;

--30isanarbitrarydecisionpointatwhichtoswitchbetweenreorganizingandrebuilding.

SET@command=N'ALTERINDEX'+@indexname+N'ON'+@schemaname+N'.'+@objectname+N'REBUILD';

IF@partitioncount>1

SET@command=@command+N'PARTITION='+CAST（@partitionnumASnvarchar（10））;

EXEC（@command）;

PRINTN'Executed:

'+@command;

END;

--Closeanddeallocatethecursor.

CLOSEpartitions;

DEALLOCATEpartitions;

--Dropthetemporarytable.

DROPTABLE#work_to_do;

GO

后期更新：

解决表有禁止锁升级限制旧脚本会报错

SETQUOTED_IDENTIFIERON

SETNOCOUNTON;

DECLARE@objectidint;

DECLARE@indexidint;

DECLARE@partitioncountbigint;

DECLARE@schemanamenvarchar（130）;

DECLARE@objectnamenvarchar（130）;

DECLARE@indexnamenvarchar（130）;

DECLARE@partitionnumbigint;

DECLARE@partitionsbigint;

DECLARE@fragfloat;

DECLARE@commandnvarchar（4000）;

--Conditionallyselecttablesandindexesfromthesys.dm_db_index_physical_statsfunction

--andconvertobjectandindexIDstonames.

SELECT

object_idASobjectid,

index_idASindexid,

partition_numberASpartitionnum,

avg_fragmentation_in_percentASfrag

INTO#work_to_do

FROMsys.dm_db_index_physical_stats（DB_ID（）,NULL,NULL,NULL,'LIMITED'）

WHEREavg_fragmentation_in_percent>10.0ANDindex_id>0

andobject_name（object_id）<>'UBF_Assemble_Page'

andobject_name（object_id）<>'UBF_Sys_ExtEnumValue';

--Declarethecursorforthelistofpartitionstobeprocessed.

DECLAREpartitionsCURSORFORSELECT*FROM#work_to_do;

--Openthecursor.

OPENpartitions;

--Loopthroughthepartitions.

WHILE（1=1）

BEGIN;

FETCHNEXT

FROMpartitions

INTO@objectid,@indexid,@partitionnum,@frag;

IF@@FETCH_STATUS<0BREAK;

SELECT@objectname=QUOTENAME（o.name）,@schemaname=QUOTENAME（s.name）

FROMsys.objectsASo

JOINsys.schemasassONs.schema_id=o.schema_id

WHEREo.object_id=@objectid;

SELECT@indexname=QUOTENAME（name）

FROMsys.indexes

WHEREobject_id=@objectidANDindex_id=@indexid;

SELECT@partitioncount=count（*）

FROMsys.partitions

WHEREobject_id=@objectidANDindex_id=@indexid;

--30isanarbitrarydecisionpointatwhichtoswitchbetweenreorganizingandrebuilding.

IF@frag<30.0

SET@command=N'ALTERINDEX'+@indexname+N'ON'+@schemaname+N'.'+@objectname+N'REORGANIZE';

IF@frag>=30.0

SET@command=N'ALTERINDEX'+@indexname+N'ON'+@schemaname+N'.'+@objectname+N'REBUILDwith（Data_Compression=page）';

IF@partitioncount>1

SET@command=@command+N'PARTITION='+CAST（@partitionnumASnvarchar（10））;

EXEC（@command）;

--PRINTN'Executed:

'+@command;

END;

--Closeanddeallocatethecursor.

CLOSEpartitions;

DEALLOCATEpartitions;

--Dropthetemporarytable.

DROPTABLE#work_to_do;

GO

三，尝试在数据库x中提取逻辑页错误，该逻辑页属于分配单元。

。

。

。

。

。

（微软BUG）

变相解决办法，还不知道根本原因（注意改动后必须重启服务才有效果。

）：

1、在SQLServer配置管理器中，单击“SQLServer服务”。

2、在右窗格中，右键单击SQLServer（<实例名>），再单击“属性”。

3、在“高级”选项卡的“启动参数”框内容的末尾，键入:

;-T4135

例如，我机器的启动参数在添加跟踪标志前：

-dC:

\ProgramFiles\MicrosoftSQLServer\MSSQL10.MSSQLSERVER\MSSQL\DATA\master.mdf;-eC:

\ProgramFiles\MicrosoftSQLServer\MSSQL10.MSSQLSERVER\MSSQL\Log\ERRORLOG;-lC:

\ProgramFiles\MicrosoftSQLServer\MSSQL10.MSSQLSERVER\MSSQL\DATA\mastlog.ldf

添加了4135跟踪标志后的内容：

-dC:

\ProgramFiles\MicrosoftSQLServer\MSSQL10.MSSQLSERVER\MSSQL\DATA\master.mdf;-eC:

\ProgramFiles\MicrosoftSQLServer\MSSQL10.MSSQLSERVER\MSSQL\Log\ERRORLOG;-lC:

\ProgramFiles\MicrosoftSQLServer\MSSQL10.MSSQLSERVER\MSSQL\DATA\mastlog.ldf;-T4135

四,死锁相关

1，死锁（SQL帮助）

在两个或多个任务中，如果每个任务锁定了其他任务试图锁定的资源，此时会造成这些任务永久阻塞，从而出现死锁。

下图清楚地显示了死锁状态，其中：

任务T1具有资源R1的锁（通过从R1指向T1的箭头指示），并请求资源R2的锁（通过从T1指向R2的箭头指示）。

任务T2具有资源R2的锁（通过从R2指向T2的箭头指示），并请求资源R1的锁（通过从T2指向R1的箭头指示）。

因为这两个任务都需要有资源可用才能继续，而这两个资源又必须等到其中一个任务继续才会释放出来，所以陷入了死锁状态。

SQLServer数据库引擎自动检测SQLServer中的死锁循环。

数据库引擎选择一个会话作为死锁牺牲品，然后终止当前事务（出现错误）来打断死锁。

2，死锁的资源（SQL帮助）

每个用户会话可能有一个或多个代表它运行的任务，其中每个任务可能获取或等待获取各种资源。

以下类型的资源可能会造成阻塞，并最终导致死锁。

锁。

等待获取资源（如对象、页、行、元数据和应用程序）的锁可能导致死锁。

例如，事务T1在行r1上有共享锁（S锁）并等待获取行r2的排他锁（X锁）。

事务T2在行r2上有共享锁（S锁）并等待获取行r1的排他锁（X锁）。

这将导致一个锁循环，其中，T1和T2都等待对方释放已锁定的资源。

工作线程。

排队等待可用工作线程的任务可能导致死锁。

如果排队等待的任务拥有阻塞所有工作线程的资源，则将导致死锁。

例如，会话S1启动事务并获取行r1的共享锁（S锁）后，进入睡眠状态。

在所有可用工作线程上运行的活动会话正尝试获取行r1的排他锁（X锁）。

因为会话S1无法获取工作线程，所以无法提交事务并释放行r1的锁。

这将导致死锁。

内存。

当并发请求等待获得内存，而当前的可用内存无法满足其需要时，可能发生死锁。

例如，两个并发查询（Q1和Q2）作为用户定义函数执行，分别获取10MB和20MB的内存。

如果每个查询需要30MB而可用总内存为20MB，则Q1和Q2必须等待对方释放内存，这将导致死锁。

并行查询执行的相关资源。

通常与交换端口关联的处理协调器、发生器或使用者线程至少包含一个不属于并行查询的进程时，可能会相互阻塞，从而导致死锁。

此外，当并行查询启动执行时，SQLServer将根据当前的工作负荷确定并行度或工作线程数。

如果系统工作负荷发生意外更改，例如，当新查询开始在服务器中运行或系统用完工作线程时，则可能发生死锁。

多个活动的结果集（MARS）资源。

这些资源用于控制在MARS下交叉执行多个活动请求（请参阅批处理执行环境和MARS）。

用户资源。

线程等待可能被用户应用程序控制的资源时，该资源将被视为外部资源或用户资源，并将按锁进行处理。

会话互斥体。

在一个会话中运行的任务是交叉的，意味着在某一给定时间只能在该会话中运行一个任务。

任务必须独占访问会话互斥体，才能运行。

事务互斥体。

在一个事务中运行的所有任务是交叉的，意味着在某一给定时间只能在该事务中运行一个任务。

任务必须独占访问事务互斥体，才能运行。

任务必须获取会话互斥体，才能在MARS下运行。

如果任务在事务下运行，则它必须获取事务互斥体。

这将确保在某一给定会话和给定事务中一次仅有一个任务处于活动状态。

获取所需互斥体后，任务就可以执行了。

任务完成或在请求过程中生成时，它将按获取的相反顺序先释放事务互斥体，然后释放会话互斥体。

但是，这些资源可能导致死锁。

在下面的代码示例中，两个任务（用户请求U1和用户请求U2）在同一会话中运行。

复制代码

U1:

Rs1=Command1.Execute（"insertsometableEXECusp_someproc"）;

U2:

Rs2=Command2.Execute（"selectcolAfromsometable"）;

用户请求U1执行的存储过程已获取会话互斥体。

如果执行该存储过程花费了很长时间，则数据库引擎会认为存储过程正在等待用户的输入。

用户等待U2的结果集时，用户请求U2正在等待会话互斥体，U1正在等待用户资源。

死锁状态的逻辑说明如下：

3，死锁检测（SQL帮助）

上面列出的所有资源均参与数据库引擎死锁检测方案。

死锁检测是由锁监视器线程执行的，该线程定期搜索数据库引擎实例的所有任务。

以下几点说明了搜索进程：

默认时间间隔为5秒。

如果锁监视器线程查找死锁，根据死锁的频率，死锁检测时间间隔将从5秒开始减小，最小为100毫秒。

如果锁监视器线程停止查找死锁，数据库引擎将两个搜索间的时间间隔增加到5秒。

如果刚刚检测到死锁，则假定必须等待锁的下一个线程正进入死锁循环。

检测到死锁后，第一对锁等待将立即触发死锁搜索，而不是等待下一个死锁检测时间间隔。

例如，如果当前时间间隔为5秒且刚刚检测到死锁，则下一个锁等待将立即触发死锁检测器。

如果锁等待是死锁的一部分，则将会立即检测它，而不是在下一个搜索期间才检测。

通常，数据库引擎仅定期执行死锁检测。

因为系统中遇到的死锁数通常很少，定期死锁检测有助于减少系统中死锁检测的开销。

锁监视器对特定线程启动死锁搜索时，会标识线程正在等待的资源。

然后，锁监视器查找特定资源的所有者，并递归地继续执行对那些线程的死锁搜索，直到找到一个循环。

用这种方式标识的循环形成一个死锁。

检测到死锁后，数据库引擎通过选择其中一个线程作为死锁牺牲品来结束死锁。

数据库引擎终止正为线程执行的当前批处理，回滚死锁牺牲品的事务并将1205错误返回到应用程序。

回滚死锁牺牲品的事务会释放事务持有的所有锁。

这将使其他线程的事务解锁，并继续运行。

1205死锁牺牲品错误将有关死锁涉及的线程和资源的信息记录在错误日志中。

默认情况下，数据库引擎选择运行回滚开销最小的事务的会话作为死锁牺牲品。

此外，用户也可以使用SETDEADLOCK_PRIORITY语句指定死锁情况下会话的优先级。

可以将DEADLOCK_PRIORITY设置为LOW、NORMAL或HIGH，也可以将其设置为范围（-10到10）间的任一整数值。

死锁优先级的默认设置为NORMAL。

如果两个会话的死锁优先级不同，则会选择优先级较低的会话作为死锁牺牲品。

如果两个会话的死锁优先级相同，则会选择回滚开销最低的事务的会话作为死锁牺牲品。

如果死锁循环中会话的死锁优先级和开销都相同，则会随机选择死锁牺牲品。

使用CLR时，死锁监视器将自动检测托管过程中访问的同步资源（监视器、读取器/编写器锁和线程联接）的死锁。

但是，死锁是通过在已选为死锁牺牲品的过程中引发异常来解决的。

因此，请务必理解异常不会自动释放牺牲品当前拥有的资源；必须显式释放资源。

用于标识死锁牺牲品的异常与异常行为一样，也会被捕获和解除。

4，死锁信息工具（SQL帮助）

为了查看死锁信息，数据库引擎提供了监视工具，分别为两个跟踪标志以及SQLServerProfiler中的死锁图形事件。

跟踪标志1204和跟踪标志1222

发生死锁时，跟踪标志1204和跟踪标志1222会返回在SQLServer2005错误日志中捕获的信息。

跟踪标志1204会报告由死锁所涉及的每个节点设置格式的死锁信息。

跟踪标志1222会设置死锁信息的格式，顺序为先按进程，然后按资源。

可以同时启用这两个跟踪标志，以获取同一个死锁事件的两种表示形式。

除了定义跟踪标志1204和1222的属性之外，下表还显示了它们之间的相似之处和不同之处。

属性

跟踪标志1204和跟踪标志1222

仅跟踪标志1204

仅跟踪标志1222

输出格式

在SQLServer2005错误日志中捕获输出。

主要针对死锁所涉及的节点。

每个节点都有一个专用部分，并且最后一部分说明死锁牺牲品。

返回采用不符合XML架构定义（XSD）架构的类XML格式的信息。

该格式有三个主要部分。

第一部分声明死锁牺牲品；第二部分说明死锁所涉及的每个进程；第三部分说明与跟踪标志1204中的节点同义的资源。

标识属性

SPID:

ECID:

。

标识并行进程中的系统进程ID线程。

项SPID:

ECID:

0（其中，将替换为SPID值）表示主线程。

项SPID:

ECID:

（其中，将替换为SPID值，大于0）表示具有相同SPID的子线程。

BatchID（对于跟踪标志1222为sbid）。

标识代码执行从中请求锁或持有锁的批处理。

当多个活动的结果集（MARS）处于禁用状态时，BatchID的值为0；当MARS处于启用状态时，活动的批处理的值在1到n之间。

如果会话中没有活动的批处理，则BatchID为0。

Mode。

指定线程所请求的、获得的或等待的特定资源的锁的类型。

模式可以为IS（意向共享）、S（共享）、U（更新）、IX（意向排他）、SIX（意向排他共享）和X（排他）。

有关详细信息，请参阅锁模式。

Line#（对于跟踪标志1222为line）。

列出发生死锁时当前批处理中正在执行的语句的行数。

InputBuf（对于跟踪标志1222为inputbuf）。

列出当前批处理中的所有语句。

Node。

表示死锁链中的项数。

Lists。

锁所有者可能属于以下列表：

GrantList。

枚举资源的当前所有者。

ConvertList。

枚举尝试将其锁转换为较高级别的当前所有者。

WaitList。

枚举对资源的当前新锁请求。

StatementType。

说明线程对其具有权限的DML语句的类型（SELECT、INSERT、UPDATE或DELETE）。

VictimResourceOwner。

指定SQLServer选