sqlserverDBA入门教程.docx
《sqlserverDBA入门教程.docx》由会员分享，可在线阅读，更多相关《sqlserverDBA入门教程.docx（17页珍藏版）》请在冰点文库上搜索。
sqlserverDBA入门教程
SQLServer2005DBA入门技术
一、SQLServer2005简述
1、SqlServer各个版本
SQLServer2005EnterpriseEdition（32位和64位）
EnterpriseEdition达到了支持超大型企业进行联机事务处理（OLTP）、高度复杂的数据分析、数据仓库系统和网站所需的性能水平。
EnterpriseEdition的全面商业智能和分析能力及其高可用性功能（如故障转移群集），使它可以处理大多数关键业务的企业工作负荷。
EnterpriseEdition是最全面的SQLServer版本，是超大型企业的理想选择，能够满足最复杂的要求。
该版本还推出了一种适用于32位或64位平台的120天EvaluationEdition。
SQLServer2005StandardEdition（32位和64位）
StandardEdition是适合中小型企业的数据管理和分析平台。
它包括电子商务、数据仓库和业务流解决方案所需的基本功能。
StandardEdition的集成商业智能和高可用性功能可以为企业提供支持其运营所需的基本功能。
StandardEdition是需要全面的数据管理和分析平台的中小型企业的理想选择。
SQLServer2005WorkgroupEdition（仅适用于32位）
对于那些需要在大小和用户数量上没有限制的数据库的小型企业，WorkgroupEdition是理想的数据管理解决方案。
WorkgroupEdition可以用作前端Web服务器，也可以用于部门或分支机构的运营。
它包括SQLServer产品系列的核心数据库功能，并且可以轻松地升级至StandardEdition或EnterpriseEdition。
WorkgroupEdition是理想的入门级数据库，具有可靠、功能强大且易于管理的特点。
SQLServer2005DeveloperEdition（32位和64位）
DeveloperEdition使开发人员可以在SQLServer上生成任何类型的应用程序。
它包括SQLServer2005EnterpriseEdition的所有功能，但有许可限制，只能用于开发和测试系统，而不能用作生产服务器。
DeveloperEdition是独立软件供应商（ISV）、咨询人员、系统集成商、解决方案供应商以及创建和测试应用程序的企业开发人员的理想选择。
DeveloperEdition可以根据生产需要升级至SQLServer2005EnterpriseEdition。
SQLServer2005ExpressEdition（仅适用于32位）
SQLServerExpress是一个免费、易用且便于管理的数据库。
SQLServerExpress与MicrosoftVisualStudio2005集成在一起，www.ks-可以轻松开发功能丰富、存储安全、可快速部署的数据驱动应用程序。
SQLServerExpress是免费的，可以再分发（受制于协议），还可以起到客户端数据库以及基本服务器数据库的作用。
SQLServerExpress是低端ISV、低端服务器用户、创建Web应用程序的非专业开发人员以及创建客户端应用程序的编程爱好者的理想选择。
2、SQLServer2005体系结构
1、事务日志和数据库文件
2、SQLNativeClient
3、系统数据库
（1）Resource数据库
它是SQLServer2005新增www.chinashin-
徐州网站建设
蹭网卡的一个系统数据库，该数据库包含SQLServer正常运行所需的重要系统表、元数据和存储过程，它们是只读的。
它只在安装新的补丁程序包时才发生写入操作。
C:
\ProgramFiles\MicrosoftSQLServer\MSSQL.1\MSSQL\Data\mssqlsystemresouce.mdf，每个实例只包含一个Resource数据库。
（2）master数据库
Master 数据库包含有关用户数据库的元数据（数据库配置和文件位置）、登录名以及有关实例的配置信息。
如果这个重要的数据库丢失，SQLServer将不能启动。
查询服务器安装数据库：
select*fromsys.databases
（3）tempdb数据库
Tempdb类似于数据库的交换
红血丝
银杏树文件，它用于为所有登录名保存临时对象。
Tempdb数据库在SQLServer每次重新启动时自动创建，当SQLServer停止运行时，该数据库将自动重新创建为其原始大小。
（4）model数据库
Model数据库是SQLServer创建新数据库时使用它作为模板。
创建每个数据库时，首先将model数据库中的所有对象复制到新数据库的空壳中。
（5）msdb数据库
Msdb数据库包含SQLServer代理、日志传送、SSIS和关系数据库引擎的备份和恢复系统等使用的信息。
4、架构
架构让用户能够将数据库对象根据其用途进行分组，可将架构视为数据库中对象的逻辑分组。
调用架构中的对象时，需要使用至少包含两部分的名称。
用户较熟悉dbo架构，对于给定数据库，它是默认的架构。
以前版本架构与用户名绑定。
5、同义词
同义词在数据库对象和客户端之间
徐州搬家公司
减肥产品排行榜创建一个抽象层，实质上它为数据库对象创建另一个逻辑名称。
这种抽象使得在使用链接服务器时非常方便。
SELECTColumn1,Column2
FROMLinkedServerName.DatabaseName.SchemaName.TableName
同义词不能引用另一个同义词。
6、动态管理视图
DMV和函数返回有关SQLServer实例和操作系统的信息。
DMV来获取当前连接到该实例的会话数量。
DMV来获取数据
达达棋牌
玻璃清洗机库数据文件的I/O统计。
7、SQLServer2005数据类型
（1）字符数据类型
包括varchar、char、nvarchar、nchar、text、ntext。
（2）数值数据类型
包括：
bit、tinyint、smallint、int、bigint、numberic、decimal、money、float、real。
（3）二进制数据类型
包括类型：
varbinary、binary、varbinary（max）、image等二进制数据类型用于存储二进制数据。
Image数据类型蚵在数据页外部存储最多2GB的文件，varbinary（max）它可保存最多8KB的二进制数据，其性能通常比image类型
昆山搬家公司
昆山空调维修www.ks-列好。
（4）XML
初期数据保存到text或varchar列中，最适合的XML数据类型。
（5）DateTime
Datetime和smalldatetime数据类型都用于存储日期和时间数据。
（6）CLR集成
3、SQLServer版本
SQLServer2005支持操作系统：
Win2000sp4+专业版（企业版除外）、服务器版、win2003Serversp1+、winXP家庭版（工作组、标准版、企业版除外）、专业版sp2+
SQL速成版，32位内存支持1GB、最大数据库大小4GB、处理器数量1
二、SQLServer2005安装最佳实践
1、示例数据库
AdventureWorks：
C:
\ProgramFiles\MicrosoftSQLServer\90\Tools\Samples\AdventureWorksOLTP\instawdb.sql
2、测试系统
（1）SQLIO工具
示例：
（2）SQLIOStress工具
3、失败安装排错
C:
\ProgramFiles\MicrosoftSQLServer\90\SetupBootstrap\LOG
三、监视SQLServer
1、监视工具
sp_who、sp_who2、sp_lock
2、性能监视器
三个方面：
（1）处理器使用率；
（2）磁盘活动；
（3）内存使用率；
3、查看锁定信息
SELECTl.resource_type,l.resource_associated_entity_id
OBJECT_NAME（sp.OBJECT_ID）ASObjectName
l.request_status,l.request_mode,request_session_id
l.resource_description
FROMsys.dm_tran_locksl
LEFTJOINsys.partitionssp
ONsp.hobt_id=l.resource_associated_entity_id
WHEREl.resource_database_id=DB_ID（）
4、数据库中的索引使用情况
--------------------------------------------------------------------------------
IFOBJECT_ID（'dbo.IndexUsageStats'）ISNULL
CREATETABLEdbo.IndexUsageStats
（
IndexNamesysnameNULL
ObjectNamesysnameNOTNULL
user_seeksbigintNOTNULL
user_scansbigintNOTNULL
user_lookupsbigintNOTNULL
user_updatesbigintNOTNULL
last_user_seekdatetimeNULL
last_user_scandatetimeNULL
last_user_lookupdatetimeNULL
last_user_updatedatetimeNULL
StatusDatedatetimeNOTNULL
DatabaseNamesysnameNOTNULL
）
GO
----BelowquerywillgiveyouindexUSEDpertableinadatabase.----
INSERTINTOdbo.IndexUsageStats
（
IndexName
ObjectName
user_seeks
user_scans
user_lookups
user_updates
last_user_seek
last_user_scan
last_user_lookup
last_user_update
StatusDate
DatabaseName
）
SELECT
si.nameASIndexName
so.nameASObjectName
diu.user_seeks
diu.user_scans
diu.user_lookups
diu.user_updates
diu.last_user_seek
diu.last_user_scan
diu.last_user_lookup
diu.last_user_update
GETDATE（）ASStatusDate
sd.nameASDatabaseName
FROMsys.dm_db_index_usage_statsdiu
JOINsys.indexessi
ONdiu.object_id=si.object_id
ANDdiu.index_id=si.index_id
JOINsys.all_objectsso
ONso.object_id=si.object_id
JOINsys.databasessd
ONsd.database_id=diu.database_id
WHEREis_ms_shipped<>1
ANDdiu.database_id=DB_ID（）
IFOBJECT_ID（'dbo.NotUsedIndexes'）ISNULL
CREATETABLEdbo.NotUsedIndexes
（
IndexNamesysnameNULL
ObjectNamesysnameNOTNULL
StatusDatedatetimeNOTNULL
DatabaseNamesysnameNOTNULL
）
----BelowquerywillgiveyouindexwhichareNOTusedpertableinadatabase.----
INSERTdbo.NotUsedIndexes
（
IndexName
ObjectName
StatusDate
DatabaseName
）
SELECT
si.nameASIndexName
so.nameASObjectName
GETDATE（）ASStatusDate
DB_NAME（）
FROMsys.indexessi
JOINsys.all_objectsso
ONso.object_id=si.object_id
WHEREsi.index_idNOTIN（
SELECTindex_id
FROMsys.dm_db_index_usage_statsdiu
WHEREsi.object_id=diu.object_id
ANDsi.index_id=diu.index_id
）
ANDso.is_ms_shipped<>1
5、查看阻塞情况
SELECT
t1.resource_type
t1.resource_database_id
t1.resource_associated_entity_id
OBJECT_NAME（sp.OBJECT_ID）ASObjectName
t1.request_mode
t1.request_session_id
t2.blocking_session_id
FROMsys.dm_tran_locksast1
JOINsys.dm_os_waiting_tasksast2
ONt1.lock_owner_address=t2.resource_address
LEFTJOINsys.partitionssp
ONsp.hobt_id=t1.resource_associated_entity_id
6、已连接用户的信息
SELECTlogin_name,count（session_id）assession_count
FROMsys.dm_exec_sessions
GROUPBYlogin_name
7、内存使用情况
SELECT
name
type
SUM（single_pages_kb+multi_pages_kb）ASMemoryUsedInKB
FROMsys.dm_os_memory_clerks
GROUPBYname,type
ORDERBYSUM（single_pages_kb+multi_pages_kb）DESC
四、数据库索引维护
监视索引碎片：
DBCCSHOWCONFIG、Sys.dm_db_index_physical_stats
整理索引：
（1）删除并重新创建索引；
（2）ALTERINDEXREORGANIZE，如：
Alterindexallonpeoplereorganize
（3）ALTERINDEXREBUILD，如：
Alterindexallonpeoplerebuild
五、碎片化
安装避免碎片化最佳方式如下：
（1）安装操作系统；
（2）对磁盘进行碎片整理；
（3）安装任何应用程序（SQLServer）；
（4）对磁盘执行碎片整理；
（5）对最大尺寸创建数据文件和日志文件；
（6）检查碎片化状态并在必要时执行磁盘碎片整理；
（7）禁用自动增长；
（8）定期对磁盘执行碎片整理，以消除其他应用程序导致的碎片。
六、磁盘和I/O特征及内存项
1、数据分析
RAID0:
I/Osperdisk=（Reads+Writes）/磁盘数
RAID1:
I/Osperdisk=[Reads+（2*Writes）]/2
RAID5:
I/Osperdisk=[Reads+（4*Writes）]/磁盘数
RAID10:
I/Osperdisk=[Reads+（2*Writes）]//磁盘数
2、一些基本的磁盘信息
磁盘转速
旋转延时
磁道延时
寻道时间
数据传输率
8KB传输时间
总延时
5400r/min
5.5ms
6.5ms
12ms
90MB/s
88>s
12.1ms
7200
4.1
6.5
10.7
120
66
10.8
10000
3
1.5
4.5
166
48
4.6
15000
2
1.5
3.5
250
32
3.5
3、主要功能所需的I/O类型和数量
操作
随机/顺序
读/写
大小范围
CREATEDATABASE
顺序
写
512KB
备份
顺序
读/写
64KB的倍数（最多4MB）
还原
顺序
读/写
64KB的倍数（最多4MB）
DBCCCHECKDB
顺序
读
8KB-64KB
DBCCDBREINDEX（读取阶段）
顺序
读
（参见预读）
DBCCDBREINDEX（写入阶段）
顺序
写
8KB的倍数，最多128KB
DBCCSHOWCONFIG
顺序
读
8KB-64KB
4、各种内存管理选项
选项
内存少于3GB
内存超过3GB
内存16GB
PAE
否
是
是
/3GB
否
是
否
AWE
否
是
是
在有超过16GB内存时仅使用/PAE开关；
必须启用SQLServer的“”设置，SQLServer才能能够访问超过3GB的内存；
仅当物理内存超过4GB且少于16GB时才使用/3GB开关。
七、数据库导入与导出
实际操作演示
八、SQL2005常见问题
1、SQL无法启动
2、SQLServer2005中如何列所有存储过程
（1）列出所有的储存过程
以下代码列出了SQLServer2005中存在的所有储存过程。
USE[your_database_name_here];
GO
SELECT*FROMsys.all_objects
WHERE（[type]='P'OR[type]='X'OR[type]='PC'）
ORDERBY[name];
GO
（2）用户自定义存储过程
通过一个T-SQL代码演示如何获取只有用户定义的存储过程。
USE[your_database_name_here];
GO
SELECT*FROMsys.all_objects
WHERE（[type]='P'OR[type]='X'OR[type]='PC'）
AND[is_ms_shipped]=0
ORDERBY[name];
GO
（3）获取SQLServer数据库里表的占用容量大小
CREATEPROCEDUREget_tableinfoAS
ifnotexists（select*fromdbo.sysobjects
whereid=object_id（N'[dbo].[tablespaceinfo]'）
andOBJECTPROPERTY（id,N'IsUserTable'）=1）
createtabletablespaceinfo--创建结果存储表
（nameinfovarchar（50）,
rowsinfoint,reservedvarchar（20）,
datainfovarchar（20）,
index_sizevarchar（20）,
unusedvarchar（20））
deletefromtablespaceinfo--清空数据表
declare@tablenamevarchar（255）--表名称
declare@cmdsqlvarchar（500）
DECLAREInfo_cursorCURSORFOR
selecto.name
fromdbo.sysobjectsowhereOBJECTPROPERTY（o.id,N'IsTable'）=1
ando.namenotlikeN'#%%'orderbyo.name
OPENInfo_cursor
FETCHNEXTFROMInfo_cursor
INTO@tablename
WHILE@@FETCH_STATUS=0
BEGIN
ifexists（select*fromdbo.sysobjects
whereid=object_id（@tablename）andOBJECTPROPERTY（id,N
升级会员 