Execution plan compilation performance troubles

Hello all,
Could anybody give me hints or links on what is affecting stored procedure
execution plan compilation time ?
I execute this statement on 2 different machines:
EXEC MyProc WITH RECOMPILE
On M1 I have 20s for compilation of the execution plan and 1s for proc
execution
on M2 I have 50s for compilation of the execution plan and 1s for proc
execution
MyProc is quite big (3MB text file) and does a lot of insert.
M1 is P4 2.6GHZ XPSP2 SQL Server Developper
M2 is a Bi Xeon 3.0GHz WS2003 SQL Server Enterprise !
Are there some parameters affecting execution plan compilation time ?
Both machines are idle and devoted to performance tests...
ThanksFourmi,
Are the databases exactly the same? Same indexes, statistics current and
up-to-date for both, comparable fragmentation, buffer cache cleared? Are the
exec plans generated the same?
HTH
Jerry
"fourmi" <fourmi@.discussions.microsoft.com> wrote in message
news:F52A5C06-AD1A-403C-884E-EA109B9B62A7@.microsoft.com...
> Hello all,
> Could anybody give me hints or links on what is affecting stored procedure
> execution plan compilation time ?
> I execute this statement on 2 different machines:
> EXEC MyProc WITH RECOMPILE
> On M1 I have 20s for compilation of the execution plan and 1s for proc
> execution
> on M2 I have 50s for compilation of the execution plan and 1s for proc
> execution
> MyProc is quite big (3MB text file) and does a lot of insert.
> M1 is P4 2.6GHZ XPSP2 SQL Server Developper
> M2 is a Bi Xeon 3.0GHz WS2003 SQL Server Enterprise !
> Are there some parameters affecting execution plan compilation time ?
> Both machines are idle and devoted to performance tests...
> Thanks|||Jerry,
The databases are exactly the same (in fact I transfered the mdf and log to
copy the DB). They have the same structure, indexes, triggers, constraints
etc. To be sure I updated the statistics on both servers.
Also the SQL server configuration is exactly the same as shown by sp_configure
I cleaned the Proccache with DBCC FREEPROCCACHE.
The exec plans are the same.
And this factor 2 in time for sme operations !
When I generated the plan on M1 it took 2'44'' and on M2 4'48''
Additional infos
The proc is only one big transaction with a lot of inserts.
M2 is StandardEdition and not EE.
M2 is scsi dual disks in raid 1 but same problem with only one scsi disk
without raid on another machine.
M2 is 4GBMem. M1 is 1GBmem (and I am disappointed...)
In fact all our computers running WindowsServer2003 seems to have this
problem regardless of 2003 SP1 or not.
"Jerry Spivey" wrote:
> Fourmi,
> Are the databases exactly the same? Same indexes, statistics current and
> up-to-date for both, comparable fragmentation, buffer cache cleared? Are the
> exec plans generated the same?
> HTH
> Jerry
> "fourmi" <fourmi@.discussions.microsoft.com> wrote in message
> news:F52A5C06-AD1A-403C-884E-EA109B9B62A7@.microsoft.com...
> > Hello all,
> >
> > Could anybody give me hints or links on what is affecting stored procedure
> > execution plan compilation time ?
> >
> > I execute this statement on 2 different machines:
> > EXEC MyProc WITH RECOMPILE
> >
> > On M1 I have 20s for compilation of the execution plan and 1s for proc
> > execution
> > on M2 I have 50s for compilation of the execution plan and 1s for proc
> > execution
> >
> > MyProc is quite big (3MB text file) and does a lot of insert.
> >
> > M1 is P4 2.6GHZ XPSP2 SQL Server Developper
> > M2 is a Bi Xeon 3.0GHz WS2003 SQL Server Enterprise !
> >
> > Are there some parameters affecting execution plan compilation time ?
> >
> > Both machines are idle and devoted to performance tests...
> >
> > Thanks
>
>|||You will never get good compile performance on a 3MB procedure. You really
should think about breaking it up into several smaller sp's that can be more
easily compiled and are less likely to recompile.
--
Andrew J. Kelly SQL MVP
"fourmi" <fourmi@.discussions.microsoft.com> wrote in message
news:C7AA619B-BDD5-4F22-9FAA-96D37778EC2A@.microsoft.com...
> Jerry,
> The databases are exactly the same (in fact I transfered the mdf and log
> to
> copy the DB). They have the same structure, indexes, triggers, constraints
> etc. To be sure I updated the statistics on both servers.
> Also the SQL server configuration is exactly the same as shown by
> sp_configure
> I cleaned the Proccache with DBCC FREEPROCCACHE.
> The exec plans are the same.
> And this factor 2 in time for sme operations !
> When I generated the plan on M1 it took 2'44'' and on M2 4'48''
>
> Additional infos
> The proc is only one big transaction with a lot of inserts.
> M2 is StandardEdition and not EE.
> M2 is scsi dual disks in raid 1 but same problem with only one scsi disk
> without raid on another machine.
> M2 is 4GBMem. M1 is 1GBmem (and I am disappointed...)
> In fact all our computers running WindowsServer2003 seems to have this
> problem regardless of 2003 SP1 or not.
>
> "Jerry Spivey" wrote:
>> Fourmi,
>> Are the databases exactly the same? Same indexes, statistics current and
>> up-to-date for both, comparable fragmentation, buffer cache cleared? Are
>> the
>> exec plans generated the same?
>> HTH
>> Jerry
>> "fourmi" <fourmi@.discussions.microsoft.com> wrote in message
>> news:F52A5C06-AD1A-403C-884E-EA109B9B62A7@.microsoft.com...
>> > Hello all,
>> >
>> > Could anybody give me hints or links on what is affecting stored
>> > procedure
>> > execution plan compilation time ?
>> >
>> > I execute this statement on 2 different machines:
>> > EXEC MyProc WITH RECOMPILE
>> >
>> > On M1 I have 20s for compilation of the execution plan and 1s for proc
>> > execution
>> > on M2 I have 50s for compilation of the execution plan and 1s for proc
>> > execution
>> >
>> > MyProc is quite big (3MB text file) and does a lot of insert.
>> >
>> > M1 is P4 2.6GHZ XPSP2 SQL Server Developper
>> > M2 is a Bi Xeon 3.0GHz WS2003 SQL Server Enterprise !
>> >
>> > Are there some parameters affecting execution plan compilation time ?
>> >
>> > Both machines are idle and devoted to performance tests...
>> >
>> > Thanks
>>|||I would suggest that you modify the SQL server setting to only use 1 CPU on
the Processor tab of the server properties. Strange things sometimes happens
on multiproc boxes. If this helps you - have a look at "option (maxdop)"
/Fredrik
"fourmi" <fourmi@.discussions.microsoft.com> wrote in message
news:F52A5C06-AD1A-403C-884E-EA109B9B62A7@.microsoft.com...
> Hello all,
> Could anybody give me hints or links on what is affecting stored procedure
> execution plan compilation time ?
> I execute this statement on 2 different machines:
> EXEC MyProc WITH RECOMPILE
> On M1 I have 20s for compilation of the execution plan and 1s for proc
> execution
> on M2 I have 50s for compilation of the execution plan and 1s for proc
> execution
> MyProc is quite big (3MB text file) and does a lot of insert.
> M1 is P4 2.6GHZ XPSP2 SQL Server Developper
> M2 is a Bi Xeon 3.0GHz WS2003 SQL Server Enterprise !
> Are there some parameters affecting execution plan compilation time ?
> Both machines are idle and devoted to performance tests...
> Thanks