Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 Performance issue

Author  Topic 

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2011-04-07 : 10:18:41
Hi Team,
i've a 35 Gb database, and it is in good state. when i'm firing queries at SSMS, i'm getting the output in just 2 sec. but, when we execute the same query from application server, it is taking more than 30 sec.

please give me suggesions which steps i need to take care? and what tweaks i need to apply....

pls provide me your valuble suggesions.

Arnav
Even you learn 1%, Learn it with 100% confidence.

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2011-04-07 : 10:31:28
do you know your routing path from the app server to your database server?
do you have any router problems? when executing locally you bbypass network issues. sometimes your routers can be configured to operate at lower speeds than capacity.

exact same query (as seen via profiler) or queries that are meant to be the same.
are you being affected by parameter sniffing - see here for examples?
Go to Top of Page

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2011-04-07 : 16:21:15
I would guess the issue is parameter sniffing - or different execution plans because of different connection options (e.g. ARITHABORT ON/OFF).

Jeff
Go to Top of Page

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2011-04-08 : 05:05:59
Get logical read values for same queries for both SSMS and application, you can use profiler for this. If logical read is almost same for both of the quries then you must analyze your network performance.

--------------------------
http://connectsql.blogspot.com/
Go to Top of Page

Jahanzaib
Posting Yak Master

115 Posts

Posted - 2011-04-15 : 15:30:59
Check with Ping

time < 1ms and TTL = 128

Execute with SSMS on the application server with Client Statistic then check the result

Regards,
Syed Jahanzaib Bin Hassan
MCTS,MCITP,OCA,OCP,OCE,SCJP,IBMCDBA
Go to Top of Page
   

- Advertisement -