SQLServer Select Query Performance issue

Question

I have a performance issue with a select query. The query can take more than 4 minutes which is too long. This query is launched on .Net Client Process but not on Microsoft SQLServer Management Studio. So It can make sometimes 5 seconds and can make sometimes 4 minutes, 1 minutes, 30seconds. So I have 3 questions:

Why this select query is too long? Because it can make less than 5 seconds with sqlserver cache free. I test this query with instructions DBCC FREEPROCCACHE and DBCC DROPCLEANBUFFERS
Why a sqlserver system SQLTransaction named sort_init to rebuild indexes makes about 30seconds? How can I proceed to reduce this time?
Why on the execution plan, I have Clustered Index Scan, and not Clustered Index Seek (scan all page maybe takes long time) . Each Clustered Index scanned is the Primary Key of each table. NB: I have another lot of queries using these tables which have Clustered Index Seek on join clause. What I'm doing wrong? How could I get the query to run faster? How can I optimize the index?

I use SQL Profiler to trace the issue, so I have these principal Events:

A) 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)))`

Duration (ms)

: 201277
SQLTransaction :

ObjectName : sort_init
Duration (ms): 29982
EventSubClass: 1-Commit

B)Query with SHOWPLAN_ALL On

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) For each table I launch DBCC SHOWCONTIG([MyTable]), so:

DBCC SHOWCONTIG analyse la table 'Transaction'...

Table : 'Transaction' (770101784) ; index ID : 1, base de données ID : 5 Analyse du niveau TABLE effectuée. - Pages analysées................................: 3690 - Extensions analysées..............................: 466 - Commutateurs d'extension..............................: 526 - Moyenne des pages par extension........................: 7.9 - Densité d'analyse [meilleure valeur:valeur réelle].......: 87.67% [462:527] - Fragmentation d'analyse logique..................: 1.95% - Fragmentation d'analyse d'extension...................: 5.79% - Moyenne d'octets libres par page.....................: 631.1 - Densité de page moyenne (complète).....................: 92.20%

DBCC SHOWCONTIG analyse la table 'DespatchNote'...

Table : 'DespatchNote' (1138103095) ; index ID : 1, base de données ID : 5 Analyse du niveau TABLE effectuée. - Pages analysées................................: 409 - Extensions analysées..............................: 52 - Commutateurs d'extension..............................: 51 - Moyenne des pages par extension........................: 7.9 - Densité d'analyse [meilleure valeur:valeur réelle].......: 100.00% [52:52] - Fragmentation d'analyse logique..................: 0.00% - Fragmentation d'analyse d'extension...................: 5.77% - Moyenne d'octets libres par page.....................: 806.2 - Densité de page moyenne (complète).....................: 90.04%

DBCC SHOWCONTIG analyse la table 'ProductsMovements'...

Table : 'ProductsMovements' (1074102867) ; index ID : 1, base de données ID : 5 Analyse du niveau TABLE effectuée. - Pages analysées................................: 1112 - Extensions analysées..............................: 139 - Commutateurs d'extension..............................: 138 - Moyenne des pages par extension........................: 8.0 - Densité d'analyse [meilleure valeur:valeur réelle].......: 100.00% [139:139] - Fragmentation d'analyse logique..................: 0.00% - Fragmentation d'analyse d'extension...................: 5.76% - Moyenne d'octets libres par page.....................: 725.6 - Densité de page moyenne (complète).....................: 91.03%

DBCC SHOWCONTIG analyse la table 'Flow'...

Table : 'Flow' (1890105774) ; index ID : 1, base de données ID : 5 Analyse du niveau TABLE effectuée. - Pages analysées................................: 2662 - Extensions 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] - Fragmentation d'analyse logique..................: 0.45% - Fragmentation d'analyse d'extension...................: 5.93% - Moyenne d'octets libres par page.....................: 579.2 - Densité de page moyenne (complète).....................: 92.84%

D)Details for each table:

For all Table in my DataBase, each Index (Clustered and non clustered) have total Fragmentation less than 10 %.

DESPATCHNOTE: (36360 LINES ON THIS TABLE) (7 COLUMNS)

 Indexes : PK_DespatchNote(Cluster) IX3_DespatchNote_RealType(non unique , non Cluster) FK_DespatchNote_Archived(non unique , non Cluster)  Script to create the Table :

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]

TRANSACTION: (136120 LINES ON THIS TABLE) (20 COLUMNS)

 Indexes : PK_Transaction(Cluster) IX3_Transaction_RealType(non unique , non Cluster) FK_Transaction_ReferencedTransactionId(non unique , non Cluster) FK_Transaction_DefaultDisplayLanguageId(non unique , non Cluster) FK_Transaction_CanceledById(non unique , non Cluster) FK_Transaction_Archived(non unique , non Cluster)  Script to create the Table :

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]

FLOW: (136120 LINES ON THIS TABLE) (13 COLUMNS)

 Indexes : PK_Flow(Cluster)
IX3_Flow_RealType (non unique , non Cluster) FK_Flow_ValidatedById(non unique , non Cluster) FK_Flow_UpdatedById(non unique , non Cluster) FK_Flow_CreatedById(non unique , non Cluster) FK_Flow_Archived(non unique , non Cluster)  Script to create the Table :

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]

PRODUCTSMOVEMENTS : (83631 LINES ON THIS TABLE) (8 COLUMNS)

 Indexes : PK_ProductsMovements(Cluster) IX3_ProductsMovements_RealType(non unique , non Cluster) FK_ProductsMovements_DoneById(non unique , non Cluster) FK_ProductsMovements_Archived(non unique , non Cluster)  Script to create the Table :

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]

If you want more information please advise me Best regards Thanks.

score 0 · Answer 1 · answered Mar 17 '15 at 15:32

0

Your query is basically:

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

First, all your tables have id declared as the primary key. Alas, the lowest hanging fruit is eaten. i Next, the question is about additional indexes. My guess is that an index on DespatchNote(RealType, Archived, id) would help the query. This should reduce the volume of data going into the joins, and might encourage the database engine to use the indexes for the joins.

answered Mar 17 '15 at 15:32

Gordon Linoff

1,242,037
58
646
786

Hi Gordon, I have 2 Non-Clustered Index on columns DespatchNote.RealType and DespatchNote.Archived named : IX3_DespatchNote_RealType(non unique , non Cluster) FK_DespatchNote_Archived(non unique , non Cluster) – Andry Mar 17 '15 at 16:11
/****** Object: Index [FK_DespatchNote_Archived] Script Date: 03/17/2015 17:10:34 ******/ CREATE NONCLUSTERED INDEX [FK_DespatchNote_Archived] ON [dbo].[DespatchNote] ( [Archived] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY] – Andry Mar 17 '15 at 16:13
/****** Object: Index [IX3_DespatchNote_RealType] Script Date: 03/17/2015 17:13:53 ******/ CREATE NONCLUSTERED INDEX [IX3_DespatchNote_RealType] ON [dbo].[DespatchNote] ( [RealType] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] – Andry Mar 17 '15 at 16:14
And I have A clustered Index on Primary key ID for each table like: /****** Object: Index [PK_DespatchNote] Script Date: 03/17/2015 17:24:34 ******/ ALTER TABLE [dbo].[DespatchNote] ADD CONSTRAINT [PK_DespatchNote] PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY] – Andry Mar 17 '15 at 16:25