MSSQL DBA 精华总结[处理对象名称中特殊字符]

Cherish_j_wang

~贝贝~ 发表于 2012-3-7 09:25
呵呵，支持原创哦，版主以及加精华了，本帖将推荐上明天的itpub门户社区推荐位置，本帖还可参与本月的精华中 ...

哈哈，送门票当然不错啦。~~~~大家顶起嘛。。。顶得多，分享得多哈

Cherish_j_wang

不好意思，昨天晚上改帖子Bug的时候，导致附件没有上传成功，Download下来是空的sql文件，今天已经重新上传啦。~~

Cherish_j_wang

MSSQL DBA 常用脚本总结

之MSSQL查看磁盘使用信息

背景
DBA在工作中经常会遇到下面的需求：
在当前的系统中，我们的磁盘空间使用状况如何，需要回答如下问题

• 当前Windows Server有哪些Driver ？
• 每个Driver上总的空间大小是多少？
• 使用了多少磁盘空间？
• 剩余多少个percent？
  

脚本
直接上脚本：
DiskSpace.sql (2.3 KB, 下载次数: 1445)

2012-3-8 22:02 上传

点击文件名下载附件

1. USE master
   
2. GO
   
3. 
   
4. declare
   
5. @drive sysname
   
6. ;
   
7. select
   
8. @drive = NULL --null: all the drives
   
9. ;
   
10. 
    
11. if OBJECT_ID('tempdb.dbo.#Disk','u') is not null
    
12. drop table #Disk
    
13. 
    
14. CREATE TABLE #Disk
    
15. (
    
16. driver char(1)
    
17. ,freespace nvarchar(255)
    
18. ,totalspace nvarchar(255)
    
19. ,freeprecent nvarchar(5)
    
20. )
    
21. DECLARE @TotalDisk TABLE
    
22. (
    
23. t nvarchar(100)
    
24. )
    
25. 
    
26. DECLARE
    
27. @driver nvarchar(5)
    
28. ,@totalsize nvarchar(50)
    
29. ;
    
30. INSERT INTO #Disk
    
31. (
    
32. driver
    
33. ,freespace
    
34. )
    
35. EXEC master.sys.xp_fixeddrives
    
36. 
    
37. --------============open xp_cmdshell
    
38. IF EXISTS (
    
39. SELECT TOP 1 *
    
40. FROM sys.configurations WITH (NOLOCK)
    
41. WHERE name='xp_cmdshell' and value=0)
    
42. BEGIN
    
43. EXEC master.dbo.sp_configure 'show advanced options', 1
    
44. RECONFIGURE WITH OVERRIDE
    
45. EXEC master.dbo.sp_configure 'xp_cmdshell', 1
    
46. RECONFIGURE WITH OVERRIDE
    
47. 
    
48. END
    
49. --------============end open xp_cmdshell
    
50. 
    
51. DECLARE disk_cur CURSOR LOCAL STATIC FORWARD_ONLY READ_ONLY
    
52. FOR
    
53. SELECT
    
54. driver
    
55. FROM #Disk
    
56. 
    
57. OPEN disk_cur
    
58. FETCH NEXT FROM disk_cur INTO @driver
    
59. 
    
60. WHILE @@FETCH_STATUS=0
    
61. BEGIN
    
62. INSERT INTO @TotalDisk
    
63. EXEC('xp_cmdshell ''wmic LogicalDisk WHERE "Caption='''''+@driver+':''''" GET FreeSpace ,SIZE /VALUE''' )
    
64. SELECT
    
65. @totalsize=t
    
66. FROM @TotalDisk
    
67. WHERE
    
68. t LIKE 'Size%'
    
69. 
    
70. if(@totalsize is not null and LEN(@totalsize)>4)
    
71. BEGIN
    
72. SET @totalsize=REPLACE(SUBSTRING(@totalsize,CHARINDEX('=',@totalsize)+1,LEN(@totalsize)-CHARINDEX('=',@totalsize)-1),' ','')
    
73. SET @totalsize=cast(cast(RTRIM(@totalsize) as bigint)/1024/1024 as nvarchar(50))
    
74. 
    
75. UPDATE #Disk
    
76. SET totalspace=@totalsize
    
77. ,freeprecent=CAST(CAST(freespace AS bigint)*1.0/CAST(@totalsize AS bigint)*100 AS decimal(5,2))
    
78. WHERE
    
79. driver=@driver
    
80. END
    
81. 
    
82. FETCH NEXT FROM disk_cur INTO @driver
    
83. END
    
84. 
    
85. close disk_cur
    
86. deallocate disk_cur
    
87. 
    
88. ---==============query summary
    
89. SELECT
    
90. GETDATE() datetime
    
91. ,driver
    
92. ,[freespace(GB)] = cast(freespace as decimal(10,2))/1024.
    
93. ,[totalspace(GB)] = cast(totalspace as decimal(10,2))/1024.
    
94. ,[freeprecent(%)]= freeprecent
    
95. FROM #Disk
    
96. WHERE driver = ISNULL(@drive,driver);
    
97. 
    
98. if OBJECT_ID('tempdb.dbo.#Disk','u') is not null
    
99. drop table #Disk
    
100. GO

复制代码

说明
下面是在我本机的测试结果：
datetime                driver freespace(GB)                           totalspace(GB)                          freeprecent(%)
----------------------- ------ --------------------------------------- --------------------------------------- --------------
2012-03-08 22:01:54.503 C      13.7343750                              29.2958984                              46.88
2012-03-08 22:01:54.503 D      112.6279296                             119.7490234                             94.05

Cherish_j_wang

其实代码很简单，可能只是我们平时可能没有这么想过从数据库去获取Windows磁盘空间信息，因为我们已经习惯于是用Windows窗口来查看。~~~~

Cherish_j_wang

hwtong 发表于 2012-3-5 22:21
还有更多的脚本吗？能一起发出来吗，我加为精华。
供大家学习，谢谢

@hwtong：
考虑到您的建议，将这个帖子做成DBA工作分享帖~

hwtong

Cherish_j_wang 发表于 2012-3-8 20:25
@hwtong：
考虑到您的建议，将这个帖子做成DBA工作分享帖~

好的 那样就更好了。希望能多多分享点东西给大家，让大家共同进步。
分享的多了，我是有奖励的哦

Cherish_j_wang

MSSQL DBA 常用脚本总结

之更新所有数据库的用户表统计信息

背景
DBA在工作中经常会遇到下面的需求：
Action当前实例所有数据库的所有用户表(Action代表动作，比如：更新统计信息、重建索引或者是删除等，哈哈~~删除操作可千万不能在产品环境测试啊。~~)
带着这个问题，在此以“更新当前实例所有数据库的所有用户表统计信息”为例
脚本
直接上脚本：

1. use master
   
2. GO
   
3. 
   
4. declare
   
5. @sql nvarchar(max)
   
6. ;
   
7. 
   
8. set
   
9. @sql = N'
   
10. USE [?]
    
11. RAISERROR(N''----------------------------------------------------------------
    
12. Search on database: ?'', 10, 1) WITH NOWAIT
    
13. 
    
14. Exec sys.sp_MSForEachTable N''
    
15. Update Statistics * WITH FULLSCAN
    
