dbatools. Часть 1. Общеиспользуемые объекты

добавлено: 20 дек 11
понравилось:0
просмотров: 3247
комментов: 1

теги:

Автор: gds

Эту серию постов я хотел бы посвятить утилитам для DBA (dbatools). dbatools - это набор представлений, функций, процедур, и т.п. для облегчения в обслуживании баз данных и получения основных данных для принятия решения.
Хотелось бы отметить, что все объекты будут создаваться в схеме dbatools, т.ч. давайте создадим схему обслуживания
-- Создаем схему для DBA
--------------------------
if schema_id('dbatools') is null
exec sp_sqlexec 'create schema dbatools'
go


Ниже приведен скрипт создания общеиспользуемых объектов
+ script
-- Создаем схему для DBA
--------------------------
if schema_id('dbatools') is null
exec sp_sqlexec 'create schema dbatools'
go

-- Табличка с системными параметрами
----------------------------------------
if OBJECT_ID(N'dbatools.sys_params',N'U') is not null
	drop table dbatools.sys_params;
go

CREATE TABLE dbatools.sys_params(
		[param] [varchar](512) NOT NULL,
		[value_char] [varchar](128),
		[value_int] int,
Constraint PK_sys_params Primary key (param)
	) 
GO

-- Расширенное представление по индексам
-----------------------------------------
create view [dbatools].[v_indexes] 

as
select 	
		SCHEMA_ID(OBJECT_SCHEMA_NAME(ix.object_id,DB_ID())) as [schema_id],		
		OBJECT_SCHEMA_NAME(ix.object_id,DB_ID()) as [schema_name],
		ix.object_id as [object_id],
		OBJECT_NAME(ix.object_id) as [object_name],
		ix.index_id as [index_id],
		ix.Name as [index_name],
		ix.type,
		ix.type_desc,
		ix.is_unique,
		ix.data_space_id,
		case ds.type
			when 'FG' then ds.name
			when 'PS' then (select distinct name from sys.partition_schemes where data_space_id = ix.data_space_id)
		end as data_space,
		isnull((select fanout from sys.partition_functions prtfn
									join sys.partition_schemes as prtsch on prtfn.function_id = prtsch.function_id
									where prtsch.data_space_id =  ix.data_space_id),0) as part_count,
		ix.ignore_dup_key,
		ix.is_primary_key,
		ix.is_unique_constraint,
		ix.fill_factor,
		ix.is_padded,
		ix.is_disabled,
		ix.is_hypothetical,		
		ix.allow_row_locks,
		ix.allow_page_locks
	
from sys.indexes as ix
join sys.data_spaces as ds on ds.data_space_id = ix.data_space_id
GO

grant select on dbatools.v_indexes to public
go

-- Расширенное представление по индексам со статистикой использования индекса
--------------------
if object_id(N'dbatools.v_indexes_usage_stats',N'V') is not null
	drop view dbatools.v_indexes_usage_stats;
go
create view [dbatools].[v_indexes_usage_stats]
as
select 
		ix.schema_id,		
		ix.schema_name,
		ix.object_id,
		ix.object_name,
		ix.index_id,
		ix.index_name,
		ix.type,
		ix.type_desc,
		ix.is_unique,
		ix.data_space_id,
		ix.data_space,
		ix.part_count,
		ix.ignore_dup_key,
		ix.is_primary_key,
		ix.is_unique_constraint,
		ix.fill_factor,
		ix.is_padded,
		ix.is_disabled,
		ix.is_hypothetical,		
		ix.allow_row_locks,
		ix.allow_page_locks,
		iu.user_seeks,
		iu.user_scans,
		iu.user_lookups,
		iu.system_seeks,
		iu.system_scans,
		iu.system_lookups
	
from dbatools.v_indexes as ix
join sys.dm_db_index_usage_stats as iu on iu.index_id = ix.index_id and iu.object_id = ix.object_id and iu.database_id = DB_ID()

GO
grant select on dbatools.v_indexes_usage_stats to public
go		

-- Представление о просмотре функций секционирования и параметров
------------------------------------------------------------------
if object_id(N'dbatools.v_partition_scheme_info',N'V') is not null
	drop view dbatools.v_partition_scheme_info;
go

create view dbatools.v_partition_scheme_info 

as
select 
		prtsch.data_space_id as [DATA_SPACE_ID],
		prtsch.name as [SCHEME_NAME],
		prtfn.name as [FUNC_NAME],
		prtfn.fanout as [FUNC_PART],
		TYPE_NAME(prtpar.user_type_id) as [TYPE],
		prtrange.boundary_id as [RANGE_BOUNDARY],
		prtrange.value as [RANGE_VALUE]
from sys.partition_schemes as prtsch
	join sys.partition_functions as prtfn on prtfn.function_id = prtsch.function_id
	join sys.partition_parameters as prtpar on prtpar.function_id = prtfn.function_id
	join sys.partition_range_values as prtrange on prtrange.function_id = prtfn.function_id	
go

grant select on dbatools.v_partition_scheme_info to public
go

-- Представление о просмотре информации по файлам данных 
-- и журналу транзакций (размер файла/ Занято / Свободно)
----------------------------------------------------------

if OBJECT_ID(N'dbatools.v_show_free_spacedata_all',N'V') is not null
	drop view dbatools.v_show_free_spacedata_all
go

create view dbatools.v_show_free_spacedata_all 
as
with spaceused as(
select
	a.FILEID,
	[FILE_SIZE_MB] = 
		convert(decimal(12,2),round(a.size/128.000,2)),
	[SPACE_USED_MB] =
		convert(decimal(12,2),round(fileproperty(a.name,'SpaceUsed')/128.000,2)),
	[FREE_SPACE_MB] =
		convert(decimal(12,2),round((a.size-fileproperty(a.name,'SpaceUsed'))/128.000,2)) ,
	NAME = left(a.NAME,128),
	FILENAME = left(a.FILENAME,520),
    FILE_GROUP = fg.name,
	STATE_DESC = df.state_desc,

	[DEFAULT] = fg.is_default 
from
	sys.sysfiles a
left join sys.database_files df on df.file_id = a.fileid
left join sys.filegroups as fg on fg.data_space_id =  df.data_space_id
) 
select 
	FILEID, 
	FILE_SIZE_MB,
	SPACE_USED_MB,
	FREE_SPACE_MB,
	[NAME],
	[FILENAME],
	FILE_GROUP,
	[STATE_DESC],
	[DEFAULT]
 from spaceused
union all
select	
	NULL as FILEID,
	sum(FILE_SIZE_MB)as FILE_SIZE_MB, 
	sum(SPACE_USED_MB)as SPACE_USED_MB,
	sum(FREE_SPACE_MB)as FREE_SPACE_MB,
	NULL as [NAME],
	NULL as [FILENAME],
	NULL as [STATE_DESC],
	NULL as FILE_GROUP,
	NULL as [DEFAULT]
from spaceused
go

grant select on dbatools.v_show_free_spacedata_all to public
go

Ниже приведу краткое описание
1. Таблица dbatools.sys_params. В нее будет писать параметры испольуемые в наших процедурах и функциях.
2. Представление dbatools.v_indexes. Это расширенное представление sys.indexes с добавлением некоторых полей:
2.1. schema_id - идентификатор схемы
2.2. schema_name - Наименование схемы
2.3. object_name - Наименование объекта, которому пренадлежит индекс
2.4. data_space - Наименование файловой группы или схемы секционирования где расположен индекс
2.5. part_count - В случае если индекс расположен в схеме секционирования - вернет количество партиций в данной схеме.
Задача: Переместить все некластерные индексы из одной файловой групы (PRIMARY) в другую.
Решение: Можно воспользоваться представлением для просмотра списка индексов, которые находятся в файловой группе (PRIMARY)
select * from dbatools.v_indexes
where data_space = 'PRIMARY'
	and index_id > 1 
	and schema_name != N'sys'
order by schema_name,object_name
go

3. dbatools.v_indexes_usage_stats. Расширенное представление по индексам со статистикой использования индекса. Статистика использования индексов берется из системного динамического представления sys.dm_db_index_usage_stats. По этому представлению можно определить эффективность использования индексов, а также определить какие индексы вообще не используются. Минусом этого является то, что вся статистика сбрасывается после перезапуска SQL Server'а или восстановление бд из бекапа, а также при атаче.
4. dbatools.v_partition_scheme_info. Представление о просмотре функций секционирования и параметров
5. dbatools.v_show_free_spacedata_all. Пожалуй одно из наиболее часто используемых мною представлений. Оно показывает размер файла на диске, занято данными в файле, свободно в фейле под данные, по каждому файлу в базе данных
Например.
select * from dbatools.v_show_free_spacedata_all 


FILEID FILE_SIZE_MB SPACE_USED_MB FREE_SPACE_MB NAME FILENAME FILE_GROUP STATE_DESC DEFAULT
------ -------------- -------------- --------------- ---------- ---------------------------- ------------ ------------ -------
1 16069.25 35.31 16033.94 tempdev H:\MSSQL\DATA\tempdb.mdf PRIMARY ONLINE 1
2 894.88 36.98 857.90 templog H:\MSSQL\DATA\templog.ldf NULL ONLINE NULL
NULL 16964.13 72.29 16891.84 NULL NULL NULL NULL NULL

(3 row(s) affected)

Информация по индексам в БД.
-- список индексов, по которым нет статистики
select  
	 quotename(i.schema_name)+'.'+quotename(i.object_name) as [object], 
	 i.index_id, quotename(i.index_name) as index_name, 
	 i.type_desc, i.data_space, i.part_count,si.rowcnt
from dbatools.v_indexes i
inner join sys.sysindexes si on si.id = i.object_id and si.indid = i.index_id
where schema_name not in ('sys','dbatools') 
	 and not exists(select * from dbatools.v_indexes_usage_stats  
						  where object_id = i.object_id and index_id = i.index_id)
	 and i.is_primary_key = 0 and is_unique = 0 and index_id > 0
order by object_name,index_id
go

1. i.is_primary_key = 0 - исключаем первичные ключи.
2. is_unique = 0 - исключаем уникальные индексы, т.к. их основное назначение обеспечить уникальность значений.
3. index_id > 0 - исключаем таблицы-кучи.

-- список неиспользуемых индексов
select  
	 quotename(i.schema_name)+'.'+quotename(i.object_name) as [object], 
	 i.index_id, quotename(i.index_name) as index_name, 
	 i.type_desc, i.data_space, i.part_count,si.rowcnt, i.user_lookups
from dbatools.v_indexes_usage_stats i
inner join sys.sysindexes si on si.id = i.object_id and si.indid = i.index_id
where schema_name not in ('sys','dbatools') 
	 and i.is_primary_key = 0 and is_unique = 0
	 and user_seeks = 0 and user_scans = 0 and index_id > 0 
order by object_name,index_id


1. i.is_primary_key = 0 - исключаем первичные ключи.
2. is_unique = 0 - исключаем уникальные индексы, т.к. их основное назначение обеспечить уникальность значений.
3. index_id > 0 - исключаем таблицы-кучи.
4. user_seeks = 0 and user_scans = 0 - ищем только те индексы, по которым не было поиска или скана.
В данной версии скрипта не учитывается количество просмотров (lookups) по индексу. Я посчитал не включать в ограничение этот параметр, а оставить на ответственности ДБА. По моему опыту, просмотры по индексу чаще всего происходят по кластерному индексу и/или первичному ключу (второе мы исключили).

-- Детальная статистика по индексам (выбранные таблицы)
select  quotename(schema_name)+'.'+quotename(object_name) as [object],
		  quotename(index_name) as index_name,type_desc,
		  user_seeks,user_scans,user_lookups,is_unique,data_space,part_count
from dbatools.v_indexes_usage_stats s
where	  schema_name not in ('dbatools','sys') and s.index_id > 0 
--		  and is_primary_key = 0
		  and object_name in ('Dim','Task')
--and object_name not like 'MSmerge_%' collate Cyrillic_General_CS_AS
order by s.object_name
GO


-- Детальная статистика по индексам (выбранные таблицы, секции детально)
select  quotename(schema_name)+'.'+quotename(object_name) as [object],
		  quotename(index_name) as index_name,type_desc,p.partition_number,p.rows,
		  user_seeks,user_scans,user_lookups,is_unique,data_space,part_count
from dbatools.v_indexes_usage_stats s
	 inner join sys.partitions p on p.object_id = s.object_id and p.index_id = s.index_id
where	  schema_name not in ('dbatools','sys') and s.index_id > 0 
--		  and is_primary_key = 0
		  and object_name in ('Dim','Task')
--and object_name not like 'MSmerge_%' collate Cyrillic_General_CS_AS
order by s.object_name
GO

Здесь приводиться детальная статистика по всем индексам (по которым существует статистика) из таблиц Dim и Task.
В эти 3 запроса я не включал фильтрацию по отключенным индексам (is_disabled = 1).
-- перечень отключенных индексов
select  quotename(object_schema_name(i.object_id,db_id()))+'.'+quotename(object_name(i.object_id)) as [object],
		  index_id,quotename(i.name) as index_name
 from sys.indexes i
where i.is_disabled = 1
-- или
select  quotename(i.schema_name)+'.'+quotename(i.object_name) as [object],
		  index_id,quotename(i.index_name)as index_name
 from dbatools.v_indexes i
where i.is_disabled = 1


Данная статья будет периодически обновлятся. Обо всех обновлениях я буду писать в комментах.

Ссылки на связанные темы:
1. dbatools. Часть 2. Реализация последовательности (sequence)
2. dbatools. Часть 3. Обслуживание индексов

Комментарии




Необходимо войти на сайт, чтобы оставлять комментарии