通过sql server动态管理试图查询性能数据

最新推荐文章于 2020-07-28 00:32:22 发布

debussy2005

最新推荐文章于 2020-07-28 00:32:22 发布

阅读量558

点赞数

文章标签： sql server buffer performance manager integer object

本文链接：https://blog.csdn.net/debussy2005/article/details/5868545

版权

1 Buffer Cache Hit Ratio:

SELECT

(CAST(SUM(CASE LTRIM(RTRIM(counter_name)) WHEN 'Buffer cache hit ratio' THEN

CAST(cntr_value AS INTEGER) ELSE NULL END) AS FLOAT) /

CAST(SUM(CASE LTRIM(RTRIM(counter_name)) WHEN 'Buffer cache hit ratio base'

THEN CAST(cntr_value AS INTEGER) ELSE NULL END) AS FLOAT)) * 100

AS BufferCacheHitRatio

FROM

sys.dm_os_performance_counters

WHERE

LTRIM(RTRIM([object_name])) LIKE '%:Buffer Manager' AND

[counter_name] LIKE 'Buffer Cache Hit Ratio%'

2 SQLSERVER CPU 占有率

DECLARE @ts_now BIGINT

SELECT @ts_now = cpu_ticks / CONVERT(FLOAT, ms_ticks) FROM

sys.dm_os_sys_info

SELECT record_id,

DATEADD(ms, -1 * (@ts_now - [timestamp]), GETDATE()) AS

EventTime,

SQLProcessUtilization,

SystemIdle,

100 - SystemIdle - SQLProcessUtilization AS

OtherProcessUtilization

FROM (

SELECT

record.value('(./Record/@id)[1]', 'int') AS record_id,

record.value

('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS

SystemIdle,

record.value

('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int')

AS SQLProcessUtilization,

TIMESTAMP

FROM (

SELECT TIMESTAMP, CONVERT(XML, record) AS record

FROM sys.dm_os_ring_buffers

WHERE ring_buffer_type =

N'RING_BUFFER_SCHEDULER_MONITOR'

AND record LIKE '% %') AS x

) AS y

ORDER BY record_id DESC

3 Memory Manager

declare @physical_memory_kb int

SELECT @physical_memory_kb = total_physical_memory_kb FROM sys . dm_os_sys_memory

select *, convert ( decimal , cntr_value )/ convert ( decimal , @physical_memory_kb ) as 'P' FROM

sys . dm_os_performance_counters

where object_name like '%Memory Manager %' and counter_name like '%server memory%'

Buffer Manager Free pages

SELECT * FROM sys . dm_os_performance_counters

WHERE counter_name like '%free pages%' and object_name like '%Buffer Manager%'

Buffer Manager : Page life expectancy

SELECT * FROM sys . dm_os_performance_counters

WHERE counter_name like '%expectancy%' and object_name like '%Buffer Manager%'

查找表占用磁盘 sp_spaceused

查询各个磁盘分区的剩余空间： Exec master.dbo.xp_fixeddrives

查询数据库服务器各数据库日志文件的大小及利用率 DBCC SQLPERF(LOGSPACE)

开启 xp_cmdshell

-- 允许配置高级选项

EXEC sp_configure 'show advanced options' , 1

-- 重新配置

RECONFIGURE

-- 启用 xp_cmdshell

EXEC sp_configure 'xp_cmdshell' , 1

-- 重新配置

RECONFIGURE

获取磁盘空间的：

A EXEC XP_CMDSHELL 'fsutil volume diskfree e: '

需要开启

-- 允许配置高级选项

EXEC sp_configure 'show advanced options' , 1

-- 重新配置

RECONFIGURE

-- 启用 xp_cmdshell

EXEC sp_configure 'xp_cmdshell' , 1

-- 重新配置

RECONFIGURE

获取一个磁盘总容量

CREATE FUNCTION dbo.GetDriveSize

(@driveletter CHAR(1))

RETURNS NUMERIC(20)

BEGIN

DECLARE @rs INTEGER, @fso INTEGER, @getdrive VARCHAR(13), @drv

INTEGER,

@drivesize VARCHAR(20)

SET @getdrive = 'GetDrive("' + @driveletter + '")'

EXEC @rs = sp_OACreate 'Scripting.FileSystemObject', @fso OUTPUT

IF @rs = 0

EXEC @rs = sp_OAMethod @fso, @getdrive, @drv OUTPUT

IF @rs = 0

EXEC @rs = sp_OAGetProperty @drv,'TotalSize', @drivesize OUTPUT

IF @rs<> 0

SET @drivesize = NULL

EXEC sp_OADestroy @drv

EXEC sp_OADestroy @fso

RETURN @drivesize

END

SELECT dbo.GetDriveSize('C')

获取每个磁盘空间情况

CREATE PROCEDURE sp_diskspace

SET NOCOUNT ON

DECLARE @hr int

DECLARE @fso int

DECLARE @drive char ( 1)

DECLARE @odrive int

DECLARE @TotalSize varchar ( 20)

DECLARE @MB bigint ; SET @MB = 1048576

CREATE TABLE #drives ( ServerName varchar ( 15),

drive char ( 1) PRIMARY KEY ,

FreeSpace int NULL,

TotalSize int NULL,

FreespaceTimestamp DATETIME NULL)

INSERT #drives ( drive , FreeSpace )

EXEC master . dbo . xp_fixeddrives

EXEC @hr = sp_OACreate 'Scripting.FileSystemObject' , @fso OUT

IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso

DECLARE dcur CURSOR LOCAL FAST_FORWARD

FOR SELECT drive from #drives

ORDER by drive

OPEN dcur

FETCH NEXT FROM dcur INTO @drive

WHILE @@FETCH_STATUS = 0

BEGIN

EXEC @hr = sp_OAMethod @fso , 'GetDrive' , @odrive OUT , @drive

IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso

EXEC @hr = sp_OAGetProperty @odrive , 'TotalSize' , @TotalSize OUT

IF @hr <> 0 EXEC sp_OAGetErrorInfo @odrive

UPDATE #drives

SET TotalSize = @TotalSize / @MB , ServerName = host_name (), FreespaceTimestamp = ( GETDATE ())

WHERE drive = @drive

FETCH NEXT FROM dcur INTO @drive

END

CLOSE dcur

DEALLOCATE dcur

EXEC @hr = sp_OADestroy @fso

IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso

SELECT ServerName ,

drive ,

TotalSize as 'Total(MB)' ,

FreeSpace as 'Free(MB)' ,

CAST (( FreeSpace /( TotalSize * 1.0))* 100.0 as int ) as 'Free(%)' ,

FreespaceTimestamp

FROM #drives

ORDER BY drive

DROP TABLE #drives

RETURN

用户名称，密码：

select p . name , p . type_desc ,

p . default_database_name ,

s . status , s . name , s . password , s . dbname

, s .* from sys . server_principals p join

sys . syslogins s ON p . sid = s . sid

where p . type_desc = 'SQL_LOGIN'

debussy2005

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
通过sql server动态管理试图查询性能数据

<br /><!-- /* Font Definitions */ @font-face {font-family:宋体; panose-1:2 1 6 0 3 1 1 1 1 1; mso-font-alt:SimSun; mso-font-charset:134; mso-generic-font-family:auto; mso-font-pitch:variable; mso-font-signature:3 135135232 16 0 262145 0;}
复制链接

扫一扫