Solved

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

Posted on 2010-09-09
6
485 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
  • 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:
tedbilly 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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 

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:tedbilly
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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

The canonical version of this article is on my web site here: http://iconoun.com/articles/collisions/ A companion presentation is available here: http://iconoun.com/articles/collisions/Unicode_Presentation.pdf
We were having a lot of "Heartbeat Alerts" in our SCOM environment, now "Heartbeat" in a SCOM environment for those of you who might not be familiar with SCOM is a packet of data sent from the agent to the management server on a regular basis, basic…
Viewers will learn the different options available in the Backstage view in Excel 2013.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …

746 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now