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

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

maco_wang 发表于 2013-2-27 23:00
谢谢楼主的技术分享！

希望对你有帮助。

Cherish_j_wang · 发表于 2014-1-22 14:00

路人ABCD 发表于 2013-2-19 09:52
好久没登陆，楼主不继续？

不好意思，的确如你所说，是很久没有来了。去年发生了很多事情：
1. 换了工作环境，到了新的地方，新的国度，不断的在适应中
2.当了父亲，每天留给自己的时间相对少了很多
3.在关系型数据库和BigData技术间游走徘徊
...

Cherish_j_wang · 发表于 2014-1-22 14:01

zsping 发表于 2012-6-27 09:50
非常感謝...大有用處

这就是写这个帖子的初衷

paulchan · 发表于 2014-1-22 14:59

Cherish_j_wang 发表于 2014-1-22 13:56
其实也是对自己工作内容的一次梳理和再总结，还是比较有意思的。尤其是看到那么多热心朋友的回复，希望对 ...

相对而言，我就惭愧了，经常上来下载资料，自己平时很少做总结，

Cherish_j_wang · 发表于 2014-1-23 13:13

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

MSSQL DBA 精华总结
之使用SQL脚本部署Windows性能监视器（二）
目的
这一贴主要是使用SQL语句读取性能监视器抓取到的文件。前提：我的Demo是在性能监视器文件以bincirc格式抓取到的。
脚本

set nocount on
declare
@local_server_name sysname
,@perfmon_data_file sysname
,@sql nvarchar(max)
;
select
@local_server_name = @@SERVERNAME
;
;WITH DATA
AS(
--shoud be init.
select server_name = @@SERVERNAME,perfmon_data_file = 'D:\DBA\perfmon\7B023654B9674647A069069D3B854FC2\perfmon_data.tsv'
union all
select server_name = 'ZZZ',perfmon_data_file = 'D:\DBA\perfmon\45673654B96746123069069D3B854FC2\perfmon_data.tsv'
)
select top 1 @perfmon_data_file = perfmon_data_file
from data
where server_name = @local_server_name
if object_id('tempdb..#temp','U') is not null
drop table #temp
create table #temp
(
id int identity(1,1) not null primary key
,data_list varchar(max)
)
/*
SET @sql = N'
;WITH data
AS (
select c =
cast(''<V><![CDATA['' + replace(
replace(
replace(
cast(c as varchar(max)),CHAR(10),'']]></V><V><![CDATA[''
),'','','']]></V><V><![CDATA[''
),CHAR(13),'']]></V><V><![CDATA[''
) + '']]></V>''
as xml)
FROM OPENROWSET(BULK N''' + @perfmon_data_file + ''', SINGLE_BLOB) as T(c)
)
SELECT
data_list = T.C.value(''(./text())[1]'',''varchar(max)'')
FROM data as a
CROSS APPLY C.nodes(''./V'') AS T(C)
'
*/
set
@sql = N'
exec sys.xp_cmdshell ''type ' + @perfmon_data_file + '''
'
;
--print @sql
insert into #temp
exec sys.sp_executesql @sql
;WITH point_time_set
as(
select
point_time = left(data_list,PATINDEX('%" "%',data_list))
,data_list
from #temp
where data_list is not null
and id > 2 --remove column titile
)
--select * from point_time_set
,ProcessorTime_set_pre
as(
select
*
,new_data_list = replace(data_list,point_time,'')
from point_time_set
)
--select * from ProcessorTime_set_pre
,ProcessorTime_set
as(
select
Point_Time
,ProcessorTime = left(new_data_list,PATINDEX('%" "%',new_data_list))
,data_list
from ProcessorTime_set_pre
)
--select * from ProcessorTime_set
,TransportMsgs_set_pre
as(
select
*
,new_data_list = replace(data_list,Point_Time + ProcessorTime,'')
from ProcessorTime_set
)
--select * from TransportMsgs_set_pre
,TransportMsgs_set
as(
select
Point_Time
,ProcessorTime
,TransportMsgsPerSec = left(new_data_list,PATINDEX('%" "%',new_data_list))
,data_list
from TransportMsgs_set_pre
)
--select * from TransportMsgs_set
,TransmissionQMsgs_set_pre
as(
select
*
,new_data_list = replace(data_list,Point_Time + ProcessorTime + TransportMsgsPerSec,'')
from TransportMsgs_set
)
--select * from TransmissionQMsgs_set_pre
,TransmissionQMsgs_set
as(
select
Point_Time
,ProcessorTime
,TransportMsgsPerSec
,TransmissionQMsgsPerSec = left(new_data_list,PATINDEX('%" "%',new_data_list))
,data_list
from TransmissionQMsgs_set_pre
)
--select * from TransmissionQMsgs_set
,DiskQueueLength_pre
as(
select *
,new_data_list = replace(data_list,Point_Time + ProcessorTime + TransportMsgsPerSec + TransmissionQMsgsPerSec,'')
from TransmissionQMsgs_set
)
--select * from DiskQueueLength_pre
,DiskQueueLength
as(
select
Point_Time
,ProcessorTime
,TransportMsgsPerSec
,TransmissionQMsgsPerSec
,DiskQueueLength = left(new_data_list,PATINDEX('%" "%',new_data_list))
,data_list
from DiskQueueLength_pre
)
--select * from DiskQueueLength
,DiskReadBytes_pre
as(
select *
,new_data_list = replace(data_list,Point_Time + ProcessorTime + TransportMsgsPerSec + TransmissionQMsgsPerSec + DiskQueueLength,'')
from DiskQueueLength
)
--select * from DiskReadBytes_pre
,DiskReadBytes
as(
select
Point_Time
,ProcessorTime
,TransportMsgsPerSec
,TransmissionQMsgsPerSec
,DiskQueueLength
,DiskReadBytesPerSec = left(new_data_list,PATINDEX('%" "%',new_data_list))
,data_list
from DiskReadBytes_pre
)
--select * from DiskReadBytes
,DiskWriteBytes_pre
as(
select *
,new_data_list = replace(data_list,Point_Time + ProcessorTime + TransportMsgsPerSec + TransmissionQMsgsPerSec + DiskQueueLength + DiskReadBytesPerSec,'')
from DiskReadBytes
)
--select * from DiskReadBytes_pre
,DiskWriteBytes
as(
select
Point_Time
,ProcessorTime
,TransportMsgsPerSec
,TransmissionQMsgsPerSec
,DiskQueueLength
,DiskReadBytesPerSec
,DiskWriteBytesPerSec = left(new_data_list,PATINDEX('%" "%',new_data_list))
,data_list
from DiskWriteBytes_pre
)
--select * from DiskWriteBytes
select
Point_Time
,ProcessorTime
,TransportMsgsPerSec
,TransmissionQMsgsPerSec
,DiskQueueLength
,DiskReadBytesPerSec
,DiskWriteBytesPerSec
,BatchRequestsPerSec = reverse(left(reverse(data_list),PATINDEX('%" "%',reverse(data_list))))
--replace(data_list,Point_Time + ProcessorTime + TransportMsgsPerSec + TransmissionQMsgsPerSec + DiskQueueLength + DiskReadBytesPerSec + DiskWriteBytesPerSec,'')
from DiskWriteBytes
--select * from BatchRequests_pre