16. raiserror(''''on table:*'''',10,1)with nowait''
    
17. ,@replacechar =N''*''
    
18. ,@whereand=N''and o.name NOT LIKE ''''#%''''''
    
19. '
    
20. ;
    
21. 
    
22. EXEC sys.sp_msforeachdb @sql,@replacechar=N'?'
    

复制代码

说明
这个脚本是将微软为公开的存储过程sp_MsForEachdb和sp_MsForEachtable组合使用来解决问题，避免了使用游标或者循环数据库的方法。
其实这个脚本是一个通用的解决方法，只要涉及到“Action当前实例所有数据库的所有用户表”的需求，均可以使用该脚本来完成。

Cherish_j_wang

MSSQL DBA 常用脚本总结

之利用Hashkey解决宽字段检索的性能问题

场景

就像你所知道的那样，宽字段(我们暂且将长度超过2000的字段)是不适合建立Index的，就算建立了Index对IO的消耗也不会少。那么，当我们不得不对超宽字段Search时（where col=constant），那么这种情况，我们如何来做优化呢？

优化思路
上面情形的优化灵感来自于Join中的Hash Join，我们是否可以建立一个Hash key字段，这个字段来自于宽字段的hash函数值，然后我们在该字段上建立Index。如此以来，我们就可以将常量的Hash值与表中的Hash值进行匹配，这样就可以大大提高查询效率。
根据这个优化思路，我们有多种方法来实现：
1.       使用Trigger来自动维护HashKey：这个太复杂，至少要实现更新和插入触发器
2.       使用计算列的方式来自动维护HashKey：这个比较简单
3.       开发人员手动维护这个HashKey（也就是在Update和Insert宽字段的时，同时生成HashKey）：这个是最简单的

注意：Hash Key很有可能存在Hash值对撞的可能性，我们下面会将如何解决这个问题

脚本
虽然我们实现的方法有多种，在此，我们以方法2为例，上脚本：
HashKey_V2.sql (2.05 KB, 下载次数: 784)

2012-3-9 20:59 上传

点击文件名下载附件

1. use test
   
2. go
   
3. 
   
4. --drop test table if exists
   
5. if OBJECT_ID('dbo.test_for_hashkey','U') is not null
   
6. drop table dbo.test_for_hashkey
   
7. GO
   
8. --create test table
   
9. create table dbo.test_for_hashkey
   
10. (
    
11. id int identity(1,1) primary key
    
12. ,SearchKeyword varchar(2000) not null
    
13. ,SearchKeyword_hashkey as checksum(SearchKeyword) PERSISTED not null
    
14. );
    
15. 
    
16. --create index of hask key column
    
17. create index ix_DBA_SearchKeyword_hashkey
    
18. ON dbo.test_for_hashkey(SearchKeyword_hashkey);
    
19. GO
    
20. 
    
21. --data init.
    
22. declare
    
23. @loop int
    
24. ,@do int
    
25. ,@SearchKeyword varchar(2000)
    
26. ;
    
27. 
    
28. select
    
29. @loop = 100000
    
30. ,@do = 1
    
31. ;
    
32. 
    
33. while @do <= @loop
    
34. begin
    
35. set
    
36. @SearchKeyword = REPLICATE(newid(),55)
    
37. ;
    
38. insert into dbo.test_for_hashkey
    
39. select @SearchKeyword
    
40. ;
    
41. 
    
42. set @do = @do + 1
    
43. end
    
44. go
    
45. 
    
46. -------------------performance testing
    
47. declare
    
48. @SearchKeyword varchar(2000)
    
49. ,@SearchKeyword_hashkey int
    
50. ;
    
51. 
    
52. select TOP 1
    
53. @SearchKeyword = SearchKeyword
    
54. FROM dbo.test_for_hashkey WITH(NOLOCK)
    
55. where id = 20120;
    
56. 
    
57. set
    
58. @SearchKeyword_hashkey = CHECKSUM(@SearchKeyword)
    
59. ;
    
60. 
    
61. --statistics time & io opening
    
62. set statistics time on
    
63. set statistics io on
    
64. 
    
65. select *
    
66. FROM dbo.test_for_hashkey WITH(NOLOCK)
    
67. where SearchKeyword = @SearchKeyword
    
68. ;
    
69. /*如果直接使用SearchKeyword来检索，性能消耗
    
70. 
    
71. Table 'test_for_hashkey'. Scan count 3, logical reads 27602, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    
72. SQL Server Execution Times:
    
73. CPU time = 109 ms, elapsed time = 121 ms.
    
74. */
    
75. 
    
76. select *
    
77. FROM dbo.test_for_hashkey WITH(NOLOCK)
    
78. where SearchKeyword_hashkey = @SearchKeyword_hashkey
    
79. --这个条件是为了防止hash对撞
    
80. and SearchKeyword = @SearchKeyword
    
81. ;
    
82. 
    
83. /*如果使用hash key来检索，性能消耗
    
84. Table 'test_for_hashkey'. Scan count 1, logical reads 5, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    
85. SQL Server Execution Times:
    
86. CPU time = 0 ms, elapsed time = 0 ms.
    
87. */
    
88. 
    
89. set statistics time off
    
90. set statistics io off
    

复制代码

说明

• Hash对撞：在大量数据测试的，可能导致Hash值对撞的可能，也就是说可能会存在不同的字段值对应于相同的hash Key。我们解决这个问题的方法是：
• 1. select *
     
  2. FROM dbo.test_for_hashkey WITH(NOLOCK)
     
  3. where SearchKeyword_hashkey = @SearchKeyword_hashkey
     
  4. --这个条件是为了防止hash对撞
     
  5. and SearchKeyword = @SearchKeyword

  复制代码
  
  
• 这个与早些时候的帖子“使用fulltext解决Like完全模糊匹配的性能问题”均是优化宽字段查询的性能问题帖子地址：http://www.itpub.net/thread-1587348-1-1.html。只不过这个帖子是解决款字段等于某常量的情况，前面的帖子是解决Like完全模糊匹配的情况。
• 性能测试结果：在10万数据的时候测试，从logical reads、CPU和时间消耗来看，优化效果很明显
  
  

优化前
/*如果直接使用SearchKeyword来检索，性能消耗
Table 'test_for_hashkey'. Scan count 3, logical reads 27602, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
   CPU time = 109 ms,  elapsed time = 121 ms.
*/

优化后
/*如果使用hash key来检索，性能消耗
Table 'test_for_hashkey'. Scan count 1, logical reads 5, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
*/

paulyao911

好帖，顶起来，希望看到更多分享～

周应侯

这个也有bug~