Hi Friends,
In this post I would like to share my experience with respect to time out Error from a website with SQL Server Database as Backend.
As you can see from the above picture the website is not able to come up with results and it popped up with Message Query timeout.
From the Message it is very clear that ExecuteReader(.NET method) is not able to fetch the results within the default connection timeout.
In general for any .NET Application the default time out is 30 seconds however in my case it has been setup with 15 seconds.
When I asked the developer he said they have 5 webpages related to Logging (Audit, Error, Trace, Event and Episode). They were able to obtain the results for all the web pages with in 2 seconds however for Trace log it took 17 seconds for its completion. As the timeout has been set for 15 seconds the page is getting timed out.
As a DBA what will be our next steps?
I started with initial checks on the tables(statistics and Fragmentation) and they all are performing good.
Next I started checking the code and the indexes for the Logging tables and from index standpoint they look one at the same.
when verified the code I identified the problem and it is happening because of type conversion from DATETIME2 to varchar.
The Datecreated column inside the table(TransactionTraceLOG) is having DATETIME2 where as in the code they are changing that to varchar(106)
SELECT
@PageCount = (COUNT([TransactionTraceLogId]) / @PageSize),
@PageMod = (COUNT([TransactionTraceLogId]) % @PageSize)
FROM [dbo].[TransactionTraceLog] WITH (NOLOCK)
WHERE
(
[Source] = @Source or
@Source = '%%'
)
AND
(
@Date IS NULL OR
--CONVERT(VARCHAR, @Date, 106) LIKE CONVERT(VARCHAR, [DateCreated], 106)
I told them to avoid conversion in where clause and asked them to pre-populate but the developer told me they are using multi search functionality and they have 3 parameters to search for like below.
exec [dbo].[Transactions.TransactionTraceLog.RetrievePageBySource_test] @Source = '%%', @Page = 100, @Date = '11/01/2020' WITH RECOMPILE;
When it comes to Dynamic search conditions there are so many things that we need to consider and it requires lot of efforts. http://www.sommarskog.se/dyn-search-2008.html this will help you to understand completely.
Now back to the problem as said above no issues with stats/Fragmentation/Indexes and the code definition is same for all other stored procedures.
I always never rule out row count of the tables when it comes to performance issues. In this case the problem lies here the table size and the row count for other tables are too small hence the results are lightening fast however when it comes to this table the size is 60 GB and as they can't change the code I told them to purge. As expected after purging the issue got resolved.
As said we can change the column definition from datetime to varchar but again this is an architectural decision.
Comments