Re: Fill Factor

Well the only way it would go back to 90 is if someone or something is reindexing with a different fill factor. Maybe if you set up a trace to see when the reindexing is occurring that may help.

i discovered that insert operation takes more than 30 sec when FillFactor
become 90%, but when i return the value to 0% the insert operation takes less then 1 sec.

I find it very hard to believe it is the fill factor itself for a couple of reasons. One is that 0 means fill the leaf page 100% where as 90 means 90% full. The 90 will leave room for page splits where as the 0 does not. But in any case you only have 1 column of INT datatype on the pages since the image is kept on different pages. My guess is there is something else going on. Is this 2005? If so why are you using an Image type? Does your log file have plenty of free space in it? Are your log files on a Raid 1 or Raid 10 by themselves?

--
Andrew J. Kelly SQL MVP
Solid Quality Mentors


"Saeed Abd Al-Ati" <SaeedAbdAlAti@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:CAC0D1C0-875C-40A3-94F4-72D8F543BB71@xxxxxxxxxxxxxxxx
The case is: I have a table with two fields, ID as Int, Doc as Image datatype,
the second field is used to store images of type "tiff".

i discovered that insert operation takes more than 30 sec when FillFactor
become 90%, but when i return the value to 0% the insert operation takes less
then 1 sec.

the table size around 50,000 rows.

Note: there is no maintaince plan againts this table.

"Andrew J. Kelly" wrote:

My guess would be that someone set up a maintenance plan that reindexes the
tables and has the fill factor set to 90 there. But why would this cause
problems? I would think it would eliminate more problems than it would
cause. Having the fill factor at 0 means that each leaf level is essentially
100% full and any additional insert or update with more bytes than before
will cause a page split. With a Fill Factor of 90 you at least have some
room to operate and still minimize page splits.

--
Andrew J. Kelly SQL MVP
Solid Quality Mentors


"Saeed Abd Al-Ati" <SaeedAbdAlAti@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
message news:92FA67B7-FAAC-4F74-9F3D-4F4CEEB81DF4@xxxxxxxxxxxxxxxx
> Hi,
>
> I am facing a Weird problem in configuring the Fill Factor for my
> production
> database, i deployed a database to a client and sit the Fill Factor for
> all
> indexes to be "0" which is the default value, after 1 week the value
> changed
> to be "90" which caused some problems in the application, since there > are
> some problems in HD allocations, my question is: how this value changed
> automatically? taking into consideration that there is no specific
> devleopment/administration script applied on the Database.
>
> Thanks in advance.



.

Relevant Pages

  • Re: Performance really poor if calling data from sql clients
    ... Andrew J. Kelly SQL MVP ... The TCP connection is running from the client ... On a computer running the test which is not the database hosting ...
    (microsoft.public.sqlserver.clients)
  • Re: Performance really poor if calling data from sql clients
    ... Andrew J. Kelly SQL MVP ... The TCP connection is running from the client ... On a computer running the test which is not the database hosting ...
    (microsoft.public.sqlserver.clients)
  • Re: DataBase
    ... Andrew J. Kelly SQL MVP "Andrew J. Kelly" wrote in message ... Sorry I don't know anything about> dbase being supported with windows other than there are odbc and possibly> oledb drivers for it now, ... What I actually need to>> know>>>> is it posible to create this as a database and create tables in it, ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: different environment fails to fill a dataset
    ... Are you sure that the login on the Test system has the database Planit as ... Andrew J. Kelly SQL MVP ... on the test workstation but not my development station. ...
    (microsoft.public.sqlserver.clients)
  • Re: how to shrink tran-logs of databases
    ... Add on to Andrews detailed description, please shrink the MDF file in Single ... Alter database set single_user with rollback immediate ... > database to operate properly anyway for things such as reindexing and you ... > Andrew J. Kelly SQL MVP ...
    (microsoft.public.sqlserver.tools)