T-SQLを使用したデータベースとDBMSの調査

USE [_] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE VIEW [inf].[vInnerTableSize] as --   select object_name(p.[object_id]) as [Name] , SUM(a.[total_pages]) as TotalPages , SUM(p.[rows]) as CountRows , cast(SUM(a.[total_pages]) * 8192/1024. as decimal(18, 2)) as TotalSizeKB from sys.partitions as p inner join sys.allocation_units as a on p.[partition_id]=a.[container_id] left outer join sys.internal_tables as it on p.[object_id]=it.[object_id] where OBJECTPROPERTY(p.[object_id], N'IsUserTable')=0 group by object_name(p.[object_id]) --order by p.[rows] desc; GO 

 USE [  ] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE view [inf].[vScheduleMultiJobs] as with sh as( SELECT schedule_id FROM [inf].[vJobSchedules] group by schedule_id having count(*)>1 ) select * from msdb.dbo.sysschedules as s where exists(select top(1) 1 from sh where sh.schedule_id=s.schedule_id) GO 

 USE [_] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE view [inf].[vObjectDescription] as select SCHEMA_NAME(obj.[schema_id]) as SchemaName ,QUOTENAME(object_schema_name(obj.[object_id]))+'.'+quotename(obj.[name]) as ObjectName ,obj.[type] as [Type] ,obj.[type_desc] as [TypeDesc] ,ep.[value] as ObjectDescription from sys.objects as obj left outer join sys.extended_properties as ep on obj.[object_id]=ep.[major_id] and ep.[minor_id]=0 and ep.[name]='MS_Description' where obj.[is_ms_shipped]=0 and obj.[parent_object_id]=0 GO 

 USE [_] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE view [inf].[vObjectInParentDescription] as select SCHEMA_NAME(obj.[schema_id]) as SchemaName ,QUOTENAME(object_schema_name(obj.[parent_object_id]))+'.'+quotename(object_name(obj.[parent_object_id])) as ParentObjectName ,QUOTENAME(object_schema_name(obj.[object_id]))+'.'+quotename(obj.[name]) as ObjectName ,obj.[type] as [Type] ,obj.[type_desc] as [TypeDesc] ,ep.[value] as ObjectDescription from sys.all_objects as obj left outer join sys.extended_properties as ep on obj.[parent_object_id]=ep.[major_id] and ep.[minor_id]=obj.[object_id] and ep.[name]='MS_Description' where obj.[is_ms_shipped]=0 and obj.[parent_object_id]<>0 GO 

 USE [_] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE view [inf].[vParameterDescription] as select SCHEMA_NAME(obj.[schema_id]) as SchemaName ,QUOTENAME(object_schema_name(obj.[object_id]))+'.'+quotename(object_name(obj.[object_id])) as ParentObjectName ,p.[name] as ParameterName ,obj.[type] as [Type] ,obj.[type_desc] as [TypeDesc] ,ep.[value] as ParameterDescription from sys.parameters as p inner join sys.objects as obj on p.[object_id]=obj.[object_id] left outer join sys.extended_properties as ep on obj.[object_id]=ep.[major_id] and ep.[minor_id]=p.[parameter_id] and ep.[name]='MS_Description' where obj.[is_ms_shipped]=0 GO 

 USE [_] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE VIEW [inf].[vColumnTableDescription] as select SCHEMA_NAME(t.schema_id) as SchemaName ,QUOTENAME(object_schema_name(t.[object_id]))+'.'+quotename(t.[name]) as TableName ,c.[name] as ColumnName ,ep.[value] as ColumnDescription from sys.tables as t inner join sys.columns as c on c.[object_id]=t.[object_id] left outer join sys.extended_properties as ep on t.[object_id]=ep.[major_id] and ep.[minor_id]=c.[column_id] and ep.[name]='MS_Description' where t.[is_ms_shipped]=0; GO 

 USE [_] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE VIEW [inf].[vColumnViewDescription] as select SCHEMA_NAME(t.schema_id) as SchemaName ,QUOTENAME(object_schema_name(t.[object_id]))+'.'+quotename(t.[name]) as TableName ,c.[name] as ColumnName ,ep.[value] as ColumnDescription from sys.views as t inner join sys.columns as c on c.[object_id]=t.[object_id] left outer join sys.extended_properties as ep on t.[object_id]=ep.[major_id] and ep.[minor_id]=c.[column_id] and ep.[name]='MS_Description' where t.[is_ms_shipped]=0; GO 

 USE [_] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE VIEW [inf].[vSchemaDescription] as select SCHEMA_NAME(t.schema_id) as SchemaName --,QUOTENAME(object_schema_name(t.[object_id]))+'.'+quotename(t.[name]) as TableName ,ep.[value] as SchemaDescription from sys.schemas as t left outer join sys.extended_properties as ep on t.[schema_id]=ep.[major_id] and ep.[minor_id]=0 and ep.[name]='MS_Description' GO 

 --     @ObjectID - dbo.GetPlansObject --       EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N' ', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'FUNCTION', @level1name = N'GetPlansObject', @level2type = N'PARAMETER', @level2name = N'@ObjectID'; --    - dbo.GetPlansObject --     ,  EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'    ', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'FUNCTION', @level1name=N'GetPlansObject'; --     inf.vColumnTableDescription --     EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'  ', @level0type=N'SCHEMA', @level0name=N'inf', @level1type=N'VIEW', @level1name=N'vColumnTableDescription'; --     TEST_GUID  dbo.TABLE --      EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'  ()', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'TEST', @level2type=N'COLUMN', @level2name=N'TEST_GUID'; --     rep EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'  rep    ' , @level0type=N'SCHEMA', @level0name=N'rep'; --     EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'      MS SQL Server 2016-2017 (     MS SQL Server 2012-2014).      MS SQL Server 2012           .    .   inf.InfoAgentJobs.'; 

 USE [  ] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO create view [inf].[vSessionThreadOS] as /*   ,       Windows.         Windows.     ,        . */ SELECT STasks.session_id, SThreads.os_thread_id FROM sys.dm_os_tasks AS STasks INNER JOIN sys.dm_os_threads AS SThreads ON STasks.worker_address = SThreads.worker_address WHERE STasks.session_id IS NOT NULL; GO 

 USE [  ] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO create view [inf].[vServerProblemInCountFilesTempDB] as /* http://sqlcom.ru/dba-tools/tempdb-in-sql-server-2016/          tempdb.     latch    PFS, GAM, SGAM    tempdb.           «Is Not PFS, GAM, or SGAM page»,          tempdb */ Select session_id, wait_type, wait_duration_ms, blocking_session_id, resource_description, ResourceType = Case When Cast(Right(resource_description, Len(resource_description) - Charindex(':', resource_description, 3)) As Int) - 1 % 8088 = 0 Then 'Is PFS Page' When Cast(Right(resource_description, Len(resource_description) - Charindex(':', resource_description, 3)) As Int) - 2 % 511232 = 0 Then 'Is GAM Page' When Cast(Right(resource_description, Len(resource_description) - Charindex(':', resource_description, 3)) As Int) - 3 % 511232 = 0 Then 'Is SGAM Page' Else 'Is Not PFS, GAM, or SGAM page' End From sys.dm_os_waiting_tasks Where wait_type Like 'PAGE%LATCH_%' And resource_description Like '2:%' GO 

 USE [  ] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO create view [srv].[vStatisticsIOInTempDB] as /*     (avg_write_stall_ms)  5 ,      .  5  10  —  .  10  —  ,    ,    -     https://minyurov.com/2016/07/24/mssql-tempdb-opt/ */ SELECT files.physical_name, files.name, stats.num_of_writes, (1.0 * stats.io_stall_write_ms / stats.num_of_writes) AS avg_write_stall_ms, stats.num_of_reads, (1.0 * stats.io_stall_read_ms / stats.num_of_reads) AS avg_read_stall_ms FROM sys.dm_io_virtual_file_stats(2, NULL) as stats INNER JOIN master.sys.master_files AS files ON stats.database_id = files.database_id AND stats.file_id = files.file_id WHERE files.type_desc = 'ROWS' GO 

 USE [  ] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE VIEW [inf].[vServerLastBackupDB] as with backup_cte as ( select bs.[database_name], backup_type = case bs.[type] when 'D' then 'database' when 'L' then 'log' when 'I' then 'differential' else 'other' end, bs.[first_lsn], bs.[last_lsn], bs.[backup_start_date], bs.[backup_finish_date], cast(bs.[backup_size] as decimal(18,3))/1024/1024 as BackupSizeMb, rownum = row_number() over ( partition by bs.[database_name], type order by bs.[backup_finish_date] desc ), LogicalDeviceName = bmf.[logical_device_name], PhysicalDeviceName = bmf.[physical_device_name], bs.[server_name], bs.[user_name] FROM msdb.dbo.backupset bs INNER JOIN msdb.dbo.backupmediafamily bmf ON [bs].[media_set_id] = [bmf].[media_set_id] ) select [server_name] as [ServerName], [database_name] as [DBName], [user_name] as [USerName], [backup_type] as [BackupType], [backup_start_date] as [BackupStartDate], [backup_finish_date] as [BackupFinishDate], [BackupSizeMb], --   [LogicalDeviceName], [PhysicalDeviceName], [first_lsn] as [FirstLSN], [last_lsn] as [LastLSN] from backup_cte where rownum = 1; GO 

 USE [  ] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE VIEW [inf].[vServerBackupDB] as with backup_cte as ( select bs.[database_name], backup_type = case bs.[type] when 'D' then 'database' when 'L' then 'log' when 'I' then 'differential' else 'other' end, bs.[first_lsn], bs.[last_lsn], bs.[backup_start_date], bs.[backup_finish_date], cast(bs.[backup_size] as decimal(18,3))/1024/1024 as BackupSizeMb, LogicalDeviceName = bmf.[logical_device_name], PhysicalDeviceName = bmf.[physical_device_name], bs.[server_name], bs.[user_name] FROM msdb.dbo.backupset bs INNER JOIN msdb.dbo.backupmediafamily bmf ON [bs].[media_set_id] = [bmf].[media_set_id] ) select [server_name] as [ServerName], [database_name] as [DBName], [user_name] as [USerName], [backup_type] as [BackupType], [backup_start_date] as [BackupStartDate], [backup_finish_date] as [BackupFinishDate], [BackupSizeMb], --   [LogicalDeviceName], [PhysicalDeviceName], [first_lsn] as [FirstLSN], [last_lsn] as [LastLSN] from backup_cte; GO 

 USE [  ] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE view [inf].[vWaits] as WITH [Waits] AS (SELECT [wait_type], --   [wait_time_ms] / 1000.0 AS [WaitS],--      .    signal_wait_time_ms ([wait_time_ms] - [signal_wait_time_ms]) / 1000.0 AS [ResourceS],--        signal_wait_time_ms [signal_wait_time_ms] / 1000.0 AS [SignalS],--           [waiting_tasks_count] AS [WaitCount],--   .         100.0 * [wait_time_ms] / SUM ([wait_time_ms]) OVER() AS [Percentage], ROW_NUMBER() OVER(ORDER BY [wait_time_ms] DESC) AS [RowNum] FROM sys.dm_os_wait_stats WHERE [wait_type] NOT IN ( N'BROKER_EVENTHANDLER', N'BROKER_RECEIVE_WAITFOR', N'BROKER_TASK_STOP', N'BROKER_TO_FLUSH', N'BROKER_TRANSMITTER', N'CHECKPOINT_QUEUE', N'CHKPT', N'CLR_AUTO_EVENT', N'CLR_MANUAL_EVENT', N'CLR_SEMAPHORE', N'DBMIRROR_DBM_EVENT', N'DBMIRROR_EVENTS_QUEUE', N'DBMIRROR_WORKER_QUEUE', N'DBMIRRORING_CMD', N'DIRTY_PAGE_POLL', N'DISPATCHER_QUEUE_SEMAPHORE', N'EXECSYNC', N'FSAGENT', N'FT_IFTS_SCHEDULER_IDLE_WAIT', N'FT_IFTSHC_MUTEX', N'HADR_CLUSAPI_CALL', N'HADR_FILESTREAM_IOMGR_IOCOMPLETION', N'HADR_LOGCAPTURE_WAIT', N'HADR_NOTIFICATION_DEQUEUE', N'HADR_TIMER_TASK', N'HADR_WORK_QUEUE', N'KSOURCE_WAKEUP', N'LAZYWRITER_SLEEP', N'LOGMGR_QUEUE', N'ONDEMAND_TASK_QUEUE', N'PWAIT_ALL_COMPONENTS_INITIALIZED', N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP', N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP', N'REQUEST_FOR_DEADLOCK_SEARCH', N'RESOURCE_QUEUE', N'SERVER_IDLE_CHECK', N'SLEEP_BPOOL_FLUSH', N'SLEEP_DBSTARTUP', N'SLEEP_DCOMSTARTUP', N'SLEEP_MASTERDBREADY', N'SLEEP_MASTERMDREADY', N'SLEEP_MASTERUPGRADED', N'SLEEP_MSDBSTARTUP', N'SLEEP_SYSTEMTASK', N'SLEEP_TASK', N'SLEEP_TEMPDBSTARTUP', N'SNI_HTTP_ACCEPT', N'SP_SERVER_DIAGNOSTICS_SLEEP', N'SQLTRACE_BUFFER_FLUSH', N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP', N'SQLTRACE_WAIT_ENTRIES', N'WAIT_FOR_RESULTS', N'WAITFOR', N'WAITFOR_TASKSHUTDOWN', N'WAIT_XTP_HOST_WAIT', N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG', N'WAIT_XTP_CKPT_CLOSE', N'XE_DISPATCHER_JOIN', N'XE_DISPATCHER_WAIT', N'XE_TIMER_EVENT') ) , ress as ( SELECT [W1].[wait_type] AS [WaitType], CAST ([W1].[WaitS] AS DECIMAL (16, 2)) AS [Wait_S],--      .    signal_wait_time_ms CAST ([W1].[ResourceS] AS DECIMAL (16, 2)) AS [Resource_S],--        signal_wait_time_ms CAST ([W1].[SignalS] AS DECIMAL (16, 2)) AS [Signal_S],--           [W1].[WaitCount] AS [WaitCount],--   .         CAST ([W1].[Percentage] AS DECIMAL (5, 2)) AS [Percentage], CAST (([W1].[WaitS] / [W1].[WaitCount]) AS DECIMAL (16, 4)) AS [AvgWait_S], CAST (([W1].[ResourceS] / [W1].[WaitCount]) AS DECIMAL (16, 4)) AS [AvgRes_S], CAST (([W1].[SignalS] / [W1].[WaitCount]) AS DECIMAL (16, 4)) AS [AvgSig_S] FROM [Waits] AS [W1] INNER JOIN [Waits] AS [W2] ON [W2].[RowNum] <= [W1].[RowNum] GROUP BY [W1].[RowNum], [W1].[wait_type], [W1].[WaitS], [W1].[ResourceS], [W1].[SignalS], [W1].[WaitCount], [W1].[Percentage] HAVING SUM ([W2].[Percentage]) - [W1].[Percentage] < 95 -- percentage threshold ) SELECT [WaitType] ,MAX([Wait_S]) as [Wait_S] ,MAX([Resource_S]) as [Resource_S] ,MAX([Signal_S]) as [Signal_S] ,MAX([WaitCount]) as [WaitCount] ,MAX([Percentage]) as [Percentage] ,MAX([AvgWait_S]) as [AvgWait_S] ,MAX([AvgRes_S]) as [AvgRes_S] ,MAX([AvgSig_S]) as [AvgSig_S] FROM ress group by [WaitType]; GO