HostWeb Forums » Microsoft Databases » microsoft.public.sqlserver.relationalserver.performance » Different execution plan on 2 servers with same database, same dat
Topic: Different execution plan on 2 servers with same database, same dat
Hi guys
This my problem, the execution plan is different on server (server1) than
another server (server2) on the same database shema and the same data.
MSSQLserver 9.0.3042 is installed on the server1.
MSSQLserver 9.0.3054 is installed on the server2.
The server2 generates a execution plan very more performant than the server1.
I would like that the server1 generates the same execution plan than the
server2.
The two servers have the same configuration (SQLServer).
the server1 generates:
Filter(WHERE:(CONVERT_IMPLICIT(nvarchar(100),
[CONTENT].[CONTENT_COLLECTIONKEY],0)=[@P1]))
|--Nested Loops(Inner Join, OUTER REFERENCES:( [CONTENT].[CONTENT_ID])
OPTIMIZED)
|--Index Scan(OBJECT:(
[CONTENT].[IDX_CONTENT_KEY]),
| WHERE:(CONVERT_IMPLICIT(nvarchar(100), [CONTENT].
| [CONTENT_KEY],0)=[@P0]))
|--Clustered Index Seek(OBJECT:(
[CONTENT].[PK__CONTENT__7C8480AE]),
SEEK:( [CONTENT].[CONTENT_ID]=
[CONTENT].[CONTENT_ID]) LOOKUP ORDERED FORWARD)
* server2
Filter(WHERE:(CONVERT_IMPLICIT(nvarchar(100),
[CONTENT].[CONTENT_COLLECTIONKEY],0)=[@P1]))
|--Nested Loops(Inner Join, OUTER REFERENCES:( [CONTENT].[CONTENT_ID])
OPTIMIZED)
|--Nested Loops(Inner Join, OUTER
REFERENCES:([Expr1006], [Expr1007], [Expr1005]))
| |--Compute
Scalar(DEFINE:(([Expr1006],[Expr1007],[Expr1005])=GetRangeThroughConvert([@P0],[@P0],(62))))
| | |--Constant Scan
| |--Index Seek(OBJECT:(
[CONTENT].[IDX_CONTENT_KEY]),
| SEEK:( [CONTENT].[CONTENT_KEY] > [Expr1006] AND
| [CONTENT].[CONTENT_KEY] < [Expr1007]),
| WHERE:(CONVERT_IMPLICIT(nvarchar(100),
| [CONTENT].[CONTENT_KEY],0)=[@P0])
ORDERED FORWARD)
|--Clustered Index Seek(OBJECT:( [CONTENT].[PK__CONTENT__7C8480AE]),
SEEK:( [CONTENT].[CONTENT_ID]=
[CONTENT].[CONTENT_ID]) LOOKUP ORDERED FORWARD)
My application executes this query:
select CONTENT_ID from CONTENT where CONTENT_KEY = @P0 and
CONTENT_COLLECTIONKEY = @P1;
This my definition of the table Content:
CREATE TABLE CONTENT (
CONTENT_ID NUMERIC(20) IDENTITY,
CONTENT_KEY varchar(100) NOT NULL,
CONTENT_INFO XML,
CONTENT_COLLECTIONKEY varchar(100),
CONTENT_DATATXT NVARCHAR(MAX),
CONTENT_DATABIN VARBINARY(MAX),
CONTENT_REVNBR varchar(100),
CONTENT_MODIRC varchar(100),
CONTENT_ORDER varchar(100),
PRIMARY KEY (CONTENT_ID)
)
;
and this is the index created on this table:
CREATE INDEX IDX_CONTENT_COLLECTIONKEY ON CONTENT (
CONTENT_COLLECTIONKEY
)
ON [INDEX]
;
CREATE INDEX IDX_CONTENT_KEY ON CONTENT (
CONTENT_KEY
)
ON [INDEX]
;
Replies below ↓
Replies
RE: Different execution plan on 2 servers with same database, same dat
I am sorry, I post this message twice
"Fabrice Guillet" wrote:
> Hi guys
>
> This my problem, the execution plan is different on server (server1) than
> another server (server2) on the same database shema and the same data.
> MSSQLserver 9.0.3042 is installed on the server1.
> MSSQLserver 9.0.3054 is installed on the server2.
>
> The server2 generates a execution plan very more performant than the server1.
> I would like that the server1 generates the same execution plan than the
> server2.
> The two servers have the same configuration (SQLServer).
>
> the server1 generates:
> Filter(WHERE:(CONVERT_IMPLICIT(nvarchar(100),
> [CONTENT].[CONTENT_COLLECTIONKEY],0)=[@P1]))
>
> |--Nested Loops(Inner Join, OUTER REFERENCES:( [CONTENT].[CONTENT_ID])
> OPTIMIZED)
> |--Index Scan(OBJECT:(
> [CONTENT].[IDX_CONTENT_KEY]),
> | WHERE:(CONVERT_IMPLICIT(nvarchar(100), [CONTENT].
> | [CONTENT_KEY],0)=[@P0]))
> |--Clustered Index Seek(OBJECT:(
> [CONTENT].[PK__CONTENT__7C8480AE]),
> SEEK:( [CONTENT].[CONTENT_ID]=
> [CONTENT].[CONTENT_ID]) LOOKUP ORDERED FORWARD)
> * server2
>
> Filter(WHERE:(CONVERT_IMPLICIT(nvarchar(100),
> [CONTENT].[CONTENT_COLLECTIONKEY],0)=[@P1]))
>
> |--Nested Loops(Inner Join, OUTER REFERENCES:( [CONTENT].[CONTENT_ID])
> OPTIMIZED)
> |--Nested Loops(Inner Join, OUTER
> REFERENCES:([Expr1006], [Expr1007], [Expr1005]))
>
> | |--Compute
> Scalar(DEFINE:(([Expr1006],[Expr1007],[Expr1005])=GetRangeThroughConvert([@P0],[@P0],(62))))
>
> | | |--Constant Scan
>
> | |--Index Seek(OBJECT:(
> [CONTENT].[IDX_CONTENT_KEY]),
> | SEEK:( [CONTENT].[CONTENT_KEY] > [Expr1006] AND
> | [CONTENT].[CONTENT_KEY] < [Expr1007]),
> | WHERE:(CONVERT_IMPLICIT(nvarchar(100),
> | [CONTENT].[CONTENT_KEY],0)=[@P0])
> ORDERED FORWARD)
> |--Clustered Index Seek(OBJECT:( [CONTENT].[PK__CONTENT__7C8480AE]),
> SEEK:( [CONTENT].[CONTENT_ID]=
> [CONTENT].[CONTENT_ID]) LOOKUP ORDERED FORWARD)
>
>
> My application executes this query:
> select CONTENT_ID from CONTENT where CONTENT_KEY = @P0 and
> CONTENT_COLLECTIONKEY = @P1;
>
> This my definition of the table Content:
> CREATE TABLE CONTENT (
> CONTENT_ID NUMERIC(20) IDENTITY,
> CONTENT_KEY varchar(100) NOT NULL,
> CONTENT_INFO XML,
> CONTENT_COLLECTIONKEY varchar(100),
> CONTENT_DATATXT NVARCHAR(MAX),
> CONTENT_DATABIN VARBINARY(MAX),
> CONTENT_REVNBR varchar(100),
> CONTENT_MODIRC varchar(100),
> CONTENT_ORDER varchar(100),
> PRIMARY KEY (CONTENT_ID)
> )
> ;
>
> and this is the index created on this table:
> CREATE INDEX IDX_CONTENT_COLLECTIONKEY ON CONTENT (
> CONTENT_COLLECTIONKEY
> )
> ON [INDEX]
> ;
>
> CREATE INDEX IDX_CONTENT_KEY ON CONTENT (
> CONTENT_KEY
> )
> ON [INDEX]
> ;
>
>
>
>
RE: Different execution plan on 2 servers with same database, same dat
Fabrice Guillet (FabriceGuillet@discussions.microsoft.com) writes:
> I am sorry, I post this message twice
Just curious: did my post address your problem?
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx