July 30, 2012 at 1:38 pm
I am in the process of upgrading several old servers to SQL Server 2008 R2 and am having a problem with a user application. This app uses a linked server to pull data from an Oracle data warehouse to SQL Server. On the new dev server and the old prod server, this process takes just over 30 minutes to run. On the new prod server, this process takes 2 hours 40 minutes. To test this process, I extracted the code which does the query to Oracle and ran it by itself with the same results. I have verified that all servers are running the same code, have the same data source definition in the tnsnames.ora file, and the same Oracle user. The network admins are not seeing any thing unusual between the servers. Does anyone have any other suggestions?
July 30, 2012 at 2:21 pm
Are there any hardware or operating system differrences? Are both servers using the same driver to connect to Oracle? Are you using the same protocol? Are the linked servers setup exactly the same?
July 30, 2012 at 2:25 pm
The same Oracle client is installed on both servers, and the linked servers are identical. Both servers are 64-bit. The new production server has more RAM and a bigger CPU than the new dev server, but still runs this query slower. Both are virtual servers using VMWare.
July 30, 2012 at 3:27 pm
What kind of wait types are you getting on both servers while the query is running?
Are all sql server, server wide settings and database settings the same?
Are both vm's on the same host machine?
Also in vSphere you should have a resourse allocation tab, a performance tab, and a tasks & events tab; double check to make sure you aren't overcommitted on resources and that the configurations are the same for both machines. The performance tab should give you a logged view on utilization, and the tasks and events tab will just to get a rough idea that there aren't any serious conflicts.
What does the drive/san architecture look like? What are you doing with the tempdb?
July 30, 2012 at 3:39 pm
benjamin.reyes (7/30/2012)
What kind of wait types are you getting on both servers while the query is running?The only wait I can see in the Activity Monitor is PREEMPTIVE_WAIT_GETDATA.
Are all sql server, server wide settings and database settings the same?
Yes
Are both vm's on the same host machine?
I don't know. I will have to check with our server admins, however I don't thinkk they are.
Also in vSphere you should have a resourse allocation tab, a performance tab, and a tasks & events tab; double check to make sure you aren't overcommitted on resources and that the configurations are the same for both machines. The performance tab should give you a logged view on utilization, and the tasks and events tab will just to get a rough idea that there aren't any serious conflicts.
Again, I will have to check with out server admins.
What does the drive/san architecture look like?
Out default set-up is a seperate drive for backups, transaction logs, database files, and install binaries.
What are you doing with the tempdb?
In what way?
July 30, 2012 at 4:03 pm
Same number of files, are they on their own spindles, same type of raid able to handle the same iops. Is you disk alignment the same on all drives on both machines?
Do you have instant file initialization turned on, with both machines? Log and db growth rate settings the same on both machines? Is the log on it's own spindles on both machines?
Was this an existing server that only became slow after 1 change (upgrading to sql server 2008 R2)? Or were there other changes done at the same time?
July 30, 2012 at 4:29 pm
Brian Brown-204626 (7/30/2012)
benjamin.reyes (7/30/2012)
The only wait I can see in the Activity Monitor is PREEMPTIVE_WAIT_GETDATA.
PREEMPTIVE: Simply put, this wait means non-cooperative. While SQL Server is executing a task, the Operating System (OS) interrupts it. This leads to SQL Server to involuntarily give up the execution for other higher priority tasks. This is not good for SQL Server as it is a particular external process which makes SQL Server to yield. This kind of wait can reduce the performance drastically and needs to be investigated properly.
also look at this.
Linked server operations involve a number of in-between software layers, adding an element of sluggishness. So I am not surprised if you see performance differences from one version to another. Generally it is better to use OPENQUERY (pass-through query) over 4-part naming queries. Even better to use remote stored procedures to force processing to the right server and minimize communications between remote and local servers.
http://social.msdn.microsoft.com/Forums/en/transactsql/thread/a88a69ba-45f4-4a5f-acd5-a6abd2628523
July 31, 2012 at 7:48 am
benjamin.reyes (7/30/2012)
Same number of files, are they on their own spindles, same type of raid able to handle the same iops. Is you disk alignment the same on all drives on both machines?Do you have instant file initialization turned on, with both machines? Log and db growth rate settings the same on both machines? Is the log on it's own spindles on both machines?
Was this an existing server that only became slow after 1 change (upgrading to sql server 2008 R2)? Or were there other changes done at the same time?
I will have to check with our Server Admins about the disk layout.
We ar moving from an old virtual machine to a new virtual machine.
July 31, 2012 at 9:32 am
Also check the power saving mode of the vm host(s). For reliable performance: high performance mode instead of balanced power saving
July 31, 2012 at 12:09 pm
My server admin check the two servers and found a couple of differences in the VM and hardware settings. We are making changes and will be trying the queries again.
Thank you for your advice.
August 1, 2012 at 11:36 am
Well, those changes did not work, so now we are rolling out a new virtual server. We are going to move the database that contains the queries we are testing and see if their is any improvement in the run times. If there is improvement, we will migrate the rest of the databases to this new server.
August 1, 2012 at 12:41 pm
Have you run the queries on SQL Plus on the slow machine?
August 1, 2012 at 12:49 pm
No, but if this does not work, that and running the queries on Oracle directly are our next step.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply