SQLServer DBA 三十问之我答(第1~10题)

SQLServer DBA 三十问之我答(第1~10题)

首先声明:这是本人的答案,并非官方答案,不要盲目追捧,因为觉得这问题很具有代表性,很多人想了解,所以记下,顺便让广大园友也来给本人打打分。因为本人平时用sqlserver2000,所以大多基于sql2000回答。

原帖地址 http://www.cnblogs.com/fygh/archive/2011/10/18/2216166.html 

问题如下: 

1. char、varchar、nvarchar之间的区别(包括用途和空间占用);xml类型查找某个节点的数据有哪些方法,哪个效率高;使用存储

   过程和使用T-SQL查询数据有啥不一样;

答:char 是定长的字符类型,若长度不够自动在后面补足够的空格 ;varchar是可变长度的字符类型,具长度体根据存入的数据而定;nvarchar可理解为unicode的可变长度字符类型;

xml类型的字段没用过,因为感觉xml用不上索引; 

xml类型有索引的
1.主XML索引
主XML索引是XML数据类型字段中XML BLOB的已拆分和持久的表示形式。
说明:XML主索引可以对字段中的每个XML实例都创建数据行,而且数据行数等于XML实例中的节点数,在数据行中存储了XML实例的节点信息。
2.辅助XML索引
创建辅助XML索引的前提是必须存在主XML索引,辅助XML索引用于增强搜索的性能。辅助XML索引主要有以下几种。
PATH辅助索引:在对XML字段中的数据信息进行指定路径查询时,使用PATH辅助XML索引可以提高查询的速度。
VALUE辅助索引:在对XML字段中的数据信息进行基于值的查询时,使用VALUE辅助XML索引可以提高查询的速度。
PROPERTY辅助索引:在对XML字段中的一个或多个属性值查询时,使用PROPERTY辅助XML索引可以提高查询的速度。

在sql2000,存储过程是经过编译的,sql语句是解释型的,其实我认为存储过程的好处在于提供了一个统一的数据接口,避免到处查找sql语句,假如你有10个程序都访问同一个数据库,那你可以仅通过修改存储过程而达到优化效果,若是sql语句,那可有的忙了。

 使用存储过程和使用T-SQL查询的区别还有一个权限的问题,即可以屏蔽用户对某个对象的访问权限,从而提升安全性

2. 系统DB有哪些,都有什么作用,需不需要做备份,为什么;损坏了如何做还原(主要是master库);

答:master 主要保存系统级的信息,比如本数据库实例都有哪些数据库 ,都有哪些账号等,需备份;

model 模板,每创建一个数据库 ,都会根据这个库的结构来创建,如果改过此库,建议备份;

msdb 保存计划任务,作业之类的信息,需备份,否则会丢失作业和备份计划;

tempdb 用户对sqlserver操作时产生的临时数据依赖于此库,最常见的是临时表,不许备份;

resource 这个是sqlserver2005新增的只读数据库,保存了sqlserver的系统对象,如sys.objects,建议备份,磁盘损坏时需要; 

master的还原要依赖于sqlcmd,估计很少人经常干这事,所以这时候应该查下资料; 

 

3. 有哪些操作会使用到TempDB;如果TempDB异常变大,可能的原因是什么,该如何处理;

 答:每个sqlserver运行时所产生的临时数据都会用到tempdb,最常见的是执行sql脚本需要返回的记录集;

异常变大的原因是执行的操作返回的记录集过大造成,找出该语句优化,减少数据范围,或者分批操作这些数据; 

 

4. Index有哪些类型,它们的区别和实现原理是什么,索引有啥优点和缺点;如何为SQL语句创建合适的索引,索引创建时有哪些需要

    注意的项,如何查看你创建的索引是否被使用;如何维护索引;索引损坏如何检查,怎么修复;T-SQL有更好的索引存在,但是运行

    时并没有使用该索引,原因可能是什么;

答:聚集索引,非聚集索引;聚集索引只能有一个,非聚集可有多个,数据依赖于聚集索引来保存,如果没有聚集索引,数据是一个乱序的堆;

优点:合适的索引可有效提高查询效率;缺点:过多的索引,在insert、update 和 delete 的时候增加索引的维护成本,降低并发量;

一般索引的创建要依赖于 where 和 order by 这两个关键字,执行计划可以看出是否用到了索引;

还没遇到过索引损坏的情况,如果损坏,重建之;

用不到索引可能是索引碎片过多 ,可进行碎片整理,若不行可加强制索引with(index( 索引名 ))

  

5. 视图上我们能建索引吗,如果能建的话,会啥好处和坏处;视图上建索引和表上建索引有啥区别;

答:有索引视图,不过本人从来不用, 因为视图被定义为一个虚拟表,如果对其创建索引,需要具备一些苛刻的条件,也不易维护,所以从来不用;

 视图上建索引大概题意是索引视图,它不再是一个虚表,而是相当于一个物理表。

6. Job信息我们可以通过哪些表获取;系统正在运行的语句可以通过哪些视图获取;如何获取某个T-SQL语句的IO、Time等信息;

答:sql2000 下是通过 master.dbo.sysjobs 来查看作业信息;系统正在运行的语句可通过 master.dbo.sysprocesses 结合 dbcc inputbuffer 来查看,一般查堵塞这个很管用(关于堵塞的定位可看我的另外一篇博客,sqlserver阻塞定位  ); 

IO,在sql2000下我本人都是通过profiler看reads,duration,sql2005下有了动态视图(dmv); 

 

7. 在线系统,一个表有五千万记录,现在要你将其中的两千万条记录导入到另一台服务器的某个表中,导完后,需要将这两千万数据删

   除,你预备如何处理,优缺点是什么;

答:这个的我之前专门写了一篇博客,在这里  SQLServer DBA 三十问之我答(第7题)

 

8. 数据库服务器报磁盘空间不足,你将如何应对,要求尽快恢复;

答:如果是日志文件太大,备份日志,sqlserver会自动截断日志;

如果是数据文件过大,赶紧转移到一个足够大的硬盘上,然后再想办法拆分库,一般一个库100G应该差不多,如果太大备份还原什么的都不是很方便,至于拆分方案那不可一概而论; 

 

9. 临时表、表变量、CTE(公用表表达式)有啥区别和联系,保存位置有啥不一样,使用时如何决定选哪种;

答:临时表,保存在 tempdb中,

表变量 保存在内存中,他和临时表的区别是 一个是保存在tempdb,一个保存在内存中,

cte是sqlserver2005新增的,是把一个记录集当做一个变量来使用,他跟表变量的区别是不需要insert数据,所以应该比表变量更高效;

如果只使用一次cte应该是最好的,如果要重复多次使用同一个记录集,我个人认为还是表变量,

如果你还需要用到truncate语句来清除临时的数据,那最好用临时表,因为表变量不支持truncate,可参考我的另一篇 本质:sqlserver为什么表变量不能truncate

 

 

10. SQLServer有哪些隔离级别,默认级别是哪个;数据库有哪些主要的锁类型;行版本控制是如何实现的;

答: 隔离级别: read uncommitted,相当于给每个select 加with(nolock), 会产生脏读,为提高并发量可设置此选项;

read committed 不允许脏度,默认级别;repeatable read 锁定select的数据集;serializable 相当于在select的表上加 with(holdlock),从来没用过;

books online 里查 set transaction isolation level 可查到。 

 

 

后 语:刚开始看这题目的时候,感觉都经常用到,应该很easy,但是真正要回答的时候发现不知道该怎么表示,最后还是花了不少时间来查一些资料,也算是对自 己知识的再巩固。可能有些回答的不一定准确,如果哪位仁兄另有高见,不妨赐教一二,也可经常切磋切磋,共同进步。从本人招聘的经历来看,做asp.net 开发的能答对第1、第4题已经算是很不错的了,从这点可以看到sql是广大的程序员的弱项。

此条目发表在未分类分类目录,贴了标签。将固定链接加入收藏夹。