[MSSQL]维护Sql Server中表的索引等常用命令收藏

维护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 }