SQL Server returning empty result sets (to busy?)

Question

My apologies from the start, I don't have much information to go on and I am just looking for some help debugging my issue.

I have 5 sites running on a Windows Server Web Edition 2008 with SQL Server 2008 RC2 machine. Periodically on all sites (not all at the same time) I will get some errors in my ASP.NET 2 website(s) saying Index was outside the bounds of the array. on an SQL cursor. This is happening in places where data is definitely present and if it wasn't I would get a lot more errors. It's as if the SQL server can't return result for some reason.

I have no idea how to start debugging this, I don't seem to get any timeout errors. My questions would be:

1. How can I tell if the SQL server is under to much load? Can I enable something that allows me to track this?
2. If I have a date and time, am I able to check some logs on SQL Server to show more information? If so, where?

Sorry about the vague description; I don't have much more to go on at the moment.

Edit to the answers so far:

Most of the time, the same code will work without a hitch. I managed to replicate this today by creating an endless loop and perform an SQL query each time, I then tried to load a page on the same website and it brought back this error. My point is that cursors are being returned empty when there should be no possible way for them to be.

Edit2 Ok, maybe I wasn't explaining properly. I understand the out of bounds error is down to coding. What I am saying is that is happens randomly on the same code that works 99.9% of the time. Yes the code raises the error BUT it shouldn't have an empty result set in the first place. Here is the full stack trace (again, this code works 99.9% of the time):

(I am using Delphi.NET so this is where the Borland part has come from) - Please also note that the error is occurring when it's clearing down it's own list internally ...

Borland.Vcl.TDBBufferList.FreeHGlobal(IntPtr Ptr) at 
Borland.Vcl.TCustomADODataSet.FreeRecordBuffer(IntPtr& Buffer) at 
Borland.Vcl.TDataSet.SetBufListSize(Int32 Value) at 
Borland.Vcl.TDataSet.CloseCursor() at 
Borland.Vcl.TDataSet.SetActive(Boolean Value) at 
Borland.Vcl.TDataSet.Close() at 
WebCommon.TStockItemIntf.@13$Initialise$GetPLPrice(TStockItemIntf Self, String CCode, String SkCode, String PLStr) at 
WebCommon.TStockItemIntf.@23$Initialise$FinalPrice(TStockItemIntf Self, $Unnamed205& $frame_TStockItemIntf.Initialise, TKosCompany Company, TKosStock SkItem, TStockPriceCalcType PLCalcOption) at 
WebCommon.TStockItemIntf.Initialise(TKosStock SkItem, STOCK_ITEM SkCatItem, TAssignStockOptions AssignOptions, HttpSessionState Session, String DefaultPriceList) at 
WebCommon.TKosDropInProducerP.@68$AssignStockItem$AssignStockObjLst(TKosDropInProducerP Self, $Unnamed206& $frame_TKosDropInProducerP.AssignStockItem, TKosStock SkItem, String DefaultPriceList) at 
WebCommon.TKosDropInProducerP.AssignStockItem(TStringList StockCodeList, CATEGORY Category, HttpSessionState Session, TAssignStockOptions AssignOptions) at 
RecentlyViewedShortList.TRecentlyViewedShortList.Page_Load(Object sender, EventArgs e) at 
System.Web.UI.Control.OnLoad(EventArgs e) at 
System.Web.UI.Control.LoadRecursive() at 
System.Web.UI.Control.LoadRecursive() at 
System.Web.UI.Control.LoadRecursive() at 
System.Web.UI.Control.LoadRecursive() at 
System.Web.UI.Control.LoadRecursive() at 
System.Web.UI.Control.LoadRecursive() at 
System.Web.UI.Control.LoadRecursive() at 
System.Web.UI.Control.LoadRecursive() at 
System.Web.UI.Control.LoadRecursive() at 
System.Web.UI.Control.LoadRecursive() at 
System.Web.UI.Control.LoadRecursive() at 
System.Web.UI.Control.LoadRecursive() at 
System.Web.UI.Control.LoadRecursive() at 
System.Web.UI.Control.LoadRecursive() at 
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) 

Answer 1

As Martin pointed out, it's a .Net error.

You should check your code, it could be anything but I'll give an example of how this can happen in pseudo-code:

Item[] myItems = new Item[sql.queryshot("select count(*) form myTable")];
int counter=0;

Cursor MyCursor = GetData();
foreach(Data data in MyCursor){
  myItems[counter] = new Item(data);
  counter++;
}

If someone inserted a record between the initialization of the array Item[] and the GetData() method, you will see an Index out of bounds exception because you will pass the end of the array. This will not happen frequently though since there is very little time between the two operations.

If it were an overloaded server, you would certainly get other errors, not this one.

Answer 2

This error is from .NET and has nothing to do with SQL Server at all. Check your code to make sure your arrays are correctly defined. Remember that there is a 0 position in .NET arrays also. If you know you have 10 post to read from sql server and you dim the array to lenght 10 you have to use 0 - 9 NOT 1 -10 since the 10 would give you the above error. Hence not an sql server issue at all.

There are many ways to track down if your sql server is under too much load or in any other kind of trouble but it is not relevant for the issue you describe above. I recomend https://dba.stackexchange.com/ to learn and read more about that.

Answer 3

I have managed to find the cause of this problem (I think).

I can definitely rule out an SQL server being to busy because I have run a website on a separate instance of SQL server to see if it helped but the issue was still present.

Basically when I first wrote the database connection code of the site I thought it would be better to create and use one connection to the database per page load. This (as I now know) is not recommended because of SQL server's connection pooling which will handle the connections anyway.

When I use this connection, I think the TList that held the buffer of records was sometimes being changed at which point it would throw an out of range exception when I tried to access a result that was there but was removed / changed.

On this note, I have re-written my database connections and am now using native .NET connections instead of Delphi's crappy adaptations.