维护Sql Server中表的索引等常用命令

--select left('claro',2) 整理与西安 2009-02-09 23:18:19.640
--Microsoft SQL Server 2005 - 9.00.3042.00
-- (Intel X86)   Feb  9 2007 22:47:07   Copyright (c) 1988-2005
-- Microsoft Corporation  Express Edition on Windows NT 5.1 (Build 2600: Service Pack 3)

--维护Sql Server中表的索引

--第一步：查看是否需要维护，查看扫描密度/Scan Density是否为100%
　　declare @table_id int
　　set @table_id=object_id('表名')
　　dbcc showcontig(@table_id)
/*--example:
select * from sys.objects where type='U'
dbcc showcontig(chktab)
--
--DBCC SHOWCONTIG scanning 'chktab' table...
--Table: 'chktab' (1195151303); index ID: 0, database ID: 1
--TABLE level scan performed.
--- Pages Scanned................................: 2
--- Extents Scanned..............................: 2
--- Extent Switches..............................: 1
--- Avg. Pages per Extent........................: 1.0
--- Scan Density [Best Count:Actual Count].......: 50.00% [1:2]
--- Extent Scan Fragmentation ...................: 50.00%
--- Avg. Bytes Free per Page.....................: 8073.5
--- Avg. Page Density (full).....................: 0.25%
--DBCC execution completed. If DBCC printed error messages, contact your system administrator.
*/

--第二步:重构表索引
　　dbcc dbreindex('表名',pk_索引名,100)
/*--example:
dbcc dbreindex ('chktab',pk_index,100)
*/
　　--重做第一步，如发现扫描密度/Scan Density还是小于100%则重构表的所有索引
　　--杨铮：并不一定能达100%。
　　dbcc dbreindex('表名','',100)
/*--example:
dbcc dbreindex ('chktab','',100)
*/


--SQL Server常用管理命令

--1. 查看数据库的版本
select @@version
/*
Microsoft SQL Server 2005 - 9.00.3042.00
 (Intel X86)   Feb  9 2007 22:47:07   Copyright (c) 1988-2005
 Microsoft Corporation  Express Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
*/

--2. 查看数据库所在机器操作系统参数
exec master..xp_msver
/*
Index    Name    Internal_Value    Character_Value
1    ProductName    NULL    Microsoft SQL Server
2    ProductVersion    589824    9.00.3042.00
3    Language    1033    英语(美国)
4    Platform    NULL    NT INTEL X86
5    Comments    NULL    NT INTEL X86
6    CompanyName    NULL    Microsoft Corporation
7    FileDescription    NULL    SQL Server Windows NT
8    FileVersion    NULL    2005.090.3042.00
9    InternalName    NULL    SQLSERVR
10    LegalCopyright    NULL    © Microsoft Corp. All rights reserved.
11    LegalTrademarks    NULL    Microsoft® is a registered trademark of Microsoft Corporation. Windows(TM) is a trademark of Microsoft Corporation
12    OriginalFilename    NULL    SQLSERVR.EXE
13    PrivateBuild    NULL    NULL
14    SpecialBuild    199360512    NULL
15    WindowsVersion    170393861    5.1 (2600)
16    ProcessorCount    1    1
17    ProcessorActiveMask    1    00000001
18    ProcessorType    586    PROCESSOR_INTEL_PENTIUM
19    PhysicalMemory    1270    1270 (1332133888)
20    Product ID    NULL    NULL
*/

--3. 查看数据库启动的参数
sp_configure
/*
name    minimum    maximum    config_value    run_value
Ad Hoc Distributed Queries    0    1    1    1
affinity I/O mask    -2147483648    2147483647    0    0
affinity mask    -2147483648    2147483647    0    0
Agent XPs    0    1    0    0
allow updates    0    1    0    0
awe enabled    0    1    0    0
blocked process threshold    0    86400    0    0
c2 audit mode    0    1    0    0
clr enabled    0    1    0    0
cost threshold for parallelism    0    32767    5    5
cross db ownership chaining    0    1    0    0
cursor threshold    -1    2147483647    -1    -1
Database Mail XPs    0    1    0    0
default full-text language    0    2147483647    1033    1033
default language    0    9999    0    0
default trace enabled    0    1    1    1
disallow results from triggers    0    1    0    0
fill factor (%)    0    100    0    0
ft crawl bandwidth (max)    0    32767    100    100
ft crawl bandwidth (min)    0    32767    0    0
ft notify bandwidth (max)    0    32767    100    100
ft notify bandwidth (min)    0    32767    0    0
index create memory (KB)    704    2147483647    0    0
in-doubt xact resolution    0    2    0    0
lightweight pooling    0    1    0    0
locks    5000    2147483647    0    0
max degree of parallelism    0    64    0    0
max full-text crawl range    0    256    4    4
max server memory (MB)    16    2147483647    2147483647    2147483647
max text repl size (B)    0    2147483647    65536    65536
max worker threads    128    32767    0    0
media retention    0    365    0    0
min memory per query (KB)    512    2147483647    1024    1024
min server memory (MB)    0    2147483647    0    8
nested triggers    0    1    1    1
network packet size (B)    512    32767    4096    4096
Ole Automation Procedures    0    1    0    0
open objects    0    2147483647    0    0
PH timeout (s)    1    3600    60    60
precompute rank    0    1    0    0
priority boost    0    1    0    0
query governor cost limit    0    2147483647    0    0
query wait (s)    -1    2147483647    -1    -1
recovery interval (min)    0    32767    0    0
remote access    0    1    1    1
remote admin connections    0    1    0    0
remote login timeout (s)    0    2147483647    20    20
remote proc trans    0    1    0    0
remote query timeout (s)    0    2147483647    600    600
Replication XPs    0    1    0    0
scan for startup procs    0    1    0    0
server trigger recursion    0    1    1    1
set working set size    0    1    0    0
show advanced options    0    1    1    1
SMO and DMO XPs    0    1    1    1
SQL Mail XPs    0    1    0    0
transform noise words    0    1    0    0
two digit year cutoff    1753    9999    2049    2049
user connections    0    32767    0    0
user instance timeout    5    65535    60    60
user instances enabled    0    1    1    1
user options    0    32767    0    0
Web Assistant Procedures    0    1    0    0
xp_cmdshell    0    1    0    0
*/

--4. 查看数据库启动时间
select convert(varchar(30),login_time,120) from master..sysprocesses where spid=1
/*
2009-02-09 18:25:10
*/

--5. 查看数据库服务器名和实例名
print 'Server Name...............：' + convert(varchar(30),@@SERVERNAME)
--Server Name...............：CLARO/SQLEXPRESS
print 'Instance..................：' + convert(varchar(30),@@SERVICENAME)
--Instance..................：SQLEXPRESS


--6. 查看所有数据库名称及大小
sp_helpdb
/*
name    db_size    owner    dbid    created    status    compatibility_level
master          5.25 MB    sa    1    Apr  8 2003    Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=611, Collation=Chinese_PRC_CI_AS, SQLSortOrder=0, IsAutoCreateStatistics, IsAutoUpdateStatistics    90
model          2.94 MB    sa    3    Apr  8 2003    Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=611, Collation=Chinese_PRC_CI_AS, SQLSortOrder=0, IsAutoCreateStatistics, IsAutoUpdateStatistics    90
msdb          7.44 MB    sa    4    Oct 14 2005    Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=611, Collation=Chinese_PRC_CI_AS, SQLSortOrder=0, IsAutoCreateStatistics, IsAutoUpdateStatistics, IsFullTextEnabled    90
tempdb          2.69 MB    sa    2    Feb  9 2009    Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=611, Collation=Chinese_PRC_CI_AS, SQLSortOrder=0, IsAutoCreateStatistics, IsAutoUpdateStatistics    90
testdb          4.00 MB    sa    5    Nov 12 2008    Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=611, Collation=Chinese_PRC_CI_AS, SQLSortOrder=0, IsAutoCreateStatistics, IsAutoUpdateStatistics, IsFullTextEnabled    90
*/

--7. 重命名数据库SQL
sp_renamedb 'old_dbname', 'new_dbname'

--8. 查看所有数据库用户登录信息
sp_helplogins

--9. 查看所有数据库用户所属的角色信息
sp_helpsrvrolemember
/*
ServerRole    MemberName    MemberSID
sysadmin    sa    0x01
sysadmin    BUILTIN/Administrators    0x01020000000000052000000020020000
sysadmin    NT AUTHORITY/SYSTEM    0x010100000000000512000000
sysadmin    CLARO/SQLServer2005MSSQLUser$CLARO$SQLEXPRESS    0x010500000000000515000000A1F40462507B9E5F07E53B2BF3030000
*/

--10. 修复迁移服务器时孤立用户时,可以用的fix_orphan_user脚本或者LoneUser过程
--更改某个数据对象的用户属主
sp_changeobjectowner [@objectname =] 'object', [@newowner =] 'owner'
--注意：更改对象名的任一部分都可能破坏脚本和存储过程。
--把一台服务器上的数据库用户登录信息备份出来可以用add_login_to_aserver脚本

--11. 查看某数据库下,对象级用户权限
sp_helprotect
/*
(1664 row(s) affected)
*/

--12. 查看链接服务器
sp_helplinkedsrvlogin
/*
Linked Server    Local Login    Is Self Mapping    Remote Login
CLARO/SQLEXPRESS    NULL    1    NULL
*/

--13. 查看远端数据库用户登录信息
sp_helpremotelogin
/*
Msg 15200, Level 16, State 1, Procedure sp_helpremotelogin, Line 14
There are no remote servers defined.
*/

--14. 查看某数据库下某个数据对象的大小
sp_spaceused @objname
--还可以用sp_toptables过程看最大的N(默认为50)个表

--15. 查看某数据库下某个数据对象的索引信息
sp_helpindex @objname
--还可以用SP_NChelpindex过程查看更详细的索引情况
--SP_NChelpindex @objname
--clustered索引是把记录按物理顺序排列的，索引占的空间比较少。
--对键值DML操作十分频繁的表我建议用非clustered索引和约束，fillfactor参数都用默认值。
　　
--16. 查看某数据库下某个数据对象的的约束信息
sp_helpconstraint @objname

--17. 查看数据库里所有的存储过程和函数
use @database_name
sp_stored_procedures

--18. 查看存储过程和函数的源代码
sp_helptext '@procedure_name'
/*--example:
sp_helptext 'sp_MScleanupmergepublisher'
--create procedure dbo.sp_MScleanupmergepublisher 
--as 
--    exec sys.sp_MScleanupmergepublisher_internal 
*/

--19. 查看包含某个字符串@str的数据对象名称
select distinct object_name(id) from syscomments where text like '%@str%'
--创建加密的存储过程或函数在AS前面加WITH ENCRYPTION参数
--解密加密过的存储过程和函数可以用sp_decrypt过程

--20. 查看数据库里用户和进程的信息
sp_who

--21. 查看SQL Server数据库里的活动用户和进程的信息
sp_who 'active'

--22. 查看SQL Server数据库里的锁的情况
sp_lock
--进程号(1--50)是SQL Server系统内部用的,进程号大于50的才是用户的连接进程.
--spid是进程编号,dbid是数据库编号,objid是数据对象编号

--23. 查看进程正在执行的SQL语句 /?
dbcc inputbuffer ()
推荐大家用经过改进后的sp_who3过程可以直接看到进程运行的SQL语句
sp_who5

--24. 检查死锁用sp_who_lock过程 /?
sp_who_lock

--25. 查看和收缩数据库文件的方法
--查看所有数据库文件大小
dbcc sqlperf(logspace)
/*
Database Name    Log Size (MB)    Log Space Used (%)    Status
master    1.242188    44.33962    0
tempdb    1.492188    62.04189    0
model    0.7421875    43.15789    0
msdb    1.992188    40.78431    0
testdb    0.9921875    41.53543    0
*/

--如果某些文件较大，收缩简单恢复模式数据库文章，收缩后@database_name_log的大小单位为M
--backup log @database_name with no_log
--dbcc shrinkfile (@database_name_log, 5)

--26. 分析SQL Server SQL 语句的方法：
set statistics time {on | off}
set statistics io {on | off}
--图形方式显示查询执行计划
--在查询分析器->查询->显示估计的评估计划(D)-Ctrl-L    或者点击工具栏里的图形
--文本方式显示查询执行计划
set showplan_all {on | off}
set showplan_text { on | off }
set statistics profile { on | off }