A simple way to monitor SQL server SQL performance.

This is all begins from a mail.

...

Dear sir:

This is liulei.

Thanks for your help about last PM for data migration to SSD storage. However, it is still important for monitor SQL server inefficient

SQL statements.

I can do nothing before without thrid part database monitor software. Does it means we have no idear to fix it ?

The answer is NO. This mail is the answer.

First, I would like to introduce some script.

checkCPU -- check CPU status every 6 minutes about 255 records

getperformancetest -- the original script is copied from yours, I made it as a procedure and conver xml data to nvarchar(max)

-- CAST(qp.query_plan AS nvarchar(max)) , you will know why I did like that.

linkserver_LN_N4TEST --make link server from empty server which getting and collecting all information from clients.

-- if we decide save record table at each client. That is not necessary.

checkperformance -- check and get performacnce top 10 data local version, store result tables at client database

checkperformancetest -- check and get performacnce top 10 data remote version, store result tables at dedicated server

showdelperftable -- collect performance tables

checkoldtable2 -- check and drop performacd tables any days before , like oracle AWR.

mssql_cpu_status_check_test.xlsm -- we can get checkCPU result from this excel and we can check this performance table result if

I need to check SQL information result.

we can set checkperformance or checkperformancetest as agent job evert 6 minutes.

That is all about simple way to monitor SQL server SQL performance.

============================

checkCPU:

CREATE PROCEDURE [dbo].[checkCPU]

BEGIN

DECLARE @ts_now bigint = (SELECT cpu_ticks/(cpu_ticks/ms_ticks)FROM sys.dm_os_sys_info);

SELECT TOP(500) SQLProcessUtilization AS [SQL Server Process CPU Utilization],

SystemIdle AS [System Idle Process],

100 - SystemIdle - SQLProcessUtilization AS [Other Process CPU Utilization],

DATEADD(ms, -6 * (@ts_now - [timestamp]), GETDATE()) AS [Event Time]

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 '%<SystemHealth>%') AS x

) AS y

ORDER BY record_id DESC;

END

checkoldtable2

CREATE PROCEDURE [dbo].[checkoldtable2] @days int

BEGIN

declare @m int

declare @n int

declare @tmp nvarchar(MAX)

if object_id('tempdb..#droptableList') is not null

drop table #droptableList

else

CREATE TABLE #droptableList(id int IDENTITY(1,1) NOT NULL,contents VARCHAR(35) NOT NULL)

SET NOCOUNT ON

INSERT #droptableList (contents) exec showdelperftable @days

set @m=(select count(*) from #droptableList)

--select * from #droptableList -- where id=1

set @n=1

while @n<[email protected]

begin

set @tmp=(select contents from #droptableList where [email protected])

--exec (@tmp)

print (@tmp+CHAR(5))

end

set @[email protected]+1

declare @droptableList TABLE (id int IDENTITY(1,1) NOT NULL,contents VARCHAR(35) NOT NULL)

SET NOCOUNT ON

INSERT @droptableList (contents) exec showdelperftable @days

set @m=(select count(*) from @droptableList)

set @n=1

while @n<[email protected]

begin

set @tmp=(select contents from @droptableList where [email protected])

exec (@tmp)

--print (@tmp+CHAR(5))

set @[email protected]+1

end

checkperfprmance

CREATE PROCEDURE [dbo].[checkperformance]

BEGIN

DECLARE @SQL2 VARCHAR(MAX)

DECLARE @createtable VARCHAR(MAX)

DECLARE @inserttable VARCHAR(MAX)

DECLARE @inserttable2 VARCHAR(MAX)

DECLARE @@tablename2 VARCHAR(MAX)

SELECT @SQL2 = (select replace(replace(replace(CONVERT(varchar, getdate(), 120 ),'-',''),' ',''),':',''))

--set @createtable='create table perf_'[email protected]+' (dbname varchar(20),logSizeMB float,logSpaceUsedPct float,Status int);'

set @createtable='create table perf_'[email protected]+' ([SQL_TEXT] [nvarchar](max),[execution_count] [numeric](18, 0),[avg_worker_time] [numeric](18, 0),[avg_elapsed_time] [numeric](18, 0),[avg_physical_reads] [numeric](18, 0),[avg_logical_reads] [numeric](18, 0),[query_plan] [xml]) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY];'

exec(@createtable)

--set @inserttable='insert into perf_'[email protected]+' EXEC ("DBCC SQLPERF(LOGSPACE)")'

set @inserttable='insert into perf_'[email protected]+' EXEC [dbo].[getperformance]'

--set @inserttable2=(select replace(@inserttable,'"',''''))

set @inserttable2=(select replace(@inserttable,'"',''''))

exec(@inserttable2)

END

checkperformancetest

CREATE PROCEDURE [dbo].[checkperformancetest]

BEGIN

DECLARE @SQL2 VARCHAR(MAX)

DECLARE @createtable VARCHAR(MAX)

DECLARE @inserttable VARCHAR(MAX)

DECLARE @inserttable2 VARCHAR(MAX)

DECLARE @@tablename2 VARCHAR(MAX)

SELECT @SQL2 = (select replace(replace(replace(CONVERT(varchar, getdate(), 120 ),'-',''),' ',''),':',''))

--set @createtable='create table perf_'[email protected]+' (dbname varchar(20),logSizeMB float,logSpaceUsedPct float,Status int);'

exec(@createtable)

--set @inserttable='insert into perf_'[email protected]+' EXEC ("DBCC SQLPERF(LOGSPACE)")'

-----set @inserttable='insert into perf_'[email protected]+' EXEC [dbo].[getperformance]'

set @inserttable='insert into perf_'[email protected]+' SELECT sql_text,execution_count,avg_worker_time,avg_elapsed_time,avg_physical_reads,avg_logical_reads, CAST(query_plan AS xml) query_plan FROM openquery([LN_N4TEST],''exec [dbo].[getperformancetest]'')'

--set @inserttable2=(select replace(@inserttable,'"',''''))

set @inserttable2=(select replace(@inserttable,'"',''''))

exec(@inserttable2)

END

getperformancetest

CREATE PROCEDURE [dbo].[getperformancetest]

BEGIN

SELECT TOP 10

Left(SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1

, ((CASE qs.statement_end_offset

WHEN -1 THEN DATALENGTH(qt.TEXT)

ELSE qs.statement_end_offset

END - qs.statement_start_offset)/2)+1), 200) AS "SQL_TEXT"

, qs.execution_count

, qs.total_worker_time /qs.execution_count/1000 as avg_worker_time

, qs.total_elapsed_time /qs.execution_count /1000 as avg_elapsed_time

, qs.total_physical_reads /qs.execution_count as avg_physical_reads

, qs.total_logical_reads /qs.execution_count as avg_logical_reads

--, qp.query_plan

, CAST(qp.query_plan AS nvarchar(max)) query_plan

FROM sys.dm_exec_query_stats qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt

CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp

WHERE qs.last_execution_time > dateadd(minute, -1, getdate())

ORDER BY qs.total_elapsed_time/qs.execution_count DESC

END

showdelperftable

CREATE PROCEDURE [dbo].[showdelperftable] @days int

BEGIN

select 'drop table dbo.'+name from sys.tables where name like 'perf_%' and create_date < dateadd(day, @days, getdate())

order by name asc,create_date asc

END

A simple way to monitor SQL server SQL performance.

相关推荐