Фиксирование таблиц баз данных MS-SQL в памяти   

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

Возможны различные методы решения данной задачи. Можно использовать для этого файловые группы, быстрые диски и т.д. Это зависит от того, какой доступ к таблице нужно сделать максимально быстрым – на запись или на чтение.

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

У метода закрепления таблиц в памяти есть как преимущества, так и недостатки. Главный недостаток – это возможность переполнения буфера. Так как память MS-SQL сервера используется для всех баз данных, находящихся на нем, то значит и закрепленные в памяти таблицы из разных баз, отнимают из общего доступного объема буфера. Независимо от количества таблиц, закрепляемых в памяти, общее количество памяти, выделенное под закрепленные страницы таблиц не должно превысить физически доступную для MS-SQL сервера память, во избежание нарушения работы MS-SQL сервера. Такое может случиться, если закрепленная в памяти таблица или общее количество таблиц займет всю память, доступную для MS-SQL сервера.

Если происходит переполнение, то придется делать останов и запуск сервера, и снимать фиксацию с больших таблиц, забравших всю память. Так что с данной функцией нужно обращаться с осторожностью. Например, использовать ее для закрепления небольших, но часто используемых таблиц, вроде «_1SJOURN» и др.

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

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

Текст обработки представлен ниже:

Процедура ПрочитатьДанные()
    ТЗ.Очистить();
    ТЗ.НоваяКолонка("ИмяТаблицы","Строка",,,"Имя таблицы");
    ТЗ.НоваяКолонка("Закреплена","Число",1,0,);
    ТЗ.НоваяКолонка("ТекущийСтатус","Строка",,,"Фиксирована");
    ТЗ.ВидимостьКолонки("Закреплена",0);
    ЗапросРадуги=СоздатьОбъект("ODBCQuery");
    ТекстЗапроса="SELECT RTRIM(CONVERT(char(30),TABLE_NAME)), OBJECTPROPERTY(OBJECT_ID(TABLE_NAME),'TableIsPinned')
    |FROM information_schema.tables
    |WHERE TABLE_TYPE='BASE TABLE' and TABLE_NAME<>'dtproperties'";
    Если ЗапросРадуги.Prepare(ТекстЗапроса,0,0)=1 Тогда
        Если ЗапросРадуги.Open()=1 Тогда
            ЗапросРадуги.GotoNext();
            Пока ЗапросРадуги.IsOK()=1 Цикл
                ТЗ.НоваяСтрока();
                ТЗ.ИмяТаблицы=СокрЛП(ЗапросРадуги.GetString(0));
                ТЗ.Закреплена=ЗапросРадуги.GetLong(1);
                ТЗ.ТекущийСтатус=?(ТЗ.Закреплена=1,"Да","Нет");
                ЗапросРадуги.GotoNext();
            КонецЦикла;
            ЗапросРадуги.Close();
        Иначе
            Предупреждение("Ошибка открытия запроса!",10);
        КонецЕсли;
        ЗапросРадуги.Reset();
    Иначе
        Предупреждение("Ошибка выполнения запроса!",10);
    КонецЕсли;
    ЗапросРадуги="";
    ТЗ.Сортировать("ТекущийСтатус, ИмяТаблицы");
КонецПроцедуры
//________________________________________________________
Процедура ЗаписатьДанные()
    ЗапросРадуги=СоздатьОбъект("ODBCQuery");
    ТЗ.ВыбратьСтроки();
    Пока ТЗ.ПолучитьСтроку()=1 Цикл
        ТекНовСтатус=?(ТЗ.ТекущийСтатус="Да",1,0);
        Если ТЗ.Закреплена<>ТекНовСтатус Тогда
            ТекВариант=?(ТекНовСтатус=0,"UN","");
            ТекстЗапроса="DECLARE @db_id int, @tbl_id int
            |SET @db_id = DB_ID()
            |SET @tbl_id = OBJECT_ID('"+ТЗ.ИмяТаблицы+"')
            |DBCC "+ТекВариант+"PINTABLE (@db_id, @tbl_id)";
            Если ЗапросРадуги.Prepare(ТекстЗапроса,0,0)=1 Тогда
                Если ЗапросРадуги.Open()=1 Тогда
                Иначе
                    Предупреждение("Ошибка открытия запроса!",10);
                КонецЕсли;
                ЗапросРадуги.Reset();
            Иначе
                Предупреждение("Ошибка выполнения запроса!",10);
            КонецЕсли;
        КонецЕсли;
    КонецЦикла;
    ЗапросРадуги="";
КонецПроцедуры
//________________________________________________________
Процедура ОбновитьСтатусы()
    ЗаписатьДанные();
    ПрочитатьДанные();
КонецПроцедуры
//________________________________________________________
Процедура ОбработкаТаблицы()
    Если (ТЗ.ТекущаяСтрока()>0) И (ТЗ.ТекущаяКолонка()="ТекущийСтатус") Тогда
        СЗ=СоздатьОбъект("СписокЗначений");
        СЗ.ДобавитьЗначение("Да");
        СЗ.ДобавитьЗначение("Нет");
        НомПоз=0;
        Если СЗ.ВыбратьЗначение(,,НомПоз,,1)=1 Тогда
            ТЗ.ТекущийСтатус=?(НомПоз=1,"Да","Нет");
        КонецЕсли;
    КонецЕсли;
КонецПроцедуры
//________________________________________________________
Процедура ПриОткрытии()
    Если ЗагрузитьВнешнююКомпоненту("rainbow.dll")=0 Тогда
        Предупреждение("Не загружена внешняя компонента ""rainbow.dll"" !",10);
        СтатусВозврата(0);
    Иначе
        ПрочитатьДанные();
    КонецЕсли;
КонецПроцедуры

Эта обработка доступна в разделе "Скачать".

 


 

Перепечатка, воспроизведение в любой форме, распространение, в том числе в переводе, любых материалов с сайта www.softpoint.ru возможны только с письменного разрешения компании "СофтПоинт". Это правило действует для всех без исключения случаев, кроме тех, когда в материале прямо указано разрешение на копирование (основание: Закон Российской Федерации "Об авторском праве и смежных правах").