SQL Server DBA三十问之“答案整理篇”

1.char()是定长的字段限制 如：char(20) 我們存入Microsoft 九个字符，每次存储只能是20个，后面的空格都会包括的，如果不确定数据的数量肯定会浪费空间。
varchar()是不定長的，如存入Microsoft，每次取出只有9个字符不包括空格。至于nvarchar()是比varchar()功能更多实际上，能存入更多类型的数据
XML类型查找数据的方法：query方法用于从XML数据类型提取XML；value方法用于从XML文档中返回单个值；exist方法用于确定指定节点是否存在于XML文档中。
这几个查找数据的方法谈不上哪个效率更高，每个都有它的用途；
使用存储过程和T-sql语句最大的区别就是：存储过程在创建时就进行了编译，以后调用时不在需要编译了，速度快。而T-sql语句每次执行时都会进行编译一次的，
会浪费很多时间，而且速度慢

2.master:它包含一个系统表集合，是整个实例的中央存储库，维护登录账户，其他数据库，文件分布，系统配置设置，磁盘空间，资源消耗，端点和
链接服务器等方面的信息。它记录SQL2005初始化信息，所以它对实例极为重要。在创建，修改或删除用户数据库，更改服务器或任何数据库配置，以及修改
或添加用户账户后都要备份该数据库。
   model:它是SQL2005实例中所有新建数据库的模板。执行create database 命令时SQL2005会简单地将该模板数据库中的内容复制到新建数据库中，如果希望
新建的每个数据库都带有表，存储过程，数据库选项和许可等，那么可以在Model数据库中加入这些元素，此后再新建数据库时就会添加这些元素。
   msdb:该库供SQLserver实例－－主要是SQLServer代理使用来存储计划的任务，修改和备份、还原历史信息。对自动化任务和DTS作了某些修改的命令时需要备份它。
   tempdb:它是SQL2005用于各种操作的实例范围的临时工作空间。
   resource:它是SQL2005新引入的数据库，是只读的。包含所有的系统对象，如系统存储过程，系统扩展存储过程和系统函数等。
当master数据库被破坏并且没有可用的备份时，可以使用自动设置中的Rebuild database选项将其恢复成实例安装后的状态。必须经过认真考虑后才能使用该操作，因为它会清除包括
所有登录在内的所有服务器相关的配置，从而不得不从头开始重做所有的事情。

3.tempdb的用途：1)存储专用和全局临时变量，不考虑数据库上下文；
2)与Order by 子句，游标，Group by子句和Hash计划相关的工作表
3)显式创建的临时对象，如存储过程，游标，表和表变量。
4)如果启动快照隔离功能，记录所有版本的更新记录。
5)指定Sort_in_tempdb时创建或重建索引操作时的临时排序结果
如果TempDB异常变大，可能的原因是数据被频繁的使用tempdb数据库，出现了瓶颈。这种现象是十分罕见的。可以考虑在同一台服务器上安装多个SQL2005实例，并将数据库分为多个
实例。因为每个实例都有它自己的tempdb，所以这样就会有效地分散tempdb的使用。

4.聚焦索引：必须按顺序存储数据
非聚焦索引：它可以按非顺序存储数据的
包含性列索引：指包含索引键值之外的其他的索引
索引视图：第五题
全文索引：它是为了在文本字符串中进行模式检索用的
XML索引：用于快速访问XML数据的。
索引优点：查询速度快　缺点：索引建立不挡插入数据时浪费时间。
如何为SQL语句创建合适的索引：通常是把排序，聚焦函数，分组，联合查询，where 条件的字段作为建立索引的候选字段。
索引创建时有哪些需要注意的项：这个我面试时也被问过！！！！！！
你创建的索引是否被使用：它真正取决于同其他索引和聚焦索引的有效性比较。优化器然后确定是否使用该索引。因为索引是一种辅助结构，而且只有在优化器
确定他们能够提高查询性能后才使用，为了确定索引的有效性可以使用：DBCC　Statistics命令，它可确定哪个索引在被使用，哪个没有被使用。具体里面的值你百度吧。
重新组织与重新生成；
优化索引：通常需要对应用非常了解。索引优化经常由查看配置文件输出引起，而且首先处理涉及最多读操作的SQL语句。因为索引的主要好处是降低获取数据所需的
I/O操作数，所以首先处理执行许多读操作的SQL语句通常很有效。

5.能创建索引，好处：可以改善查询性能。　坏处：会相应的增加维护索引的开销
　　区别：1)对视图创建的第一个索引必须是唯一聚焦索引 2)该视图必须是使用schemabinding选项定义的。架构绑定
将视图与底层基表的架构进行绑定。3)视图所引用的基表必须与该视图位于同一数据库中，并且与该视图有着相同的所有者 4)视图所引用的表和用户定义函数
在视图中必须通过两段式名称引用，不允许单段式，三段，和四段式名称 ；对表建立索引你应该知道吧。

6.
我的MSDB数据库中没有全部的表；
sys.all_columns，sys.all_objects，sys.columns；
set statistics io 
set statistics time  来获取

7.个人认为先将两千万数据存储在本地，然后在导其他服务器中，回来删除，呵呵

9.临时表和表变量的用法
A. 如果语句很复杂，连接太多，可以考虑用临时表和表变量分步完成
B. 如果需要多次用到一个大表的同一部分数据，考虑用临时表和表变量暂存这部分数据.
C. 如果需要综合多个表的数据，形成一个结果，可以考虑用临时表和表变量分步汇总这多个表的数据
D. 其他情况下，应该控制临时表和表变量的使用.
E. 关于临时表和表变量，这个选择主要是看放在临时表的数据量，在较多的情况下，临时表的速度反而更快.
F. 关于临时表产生使用select into和create table + insert into 的选择，一般情况下，select into 会比create table + insert into 的方法快很多，但是，select into 会锁定tempdb的系统表sysobjects,sysindexs,syscolumns，在多用户并发环境下，容易阻塞其他进程，所以在并发系统中，尽量使用create table + insert into ，而大数据量的单个语句使用中，使用select into.
G. 注意排序规则，用create table 建立的临时表，如果不指定字段的排序规则，会选择tempdb的默认排序规则.
而CTE也相当于临时表，只不过是把复杂的语句整合了。

10.非提交读，已提交读（默认的），已提交读快照（数据库选项），可重复读，快照隔离，可序列化
共享，更新，排他，意向锁。四个。
为实现新的快照隔离级别行为－－－通过已提交读快照和快照隔离，使用了一个称作为行版本控制的新功能。这两个快照选项是不同的：已提交读快照只影响
语句级别的锁定行为，而快照隔离影响整个事务。它们都使用行版本控制创建自己修改数据的快照。方法是将修改前的数据图像副本存储在tempdb中，以便从
tempdb访问一致的数据快照视图，而不会阻塞实际表数据的写，也不会锁定实际的表数据。　

 

