March 26, 2009 at 10:56 am
We have an application which generates a large (8784 chars) query. These queries have serveral union alls (sometimes as large as 8) and use the FOR XML EXPLICIT clause. Performance of the application when it's executing these queries varies significantly among different instances of SQL Server. I've found that the single biggest factor accounting for the performance differences is compile time. I'm curious why compile time would vary so much and if there are any knobs you can twist to improve it on a slow system.
For example comparing a developer (dev) box with a production (prod) box. Msinof32 specs
Dev
OS: Windows XP
Ver: 5.1.2600 Service Pack 2 Build 2660
Dell OptiPlex 755
4 Processors x86 Family 6 Model 15 Stepping 11 Intel ~2392 Mhz
Total Physical Memory: 4096 MB
Disk: ST3250310AS IDE
SQL Server Devloper Edition 9.00.3042.00
Prod
OS: Windows Server 2003 Standard x64 Edition
Ver: 5.2.3790 Service Pack 1 Build 3790
Dell PowerEdge 2850
4 Processors EM64T Family 15 Model 4 Stepping 1 ~2793 Mhz
Total Physical Memory 4095.08
Disk: PERC LD 0 PERCRAID SCSI Disk Device
PERC LD 1 PERCRAID SCSI Disk Device
SQL Server Enterprise Edition 9.00.3042.00 (SQL Server 32bit)
Note, the Prod Box also has a SQL Server 2000 Instance installed on it. My next test is to stop the 2000 instances and see if that has any effect on the performance of 2005.
For a test I took 114 of these queries varying only by a guid and executed them twice. Following is the average compile and execution time for each pass. The system is followed by the total elapsed time to execute the batch and the times are in milli-seconds.
Dev 0:54 vv2005 2:24
Exec Compile Exec Compile
----- --------- ------ --------------
4 465 7 1220 Pass 1
4 1 7 1 Pass 2
March 27, 2009 at 6:00 am
You have 4gb of memory on a production box running both SQL Server 2000 and SQL SErver 2005? I'd be willing to bet you're seeing serious memory pressure and probably CPU pressure as well. Both these would seriously impact compile time.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 30, 2009 at 6:42 am
I did a test on the prod box. I shut down the SQL 2000 instance so that all the machine resources would be available to the SQL 2005 instance and ran the test script. The compile time was basically constant. I then constrained the SQL 2005 instance to 64 MB of memory and ran the script. Again the compile time was basically constant. However, with the memory constrained, the second pass did not find the query plans in the cache and the queries were compiled again. I then shutdown the SQL 2005 instance and started the SQL 2000 instance and did the same test. What I found was that compile time seems to be roughly constant while I can see affects on plans being available in the cache by constraining memory.
(By the way the prod box is not a 'real' production server, but serves as sort of a production box to the development group.)
March 30, 2009 at 7:30 am
Interesting experiment.
The only way I know to affect compile time is to modify the query itself. The more complicated it is, the longer the compile time, the simpler, the quicker. We had a query with an 80 table join once (don't ask) that took a LONG time to compile. You can't even force the plan because it will still compile prior to using the plan you want it to use, making it actually a longer process.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 30, 2009 at 8:58 am
I'm done looking at the SQL Server side. I've looked at the code and we definitely have some inefficiencies where we are executing the query more than is necessary. So we'll fix it there. It was just surprising to me to see basically 3 times longer to compile the query on the prod machine as compared to the dev machine. Actually in looking at this across 5 instances on 4 separate machines we seem to have compile times varying from 300 ms to 1500 ms without being able to pin those differences onto hardware or SQL Server Versions.
March 30, 2009 at 9:10 am
Is it possible the statistics are out of date? Maybe it's working harder to gather information on some servers compared to others because of the status of the statistics.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 30, 2009 at 10:55 am
Nope we had tried statistics and I updated all stats with fullscan. Still no effect.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply