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

I have had this type of problem before and found two SQL tools of use - assuming it is not simply a communications fault.

1. SQL Server Performance Dashboard Reports (google it as it is a free Microsoft download and not part of a SQL Server installation).

2. SQL Activity Monitor.

It is very difficult tracing a connection error in SQL Profiler due to the volume of data. The dashboard reports can help as (among other things) they analysis the queries hitting the database and make recommendations on indexing based on the real workload over time. I have had problems when my indexes were not up to the job and it caused timeout errors in certain circumstances as the data volume increased. Even if you think your indexes are okay, this tool is likely to point out some improvements. Of course the tool offers much more than this and can identify your worst performing queries, etc.

The SQL Activity Monitor has been very useful as I have had locking issues which caused queries to hang. The activity monitor will trace the query back to the PC and should give you the SQL command that caused the problem. Sometimes it is surprising how SQL can lock up. Of course you need to have immediate access to the activity monitor at the point the timeout has occurred if locking is the cause of your problem.

Hope this helps as timeouts can be frustrating.

Aaron

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.

Hi Bill,



You are right about the proc being executed on the server and the result set is passed up the line.



Presumably your app executes more than one query - if the rest run ok then I would have to think there is some transient issue with the query itself.



You can start and stop the profiler as you need to and then apply a filter to any of the columns to restrict the amount of data you are looking at - I find filtering by username is pretty useful. In the Profiler Event Classes go to Stored Procedures and select SP-Starting and SP-Completed (to give the run time of the proc) and in Data Columns select DbUserName. Coordinate with the remote user and start / stop the Profiler to collect the data you want then filter as needed. This should give you an insight into what is happening on the server anyway.



Also, you could write a form that simply takes in a proc name. You could then have various procs that the remote user could execute to see what happens under various conditions, e.g. single row data set returned, 5,000 row data set returned etc.



Cheers, Peter

Gotta love those transient issues! Using the Profiler, I can see that there are about 1000 user and system queries running against the server every few minutes. That does not seem too excessive to me (although, I do not have any benchmarks for that stat). I have watched that query in the Profiler. Oddly, like a watched pot, it never boils (uh, fails). The connection does not timeout when I am looking. Typical.







I like this idea. Easy to implement and I could set it on my admin menu so that only the IT group can access and test. That will not take too long to setup. Maybe this will shed a little light on this dark corner of my application and network.



Thanks!!

Bill

I created a small Windows Forms app and pointed the database to a web-based SQL Server (controls a DotNetNuke website). The app is very simple. I have basically three forms...the main and two maintenance forms. The main opens without any problem (no security involved, yet). When I open either of the maintenance forms, it takes 20-30 seconds before the form is displayed. There are only a few records (2 or 3) that the app has to retrieve. I am not sure I understand why there is such a delay. The connection tests successfully instantly. I can open a table in Management Studio and get a near instant response. Yet, when I utilize StrataFrame, and in this case the Business Objects, I get delayed response times. I must be doing something fundamentally wrong with the framework. I share this here because of the similar issues with the remote subnet mentioned above. Both of these apps share similar network speeds on different networks. One is T1 and the other is DSL.



Maybe this additional information will provide an "AHA!" moment for someone. To me it is not at all obvious what I am doing wrong. This is something that I now need to address with two different installations.



Thanks, again!

Bill

Bill, there is really not much between SF and the connection. I would be curious to see what your results were if you just created an SqlConnection and used an SqlAdapter to populate a DataTable with the same query you are performing within SF.



The one thing that I know is that there is nothing between the BO and the DAL that would cause anything like this, so there has to be something going on within a query, sproc, or connection. It will have to be one of these things.



Now, if you can provide the database and a sample that reproduces this, I will be more than happy to give it a shot on this side to see if the results are reproducable. Performance issues are always frustrating, but I really can tell you that SF should have nothing to do with this part of the formula.



So some additional questions I would post are:



1. If you use the raw SqlConnection and SqlAdapter to fill a data table, what are the results?

2. What is your query and are you using an intrinsic SF method (i.e. Fill methods)?

3. If you are calling a sproc, what does your SPROC look like and how are you calling the sproc?



I know that the answer lies within these questions as the ONLY time I have ever experienced slowness in SQL (including through SF BOs) is when there was a legitimate query issue (unoptimized ... which I am good at writing those first ) or a permission or connection issue. I have also had issues in the field with a proxy and firewall (and a few times NAT translations) causing the issue. So my point is that the connection between the app and the server and SSMS were not taking the same path.



One more thought, if you are using SSMS it is most likely using named pipes, not TCPIP. If you are connecting through your application, you are most likely connection via TCPIP...thus you can experience differences in performance. Make sure that you have your TCPIP traffic on the server and between the two points setup correctly as well. This can cause slowness also.