[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 7156 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 365 days
Recommended Popular Threads Controvertial Threads
TPS Re-registration case still pending ..
TPS Re-registration
What are your first memories of when Nepal Television Began?
निगुरो थाहा छ ??
ChatSansar.com Naya Nepal Chat
Basnet or Basnyat ??
Sajha has turned into MAGATs nest
NRN card pros and cons?
Toilet paper or water?
TPS EAD auto extended to June 2025 or just TPS?
Biden out, Trump next president, so what’s gonna happen to TPS, termination?
Nas and The Bokas: Coming to a Night Club near you
and it begins - on Day 1 Trump will begin operations to deport millions of undocumented immigrants
मन भित्र को पत्रै पत्र!
Will MAGA really start shooting people?
Democrats are so sure Trump will win
Tourist Visa - Seeking Suggestions and Guidance
From Trump “I will revoke TPS, and deport them back to their country.”
Anybody gotten the TPS EAD extension alert notice (i797) thing? online or via post?
Top 10 Anti-vaxxers Who Got Owned by COVID
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