Типы данных переменной длины и значения null.

Выбор типов данных для столбцов таблицы является чрезвычайно важным решением при проектировании базы данных. Я пропагандирую подход, при котором следует стремиться к тому, чтобы не хранить ничего лишнего. А также как можно реже использовать null, стараясь всегда объявлять столбцы с опцией not null.

Если у вас в базе данных хранятся текстовые данные одинаковой длины, то не следует использовать для их хранения тип varchar. Вместо него следует использовать тип char.

В чем же потенциальные минусы использования типа varchar. Рассмотрим в простейшем приближении схему хранения данных строки таблицы на странице базы данных:

Столбцы постоянной длины всегда хранятся в начале строки. Для доступа к ним не требуется никакой дополнительной информации, так как, зная типы данных и место начала строки, всегда можно сказать в каком месте начинается и оканчивается значение столбца. Для того чтобы прочитать столбец переменной длины, надо знать где он оканчивается. Для этой цели после столбцов постоянной длины хранится байтовый массив, в котором каждые два байта обозначают место, в котором оканчивается очередной столбец переменной длины. Этот массив занимает место, увеличивая общий размер таблицы.
В дополнение к этому, если в таблице есть строки, допускающие пустые значения, то в каждой строке есть битовая маска, в которой зашита информация о том какие столбцы принимают значения null, а какие нет.
Все это в совокупности приводит к тому что таблица может занимать больше места чем могла бы. Как следствие для поиска данных в ней требуется выполнять больше операций чтения, чтобы считать больше страниц. Нужно стремиться к тому чтобы страниц было меньше.
Приведем следующий пример двух таблиц, предназначенных для хранения данных клиентов: зарплатный счет, инн, номер пенсионного свидетельства.

if object_id ( N'dbo.PersData', N'U' ) is null

begin

       create table dbo.PersData

       (

             iRowId       int identity ( 1, 1 )      not null,

             vcAccount    char ( 20 )                not null,

             vcSnils      char ( 14 )                not null,

             vcInn        char ( 12 )                not null,

             constraint PK_PersData_iRowId primary key clustered ( iRowId asc ) on [PRIMARY]

       ) on [PRIMARY]

end

go

if object_id ( N'dbo.PersDataVar', N'U' ) is null

begin

       create table dbo.PersDataVar

       (

             iRowId       int identity ( 1, 1 )      not null,

             vcAccount    varchar ( 20 )             null,

             vcSnils      varchar ( 13 )             null,

             vcInn        varchar ( 12 )             null,

             constraint PK_PersDataVar_iRowId primary key clustered ( iRowId asc ) on [PRIMARY]

       ) on [PRIMARY]

end

go

Вторая таблица имеет неоптимальные типы данных. Ее столбцы с атрибутами допускают пустые значения и имеют переменную длину. В остальном таблицы одинаковы. Наполним их данными одинакового размера, так чтобы в таблицах было одинаковое число строк:

insert into dbo.PersData with ( tablock ) ( vcAccount, vcSnils, vcInn )

       select

             left ( concat

             (

                    abs ( binary_checksum ( newid () ) ),

                    abs ( binary_checksum ( newid () ) ),

                    abs ( binary_checksum ( newid () ) )

             ), 20 ),

             left ( concat ( '', abs ( binary_checksum ( newid () ) ) ), 3 ) + '-' +

             left ( concat ( '', abs ( binary_checksum ( newid () ) ) ), 3 ) + '-' +

             left ( concat ( '', abs ( binary_checksum ( newid () ) ) ), 3 ) + '-' +

             left ( concat ( '', abs ( binary_checksum ( newid () ) ) ), 2 ),

             left ( concat

             (

                    abs ( binary_checksum ( newid () ) ),

                    abs ( binary_checksum ( newid () ) ),

                    abs ( binary_checksum ( newid () ) )

             ), 12 )

       from

             master.dbo.spt_values t1

                    cross join

             master.dbo.spt_values t2

insert into dbo.PersDataVar with ( tablock ) ( vcAccount, vcSnils, vcInn )

       select

             left ( concat

             (

                    abs ( binary_checksum ( newid () ) ),

                    abs ( binary_checksum ( newid () ) ),

                    abs ( binary_checksum ( newid () ) )

             ), 20 ),

             left ( concat ( '', abs ( binary_checksum ( newid () ) ) ), 3 ) + '-' +

             left ( concat ( '', abs ( binary_checksum ( newid () ) ) ), 3 ) + '-' +

             left ( concat ( '', abs ( binary_checksum ( newid () ) ) ), 3 ) + '-' +

             left ( concat ( '', abs ( binary_checksum ( newid () ) ) ), 2 ),

             left ( concat

             (

                    abs ( binary_checksum ( newid () ) ),

                    abs ( binary_checksum ( newid () ) ),

                    abs ( binary_checksum ( newid () ) )

             ), 12 )

       from

             master.dbo.spt_values t1

                    cross join

             master.dbo.spt_values t2

go 20

В таблицах теперь одинаковое число строк. Каждый соответствующий столбец имеет одну и ту же длину. Выполним процедуру sp_spaceused, чтобы посмотреть сколько места используется данными и индексами в каждой из таблиц:

exec sp_spaceused [dbo.PersData]

exec sp_spaceused [dbo.PersDataVar]

Размер данных и индекса для dbo.PersData составляет 7.07 Гб, для таблицы dbo.PersDataVar размер составляет 8.07 Гб. Это на целый гигабайт больше. И этот гигабайт не дает ничего полезного, а наоборот занимает место на диске, вызывает большее число операций чтения, замедляя запросы!
Поэтому при проектировании таблицы следует избегать значений null, а там где надо, использовать типы постоянной длины. Также следует быть экономными при выборе того насколько вместительными будут ваши типы данных. Например, если от столбца с датой требуется хранение только числа, месяца и года, не надо использовать тип данных datetime, достаточно просто типа date, который занимает намного меньше места. Если таблица атрибутов не будет хранить более 64 тысяч строк, то для ее идентификатора достаточно использовать тип smallint, а не int. Так как каждая строка любой таблицы, ссылающейся на такой справочник, будет экономить по два байта.

Отслеживание запросов с отсутствующими индексами

Думаю, что многие видели в планах выполнения зеленую надпись вверху о том что в запросе не хватает полезного индекса. Так оптимизатор указывает, что запрос мог бы выполняться эффективнее при наличии данного индекса. При этом можно увидеть и столбцы на которых нужен индекс и оценку того на сколько процентов запрос мог бы работать лучше с таким индексом. Приведем пример, в котором создаются таблица и неоптимальный запрос:

if object_id ( N'dbo.Data', N'U' ) is null

begin

       create table dbo.Data

       (

             iRowId int identity ( 1, 1 )      not null,

             iItem  int                        not null,

             vcStr  varchar ( 100 )            not null

       ) on [PRIMARY]

end

go

insert into dbo.Data with ( tablock ) ( iItem, vcStr )

       select abs ( binary_checksum ( newid () ) ), concat ( '', newid () )

       from

             master.dbo.spt_values t1

                    cross join

             master.dbo.spt_values t2

go

create nonclustered index IX_iRowId on dbo.Data ( iRowId asc ) on [PRIMARY]

go

select *

from dbo.Data with ( nolock )

where iItem between 1000 and 1000000

Очевидно, что в приведенном запросе не хватает индекса на столбце iItem. В плане выполнения это также можно увидеть:

Существует способ автоматически извлекать и логировать эту полезную информацию. Это позволяет создавать автоматические уведомления для авторов запросов и более оперативно реагировать на снижение производительности баз данных. Для извлечения планов выполнения текущих запросов можно воспользоваться каталогом dm_exec_requests и столбцами plan_handle, sql_handle в сочетании с функциями dm_exec_text_query_plan, dm_exec_sql_text для извлечения текста запроса и плана выполнения в формате XML.
Функция dm_exec_text_query_plan чрезвычайно полезна, так как получает в качестве параметров не только код плана, но и оффсеты пакета, что позволяет получать план не всего пакета, а отдельной инструкции. Я начну писать хранимую процедуру, которая проверяет текущие запросы на предмет отсутствующих индексов.

if object_id ( N'dbo.GetMissingINdexes', N'P' ) is null

begin

       exec ( N'create proc dbo.GetMissingINdexes as return' )

end

go

alter proc dbo.GetMissingINdexes

as

begin

       set nocount, xact_abort on

       if object_id ( N'tempdb..#QueryData', N'U' ) is not null

             drop table #QueryData

       select *

             into #QueryData

       from

       (

             select

                    iSessionId   = bat.session_id,

                    vcDb         = db_name ( pl.dbid ),

                    vcObj        = quotename ( db_name ( pl.dbid ) ) + N'.' +

                           quotename ( object_schema_name ( pl.objectid, pl.dbid ) ) + N'.' +

                           quotename ( object_name ( pl.objectid, pl.dbid ) ),

                    vcSql        = substring

                    (

                           txt.text,

                           iif ( bat.statement_start_offset > 0, bat.statement_start_offset / 2, 1 ),

                           iif ( bat.statement_end_offset = -1, len ( cast ( txt.text as nvarchar ( max ) ) ), ( bat.statement_end_offset - bat.statement_start_offset ) / 2 + 2 )

                    ),

                    xPlan        = try_convert ( xml, pl.query_plan )

             from

                    sys.dm_exec_requests bat

                           outer apply

                    sys.dm_exec_sql_text ( bat.sql_handle ) txt

                           outer apply

                    sys.dm_exec_text_query_plan ( bat.plan_handle, bat.statement_start_offset, bat.statement_end_offset ) pl

       ) Queries

       where

             Queries.vcSql is not null and

             Queries.xPlan is not null and

             Queries.iSessionId is not null and

             Queries.vcDb not in ( 'master', 'model', 'msdb', 'ReportServer', 'ReportServerTempDB', 'semanticsdb' )

       alter table #QueryData add primary key clustered ( iSessionId asc ) on [PRIMARY]

       create primary xml index IX_xPlan on #QueryData ( xPlan )

       create xml index IX_xPlan_path on #QueryData ( xPlan ) using xml index IX_xPlan for path

Запрос извлекает планы выполнения и сохраняет их в таблице. На таблице строится первичный ключ, что позволяет создать первичный xml-индекс. Вторичный xml-индекс типа path позволит быстро находить строки, в которых есть конкретные узлы xml-документа. Наша ближайшая цель - найти все планы, в которых есть элемент MissingIndexes. Делается это так:

       declare @iSessionId smallint, @xPlan xml, @fInfl float, @vcDb nvarchar ( 200 ), @vcSch nvarchar ( 200 ), @vcTab nvarchar ( 200 ), @xIndData xml

       declare curInd cursor local static forward_only for

             select iSessionId, xPlan

             from #QueryData

             where xPlan.exist

             (

                    'declare namespace NS="http://schemas.microsoft.com/sqlserver/2004/07/showplan";

                    /NS:ShowPlanXML/NS:BatchSequence/NS:Batch/NS:Statements/NS:StmtSimple/NS:QueryPlan/NS:MissingIndexes'

             ) = 1

       open curInd

       fetch next from curInd into @iSessionId, @xPlan

       while @@fetch_status = 0

       begin

При применении метода exist помогает вторичный xml-индекс. Теперь найдем оценку влияния отсутствия индекса, а также таблицу, на которой не хватает индекса, и схему и базу данных, которым принадлежит таблица. Для этого воспользуемся методом value.

             set @fInfl = @xPlan.value

             (

                    'declare namespace NS="http://schemas.microsoft.com/sqlserver/2004/07/showplan";

                    (/NS:ShowPlanXML/NS:BatchSequence/NS:Batch/NS:Statements/NS:StmtSimple/NS:QueryPlan/NS:MissingIndexes/NS:MissingIndexGroup/@Impact)[1]',

                    'float'

             )

            

             set @vcDb = parsename ( @xPlan.value

             (

                    'declare namespace NS="http://schemas.microsoft.com/sqlserver/2004/07/showplan";

                    (/NS:ShowPlanXML/NS:BatchSequence/NS:Batch/NS:Statements/NS:StmtSimple/NS:QueryPlan/NS:MissingIndexes/NS:MissingIndexGroup/NS:MissingIndex/@Database)[1]',

                    'nvarchar ( 200 )'

             ), 1 )

             set @vcSch = parsename ( @xPlan.value

             (

                    'declare namespace NS="http://schemas.microsoft.com/sqlserver/2004/07/showplan";

                    (/NS:ShowPlanXML/NS:BatchSequence/NS:Batch/NS:Statements/NS:StmtSimple/NS:QueryPlan/NS:MissingIndexes/NS:MissingIndexGroup/NS:MissingIndex/@Schema)[1]',

                    'nvarchar ( 200 )'

             ), 1 )

             set @vcTab = parsename ( @xPlan.value

             (

                    'declare namespace NS="http://schemas.microsoft.com/sqlserver/2004/07/showplan";

                    (/NS:ShowPlanXML/NS:BatchSequence/NS:Batch/NS:Statements/NS:StmtSimple/NS:QueryPlan/NS:MissingIndexes/NS:MissingIndexGroup/NS:MissingIndex/@Table)[1]',

                    'nvarchar ( 200 )'

             ), 1 )

Теперь необходимо получить данные о столбцах, составляющих индекс. Следующий запрос инициализирует переменную xml-документом, который содержит узлы, относящиеся только к столбцам:

             select @xIndData = Info.IndCol.query ( '.' )

             from @xPlan.nodes

             (

                    'declare namespace NS="http://schemas.microsoft.com/sqlserver/2004/07/showplan";

                    /NS:ShowPlanXML/NS:BatchSequence/NS:Batch/NS:Statements/NS:StmtSimple/NS:QueryPlan/NS:MissingIndexes/NS:MissingIndexGroup'

             ) Info ( IndCol )

Используя метод nodes, можно написать запрос, который даст представление переменной @xIndData в табличном виде:

             ;

             with Cols

             as

             (

                    select

                           vcType = ColList.Col.value ( '(@Usage)[1]', 'nvarchar ( max )' ),

                           xCol = ColList.Col.query ( '.' )

                    from @xIndData.nodes

                    (

                           'declare namespace NS="http://schemas.microsoft.com/sqlserver/2004/07/showplan";

                          /NS:MissingIndexGroup/NS:MissingIndex/NS:ColumnGroup'

                    ) ColList ( Col )

             )

             select vcType, vcColumn = parsename ( colinfo.onecol.value ( '(@Name)[1]', 'nvarchar ( max )' ), 1 )

             from

                    Cols

                           outer apply

                    Cols.xCol.nodes

                    (

                           'declare namespace NS="http://schemas.microsoft.com/sqlserver/2004/07/showplan";

                           /NS:ColumnGroup/NS:Column'

                    ) colinfo ( onecol )

             select @iSessionId as iSessionId, @fInfl as fInfl, @vcDb as vcDb, @vcSch as vcSch, @vcTab as vcTab

             fetch next from curInd into @iSessionId, @xPlan

       end

       close curInd

       deallocate curInd

end

go

В коде можно вставить логирование или рассылку по электронной почте. Запустив запрос к таблице в одном соединении, а затем процедуру в другом, можно увидеть результаты ее работы: