SQL Server优化50法

原文出处 查询速度慢的原因很多,常见如下几种: 没有索引或者没有用到索引(这是查询慢最常见的问题,是程序设计的缺陷) I/O吞吐量小,形成了瓶颈效应。 没有创建计算列导致查询不优化。 内存不足 网络速度慢 查询出的数据量过大(可以采用多次查询,其他的方法降低数据量) 锁或者死锁(这也是查询慢最常见的问题,是程序设计的缺陷) sp_lock,sp_who,活动的用户查看,原因是读写竞争资源。 返回了不必要的行和列 查询语句不好,没有优化

SQL Server T-SQL Samples

Ø 基本常用查询 –select select * from student; –all 查询所有 select all sex from student; –distinct 过滤重复 select distinct sex from student; –count 统计 select count(*) from student; select count(sex) from student; select count(distinct sex) from student; –top 取前N条记录 select top 3 * from student; –alias column name 列重命名 select id as 编号, name ‘名称’, sex… Continue reading SQL Server T-SQL Samples

SQL Server中的加密

原文出处 简介 加密是指通过使用密钥或密码对数据进行模糊处理的过程。在SQL Server中,加密并不能替代其他的安全设置,比如防止未被授权的人访问数据库或是数据库实例所在的Windows系统,甚至是数据库所在的机房,而是作为当数据库被破解或是备份被窃取后的最后一道防线。通过加密,使得未被授权的人在没有密钥或密码的情况下所窃取的数据变得毫无意义。这种做法不仅仅是为了你的数据安全,有时甚至是法律所要求的(像国内某知名IT网站泄漏密码这种事在中国可以道歉后不负任何责任了事,在米国妥妥的要破产清算)。 SQL Server中的加密简介 在SQL Server2000和以前的版本,是不支持加密的。所有的加密操作都需要在程序中完成。这导致一个问题,数据库中加密的数据仅仅是对某一特定程序有意义,而另外的程序如果没有对应的解密算法,则数据变得毫无意义。 到了SQL Server2005,引入了列级加密。使得加密可以对特定列执行,这个过程涉及4对加密和解密的内置函数 SQL Server 2008时代,则引入的了透明数据加密(TDE),所谓的透明数据加密,就是加密在数据库中进行,但从程序的角度来看就好像没有加密一样,和列级加密不同的是,TDE加密的级别是整个数据库。使用TDE加密的数据库文件或备份在另一个没有证书的实例上是不能附加或恢复的。

SQL Server死锁总结

  原文出处 http://www.cnblogs.com/happyhippy/archive/2008/11/14/1333922.html 死锁原理 根据操作系统中的定义:死锁是指在一组进程中的各个进程均占有不会释放的资源,但因互相申请被其他进程所站用不会释放的资源而处于的一种永久等待状态。死锁的四个必要条件: 互斥条件(Mutual exclusion):资源不能被共享,只能由一个进程使用。 请求与保持条件(Hold and wait):已经得到资源的进程可以再次申请新的资源。 非剥夺条件(No pre-emption):已经分配的资源不能从相应的进程中被强制地剥夺。 循环等待条件(Circular wait):系统中若干进程组成环路,该环路中每个进程都在等待相邻进程正占用的资源。 对应到SQL Server中,当在两个或多个任务中,如果每个任务锁定了其他任务试图锁定的资源,此时会造成这些任务永久阻塞,从而出现死锁;这些资源可能是:单行(RID,堆中的单行)、索引中的键(KEY,行锁)、页(PAG,8KB)、区结构(EXT,连续的8页)、堆或B树(HOBT) 、表(TAB,包括数据和索引)、文件(File,数据库文件)、应用程序专用资源(APP)、元数据(METADATA)、分配单元(Allocation_Unit)、整个数据库(DB)。一个死锁示例如下图所示: 说明:T1、T2表示两个任务;R1和R2表示两个资源;由资源指向任务的箭头(如R1->T1,R2->T2)表示该资源被改任务所持有;由任务指向资源的箭头(如T1->S2,T2->S1)表示该任务正在请求对应目标资源; 其满足上面死锁的四个必要条件: (1).互斥:资源S1和S2不能被共享,同一时间只能由一个任务使用; (2).请求与保持条件:T1持有S1的同时,请求S2;T2持有S2的同时请求S1; (3).非剥夺条件:T1无法从T2上剥夺S2,T2也无法从T1上剥夺S1; (4).循环等待条件:上图中的箭头构成环路,存在循环等待。

sql server 分区表

IF EXISTS( SELECT 1 FROM sys.databases AS d WHERE d.name = ‘Test_1′ ) DROP DATABASE Test_1 GO CREATE DATABASE [Test_1] ON PRIMARY( NAME = N’Test_1′, FILENAME = N’E:\Database\Sharding\Test_1.mdf’, SIZE = 10240KB, MAXSIZE = UNLIMITED, FILEGROWTH = 1024Kb ), FILEGROUP [Test_A]( NAME = N’Test_A’, FILENAME = ‘E:\Database\Sharding\Test_A.ndf’, SIZE = 10240kb, MAXSIZE = UNLIMITED, FILEGROWTH = 1024kB… Continue reading sql server 分区表

SQL Server调优系列进阶篇(如何维护数据库索引)

This is post 12 of 12 in the series “SQL Server调优系列” SQL Server调优系列基础篇 SQL Server调优系列基础篇(常用运算符总结——三种物理连接方式剖析) SQL Server调优系列基础篇(联合运算符总结) SQL Server调优系列基础篇(并行运算总结一) SQL Server调优系列基础篇(并行运算总结篇二) SQL Server调优系列基础篇(索引运算总结) SQL Server调优系列基础篇(子查询运算总结) SQL Server调优系列进阶篇(查询优化器的运行方式) SQL Server调优系列进阶篇(查询语句运行几个指标值监测) SQL Server调优系列进阶篇(深入剖析统计信息) SQL Server调优系列进阶篇(如何索引调优) SQL Server调优系列进阶篇(如何维护数据库索引) 技术准备 数据库版本为SQL Server2012,前几篇文章用的是SQL Server2008RT,内容区别不大,利用微软的以前的案例库(Northwind)进行分析,部分内容也会应用微软的另一个案例库AdventureWorks。 相信了解SQL Server的朋友,对这两个库都不会太陌生。 一、创建索引 当我们要开始对表进行索引的创建的时候,首先明确的是,一张表内只能创建一个聚集索引,最多可以创建最多249个非聚集索引(SQL Server2005),在SQL Server2008以后聚集索引数提升至999个,上一篇文章我们知道对于聚集索引项一般要创建上,而非聚集索引项要根据日常的T-SQL语句进行选择。 关于索引的选择是一个很考验调优能力的事情,大部分的情况下优质的索引新建全靠经验而论,有兴趣的可以点击查阅我前面的一系列关于分析查询计划的文章,掌握住里面的精髓才能有的放矢。 当然,小白级别的也可以参照如下方法尝试进行创建: 由于SQL Server有着自己的一套调优技巧,所以在我们每次运行的T-SQL语句应该怎样优化,SQL Server是了如指掌的,所以它会将缺失的索引项进行记录,用于提示使用者,尝试去建立这些索引。 主要记录在以下几个DMV中 sys.dm_db_missing_index_details sys.dm_db_missing_index_groups sys.dm_db_missing_index_group_stats sys.dm_db_missing_index_columns(index_handle)… Continue reading SQL Server调优系列进阶篇(如何维护数据库索引)

SQL Server调优系列进阶篇(如何索引调优)

This is post 11 of 12 in the series “SQL Server调优系列” SQL Server调优系列基础篇 SQL Server调优系列基础篇(常用运算符总结——三种物理连接方式剖析) SQL Server调优系列基础篇(联合运算符总结) SQL Server调优系列基础篇(并行运算总结一) SQL Server调优系列基础篇(并行运算总结篇二) SQL Server调优系列基础篇(索引运算总结) SQL Server调优系列基础篇(子查询运算总结) SQL Server调优系列进阶篇(查询优化器的运行方式) SQL Server调优系列进阶篇(查询语句运行几个指标值监测) SQL Server调优系列进阶篇(深入剖析统计信息) SQL Server调优系列进阶篇(如何索引调优) SQL Server调优系列进阶篇(如何维护数据库索引) 前言 上一篇我们分析了数据库中的统计信息的作用,我们已经了解了数据库如何通过统计信息来掌控数据库中各个表的内容分布。不清楚的童鞋可以点击参考。 作为调优系列的文章,数据库的索引肯定是不能少的了,所以本篇我们就开始分析这块内容,关于索引的基础知识就不打算深入分析了,网上一搜一片片的,本篇更侧重的是一些实战项内容展示,希望通过本篇文章各位看官能在真正的场景中找到合适的解决方法足以。 对于索引的使用,我希望的是遇到问题找到合适的解决方法就可以,切勿乱用!!! 本篇在分析出索引的优越性的同时也将负面影响展现出来。 技术准备 数据库版本为SQL Server2012,前几篇文章用的是SQL Server2008RT,内容区别不大,利用微软的以前的案例库(Northwind)进行分析,部分内容也会应用微软的另一个案例库AdventureWorks 相信了解SQL Server的朋友,对这两个库都不会太陌生。 概念理解 所谓的索引同SQL Server中的其它类型的数据页一样,也是固定的8KB(8192字节),存储方式同为B-Tree结构,索引B树中的每一页称为一个索引节点。B树顶端节点为根节点。索引中的底层节点称为叶节点。根节点与叶节点之间的任何索引统称为中间级。 算了,描述起来太麻烦,联机丛书上截个图直观的展示结构:

SQL Server调优系列进阶篇(深入剖析统计信息)

This is post 10 of 12 in the series “SQL Server调优系列” SQL Server调优系列基础篇 SQL Server调优系列基础篇(常用运算符总结——三种物理连接方式剖析) SQL Server调优系列基础篇(联合运算符总结) SQL Server调优系列基础篇(并行运算总结一) SQL Server调优系列基础篇(并行运算总结篇二) SQL Server调优系列基础篇(索引运算总结) SQL Server调优系列基础篇(子查询运算总结) SQL Server调优系列进阶篇(查询优化器的运行方式) SQL Server调优系列进阶篇(查询语句运行几个指标值监测) SQL Server调优系列进阶篇(深入剖析统计信息) SQL Server调优系列进阶篇(如何索引调优) SQL Server调优系列进阶篇(如何维护数据库索引) 前言 经过前几篇的分析,其实大体已经初窥到SQL Server统计信息的重要性了,所以本篇就要祭出这个神器了。 该篇内容会很长,坐好板凳,瓜子零食之类… 不废话,进正题 技术准备 数据库版本为SQL Server2008R2,利用微软的以前的案例库(Northwind)进行分析,部分内容也会应用微软的另一个案例库AdventureWorks 相信了解SQL Server的朋友,对这两个库都不会太陌生。 概念理解 关于SQL Server中的统计信息,在联机丛书中是这样解释的 查询优化的统计信息是一些对象,这些对象包含与值在表或索引视图的一列或多列中的分布有关的统计信息。查询优化器使用这些统计信息来估计查询结果中的基数或行数。通过这些基数估计,查询优化器可以创建高质量的查询计划。例如,查询优化器可以使用基数估计选择索引查找运算符而不是耗费更多资源的索引扫描运算符,从而提高查询性能。 其实关于统计信息的作用通俗点将就是:SQL Server通过统计信息理解库中每张表的数据内容项分布,知道里面数据“长得啥德行,做到心中有数”,这样每次查询语句的时候就可以根据表中的数据分布,基本能定位到要查找数据的内容位置。 比如,我记得我以前有篇文章写过一个相同的查询语句,但是产生了完全不同的查询计划,这里回顾下,基本如下: SELECT * FROM… Continue reading SQL Server调优系列进阶篇(深入剖析统计信息)

SQL Server调优系列进阶篇(查询语句运行几个指标值监测)

This is post 9 of 12 in the series “SQL Server调优系列” SQL Server调优系列基础篇 SQL Server调优系列基础篇(常用运算符总结——三种物理连接方式剖析) SQL Server调优系列基础篇(联合运算符总结) SQL Server调优系列基础篇(并行运算总结一) SQL Server调优系列基础篇(并行运算总结篇二) SQL Server调优系列基础篇(索引运算总结) SQL Server调优系列基础篇(子查询运算总结) SQL Server调优系列进阶篇(查询优化器的运行方式) SQL Server调优系列进阶篇(查询语句运行几个指标值监测) SQL Server调优系列进阶篇(深入剖析统计信息) SQL Server调优系列进阶篇(如何索引调优) SQL Server调优系列进阶篇(如何维护数据库索引) 前言 上一篇我们分析了查询优化器的工作方式,其中包括:查询优化器的详细运行步骤、筛选条件分析、索引项优化等信息。 本篇我们分析在我们运行的过程中几个关键指标值的检测。 通过这些指标值来分析语句的运行问题,并且分析其优化方式。 通过本篇我们可以学习到调优中经常利用的几个利器! 废话少说,开始本篇的正题。 技术准备 数据库版本为SQL Server2008R2,利用微软的一个更简洁的案例库(Northwind)进行分析。 利器一、IO统计 通过这个IO统计能为我们分析出当前查询语句所要扫描的数据页的数量。这里面有几个重要的概念,我们依次分析。 方法很简单,一行代码搞定: SET STATISTICS IO ON 来看个例子 SET STATISTICS IO… Continue reading SQL Server调优系列进阶篇(查询语句运行几个指标值监测)

SQL Server调优系列进阶篇(查询优化器的运行方式)

This is post 8 of 12 in the series “SQL Server调优系列” SQL Server调优系列基础篇 SQL Server调优系列基础篇(常用运算符总结——三种物理连接方式剖析) SQL Server调优系列基础篇(联合运算符总结) SQL Server调优系列基础篇(并行运算总结一) SQL Server调优系列基础篇(并行运算总结篇二) SQL Server调优系列基础篇(索引运算总结) SQL Server调优系列基础篇(子查询运算总结) SQL Server调优系列进阶篇(查询优化器的运行方式) SQL Server调优系列进阶篇(查询语句运行几个指标值监测) SQL Server调优系列进阶篇(深入剖析统计信息) SQL Server调优系列进阶篇(如何索引调优) SQL Server调优系列进阶篇(如何维护数据库索引) 前言 前面我们的几篇文章介绍了一系列关于运算符的基础介绍,以及各个运算符的优化方式和技巧。其中涵盖:查看执行计划的方式、几种数据集常用的连接方式、联合运算符方式、并行运算符等一系列的我们常见的运算符。有兴趣的童鞋可以点击查看。 本篇介绍在SQL Server中查询优化器的工作方式,也就是一个好的执行计划的形成,是如何评估出来的,作为该系列的进阶篇。 废话少说,开始本篇的正题。 技术准备 数据库版本为SQL Server2008R2,利用微软的一个更简洁的案例库(Northwind)进行分析。 正文内容 在我们将写好的一个T-SQL语句抛给SQL Server准备执行的时候,首选要经历的过程就是编译过程,当然如果此语句以前在SQL Server中执行过,那么将检测是否存在已经缓存的编译过的执行计划,用以重用。 但是,执行编译的过程需要执行一系列的优化过程,关于优化过程大致分为两个阶段: 1、首先,SQL Server对我们写的T-SQL语句先执行一些简化,通常由查询本身来寻找交互性及重新安排操作的顺序。 在此过程中,SQL Server侧重于语句写法调整,而不过多的考虑成本或者分析索引可用性的等,最重要的目标就是产生一个有效的查询。 然后,SQL Server才会加载元数据,包括索引的统计信息,进入第二个阶段。 2、在这个阶段才是SQL… Continue reading SQL Server调优系列进阶篇(查询优化器的运行方式)