Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Queries running long Expand / Collapse
Author
Message
Posted Monday, July 30, 2012 1:38 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, September 9, 2013 8:12 AM
Points: 137, Visits: 297
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?
Post #1337511
Posted Monday, July 30, 2012 2:21 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 11:43 AM
Points: 256, Visits: 1,515
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?
Post #1337538
Posted Monday, July 30, 2012 2:25 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, September 9, 2013 8:12 AM
Points: 137, Visits: 297
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.
Post #1337542
Posted Monday, July 30, 2012 3:27 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 11:43 AM
Points: 256, Visits: 1,515
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?
Post #1337580
Posted Monday, July 30, 2012 3:39 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, September 9, 2013 8:12 AM
Points: 137, Visits: 297
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?
Post #1337584
Posted Monday, July 30, 2012 4:03 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 11:43 AM
Points: 256, Visits: 1,515
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?
Post #1337594
Posted Monday, July 30, 2012 4:29 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 11:43 AM
Points: 256, Visits: 1,515
Brian Brown-204626 (7/30/2012)
[quote]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.

http://blog.sqlauthority.com/2011/02/19/sql-server-preemptive-and-non-preemptive-wait-type-day-19-of-28/



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
Post #1337600
Posted Tuesday, July 31, 2012 7:48 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, September 9, 2013 8:12 AM
Points: 137, Visits: 297
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.
Post #1337904
Posted Tuesday, July 31, 2012 9:32 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 5:03 PM
Points: 1,394, Visits: 6,592
Also check the power saving mode of the vm host(s). For reliable performance: high performance mode instead of balanced power saving
Post #1337987
Posted Tuesday, July 31, 2012 12:09 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, September 9, 2013 8:12 AM
Points: 137, Visits: 297
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.
Post #1338084
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse