Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Queries running long


Queries running long

Author
Message
Brian Brown-204626
Brian Brown-204626
SSC-Enthusiastic
SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)

Group: General Forum Members
Points: 139 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?
benjamin.reyes
benjamin.reyes
Old Hand
Old Hand (307 reputation)Old Hand (307 reputation)Old Hand (307 reputation)Old Hand (307 reputation)Old Hand (307 reputation)Old Hand (307 reputation)Old Hand (307 reputation)Old Hand (307 reputation)

Group: General Forum Members
Points: 307 Visits: 2283
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?
Brian Brown-204626
Brian Brown-204626
SSC-Enthusiastic
SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)

Group: General Forum Members
Points: 139 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.
benjamin.reyes
benjamin.reyes
Old Hand
Old Hand (307 reputation)Old Hand (307 reputation)Old Hand (307 reputation)Old Hand (307 reputation)Old Hand (307 reputation)Old Hand (307 reputation)Old Hand (307 reputation)Old Hand (307 reputation)

Group: General Forum Members
Points: 307 Visits: 2283
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?
Brian Brown-204626
Brian Brown-204626
SSC-Enthusiastic
SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)

Group: General Forum Members
Points: 139 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?
benjamin.reyes
benjamin.reyes
Old Hand
Old Hand (307 reputation)Old Hand (307 reputation)Old Hand (307 reputation)Old Hand (307 reputation)Old Hand (307 reputation)Old Hand (307 reputation)Old Hand (307 reputation)Old Hand (307 reputation)

Group: General Forum Members
Points: 307 Visits: 2283
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?
benjamin.reyes
benjamin.reyes
Old Hand
Old Hand (307 reputation)Old Hand (307 reputation)Old Hand (307 reputation)Old Hand (307 reputation)Old Hand (307 reputation)Old Hand (307 reputation)Old Hand (307 reputation)Old Hand (307 reputation)

Group: General Forum Members
Points: 307 Visits: 2283
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
Brian Brown-204626
Brian Brown-204626
SSC-Enthusiastic
SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)

Group: General Forum Members
Points: 139 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.
Jo Pattyn
Jo Pattyn
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1595 Visits: 9733
Also check the power saving mode of the vm host(s). For reliable performance: high performance mode instead of balanced power saving
Brian Brown-204626
Brian Brown-204626
SSC-Enthusiastic
SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)

Group: General Forum Members
Points: 139 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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search