August 1, 2008 at 1:30 pm
More curious than anything and I'd rather be proactive then wait for problem. I have a 2 node x64 cluster of Windows Server 2003 with SQL Server 2005 x64 Enterprise, CU6 with 64GB of Ram (55GB explicityly set for the buffer pool). OLTP style environment with 200 (with peaks up to 800) concurrent users. The SQLServer:General Statistics >> User Connections also show 200 to 250 user connections (and logical). The application is web/java based with heavy reporting functionality as well. The vendor tells me that in their busiest environment, they never exceed 60-70 DB user connections. My company has some custom C# addons that contain some poorly written old code mixed with new. My question(s) are - Why is it so much higher? Does 64bit SS allow different algorythms to choose whether to spawn an new connection or to pool an existing one? So it'll be higher? The Server is dedicated to SS and the CPU rarely rises above 20%. no locks/blocks, pages/sec is almost flatlined, page splits/sec is almost flatlined. basically all statistics indicate that the server is barely working. Yet user connections are higher. So far I'm not worried and I think its related to the poorly written code that keeps db connections open longer and poor queries that take a long time to finish. Am I barking up the right tree? is the number of db connections within norms? What else should I be looking at?
Extra information:
Apache Tomcat is hooked to 9 front end servers (x86) that serve up the vendor's application. Not sure how to check Tomcat. Then for the C# custom applicatoin we have a single separate box (x86) that handles reporting services stuff. So when I check IIS on our RS box, it only giving me a partial picture. Anyhow, on the RS Server, the IIS service has 8 worker processes. I set that according to how many cores the DB server has and not how many the stand alone RS box has. Mem usage ranges from 88MB to 490MB. That RS server is barely working. That RS server has 8GB of Ram and IIS is using less than 1GB as written above.
Maybe its fine and within limits? maybe not? Suggestions?
August 5, 2008 at 3:38 pm
It sounds like your server is running okay. Each connection is going to tie up some amount of memory, so more connections more memory tied up. Pooling would free up some of that. 64 bit large memory may not be anything to worry about.
Anybody else have any ideas for this fine young gentleman to think about?
I've been telling him how sharp the folks at sql server central are and how helpful they have been for me. 😉
Thanks,
Mark
Mark Johnson
MCP, MCTS Sql Server 2005,MCTS Sql Server 2008, OCP
May 12, 2009 at 9:56 am
Hi there,
I have a very similar configuration and also have "high" values (ranging 400 - 1200) for User Connections in my production environment.
Brad McGee discusses this counter in more detail on his post at http://www.devarticles.com/c/a/SQL-Server/How-to-Perform-a-SQL-Server-Performance-Audit/5/
I would also recommend reading more about Max Worker Threads and OS schedulers in MS BOL.
I haven't experimented with changing Max Worker Threads yet to see if it would improve (or hurt) performance. Please post if you find a good answer to your question.
Regards,
-mike
May 13, 2009 at 3:00 am
'Too many connections' is an issue that could affect 32-bit SQL Server, but is far less likely to affect 64-bit SQL Server.
With 32-bit, SQL Server has to fit a great deal into memory below the 4GB line. If you have maxed out on AWE memory, SQL has to fit everything bar the buffer pool in about 1.7 GB. If enough people try to connect, SQL will eventually reject connections because it cannot fit everything within that 1.7 GB. Adding extra memory will not help, because this does not add anything extra to the memory below the 4GB line.
In 64-bit SQL Server, any of SQL Server's memory pools can exist anywhere in memory. It is still true that if you have enough connections you may restrict the memory that SQL Server can use for other purposes, but the bar is far higher on 64-bit. You can also easily mitigate the problem by adding extra memory to the box, providing you have spaer memory slots.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
May 13, 2009 at 3:10 am
It could be because the connection once opened is not closed properly? Something to look at. You can get a rough idea by doing a perfmon counter on Login vs Logout ratio to see if that is the issue.
-Roy
October 21, 2011 at 10:47 am
Hi Roy,
I really appreciate your comment. It give me new ways to resolve one issue in my server.
Thanks,
Julián Castiblanco
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply