March 24, 2014 at 9:39 am
I created a table called Performance_Statistics that have several columns with just about every SQL datatype.
I created it on Server A and Server B. I used Red Gate's Data Generator to populate both tables with 100,000 records.
The issue I am having is on Server B, the following SQL query is much longer: SELECT * FROM Performance_Statistics
On Server A it took 47 seconds and on Server B it took 153 seconds. These are both first time queries and have no compiled execution plans already on the servers.
Then I created Stored Procedures with the same query inside. The results changed to:
Server A took 38 seconds and Server B dropped to 27 seconds.
Then I tried the same queries again not inside a Stored Procedure:
Server A took 36 seconds and Server B hit 27 seconds again.
At this point I am sure the execution plan was in place and it used it.
Just to note the Performance_Statistics table did not have any indexes on it. Just straight columns created and records loaded to it.
Could there be a difference in the way the two servers are configured that would cause first time queries to perform drastically better on Server A than on Server B, but once a Stored Procedure is created on both, then Server B outperforms Server A?
If so, where should I start looking?
Added:
Since my original post, I did a right-click, properties on both servers. I didn't find much of a difference in any setting. The only difference I noticed and this may be the big difference is on Server A the Automatically set processor affinity mask for all processors and I/O affinity mask for all processors are checked. On server B, these boxes are not checked. Could this account for the difference in first time large result queries?
March 24, 2014 at 11:28 am
Are there differences between the cost threshold for parallelism between the two? How about optimize for ad hoc? Are there differences in the amount of memory or CPU? When you start the tests, can you compare the execution plans generated? Are they different?
"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 24, 2014 at 11:46 am
The cost threshold for parallelism is set to 5 on both, Optimize for Ad-Hoc is set to false on both, memory both set to 12287, and cpus both 2. I will start over, delete the performance_statistics tables, and see what the execution plans look like when running the first query.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply