[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 7153 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
TPS Re-registration case still pending ..
Toilet paper or water?
and it begins - on Day 1 Trump will begin operations to deport millions of undocumented immigrants
Tourist Visa - Seeking Suggestions and Guidance
From Trump “I will revoke TPS, and deport them back to their country.”
advanced parole
ढ्याउ गर्दा दसैँको खसी गनाउच
To Sajha admin
MAGA denaturalization proposal!!
How to Retrieve a Copy of Domestic Violence Complaint???
wanna be ruled by stupid or an Idiot ?
Travel Document for TPS (approved)
All the Qatar ailines from Nepal canceled to USA
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