Александр Волок

Вскрывая планы выполнения SQL Server

2008-06-22T07:06:00Z

Почему этот запрос выполняется медленно?
Используется ли этот индекс?
Почему один запрос выполняется быстрее второго?
Вы проверяли планы выполнения?

Часто на форумах можно встретить подобные вопросы.

Целью книги Dissecting SQL Server Execution Plans by Grant Fitchey - собрать как можно больше информации о планах выполнения, организовав, предоставить ее в одном источнике.

Скачать книгу бесплатно можно здесь: Dissecting SQL Server Execution Plans by Grant Fitchey

SSMS Tools Pack 1.0 доступен для загрузки

2008-05-09T06:47:00Z

SSMS Tools Pack - это набор дополнений к SQL Server Management Studio и SQL Server Management Studio Express.

Перечень некоторых возможностей:

Изменение регистра ключевых слов.
Запуск скриптов в множестве баз данных.
Сохранение графической копии плана выполнения в буфере обмена.
Поиск в GRID варианте отображения результата и в планах выполнения.
Генерация INSERT выражений для выборочной таблицы, всей базы или текущего результата запроса.
Ведение истории выполненых запросов.
Запуск пользовательских скриптов из контекстного меню в Object Explorer.
Генерация хранимых процедур CRUD (Create, Read, Update, Delete).
Новые шаблоны запросов.

Дополнения доступны на этом ресурсе: http://www.ssmstoolspack.com/Main.aspx

SQL Server 2008: Дефрагментация куч

2008-03-31T19:23:00Z

В SQL Server 2005 и более ранних версиях дефрагментация куч была непростой задачей. Для этих целей данные таблиц копировали в новые или же добавляли, затем удаляли кластерный индекс.

В SQL Server 2008, задача дефрагментации значительно упростилась. Разработчики добавили команде ALTER TABLE ... REBUILD поддержку куч.

В следующем примере будет создана простая таблица (куча) и вставлено небольшое количество данных. В последствии, каждая запись новой таблицы будет обновлена значениями, которые значительно превышают размеры изначальных. Результатом станет в меньшей мере фрагментация, но и большое количество указателей (forwarding pointers). Причина появления большого количества указателей кроется в том, что текущие записи больше не могут помещаться на тех же страницах кучи и вынуждены переместится на другие страницы, но оставив указатели на старых страницах. Указатели на записи остаются не изменны в индексах.

Как видите, дефрагментация куч очень упрощена:

create table heapDemo
(   
    value varchar(1000)
)
GO
set nocount on
insert into heapDemo
select 'hi'
go 10000
--Увеличим размер строки в 500 раз
update heapDemo
set value = replicate('hi',500)

Далее следует проверка фрагментации таблицы:

select index_type_desc, fragment_count, page_count, forwarded_record_count 
from sys.dm_db_index_physical_stats(db_id(),default,default,default,'DETAILED')
where object_id = object_id('heapDemo')

Что вернет:

index_type_desc fragment_count page_count forwarded_record_count
--------------------- -------------------- -------------------- ----------------------
HEAP 6 1443 9961

Далее, дефрагментация кучи и проверка фрагментации:

alter table heapDemo rebuild
select index_type_desc, fragment_count, page_count, forwarded_record_count
from sys.dm_db_index_physical_stats(db_id(),default,default,default,'DETAILED')
where object_id = object_id('heapDemo')

Результатом стала куча, в которой отсуствуют указатели – желаемый результат.

index_type_desc fragment_count page_count forwarded_record_count
--------------------- -------------------- -------------------- ----------------------
HEAP 3 1440 0

Полезный блог: SQL Server 2008 Application Compatibility Blog

2008-01-31T21:53:00Z

SQL Server 2008 уже не за горами.
Многие нововведения в этой версии обсуждались как в моем блоге, так и в других тематических ресурсах.
Но, как и при внедрении любой другой новой версии продукта, существуют проблемы совместимости с текущими решениями, которые созданы для более ранних версий.
Представляю Вашему вниманию блог SQL Server 2008 Application Compatibility Blog целью создания которого и есть освещение подобных проблем, как и освещение преимуществ от внедрения новой версии SQL Server

SQL Server 2005 Upgrade Technical Reference Guide

2008-01-14T10:39:00Z

Александр Гладченко в своем блоге опубликовал ссылку на документ, который будет полезен во время оценки рисков при миграции с SQL Server 7 и 2000 на SQL Server 2005.

В документе, на 350 страницах, представлено руководство, ориентированное на администраторов SQL Server, разработчиков приложений баз данных и поставщиков решений, в котором регламентируется перевод баз данных, обслуживаемых серверами версий SQL Server 2000 или 7.0, под управление SQL Server 2005.

Опубликован новый whitepaper: Database Mirroring and Log Shipping Working Together

2008-01-13T08:14:00Z

Краткий план документа:

Converting a log shipping setup to a database mirroring partnership

Setting up log shipping to a 3rd destination (i.e. warm standby to go with the mirroring hot standby)

Swapping the roles of the mirroring server and the log shipping secondary server

Whitepaper доступен по этому адресу

Тип данных TIME в SQL Server 2008

2008-01-12T09:51:00Z

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

Значение точности может варьироваться от 0 до 7, указывая количество цифр в дробной части секунды. По умолчанию, точность TIME(7), что значит, что секунды в этом типе данных будут храниться с дробной частью в диапазоне от .0000000 до .9999999.

Тип TIME хранится как INTEGER с варьирующимся размером, который напрямую зависит от точности. При точности 0-2, этот тип хранится в виде 3-байтового INT, 3-4 как 4-байтовый INT, а для точности 5-7 выделяется 5-байтовый INT.

Значение точности используется, для варьирования гранулярности при просчете времени после полуночи, : от 1 секунды до 100 наносекунд.

К примеру, если значение времени хранится в поле с типом Time и указанной точностью n, время в секундах после полуночи в будет рассчитано таким образом: t / 10ⁿ.

Далее следует краткое изложение хранения и точности TIME:

Точность	Хранение (байт)	Секунд	Миллисекунд	Наносекунд
TIME(0)	3	1	1000	1000000000
TIME(1)	3	0.1	100	100000000
TIME(2)	3	0.01	10	10000000
TIME(3)	4	0.001	1	1000000
TIME(4)	4	0.0001	0.1	100000
TIME(5)	5	0.00001	0.01	10000
TIME(6)	5	0.000001	0.001	1000
TIME(7)	5	0.0000001	0.0001	100

Существует возможность привести значение TIME в INT, хотя это требует несколько шагов:

    DECLARE @Time TIME(7) = '00:01:00' -- Формат HH:mm:SS[.nnnnnnn]
    DECLARE @BinaryTime VARBINARY(8) 

    
    SET @BinaryTime = SUBSTRING(CONVERT(VARBINARY, REVERSE(CONVERT(VARBINARY, @Time))),
                                                    1,
                                                    DATALENGTH(@Time))                                               

    SELECT CONVERT(BIGINT, @BinaryTime)
    -- Результат: 600000000

В приведенный выше примере получен результат – 600000000, который, учитывая точность, вполне корректен. Точность равна 7, поэтому время одной минуты после полуночи будет равно 60 секундам = 600000000 / 10⁷.

    
    DECLARE @Time TIME(3) = '00:01:00' -- Формат HH:mm:SS[.nnnnnnn]
    DECLARE @BinaryTime VARBINARY(8) 
    
    SET @BinaryTime = SUBSTRING(CONVERT(VARBINARY, REVERSE(CONVERT(VARBINARY, @Time))),
                                                   1,
                                                    DATALENGTH(@Time))                                               

    SELECT CONVERT(BIGINT, @BinaryTime)
    -- Результат: 60000

С применением точности равной 3, получен результат 60000, который тоже корректен, поскольку 60 секунд = 60000/10³.

Дополнительную информацию о типе TIME можно подчерпнуть в BOL 2008
Подготовленно по материалам Danny's SQL Server and Internals Viewer Blog

Тип данных DATE в SQL Server 2008

2008-01-09T08:09:00Z

