x

Same query different servers huge time difference - SQL server 2008

This is a probably more of an observation than a question.

We had an interesting problem, which I'm hoping is a difference between SQL versions but any alternative ideas or thoughts migth prove useful. We have a development server running SQL 2008 v. 10.0.4064.0 with 4 processors, 2 gigs of ram. We have a prodution server running SQL 2008 v. 10.0.2531.0 with 8 processors, 16 gigs of ram. I got some code off the web and created a table valued function that splits a string of comma separated values. Here's the code, passing 100 values:

-- start of code --

declare
  @Str varchar(max)
SELECT @Str = '118114649,
118919153,
118873907,
118873923,
118873937,
118125624,
123177991,
122964530,
119485437,
126048044,
126048045,
125542598,
125516806,
125467613,
121324354,
121187453,
125352841,
125329390,
125226287,
125226927,
125212441,
125212787,
122974617,
121201768,
121176803,
122979634,
122980839,
119942742,
122952628,
122952629,
119942766,
119942801,
122780751,
122780752,
119942839,
119452332,
119942878,
122760867,
121709150,
122769337,
119942885,
122769339,
124972815,
124964943,
119454215,
124617653,
124627470,
119417601,
121698010,
121698011,
124609701,
122769341,
122769342,
124609704,
119942895,
124609706,
124609707,
124597691,
119942942,
124594362,
124594712,
121698705,
124507367,
120864101,
120887102,
120449533,
120449534,
120438063,
120438064,
124451025,
120439258,
119942957,
124332687,
124308397,
120439725,
124296653,
124296888,
124297524,
124299272,
124299514,
124299811,
124300388,
124300390,
124300392,
124300648,
118873898,
124305342,
121687388,
119928741,
120439726,
124157044,
124157454,
119557143,
121677364,
124158484,
120433415,
120301828,
124131922,
120273975,
124110416'

  declare @TempList TABLE (ID varchar(4000) NOT NULL)
  select @Str = REPLACE(REPLACE(REPLACE(@Str, ':', ','), '|', ','), ';', ',')

  declare @x xml
  set @x = '<i>' + REPLACE(@Str, ',', '</i><i>') + '</i>'

  insert @TempList
  select ltrim(rtrim(x.i.value('.', 'varchar(max)'))) as ID
  from @x.nodes('//i') as x(i)
  where ltrim(rtrim(x.i.value('.', 'varchar(max)'))) <> ''

select * from @TempList

-- end of code --

On the development server, executing it 5 times, the total execution time is: 18.2000 On the production server total execution time is: 315.2000. Already a pretty big difference. Of course, when moving it to production, we just did some cursory tests to ensure the code worked. At some point, 3500 values were passed into the function. On the dev server it ran in less than 1 second. On production, around 25 secs (yes that's 25 not .25).

Checking the execution plan on both, the only differences I can see are that on the production server there are eager table spools being done before the "Table Valued Functions (XML Reader)". The cost of these spools is 0%. And the cost of the Table Valued Function (XML Reader) are higher on production (56%, 36%) and on dev (30%, 19%).

I know I can write the function any number of ways and we have since changed it. The reason we initially went with the XML method is, in testing, (of course on the development server) it was faster than some of the "charindex", "pos", "length" methods we tested.

Thanks,

Dan
more ▼

asked Dec 13 '11 at 08:31 AM in Default

dbf gravatar image

dbf
11 2 2 3

(comments are locked)
10|1200 characters needed characters left

1 answer: sort newest

First of all, It seems that your Dev Server is with SP2 whereas your Production Server is with SP1 and If I were you I would have not compare the performance of servers with different Service Packs unless it is the last option. For e.g. I remember there was a CU for SP1 where CPU usage increases when you run a query that uses a string comparison function on a computer that has many processors after you upgrade to SQL Server 2005 Service Pack 3 or to SQL Server 2008.

Secondly, people tend to think that with huge amount of RAM, multiple processors we would gain huge improvement. Which is not the case always. I have seen servers with proper configuration, optimal disk configuration, optimal tempdb configuration etc. competing big servers with improper configuration. There are instances where one RAM chip tends to malfunction. So there are many more factors to look after before we compare the servers. Our GURUs to follow would add to that.

Moreover, have you tried MAXDOP option. Does it make any difference?
more ▼

answered Dec 14 '11 at 01:29 AM

Usman Butt gravatar image

Usman Butt
13.9k 6 8 14

Did you check the ANSI settings on both servers as well?
Dec 14 '11 at 08:48 PM Sacred Jewel
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1816
x55

asked: Dec 13 '11 at 08:31 AM

Seen: 1311 times

Last Updated: Dec 13 '11 at 02:36 PM