August 26, 2004 at 1:12 pm
We have an 8CPU 2.5 Ghz processor machine with about 20 databases ranging in size between 2 gig and 30 gig. Our performance for certain applications seems to be poor but at inconsistent times. There are on average about 400 live connections to the database. Is 400 connection activley hitting this database to much. I ran some counters on the database and they didn't look to bad.
This is happening mid month. During month end, the activity is a lot worse.
Any suggestions on what I can do to make performance better?
August 26, 2004 at 1:46 pm
I think a little more information is probably needed. I personally don't know if 400 active connections is a problem but I would guess it wouldn't be so long as you had a decent amount of RAM available to support them. I'm guessing there.
What is your metric for "poor" performance? Is it end application response time, web server response time, or SQL statement response time? The first two might not be SQL Server at all but CPU spikes on the web server or network congestion either internal or external to your company. Keep in mind network issues between the SQL server and web server can impact observed times despite both machines running fine.
If it is isolated to SQL Server people here can help. Other than performing general maintanence to defragment indexes and tables (assuming clustered) and similar stuff you need to know if there are certain queries causing most of the problem. SQL Profiler scanning for long running statements can identify them for you. Once you know which Stored Procedures are causing problems you can analyze them for just plain bad SQL or needed index changes and what have you. Anyhow, I'm starting to ramble so I'll quit.
August 26, 2004 at 2:09 pm
Our application is a think client bought from a vendor and we don't have much control over it. I am sometimes timing out in QA with a query to a table that has 200 records in it. So I think it is probably related to the amount of pressure on the server. I can watch for problem queries in Profiler. Our network team assures us that it is not network related, so I had been trying to rule that out.
I was mainly interested to know if 400+ connections is a major problem, or if giving the proper amount of memory it was alright
August 26, 2004 at 2:29 pm
More connections need more memory but 400 is not that large. SQL Server with your hardware configuration should be able to handle more connections.
You have to watch server's perform from CPUs, Memory to I/O etc to try to identify where is the bottleneck. If SQL Server process is possible cause, you then need to run Profiler to trace which statements and SPs running slow and to improve the performance by adjusting the indexes and performing statistics update regularly.
If there are not resources issues, you have to track whether there are blocking or deadlocks generated from your applications in SQL Server.
Even though you don't have control to the change of application, you can still prove to your verdor where is problem, for example, user transactions has been blocked when concurrent users use the application, and request them to look into and make necessary changes to resolve the problem.
If you can, try to describe more in details about your issues with system performance metrics.
August 27, 2004 at 8:20 am
With 400 connections, have you checked to see what your maximum worker threads is set at? I think out of the box it is set at 255. You might want to bump it up to 500. And you can try to investigate if using NT fibers might help. We have been checking on those exact things at my place but I don't have the number of users that you do.
Just a thought.
--Lori
August 27, 2004 at 8:25 am
I was wondering if the max worker thread setting would help. I had read that if you have more than 255 connections that you could bump it up. But I have also read that you shouldn't really touch that setting.
Is this something that I should be moving. Has anyone else moved this setting before. It is hard to find good documentation on when to move it or when to leave it.
August 27, 2004 at 9:40 am
There is some info at the bottom of this link that may help you: http://www.devarticles.com/c/a/SQL-Server/How-to-Perform-a-SQL-Server-Performance-Audit/1/
Use PerfMon to determine how many threads are being used and bump them up if needed.
August 27, 2004 at 2:12 pm
There are monitoring tools that might help you. I would think if you've got an 8-cpu class server you might find a coupla grand in the budget to help run it.
A tool I use is Spotlight on SQL Server from Quest Software. There are other tools, some undoubtedly more powerful, but this one fit our budget. A short description of what it (or other tools like it) can do wouldn't do it justice, so get the free 60-day trial version and take a look.
August 27, 2004 at 2:15 pm
Thanks for the suggestion. I will definatley check that out.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply