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

  • Tutorial

Предисловие


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

Когда свои реализованные идеи, опыт, а также всю ту информацию, что не дает покоя, оформляешь в публикации, рано или поздно приходит логическая точка всему ранее написанному потоку информации. Эта статья будет отличаться от всех ранее опубликованных мною своей нестрогостью и более свободным стилем изложения текста, а также она завершит изложение всего моего накопленного опыта по 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]). Для удобства можно создать следующие представления:
1) [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
/*
	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

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 мс — низкая производительность, необходимо сделать детальный анализ, имеются проблемы с вводом-выводом для временной базы данных
	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

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
Поделиться публикацией
Ой, у вас баннер убежал!

Ну. И что?
Реклама
Комментарии 5
  • +1
    Хорошая подборка! Даже немного жаль что в последнее время редко имею дело с разработкой БД. Подобные знания обычно добываются по крупицам.

    Если возможно, было бы очень интересно знать ваше мнение об одном инструменте для MS SQL Server, который сделал для своих нужд. Код по ссылке.
    github.com/usharik/MsSqlDependencyBrowser
    • 0
      Весьма интересный инструмент-попробовал, а он распространяется бесплатно?
      Рекомендую указать лицензию в виде файла лицензии в корне репозитория или отдельного решения
      • 0
        Просмотрел код-лучше не использовать select FieldName=Exp стиль, т к не рекомендовано (по-моему здесь находил: Нерекомендуемые функции ядра СУБД в SQL Server 2017)
        Лучше по стандарту: select Exp as FieldName
        В остальном-отличное решение для визуализации зависимостей
        Еще бы хорошо что-то вроде дерева со схемой сделать-еще лучше если интерактивную)
        • 0
          Благодарю. Приятно удивлен вашей реакцией. Несколько раз пробовал публиковать ссылки на программу на разных форумах и реакция была не очень благожелательной.
          Про дерево я думал, но более менее простого способа его реализовать в браузере пока не придумал.

          На данный момент инструмент бесплатен и планов по коммерциализации пока нет. Разве что ссылку на PayPal для донейтов прикручу может быть, если прогой действительно будут пользоваться))
          • 0
            Не очень, т к большинство думает, что все и так есть, а вот оценить решение, пусть даже которое имеет аналоги, не каждый может
            И ещё смотря что создавать и как
            Данное решение можно хоть на производство-это просто справка и на бизнес-процессы не влияет
            Другое дело, если вы делаете утилиту, влияющую на сами данные или на бизнес-процессы, или их оптимизацию или автоматизацию. Тогда в этом случае хорошо бы решение отдать в отдел тестирования, но прежде попробовать что-то готовое и решить-стоит ли своё писать и во сколько часов обойдётся своё решение (т е может купить подходящее решение окажется значительно дешевле)
            В крайнем случае, если по каким-либо причинам не возможно приобрести стороннее решение (а для конкретных задач это частое явление, т к унификация в БД-это плохой подход и так можно делать лишь на первом этапе оптимизации и автоматизации обслуживания)
            И вот об этом многие тоже опять забывают)
            В крайнем случае-даже разрабатывая велосипеды, вы начинаете понимать как не нужно писать, как разработаны аналоги и как это все работает с ньюансами в отличии от тех, кто просто пользуется но новыми решениями и настраивает их. Т е по сути есть конфигурирование и разработка-и это две большие разницы, которую порой тоже не видят, хотя приходится заниматься и тем и тем
            Так что смотрите аналоги, пишите сами, исследуйте и экспериментируйте, и конечно не забывайте читать классику (а не просто ходить на тренинги)
            Классика концепций-наше все (как было в 70-х годах и до этого все придумано, так и осталось, а технологии и языки, что их реализуют постоянно меняются, умирают и вновь возвращаются под другими названиями)

      Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.

      Самое читаемое