11.--找到死锁与阻塞的原因的方法:
if exists (select * from dbo.sysobjects
where id = object_id(N'[dbo].[sp_who_lock]')
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_who_lock]
GO
--说明 : 查看数据库里阻塞和死锁情况
use master
go
create procedure sp_who_lock
as
begin
declare @spid int,@bl int,
@intTransactionCountOnEntry  int,
@intRowcount   int,
@intCountProperties   int,
@intCounter      int
create table #tmp_lock_who (
id int identity(1,1),
spid smallint,
bl smallint)
IF @@ERROR<>0 RETURN @@ERROR
insert into #tmp_lock_who(spid,bl) select  0 ,blocked
from (select * from sysprocesses where  blocked>0 ) a
where not exists(select * from (select * from sysprocesses
where  blocked>0 ) b
where a.blocked=spid)
union select spid,blocked from sysprocesses where  blocked>0
IF @@ERROR<>0 RETURN @@ERROR
-- 找到临时表的记录数
select     @intCountProperties = Count(*),@intCounter = 1
from #tmp_lock_who
IF @@ERROR<>0 RETURN @@ERROR
if    @intCountProperties=0
select '现在没有阻塞和死锁信息' as message
-- 循环开始
while @intCounter <= @intCountProperties
begin
-- 取第一条记录
select     @spid = spid,@bl = bl
from #tmp_lock_who where Id = @intCounter
begin
if @spid =0
select '引起数据库死锁的是: '+ CAST(@bl AS VARCHAR(10))
+ '进程号,其执行的SQL语法如下'
else
select '进程号SPID：'+ CAST(@spid AS VARCHAR(10))+ '被'
+ '进程号SPID：'+ CAST(@bl AS VARCHAR(10)) +'阻塞,其当前进程执行的SQL语法如下'
DBCC INPUTBUFFER (@bl )
end
-- 循环指针下移
set @intCounter = @intCounter + 1
end
drop table #tmp_lock_who
return 0
end

--需要的时候直接调用,就可以查出引起死锁的进程和SQL语句.

exec sp_who_lock 
发现问题后：就进行语句的优化，避免来再次出现上面现象。Profiler跟踪时主要是看：TextData，Applicationname，Username，Loginname，CpU，Read and write
Duration(这个很重要),spid

12.我经验不多，具体还得请叫小F，晴天等等高手们。
13.分区表的目的是将同一张表中的数据分布在多个物理位置，以提高访问性能，大多数情况下，将根据某个特定列中的一系列值进行水平分区，分区的物理
位置为多个文件组。
　分区视图横向联接跨一个或多个服务器的一组成员表中的已分区数据，使这些数据表现为好像来自一个表，分区视图使用UNION ALL子句将所有成员表的Select
语句的结果合并到单个结果集中。
　实现分区表的主要原因是更易于管理同一个表中的不同数据集。利用分区表，能有效的改善可管理性，主要体现在以下：
1)能够实现单独的备份策略。不同的数据集可能有不同的备份要求
2)可控制存储介质。对表进行分区可使你根据数据的存取要求为数据选择相应的存储。
3)实现索引管理。除了分区表之外，还可以分区其索引。这允许你按分区重新组织，优化和重建索引。这比管理整个索引更快且干扰更低。此外，分区索引
可使碎片最小。
　如果分区视图中的表位于不同的服务器上，或者位于一台多处理计算机上，则可以对查询中所涉及的每个表进行并行扫描，从而提高查询性能。此外
可更快地执行重建索引，或备份表之类的维护任务。

15.
CheckPoint（检察点）:是一个SQLServer操作，通过将缓冲区高速缓存中的所有　已修改数据写入磁盘，使物理数据与缓冲区高速缓存的当前状态同步。检查点
不会像惰性编写器那样将页面放回自由列表中。此外，检查点线程会强制把缓冲区高速缓存中所有等待的事务日志记录写入磁盘上的日志文件中。

LazyWriter（惰性编写器）：负责定期检察，已确保空闲缓冲区列表不低于特定大小（该值取决于缓冲区调高速缓存的大小），如果自由列表已经小于该值，那么
惰性编写器将扫描缓存，回收不使用的页面，并释放脏页，将引用计数器设为0.

DDL：是建立在数据库级的，一般很少用。
DML：分为After和Instead of　他们是建立在表与表之间的。1)当约束所支持的功能无法满足应用程序的功能性需求时，则该触发器最为有用的；
2)触发器可将更改级联传播到数据库中的相关表。但是，通过级联引用
完整性约束可更有效的执行这些更改。

 

16. 如果你购买的服务器不具备支持群集的硬件，需要怎么才能做到实现高可用性？
在SQL2005Enterprise Edition中，数据库镜像是另一种实现故障转移群集的高可用性解决方案.数据库镜像支持自动故障转移，但无需使用具有群集能力的硬件，因此，它为实现故障转移群集提供了一种低成本方法.
数据库镜像通过标准硬件就可以实现，且其所有管理任务都在SQLServer内部进行
数据库镜像需要多个SQLServer实例，这些实例应当被安装在独立的计算机上以防止发生服务器故障.
数据库镜像中的服务器角色
主体服务器：它承载数据库的有效副本（称为＂主体数据库＂）和服务客户端的请求.主体服务器在将所有事务应用于主体数据库之前将其转发到镜像服务器.
镜像服务器：它承载主体数据库的副本（称为＂镜像数据库＂）并应用由主体数据库转发的事务，以保持镜像数据库与主体数据库的同步.
见证服务器：是数据库镜像解决方案的一个可选组件.如果存在见证服务器，则它将负责监视主体服务器和镜像服务器，以确保持续连通性和参与到镜像会话中（此过程称为＂仲裁＂）.如果任何主体服务器丢失了仲裁，则见证服务器将为某个镜像服务器分配主体服务器角色，并在必要时促成从主体服务器到镜像服务器的自动故障转移.见证服务器是实现自动故障转移所必需的，但是一个见证服务器可同时支持多个镜像会话，因为其工作强度并还是很高.

　　　使用数据库镜像的场合：
　　　单个数据库要求冗余时
　　　不准备投资具有群集能力的硬件时
　　　需要比故障恢复群集少的管理开销时

日志传送是通过使用标准硬件来创建备用服务器的低成本方法.日志传送首先将主体服务器上数据库的完整备份还原到辅助服务器上.然后周期性地将主体服务器上的事务日志应用到备用服务器上.日志传送可用于用户数据库，但不能用于系统数据库.
　　　日志传送是一项高可用性的技术，通过它将主体服务器的事务日志周期性地还原到备用服务器上.你可以设定日志备份的出现周期，以使其充分地满足你对可用性和性能的要求，除了提供冗余外，备用服务器还提供客户端的可读查询，以减轻主体服务器的负担.
在主体服务器运行失败时，该方法不会产生自动故障转移.你必须手动将备用服务器提升为主体服务器并重新配置所有客户端以使它们连接到此备用服务器．（即新的主体服务器）
　　你可以选择性地创建一个监视服务器，它记录所有与日志传送，最近一次备份以及还原操作相关的问题.一旦某个服务器运行失败，监视服务器就会与主体服务器和备用服务器断开.
;;Oracle没用过

17.搭建步骤：http://guobaoguo.blog.163.com/blog/static/1091625820091179247175/
　三种模式区别：高可用性模式提供最健壮的覆盖。它由主体，镜像和见证服务器组成。
　高级保护模式由同步通信的主体和镜像组成。
　高性能模型仅由主体和异步通信镜像组成。
　同步镜像需要镜像接收数据。确认操作已经提交给镜像数据库，然后向主体发送回一个确认或应答，确认操作在提交给主体之前已经完成，而且客户
端正在进入下一个操作。关键概念是客户端等待，直到远程镜像服务器的操作完成。
　异步镜像是“给和去”类型的方法。数据作为可用资源发送给镜像服务器，但是客户在继续操作之前不等待发送回的确认。
查看下数据库日志的是否增长很快，并做适当的处理。其他的我也不知道。

　21.SQLServer profiler已经很强大了；DMV（动态管理视图来编写相应的代码）
　22.主要查看该SQL语句的查询性能。即：执行计划，利用它来判断优化器在哪个语句上做了很大的开销，进步做适当的处理。
　23.经验来谈
　24.每周日晚10点做一次完整备份，每天晚上10点做一次差异备份，每天每15分钟做一次事务日志备份。
　25.上面写了好像
　26.附加、分离是不考虑原数据库可用的，但也是最快的
　29.我还真有过类似的经历，就是把100多万的数据给弄没了，当时脸都绿了，幸亏是测试库，呵呵，时间点还原了。
　30.这个每次去大公司面试，人力资源的都会问。我就说两年之内一定能成为高级DBA，呵呵，虽然我是这个6月份毕业的。