Our new SF application has been in full use for over a month. All, for the most part, has been going extremely well. Just recently...perhaps in the last week or so...we have been encountering a ton of SQLConnection errors at the remote end of our T1 line (10 - 20 per day). I have been doing my best to examine each window to best utilize the tips that I have gotten from all of you in the past, but now, I am stuck trying to figure out the origin of these errors (so many windows...so many queries). I assume that I could fire up the SQL Profiler, but there is so much data that comes flying through there, I am not sure how to best filter it to get to the root of the problem. I have a suspicion that there is one window or even one query that is gumming up the works for everyone else. I'd appreciate any tips or tricks for helping me track down my pesky connection problems.



Thanks!!

Bill

Yeah, this is one thing that is hard to get away from and why we generally develop on really large databases so that we can see where our performance issues are in development. Though this doesn't always work, it does resolve most of the issues.



So it does sound like you have a query that is taking a while. We have learned over the years that you can make most any query fast if you spend time working through the execution plan and tweaking the SPROC until you get it optimized...this can take time and be frustrating as well. But you are correct in that you most likely have a query that is taking a while which is causing a timeout.



You can prevent the timeout by changing the connection timeout as well (and this is an important step) setting the timeout on the SqlCommand (or whichever command object you are using). But if you know where this is happening to your clients and what they are running when it happens, then I would start looking at queries to see if I could optimized the timeout. In this case, I would probably do both to ensure the customer doesn't get kicked out. But our customers are impatient enough that if they have to wait for a timeout, the query is taking too long anyway. There are some queries in our medical application that may be combining dozens of tables and reflecting over millions of records to produce statements, and if they leave the criteria wide open, then it would possible timeout on a large database. So in these instances, when it could take a long time (up to 3-5 minutes as it is generating all of the statements and doing some massive data crunching) we adjust the timeout and make sure the customer knows it could take a while.

I am still using SQL Server 2000. Yes, it is a dinosaur, but we have not had a good reason to upgrade the 15-20 databases on the server to 2005/2008. I'll do the hard work of checking my indexes and stored procedures. Knowing a tool exists for a newer version of SQL Server that may help us, perhaps my manager will authorize the upgrades soon. I can only hope.



Thanks a bunch, guys!

Bill

Ah...gotcha! To be honest it didn't cross my mind to ask which version of SQL you were on. I will tell you that the performance between SQL Server 2000 and SQL Server 2008 is massive! 2008 is faster than 2005 and 2005 is light years ahead of 2000. So good luck on getting the upgrade pushed through, it is most definitely worth it!

Let me approach this subject again with a little more information...



I have query that takes 9 seconds to run on the SQL Server itself. I can repeat that duration nearly every single time I execute it in the Query Analyzer. Sometimes it goes as high as 10 or 11 seconds, but 99% of the time it completes in 9 seconds. I am working on this query to improve it, but for all practical purposes, it is working within acceptable parameters. The number of records returned is less than 500.



As far as I understand the nature of the SQL Server, the actual execution is happening on the server itself, so if the query is called from the same subnet or a subnet from a distant location, the query will take the same amount of time to execute on the server. Please correct me if I am wrong about this point.



If a user in subnet A (the same subnet as the SQL Server) executes the query via the StrataFrame application, the window responds in less than 10 seconds (normally). If a user in subnet B (connected via a T1 line) executes the query via the same application, the window occasionally times out. Not always, but sometimes (couple times per day). Enough to be annoying, anyway.



Shouldn't I expect that the window in subnet B to be slightly slower than subnet A? After all it is only 1.5Mb. It reality it is much slower, so, where could the bottleneck be...the SQL connection, the data returned, something else? Should I be assuming that business objects (if any in the window) are being hydrated on the client? So, there would be no additional overhead between the client and the server regarding data flow, right?



Sorry...I know I have brought this up soooo many times, but I've got to point a finger at something tangible. My manager (and, in addition, the vice-president) is expecting an answer. At the moment, I have nothing more to offer.