June 21, 2006 at 8:39 am
ok so our server is SQL 2k with 4GB of Ram and we will be eventually moving to 64 bit 2005 with 8 GB of RAM but that is still a month away.
we have been seeing some random timeouts on random web pages. the query will run for normally 2 sec or less but then suddenly will take over 1 minute. no blocking no locks reported. pageIOlatch_sh wait types and some cxpacket's .
the error we are seeing is:
Exception of type System.Web.HttpUnhandledException was thrown. Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. System.Web .Net SqlClient Data Provider at System.Web.UI.Page.HandleError(Exception e) at System.Web.UI.Page.ProcessRequestMain() at System.Web.UI.Page.ProcessRequest() at System.Web.UI.Page.ProcessRequest(HttpContext context) at System.Web.CallHandlerExecutionStep.System.Web.HttpApplication+IExecutionStep.Execute() at System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior) at System.Data.SqlClient.SqlCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) at System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable) at common.ProprietaryNAME.BindSQL() at ProprietaryNAMENum2.ourWebpageName.Page_Load(Object sender, EventArgs e) at System.Web.UI.Control.OnLoad(EventArgs e) at System.Web.UI.Control.LoadRecursive() at System.Web.UI.Page.ProcessRequestMain()
Could this be disk IO subsystem queuing? I did not build this server ( new DBA at this company).
I have noticed some disk queue lengths that are a bit large but they are not consistent.
the database and the logs are on the same physical and logical partition which I cannot do anything about
until we move to 2005 ( new server with the correct set up)
thoughts? Questtions? any help would be appreciated!
June 21, 2006 at 10:55 am
A great start is to have a look at http://www.sql-server-performance.com/sql_server_performance_audit.asp
about how to do a sql server performance audit.
Here is a list of all the waittypes:
http://www.sqldev.net/misc/waittypes.htm
Since it is pageIOlatch_sh indicates IO and CXPACKET cpu
June 22, 2006 at 2:06 pm
yup I have confirmed we are seeing a IO performance hit.
disk read time on our D drive ( where the data files are) is very high and also accounts for 90 percent of all disk queue length numbers higher than 2.
I have created another database file inthe hopes that it will give me a small boost for reads. I dont have any other disk drives to move data files to.
June 22, 2006 at 3:27 pm
You could also try sp_updatestats on your databases (updating statistics)
A query plan on a very large table changed from a clustered index scan to an index seek
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply