商讨SQL Server元数据(一)

by admin on 2019年9月22日

背景

在第一篇中我介绍了如何访问元数据,元数据为什么在数据库里面,以及如何使用元数据。介绍了如何查出各种数据库对象的在数据库里面的名字。第二篇,我选择了触发器的主题,因为它是一个能提供很好例子的数据库对象,并且在这个对象中能够提出问题和解决问题。

本篇我将会介绍元数据中的索引,不仅仅是因为它们本身很重要,更重要的是它们是很好的元数据类型,比如列或者分布统计,这些不是元数据中的对象。

索引对于任何关系数据库表都是必不可少的。然而,就像吐司上的黄油一样,过度使用它们可能会在数据库中产生问题。有时,可以对表进行过度索引或缺失索引,或者构建重复索引。有时问题是选择一个坏的填充因子,错误地设置ignore_dup_key选项,创建一个永远不会被使用(但必须被维护)的索引,丢失外键上的索引,或者将GUID作为主键的一部分。简而言之,任何频繁使用的数据库系统中的索引都需要定期维护和验证,而目录视图是完成这些工作的最直接的方式之一。

背景

  上一篇中,我介绍了SQL Server
允许访问数据库的元数据,为什么有元数据,如何使用元数据。这一篇中我会介绍如何进一步找到各种有价值的信息。以触发器为例,因为它们往往一起很多问题。

 

简介

  在数据库中,我们除了存储数据外,还存储了大量的元数据。它们主要的作用就是描述数据库怎么建立、配置、以及各种对象的属性等。本篇简单介绍如何使用和查询元数据,如何更有效的管理SQLServer
数据库。

  对一些有经验的数据库开发和管理人员而言,元数据是非常有价值的。下面我会介绍一下简单的原理,然后尽量用代码的方式直接说明,毕竟“talk
is cheap show me the code ”。

索引是数据库规划和系统维护的一个关键部分。它们为SQL
Server(以及任何其他的数据库系统)提供了查找数据和定位到数据物理位置的快捷方式的其他方法。通过添加正确的索引可以大大减少查询的执行时间。但是,许多设计很差的索引实际上会增加运行所花费的时间。事实上,索引正逐步成为SQL
Server中最容易令人误解的对象,因此也是最容易管理不当的对象。

SQL Server
中数据存储的基本单位是页(Page)。数据库中的数据文件(.mdf 或
.ndf)分配的磁盘空间可以从逻辑上划分成页(从 0 到 n 连续编号)。磁盘 I/O
操作在页级执行。也就是说,SQL Server
每次读取或写入数据的最少数据单位是数据页。

都有哪些索引可以查到?

让我们通过下面的简单语句来看一下都有哪些索引在你的数据库上,代码如下:

SELECT  convert(CHAR(50),object_schema_name(t.object_ID)+'.'
    +object_name(t.object_ID)) AS 'The Table', i.name AS index_name
FROM sys.indexes AS i
  INNER JOIN sys.tables t
    ON t.object_id=i.object_id
  WHERE is_hypothetical = 0 AND i.index_id <> 0;

结果如下:

图片 1

为什么要去引用sys.tables?这是因为它是确保只获得用户表的最简单方法。我们选择index_id
的values大于0,因为如果不为表创建集群索引,在sys中仍然有一个条目。索引,但它指向的是堆,不代表索引。每个表在sys中都有一行。索引值为0或1的索引。如果该表有一个聚集索引,则有一行数据且index_id值为1;如果该表是一个堆(这只是表示该表没有聚集索引的另一种方式),则会有一行的index_id值为0。此外,无论该表是否有聚集索引,每个非聚集索引都有一行,其index_id值大于1。我们过滤了的索引,这些索引是由数据库引擎优化顾问(DTA)创建的,目的仅仅是测试一个可能的索引是否有效。以防它们积累起来,最好把它们去掉。

如果你过一个多个指定的表,下面的这个查询是更为合理的,需要在上面的例子中增加对象的指定:

AND t.object_id = OBJECT_ID('Production.BillOfMaterials');

 

那么如何找到触发器的数据?

*  以sys.system_views*is表开始。让我们查询出数据库中使用触发器的信息。可以告知你当前SQL
Server版本中有什么触发器。

SELECT schema_name(schema_ID)+'.'+ name

  FROM sys.system_views WHERE name LIKE '%trigger%'

 ----------------------------------------

sys.dm_exec_trigger_stats              

sys.server_trigger_events              

sys.server_triggers                    

sys.trigger_event_types                

sys.trigger_events                     

sys.triggers                           



(6 row(s) affected)

  其中sys.triggers看起来信息很多,它又包含什么列?下面这个查询很容易查到:

 SELECT Thecol.name+ ' '+ Type_name(TheCol.system_type_id)

  + CASE WHEN TheCol.is_nullable=1 THEN ' NULL' ELSE ' NOT NULL' END as Column_Information

FROM sys.system_views AS TheView

  INNER JOIN sys.system_columns AS TheCol

    ON TheView.object_ID=TheCol.Object_ID

  WHERE  TheView.name = 'triggers'

  ORDER BY column_ID;

结果如下:

 Column_Information

----------------------------------------

name nvarchar NOT NULL

object_id int NOT NULL

parent_class tinyint NOT NULL

parent_class_desc nvarchar NULL

parent_id int NOT NULL

type char NOT NULL

type_desc nvarchar NULL

create_date datetime NOT NULL

modify_date datetime NOT NULL

is_ms_shipped bit NOT NULL

is_disabled bit NOT NULL

is_not_for_replication bit NOT NULL

is_instead_of_trigger bit NOT NULL

 

因此我们多这个信息有了更好的理解,有了一个目录的目录。这个概念有点让人头晕,但是另一方面,它也是相当简单的。我们能够查出元数据,再找个查询中,需要做的就是改变这个单词‘triggers’来查找你想要的视图名称。.

在2012及其以后版本,可以使用一个新的表值函数极大地简化上述查询,并可以避免各种连接。在下面的查询中,我们将查找sys.triggers
视图

中的列。可以使用相同的查询通过更改字符串中的对象名称来获取任何视图的定义。

 SELECT name+ ' '+ system_type_name

  + CASE WHEN is_nullable=1 THEN ' NULL' ELSE ' NOT NULL' END as Column_Information

FROM sys.dm_exec_describe_first_result_set

  ( N'SELECT * FROM sys.triggers;', NULL, 0) AS f

  ORDER BY column_ordinal;

查询结果如下:

 Column_Information

----------------------------------------

name nvarchar(128) NOT NULL

object_id int NOT NULL

parent_class tinyint NOT NULL

parent_class_desc nvarchar(60) NULL

parent_id int NOT NULL

type char(2) NOT NULL

type_desc nvarchar(60) NULL

create_date datetime NOT NULL

modify_date datetime NOT NULL

is_ms_shipped bit NOT NULL

is_disabled bit NOT NULL

is_not_for_replication bit NOT NULL

is_instead_of_trigger bit NOT NULL

 

sys.dm_exec_describe_first_result_set函数的最大优势在于你能看到任何结果的列,不仅仅是表和视图、存储过程或者贬值函数。

为了查出任何列的信息,你可以使用稍微修改的版本,只需要改变代码中的字符串’sys.triggers’即可,如下:

 Declare @TheParamater nvarchar(255)

Select @TheParamater = 'sys.triggers'

Select @TheParamater = 'SELECT * FROM ' + @TheParamater

SELECT

  name+ ' '+ system_type_name

  + CASE WHEN is_nullable=1 THEN ' NULL' ELSE ' NOT NULL' END as Column_Information

FROM sys.dm_exec_describe_first_result_set

  ( @TheParamater, NULL, 0) AS f

  ORDER BY column_ordinal;

什么是动态在线目录?

  每一个关系型数据库系统,比如SQL Server
一定要提供关于它的结构的信息,这些信息往往需要通过sql语法来查询。通常这些信息被保存在指定数据表的结构中。这意味着数据库中有两种不同的表:一是用户自定义的表和系统表或者视图(包含元数据)。从SQL
Server 2005开始,只有视图可以查询了,不能直接看到数据表了。

 图片 2

系统视图

这种系统表或者视图的结合通常参考关系型数据库理论的文献叫做作为系统目录或者数据字典。

在数据库内部,有一些系统表一直追踪数据库中发生的每一件事情。系统表存储像表、活动、列、索引等事情。这些完全符合Edgar
Codd
的关系型数据库试试的十三条准则直译。这个准则就是定义动态在线目录,它就是“关于数据的数据”,也叫作元数据。

 Edgar Codd  准则4, 描述如下:

‘The database description is represented at the logical level in the
same way as ordinary data, so that authorized users can apply the same
relational language to its interrogation as they apply to the regular
data.’

翻译:像普通数据一样,在逻辑层的数据表达了对数据库的描述,以便于授权用户能应用相同的SQL语言来查询元数据,就如同查询常规数据一样。

在SQL
Server中,可以通过系统视图或者架构视图直接访问动态在线目录,方便用户更为快捷的开发和管理数据库。

SQL Server的存储机制

区段

区段(extent)是用来为表和索引分配空间的基本存储单元。它由8个连续的8KB数据页组成,共计64KB大小。关于区段的要点包括以下两方面:

一旦区段已满,那么下一记录将要占据的空间不是记录的大小,而是整个新区段的大小。
通过预先分配空间,SQL Server节省了为每个记录分配新空间的时间。

页是特定区段中的分配单元。每个区段包含8页。页是在达到实际数据行之前所能达到的最后一个存储级别。尽管每个区段中的页数据是固定的,但每一页中的行数不是固定的,这完全取决于行的大小,而行的大小是可以变化的。可以把页看作是表行和索引行数据的容器。通常不允许行跨页。

页拆分

当页已满时,它会进行行拆分。这意味着多个新页被分配,也以为着现有页上有近半的数据被移到新页上。

在使用群集索引时,该过程会有例外。如果有一个群集索引,并且下一个插入的记录在物理上作为表中的最后一个记录,那么创建一个新页,然后将该新行添加到这个新页中,而不需要重新定位任何现有数据。在研究索引时将介绍有关拆分的内容。

索引的用途

每个表中有多少个索引,并展示他们的名字

前面的表并不特别有用,因为无法一眼看到每个表有多少索引,以及它们是什么。下面这个语句可以实现:

SELECT  convert(CHAR(20),object_schema_name(t.object_ID)+'.'
    +object_name(t.object_ID)) AS 'The_Table',
sum(CASE WHEN i.object_ID IS NULL THEN 0 ELSE 1 END) AS The_Count,
coalesce(stuff(( 
     SELECT ', '+i2.name
       FROM sys.indexes i2
       WHERE t.object_ID = i2.object_ID
       ORDER BY i2.name
     FOR XML PATH(''), TYPE).value(N'(./text())[1]',N'varchar(8000)'),1,2,''),'') AS Index_List
  FROM sys.tables AS t
  LEFT OUTER JOIN sys.indexes i
    ON t.object_id=i.object_id
      AND is_hypothetical = 0 AND i.index_id > 0 
GROUP BY t.Object_ID;

 

我在老的测试数据库上执行这个测试,对象名称比较短。

The_Table            The_Count   Index_List
-------------------- ----------- --------------------------------------------------
dbo.publishers       1           UPKCL_pubind
dbo.titles           2           titleind, UPKCL_titleidind
dbo.titleauthor      3           auidind, titleidind, UPKCL_taind
dbo.stores           1           UPK_storeid
dbo.sales            2           titleidind, UPKCL_sales
dbo.roysched         1           titleidind
dbo.discounts        0           
dbo.jobs             1           PK__jobs__6E32B6A51A14E395
dbo.pub_info         1           UPKCL_pubinfo
dbo.employee         2           employee_ind, PK_emp_id
dbo.authors          2           aunmind, UPKCL_auidind

(11 row(s) affected)

但是当然一个触发器是首先是一个对象,因此一定在sys.objects?

  在我们使用sys.triggers的信息之前,需要来重复一遍,所有的数据库对象都存在于sys.objects中,在SQL
Server 中的对象包括以下:聚合的CLR函数,check
约束,SQL标量函数,CLR标量函数,CLR表值函数,SQL内联表值函数,内部表,SQL存储过程,CLR存储过程,计划指南,主键约束,老式规则,复制过滤程序,系统基础表,同义词,序列对象,服务队列,CLR
DML
触发器,SQL表值函数,表类型,用户自定义表,唯一约束,视图和扩展存储过程等。

  触发器是对象所以基础信息一定保存在sys.objects。不走运的是,有时我们需要额外的信息,这些信息可以通过目录视图查询。这些额外数据有是什么呢?

 

  修改我们使用过的查询,来查询sys.triggers的列,这次我们会看到额外信息。这些额外列是来自于sys.objects。

 SELECT coalesce(trigger_column.name,'NOT INCLUDED') AS In_Sys_Triggers,

       coalesce(object_column.name,'NOT INCLUDED') AS In_Sys_Objects

FROM

 (SELECT Thecol.name

  FROM sys.system_views AS TheView

    INNER JOIN sys.system_columns AS TheCol

      ON TheView.object_ID=TheCol.Object_ID

  WHERE  TheView.name = 'triggers') trigger_column

FULL OUTER JOIN

 (SELECT Thecol.name

  FROM sys.system_views AS TheView

    INNER JOIN sys.system_columns AS TheCol

      ON TheView.object_ID=TheCol.Object_ID

  WHERE  TheView.name = 'objects') object_column

ON trigger_column.name=object_column.name

查询结果:

In_Sys_Triggers                In_Sys_Objects

------------------------------ ----------------------

name                           name

object_id                      object_id

NOT INCLUDED                   principal_id

NOT INCLUDED                   schema_id

NOT INCLUDED                   parent_object_id

type                           type

type_desc                      type_desc

create_date                    create_date

modify_date                    modify_date

is_ms_shipped                  is_ms_shipped

NOT INCLUDED                   is_published

NOT INCLUDED                   is_schema_published

is_not_for_replication         NOT INCLUDED

is_instead_of_trigger          NOT INCLUDED

parent_id                      NOT INCLUDED

is_disabled                    NOT INCLUDED

parent_class                   NOT INCLUDED

parent_class_desc              NOT INCLUDED

 

以上这些让我们知道在sys.triggers的额外信息,但是因为它始终是表的子对象,所以有些不相关信息是不会展示在这些指定的视图或者sys.triggers中的。现在就要带大家去继续找找这些信息。

如何获得以上信息?

因为我们不能直接访问,需要使用视图和函数来看这些信息。只能看到你权限内的数据。有更好的方法在用户数据库中使用数据定义语言(DDL),这些DDL语句包括CREATE,
DROP, ALTER, GRANT, DENY, REVOKE 和sp_rename statements
等。总有一种方法可以使用DDL来修改视图中的任何信息,即使并不总是显而易见的。

关系型数据库使用动态的系统视图中的数据描述数据库,但是目前还有没有标准化。但是有一个包含在每个数据库内的架构可以读取这些信息:就是Information
Schema

不走运的是,这个架构不足以提供足够信息,这意味着我们需要使用SQL Server
系统数据库的视图和函数来补充信息。接下来需要解释一些术语和技术,我会尽可能少的细节足以让大家轻松地理解这些示例

如图所示,如何访问元数据,及其接口

 图片 3

 

* *

理解索引

韦氏字典中将索引定义为“通常按字母顺序排列的一些指定数据(入作者、主题或者关键字)的列表(如目录信息或者著作正文的引用)”。换种简单的表述,即索引是一种能快速访问数据的方法。

索引排序规则

索引中的存储顺序取决于为数据建立的排序规则信息。可以在数据库或列级设置排列规则,因此有相当细粒度的控制级别。一旦设置了排序规则顺序,改变它是非常困难的(但也是可能的),因此在设置之前要确定所需的排序规则顺序。

平衡树(B-Tree)

平衡树仅提供了一种以一致相对低成本的方式查找特定信息的方法。其名称中的“平衡”是自说明的。平衡树是自平衡的(只有很少的例外的情况),这意味着每次树进行分支时都有接近一半的数据在一边,而另一半数据在另一边。而名称中的“树”的概念也是很清楚的,如果绘制该结构,然后倒置它,会发现该结构具有树的一般形状,因此称为树。

图片 4

平衡树

更新平衡树:页拆分简介

所有这些页在读取方面工作良好,但在插入时会有点棘手。回顾一下,“B-Tree”中的“B”表示平衡(Balanced)。而且前面提到每次遇到树种的分支时,因为每一边都大约有一半的数据,所以B-Tree是平衡的。另外,由于添加新数据到树上的方法一般可避免出现不平衡,所以B-Tree有时被认为是自平衡的。

通过将数据添加到树上,节点最终将变满,并且将需要拆分。因为在SQL
Server中一个节点相当于一个页,所以这称为页拆分。如下图所示:

图片 5

更新平衡树:页拆分

当发生页拆分时,数据自动地四处移动以保持平衡。数据的前半部分保留在旧页上,而数据的剩余部分添加到新页,这样就形成对半拆分,使得树保持平衡。

如果稍微考虑下这个拆分过程,将认识到它在拆分时增加了大量系统开销。不只是插入一页,而是进行下列操作:

  • 创建新页
  • 将行从现有页移动到新页上
  • 将新行添加到其中一页上
  • 在父节点中添加另一个记录项

但是,系统开销远不止这些。因为在进行树的排列,就可能有级联操作。创建新页时(因为拆分的缘故),需要在父节点中建立另一个记录项。在父节点中的这个及记录项在该级别也可能导致页拆分,而且整个过程会重新开始。实际上,这种可能性扩展到所有节点,甚至影响到根节点。

如果根节点拆分,那么最终实际会创建两个额外的页。由于只能有一个根节点,所以之前作为根节点的页被拆分为两页,而且成为树的新中间级别。然后创建全新的根节点,并且将有两个记录项(一个指向旧的根节点,另一个指向拆分的页)。

显然,页拆分会对系统性能产生非常负面的影响,其表现是在服务器上的处理会暂停几秒(此时页被拆分并改写)。

虽然页级的页拆分时很常见的,但是在中间节点进行页拆分却很少发生。当表增长时,索引的每一层将进行页拆分,但是,因为中间节点对于下一级节点的几个记录只有一个记录项,所以当向树的上层移动时,页拆分的数量将变得越来越少。尽管如此,对于发生在页级以上的拆分来说,在下一个较低级别上一定有一个,这意味着沿树而上的页拆分在本质上是累计的(而且严重影响性能)。

SQL Server 中的数据访问方式

从广义上讲,SQL Server检索所需数据的方法只有两种:

  • 使用表扫描:表扫描是相当直观的过程。当执行表扫描时,SQL
    Server从表的物理起点处开始,浏览表中的每一行。当发现和查询条件匹配的行,就在结果集中包含它们。
  • 使用索引:当SQL
    Server采用索引时,该过程实际上与表扫描的工作方式相类似,但是有一些捷径。在查询优化过程中,优化器查看所有可以那个的索引结构并选择最好的一个索引(这主要基于在连接和WHERE字句中所指定的信息,以及SQL
    Server在索引结构中保存的统计信息)。一旦选择了索引,SQL
    Server将在树结构中导航至与条件匹配的数据位置,并且提取它所需要的记录。区别在于,因为数据是排序的,所以查询引擎知道它何时到达正在查找的当前范围的下界。然后它可以结束查询,或者根据需要移至下一数据范围。

SQL
Server使用何种方法来执行特定查询取决于可用的索引、所需的列、使用的连接以及表的大小。

索引类型

尽管表面上在SQL
Server中有两种索引结构(群集索引和非群集索引),但是在实际撒很高,就内部而言,有3种不同的索引类型。

  • 群集索引
  • 非群集索引,该索引又包括以下两种:
    • 堆上的非群集索引
    • 群集索引上的非群集索引

物理数据的存储方式在群集索引和非群集索引中是不同的。而SQL
Server遍历平衡树以到达末端数据的方式在所有3中索引类型中也是不同的。

所有的SQL
Server索引都有叶级和非页级页。正如讨论平衡树所提到的那样,叶级是保存标志记录的“键”的级别,非页级是叶级的引导者。

索引在群集表(如果有群集索引)或者堆(用于没有群集索引)上创建。

  • 群集表:群集表示在其上具有U群集索引的任意表。它们对于表而言意味着以指定顺序物理存储数据。通过使用群集键唯一地标志独立的行,群集键即定义群集索引的列。
  • :堆是在其上没有群集索引的任意表。在这种情况下,基于行的区段、页以及行偏移量(偏移页顶部的位置)的组合创建唯一的标识符,或者成为行ID(Row
    ID,
    RID)。如果没有可用的群集键(没有群集索引),那么RID是唯一必要的内容。

** 群集索引**

群集索引对于任意给定的表而言是唯一的,每个表只能有一个群集索引。不一定要有非群集索引,但是如果查看索引类型,你会发现由于多种很显然那的原因,它正成为最常被使用的一种类型。

使群集索引变得特殊的方面是,群集索引的页级是实际的数据。也就是说,数据重新排序,按照和索引排序条件声明的相同相同物理顺序存储。这意味着,一旦到达索引的页级,就到达了数据。任何新纪录都根据其正确的物理顺序插入到群集索引中。创建新页的方式随需要插入的记录的位置而变化。

如果新纪录要插入到索引结构中间,就会发生正常的页拆分。来自旧页的后一半记录被移到新页,并且适当的时候,将新纪录插入到新页或旧页。

如果新纪录在逻辑上位于索引结构末端,那么创建新页,但是只将新纪录添加到新页,如下图。

图片 6

新纪录在逻辑上位于索引结构末端

堆上的非群集索引

页级不是数据,相反,它是一个可从中获得指向该数据的指针的级别。该指针以RID的形式出现,如同在本章前面描述的那样,这种RID由索引指向的特定行的区段、页以及偏移量构成。即使叶级不是实际的数据(相反,它具有RID),使用叶级也仅仅比使用群集索引多一个步骤。因为RID具有行的位置的全部信息。所以可以通过RID直接达到数据。

然而,不要误以为“这个额外步骤”只有少量的系统开销,并且堆上的非群集索引将和群集索引几乎一样快的运行。使用群集索引,数据在物理上是按照索引的顺序排列的。这意味着,对于一定范围的数据,当找到在其上具有数据范围起点的行时,那么很可能有其他行
在同一页上(也就是说,因为它们存储在一起,所以在物理上已几乎到达下一个记录)。使用堆,数据并为通过除索引外的其他方法链接在一起。从物理上看,绝对没有任意种类的排序。这意味着,从物理读取的角度看,系统可能不得不从整个文件中检索记录。实际上,很可能最终多次从同样的页中取出数据。SQL
Server没有方法知道它需要回到该物理位置,因为在数据之间没有链接。使用群集索引,它知道这是物理上的排序,因此仅仅通过访问页一次就完全获得数据。

图片 7

堆上的非群集索引查找

如上图所示。主要通过索引导航,但一切都按以前的方式工作。服务器从相同的根节点开始,并且遍历树,处理越来越聚焦的页,直到到达索引的叶级。这里就有了区别。采用群集索引的方式,能够正好在这里停止,而采用非群集索引的方式则需要做更多的工作。如果非群集索引是在堆上,那么只要再进入一个级别。获得来自叶级页的RID,并且定位到该RID,直到这时才可以直接获得实际的数据。

群集表上的非群集索引

和堆上的非群集索引一样,索引的非叶级节点的工作与使用群集索引时相比几乎一样。区别出现在叶级。

在叶级,与使用其他两种索引结构所看到的内容有相当明显的区别:有另外一个索引来查找。使用群集索引,当服务器达到叶级时,它可以找到实际的数据。使用堆上的非群集索引,不能找到实际的数据,但是可以找到能够直接获得数据的标识符(仅仅多了一步)。使用群集表的非群集索引,可以找到群集键。也就是说,服务器找到足够的信息来利用群集索引。

如下图。服务器首先执行范围搜索,这一点与前面相同。在索引中执行一次单独的查找,并且可以浏览非群集索引以找到满足条件(LIKE’T%’)的连续数据范围。这种能够直接到达的索引中的特定位置的查找称为seek。

图片 8

查找FName类似“T%”的EmployeeID

然后开始第二个查找,使用群集索引的查找。第二种查找非茶馆迅速:问题在于它必须执行多次。可以看到,SQL
Server从第一个索引查找中索引列表(所有名称以“T”开始的列表),但是该列表在逻辑上并没有以任意连续的方式与群集键相匹配,每个记录需要单独地查找,如下图。

![Uploading image_980906.png . . .]

  我们对数据查询及处理速度已成为衡量应用系统成败的标准,而采用索引来加快数据处理速度通常是最普遍采用的优化方法。

查找没有聚集索引的表

关于索引,您可以找到很多有趣的东西。例如,这里有一种快速查找表的方法,无需使用聚集索引(堆)

-- 展示所有没有聚集索引的表名称
SELECT  object_schema_name(sys.tables.object_id)+'.'
    +object_name(sys.tables.object_id) AS 'Heaps'
      FROM sys.indexes /* see whether the table is a heap */
      INNER JOIN sys.tables ON sys.tables.object_ID=sys.indexes.object_ID
      WHERE sys.indexes.type = 0;

触发器的问题

  触发器是有用的,但是因为它们在SSMS对象资源管理器窗格中不是可见的,所以一般用来提醒错误。触发器有时候会有些微妙的地方让其出问题,比如,当导入过程中禁用了触发器,并且由于某些原因他们没有重启。

下面是一个关于触发器的简要提醒:

  触发器可以在视图,表或者服务器上,任何这些对象上都可以有超过1个触发器。普通的DML触发器能被定义来执行替代一些数据修改(Insert,Update或者Delete)或者在数据修改之后执行。每一个触发器与只与一个对象管理。DDL触发器与数据库关联或者被定义在服务器级别,这类触发器一般在Create,Alter或者Drop这类SQL语句执行后触发。

  像DML触发器一样,可以有多个DDL触发器被创建在同一个T-SQL语句上。一个DDL触发器和语句触发它的语句在同一个事务中运行,所以除了Alter
DATABASE之外都可以被回滚。DDL触发器运行在T-SQL语句执行完毕后,也就是不能作为Instead
OF触发器使用。

  两种触发器都与事件相关,在DML触发器中,包含INSERT, UPDATE,
和DELETE,然而很多事件都可以与DDL触发器关联,稍后我们将了解。

系统视图

创建、修改和删除索引

CREATE INDEX语句

CREATE [UNIQUE] [CLUSTERED | NONCLUSTERED]
INDEX <index name> ON <table or view name>(<column name> [ASC | DESC] [, ...n]
[WHERE <condition>])
[WITH
[PAD_INDEX = { ON | OFF }]
[[, ] FILLFACTOR = <fillfactor>]
[[, ] IGNORE_DUP_KEY = { ON | OFF }]
[[, ] DROP_EXISTING = { ON | OFF }]
[[, ] STATISTICS_NORECOMPUTE = { ON | OFF }]
[[, ] SORT_IN_TEMPDB = { ON | OFF}]
[[, ] ONLINE = { ON | OFF }]
[[, ] ALLOW_ROW_LOCKS = { ON | OFF }]
[[, ] MAXDOP = <maxinum degree of parallelism>]
[[, ] DATA_COMPRESSON = { NONE | ROW | PAGE }]
]
[ON {<filegroup> | <partition scheme name> | DEFAULT}]

随约束创建的隐含索引

当向表中添加如下两种约束之一时,就会创建隐含索引:

  • 主键约束
  • 唯一约束(也称为替换键)

当创建一个索引作为约束的隐含索引时,除{CLUSTERED |
NONCLUSTERED}和FILLFACTOR外,所有选项都不允许使用。

创建XML索引

XML索引是SQL Server
2005中的新增功能。除了WHERE、IGNORE_DUP_KEY和ONLINE外,XML索引创建语法支持在前面的CREATE语句中所看到的所有相同选项。

在SQL Server中,可以在类型为XML的列上创建索引。这样做的主要要求如下:

  • 在包含需要索引的XML的表上必须具有群集索引。
  • 在创建“辅助”索引之前,必须现XML数据列上创建“主”XML索引。
  • XML索引只能在XML类型的列上创建(而且XML索引是可以在该类型的列上创建的唯一一种索引)。
  • 主索引必须是基表的一部分,不能在视图上创建索引。

主XML索引:在XML索引上创建的第一个索引必须声明为“主”索引。当创建主索引时,SQL
Server创建一个新的群集索引,这个群集索引将基表的群集索引和来自任何指定的XML节点的数据组合在一起。

辅助XML索引:这里没有任何特别之处,非常类似指向群集索引的群集键的非群集索引,辅助XML索引以相似的方法指向主XML索引。一旦创建了主XML索引,就能在XML列上创建多达248个以上的XML索引。

在稀疏列和地理空间列上创建索引
由于其复杂度,这里不过多说明。但事实是可在稀疏列和地理空间类型数据上创建特殊索引。因此,如果要应用这些特殊需求类型的列,就需要将此谨记在心。

索引是什么

每个索引中有多少行在表里面?

通过连接sys.partitions视图,我们可以计算出索引中大约有多少行。我修改了一些代码,关联了sys.extended_properties,这样可以把备注的信息带出来。

--列出每个索引/堆的行数
SELECT 
  OBJECT_SCHEMA_NAME(t.object_id)+'.'+OBJECT_NAME(t.object_id) as 'Table',
  coalesce(i.NAME,'(IAM for heap)') as 'Index',
  Coalesce(
   (SELECT SUM(s.rows) FROM sys.partitions s WHERE s.object_id = i.object_id
        AND s.index_id = i.index_ID    
    ), 0) 'Rows',coalesce(ep.Value,'') as comments
 FROM sys.tables t
   INNER JOIN sys.indexes i ON i.object_id = t.object_id
   LEFT OUTER JOIN sys.Extended_Properties ep
   ON i.Object_Id = ep.Major_Id AND i.Index_Id = Minor_Id AND Class = 7;

 

图片 9

然后,你可以修改这个代码,让其只是展示每个在索引表中的表有多少行。

SELECT
  OBJECT_SCHEMA_NAME(t.object_id)+'.'+OBJECT_NAME(t.object_id) AS 'Table',  
  sum(rows) AS row_count
FROM sys.partitions p INNER JOIN sys.tables t 
   ON p.object_ID=t.object_ID 
WHERE index_id < 2 GROUP BY t.object_ID,Index_ID;

在数据库中列出触发器

那么怎么获取触发器列表?下面我在AdventureWorks数据库中进行查询,注意该库的视图中没有触发器。

第一个查询所有信息都在sys.triggers 的目录视图中。

SELECT

  name AS TriggerName,

  coalesce(object_schema_name(parent_ID)+'.'

    +object_name(parent_ID),'Database ('+db_name()+')') AS TheParent

FROM sys.triggers;



TriggerName                    TheParent

------------------------------ ----------------------------------------

ddlDatabaseTriggerLog          Database (AdventureWorks2012)          

dEmployee                      HumanResources.Employee                

iuPerson                       Person.Person                          

iPurchaseOrderDetail           Purchasing.PurchaseOrderDetail         

uPurchaseOrderDetail           Purchasing.PurchaseOrderDetail         

uPurchaseOrderHeader           Purchasing.PurchaseOrderHeader         

iduSalesOrderDetail            Sales.SalesOrderDetail                 

uSalesOrderHeader              Sales.SalesOrderHeader                 

dVendor                        Purchasing.Vendor                      

iWorkOrder                     Production.WorkOrder                   

uWorkOrder                     Production.WorkOrder   

  我使用元数据函数db_name()使SQL保持简单。db_name()告诉我数据库的名称。object_schema_name()用来查询object_ID代表的对象的架构,以及object_name**()**查询对象名称。这些对对象的引用指向触发器的所有者,触发器可以是数据库本身,也可以是表:服务器触发器有自己的系统视图,稍后我会展示。

如果想要看到所有触发器,那么我们最好使用sys.objects 视图:

SELECT name as TriggerName, object_schema_name(parent_object_ID)+'.'

    +object_name(parent_object_ID) AS TheParent

            FROM   sys.objects

           WHERE  OBJECTPROPERTYEX(object_id,'IsTrigger') = 1

 

注意,输出不包含数据库级别的触发器,因为所有的DML触发器都在sys.objects视图中,但是你会漏掉在sys.triggers视图中的触发器。

上面查询结果:

name                           TheParent

------------------------------ -------------------------------

dEmployee                      HumanResources.Employee

iuPerson                       Person.Person

iPurchaseOrderDetail           Purchasing.PurchaseOrderDetail

uPurchaseOrderDetail           Purchasing.PurchaseOrderDetail

uPurchaseOrderHeader           Purchasing.PurchaseOrderHeader

iduSalesOrderDetail            Sales.SalesOrderDetail

uSalesOrderHeader              Sales.SalesOrderHeader

dVendor                        Purchasing.Vendor

iWorkOrder                     Production.WorkOrder

uWorkOrder                     Production.WorkOrder

 

Information Schema

这个架构是一套视图,视图中是当前数据库的信息。每一个数据库中都有这个架构,只能看到当前数据库的对象信息。可以直接访问这些架构的数据在主要的关系型数据中。其中架构视图不包含数据库部署信息。

对于不同的关系型数据库之间的处理工作这个架构尤其重要。它们非常适合日常工作,例如在访问钱检查是否存在,但是如果需要详细报告则会受到限制。他们还使用一种稍有不同的标准命名法:例如,数据库被称为目录,用户定义的数据类型被称为“domain”。

之前看到MSDN上有人警告说不要使用INFORMATION_SCHEMA视图来确认对象架构,我理解是因为SQL
Server允许在不同的架构中有相同的表名字,因此当只有表名称的时候会有混淆。所以我认为尽管放心使用就好了。

 

明智地选择——在何时何地使用何种索引

选择性

索引,特别是非群集索引,主要在其中有相当高级别的选择性的情况下是有益的。所谓选择性,指的是列中唯一值得百分比。列中唯一值得百分比越高,选择性就越高,从而索引的益处就越大。

** 注意成本:少即是多**

记住,虽然索引在读取数据方面可提高性能,但是在修改数据时,它们实际上花费很高。索引没有通过魔法来维护,每次对数据进行修改时,任何与该数据相关的索引也需要更新。可以将执行更新时花费的时间认为是投资的金钱。每次通过索引读取数据时,您
可以将节省的时间视为投资回报:但是对于没有使用的每个索引,投资就没有任何回报。

选择群集索引

记住,只可以有一个群集索引,所以需要明智地选择它。

默认情况下,主键是和群集索引一起创建的。这通常是个不错的选择,但并不总是如此(实际上,在有些情况下,这回带来严重的危害),并且如果这样做,那么
将不能在其他任何地方使用群集索引。这里的要点在于不要接受默认方式。在定义主键时要考虑一下,确实想要它作为群集索引吗?

如果确实想要改变,也就是说,不想声明为群集索引,那么在创建表时只需要添加NONCLUSTERED关键字。例如:

CREATE TABLE MyTableKeyExample
(
  Column1 int IDENTITY
    PRIMARK KEY NONCLUSTERED,
  Column2 int
)

如果讨论的列常作为范围查询的对象,那么群集索引对于这类查询是很用的。这类查询通常使用BETWEEN语句或者<or>符号。使用GROUP
BY以及可利用MAX、MIN和COUNT聚合函数额查询也是使用范围和偏好群集索引的查询的重要示例。群集索引适用此处,这是因为搜索可以直接到达物理数据中的特定点,可一直读数据,直到到达范围的末端,然后停止。这种方法非常有效。当想要数据基于群集排序(使用ORDER
BY)时,群集也是极好的方法。

在将要以非连续的顺序进行大量插入时不适合使用群集索引。还记得页拆分的概念吗?这里会进行叶拆分,并且会消耗大量时间。

列顺序问题

仅仅因为索引中有两个列,这不能说明索引对于任何引用其中一列的查询是有用的。

如果查询中使用了索引中列出的第一个列,那么可考虑使用该索引。好的方面是不必在每一列上一对一匹配——只需要第一个列匹配。当然,(按照顺序)匹配的列越多越好,但只需要通过第一个列就可确定“不要使用”某索引。

可以这样考虑一下,假设在使用电话薄。所有项都按先姓后名的方式进行索引。如果知道要通话电话的人的名是Fred,那么这种排列顺序能带来任何好处吗?另一方面,如果只知道他的姓是Blakc,那么索引将可以用来缩小查找范围。

索引构造过程中较为常见的一种错误是认为一个包含所有列的索引将对任何情况都是有帮助的。实际上,这样做只是将所有数据又存储了一次。如果索引的第一个列没有在查询中的JOIN、ORDER
BY或者WHERE子句中提及,那么索引将完全被忽略。

覆盖索引

简单来说,覆盖索引包含查询所需要的所有数据。如果前面看到的那样,一旦SQL
Server发现它需要的数据,就会停止查找。建立在索引基础的几乎所有最终查找都采用这种处理方式。如果只是仅需要在一个索引键中解析查询,或者需要将其包含在叶子中,就没有理由执行这种查找。

最基础和最显而易见的覆盖索引是群集索引。表中的所有数据都在叶子中,因此群集覆盖了所有查询。

您可以在索引的叶子中包含(INCLUDE)非键列。尽管这看起来可以解决群集键查找的任何问题,但是它需要一定的开销。你的索引键保持相同的大小,因此查找速度依然很快,但是叶子必须增大以容纳额外的数据。此外,每次插入、更新或删除数据时,必须抛弃更多的位数进行补偿。当然,对于必须快速执行的常见查询,这是极好的工具。

过滤索引

到目前为止看到的所有索引有一个共同点:每个索引都针对表中的每一行在叶子中有一个条目。然而,这并不是严格的必要条件,并且有时需要限制出现在索引中的行。

创建过滤索引只需要包括WHERE字句。

您可以在运行包括兼容WHERE表达式的查询时使用该索引。关于过滤索引需要注意一下几点:

  • 索引深度远小于全表索引。您只是索引几千行,而不是索引超过十万行,因此遍历索引的速度更快。
  • 因为索引只包含条件过滤后的结果集,所以通过插入、更新、删除操作维护该索引的开销较低。改变不存在过滤后的结果集中的数据完全不影响索引。

过滤索引的一个相对常见用途是有效地允许在可为NULL的列上设置唯一约束。通过使用WHERE
<column> IS NOT NUL
字句创建唯一索引,您可以阻止重复的实际数据,并且仍然允许存在的NULL值。

修改索引

如果修改索引的组成,那么仍然需要DROP(删除)然后CREATE(创建)索引,或者用DROP_EXISTING=ON选项CRAETE(创建)并使用索引。

ALTER INDEX的语法如下。

ALTER INDEX { <name of index> | ALL }
  ON <table or view name>
  { 
    REBUILD
    [  [  WITH (
          [ PAD_INDEX = { ON | OFF }  ]
          | [ [, ] FILLFACTOR = <fillfactor> ]
          | [ [, ] SORT_IN_TEMPDB = { ON | OFF } ]
          | [ [, ] IGNORE_DUP_KEY = { ON | OFF } ]
          | [ [, ] STATISTICS_NORECOMPUTE = { ON | OFF } ]
          | [ [, ] ONLINE = { ON | OFF } ]
          | [ [, ] ALLOW_ROW_LOCKS = { ON | OFF } ]
          | [ [, ] ALLOW_PAGE_LOCKS = { ON | OFF } ]
          | [ [, ] MAXDOP = <max degree of parallelism> ]
          | [ [, ] ONLINE = { ON | OFF } ]
        )]
        | [ PARTITION = <partition number>
            [ WITH ( 
              <partition rebuild index option>
              [, ...n]
           )]
          ]
        ]
        | DISABLE
        | REORGANIZE
          [ PARTITION = <partition number> ]
          [ WITH ( LOB_COMPACTION = { ON | OFF } ) ]
        | SET (
          [ ALLOW_ROW_LOCKS = { ON | OFF }] 
          | [ [, ] ALLOW_PAGE_LOCKS = { ON | OFF } ]
          | [ [, ] IGNORE_DUP_KEY = { ON | OFF } ]
          | [ [, ] STATISTICS_NORECOMPUTE = { ON | OFF } ]
          )
  }  [ ; ]
  • 索引名:如果项维护一个特定的索引,可以指定该索引,或者使用ALL表明项要维护与指定的表相关联的所有索引。

  • 表明或视图名
    从字面可知,这是想要在其上执行维护的特定对象(表或视图)的名称。注意,必须是一个特定的表(可以给它提供一个列表,然后说“请处理所有这些”)。

  • REBUILD:这是用来修复索引的“工业级强度”的方法。如果使用该选项运行ALTER
    INDEX,那么将完全丢弃旧的索引并重新生成新的索引。结果是真正优化的速印,其中所有叶级和非叶级的页都按照定义进行了重新构建(使用个默认值或者开关修改填充因子等)。如果讨论中的索引是群集索引,那么也会重新组织物理数据。
    要小心使用该选项。一旦开始REBUILD,在完成索引重建之前,正在使用的索引实际就没有了。依赖该索引的所有查询可能变得异常缓慢(可能会降几个数量级)。对于这类事情,首先需要在离线系统上测试,以了解整个过程将花多少时间。然后,计划在非高峰时段运行(最好有人监控,以确保它在高峰时段来临时恢复联机状态)。

  • DISABLE:该选项名副其实,只是方式有点过激。如果该命令的全部作用只是为了让索引离线,直至您决定了进一步要做什么,则它是不错的选择,但它实际上会把索引标记为不可用。一旦禁用了某个索引,在重新激活之前,必须重建索引(不是重新组织,而是重建)。ALTER
    INDEX…DISABLE的相反操作并不是ENABLE,这样的操作并不存在。你必须执行ALTER
    INDEX…REBUILD。
    如果对表禁用了群集索引,那么也会禁用表。数据仍会保留,但在重建群集索引之前,不能被所有索引(因为它们都依赖于群集索引)访问。

REORGANIZE

从开发人员的角度看,这一选项特别好。如果重新组织索引,就得到了比完全重建索引稍逊一点的完全优化,但这种方法可以联机进行(用户仍能使用索引)。

如果仔细琢磨,那么上面的描述可能会让你想到一个问题,“稍逊一点”到底是指什么。它其实指,REORGANIZE只是在索引的叶级起作用,而不触及非叶级。这意味着未获得完全优化。但是,对于大部分的索引而言,那不是真正产生碎片的地方(尽管可能会发生这种情况,并且遇到的情况也不尽相同)。

由于该选项对用户的影响非常小,通常您会希望该工具作为常规维护计划的一部分来使用。后面讨论碎片时将更进一步讨论它。

删除索引

如果正在不断地重新分析情况和添加索引,那么也不要忘记删除索引。记住插入索引需要系统开销。由于在考虑需要的索引时并没有对插入的开销太在意,因此也没有考虑过那些索引是不需要的。总是要自问一下:“可以从中去掉那些索引?”

删除索引的语法非常类似于删除表的语法。唯一的不同在于需要使用索引所附着的表或视图来限定索引名称:

DROP INDEX <table or view name>.<index name>

或者

DROP INDEX <index name> ON <table or view name>

这样就可以删除索引。

从查询计划中获取提示

SQL Server
2008提供了一个新功能:查询计划信息中的索引提示,它们将给出查询优化器所认为有用的、但不存在的索引(一次给出一个索引)。现实情况是,在创建建议的索引之后,您不需要严格地检查查询是否使用该索引;即使该索引不会被任何其他的查询再次使用,他也会用于查询。如果您在执行某个重要的查询时获得该提示,则在大多数情况下需要采取该提示的建议。

索引未被使用的原因

反复的测试!检查您的索引是否被使用。如果它们未被使用,则开始查找原因,比如无序的WHERE子句、缺乏选择性、建议的索引或不可索引条件。

当你的WHERE子句中过滤的是某个函数而不是列时,就不会用索引。

  数据库中的索引类似于一本书的目录,在一本书中使用目录可以快速找到你想要的信息,而不需要读完全书。在数据库中,数据库程序使用索引可以定位到表中的数据,而不必扫描整个表。书中的目录是一个字词以及各字词所在的页码列表,数据库中的索引是表中的值以及各值存储位置的列表。

表中都有很多索引吗?

如果您对某些表具有大量索引感到怀疑,那么可以使用下面查询,该查询告诉您具有超过4个索引和索引计数超过列计数一半的表。它是一种任意选择具有大量索引的表的方法。

--超过4个索引的表 
--索引个数超过列数一半
SELECT object_schema_name(TheIndexes.Object_ID) + '.'+ object_name(TheIndexes.Object_ID) AS TableName,
       Columns, Indexes 
 FROM 
   (SELECT count(*) AS indexes, t.object_ID
      FROM sys.indexes i
      INNER JOIN sys.tables t
        ON i.object_ID=t.object_ID 
    GROUP BY t.object_ID) TheIndexes
 INNER JOIN
  (SELECT count(*) AS columns, t.object_ID
     FROM sys.columns c
       INNER JOIN sys.tables t
     ON c.object_ID=t.object_ID 
   GROUP BY t.object_ID)TheColumns
 ON TheIndexes.object_ID=TheColumns.object_ID
 WHERE indexes>columns/2 AND indexes>4;

 

我的表和视图有多少个触发器?

我想知道每个表有多少个触发器,并且什么情况下触发它们。下面我们列出了具有触发器的表以及每个事件的触发器数量。每个表或者视图对于触发器行为都有一个INSTEAD
OF 触发器,可能是UPDATE, DELETE, 或者 INSERT

。但是一个表可以有多个AFTER触发器行为。这些将展示在下面的查询中(排除视图):

SELECT

convert(CHAR(32),coalesce(object_schema_name(parent_ID)+'.'

    +object_name(parent_ID),'Database ('+db_name()+')')) AS 'Table', triggers,[KD1] [AC2] 

convert(SMALLINT,objectpropertyex(parent_ID, N'TABLEDeleteTriggerCount')) AS 'Delete',

convert(SMALLINT,objectpropertyex(parent_ID, N'TABLEInsertTriggerCount')) AS 'Insert',

convert(SMALLINT,objectpropertyex(parent_ID, N'TABLEUpdateTriggerCount')) AS 'Update'

FROM (SELECT count(*) AS triggers, parent_ID FROM sys.triggers

      WHERE objectpropertyex(parent_ID, N'IsTable') =1

         GROUP BY parent_ID

          )TablesOnly;

--查询结果如下:

Table                            triggers    Delete Insert Update

-------------------------------- ----------- ------ ------ ------

Purchasing.Vendor                1           0      0      0

Production.WorkOrder             2           0      1      1

Purchasing.PurchaseOrderDetail   2           0      1      1

Purchasing.PurchaseOrderHeader   1           0      0      1

Sales.SalesOrderDetail           1           1      1      1

HumanResources.Employee          1           0      0      0

Sales.SalesOrderHeader           1           0      0      1

Person.Person                    1           0      1      1



(8 row(s) affected)

如果超过一个触发器被触发在一个表上,它们不保证顺序,当然也可以使用sp_settriggerorder来控制顺序。通过使用objectpropertyex()元数据函数,需要根据事件输入参数‘ExecIsLastDeleteTrigger’,
‘ExecIsLastInsertTrigger’ 或者
‘ExecIsLastUpdateTrigger’来确认谁是最后一个执行的触发器
。为了得到第一个触发器,酌情使用ObjectPropertyEx()
元数据函数,需要输入参数 ‘ExecIsFirstDeleteTrigger’,
‘ExecIsFirstInsertTrigger’ 或者 ‘ExecIsFirstUpdateTrigger’。

因此我们现在知道了表有哪些触发器,哪些事件触发这些触发器。可以使用objectpropertyex()元数据函数,这个函数返回很多不同信息,根据指定的参数不同。通过查看MSDN中的文档,查看其中的一个文档是否有助于元数据查询,总是值得检查的。

兼容性视图

兼容性视图是维护元数据的视图,在SQL Server
2005之前是有系统表支持的,并且只向后兼容。只在2005之后的版本支持对于某些系统表的查询,例如分区表等,只有部分元数据或者特性是对用户可见的。对于带有很多用户、群组、角色或者2000版本数据类型的数据库而言,使用兼容性视图是有潜在风险的,因为视图中有的列存储了用户的ID或者类型ID,可能会返回NULL或者触发溢出。

维护索引

实际上,就索引的维护而言有以下两个问题需要处理:

  • 页拆分
  • 碎片

这两个问题和页密度相关,虽然两者的表现形式在本质上有区别,但是故障排除工具是一样的,因为处理也是相同的。

碎片

当数据库增长而执行页拆分,然后最终删除数据时,就会产生碎片。虽然从增长的观点看,平衡树机制在保持平衡方面做得还不错,但在删除数据时,它并没有太多作用。最终,可能出现这样一种情况:在这一页上有一个记录,而在那一页上有几个记录,在这种情况下,许多数据页上的数据量只是它们可以保存的总数据量的一小部分。

关于碎片首先会想到的第一个问题是,浪费空间。前面提到过,SQL
Server每次分配一个区段的空间。如果一个页上只有一条记录,则仍然会分配整个区段。

第二个问题是,散布在各处的记录会造成数据检索时的额外的系统开销。为了获取需要的10行记录,SQL
Server不是只加载一页,而是可能必须加载10个不同的页来获取相同的信息。并不只是读取行导致了这一结果,SQL
Server必须读取该页,更多的页意味着更多的读取工作量。

虽说如此,数据库碎片也有它好的一面,OLTP系统就喜欢碎片。原因是什么呢?页拆分。没有许多数据的页在插入数据时几乎或完全不用担心也拆分。

所以,大量的碎片意味着较差的读取性能,但是它也意味着极好的插入性能。正如您所预料的,这意味着OLAP系统实际不喜欢碎片。

索引的利弊

查询更新过的索引缺没有使用过有哪些?

总是有必要找出自上次启动服务器以来没有使用的索引,特别是如果服务器一直在做各种各样的工作时。

--Indexes updated but not read.
SELECT
    object_schema_name(i.Object_ID) + '.'+ object_name(i.Object_ID) as Thetable,
    i.name    AS 'Index'
  FROM sys.indexes i
    left outer join sys.dm_db_index_usage_stats s 
      ON s.object_id = i.object_id
       AND s.index_id = i.index_id
       AND s.database_id = DB_ID()
  WHERE OBJECTPROPERTY(i.object_id, 'IsUserTable') = 1
    AND i.index_id > 0  --Exclude heaps.
    AND i.is_primary_key = 0 --and Exclude primary keys.
    AND i.is_unique = 0    --and Exclude unique constraints.
    AND coalesce(s.user_lookups + s.user_scans + s.user_seeks,0) = 0 --No user reads.
    AND coalesce(s.user_updates,0) > 0; --Index is being updated.

 

注意:我已经在代码里使用了动态管理视图sys.dm_db_index_usage_stats,这里起到了手机使用信息的作用,之后我们会更详尽的使用换这个对象来说明其作用。

触发器何时触发事件?

让我们看一下这些触发器,DML触发器可以在所有其他时间发生后触发,但是可以在约束被处理前并且触发INSTEAD
OF触发动作。下面我们就来看看所有的触发的到底是AFTER 还是INSTEAD OF
触发器,有事什么时间触发了触发器。

/* 列出触发器,无论它们是否启用,以及触发器事件。*/

SELECT

  convert(CHAR(25),name) AS triggerName,

  convert(CHAR(32),coalesce(object_schema_name(parent_ID)+'.'

    +object_name(parent_ID),'Database ('+db_name()+')')) AS TheParent,

       is_disabled,

       CASE WHEN is_instead_of_trigger=1 THEN 'INSTEAD OF ' ELSE 'AFTER ' END

       +Stuff (--get a list of events for each trigger

        (SELECT ', '+type_desc FROM sys.trigger_events te

           WHERE te.object_ID=sys.triggers.object_ID

         FOR XML PATH(''), TYPE).value('.', 'varchar(max)'),1,2,'') AS events

 FROM sys.triggers;

结果如下:

triggerName               TheParent                        is_disabled events

------------------------- -------------------------------- ----------- ---------

ddlDatabaseTriggerLog     Database (AdventureWorks2012)    1           AFTER CREATE_TABLE, ALTER_TABLE, DROP_TABLE, CREATE_VIEW, ALTER_VIEW, DROP_VIEW, CREATE_INDEX, ALTER_INDEX, DROP_INDEX, CREATE_XML_INDEX, ALTER_FULLTEXT_INDEX, CREATE_FULLTEXT_INDEX, DROP_FULLTEXT_INDEX, CREATE_SPATIAL_INDEX, CREATE_STATISTICS, UPDATE_STAT

t_AB                      dbo.AB                           0           INSTEAD OF INSERT

dEmployee                 HumanResources.Employee          0           INSTEAD OF DELETE

iuPerson                  Person.Person                    0           AFTER INSERT, UPDATE

iPurchaseOrderDetail      Purchasing.PurchaseOrderDetail   0           AFTER INSERT

uPurchaseOrderDetail      Purchasing.PurchaseOrderDetail   0           AFTER UPDATE

uPurchaseOrderHeader      Purchasing.PurchaseOrderHeader   0           AFTER UPDATE

iduSalesOrderDetail       Sales.SalesOrderDetail           0           AFTER INSERT, UPDATE, DELETE

uSalesOrderHeader         Sales.SalesOrderHeader           0           AFTER UPDATE

dVendor                   Purchasing.Vendor                0           INSTEAD OF DELETE

iWorkOrder                Production.WorkOrder             0           AFTER INSERT

uWorkOrder                Production.WorkOrder             0           AFTER UPDATE

 

As you will notice, we used a FOR XML PATH(‘’)
trick
here to make a list of the events for each trigger to make it easier to
read. These events were pulled from the sys.trigger_events view using
a correlated subquery.

注意到我们使用了FOR XML
PATH(‘’)来列出事件的每一个触发器,更容易读取理解。sys.trigger_events使用相关子查询来查询这些事件。

目录视图

目录视图提供了关于数据库架构的信息。它们也被数据库引擎自己本身使用,尤其在查询优化环节。因此这些视图需要更高效的方式来获取元数据。除了复制、备份、数据库维护计划或SQL
Server代理目录数据之外,所有元数据都通过这些编目视图公开。

这些视图用一种相当特殊的方式排列,SQL
Server对象的共有信息都保存在sys.objects里面。有许多派生视图,比如外键、约束、服务队列、表、视图和过程,这些视图用特定于被编目的对象类型的信息来补充一般的对象信息

并非SQL
Server元数据中的所有内容都是对象。例如,一个列、索引或分布统计信息不是对象。一些如主键约束或扩展属性有一个奇怪的两面性,因为它们被被当做为一个对象,当被强制键索引的实例化时,它就不是一个对象。有些对象(主要是约束)与另一种类型的对象具有父/子关系;父即表。

小结

在SQL
Server或者任何其他数据艰苦环境中,索引都是一个基本的主题,而且不可轻视。它们有助于获得良好的性能,但也可能
导致极差的性能。

关于索引要考虑的几个首要问题如下:

  • 群集索引通常比非群集索引快(可以说前者总是比后者块,但是也有一些例外)。
  • 仅在将得到高级别选择性的列(也就是说,95%或者更多的行是唯一的)上放置非群集索引。
  • 所有的数据操作语言(DML :
    INSERT、UPDATE、DELETE、SELECT)语句可以通过索引获益,但是插入、删除和更新(记住,它们使用删除和插入方法)会因为索引而变慢。索引有助于查询的查找过程,但是任何修改数据的行为将有额外的工作要做(除了实际数据外,还要维护索引)。
  • 索引会占用空间。
  • 仅当索引中的第一列和查询相关时才使用索引。
  • 索引的负面影响和它的正面影响一样多,了解为什么建立索引,以及为什么只建立需要的索引。
  • 索引可为非结果化XML数据提供结构化的数据性能,但是要记住,和其他索引一样,这回涉及系统开销。

考虑索引是可以问自己这样一些问题:

  • Q : 会对这个表进行大量插入或者修改吗?
    A : 如果是,尽量少用索引。这种索引的表通常通过主键的单个记录查找
    完成修改,这往往是该表上需要的唯一索引。如果插入时非连续的,不考虑使用群集索引。
  • Q :
    这是报表吗?也就是说,这里没有许多插入,但是会以许多不同的方法运行报表吗?
    A :
    有更多索引是好的。将群集索引确定为频繁使用的、可能会在范围内提取的信息。OLAP系统中的索引数量是OLTP环境中所看到的许多倍。
  • Q : 在数据上有高级别的选择性吗?
    A : 如果是,而且它通常是WHERE子句的目标,那么添加索引。
  • Q : 已经删除不再需要的索引了吗?
    A : 如果没有,为什么不删除?
  • Q : 已建立了维护策略吗?
    A : 如果没有,为什么不建立?

  查询执行的大部分开销是I/O,使用索引提高性能的一个主要目标是避免全表扫描,因为全表扫描需要从磁盘上读取表的每一个数据页,如果有索引指向数据值,则查询只需要读少数次的磁盘就行了。所以合理的使用索引能加速数据的查询。但是索引并不总是提高系统的性能,带索引的表需要在数据库中占用更多的存储空间,同样用来增删数据的命令运行时间以及维护索引所需的处理时间会更长。所以我们要合理使用索引,及时更新去除次优索引。

这些索引占用了多少空间?

如果打算知道索引占了多少空间,有许多‘胖’索引,就是包含了很多列,有可能索引中有的列不会出现在任何查询中,这就是浪费了空间。

SELECT 
  object_schema_name(i.Object_ID) + '.'+ object_name(i.Object_ID) AS Thetable,
  coalesce(i.name,'heap IAM')    AS 'Index',
  convert(DECIMAL(9,2),(sum(a.total_pages) * 8.00) / 1024.00)    AS 'Index_MB'
FROM sys.indexes i
INNER JOIN sys.partitions p 
  ON i.object_id = p.object_id
    AND i.index_id = p.index_id
INNER JOIN sys.allocation_units a 
  ON p.partition_id = a.container_id
  WHERE objectproperty(i.object_id, 'IsUserTable') = 1
GROUP BY i.object_id, i.index_id, i.name;

 

图片 10

触发器的多长?

许多数据库人员不赞成冗长触发器的定义,但他们可能会发现,根据定义的长度排序的触发器列表是研究数据库的一种有用方法。

SELECT convert(CHAR(32),coalesce(object_schema_name(t.object_ID)+'.','')

    +name) AS TheTrigger,

       convert(CHAR(32),coalesce(object_schema_name(parent_ID)+'.'

    +object_name(parent_ID),'Database ('+db_name()+')')) AS theParent,

       len(definition) AS length --the length of the definition

FROM sys.SQL_modules m

  INNER JOIN sys.triggers t

    ON t.object_ID=m.object_ID

ORDER BY length DESC;

访问sys.SQL_modules视图可以查看触发器定义的SQL
DDL,并按大小顺序列出它们,最上面是最大的。

结果:

TheTrigger                       theParent                        length

-------------------------------- -------------------------------- --------

Sales.iduSalesOrderDetail        Sales.SalesOrderDetail           3666

Sales.uSalesOrderHeader          Sales.SalesOrderHeader           2907

Purchasing.uPurchaseOrderDetail  Purchasing.PurchaseOrderDetail   2657

Purchasing.iPurchaseOrderDetail  Purchasing.PurchaseOrderDetail   1967

Person.iuPerson                  Person.Person                    1498

ddlDatabaseTriggerLog            Database (AdventureWorks2012)    1235

Purchasing.dVendor               Purchasing.Vendor                1103

Production.uWorkOrder            Production.WorkOrder             1103

Purchasing.uPurchaseOrderHeader  Purchasing.PurchaseOrderHeader   1085

Production.iWorkOrder            Production.WorkOrder             1011

HumanResources.dEmployee         HumanResources.Employee          604

 

好吧,我可能太挑剔了,不太喜欢太长的,但是逻辑有时候会很长。事实上,前三名在我看来是不可靠的,尽管我总是倾向于尽可能少地使用触发器。

数据层应用程序视图

数据层应用程序视图被用于访问注册服务器信息。特殊版本的服务器和信息用来检查这些版本是否漂移。这是一种作为容易的检查当前注册数据库版本的方式,直接用T-SQL查询。

参考

《SQLServer2012编程入门经典(第4版)》

索引的分类

计算表总的索引空间

让我们看看每个表的总索引空间,以及表中的行数。

SELECT 
  object_schema_name(i.Object_ID) + '.'+ object_name(i.Object_ID) AS Thetable,
  convert(DECIMAL(9,2),(sum(a.total_pages) * 8.00) / 1024.00)    AS 'Index_MB',
  max(row_count) AS 'Rows',
  count(*) AS Index_count
FROM sys.indexes i
INNER JOIN
  (SELECT object_ID,Index_ID, sum(rows) AS Row_count 
     FROM sys.partitions GROUP BY object_ID,Index_ID)f
  ON f.object_ID=i.object_ID AND f.index_ID=i.index_ID
INNER JOIN sys.partitions p 
  ON i.object_id = p.object_id
    AND i.index_id = p.index_id
INNER JOIN sys.allocation_units a 
  ON p.partition_id = a.container_id
  WHERE objectproperty(i.object_id, 'IsUserTable') = 1
GROUP BY i.object_id;

图片 11

这些触发器访问了多少对象

在代码中,每个触发器要访问多少对象(比如表和函数)?

我们只需要检查表达式依赖项。这个查询使用一个视图来列出“软”依赖项(如触发器、视图和函数)。

SELECT coalesce(object_schema_name(parent_id)

          +'.','')+convert(CHAR(32),name) AS TheTrigger,

          count(*) AS Dependencies

FROM sys.triggers

INNER JOIN sys.SQL_Expression_dependencies

ON [referencing_id]=object_ID

GROUP BY name, parent_id

ORDER BY count(*) DESC;
--结果:

TheTrigger                               Dependencies

---------------------------------------- ------------

Sales.iduSalesOrderDetail                7

Sales.uSalesOrderHeader                  7

Purchasing.iPurchaseOrderDetail          5

Purchasing.uPurchaseOrderDetail          5

Purchasing.uPurchaseOrderHeader          3

Production.iWorkOrder                    3

Production.uWorkOrder                    3

dbo.t_AB                                 2

Purchasing.dVendor                       2

Person.iuPerson                          2

ddlDatabaseTriggerLog                    1

 

居然有两个触发器有7个依赖!让我们就Sales.iduSalesOrderDetail来实际看一下,有哪些依赖。

动态管理视图和功能(DMVs)

DMV一般用来调优,诊断问题和监控数据库服务器状态。最重要的作用就是提供了一种方式来查询数据库的使用信息。例如,不仅查询到索引,而且可以查询到使用量的排序和耗时等。

    SQL SERVER中有多种索引类型。

如何查询表使用索引的各种方式?

发现关于索引的某些属性,通常最好使用属性函数作为快捷方式。

-- 查询没有主键的表
SELECT  object_schema_name(object_id)+'.'+object_name(object_id) as No_Primary_key
  FROM sys.tables/* see whether the table has a primary key */
  WHERE objectproperty(OBJECT_ID,'TableHasPrimaryKey') = 0;


-- 查询没有索引的表
SELECT  object_schema_name(object_id)+'.'+object_name(object_id) as No_Indexes  
  FROM sys.tables /* see whether the table has any index */
  WHERE objectproperty(OBJECT_ID,'TableHasIndex') = 0;


-- )查询没有候选键的表
SELECT  object_schema_name(object_id)+'.'+object_name(object_id) as No_Candidate_Key
  FROM sys.tables/* if no unique constraint then it isn't relational */
  WHERE objectproperty(OBJECT_ID,'TableHasUniqueCnst') = 0
    AND   objectproperty(OBJECT_ID,'TableHasPrimaryKey') = 0;


--查询带有禁用索引的表
SELECT  distinct
  object_schema_name(object_id)+'.'+object_name(object_id) as Has_Disabled_indexes
  FROM sys.indexes /* don't leave these lying around */
  WHERE is_disabled=1;

特定触发器访问或者写入哪些对象?

我们可以列出触发器在代码中引用的所有对象

SELECT

  convert(char(32),name) as TheTrigger,

  convert(char(32),coalesce([referenced_server_name]+'.','')

            +coalesce([referenced_database_name]+'.','')

       +coalesce([referenced_schema_name]+'.','')+[referenced_entity_name])
     as referencedObject

FROM sys.triggers

INNER JOIN sys.SQL_Expression_dependencies

ON [referencing_id]=object_ID

WHERE name LIKE 'iduSalesOrderDetail';

--查询结果:

TheTrigger                       referencedObject

-------------------------------- --------------------------------

iduSalesOrderDetail              Sales.Customer                 

iduSalesOrderDetail              Person.Person                  

iduSalesOrderDetail              Sales.SalesOrderDetail         

iduSalesOrderDetail              Sales.SalesOrderHeader          

iduSalesOrderDetail              Production.TransactionHistory  

iduSalesOrderDetail              dbo.uspLogError                

iduSalesOrderDetail              dbo.uspPrintError

 

元数据function

还有很多元数据函数,如object_name()或col_name(),它们提供关于当前数据库中的模式作用域对象的信息。通过避免在元数据表达式中进行显式连接,它们提供了获取信息的捷径,因此,当与编目视图一起使用时,它们可以帮助您更快地获取关于元数据的信息。

  按存储结构区分:“聚集索引(又称聚类索引,簇集索引)”,“非聚集索引(非聚类索引,非簇集索引)”

那些是对象,那些不是?

你可能注意到了一些奇怪的事情。尽管表的一些属性(如主键)本身就是对象,但列、统计或索引并非对象。让我们弄清楚这一点,因为它不是完全直观的体现在sys.objects,您可以找到关于所有公共数据库组件的基本标准信息,如表、视图、同义词、外键、检查约束、键约束、默认约束、服务队列、触发器和过程。我列出的所有这些组件都有其他属性,这些属性必须通过继承相关基本属性的视图可见,但也包括与对象相关的数据列。最好使用这些特殊的视图,因为它们有您需要的所有信息,系统只过滤您感兴趣的对象类型,比如表。各种对象(如约束和触发器)在sys.objects中都有parent_ID,非零的对象表,显示它们是子对象。

下面的查询向您展示了一种查看这些子对象并将其与父母关联的简单方法。

--查询索引父对象(表名)和索引名称
SELECT parent.name AS Parents_name, 
       child.name AS Childs_Name, 
       replace(lower(parent.type_desc),'_',' ') AS Parents_type, 
       replace(lower(child.type_desc),'_',' ') AS Childs_type
FROM sys.objects child
  INNER JOIN sys.objects parent
    ON parent.object_ID=child.parent_object_id
WHERE child.parent_object_id<>0
ORDER BY parents_name;

 

图片 12.

你会发现索引不是对象。在第一个查询中,返回的object_ID是定义索引的表的ID。

这里的问题是关系是复杂的。约束可以包含几个列,也可以由索引强制。索引可以包含几个列,但是顺序很重要。统计数据还可以包含几个列,也可以与索引相关联。这意sys.indexes,
sys.stats and
sys.columns不从sys.objects继承。参数和类型也是如此。

触发器里有什么代码?

现在让我们通过检查触发器的源代码来确认这一点。.

SELECT OBJECT_DEFINITION ( object_id('sales.iduSalesOrderDetail') ); 

我们之前的查询是正确的,扫描源码可知所有的依赖项。大量依赖项表名对于数据库的重构等需要非常小心,例如,修改一个基础表的列。

据需要做什么,您可能希望检查来自元数据视图的定义,而不是使用OBJECT_DEFINITION函数。

 SELECT definition

FROM sys.SQL_modules m

  INNER JOIN sys.triggers t

    ON t.object_ID=m.object_ID

WHERE t.object_ID=object_id('sales.iduSalesOrderDetail');

目录存储过程

有许多存储过程的主要功能是为SQL
Server的ODBC驱动程序提供元数据信息。当您建立ODBC连接时,该信息作为数据对象的集合。但是,这些信息通常是可用的,并且可以像任何其他存储过程一样从SQL中使用。它们通常被认为不如目录视图有用,因为存储过程返回的结果必须使用INSERT插入一个表或者表变量中,需要使用INSERT
… EXECUTE 语法。

为什么元数据视图和功能很重要?

元数据视图和函数允许您搜索元数据,提供对数据库报告和总结,找出谁有权限查看或改变什么数据,让你减少重复输入,让几乎所有隐藏在SQL
Server Management
Studio的信息可查询,使部署脚本更安全,更可靠,找出最近的改变或创建,快速处理一些函数或过程,确定已注册数据库的版本,审计用于编码实践的数据库代码,发现重复索引并且允许减少低效的点击操作。当与其他SQL
Server工具(如默认跟踪和动态管理对象)结合使用时,使用强大的SQL脚本用于开发和管理数据库是相当快速的。

元数据视图和函数允许执行几乎不可能执行的操作,例如查找依赖于指定的CLR用户定义类型或别名类型的参数。

  按数据唯一性区分:“唯一索引”,“非唯一索引”

如何查询每一个表的每一个索引的每一个列?

最简单的查询方式如下:

SELECT object_schema_name(t.object_ID)+'.'+t.name AS The_Table, --the name of the table
       i.name AS The_Index,  -- its index
       index_column_id,
       col_name(Ic.Object_Id, Ic.Column_Id) AS The_Column --the column
FROM sys.tables t
INNER JOIN sys.indexes i
    ON t.object_ID=i.object_ID
INNER JOIN sys.Index_columns  ic
    ON i.Object_ID=ic.Object_ID
    AND i.index_ID=ic.index_ID
ORDER BY t.name,i.index_id, index_column_id;

 

 

图片 13

当然也可以指定特定表,例如:

  WHERE i.object_id = OBJECT_ID('Production.BillOfMaterials');

搜索触发器的代码

There are always plenty of ways of using the metadata views and
functions. I wonder if all these triggers are executing that
uspPrintError procedure?

有很多使用元数据视图和函数的方法。想知道是否所有这些触发器都执行uspPrintError存储过程?

/* 在所有触发器中搜索字符串 */

 

SELECT convert(CHAR(32),coalesce(object_schema_name(object_ID)+'.','')

    +name) AS TheTrigger, '...'+substring(definition, hit-20,120) +'...'

FROM

  (SELECT name, definition, t.object_ID, charindex('EXECUTE [dbo].[uspPrintError]',definition) AS hit

   FROM sys.SQL_modules m

     INNER JOIN sys.triggers t

       ON t.object_ID=m.object_ID)f

WHERE hit>0; 

 

结果如图:

图片 14

 

8个引用正在执行这个过程。我们在sys.SQL_modules中搜索了所有的定义可以找到一个特定的字符串,这种方式很慢很暴力,但是它是有效的!

我是如何逐渐使用的?

学习使用元数据视图和函数的第一阶段是收集从各种著名的数据源(如SQL Server
Central)中使用它们的查询。可以在MSDN上查询到。使用记录工具保存这些查询。如果它是一个用来保存注释或片段的工具,可以让您在任何地方轻松地获取查询,那么它将会有所帮助。一段时间后,就可以根据使用需要对这些查询稍作修改。然后,不需要在object
browser窗格中搜索表列表,您很快就可以从集合中获取适当的查询,执行它,并快速获取信息。

  按键列个数区分:“单列索引”,“多列索引”。

索引中有哪些列,顺序又是什么 ?

也可以汇总上面语句,每个索引汇总成一行,展示所有索引,具体代码如下:

SELECT object_schema_name(t.object_ID)+'.'+t.name AS The_Table, --the name of the table
   coalesce(stuff (--get a list of indexes
     (SELECT ', '+i.name
     +' ( '
         +stuff (--get a list of columns
         (SELECT ', ' + col_name(Ic.Object_Id, Ic.Column_Id) 
         FROM  sys.Index_columns  ic
         WHERE ic.Object_ID=i.Object_ID
         AND ic.index_ID=i.index_ID
         ORDER BY index_column_ID ASC
         FOR XML PATH(''), TYPE).value('.', 'varchar(max)'),1,2,'') +' )'
     FROM sys.indexes i 
     WHERE i.object_ID=t.object_ID
     FOR XML PATH(''), TYPE).value('.', 'varchar(max)'),1,2,''),'') AS Indexes
 FROM sys.tables t;

效果如下:

图片 15

在所有对象中搜索字符串

我想知道除了触发器之外是否还有其他对象调用这个过程?我们稍微修改查询以搜索sys.objects视图,而不是sys.triggers,以搜索所有具有与之关联的代码的对象。我们还需要显示对象的类型

/* 在所有对象中搜索字符串 */

 SELECT convert(CHAR(32),coalesce(object_schema_name(object_ID)+'.','')

    +object_name(object_ID)) AS TheObject, type_desc, '...'+substring(definition,hit-20,120)+'...' as TheExtract

FROM

  (SELECT  type_desc, definition, o.object_ID, charindex('uspPrintError',definition) AS hit

   FROM sys.SQL_modules m

     INNER JOIN sys.objects o

       ON o.object_ID=m.object_ID)f

WHERE hit>0; 

查询结果如下图:

图片 16

 From this output we can see that, other than the procedure itself where
it is defined, and the triggers, only dbo.uspLogError is executing the
uspPrintError procedure. (see the first column, second line down)

从这个输出中我们可以看到,除了在定义它的过程本身之外,还有触发器,只有dbo.uspLogError正在执行uspPrintError过程。(见第一列,第二行往下)

比较有用的查询实例

下面我会展示的例子都已经在2008和2012
两个版本中测试。当然只用到了各自版本的最后一个版本更新后的数据库。

下图中展示了所有继承sys.objects列的视图。这意味着它们除了拥有这些列以外,还有列的对应类型。这是视图所有的信息比如create_date也都来自sys.objects

 图片 17

* *

要列出数据库中的所有视图(存储过程和外键),只需执行以下操作 …

 

SELECT  object_schema_name(object_id)+'.'+name FROM sys.views;

 SELECT  object_schema_name(object_id)+'.'+name FROM sys.procedures;

SELECT name AS Foreign_key,object_schema_name(parent_object_ID)+'.'+object_name(parent_object_ID) AS parent,

object_schema_name(referenced_object_ID)+’.’+object_name(referenced_object_ID)
AS referenced

FROM sys.foreign_keys;

 

对于所有其他的,您需要使用一个系统函数来过滤您想要的对象。下面的代码提供了一些有用的示例。因为我们只获取对象的名称,所以使用sys.objects,它具有所有数据库对象共有的基本信息的视图。如果我们需要特定于特定类型对象的信息,比如主键是否具有系统生成的名称,那么您就必须为该特定类型的对象使用视图。

/* The Tables */
  --数据库中的所有用户表
    SELECT
      ob.name AS User_Table, Coalesce(ep.value, '') AS documentation
    FROM sys.objects AS ob
      LEFT OUTER JOIN sys.extended_properties AS ep
        ON ep.major_id = ob.object_id
           AND ep.class = 1
           AND ep.minor_id = 0
    WHERE ObjectProperty(ob.object_id, 'IsUserTable') = 1 

/* The Views */
  --视图
  SELECT ob.name AS ViewName, Coalesce(ep.value, '') AS documentation
  FROM sys.objects ob  LEFT OUTER JOIN sys.extended_properties AS ep
      ON ep.major_id = ob.object_id
         AND ep.class = 1
         AND ep.minor_id = 0
  WHERE objectproperty(ob.object_id,'IsView')= 1 


/* The Check Constraints */
 --Check约束
    SELECT
      objects.name AS Name_of_Check_Constraint,
      Object_Schema_Name(objects.parent_object_id) + '.' + Object_Name(objects.parent_object_id) AS parent,
   Coalesce(ep.value,'') AS documentation
   FROM sys.objects
      LEFT OUTER JOIN sys.extended_properties AS ep
        ON ep.major_id = objects.object_id AND ep.class=1
       AND ep.name='MS_Description'--microsoft 公约
    WHERE ObjectProperty(objects.object_id, 'IsCheckCnst') = 1 

/* The Constraints */ 

SELECT
--约束
  objects.name AS Name_of_Constraint, --see all constraints and parent table
  Lower(Replace(type_desc,'_',' ')),--the type of constraint
  Object_Schema_Name(objects.parent_object_id) + '.' + Object_Name(objects.parent_object_id) AS parent,
  Coalesce(ep.value, '') AS documentation
FROM sys.objects
  LEFT OUTER JOIN sys.extended_properties AS ep
    ON ep.major_id = objects.object_id
       AND ep.class = 1
       AND ep.name = 'MS_Description' 
WHERE ObjectProperty(objects.object_id, 'IsConstraint') = 1;

/* The Defaults */
--默认
SELECT
  objects.name, 
  Coalesce(ep.value, '') AS documentation
FROM sys.objects
  LEFT OUTER JOIN sys.extended_properties AS ep
    ON ep.major_id = objects.object_id
       AND ep.class = 1
       AND ep.name = 'MS_Description' 
WHERE ObjectProperty(objects.object_id, 'IsDefault') = 1;

/* The Default Constraints */
--数据库及其父表中的所有默认约束
SELECT objects.name AS Name_of_Default_Constraint,--see all Default constraints and parent table
   Coalesce(ep.value,'') AS documentation,
object_schema_name(objects.parent_object_id)+'.'+object_name(objects.parent_object_id) AS parent,
   Coalesce(EP_parent.value,'') AS documentation
FROM sys.objects 
  LEFT OUTER JOIN sys.extended_properties AS ep
    ON ep.major_id = objects.object_id
       AND ep.class = 1
       AND ep.name = 'MS_Description' --the microsoft convention
  LEFT OUTER JOIN sys.extended_properties AS EP_parent
    ON ep.major_id = objects.parent_object_id
       AND ep.name = 'MS_Description' --the microsoft convention   
 WHERE objectproperty(objects.object_id,'IsDefaultCnst')= 1;

/* The Executables */
--数据库中的所有可执行文件(过程、函数等)
SELECT
  oe.name AS Name_Of_Executable,
  Replace(Lower(oe.type_desc), '_', ' ') AS Type_Of_Executable,
  Coalesce(EP.value, '') AS Documentation
FROM sys.objects AS oe
  LEFT OUTER JOIN sys.extended_properties AS EP
    ON EP.major_id = oe.object_id
       AND EP.name = 'MS_Description' 
WHERE ObjectProperty(oe.object_id, 'IsExecuted') = 1;


/* The Extended Stored Procedures */ 
--数据库中的所有扩展存储过程
SELECT
  oep.name AS Name_of_Extended_Procedure, Coalesce(EP.value, '') AS Documentation
FROM sys.objects AS oep
  LEFT OUTER JOIN sys.extended_properties AS EP
    ON EP.major_id = oep.object_id
       AND EP.name = 'MS_Description' 
WHERE ObjectProperty(oep.object_id, 'IsExtendedProc') = 1;

/* The Inline Functions */ 
--数据库中的所有内联函数
SELECT ilf.name AS Inline_function,
Coalesce(EP.value, '') AS Documentation
FROM sys.objects AS ilf
  LEFT OUTER JOIN sys.extended_properties AS EP
    ON EP.major_id = ilf.object_id
       AND EP.name = 'MS_Description'
 WHERE objectproperty(ilf.object_id,'IsInlineFunction')= 1;

/* The Primary Keys */ 
--数据库中的所有主键及其父表
SELECT
  pk.name AS Primary_key,
  Object_Schema_Name(pk.parent_object_id) + '.' + Object_Name(pk.parent_object_id) AS parent,
  Coalesce(EP.value, '') AS KeyDoc, Coalesce(EPParent.value, '') AS TableDoc
FROM sys.objects AS pk
  LEFT OUTER JOIN sys.extended_properties AS EP
    ON EP.major_id = pk.object_id
       AND EP.name = 'MS_Description' 
  LEFT OUTER JOIN sys.extended_properties AS EPParent
    ON EPParent.major_id = pk.parent_object_id
       AND EPParent.minor_id = 0
       AND EPParent.name = 'MS_Description' 
WHERE ObjectProperty(pk.object_id, 'IsPrimaryKey') = 1;

/* The Stored Procedures */
--数据库中的所有存储过程
SELECT
  sp.name AS Stored_procedure, Coalesce(EP.value, '') AS Documentation
FROM sys.objects AS sp
  LEFT OUTER JOIN sys.extended_properties AS EP
    ON EP.major_id = sp.object_id
       AND EP.minor_id = 0
       AND EP.name = 'MS_Description'  
WHERE ObjectProperty(sp.object_id, 'IsProcedure') = 1;

/* The Queues */ 
--数据库中的所有队列
SELECT
  q.name AS QueueName, Coalesce(EP.value, '') AS Documentation
FROM sys.objects AS q
  LEFT OUTER JOIN sys.extended_properties AS EP
    ON EP.major_id = q.object_id
       AND EP.name = 'MS_Description'   
WHERE ObjectProperty(q.object_id, 'IsQueue') = 1;

/* The Rules */ 
--数据库中的所有旧式规则
SELECT
  ru.name AS RuleName, --old-fashioned sybase-style rule
  Coalesce(EP.value, '') AS Documentation
FROM sys.objects AS ru
  LEFT OUTER JOIN sys.extended_properties AS EP
    ON EP.major_id = ru.object_id
       AND EP.name = 'MS_Description'  
WHERE ObjectProperty(ru.object_id, 'IsRule') = 1;

/* The Scalar Functions */ 
--数据库中的所有标量函数。
SELECT
  sf.name AS Scalar_function, Coalesce(EP.value, '') AS Documentation
FROM sys.objects AS sf
  LEFT OUTER JOIN sys.extended_properties AS EP
    ON EP.major_id = sf.object_id
       AND EP.name = 'MS_Description' 
WHERE ObjectProperty(sf.object_id, 'IsScalarFunction') = 1;

/* The System Tables */ 
--据库中的所有系统表
SELECT
  st.name AS System_table, Coalesce(EP.value, '') AS Documentation
FROM sys.objects AS st
  LEFT OUTER JOIN sys.extended_properties AS EP
    ON EP.major_id = st.object_id
       AND EP.name = 'MS_Description' 
WHERE ObjectProperty(st.object_id, 'IsSystemTable') = 1;

--数据库中的所有表,包括系统表
SELECT
  at.name AS TableName, 
  Lower(Replace(type_desc,'_',' ')),--约束的类型
  Coalesce(EP.value, '') AS Documentation
FROM sys.objects AS at
  LEFT OUTER JOIN sys.extended_properties AS EP
    ON EP.major_id = at.object_id
       AND EP.name = 'MS_Description'
WHERE ObjectProperty(at.object_id, 'IsTable') = 1;

/* The TVFs*/ 
--数据库中的所有表值函数
SELECT
  tvf.name AS Table_Valued_Function, Coalesce(EP.value, '') AS Documentation
FROM sys.objects AS tvf
  LEFT OUTER JOIN sys.extended_properties AS EP
    ON EP.major_id = tvf.object_id
       AND EP.name = 'MS_Description' --the microsoft convention 
WHERE ObjectProperty(tvf.object_id, 'IsTableFunction') = 1;

--数据库及其所有触发器。
SELECT
  tr.name AS TriggerName,
  Object_Schema_Name(tr.parent_object_id) + '.' + Object_Name(tr.parent_object_id) AS parent,
  Coalesce(EP.value, '') AS TriggerDoc, Coalesce(EPParent.value, '') AS TableDoc
FROM sys.objects AS tr
  LEFT OUTER JOIN sys.extended_properties AS EP
    ON EP.major_id = tr.object_id
       AND EP.name = 'MS_Description' 
  LEFT OUTER JOIN sys.extended_properties AS EPParent
    ON EPParent.major_id = tr.parent_object_id
       AND EPParent.minor_id = 0
       AND EPParent.name = 'MS_Description' 
WHERE ObjectProperty(tr.object_id, 'IsTrigger') = 1;

/* The Unique Constraints */ 
--数据库及其父表中的所有惟一约束
SELECT uc.name AS Unique_constraint,--所有唯一的约束
object_schema_name(uc.parent_object_id)+'.'+object_name(uc.parent_object_id) AS parent,
  Coalesce(EP.value, '') AS ConstraintDoc, Coalesce(EPParent.value, '') AS TableDoc
FROM sys.objects AS uc
  LEFT OUTER JOIN sys.extended_properties AS EP
    ON EP.major_id = uc.object_id
       AND EP.name = 'MS_Description' 
  LEFT OUTER JOIN sys.extended_properties AS EPParent
    ON EPParent.major_id = uc.parent_object_id
       AND EPParent.minor_id = 0
       AND EPParent.name = 'MS_Description' 
WHERE objectproperty(uc.object_id,'IsUniqueCnst')= 1;

 

 

当然我们也可以调整这些语句来方便我们的精确查找,比如:

–数据库中的所有视图在过去两周内被修改的有:

SELECT name AS ViewName, convert(char(11),modify_date,113)

FROM sys.objects WHERE objectproperty(OBJECT_ID,'IsView')= 1

AND modify_date > dateadd(week,-2, GetDate());

 

–上个月创建的所有对象的名称和类型

SELECT coalesce(object_schema_name(obj.object_ID)+'.','')+obj.name AS ObjectName,

convert(varchar(30),lower(replace(type_desc,'_',' ')))

FROM sys.objects obj

WHERE create_date > dateadd(month,-1, GetDate());

–DBO架构中所有基本对象的名称和类型

SELECT coalesce(object_schema_name(obj.object_ID)+'.','')+obj.name AS ObjectName,

convert(varchar(30),lower(replace(type_desc,'_',' '))) as ObjectType

FROM sys.objects obj

WHERE parent_object_ID=0

AND schema_ID = schema_ID('dbo'); 

 

  [聚集索引](类似字典中的字母顺序查找)

如何查询XML索引?

XML索引被视为索引的扩展。我发现查看其细节的最好方法是为它们构建一个CREATE语句。

SELECT 'CREATE' + case when secondary_type is null then ' PRIMARY' else '' end
 + ' XML INDEX '+coalesce(xi.name,'')+ '  
    ON ' --what table and column is this XML index on?
 + object_schema_name(ic.Object_ID)+'.'+object_name(ic.Object_ID)
 +' ('+col_name(Ic.Object_Id, Ic.Column_Id)+' )  
    '+ coalesce('USING XML INDEX [' + Using.Name + '] FOR ' + Secondary_Type_DeSc
     COLLATE database_default,'')    
    +'  
'+      replace('WITH ( ' + 
   stuff(
  CASE WHEN xi.Is_Padded <> 0 THEN ', PAD_INDEX  = ON ' ELSE '' END 
  + CASE 
     WHEN xi.Fill_Factor NOT IN (0, 100) 
        THEN ', FILLFACTOR  =' + convert(VARCHAR(3), xi.Fill_Factor) + ''
        ELSE '' END 
  + CASE WHEN xi.Ignore_dUp_Key <> 0 THEN ', IGNORE_DUP_KEY = ON' ELSE '' END 
  + CASE WHEN xi.Allow_Row_Locks = 0 THEN ', ALLOW_ROW_LOCKS  = OFF' ELSE '' END 
  + CASE WHEN xi.Allow_Page_Locks = 0 THEN ', ALLOW_PAGE_LOCKS  = OFF' ELSE ' ' END
   , 1, 1, '')
 + ')', 'WITH ( )', '') --create the list of xml index options
+  coalesce('/* '+convert(varchar(8000),Value)+ '*/','')--and any comment
    AS BuildScript
FROM sys.xml_Indexes xi
      inner join sys.index_columns ic 
   ON ic.Index_Id = xi.Index_Id
   AND ic.Object_Id = xi.Object_Id   
  LEFT OUTER JOIN sys.Indexes [USING]
   ON [USING].Index_Id = xi.UsIng_xml_Index_Id
   AND [USING].Object_Id = xi.Object_Id
  LEFT OUTER JOIN sys.Extended_Properties ep
   ON ic.Object_Id = ep.Major_Id AND ic.Index_Id = Minor_Id AND Class = 7
WHERE object_schema_name(ic.Object_ID) <>'sys' AND ic.index_id>0;

上面的查询结果将显示所有基本的XML索引细节作为构建脚本。

图片 18

列出服务器级触发器及其定义

我们可以通过系统视图了解它们吗?嗯,是的。以下是列出服务器触发器及其定义的语句

 SELECT name, definition

FROM sys.server_SQL_modules m

  INNER JOIN sys.server_triggers t

ON t.object_ID=m.object_ID; 

注意,只能看到有权限看的触发器

总结

  如上,到这级别简单实用足够了。们已经介绍了一般的理论,并介绍了查找数据库中的内容的基本方法。在下一篇中我将会深入介绍触发器并且找到有效信息的排序以便于可以通过系统视图从动态在线目录中收集的有用信息。

 

  聚集索引是一种对磁盘上实际数据重新组织以按指定的一列或多列值排序。像我们用到的汉语字典,就是一个聚集索引,比如要查“张”,我们自然而然就翻到字典的后面百十页。然后根据字母顺序跟查找出来。这里用到微软的平衡二叉树算法,即首先把书翻到大概二分之一的位置,如果要找的页码比该页的页码小,就把书向前翻到四分之一处,否则,就把书向后翻到四分之三的地方,依此类推,把书页续分成更小的部分,直至正确的页码。

元数据中还有其他类型的索引吗?

还有两种比较特殊的索引,一是空间索引,其信息在sys.spatial_index_tessellations

sys.spatial_indexes表中。另一个是全文索引,其信息在fulltext_index_fragments,fulltext_index_catalog_usages, fulltext_index_columns
fulltext_indexes表中保存。**

总结

  本文讨论过触发器,并且你能查出触发器,以及潜在的问题。这里并没有针对关于触发器的查询提供一个全面的工具箱,因为我只是使用触发器作为示例来展示在查询系统视图时可能使用的一些技术。在我们学习了索引、列和参数之后,我们将回到触发器,并了解了编写访问系统视图和information
schema视图的查询的一些日常用途。表是元数据的许多方面的基础。它们是几种类型的对象的父类,其他元数据如索引是表的属性。我们正在慢慢地努力去发现所有关于表的信息。期待下期

  由于聚集索引是给数据排序,不可能有多种排法,所以一个表只能建立一个聚集索引。科学统计建立这样的索引需要至少相当与该表120%的附加空间,用来存放该表的副本和索引中间页,但是他的性能几乎总是比其它索引要快。

探索索引统计信息

现在,让我们讨论一下分布统计数据或“stats”。每个索引都有一个附加的统计对象,以便查询优化器能够提供一个合适的查询计划。为此,它需要估计数据的“基数”,以确定为任何索引值返回多少行,并使用这些“stats”对象告诉它数据是如何分布的。

可以查询统计信息对象是如何与表进行关联的,语句如下:

SELECT object_schema_name(t.Object_ID) + '.'+ t.name AS The_table, 
    stats.name AS Stats_Name, sys.columns.name AS Column_Name
  FROM sys.stats
 INNER JOIN sys.stats_columns
    ON stats.object_id = stats_columns.object_id
   AND stats.stats_id = stats_columns.stats_id
 INNER JOIN sys.columns
    ON stats_columns.object_id = columns.object_id
   AND stats_columns.column_id = columns.column_id
 INNER JOIN sys.tables t
    ON stats.object_id = t.object_id;

 

当它们与索引相关联时,统计数据继承索引的名称,并使用与索引相同的列。

图片 19

  由于在聚集索引下,数据在物理上是按序排列在数据页上的,重复值也排在一起,因而包含范围检查(bentween,<,><=,>=)或使用group
by 或order
by的查询时,一旦找到第一个键值的行,后面都将是连在一起,不必在进一步的搜索,避免啦大范围的扫描,可以大大提高查询速度。

检查重复的统计信息

通过比较与每个统计信息相关联的列号列表,您可以快速查看同一列或一组列是否有多个统计信息。

SELECT object_schema_name(Object_ID)+'.'+object_name(Object_ID) as tableName,
       count(*) as Similar, ColumnList as TheColumn, 
       max(name)+', '+min(name) as duplicates
FROM 
   (SELECT Object_ID, name,   
     stuff (--get a list of columns
         (SELECT ', ' + col_name(sc.Object_Id, sc.Column_Id)
         FROM  sys.stats_columns  sc
         WHERE sc.Object_ID=s.Object_ID
         AND sc.stats_ID=s.stats_ID
         ORDER BY stats_column_ID ASC
         FOR XML PATH(''), TYPE).value('.', 'varchar(max)'),1,2,'') AS ColumnList
   FROM sys.stats s)f
GROUP BY Object_ID,ColumnList 
HAVING count(*) >1;

结果如下:

图片 20

展示了包含重复的统计对象,在本例中是sales.customer表在AccountNumber列上有两个类似的统计对象。

  [非聚集索引](类似于字典中的偏旁部首查找)

总结

 在数据库中有很多有价值的信息都在索引上。一旦表的数量变大,很容易让表出现一些问题,比如无意中没有聚集索引或主键,或者有重复的索引或不必要的统计信息等。我们通过掌握如何查询这些索引的动态视图后能够快速查询定位使用表的信息,方便我们预防和解决这类问题,这些基础方法已经在DBA和数据库开发的工作中变得越来越重要了,

  SQL
Server默认情况下建立的索引是非聚集索引,他不重新组织表中的数据,而是对每一行存储索引列值并用一个指针指向数据所在的页面。
他像汉语字典中的根据‘偏旁部首’查找要找的字,即便对数据不排序,然而他拥有的目录更像是目录,对查取数据的效率也是具有的提升空间,而不需要全表扫描。

  一个表可以拥有多个非聚集索引,每个非聚集索引根据索引列的不同提供不同的排序顺序。

填充因子

  索引的一个特性,定义该索引每页上的可用空间量。FILLFACTOR(填充因子)适应以后表数据的扩展并减小了页拆分的可能性。填充因子是从0到100的百分比数值,设为100时表示将数据页填满。只有当不会对数据进行更改时(例如
只读表中)才用此设置。值越小则数据页上的空闲空间越大,这样可以减少在索引增长过程中进行页分裂的需要,但这一操作需要占用更多的硬盘空间。填充因子指定不当,会降低数据库的读取性能,其降低量与填充因子设置值成反比。

索引SQL语法

CREATE [UNIQUE] [CLUSTERED| NONCLUSTERED ]
INDEX index_name ON { table | view } ( column [ ASC | DESC ] [ ,...n ] )
[with[PAD_INDEX][[,]FILLFACTOR=fillfactor]
[[,]IGNORE_DUP_KEY]
[[,]DROP_EXISTING]
[[,]STATISTICS_NORECOMPUTE]
[[,]SORT_IN_TEMPDB]
]
[ ON filegroup ]

CREATE
INDEX命令创建索引各参数说明如下:

UNIQUE:用于指定为表或视图创建唯一索引,即不允许存在索引值相同的两行。

CLUSTERED:用于指定创建的索引为聚集索引。

NONCLUSTERED:用于指定创建的索引为非聚集索引。

index_name:用于指定所创建的索引的名称。

table:用于指定创建索引的表的名称。

view:用于指定创建索引的视图的名称。

ASC|DESC:用于指定具体某个索引列的升序或降序排序方向。

Column:用于指定被索引的列。

PAD_INDEX:用于指定索引中间级中每个页(节点)上保持开放的空间。

FILLFACTOR =
fillfactor:用于指定在创建索引时,每个索引页的数据占索引页大小的百分比,fillfactor的值为1到100。

IGNORE_DUP_KEY:用于控制当往包含于一个唯一聚集索引中的列中插入重复数据时SQL
Server所作的反应。

DROP_EXISTING:用于指定应删除并重新创建已命名的先前存在的聚集索引或者非聚集索引。

STATISTICS_NORECOMPUTE:用于指定过期的索引统计不会自动重新计算。

SORT_IN_TEMPDB:用于指定创建索引时的中间排序结果将存储在
tempdb 数据库中。

ON
filegroup:用于指定存放索引的文件组。

   例子:

--表bigdata创建一个名为idx_mobiel的非聚集索引,索引字段为mobiel
create index idx_mobiel
on bigdata(mobiel) 

--表bigdata创建一个名为idx_id的唯一聚集索引,索引字段为id
--要求成批插入数据时忽略重复值,不重新计算统计信息,填充因子为40
create unique clustered index idx_id
on bigdata(id) 
with pad_index,
fillfactor=40,
ignore_dup_key,
statistics_norecompute

  管理索引

Exec sp_helpindex BigData   --查看索引定义

Exec sp_rename 'BigData.idx_mobiel','idx_big_mobiel'  --将索引名由'idx_mobiel' 改为'idx_big_mobiel'

drop index BigData.idx_big_mobiel  --删除bigdata表中的idx_big_mobiel索引

dbcc showcontig(bigdata,idx_mobiel) --检查bigdata表中索引idx_mobiel的碎片信息

dbcc indexdefrag(Test,bigdata,idx_mobiel)  --整理test数据库中bigdata表的索引idx_mobiel上的碎片

update statistics bigdata  --更新bigdata表中的全部索引的统计信息

索引的设计原理

  对于一张表来说索引的有无和建立什么样的索引,要取决与where字句和Join表达式中。

  一般来说建立索引的原则包括以下内容:

  • 系统一般会给主键字段自动建立聚集索引。
  • 有大量重复值且经常有范围查询和排序、分组的列,或者经常频繁访问的列,考虑建立聚集索引。
  • 在一个经常做插入操作的表中建立索引,应使用fillfactor(填充因子)来减少页分裂,同时提高并发度降低死锁的发生。如果在表为只读表,填充因子可设为100。
  • 在选择索引键时,尽可能采用小数据类型的列作为键以使每个索引页能容纳尽可能多的索引键和指针,通过这种方式,可使一个查询必需遍历的索引页面降低到最小,此外,尽可能的使用整数做为键值,因为整数的访问速度最快。

索引优化实例

建测试表

CREATE TABLE T_UserInfo 
( 
Userid varchar(20), UserName varchar(20), 
RegTime datetime, Tel varchar(20), 
)

插入100W数据

DECLARE @I INT  
DECLARE @ENDID INT  
SELECT @I = 1 
SELECT @ENDID = 1000000 --在此处更改要插入的数据,重新插入之前要删掉所有数据  
WHILE @I <= @ENDID  
BEGIN  
INSERT INTO T_UserInfo  
SELECT 'ABCDE'+CAST(@I AS VARCHAR(20))+'EF','李'+CAST(@I AS VARCHAR(20)),  
GETDATE(),'876543'+CAST(@I AS VARCHAR(20))  
SELECT @I = @I + 1  
END  

 情况一:无建立索引查询

SET STATISTICS PROFILE ON 
SET STATISTICS IO ON 
SET STATISTICS TIME ON 

SELECT * FROM T_UserInfo AS tui WHERE tui.UserName='李10000'

SET STATISTICS PROFILE OFF 
SET STATISTICS IO OFF 
SET STATISTICS TIME OFF

结果为:

图片 21

情况二:创建聚集索引后查询

建聚集索引

CREATE CLUSTERED INDEX INDEX_Userid ON T_UserInfo (UserName) 

查询

SET STATISTICS PROFILE ON 
SET STATISTICS IO ON 
SET STATISTICS TIME ON 

SELECT * FROM T_UserInfo AS tui WHERE tui.UserName='李10000'

SET STATISTICS PROFILE OFF 
SET STATISTICS IO OFF 
SET STATISTICS TIME OFF

结果为:

图片 22

情况三:创建非聚集索引后查询

创建非聚集索引

CREATE NONCLUSTERED INDEX INDEX_Userid ON T_UserInfo (UserName) 

查询

SET STATISTICS PROFILE ON 
SET STATISTICS IO ON 
SET STATISTICS TIME ON 

SELECT * FROM T_UserInfo AS tui WHERE tui.UserName='李10000'

SET STATISTICS PROFILE OFF 
SET STATISTICS IO OFF 
SET STATISTICS TIME OFF

结果为:

图片 23

总结:

实现同一查询功能的SQL写法可能会有多种,如果判断哪种最优化,如果仅仅是从时间上来测,会受很多外界因素的影响,而我们明白了MSSQL如何去执行,通过IO逻辑读、通过查看图示的查询计划、通过其优化后而执行的SQL语句,才是优化SQL的真正途径。

提醒:数据量的多少有时会影响MSSQL对同一种查询写法语句的执行计划,这一点在非聚集索引上特别明显,还有就是在多CPU与单CPU下,在多用户并发情况下,同一写法的查询语句执行计划会有所不同,这个就需要大家有机会去试验。

查看SQL语句执行时间

程序优化过程中,往往需要分析所写的SQL语句是否已经优化过了,服务器的响应时间有多快,这个时候就需要用到SQL的STATISTICS状态值来查看了。

通过设置STATISTICS我们可以查看执行SQL时的系统情况。选项有PROFILE,IO
,TIME。

  【介绍】

  SET STATISTICS PROFILE
ON:显示分析、编译和执行查询所需的时间(以毫秒为单位)。 
  SET STATISTICS IO
ON:报告与语句内引用的每个表的扫描数、逻辑读取数(在高速缓存中访问的页数)和物理读取数(访问磁盘的次数)有关的信息。 
  SET STATISTICS TIME
ON:显示每个查询执行后的结果集,代表查询执行的配置文件。

  【使用方法】

  SET STATISTICS PROFILE ON 
  SET STATISTICS IO ON 
  SET STATISTICS TIME ON 
   –你的SQL脚本开始–
  SELECT [TestCase] FROM [TestCaseSelect] 
  –你的SQL脚本结束–
  SET STATISTICS PROFILE OFF 
  SET STATISTICS IO OFF 
  SET STATISTICS TIME OFF

  【执行效果】

  图片 23

通过手工添加语句,计算执行时间来查看执行语句花费了的时间,以判断该条SQL语句的效率如何:

  declare @d datetime
  set @d=getdate()
  –你的SQL脚本开始–
  SELECT [TestCase] FROM [TestCaseSelect] 
  –你的SQL脚本结束–
  select [语句执行花费时间(毫秒)]=datediff(ms,@d,getdate())

发表评论

电子邮件地址不会被公开。 必填项已用*标注

网站地图xml地图