Solved

using performance monitor to determine fill Factor for indexes. Read/write ratio

Posted on 2010-09-09
6
491 Views
Last Modified: 2012-05-10
I am trying to determine fill factor for my indexes. To do that I think the best idea is to use windows performance moniter to moniter the amount of reads and amount of writes. I have set up 2 counters:
'Disk Reads/sec' and 'Disk Writes/sec' from 'Physical Disk' performance object. I am running all this in a virtual box so that my sql server is not connected to any of the client softwares. So the only 'read' that will happen is when i run a query. My question is what about the writes?

Reason i ask is, when i look at the graphs i see there are some perks on the graphs(for writes) even if i dont do anything! what is the reason for that. Why is there an activity on 'writes' on my physical disk even if i dont do anything. When I run a query(ie read), there is a tweak on the read graph which makes sense to me.

Is this the best way to determine my write/read ratios for my fill factor for indexes?
0
Comment
Question by:itbossman
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 

Author Comment

by:itbossman
ID: 33646371
It is running in 2005 sql server in 2000 windows server
0
 
LVL 51

Accepted Solution

by:
Ted Bouskill earned 125 total points
ID: 33652050
Why aren't you querying the database directly to get the level of fragmentation?
http://www.mssqltips.com/tip.asp?tip=1708

Plus Microsoft provides some great free reports for analyzing performance issues;
http://www.microsoft.com/downloads/en/details.aspx?FamilyId=1d3a4a0d-7e0c-4730-8204-e419218c1efc&displaylang=en
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 33653439
If copying and pasting some code and then executing from the query window is too much work for you, consider displaying the Index Physical Statistics Standard Report.  You get there by using the Object Explorer in SSMS, right clicking the database and selecting Standard Reports and then choosing Index Physical Statistics.
0
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 

Author Comment

by:itbossman
ID: 33696492
tedbilly, I have a script i run to find the level of fragmentation which i can rebuild my indexes. My question is how to determine the correct fill factor for my indexes. To better predict that I need to analyze the read/write ratio. right now all the fillfactors are defaulted to 0 which should only be the case for read only tables. There are lots of updates/inserts going on our databases daily.

Is there a tool to determine fill factor? I will look into your second link if I can get anyting out of that.

acperkins, when i right click on database there is no 'Standard Reports' as a selection.

thanks for feedback
0
 
LVL 51

Expert Comment

by:Ted Bouskill
ID: 33697485
Predicting a good fill factor requires data gathered over time.

So, you'll have to watch the database and record the rate of fragmentation.  I'd rebuild the indexes at a time when the database is not in use, then every day record the fragmentation percentage.  Then if you knew that realistically you could rebuild the indexes every week, total the fragmentation for the week and set the fill factor to cover that percentage with some additional space for anomalies.

If you can rebuild the indexes daily, then you could use a smaller factor.
0
 

Author Closing Comment

by:itbossman
ID: 33704615
thanks, tedbilly i installed the performance moniter dashboard. It is a great tool
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Lync meeting or Lync conferencing is what many organizations would like to deploy to allow them save money. But companies are now giving up for various reasons, one of which is that they cannot join external meetings (non-federated company meetings)…
This collection of functions covers all the normal rounding methods of just about any numeric value.
Viewers will learn the different options available in the Backstage view in Excel 2013.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

732 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question