Увеличение производительности MS SQL 2005, перенос tempdb на другой диск

Установка MS SQL обычно происходит на системный диск и это правильно, но чаще всего базы хранятся на другом физическом диске, обычно более быстром и точно менее нагруженном (и это тоже правильно, а еще более правильно иметь разные физические диски под базы и под логи баз).

И все казалось бы замечательно, но иногда случаются тормоза в работе, причем эти тормоза сказываются на всей системе целиком, а не только на SQL и дело тут в системной базе tempdb. Являясь аналогом папки temp, эта база может создавать серьезную нагрузку на системный диск, не даваясь шевелится даже Windows, не говоря уже о производительности SQL. При всем при этом, нагрузка на tempdb не зависит от количества оперативной памяти на сервере (ЭТО ВАЖНО!), и ее объем может разрастаться до очень больших размеров, вплоть до исчерпания свободного места на диске. В ней хранятся, точнее не хранятся, а обрабатываются следующие данные:

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

Внутренние объекты, создаваемые SQL Server Database Engine, например рабочие таблицы, хранящие промежуточные результаты буферов или сортировки;

Версии строк, сформированные транзакциями изменения данных в базе данных, в которой используются транзакции изоляции моментальных снимков с зафиксированным чтением и транзакции изоляции моментальных снимков;

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

В SQL Server производительность базы данных tempdb может быть увеличена следующими способами.

1. Временные таблицы и табличные переменные могут кэшироваться. Кэширование позволяет операциям по удалению и созданию временных объектов выполняться очень быстро и снижает число конфликтов из-за выделения страниц.

2. Усовершенствован протокол кратковременных блокировок выделения страниц. При этом снижается количество используемых кратковременных блокировок UP (обновление).

3. Снижены затраты на ведение журнала базы данных tempdb. При этом снижается потребление пропускной способности подсистемы ввода-вывода файлом журнала базы данных tempdb. (По умолчанию модель восстановления это базы «простая», но я встречал системы где модель стаяла «полная», это стоит обязательно проверить.)

4. Усовершенствован алгоритм выделения смешанных страниц в базе данных tempdb.

5. Перенос базы tempdb на более быстрый не системный диск, либо перенос ее на созданный RAM диск (о чем я может быть напишу позже).

На пункты 1, 2 и 4 не разработчик приложения повлиять не может, а вот пунктами 3 и 5 можно заняться. С пунктом 3 все понятно, это очень просто, достаточно заглянуть в свойства базы. Но вот пункт 5 не такая простая задача, как может показаться на первый взгляд.

Первое что приходит в голову, это отсоединить базу, перенести ее на другой диск и присоединить. Попробует сделать Detach/Attach (отсоединить/присоединить). Но такие пункты для tempdb выключены.

Что делать?

Запускаем SQL Server Management Studio Express, или что там у вас, и выполняем следующие команды:

use tempdb
alter database tempdb
modify file( name = tempdev, filename = N'D:\SQL\tempdb.mdf')
go
alter database tempdb
modify file(name = templog, filename = N'D:\LOG\templog.ldf')
go

Где D:\SQL и D:\LOG пути до нового места размещения файла данных и логов.

Перезапускаем MS SQL Server.

База tempdb перемещена, в чем можно убедится заглянув в ее свойства.

Файлы от старой tempdb удаляем вручную, так как эта база для временных данных, важного в ней ничего нет, находятся они тут «%programfiles%\Microsoft SQL Server\MSSQL.1\MSSQL\Data».

Смотрите так же:

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *

Этот сайт использует Akismet для борьбы со спамом. Узнайте как обрабатываются ваши данные комментариев.