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...
Thanks
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
|||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...
>
>
|||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...[vbcol=seagreen]
> 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:
|||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