Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
dgivoni
Starting Member
1 Post |
 Posted - 2009-06-17 : 05:10:30
|
| Hi,First of all I'm not a DBA, so forgive me if I ask ignorant questions.The issue is that we have a SQL Server 2005 running on it's own machine behind two load balanced web servers and we're beginning to experience some performance problems with the database, that we are not quite sure how to interpret or solve.One issue is that apparently one of the background processes that we have running is "capable" of creating general database request timeouts for ALL databases on the server. This is something I find curious, since I can understand that running a query that updates a lot of data would lock out and time out other requests trying to access this data at the same time, but why would it affect other independent databases? (We're not maxing out either CPU or RAM) Is there a way of setting each database to only use a % of the database ressources, so that this can be avoided?According to our datacenter the normal first bottleneck to run into is the I/O related to the harddisk. We've been running performance counters on this and it doesn't look extreme, but I would like to know how you would determine when the time is to upgrade harddisks, cluster the database server etc. I mean which indicators would you look at and which values would you take as signs of "something has to happen"?From what we can see the activity level is on average almost 2000 batch requests/sec. Does that bring any red flags up?Well, I hope you can share your experience with this.Thanks in advance,David |
|
|
mvanwyk
Yak Posting Veteran
99 Posts |
Posted - 2009-06-17 : 06:08:37
|
| Hi Check this out might solve the problem.(You can find the Page Split counter under the SQLServer:Access Methods in Performance Monitor.)Tips for Using SQL Server Performance Monitor CountersBy : Brad McGeheeAug 24, 2005 One cause of excess I/O on a SQL Server is page splitting. Page splitting occurs when an index or data page becomes full, and then is split between the current page and a newly allocated page. While occasional page splitting is normal, excess page splitting can cause excessive disk I/O and contribute to slow performance. If you want to find out if your SQL Server is experiencing a large number of page splits, monitor the SQL Server Access Methods object: Page Splits/sec. If you find out that the number of page splits is high, consider increasing the fill factor of your indexes. An increased fill factor helps to reduce page splits because there is more room in data pages before it fills up and a page split has to occur.What is a high Page Splits/sec? There is no simple answer, as it somewhat depends on your system's I/O subsystem. But if you are having disk I/O performance problems on a regular basis, and this counter is over 100 on a regular basis, then you might want to experiment with increasing the fill factor to see if it helps or not. [6.5, 7.0, 2000] Updated 9-4-2006***** |
 |
|
|
|
|
|
|
|
performance / bottleneck questions