Query execution plan difference

Question

Query execution plan difference

farax_x

Hall of Fame

Points: 3579
More actions
November 24, 2011 at 1:52 am

#252541

Hi all,
I have 2 dbs on 2 separated servers with the same structure(all tables, indexes ,... are the same).
but Query execution plan for a similar query is different ! how It is possible ? How can I solve this problem ?
query :
SELECT senReceiverDepartmentId, COUNT(1)
FROM dbo.tblSend WITH(NOLOCK)
WHERE senRegisterDate < DATEADD(m,-6,GETDATE() )
GROUP BY senReceiverDepartmentId
execution plan 1 :
execution plan 2 :
and table structure and indexes :
USE [IstgDocument]
GO
/****** Object: Table [dbo].[tblSend] Script Date: 11/24/2011 12:21:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tblSend](
[senDocId] [uniqueidentifier] NOT NULL,
[senId] [uniqueidentifier] NOT NULL,
[senFolId] [uniqueidentifier] NOT NULL,
[senReceiverDepartmentId] [uniqueidentifier] NOT NULL,
[senReceiverPersonnelId] [uniqueidentifier] NOT NULL,
[senPersistanceDate] [char](8) NOT NULL,
[senReadDocumentDate] [datetime] NULL,
[senActionDescription] [nvarchar](900) NULL,
[senOwnerDepartmentId] [uniqueidentifier] NOT NULL,
[senOwnerPersonnelId] [uniqueidentifier] NOT NULL,
[senType] [smallint] NOT NULL,
[senReadDocument] [tinyint] NOT NULL,
[senSecurityId] [smallint] NOT NULL,
[senUrgencyId] [smallint] NOT NULL,
[senRegisterDate] [datetime] NOT NULL,
[senActionDate] [datetime] NULL,
[senProperties] [nvarchar](max) NULL,
[senDescription] [nvarchar](max) NULL,
[senRegId] [uniqueidentifier] NULL,
[senRowNo] [int] NULL,
[senOrganizationId] [uniqueidentifier] NOT NULL,
[senStatus] [smallint] NOT NULL,
[senIntId] [int] IDENTITY(1,1) NOT NULL,
CONSTRAINT [PK_tblSend] PRIMARY KEY NONCLUSTERED
(
[senIntId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 65)
)
GO
SET ANSI_PADDING ON
GO
ALTER TABLE [dbo].[tblSend] ADD CONSTRAINT [DF_tblSend_docOrganizationId] DEFAULT ('00000000-0000-0000-0000-000000000000') FOR [senOrganizationId]
GO
ALTER TABLE [dbo].[tblSend] ADD CONSTRAINT [DF_tblSend_senStatus] DEFAULT ((0)) FOR [senStatus]
GO
USE [IstgDocument]
GO
/****** Object: Index [docId] Script Date: 11/24/2011 12:22:13 ******/
CREATE NONCLUSTERED INDEX [docId] ON [dbo].[tblSend]
(
[senDocId] ASC
)
INCLUDE ( [senRowNo]) 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 = 70) ON [FGIndexingSen]
GO
/****** Object: Index [OwnerDep] Script Date: 11/24/2011 12:22:13 ******/
CREATE NONCLUSTERED INDEX [OwnerDep] ON [dbo].[tblSend]
(
[senOwnerDepartmentId] 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 = 70) ON [FGIndexingSen]
GO
/****** Object: Index [OwnerPrs] Script Date: 11/24/2011 12:22:13 ******/
CREATE NONCLUSTERED INDEX [OwnerPrs] ON [dbo].[tblSend]
(
[senOwnerPersonnelId] 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 = 70) ON [FGIndexingSen]
GO
/****** Object: Index [PK_tblSend] Script Date: 11/24/2011 12:22:13 ******/
ALTER TABLE [dbo].[tblSend] ADD CONSTRAINT [PK_tblSend] PRIMARY KEY NONCLUSTERED
(
[senIntId] 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 = 65)
GO
/****** Object: Index [RecDep] Script Date: 11/24/2011 12:22:13 ******/
CREATE NONCLUSTERED INDEX [RecDep] ON [dbo].[tblSend]
(
[senReceiverDepartmentId] 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 = 70) ON [FGIndexingSen]
GO
/****** Object: Index [RegisterDate] Script Date: 11/24/2011 12:22:13 ******/
CREATE NONCLUSTERED INDEX [RegisterDate] ON [dbo].[tblSend]
(
[senRegisterDate] 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 = 70) ON [FGIndexingSen]
GO
/****** Object: Index [RevPrs] Script Date: 11/24/2011 12:22:13 ******/
CREATE NONCLUSTERED INDEX [RevPrs] ON [dbo].[tblSend]
(
[senReceiverPersonnelId] 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 = 70) ON [FGIndexingSen]
GO
/****** Object: Index [RowNo] Script Date: 11/24/2011 12:22:13 ******/
CREATE NONCLUSTERED INDEX [RowNo] ON [dbo].[tblSend]
(
[senRowNo] 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 = 70) ON [FGIndexingSen]
GO
/****** Object: Index [SenFolId] Script Date: 11/24/2011 12:22:13 ******/
CREATE NONCLUSTERED INDEX [SenFolId] ON [dbo].[tblSend]
(
[senFolId] 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 = 70) ON [FGIndexingSen]
GO
/****** Object: Index [senFolIdDepIncId] Script Date: 11/24/2011 12:22:13 ******/
CREATE NONCLUSTERED INDEX [senFolIdDepIncId] ON [dbo].[tblSend]
(
[senFolId] ASC,
[senReceiverDepartmentId] ASC
)
INCLUDE ( [senId]) 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 = 70) ON [FGIndexingSen]
GO
/****** Object: Index [senId] Script Date: 11/24/2011 12:22:13 ******/
CREATE NONCLUSTERED INDEX [senId] ON [dbo].[tblSend]
(
[senId] 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 = 70) ON [FGIndexingSen]
GO
/****** Object: Index [SenOrgId] Script Date: 11/24/2011 12:22:13 ******/
CREATE NONCLUSTERED INDEX [SenOrgId] ON [dbo].[tblSend]
(
[senOrganizationId] 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 = 70) ON [FGIndexingSen]
GO
/****** Object: Index [SenPersisDate] Script Date: 11/24/2011 12:22:13 ******/
CREATE NONCLUSTERED INDEX [SenPersisDate] ON [dbo].[tblSend]
(
[senPersistanceDate] 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 = 65) ON [PRIMARY]
GO
/****** Object: Index [SenRegId] Script Date: 11/24/2011 12:22:13 ******/
CREATE NONCLUSTERED INDEX [SenRegId] ON [dbo].[tblSend]
(
[senRegId] 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 = 70) ON [PRIMARY]
GO
/****** Object: Index [senRegisterDate_docId] Script Date: 11/24/2011 12:22:13 ******/
CREATE NONCLUSTERED INDEX [senRegisterDate_docId] ON [dbo].[tblSend]
(
[senRegisterDate] ASC
)
INCLUDE ( [senDocId]) 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 = 65) ON [FGIndexingSen]
GO
/****** Object: Index [senStaus] Script Date: 11/24/2011 12:22:13 ******/
CREATE NONCLUSTERED INDEX [senStaus] ON [dbo].[tblSend]
(
[senStatus] 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 = 70) ON [FGIndexingSen]
GO
/****** Object: Index [Type] Script Date: 11/24/2011 12:22:13 ******/
CREATE NONCLUSTERED INDEX [Type] ON [dbo].[tblSend]
(
[senType] 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 = 70) ON [FGIndexingSen]
GO

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 1

Exact same table definitions? Exact same indexes? Exact same data volumes and distribution? Exact same hardware?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 2

GilaMonster (11/24/2011)
Exact same table definitions? Exact same indexes? Exact same data volumes and distribution? Exact same hardware?

Exact same configuration (SQL Server)? Version?

farax_x Hall of Fame Points: 3579 More actions · Answer 3

I found my problem, it was because of this : pk was non-clustered index i change it 2 cluster index and everything is ok now.

tnx