July 15, 2015 at 5:15 am
Hi,
From last few days at times we are getting below errors:
1. Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
2. Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.
1. When server gets too slow, I found NumberOfConnections around 250 for the database which is having issue using below query :
SELECT DB_NAME(dbid) as DBName, COUNT(dbid) as NumberOfConnections, loginame as LoginName
FROM sys.sysprocesses
WHERE dbid > 0 GROUP BY dbid, loginame
2. Also, when slow server issue occurs, we run sp_who2, it shows "BlkBy" column with some ID
3. I also found that "Auto Close" property is "True" for this database, should I change it to "False" ?
4. We have a dedicated server with 8GB RAM and using SQL Server 2008 R2 Express edition. Should we upgrade it to Sql Server 2012 web edition instead of using express edition?
After these errors, we tried to optimize the major queries, also created separate app pool for heavy loading sites. But still issue is not fixed from last 15 days. How can we find root cause of this issue and fix it.
July 15, 2015 at 7:30 am
shirish_padekar (7/15/2015)
Hi,From last few days at times we are getting below errors:
1. Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
2. Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.
1. When server gets too slow, I found NumberOfConnections around 250 for the database which is having issue using below query :
SELECT DB_NAME(dbid) as DBName, COUNT(dbid) as NumberOfConnections, loginame as LoginName
FROM sys.sysprocesses
WHERE dbid > 0 GROUP BY dbid, loginame
2. Also, when slow server issue occurs, we run sp_who2, it shows "BlkBy" column with some ID
3. I also found that "Auto Close" property is "True" for this database, should I change it to "False" ?
4. We have a dedicated server with 8GB RAM and using SQL Server 2008 R2 Express edition. Should we upgrade it to Sql Server 2012 web edition instead of using express edition?
After these errors, we tried to optimize the major queries, also created separate app pool for heavy loading sites. But still issue is not fixed from last 15 days. How can we find root cause of this issue and fix it.
This is almost certainly an issue in an application that is not properly closing and disposing of connections. Check your applications and make sure that anytime a connection is used that it gets closed when it is no longer needed. A good place to start would be a new application or some new code that was released recently.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 15, 2015 at 7:47 am
Hi,
We are using asp.net and all our database related codes are enclosed inside using() which takes care of closing/disposing the connections after operation is done:
using (SqlConnection connection = new SqlConnection(connectionString))
{
// database operation
}
We are having 70+ sites on the server which use our CMS application. How can I check if errors are due to any coding issue or sql related queries or if we really need to increase the max pool size value ?
July 15, 2015 at 8:12 am
shirish_padekar (7/15/2015)
Hi,We are using asp.net and all our database related codes are enclosed inside using() which takes care of closing/disposing the connections after operation is done:
using (SqlConnection connection = new SqlConnection(connectionString))
{
// database operation
}
We are having 70+ sites on the server which use our CMS application. How can I check if errors are due to any coding issue or sql related queries or if we really need to increase the max pool size value ?
I would double check any recently released code to ensure that something didn't get missed. It is nearly impossible to offer any sound advice from here. I can't see what is going on and have no knowledge of anything about your system. My suggestions are based on the limited amount of information provided and my experience.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 15, 2015 at 8:20 am
shirish_padekar (7/15/2015)
3. I also found that "Auto Close" property is "True" for this database, should I change it to "False" ?
Yes. It's not related to your question, but it's a bad setting to be enabled in general.
4. We have a dedicated server with 8GB RAM and using SQL Server 2008 R2 Express edition.
That's a waste of memory, since SQL Express 2008 is limited to 1GB.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 15, 2015 at 11:07 am
Hi,
In Windows Task Manager sqlservr.exe process showing 1.6 GB memory usage. Not sure this is what I need to check ?
July 16, 2015 at 2:50 am
Don't use Task Manager to look at SQL's memory. It often lies.
In this case though it's probably right. 1GB for the buffer pool and 600MB for the non-buffer portion (thread stacks, backup buffers, etc)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply