Solved

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

Posted on 2010-09-09
6
495 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
What Is Transaction Monitoring and who needs it?

Synthetic Transaction Monitoring that you need for the day to day, which ensures your business website keeps running optimally, and that there is no downtime to impact your customer experience.

 

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

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…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
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 …

691 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