SQL Server 2008 содержит несколько новых типов данных, включая типы для работы с датой и временем:
DATE – содержит значения даты
TIME – содержит значения времени, с точностью до 100 наносекунд
DATETIME2 – содержит значения даты и времени, но с более высокой точностью времени
DATETIMEOFFSET – содержит значения даты и времени включающие смещение по часовому поясу.

Внутреннее представление типа DATE

Тип DATE попросту содержит дату, в диапазоне от 1 января 0001 до 31 декабря 9999. Внутри же тип хранится в виде 3-х байтового INTEGER. Значение INT это количество прошедших дней после базовой даты 01.01.0001.

Напрямую конвертировать INT в DATE невозможно. Результатом выполнения:

SELECT CONVERT(DATE, 1)

будет следующая ошибка:

Msg 529, Level 16, State 2, Line 1
Explicit conversion from data type int to date is not allowed.

Но все же, существует возможность конвертации INT в DATE посредством промежуточного конвертирования целочисленного типа в BINARY(3) с последующим реверсом байтов и итоговом конвертировании в DATE.

В следующем примере приведено пошаговое увеличение 24-битового INT, который соответствует базовой дате:

    DECLARE @IntValue INT  
    SET @IntValue = 0
    
    SELECT CONVERT(DATE, CONVERT(BINARY(3), REVERSE(CONVERT(BINARY(3), @IntValue))))
    -- Результат: 0001-01-01
    
    SET @IntValue = 1
    
    SELECT CONVERT(DATE, CONVERT(BINARY(3), REVERSE(CONVERT(BINARY(3), @IntValue))))
    -- Результат: 0001-01-02
    
    SET @IntValue = 2
    
    SELECT CONVERT(DATE, CONVERT(BINARY(3), REVERSE(CONVERT(BINARY(3), @IntValue))))
    -- Результат: 0001-01-03
    
    SET @IntValue = 3
    
    SELECT CONVERT(DATE, CONVERT(BINARY(3), REVERSE(CONVERT(BINARY(3), @IntValue))))
    -- Результат: 0001-01-04

Отличной особенностью DATE от SMALLDATETIME является невозможность (по крайне мере в ноябрьском CTP) добавлять или удалять дни из даты. Код:

    DECLARE @Date DATE = '2008-02-01'
    SELECT @Date + 1

Приведет к ошибке:

Msg 206, Level 16, State 2, Line 2
Operand type clash: date is incompatible with int

Также, в результате сложение двух значений DATE, будет такая ошибка:

Msg 8117, Level 16, State 1, Line 3
Operand data type date is invalid for add operator.

Дополнительную информацию о типе DATE можно подчерпнуть в BOL 2008

Подготовленно по материалам Danny's SQL Server and Internals Viewer Blog

TRUNCATE TABLE: Транзакционность и способность отката результата команды при восстановлении логов.

2008-01-05T11:22:00Z

Вопрос 1: Является ли команда TRUNCATE TABLE транзактивной?
Краткий ответ: Да.

Далее приведена репродукция кода, в котором выполняется откат транзакции, содержащей TRUNCATE TABLE:

    USE TEMPDB
    SET NOCOUNT ON
    GO

    CREATE TABLE DATA (COL1 INT, COL2 INT)
    GO

    INSERT DATA VALUES (1, 1)
    GO

    BEGIN TRAN

    TRUNCATE TABLE DATA
    ROLLBACK TRAN
    
    SELECT * FROM DATA
    GO
    DROP TABLE DATA

    Результат:
    COL1        COL2
    ----------- -----------
    1            1

В своем блоге Paul Randal превосходно описал, каким образом гарантируется транзактивность TRUNCATE TABLE.

Далее, я привожу перевод этого поста.

Сначала создадим тестовую таблицу для экспериментов.

    SET NOCOUNT ON
    CREATE TABLE TruncateTest (c1 INT IDENTITY, c2 CHAR (8000) DEFAULT 'A');
    GO
    
    DECLARE @a INT;
    SELECT @a = 1;
    WHILE (@a < 20)
    BEGIN
    
    INSERT INTO TruncateTest DEFAULT VALUES;
    SELECT @a = @a + 1;
    
    END;
    GO

Воспользуемся недокументированной командой DBCC IND, чтобы увидеть какие страницы и экстенты выделены для нашей экспериментальной таблицы:

DBCC IND (test, TruncateTest, 0); GO

    Результат:
    PageFID PagePID
    ------- ---------
    1       193
    1       192
    1       194
    1       195
    1       196
    1       197
    1       198
    1       199
    1       200
    1       224
    1       225
    1       226
    1       227
    1       228
    1       229
    1       230
    1       231
    1       232
    1       233
    1       234
    DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Я преднамеренно сократил вывод команды, оставив лишь ID страниц. Мы видим, что эта таблица задействовала 4 экстента (которые начинаются с страниц (1:192), (1:200), (1:224), (1:232)). Теперь, если мы выполним команду TRUNCATE TABLE в транзакции, что покажет DBCC IND?

    BEGIN TRAN;
    GO
    
    TRUNCATE TABLE TruncateTest;
    GO
    
    DBCC IND (test, TruncateTest, 0);
    GO

Результат:
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Похоже что страницы под таблицу уже не выделены, но где же они? Проверим текущие блокировки. Вместо использования sp_lock я использую ее DMV-замену, sys.dm_tran_locks:

SELECT resource_type, resource_description, request_mode FROM sys.dm_tran_locks WHERE resource_type IN ('EXTENT', 'PAGE'); GO

    Результат:
    resource_type   resource_description   request_mode
    --------------- ---------------------- --------------
    EXTENT          1:200                  X
    PAGE            1:198                  X
    PAGE            1:199                  X
    PAGE            1:196                  X
    PAGE            1:197                  X
    PAGE            1:194                  X
    PAGE            1:195                  X
    PAGE            1:192                  X
    PAGE            1:193                  X
    EXTENT          1:192                  X
    PAGE            1:200                  X
    EXTENT          1:232                  X
    EXTENT          1:224                  X

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

Если же произойдет откат транзакции, страницы попросту будут снова отмечены как принадлежащие таблице TruncateTest.

Вопрос 2: Возможно ли откатить успешное выполнение TRUNCATE TABLE посредством восстановления транзакционных логов на момент предшествующий запуску команды?
Краткий ответ: Да.

Данный вопрос обсуждал в своем блоге Pinal Dave - TRUNCATE Can’t be Rolled Back Using Log Files After Transaction Session Is Closed. Но его мнение не было разделено посетителями его блога, включая и мною.

При выполнении TRUNCATE TABLE в лог не попадают ни удаленные страницы, ни экстенты. Как же тогда возможно восстановить данные таблицы при восстановлении журналов транзакций?

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

Таким образом, при восстановлении полного бэкапа, затем журналов транзакций до момента запуска команды TRUNCATE данные таблицы будут восстановлены в полном объеме.

Репродуктивный скрипт:

    SET NOCOUNT ON
    CREATE DATABASE TEST
    GO
    ALTER DATABASE TEST SET RECOVERY FULL
    USE TEST
    GO
   
    -- STEP 2. Creating table with some data (100 rows)
    CREATE TABLE data (f1 INT IDENTITY)
    GO
    INSERT data DEFAULT VALUES
    GO 100
   
    -- STEP 3. Performing full backup
    BACKUP DATABASE TEST TO DISK='c:\test_full.bak' WITH INIT
    GO
   
    -- STEP 4. Performing log backup
    DECLARE @date DATETIME
   
    --variant1: Date mark just before truncation
    SET @date = DATEADD(s, 0, GETDATE())
   
    -- variant2: Date mark after truncation
    -- SET @date = DATEADD(s, 1, GETDATE())
   
    TRUNCATE TABLE data
    WAITFOR DELAY '00:00:03'
    BACKUP LOG TEST TO DISK='c:\test_log.bak' WITH INIT
   
    -- STEP 5. Restoring Test DB.
    USE MASTER
    RESTORE DATABASE TEST FROM DISK='c:\test_full.bak' WITH NORECOVERY, REPLACE
    RESTORE LOG TEST FROM DISK='c:\test_log.bak' WITH RECOVERY, STOPAT=@date
    GO
    USE TEST
    GO
    select COUNT(*) AS RowCnt FROM data
    go
    USE MASTER
    DROP DATABASE TEST

    Результат:
    RowCnt
    -----
    100

Скринкаст: SQL Server 2008 SSMS. Управление группой серверов.

2008-01-04T16:21:00Z

Katmai Management Studio имеет новую отличную способность выполнять T-SQL одновременно на нескольких инстансах простым нажатием на F5.
Смотрите подробности в скринкасте (4:00).

Доступна бесплатно электронная книга: "Introduction to LINQ"

2008-01-04T07:28:00Z

Microsoft раздает бесплатно эту книгу, и все что необходимо для доступа - лишь регистрация: Microsoft site

SQL Server 2008 virtual training доступны в течение января

2008-01-03T07:31:00Z

Прежде чем выпустить RTM версию следующего релиза SQL Server (который 2008), Microsoft подготовила несколько подготовительных материалов, которые будут полезны ИТ специалистам, желающим ознакомиться с новыми возможностями этого релиза.

Серия семинаров состоящая из 10 частей покрывает такие темы как безопасность, бизнес-аналитика, высокая доступность, создание отчетов. Время проведения: с 8 января – по 30 января 9:00. Ознакомиться более детально и зарегистрироваться на семинары можно здесь: Microsoft SQL Server 2008 training web seminars.

Некоторые из семинаров включают в себя такие "горячие" темы бизнес-аналитики:
What’s new in SQL Server 2008 for Database Administrators January 8
Implementing Change Data Capture (CDC) in SQL Server 2008 January 9
Working with the new DATE data type in SQL Server 2008 January 21
Administering Servers by Using Declarative Management Framework (DMF) Policies January 22
Learn How to Build Occasionally Connected Applications January 23
Authoring Reports Using Microsoft SQL Server 2008 Reporting Services January 24
Business Intelligence Overview January 25
Reporting Services January 28
Data Warehousing January 29
Building a cube from scratch January 30

Скринкасты SQL Server 2008: Table valued parameters и Change Data Capture

2008-01-02T08:04:00Z

SQL Server 2008 - Table valued parameters

If you have to insert more than one row of data in a Table, you end up executing the insert statement / stored procedure as many times for each row, with Table-valued parameters you can send multiple rows of data to a single TSQL statement or stored procedure without creating temp tables or doing multiple round trips.
Table-valued parameters are a new parameter type in SQL Server 2008 and are declared by using user-defined table types.

Change Data Capture SQL Server 2008

CDC or Change data capture is a new feature in SQL Server 2008, which is an ability to record changes to table data into another table without writing triggers or some other mechanism, Change data capture records the changes like insert, update, and delete to a table in SQL server thus making the details of the changes available in relational format.

Технические статьи: RAID Scaling Charts

2007-12-21T08:20:00Z

RAID Scaling Charts, Part 1: RAID Testing With 2-8 Drives
How do RAID arrays scale as you increase the number of hard drives they contain? Part 1 of our RAID Charts project shows all the benchmark results for RAID 0, RAID 1 and RAID 0+1 setups across two to eight disk drives.

RAID Scaling Charts, Part 2: RAID 5 And RAID 6
We saw what RAID 0, RAID 1 and RAID 0+1 offer in Part 1 of our RAID Charts. Now, we put RAID 5 and RAID 6 to the test.

RAID Scaling Charts, Part 3: 4-128 kB Stripes Compared
In the first two parts of this series, we looked at setups running RAID 0, 1, 0+1, 5 and 6, with three to eight hard drives. In this final article, we analyze performance comparisons at different stripe sizes on RAID levels 0, 5 and 6.

Опубликован новый Best Practices Whitepaper: The Impact of Changing Collations and of Changing Data Types from Non-Unicode to Unicode

2007-12-09T17:34:00Z

С выходом бизнеса на международный уровень, зачастую необходима поддержка многоязычных строк в таблицах. Процесс изменения баз данных с целью внедрения Unicode Collation, а также влияние изменений на производительность это некоторые из моментов, которые затронуты в опубликованном документе:
The Impact of Changing Collations and of Changing Data Types from Non-Unicode to Unicode