asked
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 = '
' + REPLACE(@Str, ',', '
') + '' 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
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?
