Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Помогите универсализировать запрос по реорганизации индексов  [new]
stavgreengo
Member

Откуда:
Сообщений: 710
Доброе время суток, коллеги !!!
автор
Microsoft SQL Server 2000 - 8.00.2301 (Intel X86)
Jan 6 2012 17:23:12
Copyright (c) 1988-2003 Microsoft Corporation
Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

Ночью на одной из баз данных не отрабатывает скрипт по реорганизации индексов
SET NOCOUNT ON
DECLARE @tablename VARCHAR (128)
DECLARE @execstr VARCHAR (255)
DECLARE @objectid INT
DECLARE @indexid INT
DECLARE @frag DECIMAL
DECLARE @maxfrag DECIMAL
SELECT @maxfrag = 5.0
DECLARE tables CURSOR FOR

SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'

CREATE TABLE #fraglist (
ObjectName CHAR (255),
ObjectId INT,
IndexName CHAR (255),
IndexId INT,
Lvl INT,
CountPages INT,
CountRows INT,
MinRecSize INT,
MaxRecSize INT,
AvgRecSize INT,
ForRecCount INT,
Extents INT,
ExtentSwitches INT,
AvgFreeBytes INT,
AvgPageDensity INT,
ScanDensity DECIMAL,
BestCount INT,
ActualCount INT,
LogicalFrag DECIMAL,
ExtentFrag DECIMAL)
OPEN tables
FETCH NEXT
FROM tables
INTO @tablename
WHILE @@FETCH_STATUS = 0
BEGIN

INSERT INTO #fraglist
EXEC ('DBCC SHOWCONTIG (''' + @tablename + ''')
WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')
FETCH NEXT
FROM tables
INTO @tablename
END

CLOSE tables
DEALLOCATE tables

DECLARE indexes CURSOR FOR
SELECT ObjectName, ObjectId, IndexId, LogicalFrag
FROM #fraglist
WHERE LogicalFrag >= @maxfrag
AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0

OPEN indexes

FETCH NEXT
FROM indexes
INTO @tablename, @objectid, @indexid, @frag
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'Executing DBCC INDEXDEFRAG (0, ' + RTRIM(@tablename) + ',
' + RTRIM(@indexid) + ') - fragmentation currently '
+ RTRIM(CONVERT(varchar(15),@frag)) + '%'
SELECT @execstr = 'DBCC INDEXDEFRAG (0, ' + RTRIM(@objectid) + ',
' + RTRIM(@indexid) + ')'
EXEC (@execstr)
FETCH NEXT
FROM indexes
INTO @tablename, @objectid, @indexid, @frag
END

CLOSE indexes
DEALLOCATE indexes

DROP TABLE #fraglist
GO

С ошибкой
автор
Executed as user: ENERGOMERA\erpuser. Could not find a table or object named 'TSD_log'. Check sysobjects. [SQLSTATE 42S02] (Error 2501) Could not find a table or object named 'DecisionsOnTheItems'. Check sysobjects. [SQLSTATE 42S02] (Error 2501). The step failed.

Всё дело в том что указанные в ошибке таблицы реально существуют но имеют схему отличную от dbo.

Как поправить запрос чтобы он охватывал все существующие на сервере схемы ?
23 мар 16, 09:49    [18967067]     Ответить | Цитировать Сообщить модератору
 Re: Помогите универсализировать запрос по реорганизации индексов  [new]
stavgreengo
Member

Откуда:
Сообщений: 710
Последующий степ джоба по обновлению статистики
автор
SET QUOTED_IDENTIFIER ON
GO
declare curs cursor local fast_forward for
select 'update statistics '+object_name(id)+' ('+name+')'
from sysindexes
where rowmodctr > 0
and id > 1000
and indid between 1 and 254
and stats_date(id, indid) < dateadd(mi, -10, getdate())
declare @cmd varchar(2000)
open curs
while 1=1
begin
fetch next from curs into @cmd
if @@fetch_status <> 0
break
raiserror('%s', 10, 1, @cmd) with nowait
exec(@cmd)
end

Так же данные таблицы не видит(
автор
Table 'TSD_log' does not exist. [SQLSTATE 42S02] (Error 2706). The step failed.

Его бы тоже подшаманить как-нибудь
23 мар 16, 09:53    [18967074]     Ответить | Цитировать Сообщить модератору
 Re: Помогите универсализировать запрос по реорганизации индексов  [new]
Glory
Member

Откуда:
Сообщений: 104751
stavgreengo
Всё дело в том что указанные в ошибке таблицы реально существуют но имеют схему отличную от dbo.

Как поправить запрос чтобы он охватывал все существующие на сервере схемы ?

Указывать полное имя таблицы ? Т.е. так, как показано в хелпе по DBCC INDEXDEFRAG

stavgreengo
Так же данные таблицы не видит(
автор
Table 'TSD_log' does not exist. [SQLSTATE 42S02] (Error 2706). The step failed.

Его бы тоже подшаманить как-нибудь

И по update statistics тоже статью в хелпе прочитать с примерами
23 мар 16, 09:57    [18967086]     Ответить | Цитировать Сообщить модератору
 Re: Помогите универсализировать запрос по реорганизации индексов  [new]
stavgreengo
Member

Откуда:
Сообщений: 710
Glory
stavgreengo
Всё дело в том что указанные в ошибке таблицы реально существуют но имеют схему отличную от dbo.

Как поправить запрос чтобы он охватывал все существующие на сервере схемы ?

Указывать полное имя таблицы ? Т.е. так, как показано в хелпе по DBCC INDEXDEFRAG


А причём здесь DBCC INDEXDEFRAG вообще если у меня кусок кода
SET NOCOUNT ON
DECLARE @tablename VARCHAR (128)
DECLARE @execstr VARCHAR (255)
DECLARE @objectid INT
DECLARE @indexid INT
DECLARE @frag DECIMAL
DECLARE @maxfrag DECIMAL
SELECT @maxfrag = 5.0
DECLARE tables CURSOR FOR

SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'

CREATE TABLE #fraglist (
ObjectName CHAR (255),
ObjectId INT,
IndexName CHAR (255),
IndexId INT,
Lvl INT,
CountPages INT,
CountRows INT,
MinRecSize INT,
MaxRecSize INT,
AvgRecSize INT,
ForRecCount INT,
Extents INT,
ExtentSwitches INT,
AvgFreeBytes INT,
AvgPageDensity INT,
ScanDensity DECIMAL,
BestCount INT,
ActualCount INT,
LogicalFrag DECIMAL,
ExtentFrag DECIMAL)
OPEN tables
FETCH NEXT
FROM tables
INTO @tablename
WHILE @@FETCH_STATUS = 0
BEGIN

INSERT INTO #fraglist
EXEC ('DBCC SHOWCONTIG (''' + @tablename + ''')
WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')
FETCH NEXT
FROM tables
INTO @tablename
END

CLOSE tables
DEALLOCATE tables

Выдаёт
автор
Сообщение 2501, уровень 16, состояние 45, строка 1
Could not find a table or object named 'TSD_log'. Check sysobjects.
Сообщение 2501, уровень 16, состояние 45, строка 1
Could not find a table or object named 'DecisionsOnTheItems'. Check sysobjects.
23 мар 16, 10:15    [18967167]     Ответить | Цитировать Сообщить модератору
 Re: Помогите универсализировать запрос по реорганизации индексов  [new]
Glory
Member

Откуда:
Сообщений: 104751
stavgreengo
А причём здесь DBCC INDEXDEFRAG вообще если у меня кусок кода

Млин. Начните читать в хелпе про _каждую_ команду.
Для каждой есть описание синтаксиса и примеры использования.
23 мар 16, 10:18    [18967178]     Ответить | Цитировать Сообщить модератору
 Re: Помогите универсализировать запрос по реорганизации индексов  [new]
stavgreengo
Member

Откуда:
Сообщений: 710
Glory
stavgreengo
А причём здесь DBCC INDEXDEFRAG вообще если у меня кусок кода

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

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

пробую
автор
DBCC SHOWCONTIG
WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS

Выдаётся весь набор таблиц, включая системные, поэтому в искомом запросе на реорганизацию индекса получаю ошибки
автор
Executing DBCC INDEXDEFRAG (0, sysobjects,
1) - fragmentation currently 33%
Сообщение 2567, уровень 14, состояние 1, строка 1
DBCC INDEXDEFRAG cannot be used on system table indexes

Куда двигаться дальше ? Думаю что надо бы как-нибудь вместо ObjectName использовать ObjectID, но как ?
23 мар 16, 10:35    [18967258]     Ответить | Цитировать Сообщить модератору
 Re: Помогите универсализировать запрос по реорганизации индексов  [new]
Glory
Member

Откуда:
Сообщений: 104751
stavgreengo
Куда двигаться дальше ?

В раздел Примеры
23 мар 16, 10:42    [18967276]     Ответить | Цитировать Сообщить модератору
 Re: Помогите универсализировать запрос по реорганизации индексов  [new]
Glory
Member

Откуда:
Сообщений: 104751
Там даже есть пример E. Using DBCC SHOWCONTIG and DBCC INDEXDEFRAG to defragment the indexes in a database
23 мар 16, 10:49    [18967288]     Ответить | Цитировать Сообщить модератору
 Re: Помогите универсализировать запрос по реорганизации индексов  [new]
stavgreengo
Member

Откуда:
Сообщений: 710
Glory
Там даже есть пример E. Using DBCC SHOWCONTIG and DBCC INDEXDEFRAG to defragment the indexes in a database

этот пример и есть мой искомый начальный запрос)
23 мар 16, 11:02    [18967326]     Ответить | Цитировать Сообщить модератору
 Re: Помогите универсализировать запрос по реорганизации индексов  [new]
Glory
Member

Откуда:
Сообщений: 104751
stavgreengo
этот пример и есть мой искомый начальный запрос)

И что вам мешало прочитать эту статью с примером ?
23 мар 16, 11:03    [18967333]     Ответить | Цитировать Сообщить модератору
 Re: Помогите универсализировать запрос по реорганизации индексов  [new]
stavgreengo
Member

Откуда:
Сообщений: 710
Glory
stavgreengo
этот пример и есть мой искомый начальный запрос)

И что вам мешало прочитать эту статью с примером ?

Хмм...интересно девки пляшут, не работающий запрос я ЕМНИП c MSDN примера и брал, сейчас он другой, включает учёт схем
+
/*Perform a 'USE <database name>' to select the database in which to run the script.*/
-- Declare variables
SET NOCOUNT ON;
DECLARE @tablename varchar(255);
DECLARE @execstr   varchar(400);
DECLARE @objectid  int;
DECLARE @indexid   int;
DECLARE @frag      decimal;
DECLARE @maxfrag   decimal;

-- Decide on the maximum fragmentation to allow for.
SELECT @maxfrag = 30.0;

-- Declare a cursor.
DECLARE tables CURSOR FOR
   SELECT TABLE_SCHEMA + '.' + TABLE_NAME
   FROM INFORMATION_SCHEMA.TABLES
   WHERE TABLE_TYPE = 'BASE TABLE';

-- Create the table.
CREATE TABLE #fraglist (
   ObjectName char(255),
   ObjectId int,
   IndexName char(255),
   IndexId int,
   Lvl int,
   CountPages int,
   CountRows int,
   MinRecSize int,
   MaxRecSize int,
   AvgRecSize int,
   ForRecCount int,
   Extents int,
   ExtentSwitches int,
   AvgFreeBytes int,
   AvgPageDensity int,
   ScanDensity decimal,
   BestCount int,
   ActualCount int,
   LogicalFrag decimal,
   ExtentFrag decimal);

-- Open the cursor.
OPEN tables;

-- Loop through all the tables in the database.
FETCH NEXT
   FROM tables
   INTO @tablename;

WHILE @@FETCH_STATUS = 0
BEGIN
-- Do the showcontig of all indexes of the table
   INSERT INTO #fraglist 
   EXEC ('DBCC SHOWCONTIG (''' + @tablename + ''') 
      WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS');
   FETCH NEXT
      FROM tables
      INTO @tablename;
END;

-- Close and deallocate the cursor.
CLOSE tables;
DEALLOCATE tables;

-- Declare the cursor for the list of indexes to be defragged.
DECLARE indexes CURSOR FOR
   SELECT ObjectName, ObjectId, IndexId, LogicalFrag
   FROM #fraglist
   WHERE LogicalFrag >= @maxfrag
      AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0;

-- Open the cursor.
OPEN indexes;

-- Loop through the indexes.
FETCH NEXT
   FROM indexes
   INTO @tablename, @objectid, @indexid, @frag;

WHILE @@FETCH_STATUS = 0
BEGIN
   PRINT 'Executing DBCC INDEXDEFRAG (0, ' + RTRIM(@tablename) + ',
      ' + RTRIM(@indexid) + ') - fragmentation currently '
       + RTRIM(CONVERT(varchar(15),@frag)) + '%';
   SELECT @execstr = 'DBCC INDEXDEFRAG (0, ' + RTRIM(@objectid) + ',
       ' + RTRIM(@indexid) + ')';
   EXEC (@execstr);

   FETCH NEXT
      FROM indexes
      INTO @tablename, @objectid, @indexid, @frag;
END

-- Close and deallocate the cursor.
CLOSE indexes;
DEALLOCATE indexes;

-- Delete the temporary table.
DROP TABLE #fraglist;
GO

Либо переделали, либо я всё же его не от туда брал.
В любом случае как всегда спасибо, Глори, из вас вышел бы отличный педагог, не разжёвывающий ученикам, но наставляющий на путь познания)
23 мар 16, 11:26    [18967461]     Ответить | Цитировать Сообщить модератору
 Re: Помогите универсализировать запрос по реорганизации индексов  [new]
Glory
Member

Откуда:
Сообщений: 104751
stavgreengo
Либо переделали, либо я всё же его не от туда брал.

Третье - вы его взяли вообще не из MSDN. Напримеп, сами придумали

stavgreengo
В любом случае как всегда спасибо, Глори, из вас вышел бы отличный педагог, не разжёвывающий ученикам, но наставляющий на путь познания)

Мне просто интересно, почему вы при ошибке команды стали писать сообщение в форум, а не читать хелп
23 мар 16, 11:30    [18967486]     Ответить | Цитировать Сообщить модератору
 Re: Помогите универсализировать запрос по реорганизации индексов  [new]
stavgreengo
Member

Откуда:
Сообщений: 710
Glory
stavgreengo
В любом случае как всегда спасибо, Глори, из вас вышел бы отличный педагог, не разжёвывающий ученикам, но наставляющий на путь познания)

Мне просто интересно, почему вы при ошибке команды стали писать сообщение в форум, а не читать хелп

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

З.Ы.: сбор статистики поправил не мудрствуя лукаво на
exec sp_updatestats
, база данных не большая и какая-либо оптимизация по выборки не нужна.
23 мар 16, 11:36    [18967519]     Ответить | Цитировать Сообщить модератору
 Re: Помогите универсализировать запрос по реорганизации индексов  [new]
Glory
Member

Откуда:
Сообщений: 104751
stavgreengo
Так для того проффорумы и существуют, чтобы более опытные коллеги помогли сэкономить время направляя в нужное русло,

Вот как оказывается. А проффорумы об знают, что они должны экономить вам время на чтении хелпа ?
23 мар 16, 11:38    [18967532]     Ответить | Цитировать Сообщить модератору
 Re: Помогите универсализировать запрос по реорганизации индексов  [new]
AlanDenton
Member [скрыт]

Откуда:
Сообщений: 1004
stavgreengo, погуглите а Хабре есть куча статей с уже готовыми скриптами:

https://habrahabr.ru/post/209698/
https://habrahabr.ru/post/209816/
https://habrahabr.ru/post/270401/
23 мар 16, 12:13    [18967724]     Ответить | Цитировать Сообщить модератору
 Re: Помогите универсализировать запрос по реорганизации индексов  [new]
o-o
Guest
AlanDenton
stavgreengo, погуглите а Хабре есть куча статей с уже готовыми скриптами:
https://habrahabr.ru/post/209698/

у него сервер 2000, там нет sys.dm_db_index_physical_stats
23 мар 16, 12:18    [18967744]     Ответить | Цитировать Сообщить модератору
 Re: Помогите универсализировать запрос по реорганизации индексов  [new]
AlanDenton
Member [скрыт]

Откуда:
Сообщений: 1004
o-o, упустил из виду. Скузи
23 мар 16, 12:28    [18967784]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить