I have a SQL Server 2005 stored procedure that: 1) Takes 5 parameters for searching - all have default values of null since the user can pass in any combination of parameters 2) Creates a temp table variable for the initial results - this pivots the data into columns for searching. 3) Has a couple if else statements for parsing out address entry This works in less than 1 second when exectued off of SQL Server 2005 Enterprise Edition, however in SQL 2005 Workgroup edition, it takes 35 seconds when executed via Mangement Studio. If I pull the query out directly and run it (with the same parameters on the Workgroup edition), it runs in 1 second or less. Can someone explain why the stored procedure is executing so slowly in 2005 Workgroup and how I can tune it? Many thanks, Jayme
First thought, you have different default ANSI settings on the one machine, and connections can change ANSI settings. The different ANSI settings result in a different execution plan. You can check this by looking at the exec plan on the different versions of SQL Server. Just pull it directly from cache using the Dynamic Management Views (sys.dm_exec_query_plan & sys.dm_exec_query_stats). You may even see two different plans in the Workgroup version since you've run it two ways. Otherwise, it might be something related to statistics or parameter sniffing. We are dealing with two different databases after all.
Are you saying that the same query runs slower on the Workgroup instance than it does against the Enterprise instance? Are these two servers on similar hardware? With the same configuration? And the same data sets and schema? If so, then as @Grant Fritchey says, without the full execution plan (not the estimated, the actual), then it's difficult to say. If not, then depending on the differences, that could be your problem right there. Much less memory in a workgroup server could cause data to be paged to disk rather than manipulated in memory; slower CPUs will affect query times too; slower disk hardware, fewer CPUs, slower (or more convoluted) network to return results... lots of potential causes. ....Sorry - it's been a long & difficult day, and I'm feeling a little befuddled.