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

Откуда:
Сообщений: 36
Добрый день!
Ответ совсем не гуглится, оттого решил задать вопрос.
у меня имеется несколько скриптов для выяснения отсутствующих индексов

+ Скрипт 1

SET NOCOUNT ON

DECLARE @dbid int

IF (object_id('tempdb..##IndexAdvantage') IS NOT NULL) DROP TABLE ##IndexAdvantage

CREATE TABLE ##IndexAdvantage ([Преимущество индекса] float, [База данных] varchar(64), [Transact SQL код для создания индекса] varchar(512),

[Число компиляций] int, [Количество операций поиска] int, [Количество операций просмотра] int,

[Средняя стоимость ] int, [Средний процент выигрыша] int );

DECLARE DBases CURSOR FOR

SELECT database_id FROM sys.master_files -- Получаем список ID баз данных

WHERE state = 0 AND -- ONLINE

has_dbaccess(db_name(database_id)) = 1 -- Only look at databases to which we have access

GROUP BY database_id

OPEN DBases

FETCH NEXT FROM DBases

INTO @dbid

WHILE @@FETCH_STATUS = 0

BEGIN -- Выполняем для каждой базы данных --------------------------------------------------

INSERT INTO ##IndexAdvantage

SELECT [Преимущество индекса] = user_seeks * avg_total_user_cost * (avg_user_impact * 0.01),

[База данных] = DB_NAME(mid.database_id),

[Transact SQL код для создания индекса] = 'CREATE INDEX [IX_' + OBJECT_NAME(mid.object_id,@dbid) + '_' +

CAST(mid.index_handle AS nvarchar) + '] ON ' +

mid.statement + ' (' + ISNULL(mid.equality_columns,'') +

(CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ', '

ELSE '' END) +

(CASE WHEN mid.inequality_columns IS NOT NULL THEN + mid.inequality_columns ELSE '' END) + ')' +

(CASE WHEN mid.included_columns IS NOT NULL THEN ' INCLUDE (' + mid.included_columns + ')'

ELSE '' END) + ';',

[Число компиляций] = migs.unique_compiles,

[Количество операций поиска] = migs.user_seeks,

[Количество операций просмотра] = migs.user_scans,

[Средняя стоимость ] = CAST(migs.avg_total_user_cost AS int),

[Средний процент выигрыша] = CAST(migs.avg_user_impact AS int)

FROM sys.dm_db_missing_index_groups mig

JOIN sys.dm_db_missing_index_group_stats migs

ON migs.group_handle = mig.index_group_handle

JOIN sys.dm_db_missing_index_details mid

ON mig.index_handle = mid.index_handle

AND mid.database_id = @dbid

FETCH NEXT FROM DBases

INTO @dbid

END ----------------------------------------------------------------------------------------

CLOSE DBases

DEALLOCATE DBases

GO

SELECT * FROM ##IndexAdvantage ORDER BY 1 DESC

-- Значение ''Преимущество индекса'' выше 5000 в промышленных системах означает, что следует рассмотреть возможность создания этих индексов.

-- Если же значение превышает 10000, это обычно означает, что индекс может обеспечить значительное повышение производительности для операций чтения.

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

-- Отправляем email с предложением создать индекс

IF (object_id('tempdb..##IndexAdvantage2') IS NOT NULL) DROP TABLE ##IndexAdvantage2

SELECT * INTO ##IndexAdvantage2 FROM ##IndexAdvantage WHERE [Преимущество индекса] >= 5000 ORDER BY 1 DESC

IF ((SELECT COUNT(*) FROM ##IndexAdvantage2) >= 1) BEGIN

DECLARE @subject_str varchar(255),

@message_str varchar(1024),

@separator_str varchar(1),

@email varchar(128)

SET @separator_str=CHAR(9) -- Символ табуляции

SET @email = 'm.shelkovoy@chtp.net'

-- Подготовим текст сообщения

SET @subject_str = 'SQL Server '+@@SERVERNAME+': Предложение создать индексы в базе данных.'

SET @message_str = 'Сервер '+@@SERVERNAME + '. Выявлена необходимость создать индексы в базе данных!

Во вложении - таблица с кодом предлагаемых индексов.

Значение "Преимущество индекса" выше 5000 в промышленных системах означает, что следует рассмотреть возможность создания этих индексов.

Если же значение превышает 10000, это обычно означает, что индекс может обеспечить значительное повышение производительности для операций чтения.

Динамические административные представления, которые помогли нам получить информацию об отсутствующих индексах, не являются заменой помощника по настройке ядра СУБД, который также рассматривает индексированные представления и секции и обеспечивает более всесторонний анализ индексов, но они могут быть очень эффективны на начальном уровне анализа.'

-- Отправляем email

EXEC msdb.dbo.sp_send_dbmail

@recipients = @email,

@query = 'SELECT * FROM ##IndexAdvantage2',

@subject = @subject_str,

@body = @message_str,

@attach_query_result_as_file = 1,

@query_result_separator = @separator_str,

@query_result_width = 7000,
@profile_name='Основной'

END

-- Удаляем временную таблицу

IF (object_id('tempdb..##IndexAdvantage') IS NOT NULL) DROP TABLE ##IndexAdvantage

IF (object_id('tempdb..##IndexAdvantage2') IS NOT NULL) DROP TABLE ##IndexAdvantage2
--------------------------------------------------------------------------------------------




+ Скрипт 2

-- каких не хватает
declare @index_handle int,
@database_id smallint,
@object_id int,
@equality_columns nvarchar(4000),
@inequality_columns nvarchar(4000),
@included_columns nvarchar(4000)



declare cIdx cursor for
select index_handle, database_id, object_id, equality_columns, inequality_columns,
included_columns from sys.dm_db_missing_index_details where database_id=db_id()

open cIdx

fetch cIdx into @index_handle, @database_id, @object_id, @equality_columns,
@inequality_columns, @included_columns
while @@fetch_status=0
begin
print 'USE [' + db_name(@database_id)+ ']'
print 'go'
print 'CREATE NONCLUSTERED INDEX ' + OBJECT_NAME(@object_id) + '_IDX_' +
convert(nvarchar,@index_handle) + ' ON ' + OBJECT_NAME(@object_id)
print '(' + @equality_columns
IF @inequality_columns IS NOT NULL
BEGIN
PRINT ',' + @inequality_columns
END
PRINT ')'
IF @included_columns IS NOT NULL
BEGIN
PRINT 'INCLUDE (' + @included_columns + ')'
END
PRINT 'go'
PRINT ''
fetch cIdx into @index_handle, @database_id, @object_id, @equality_columns,
@inequality_columns, @included_columns
end
close cIdx
deallocate cIdx



Оба они говорят что надо создать такой индекс

первый предлагает так
CREATE INDEX [IX_OBJ_PERSON_1] ON [srv_intellect].[dbo].[OBJ_PERSON] ([parent_id]) INCLUDE ([id]);
второй вот так
USE [srv_intellect]
go
CREATE NONCLUSTERED INDEX OBJ_PERSON_IDX_1 ON OBJ_PERSON ,[parent_id] )INCLUDE ([id])
go

но при этом в моей базе на этой таблице уже есть этот индекс

/****** Object: Index [IX_OBJ_PERSON_4] Script Date: 21.02.2021 22:36:11 ******/
CREATE NONCLUSTERED INDEX [IX_OBJ_PERSON_4] ON [dbo].[OBJ_PERSON]
(
[parent_id] ASC
)
INCLUDE ( [id]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 85)
GO

Подскажите пожалуйста, почему так, зачем sql переделает создать уже существующий индекс, может что то не так и существующий он отчего то неиспользует?
21 фев 21, 22:41    [22284582]     Ответить | Цитировать Сообщить модератору
 Re: Sql Server предлагает создать уже существующий индекс  [new]
SIMPLicity_
Member

Откуда: (((@)))
Сообщений: 8818
Запустите вот это:

SELECT   OBJECT_NAME(S.[OBJECT_ID]) AS [OBJECT NAME], 
         I.[NAME] AS [INDEX NAME], 
         USER_SEEKS, 
         USER_SCANS, 
         USER_LOOKUPS, 
         USER_UPDATES 
FROM     SYS.DM_DB_INDEX_USAGE_STATS AS S 
         INNER JOIN SYS.INDEXES AS I 
           ON I.[OBJECT_ID] = S.[OBJECT_ID] 
              AND I.INDEX_ID = S.INDEX_ID 
WHERE    OBJECTPROPERTY(S.[OBJECT_ID],'IsUserTable') = 1 


И посмотрите,- возможно что Ваш именно этот индекс реально не используется...
22 фев 21, 00:39    [22284604]     Ответить | Цитировать Сообщить модератору
 Re: Sql Server предлагает создать уже существующий индекс  [new]
SIMPLicity_
Member

Откуда: (((@)))
Сообщений: 8818
И, не знаю, насколько это ещё актуально для текущих версий скуль-сервера, гляньте сюда (вспоминая Виталия)


PS Извините, если чонетак,- глубоко в Ваш вопрос не вникал...

Сообщение было отредактировано: 22 фев 21, 00:36
22 фев 21, 00:42    [22284605]     Ответить | Цитировать Сообщить модератору
 Re: Sql Server предлагает создать уже существующий индекс  [new]
shilll1
Member

Откуда:
Сообщений: 36
SIMPLicity_,

Если отфильтровать по таблице OBJ_PERSON, то получаю такой результат
[OBJECT NAME], [INDEX NAME], USER_SEEKS, USER_SCANS, USER_LOOKUPS, USER_UPDATES
OBJ_PERSON _____ NULL _________ 0 _________ 21255 _________ 274953 ________ 70508
OBJ_PERSON _ IX_OBJ_PERSON_id _ 254624 ______ 1 ____________ 0 ____________ 70508
OBJ_PERSON _ IX_OBJ_PERSON_4 _ 56752 _______ 0 ____________ 0 ____________ 70508
24 фев 21, 22:45    [22286061]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить