SQL Server Performance Forum – Threads Archive

Performace Issue

Hi there We have a VB application that sits on the Citrix box away from Shared SQL Server server. We found occasionally the application (query) time out on the application logs and the application hang. It’s suspect from the App team that there is something wrong the database server then. We investigate this and check using the Perfomance Monitor. The result is a considered a healty box in term of (memory, CPU, IO and network). So then we move the next one to check SQL stuff as follow: 1. Check Blocking : NONE 2. Check Statistic : Up to date. We are running update statistic every week with sampling 50%. We even increase to 100%. 3. Check Index: We are not doing reindexing but we do INDEXDEFRAG every day. 4. Run a server trace to check the longest and CPU consumed query and the query that on database is running less than 20 seconds during period of trace. I don’t see any queries that causing an issue on this particular database that’s why I don’t do any tuning further on any queries cause they are running considered normal. My question is how to prove that there is no database related issue? Also, when you run a query that execute from the app server, is there anyway to trace that whether the on specific query is completely 100% finish so not just hanging? How to approve that the result (record set) is sending back to apps server from SQL box? Continuing on: I ran the application within 5 minutes and the same time I run a SQL trace as well as checking the locking sql script that we have. During that period the application is hang (haourglass) but none of the RUNABLE query detected during that while the application is hang which that indication means that the query is completed during that time. So I check, the runable query during that period and then I sorted that by the longest running query and is only 30 seconds and they are all RPC:Completed which I believe means that the request is finish and send back to app. I am missing something here? Is there anyway from the App server to know that they are actually received this data package to approve that is not the database issue? I am just guessing here <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />. Thanks

Profiler can show both when a query or SP starts and ends. You can capture both staring and completed events and compare them to see if they all match. Based on what you have described, it appears that you have proven than SQL Server is not the problem. A Profiler trace is great proof that SQL Server is doing what it is supposed to be doing. I am not a developer, so I can’t suggest what you can do from the developer’s side to see what is going on (other than line by line debugging). Have you been able to determine any kind of pattern to the problem in order to try and refine the nature of the problem? Another option is to use a network sniffer to see what is going on, but this is not always easy to do. Also, I have seen Citrix do some odd things with databases. Is it possible to run the application on a non-Citrix box and see if the same problems exist? At least this would help refine the cause of the problem. ————————————————————–
Brad M. McGehee, SQL Server MVP
Technical Editor/Moderator www.SQL-Server-Performance.Com
Director of DBA Education for www.Red-Gate.Com
www.sqlbrad.com
www.sqlHawaii.com

… to addup Brad’s note have you referred on Citrix for any network or performance contention in this case. For such HIGH cpu occurrence I suggest to referhttp://sqlserver-qa.net/blogs/perft…erver-2005-high-cpu-occurrence-why-it-is.aspx blog. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
@http://www.askasqlguru.com/ This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

The key for SQL Trace in this case is "RPC:Completed" for each query, isn’t it? That will be last prove saying that the "SQL box is completed the query and return to the requested (app)". But still how do we know that the app server receiving on this RPC call? Any input on this? Thanks

quote:Originally posted by bradmcgehee Profiler can show both when a query or SP starts and ends. You can capture both staring and completed events and compare them to see if they all match. Based on what you have described, it appears that you have proven than SQL Server is not the problem. A Profiler trace is great proof that SQL Server is doing what it is supposed to be doing. I am not a developer, so I can’t suggest what you can do from the developer’s side to see what is going on (other than line by line debugging). Have you been able to determine any kind of pattern to the problem in order to try and refine the nature of the problem? Another option is to use a network sniffer to see what is going on, but this is not always easy to do. Also, I have seen Citrix do some odd things with databases. Is it possible to run the application on a non-Citrix box and see if the same problems exist? At least this would help refine the cause of the problem. ————————————————————–
Brad M. McGehee, SQL Server MVP
Technical Editor/Moderator www.SQL-Server-Performance.Com
Director of DBA Education for www.Red-Gate.Com
www.sqlbrad.com
www.sqlHawaii.com

Hi Satya We check this before and there is no CPU bottle neck or even any significant CPU spikes on that SQL box during that perfomance monitor and trace that we did.

quote:Originally posted by satya … to addup Brad’s note have you referred on Citrix for any network or performance contention in this case. For such HIGH cpu occurrence I suggest to referhttp://sqlserver-qa.net/blogs/perft…erver-2005-high-cpu-occurrence-why-it-is.aspx blog.

Have you referred on the counters specified in that blog, also other articles to go ahead with investigation. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
@http://www.askasqlguru.com/ This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

]]>