AyaNova Sales & Technical Support

http://www.ayanova.com

The SQL Server Database Engine automatically maintains indexes whenever insert, update, or delete operations are made to the underlying data. Over time (including importing, etc) these modifications can cause the information in the index to become scattered in the database (fragmented). Fragmentation exists when indexes have pages in which the logical ordering, based on the key value, does not match the physical ordering inside the data file. Heavily fragmented indexes can degrade query performance and cause your application to respond slowly. Remedy this by reorganizing the index.

It has been determiend that if you run a Reindex / Reorganization on your database, timeout errors will be resolved and also can improve performance.

If using SQL Express, refer to this very useful topic by Jasper Smith at http://www.sqldbatips.com/showarticle.asp?ID=27 and  http://www.sqldbatips.com/showarticle.asp?ID=29

Basic steps are - refer to the links above for details:

• Download the Expressmaint utility from http://www.sqldbatips.com/showarticle.asp?ID=27 
• Extract to root of the C:\ of the SQL Express server
• Download the expressmaint code from http://www.sqldbatips.com/showarticle.asp?ID=29 
• Rename expressmaint.sql 
• Create a folder called C:\Reports
• From a DOS command at the C:\, type in the following :

sqlcmd -S .\SQLExpress -i c:\expressmaint.sql

• when it returns to the DOS command type in at C:\  DOS prompt:

expressmaint -S SQLExpress -D AyaNova -T REINDEX -R c:\reports -RU DAYS -RV 1

• This attaches to the server SQLExpress, on the database AyaNova, runs the REINDEX code, and puts output into the report file located in C:\Reports.

Example of timeout error that can occur printing detailed reports :

Unhandled Exception:
Exception has been thrown by the target of an invocation.
System.Reflection.TargetInvocationException
Stack Trace:
   at System.RuntimeMethodHandle._InvokeMethodFast(Object target, Object[] arguments, SignatureStruct& sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner)
   at System.RuntimeMethodHandle.InvokeMethodFast(Object target, Object[] arguments, Signature sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner)
   at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture, Boolean skipVisibilityChecks)
   at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
   at CSLA.Server.DataPortal.CallMethod(Object obj, String method, Object[] params)
   at CSLA.Server.DataPortal.Fetch(Object Criteria, DataPortalContext context)
   at CSLA.DataPortal.Fetch(Object Criteria)
   at GZTW.AyaNova.BLL.WorkorderQuoteDetailedReportData.GetItem(Guid WorkorderID)
   at AyaNova.WorkorderForm.(Object , ToolClickEventArgs )
   at Infragistics.Win.UltraWinToolbars.UltraToolbarsManager.OnToolClick(ToolClickEventArgs e)
   at Infragistics.Win.UltraWinToolbars.UltraToolbarsManager.FireEvent(ToolbarEventIds id, EventArgs e)
   at Infragistics.Win.UltraWinToolbars.ListTool.set_SelectedItemIndex(Int32 value)
   at Infragistics.Win.UltraWinToolbars.ListTool.DoDefaultActionForItem(Int32 itemIndex)
   at Infragistics.Win.UltraWinToolbars.ListToolMenuItem.OnClick()
   at Infragistics.Win.UltraWinToolbars.PopupMenuItemUIElement.DoClickProcessing(MouseEventArgs e)
   at Infragistics.Win.UltraWinToolbars.PopupMenuItemUIElement.OnMouseUp(MouseEventArgs e)
   at Infragistics.Win.TextUIElementBase.OnMouseUp(MouseEventArgs e)
   at Infragistics.Win.ControlUIElementBase.ProcessMouseUpHelper(Object sender, MouseEventArgs e)
   at Infragistics.Win.ControlUIElementBase.ProcessMouseUp(Object sender, MouseEventArgs e)
   at System.Windows.Forms.Control.OnMouseUp(MouseEventArgs e)
   at Infragistics.Win.UltraWinToolbars.PopupControlBase.OnMouseUp(MouseEventArgs e)
   at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
   at System.Windows.Forms.Control.WndProc(Message& m)
   at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
   at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
   at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
*** Exception has been thrown by the target of an invocation.
   at System.RuntimeMethodHandle._InvokeMethodFast(Object target, Object[] arguments, SignatureStruct& sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner)
   at System.RuntimeMethodHandle.InvokeMethodFast(Object target, Object[] arguments, Signature sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner)
   at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture, Boolean skipVisibilityChecks)
   at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
   at CSLA.Server.DataPortal.CallMethod(Object obj, String method, Object[] params)
   at CSLA.Server.DataPortal.Fetch(Object Criteria, DataPortalContext context)
   at CSLA.DataPortal.Fetch(Object Criteria)
   at GZTW.AyaNova.BLL.WorkorderQuoteDetailedReportData.GetItem(Guid WorkorderID)
   at AyaNova.WorkorderForm.(Object , ToolClickEventArgs )
   at Infragistics.Win.UltraWinToolbars.UltraToolbarsManager.OnToolClick(ToolClickEventArgs e)
   at Infragistics.Win.UltraWinToolbars.UltraToolbarsManager.FireEvent(ToolbarEventIds id, EventArgs e)
   at Infragistics.Win.UltraWinToolbars.ListTool.set_SelectedItemIndex(Int32 value)
   at Infragistics.Win.UltraWinToolbars.ListTool.DoDefaultActionForItem(Int32 itemIndex)
   at Infragistics.Win.UltraWinToolbars.ListToolMenuItem.OnClick()
   at Infragistics.Win.UltraWinToolbars.PopupMenuItemUIElement.DoClickProcessing(MouseEventArgs e)
   at Infragistics.Win.UltraWinToolbars.PopupMenuItemUIElement.OnMouseUp(MouseEventArgs e)
   at Infragistics.Win.TextUIElementBase.OnMouseUp(MouseEventArgs e)
   at Infragistics.Win.ControlUIElementBase.ProcessMouseUpHelper(Object sender, MouseEventArgs e)
   at Infragistics.Win.ControlUIElementBase.ProcessMouseUp(Object sender, MouseEventArgs e)
   at System.Windows.Forms.Control.OnMouseUp(MouseEventArgs e)
   at Infragistics.Win.UltraWinToolbars.PopupControlBase.OnMouseUp(MouseEventArgs e)
   at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
   at System.Windows.Forms.Control.WndProc(Message& m)
   at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
   at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
   at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
*** Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
   at System.Data.SqlClient.SqlDataReader.get_MetaData()
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
   at GZTW.Data.GZTWDatabase.DoExecuteReader(IDbCommand command, CommandBehavior cmdBehavior)
   at GZTW.Data.GZTWDatabase.ExecuteReader(DBCommandWrapper command)
   at GZTW.AyaNova.BLL.WorkorderQuoteDetailedReportData.DataPortal_Fetch(Object Criteria)

AyaNova Sales & Technical Support

http://www.ayanova.com