-- SQL Scripts , sp_spaceused , sp_helpindex2 , sp_partitions , sp_updatestats2

-- update 2018-07-09
USE [master]

GO

IF EXISTS (
  SELECT * FROM sys.objects WHERE object_id = OBJECT_ID('dbo.sp_VAS')
) DROP PROCEDURE dbo.sp_VAS

GO

CREATE PROCEDURE dbo.sp_VAS
AS
SET NOCOUNT ON
IF NOT EXISTS (
  SELECT * FROM sys.objects WHERE object_id = OBJECT_ID('dbo.zVAS')
)

BEGIN

  CREATE TABLE dbo.zVAS ( DT datetime, vas_res bigint, vas_com bigint, phy_mem bigint
  , sqlserver_start_time datetime , server_name sysname , cpu_count int, HTR int
  , CompiledPlan_KB bigint, ExtendedProc_KB bigint, ParseTree_KB bigint
  , Xmlhd int, OrDocSz int, DormDur int, Cursors int, NetPk8K int, ClrCt int, Clr_time bigint )

  IF NOT EXISTS (
   SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID('dbo.zVAS') AND index_id = 1
  )
   CREATE CLUSTERED INDEX CX ON dbo.zVAS(DT)
END

IF NOT EXISTS (
  SELECT * FROM sys.objects WHERE object_id = OBJECT_ID('dbo.zPlanC')
)
BEGIN

  CREATE TABLE dbo.zPlanC ( cacheobjtype varchar(128), objtype varchar(20)
  , Total_ct int, SingleUse int, TwiceUse int, TotSz_KB bigint, Single_KB bigint, Twice_KB bigint
  , usecounts bigint , server_name sysname , DT datetime )

  IF NOT EXISTS (
   SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID('dbo.zPlanC') AND index_id = 1
  )
   CREATE CLUSTERED INDEX CX ON dbo.zPlanC(DT)
END

DECLARE @DT datetime = GETDATE(), @CP bigint, @EP bigint, @PT bigint

INSERT dbo.zPlanC( cacheobjtype, objtype, Total_ct,SingleUse, TwiceUse,TotSz_KB, Single_KB,Twice_KB
, usecounts , server_name, DT)

SELECT cacheobjtype, objtype, COUNT(*) Total_ct
, SUM(CASE WHEN usecounts = 1 THEN 1 ELSE 0 END) SingleUse
, SUM(CASE WHEN usecounts = 2 THEN 1 ELSE 0 END) TwiceUse
, SUM(CONVERT(bigint, size_in_bytes))/1024 TotSz_KB
, SUM(CASE WHEN usecounts = 1 THEN CONVERT(bigint,size_in_bytes) ELSE 0 END)/1024 Single_KB
, SUM(CASE WHEN usecounts = 2 THEN CONVERT(bigint, size_in_bytes) ELSE 0 END)/1024 Twice_KB
, SUM(CONVERT(bigint,usecounts)) usecounts
, @@SERVERNAME, @DT
FROM sys.dm_exec_cached_plans
GROUP BY cacheobjtype , objtype
ORDER BY cacheobjtype , objtype

SELECT @CP = [Compiled Plan], @EP = [Extended Proc], @PT = [Parse Tree]
FROM ( SELECT cacheobjtype, TotSz_KB FROM dbo.zPlanC WHERE DT = @DT ) p
PIVOT ( SUM (TotSz_KB) FOR cacheobjtype IN ( [Compiled Plan], [Extended Proc], [Parse Tree] )
) AS pvt

 

DECLARE @xmlhd int, @OrDocSz bigint, @DormDur bigint, @cursors int, @netpkt8k int, @clr_ct int, @clr_time bigint

SELECT @xmlhd = COUNT(*) , @OrDocSz = SUM(original_document_size_bytes) , @DormDur = SUM(dormant_duration_ms)
FROM sys.dm_exec_xml_handles(0)

SELECT @cursors = COUNT(*) FROM sys.dm_exec_cursors(0)

SELECT @netpkt8k = COUNT(*) FROM sys.dm_exec_connections WHERE net_packet_size > 8060

SELECT @clr_ct = COUNT(*) , @clr_time = SUM(total_clr_time)
FROM sys.dm_exec_query_stats WHERE total_clr_time > 0

 

INSERT dbo.zVAS( DT, vas_res, vas_com, phy_mem, sqlserver_start_time, server_name, cpu_count, HTR
, CompiledPlan_KB, ExtendedProc_KB, ParseTree_KB, Xmlhd, OrDocSz, DormDur, Cursors, NetPk8K, ClrCt, Clr_time)

SELECT @DT DT, b.virtual_address_space_reserved_kb vas_res, b.virtual_address_space_committed_kb vas_com
, b.physical_memory_in_use_kb phy_mem
, c.sqlserver_start_time, @@SERVERNAME server_name
, c.cpu_count, c.hyperthread_ratio HTR
, @CP CompiledPlan_KB, @EP ExtendedProc_KB, @PT ParseTree_KB
, @xmlhd Xmlhd, @OrDocSz OrDocSz, @DormDur DormDur
, @cursors Cursors, @netpkt8k NetPk8K, @clr_ct ClrCt, @clr_time Clr_time
FROM sys.dm_os_process_memory b
CROSS JOIN sys.dm_os_sys_info c

 

PRINT CONVERT( varchar(23) ,@DT,121)

return 0

GO

EXEC sp_ms_marksystemobject 'sp_VAS'

GO