Исследование БД и СУБД с помощью T-SQL

в 21:00, , рубрики: .net, Microsoft SQL Server, MS Sql Server, sql, Администрирование баз данных

Предисловие

Приветствую вновь тебя, уважаемый читатель !

Когда свои реализованные идеи, опыт, а также всю ту информацию, что не дает покоя, оформляешь в публикации, рано или поздно приходит логическая точка всему ранее написанному потоку информации. Эта статья будет отличаться от всех ранее опубликованных мною своей нестрогостью и более свободным стилем изложения текста, а также она завершит изложение всего моего накопленного опыта по MS SQL Server.

Данная статья является дополнением к статье Исследуем базы данных с помощью T-SQL, а также вкратце рассказывает о созданной базе данных по администрированию SRV и о проектах-утилитах, которые предназначены помочь в работе DBA MS SQL Server.

Некоторые полезные представления для исследования БД и СУБД в целом

Для определения размера встроенных таблиц, можно создать следующее представление [inf].[vInnerTableSize]:

Реализация представления [inf].[vInnerTableSize]

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

С помощью данного представления можно контролировать рост системных таблиц во избежании их чрезмерного роста.

С помощью системных представлений [sys].[sql_logins] и [sys].[syslogins] можно получить логины для скульных и виндовых входов.

Также интересны следующие системные представления для задач Агента экземпляра MS SQL Server:

1) [msdb].[dbo].[sysjobactivity] — активные задачи
2) [msdb].[dbo].[sysjobhistory] — история выполнения заданий
3) [msdb].[dbo].[sysjobs_view] и [msdb].[dbo].[sysjobservers] — задания
4) [msdb].[dbo].[sysjobschedules] — расписания заданий
5) [msdb].[dbo].[sysjobsteps] — шаги заданий
6) [msdb].[dbo].[sysjobstepslogs] — логирование шагов заданий

Также для того, чтобы знать для каких расписаний назначено более одной задачи, достаточно создать следующее представление [inf].[vScheduleMultiJobs]:

Реализация представления [inf].[vScheduleMultiJobs]

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

Данное представление позволит избежать необдуманное изменение в расписании для одной задачи, чтобы не вызвать изменения для другой.

Чтобы получить информацию об описании объектов БД, можно воспользоваться расширенными свойствами (системное представление [sys].[extended_properties]). Для удобства можно создать следующие представления [inf].[vObjectDescription]:

Реализация представления [inf].[vObjectDescription]

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

2) Описания для объектов, у которых есть родители — с помощью представления [inf].[vObjectInParentDescription]:

Реализация представления [inf].[vObjectInParentDescription]

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

3) Описания для параметров — с помощью представления [inf].[vParameterDescription]:

Реализация представления [inf].[vParameterDescription]

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

4) Описания столбцов таблиц — с помощью представления [inf].[vColumnTableDescription]:

Реализация представления [inf].[vColumnTableDescription]

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

5) Описания столбцов представлений — с помощью представления [inf].[vColumnViewDescription]:

Реализация представления [inf].[vColumnViewDescription]

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

6) Описания схем БД — с помощью представления [inf].[vSchemaDescription]:

Реализация представления [inf].[vSchemaDescription]

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

Чтобы добавить или отредактировать расширенные свойства для документирования объектов БД, лучше пользоваться сторонними утилитами (например, я использую dbForge).
Однако, также это можно сделать следующими запросами:

Примеры создания описаний для объектов БД

--здесь создаем описание для параметра @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.';

Для того, чтобы изменить или удалить описание, достаточно воспользоваться хранимыми процедурами sp_updateextendedproperty и sp_dropextendedproperty соответственно.

Также будут полезны следующие системные представления в рамках исследования всей СУБД:

1) [sys].[dm_os_performance_counters] — значения счетчиков производительности

2) [sys].[dm_os_schedulers] — планировщики заданий

3) [sys].[configurations] — сведения о конфигурации

4) чтобы сопоставить идентификаторы сеанса с идентификаторами потока Windows, можно создать следующее представление [inf].[vSessionThreadOS]:

Реализация представления [inf].[vSessionThreadOS]

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

5) чтобы узнать о проблемах с количеством файлов БД tempdb, можно создать следующее представление [inf].[vServerProblemInCountFilesTempDB]:

Реализация представления [inf].[vServerProblemInCountFilesTempDB]

USE [БД для администрирования]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

create view [inf].[vServerProblemInCountFilesTempDB]
as
/*
	
Улучшения tempdb в 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

6) чтобы узнать о проблемах с временем записи данных в БД tempdb, можно создать следующее представление [srv].[vStatisticsIOInTempDB]:

Реализация представления [srv].[vStatisticsIOInTempDB]

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 мс — низкая производительность, необходимо сделать детальный анализ, имеются проблемы с вводом-выводом для временной базы данных
	Оптимизация временной БД (tempdb)
*/
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

7) для удобства вывода информации о последних сделанных резервных копиях всех БД, можно создать следующее представление [inf].[vServerLastBackupDB]:

Реализация представления [inf].[vServerLastBackupDB]

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

8) аналогичное представление [inf].[vServerBackupDB] можно создать для получения информации о всех резервных копиях:

Реализация представления [inf].[vServerBackupDB]

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

9) также можно улучшить представление по статистике ожиданий (из статьи Статистика ожиданий SQL Server'а или пожалуйста, скажите мне, где болит), чтобы убрать выводимые дублирующие строки в виде представления [inf].[vWaits]:

Реализация представления [inf].[vWaits]

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

БД SRV для администрирования БД и СУБД в целом

До момента написания своих статей, мною была создана БД SRV, которая модифицировалась и дополнялась с учетом полученного опыта и знаний.
Также были разработаны и другие проекты-утилиты в помощь администратору баз данных на C#.NET.

Доступ к проектам здесь.
В корне лежит файл “Описание”, где вкратце описан каждый проект.
Данные решения открыты и распространяются свободно.

Также благодаря вам, уважаемые читатели Хабра, которые оставляли обратную связь в форме комментариев и сообщений, удалось улучшить проект по БД SRV. За что вам большое спасибо!

Но важно отметить, что существующие подходы и решения, описанные во внешних источниках, следует внимательно анализировать, т.к. для вашей задачи данные методы могут не подойти. Необходимо обращать особое внимание на отличие параметров и условий вашей задачи от задачи, которая решается в примере: нагрузка, объём обрабатываемой информации, частота, специфика бизнес-задачи и т.п. Например, процедура, которая работает 40 минут подходит для вызова раз в сутки, но если процесс необходимо запускать с большей частотой, то такое решение может не подойти.

Найдя свой уникальный подход к конкретной задаче, не забывайте поделиться им! Таким образом вы пополните «глобальную базу знаний», которая облегчает поиск решений и идей для новых задач.

Итоги

Были рассмотрены еще некоторые полезные системные представления MS SQL Server, в том числе и для самодокументирования в форме расширенных свойств.

Размышления и идеи

Как вы уже заметили, MS SQL Server уже на достаточном уровне поддерживает NoSQL в виде графовых таблиц (с MS SQL Server 2017) и документоориентированных данных (XML, а с MS SQL Server 2016 и JSON).

Однако, как отмечал еще в 70-х годах 20-го века Эдгар Франк Кодд (по источнику [1]), в реляционной модели можно рассмотреть не простое отношение. Т. е. можно как встраивать одну таблицу в другую, так и наследовать от одной таблицы другую (напомню, что таблица — это отношение в реляционной модели). Наследование таблиц реализовано в некоторых СУБД, например, в том же PostgreSQL. Но мне не доводилось видеть реализацию вложений. Если реализовать и вложения и наследование, а также заложить механизм обработки этих сложных отношений, то получится СУБД, которая обобщает форматы JSON и XML и полностью покрывает так называемую технологию NoSQL (аналог перегрузки операторов в языках программирования, но в СУБД-индексы, агрегация, статистика, обслуживание и т д для таких отношений). Более того, она, возможно, покроет в достаточной степени и все другие модели данных, хотя и будет обрабатываться декларативным языком запросов SQL с некоторыми определенными расширениями и определениями для сложных отношений.

Видя, как быстро развивается MS SQL Server, стоит надеяться на то, что когда-нибудь он придет к реализации сложных отношений и покроет все их разновидности. И тогда пожелания и дальновидность создателя реляционной алгебры будут воплощены в жизнь, а в реляционной модели специалисты откроют совершенно иные аспекты создания различных информационных баз и хранилищ данных.

Источники:

» «Высоко-нагруженные приложения. Программирование, масштабирование, поддержка», СПб.: Питер, 2018 Клеппман М. [1]
» Статистика ожиданий SQL Server'а или пожалуйста, скажите мне, где болит
» Исследуем базы данных с помощью T-SQL
» Документация по SQL
» Улучшения tempdb в SQL Server 2016
» Оптимизация временной БД (tempdb)
» Стандарт оформления T-SQL
» Утилиты для MS SQL Server DBA
» dbForge
» PostgreSQL (наследование)
» MS SQL Server 2017 (графы)
» JSON в MS SQL Server 2016-2017

Автор: Евгений Грибков

Источник

Поделиться

* - обязательные к заполнению поля