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.
| Author |
Topic |
|
miranwar
Posting Yak Master
125 Posts |
 Posted - 2006-11-14 : 18:07:49
|
| Hi, I have bee monitoring sql server via profiler for the top poorest queries internal Users are complaing about poor performance for our Global web content management system . I ran a trace all day. I have transferred the Trace file to a sql table and have written some queries to give me the most expensive query in tems of duration. I am aggegating the SP stmt completed event by AVG ,MAX, MIN ,and SUM. Now the problem is that the poorest performace SP statement has a min duration of 3 seconds and a max duration of 62 seconds. However, I cant recreate the poor performance from my query analyzer connection. When I paste all 10 s-p calls into QA the sp's execute within seconds The actual staement that has a wide disparity of duration. The Sp statement is a select statement with NO LOCK table hint. Could this purley be to network issues on the users network. I'm guessing duration would only end once all packets have been passed back to the client so if there is a lag in the bandwith this will affect duration. The problem gets a bit more complex as i know tha the min and max durations were executed by users in the UK. I dont know how to explain this except in tems of network issue. I am on sepearete domain from the uk users and my queries run without no problems.One last thing the SP is returning multiple resultsets which are being manipulated by a cold fusion page.Can ayone help me in trying to shed some light on this disparity and why this may be? |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-11-15 : 01:15:16
|
| Are all the slow queries from people on the UK network?If not was there anything else going on when that SProc was slow? (like expanding the database!!) or were other queries at the time running more slowly than average? (might have been some sort of bottleneck)"One last thing the SP is returning multiple resultsets which are being manipulated by a cold fusion page"I don't know how ColdFusion works in this regard. If it ships the HTML as it prepares it then I suppose there is a chance that there is some handshaking between Server & Client, and that could (could it??) delay ColdFusion asking for more data from the Sproc, which in turn could lead to the SProc End Time being artificially long (could it?)In ASP there is a BUFFER option which stops the page being shipped piecemeal - maybe something similar in ColdFusion?In our Web rendering Engine we assemble the whole page before we ship anything to the client. We also pull all resultsets into memory [on the web server] immediately the Sproc call returns in order to reduce these sorts of effects, but I can't speak for ColdFusion on that score.Its a few years since I had any dealings with ColdFusion, and I expect it has improved, but back then it used to perform very badly on these sorts of issues. Used to stuff the rendered output full of blank space, which was "expensive" in bandwidth, and so on.Kristen |
 |
|
|
miranwar
Posting Yak Master
125 Posts |
Posted - 2006-11-15 : 17:10:19
|
| hmmm Thats interesting. I will speak to the CF developers and get thier take. However, I just had an idea do you think there is a possibility of an inefficient query plan , if so should I compile the specfic sproc with the recompile switch. so that the query plan gets regenerated each time? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-11-16 : 00:44:24
|
| "if so should I compile the specfic sproc with the recompile switch""When I paste all 10 s-p calls into QA the sp's execute within seconds"Well assuming you are using the same parameters to the SProcs as the UK users then it doesn't look like the problem is with SQL Server at all.Kristen |
|
|
|
|
|
|
|
|
sql query disparity