Solved

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

Posted on 2010-09-09
6
488 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:
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
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.

 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

User Beware!  This is a rather permanent solution to removing your email from an exchange server.  The only way to truly go back is to have your exchange administrator restore your mailbox from backups.  This is usually the option of last resort.  A…
This collection of functions covers all the normal rounding methods of just about any numeric value.
Viewers will learn how to maximize accessibility options in an Excel workbook for users with accessibility issues.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…

911 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

23 Experts available now in Live!

Get 1:1 Help Now