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

quhp1978 · 发表于 2013-2-21 13:30

感谢分享

maco_wang · 发表于 2013-2-27 23:00

谢谢楼主的技术分享！

520tjm · 发表于 2013-8-4 21:44

dennyalice2008 · 发表于 2013-12-3 13:05

支持技术分享

cike5 · 发表于 2013-12-27 13:55

这才是技术牛人的特征，比某些个老男人请多了 -- 分享点资料还吱吱扭扭，不点名了：）

paulchan · 发表于 2014-1-19 20:31

本帖最后由 paulchan 于 2014-1-19 22:44 编辑

好文章，向无私奉献的兄弟们致敬！！谢谢分享！

Cherish_j_wang · 发表于 2014-1-22 13:51

本帖最后由 Cherish_j_wang 于 2014-1-23 13:22 编辑

MSSQL DBA 精华总结
                     之使用SQL脚本部署Windows性能监视器
背景
DBA团队管理着成百上千的数据库服务器（当然这里谈的是基于Windows平台的SQL Server数据库），某一天老大突然“命令”你说“把那十几个XXX服务器未来几天的Windows性能搜集给我看看”。当接到这个命令的时候，你会怎么做？
方法一.
      1.傻傻的RDP到每台Windows打开性能监视器，设置主要的性能指标，然后跑起来
      2.几天以后，再次傻傻的RDP到几十台Server，停止性能监视器
      3.傻傻的把每个Server的监控到的文件Copy出来统计

方法二.
      我们是不是可以有一种简单的方法？这个方法只需要我在SQL Server SSMS跑一段语句，Windows就可以乖乖的部署好一个性能监视器，并且在指定的时间停止？再加上SQL Server 2008以后的SSMS支持服务器组执行SQL 语句，也就是说只要SQL脚本准备好了，我可以分分钟搞定Windows性能监视器的部署。

脚本附件
   好了，我们这里要讲的当然是简单的方法二，这个是经过工作场景测试盒经常使用的脚本总结。语句如下：

set nocount on
declare
@file_max_size_mb varchar(8)
,@perfmon_folder_path sysname
--counter conf file info
,@counter_conf_file_name sysname
,@perf_counts_string varchar(8000)
--data gather file info
,@data_gather_bat_file_name sysname
,@begin_time varchar(20)
,@end_time varchar(20)
,@data_gether_start_file_name sysname
,@perfmon_data_file_name sysname
,@perfmon_log_format varchar(10)
;
select
@file_max_size_mb = '100'
,@perfmon_folder_path = 'D:\DBA\perfmon\'--file root folder
,@perfmon_log_format = 'bincirc'-- bin|bincirc|csv|tsv|sql
--data gather file info
,@begin_time = '' --format: M/d/yyyy hh:mm:ss [AM|PM];
--null/'': start next minute
,@end_time = '' --format:M/d/yyyy hh:mm:ss [AM|PM];
--null/'': stop after starting 24 hours
--counter conf file info
,@counter_conf_file_name = 'SQLTuning_counters.cfg.txt'
,@data_gather_bat_file_name = 'step1.SQLTuning_data_gather.bat'
,@data_gether_start_file_name = 'step2.perfmon_start_data_gather.bat'
,@perfmon_data_file_name = 'perfmon_data'
,@perf_counts_string = '"\Processor(_Total)\% Processor Time"
"\SQLServer:Broker Statistics\Enqueued Transport Msgs/sec"
"\SQLServer:Broker Statistics\Enqueued TransmissionQ Msgs/sec"
"\PhysicalDisk(_Total)\Avg. Disk Queue Length"
"\PhysicalDisk(_Total)\Disk Read Bytes/sec"
"\PhysicalDisk(_Total)\Disk Write Bytes/sec"
"\SQLServer:SQL Statistics\Batch Requests/sec"'
;
declare
@current_time datetime
;
select
@current_time = GETDATE()
;
if ISNULL(@begin_time,'') = ''
set
@begin_time = convert(varchar,@current_time,101) + ' ' + convert(char(5),DATEADD(minute,1,@current_time),114)
;
if isnull(@end_time,'') = ''
set
@end_time = convert(varchar,dateadd(day,1,@begin_time),101) + ' ' + convert(char(5),dateadd(day,1,@begin_time),114)
;
---------checking drivers capacity on server
if object_id('tempdb..#drives','U') is not null
drop table #drives
create table #drives
(
id int identity(1,1) not null primary key
,drive varchar(2)
,free_Mb int
)
;
insert into #drives
exec sys.xp_fixeddrives
--driver's capacity should be double of @file_max_size_mb
if not exists( --if the driver's capacity is less than size double of @file_max_size_mb
select top 1 1
from #drives
where drive = left(@perfmon_folder_path,1)
and free_Mb > cast(@file_max_size_mb as int) * 2
)
begin
declare
@drives varchar(2)
;
select top 1 @drives = drive --choice another drivers
from #drives
where free_Mb > cast(@file_max_size_mb as int) * 2
order by free_Mb desc
if @drives is null --if there is no another drives, choice C driver
begin
set
@drives = 'C'
;
raiserror('file should be saved into C driver',10,1) with nowait
end
set
@perfmon_folder_path = @drives + right(@perfmon_folder_path, len(@perfmon_folder_path)-1)
;
end
--in order to keep folder identical:
set
@perfmon_folder_path = @perfmon_folder_path + replace(newid(),'-','') + '\'
;
--print @perfmon_folder_path
--create folder
exec sys.xp_create_subdir @perfmon_folder_path
--raiserror('%s',10,1,@perfmon_folder_path) with nowait
declare
@sql varchar(8000)
,@fileid int
,@command varchar(2000)
;
select
@sql = ''
,@fileid = 0
,@command = ''
,@perfmon_folder_path = quotename(@perfmon_folder_path,'"')
,@data_gather_bat_file_name = @perfmon_folder_path + @data_gather_bat_file_name
,@data_gether_start_file_name = @perfmon_folder_path + @data_gether_start_file_name
,@perf_counts_string = '<V><![CDATA[' + replace(
replace(
replace(
@perf_counts_string,CHAR(10),']]></V><V><![CDATA['
),',',']]></V><V><![CDATA['
),CHAR(13),']]></V><V><![CDATA['
) + ']]></V>'
;
--===============================generate counters configure file
if object_id('tempdb..#temp','U') is not null
drop table #temp
create table #temp
(
id int identity(1,1) not null primary key
,fileid int
,command varchar(8000) null
)
;
;WITH data
AS (
SELECT cast(@perf_counts_string AS XML) as c
)
INSERT INTO #temp
SELECT
fileid = 1
,command = T.C.value('(./text())[1]','sysname')
FROM data as a
CROSS APPLY C.nodes('./V') AS T(C)
--===============================end generate counters configure file
--===============================generate data gather bat
;with DATA
AS(
SELECT fileid = 2 ,command = '@echo off'
union all
SELECT fileid = 2 ,command = '::#####setlocal enabledelayedexpansion'
union all
SELECT fileid = 2 ,command = 'setlocal enabledelayedexpansion'
union all
SELECT fileid = 2 ,command = '::variables'
union all
SELECT fileid = 2 ,command = 'set path_root=.'
union all
SELECT fileid = 2 ,command = 'set file=%path_root%\' + @perfmon_data_file_name
union all
SELECT fileid = 2 ,command = 'set cfg_file=' + @counter_conf_file_name
union all
SELECT fileid = 2 ,command = 'Logman delete PERFMON_BASE'
union all
SELECT fileid = 2 ,command = 'logman create counter PERFMON_BASE' + case
when @begin_time = '' then ''
when @begin_time is null then ''
else ' -b ' + @begin_time
end
+ case
when @end_time = '' then ''
when @end_time is null then ''
else + ' -e ' + @end_time
end + ' -f ' + @perfmon_log_format + ' -max ' + @file_max_size_mb + ' -si 00:00:05 --v -o "%file%" -cf "%path_root%\%cfg_file%"'
union all
SELECT fileid = 2 ,command = 'timeout /t 5'
)
INSERT INTO #temp
SELECT * from DATA
--===============================end generate data gather bat
--===============================generate start bat file
;with DATA
AS(
SELECT fileid = 3 ,command = '@echo off'
union all
SELECT fileid = 3 ,command = 'title User defined DCT starting...'
union all
SELECT fileid = 3 ,command = 'logman start perfmon_base'
union all
SELECT fileid = 3 ,command = 'echo started.'
union all
SELECT fileid = 3 ,command = 'timeout /t 5'
)
INSERT INTO #temp
SELECT * from DATA
--===============================end generate start bat file
set
@counter_conf_file_name = @perfmon_folder_path + @counter_conf_file_name
;
declare cur_command cursor local static forward_only read_only
for
select fileid,command
from #temp
where command is not null
open cur_command
fetch next from cur_command into @fileid, @command
while(@@FETCH_STATUS = 0)
begin
set
@sql = 'echo ' + @command + '>> ' + case @fileid
when 1 then @counter_conf_file_name
when 2 then @data_gather_bat_file_name
when 3 then @data_gether_start_file_name
else ''
end
;
--echo to files
--print @sql
exec sys.xp_cmdshell @sql, no_output
fetch next from cur_command into @fileid, @command
end
close cur_command
deallocate cur_command
--===============================data collection instance deployment
set
@sql = 'cd /d '+ @perfmon_folder_path +' & ' + @data_gather_bat_file_name
;
--print @sql
exec sys.xp_cmdshell @sql
--===============================output info
select
begin_time = @begin_time
,end_time = @end_time
,perfmon_start_command = 'exec sys.xp_cmdshell ''logman start perfmon_base'''
,perfmon_stop_command = 'exec sys.xp_cmdshell ''logman stop perfmon_base'''
,perfmon_delete_command = 'exec sys.xp_cmdshell ''Logman delete PERFMON_BASE'''
,perfmon_data_file = replace(@perfmon_folder_path,'"','') + @perfmon_data_file_name + '.' + @perfmon_log_format
;

复制代码

当然把附件也放到这里，省的大家在Copy。

perfmon.sql (8.05 KB, 下载次数: 31)

参数解释

@file_max_size_mb ：性能监视器生成的文件最大大小，超过这个值就会停止
,@perfmon_folder_path ：文件路径目录
,@perfmon_log_format ：生成的文件格式
,@begin_time ：性能监视器开始抓取的时间，如果为null或者‘’，则在下一分钟开始
,@end_time ：性能监视器停止抓取的时间，如果文件大小未超过@file_max_size_mb，默认则在24小时候停止
,@counter_conf_file_name ：可以保持默认
,@data_gather_bat_file_name ：可以保持默认
,@data_gether_start_file_name ：可以保持默认
,@perfmon_data_file_name ：可以保持默认
,@perf_counts_string ：性能监视器counter，这个自己喜欢抓哪些就抓哪些，按照上面的格式设置即可

结语
性能监视器部署好了以后，抓取到的文件读取是我们接下来要完成的工作，这部分我们下次再写。

Cherish_j_wang · 发表于 2014-1-22 13:56

paulchan 发表于 2014-1-19 20:31
好文章，向无私奉献的兄弟们致敬！！谢谢分享！

其实也是对自己工作内容的一次梳理和再总结，还是比较有意思的。尤其是看到那么多热心朋友的回复，希望对大家有帮助

Cherish_j_wang · 发表于 2014-1-22 13:56

cike5 发表于 2013-12-27 13:55
这才是技术牛人的特征，比某些个老男人请多了 -- 分享点资料还吱吱扭扭，不点名了：）

原来是来抱怨来了

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

MSSQL DBA 精华总结 [使用SQL脚本部署Windows性能监视器]

浏览过的版块