April 16, 2008 at 11:38 pm
We are using SQL Server 2005 Enterprise edition on our prodution server and we are calling stored procedures from Ado.net code written in C# (.net framework 1.0). Two days back we were facing serious performance issues in on of our application area, mean while we got some exceptions in application stating Index out of bound in the dataset.
The Stored Procedure contains only one select statement that returns two colums
like SELECT A, B FROM myTable
the application connect to data base through Ado.net and prepar command to call the stored proceudre, fill the data in a database (result) and then the code is like this
If ( result != null and result.Tables.count >1)
{
foreach(DataRow r in result.Tables[0].rows)
{
value1 = r[0];
value2 = r[1]; // on this like i am getting exception Index out of bound
}
}
Which gives me an exception Index out of bound, which means it is unable to find the second colum. the code was working we didn't change any thing so far but we start getting this exception. we were able to reproduce the exception at that time but it was just for few minuts. After that the same code is working fine!!!!
On safe side I have restarted the sql server and it is working fine with out any change now but still I want to know if some one knows the exact reason of this isse. I don't know if it was sql problem or it was Ado.Net!!
(I think it was sql crash some how as I was getting one error in past like this, my customer complained me there data has been changed by someone else, which was not possible. When I looked at the data in the colum , it was some thing like "Deadlock vicktom process id ...." but I don't remember it exactlly as 1 year passed now. thats why I was thinking this issue could be also from sql server)
Please help me in finding out the exact reason if any one knows about such problems.
April 17, 2008 at 5:50 am
It is going to be very difficult to determine the exact cause now that you cannot reproduce the behavior.
Stored procedures cache execution plans. If it had cached an execution plan that was dependant on a particular index and the index was changed or removed, the cached plan may have been failing. If it happens again, executing the procedure from Enterprise Manager to see it's results and capturing the execution plan would help you determine the actual cause. I would also flush the procedure cache before restarting the server to see if that is the issue.
Even though you are not having problems right now, it would be a good idea to use DBCC commands to check the integrity of the tables and indexes that the procedure uses and when the weekend comes around, I would do an integrity check of the entire database just to be safe.
April 17, 2008 at 10:35 pm
Thanks Michael for these suggestions, I will try to find out by verifying the db as you mention.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply