[Show all top banners]

bhikari
Replies to this thread:

More by bhikari
What people are reading
Subscribers
Subscribers
[Total Subscribers 1]

pokhreli_kancho
:: Subscribe
Back to: Kurakani General Refresh page to view new replies
 how to determine correct fill factor for sql server using performance moniter
[VIEWED 6065 TIMES]
SAVE! for ease of future access.
Posted on 09-20-10 10:28 PM     Reply [Subscribe]
Login in to Rate this Post:     0       ?    
 

Hi I am new to dba. I am analyzing page splits and disk IO to determine optimal fill factor for my indexes. I am using performance moniter to find page splits but I also need to look at disk IO to determine the correct fill factor. Does disk IO basicly tell me read/write ratio correct?

how do i determine that? do i use performance moniter for that? if so what counters should i be using and how do i read the data. 

Also I have installed sql server performance dashboard report but I dont know how i can use that for my advantage. Your help is appreciated.

thanks.

 
Posted on 09-21-10 2:20 PM     [Snapshot: 67]     Reply [Subscribe]
Login in to Rate this Post:     0       ?    
 

Not sure if this is quite a venue to discuss this but since there are few tech gurus, I will bump ur thread.
 
Posted on 09-21-10 3:59 PM     [Snapshot: 83]     Reply [Subscribe]
Login in to Rate this Post:     0       ?    
 

Following basic rules can be applied:

Low Update Tables (100-1 read to write ratio): 100% fillfactor
High Update Tables (where writes exceed reads): 50%-70% fillfactor
Everything In-Between: 80%-90% fillfactor.
It is very important to do some experiment with your particular application to find the optimum fillfactor. While page splits will be reduced with a low fillfactor, it also increases the number of pages that have to be read by SQL Server during queries, which reduces performance. Not only  is I/O overhead increased with a too low of fillfactor, it also affects your buffer cache. As data pages are moved in from disk to the buffer, the entire page is moved to the buffer. So the lower the fillfactor, the more pages that have to be moved into SQL Serve's buffer, which means there is less room for other important data pages to reside at the same time, which can reduce performance. If you don't specify a fillfactor, the default fillfactor is 0, which means the same as a 100% fillfactor, (the leaf pages of the index are filled 100%, but there is some room left on intermediate index pages).

Understanding SQL Server Architecture really helps in tuning the SQL Server queries. 

I would recommend using DBCC commands, profiler and DMVs for finding out the queries bottlenecks.

Last edited: 21-Sep-10 04:02 PM
Last edited: 21-Sep-10 04:03 PM

 
Posted on 09-24-10 11:43 AM     [Snapshot: 163]     Reply [Subscribe]
Login in to Rate this Post:     0       ?    
 

what is this I/O overhead thing. As I understand if I have high I/O overhead it affects my reads correct?


I am using dbcc showcontig to find my fragmentations and doing reindexing. If I do frequent re-indexing, then it will reorganize my pagesplits correct?..

Another questions, if i have too many pagesplits then what happens? It affects my reads or writes? Seems like more pagesplits (higher fill factor)=good for reads. Less Page Splits (less fill factor, more I/O overhead)=good for writes. Please help me understand this concept.


 
Posted on 09-24-10 11:52 AM     [Snapshot: 167]     Reply [Subscribe]
Login in to Rate this Post:     0       ?    
 

if i am not getting many pagesplits, do i need to lower to fillfactor? 
 


Please Log in! to be able to reply! If you don't have a login, please register here.

YOU CAN ALSO



IN ORDER TO POST!




Within last 60 days
Recommended Popular Threads Controvertial Threads
डीभी परेन भने खुसि हुनु होस् ! अमेरिकामाधेरै का श्रीमती अर्कैसँग पोइला गएका छन् !
शीर्षक जे पनि हुन सक्छ।
What are your first memories of when Nepal Television Began?
Sajha Poll: नेपालका सबैभन्दा आकर्षक महिला को हुन्?
NRN card pros and cons?
Basnet or Basnyat ??
निगुरो थाहा छ ??
Nas and The Bokas: Coming to a Night Club near you
अमेरिकामा छोरा हराएको सूचना
Breathe in. Breathe out.
nrn citizenship
Top 10 Anti-vaxxers Who Got Owned by COVID
Doctors dying suddenly or unexpectedly since the rollout of COVID-19 vaccines
ढ्याउ गर्दा दसैँको खसी गनाउच
Sajha has turned into MAGATs nest
Send Parcels from USA to Nepal & Worldwide.
Why is every youths leaving Nepal? Why are not youths entering politics and serving the country, starting business etc?
if you are in USA illegally and ask for asylum it is legal
TPS Work Permit/How long your took?
महँगो अण्डाको पिकल्प : कुखुरा र खोर भाडामा लिने
Nas and The Bokas: Coming to a Night Club near you
NOTE: The opinions here represent the opinions of the individual posters, and not of Sajha.com. It is not possible for sajha.com to monitor all the postings, since sajha.com merely seeks to provide a cyber location for discussing ideas and concerns related to Nepal and the Nepalis. Please send an email to admin@sajha.com using a valid email address if you want any posting to be considered for deletion. Your request will be handled on a one to one basis. Sajha.com is a service please don't abuse it. - Thanks.

Sajha.com Privacy Policy

Like us in Facebook!

↑ Back to Top
free counters