SQLServer Select Проблема производительности запроса

Question

SQLServer Select Проблема производительности запроса

У меня проблема с производительностью запроса select. Запрос может занять более 4 минут, что слишком долго. Этот запрос запускается в.Net Client Process, но не в Microsoft SQLServer Management Studio. Таким образом, он может составлять иногда 5 секунд, а иногда - 4 минуты, 1 минуту, 30 секунд. Итак, у меня есть 3 вопроса:

Почему этот запрос на выбор слишком длинный? Потому что это может сделать менее 5 секунд при свободном кеше sqlserver. Я проверяю этот запрос с инструкциями DBCC FREEPROCCACHE и DBCC DROPCLEANBUFFERS
Почему SQLTransaction системы sqlserver с именем sort_init для перестроения индексов занимает около 30 секунд? Как я могу приступить к сокращению этого времени?
Почему в плане выполнения у меня есть Clustered Index Scan, а не Clustered Index Seek (сканирование всей страницы может занять много времени) . Каждый сканированный кластерный индекс является первичным ключом каждой таблицы. NB: у меня есть еще много запросов с использованием этих таблиц, в которых есть Clustered Index Seek при соединении. Что я делаю не так? Как я мог заставить запрос работать быстрее? Как я могу оптимизировать индекс?

Я использую SQL Profiler для отслеживания проблемы, поэтому у меня есть следующие основные события:

А) SQL Profiler:

SP: StmtCompleted:

TextData:

`SELECT [DespatchNote].[Id], [DespatchNote].[RealType], [DespatchNote].[DbOwner], [DespatchNote].[Archived], [CreatedOn], [CreatedById], [UpdatedOn], [UpdatedById], [KeyingFinished], [IsValidated], [ValidatedOn], [ValidatedById], [Notes], [DefaultDisplayLanguageId], [ReferencedTransactionId], [TransactionNumber], [IsCanceled], [CanceledOn], [CanceledById], [TransactionType], [IsPending], [IsGenerated], [PrintCount], [LastPrintDate], [AttachedFile], [IsPointOfSalesTransaction], [IsAffiliatedTransaction], [IsDone], [DoneById], [DoneOn], [IsSent], [SentOn] FROM [DespatchNote] INNER JOIN [Flow] ON [DespatchNote].[Id] = [Flow].[Id] INNER JOIN [Transaction] ON [DespatchNote].[Id] = [Transaction].[Id] INNER JOIN [ProductsMovements] ON [DespatchNote].[Id] = [ProductsMovements].[Id] WHERE (([DespatchNote].[RealType] = @param42485) AND (([ProductsMovements].[IsDone] = @param42486) AND ([DespatchNote].[Archived] IS NULL)))`

Продолжительность (мс)
: 201277
SQLTransaction:

ObjectName: sort_init
Продолжительность (мс): 29982
EventSubClass: 1-Commit

B) Запрос с включенным SHOWPLAN_ALL

SELECT [DespatchNote].[Id], [DespatchNote].[RealType], [DespatchNote].[DbOwner], [DespatchNote].[Archived],   [CreatedOn], [CreatedById], [UpdatedOn], [UpdatedById], [KeyingFinished], [IsValidated], [ValidatedOn],    [ValidatedById], [Notes], [DefaultDisplayLanguageId], [ReferencedTransactionId], [TransactionNumber],    [IsCanceled], [CanceledOn], [CanceledById], [TransactionType], [IsPending], [IsGenerated],    [PrintCount], [LastPrintDate], [AttachedFile], [IsPointOfSalesTransaction], [IsAffiliatedTransaction],    [IsDone], [DoneById], [DoneOn], [IsSent], [SentOn]    FROM [DespatchNote]     INNER JOIN [Flow] ON [DespatchNote].[Id] = [Flow].[Id]     INNER JOIN [Transaction] ON [DespatchNote].[Id] = [Transaction].[Id]     INNER JOIN [ProductsMovements] ON [DespatchNote].[Id] = [ProductsMovements].[Id]     WHERE (([DespatchNote].[RealType] = (select top 1 DespatchNote.RealType from DespatchNote))     AND (([ProductsMovements].[IsDone] = 1)      AND ([DespatchNote].[Archived] IS NULL)))
  |--Merge Join(Inner Join, MERGE:([x3distributor].[dbo].[Flow].[Id])=([x3distributor].[dbo].[ProductsMovements].[Id]), RESIDUAL:([x3distributor].[dbo].[Flow].[Id]=[x3distributor].[dbo].[ProductsMovements].[Id]))
       |--Clustered Index Scan(OBJECT:([x3distributor].[dbo].[Flow].[PK_Flow]), ORDERED FORWARD)
       |--Merge Join(Inner Join, MERGE:([x3distributor].[dbo].[ProductsMovements].[Id])=([x3distributor].[dbo].[Transaction].[Id]), RESIDUAL:([x3distributor].[dbo].[Transaction].[Id]=[x3distributor].[dbo].[ProductsMovements].[Id]))
            |--Nested Loops(Inner Join, WHERE:([x3distributor].[dbo].[DespatchNote].[RealType]=[x3distributor].[dbo].[DespatchNote].[RealType]))
            |    |--Top(TOP EXPRESSION:((1)))
            |    |    |--Index Scan(OBJECT:([x3distributor].[dbo].[DespatchNote].[IX3_DespatchNote_RealType]))
            |    |--Merge Join(Inner Join, MERGE:([x3distributor].[dbo].[DespatchNote].[Id])=([x3distributor].[dbo].[ProductsMovements].[Id]), RESIDUAL:([x3distributor].[dbo].[DespatchNote].[Id]=[x3distributor].[dbo].[ProductsMovements].[Id]))
            |         |--Clustered Index Scan(OBJECT:([x3distributor].[dbo].[DespatchNote].[PK_DespatchNote]),  WHERE:([x3distributor].[dbo].[DespatchNote].[Archived] IS NULL) ORDERED FORWARD)
            |         |--Clustered Index Scan(OBJECT:([x3distributor].[dbo].[ProductsMovements].[PK_ProductsMovements]),  WHERE:([x3distributor].[dbo].[ProductsMovements].[IsDone]=(1)) ORDERED FORWARD)
            |--Clustered Index Scan(OBJECT:([x3distributor].[dbo].[Transaction].[PK_Transaction]), ORDERED FORWARD)

C) Для каждой таблицы я запускаю DBCC SHOWCONTIG([MyTable]), поэтому:

DBCC SHOWCONTIG анализировать таблицу "Транзакция"...

Таблица "Транзакция" (770101784); Индекс ID: 1, базовый номер донна ID: 5 Analyze du niveau TABLE effectuée. - Pages analysées................................: 3690 - Расширения анализируемые..............................: 466 - Commutateurs d'Extension..............................: 526 - Moyenne des pages par extension........................: 7.9 - Densité d'analyse [meilleure valeur:valeur réelle].......: 87,67% [462:527] - Логика фрагментарного анализа.................: 1,95% - Анализ фрагментации Расширение...................: 5,79% - Moyenne d'octets libres par page.....................: 631,1 - Densité de page moyenne (полная версия).....................: 92,20%

DBCC SHOWCONTIG проанализировать таблицу 'DespatchNote'...

Таблица: "DespatchNote" (1138103095); Индекс ID: 1, базовый номер донна ID: 5 Analyze du niveau TABLE effectuée. - Pages analysées................................: 409 - Расширения анализируемые..............................: 52 - Commutateurs d'Extension..............................: 51 - Moyenne des pages par extension........................: 7.9 - Densité d'analyse [meilleure valeur:valeur réelle].......: 100,00% [52:52] - Логика фрагментарного анализа..................: 0,00% - Анализ фрагментации Расширение...................: 5,77% - Moyenne d'octets libres par page.....................: 806.2 - Densité de page moyenne (полная версия).....................: 90,04%

DBCC SHOWCONTIG проанализировать таблицу "Продукты движения"...

Таблица: "Продукты" (1074102867); Индекс ID: 1, базовый номер донна ID: 5 Analyze du niveau TABLE effectuée. - Pages analysées................................: 1112 - Расширения анализируемые..............................: 139 - Commutateurs d'Extension..............................: 138 - Расширение Moyenne des pages par........................: 8.0 - Densité d'analyse [meilleure valeur:valeur réelle].......: 100,00% [139:139] - Логика фрагментарного анализа.................: 0,00% - Анализ фрагментации Расширение...................: 5,76% - Moyenne d'octets libres par page.....................: 725,6 - Densité de Page Moyenne (полная версия).....................: 91,03%

DBCC SHOWCONTIG анализировать таблицу "Поток"...

Таблица: "Поток" (1890105774); Индекс ID: 1, базовый номер донна ID: 5 Analyze du niveau TABLE effectuée. - Pages analysées................................: 2662 - Расширения Analysées..............................: 337 - Commutateurs d'Extension..............................: 343 - Moyenne des pages par extension........................: 7.9 - Densité d'analyse [meilleure valeur:valeur réelle].......: 96,80% [333:344] - Логика фрагментарного анализа.................: 0,45% - Анализ фрагментации Расширение...................: 5,93% - Moyenne d'octets libres par page.....................: 579.2 - Densité de page moyenne (полная версия).....................: 92,84%

D) Детали для каждой таблицы:

Для всей таблицы в моей базе данных каждый индекс (кластеризованный и некластеризованный) имеет общую фрагментацию менее 10 %.

ОТПРАВЛЕНИЕ: (36360 ЛИНИЙ НА ЭТОМ СТОЛЕ) (7 КОЛОНН)

 Индексы: PK_DespatchNote(кластер) IX3_DespatchNote_RealType(не уникальный, не кластерный) FK_DespatchNote_Archived(не уникальный, не кластерный)  Скрипт для создания таблицы:

CREATE TABLE [dbo].[DespatchNote](
    [Id] [uniqueidentifier] NOT NULL,
    [Archived] [datetime] NULL,
    [RealType] [uniqueidentifier] NOT NULL,
    [DbOwner] [uniqueidentifier] NOT NULL,
    [RecordVersion] [timestamp] NOT NULL,
    [IsSent] [bit] NULL,
    [SentOn] [datetime] NULL,
 CONSTRAINT [PK_DespatchNote] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

СДЕЛКА: (136120 ЛИНИЙ НА ЭТОМ СТОЛЕ) (20 КОЛОНН)

 Индексы: PK_Transaction(Cluster) IX3_Transaction_RealType(не уникальное, не кластерное) FK_Transaction_ReferencedTransactionId(не уникальное, не кластерное) FK_Transaction_DefaultDisplayLanguageId(не уникальное, не кластерное не уникальное) создать таблицу:

CREATE TABLE [dbo].[Transaction](
    [Id] [uniqueidentifier] NOT NULL,
    [Archived] [datetime] NULL,
    [RealType] [uniqueidentifier] NOT NULL,
    [DbOwner] [uniqueidentifier] NOT NULL,
    [RecordVersion] [timestamp] NOT NULL,
    [Notes] [ntext] NULL,
    [DefaultDisplayLanguageId] [uniqueidentifier] NULL,
    [ReferencedTransactionId] [uniqueidentifier] NULL,
    [TransactionNumber] [nvarchar](40) NULL,
    [IsCanceled] [bit] NULL,
    [CanceledOn] [datetime] NULL,
    [CanceledById] [uniqueidentifier] NULL,
    [TransactionType] [int] NOT NULL,
    [IsPending] [bit] NOT NULL,
    [IsGenerated] [bit] NOT NULL,
    [PrintCount] [int] NOT NULL,
    [LastPrintDate] [datetime] NULL,
    [AttachedFile] [image] NULL,
    [IsPointOfSalesTransaction] [bit] NOT NULL,
    [IsAffiliatedTransaction] [bit] NOT NULL,
 CONSTRAINT [PK_Transaction] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

ALTER TABLE [dbo].[Transaction] ADD  CONSTRAINT [DF_Transaction_TransactionType]  DEFAULT (0) FOR [TransactionType]
GO

ALTER TABLE [dbo].[Transaction] ADD  CONSTRAINT [DF_Transaction_IsPending]  DEFAULT (0) FOR [IsPending]
GO

ALTER TABLE [dbo].[Transaction] ADD  DEFAULT ((0)) FOR [IsGenerated]
GO

ALTER TABLE [dbo].[Transaction] ADD  CONSTRAINT [DF_Transaction_PrintCount]  DEFAULT ((0)) FOR [PrintCount]
GO

ALTER TABLE [dbo].[Transaction] ADD  CONSTRAINT [DF_Transaction_IsPointOfSalesTransaction]  DEFAULT ((0)) FOR [IsPointOfSalesTransaction]
GO

ALTER TABLE [dbo].[Transaction] ADD  CONSTRAINT [DF_Transaction_IsAffiliatedTransaction]  DEFAULT ((0)) FOR [IsAffiliatedTransaction]

ПОТОК: (136120 ЛИНИЙ НА ЭТОМ СТОЛЕ) (13 КОЛОНН)

 Индексы: PK_Flow(кластер)
IX3_Flow_RealType (не уникальный, не кластерный) . FK_Flow_ValidatedById(не уникальный, не кластерный) .

CREATE TABLE [dbo].[Flow](
    [Id] [uniqueidentifier] NOT NULL,
    [Archived] [datetime] NULL,
    [RealType] [uniqueidentifier] NOT NULL,
    [DbOwner] [uniqueidentifier] NOT NULL,
    [RecordVersion] [timestamp] NOT NULL,
    [CreatedOn] [datetime] NULL,
    [CreatedById] [uniqueidentifier] NULL,
    [UpdatedOn] [datetime] NULL,
    [UpdatedById] [uniqueidentifier] NULL,
    [KeyingFinished] [bit] NULL,
    [IsValidated] [bit] NULL,
    [ValidatedOn] [datetime] NULL,
    [ValidatedById] [uniqueidentifier] NULL,
 CONSTRAINT [PK_Flow] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

ИЗДЕЛИЯ ИЗДЕЛИЯ: (83631 ЛИНИЙ НА ЭТОМ СТОЛЕ) (8 КОЛОНН)

 Индексы: PK_ProductsMovements(Cluster) IX3_ProductsMovements_RealType(не уникальный, не кластерный) FK_ProductsMovements_DoneById(не уникальный, не кластерный) FK_ProductsMovements_Archived(не уникальный, не кластерный) ript Сценарий для создания таблицы:

CREATE TABLE [dbo].[ProductsMovements](
    [Id] [uniqueidentifier] NOT NULL,
    [Archived] [datetime] NULL,
    [RealType] [uniqueidentifier] NOT NULL,
    [DbOwner] [uniqueidentifier] NOT NULL,
    [RecordVersion] [timestamp] NOT NULL,
    [IsDone] [bit] NULL,
    [DoneById] [uniqueidentifier] NULL,
    [DoneOn] [datetime] NULL,
 CONSTRAINT [PK_ProductsMovements] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

Если вы хотите получить больше информации, пожалуйста, сообщите мне с уважением Спасибо.

0

sql performance select sql-server-2008-r2 select-query

Источник

user4369751 17 мар '15 в 14:59

1 ответ

Другие вопросы по тегам sql performance select sql-server-2008-r2 select-query

user1144035 17 мар '15 в 15:32 2015-03-17 15:32 · Answer 1 · 2015-03-17 15:32

Ваш запрос в основном:

SELECT . . .
FROM [DespatchNote] INNER JOIN
     [Flow]
     ON [DespatchNote].[Id] = [Flow].[Id] INNER JOIN
     [Transaction]
     ON [DespatchNote].[Id] = [Transaction].[Id] INNER JOIN
     [ProductsMovements]
     ON [DespatchNote].[Id] = [ProductsMovements].[Id]
WHERE [DespatchNote].[RealType] = @param42485 AND
      [ProductsMovements].[IsDone] = @param42486 AND
      [DespatchNote].[Archived] IS NULL

Во-первых, все ваши столы имеют id объявлен в качестве первичного ключа. Увы, самый низкий висящий фрукт съеден. Далее следует вопрос о дополнительных индексах. Я думаю, что индекс на DespatchNote(RealType, Archived, id) поможет запрос. Это должно уменьшить объем данных, поступающих в объединения, и может побудить ядро базы данных использовать индексы для объединений.