fill factors

How come most of the indexes created in MS SQL 2000 the
fill factor is 90 even though the default setting is 0?
Thanks,
LeighAre you running maintenance jobs generated by the maintenance plan =wizard? - it sets fillfactors to 90 by default.
Mike John
"Leigh Wilson" <anonymous@.discussions.microsoft.com> wrote in message =news:069901c3bbea$69d2cd30$a401280a@.phx.gbl...
> How come most of the indexes created in MS SQL 2000 the > fill factor is 90 even though the default setting is 0?
> > Thanks,
> Leigh|||Leigh,
From where did you get the 90% value? Sysindexes?
Perhaps the ones who created the indexes used some GUI tool (like EM
perhaps) which has a default value of 90%? Or someone re-configured the
default value with sp_configure?
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Leigh Wilson" <anonymous@.discussions.microsoft.com> wrote in message
news:069901c3bbea$69d2cd30$a401280a@.phx.gbl...
> How come most of the indexes created in MS SQL 2000 the
> fill factor is 90 even though the default setting is 0?
> Thanks,
> Leigh

Fill Factor settings

In 2005 Database settings....default fill factor 0

When this is 0 and and index rebuild is performed does that mean there is no space for the indexes to grew and issues a page split immediately.

Would it be better to adjust the fill factor to 5

Thanks

It all depends on how your database is used.
This Books Online 2005 topic discusses the options: Fill Factor

Fill Factor

I know default fillfactor is better. But can anyone
suggest me when it is good idea to change this. I have
some big tables. Several Mill rows.I like to fine tune it.
SQL 2000 and 7 both.
Thank you for your kind reply.
Aziz Karim wrote:
> I know default fillfactor is better. But can anyone
> suggest me when it is good idea to change this. I have
> some big tables. Several Mill rows.I like to fine tune it.
> SQL 2000 and 7 both.
> Thank you for your kind reply.
Well if you have a table with a clustered index and inserts or updates
to the table will likely cause page splits, then you leave some free
space on the pages by specifying a fill factor when you create the
index. The fill factor only applies when the index is created. It's not
something that is maintained by SQL Server. Having some free space can
help prevent excessive page splitting and reduced performance, but this
will require you maintain the free space on the pages by reindexing on a
regular basis. If the table is not highly transactional or if the
clustered key is unique on something like an identity column, then
specifying a fill factor may not be necessary. In fact, most times it's
not necessary. Adding extra pages to a table can slow multi-read
operations.
David G.
|||Aziz,
This is a fairly vauge question. There are no hard and fast rules. But in
general, you would want a lower fillfactor for tables that are going to get
a lot of inserts in-between existing data. A higher fillfactor will be
useful for tables with less insert activity and/or insert activity at the
end of the cluster (e.g. if you're clustering on an IDENTITY column or other
sequential value).
This article has more information that might help you:
http://www.sql-server-performance.co...ng_indexes.asp
"Aziz Karim" <goAziz@.yahoo.com> wrote in message
news:824701c48531$e2de7ce0$a501280a@.phx.gbl...
> I know default fillfactor is better. But can anyone
> suggest me when it is good idea to change this. I have
> some big tables. Several Mill rows.I like to fine tune it.
> SQL 2000 and 7 both.
> Thank you for your kind reply.

Fill Factor

I know default fillfactor is better. But can anyone
suggest me when it is good idea to change this. I have
some big tables. Several Mill rows.I like to fine tune it.
SQL 2000 and 7 both.
Thank you for your kind reply.Aziz Karim wrote:
> I know default fillfactor is better. But can anyone
> suggest me when it is good idea to change this. I have
> some big tables. Several Mill rows.I like to fine tune it.
> SQL 2000 and 7 both.
> Thank you for your kind reply.
Well if you have a table with a clustered index and inserts or updates
to the table will likely cause page splits, then you leave some free
space on the pages by specifying a fill factor when you create the
index. The fill factor only applies when the index is created. It's not
something that is maintained by SQL Server. Having some free space can
help prevent excessive page splitting and reduced performance, but this
will require you maintain the free space on the pages by reindexing on a
regular basis. If the table is not highly transactional or if the
clustered key is unique on something like an identity column, then
specifying a fill factor may not be necessary. In fact, most times it's
not necessary. Adding extra pages to a table can slow multi-read
operations.
--
David G.|||Aziz,
This is a fairly vauge question. There are no hard and fast rules. But in
general, you would want a lower fillfactor for tables that are going to get
a lot of inserts in-between existing data. A higher fillfactor will be
useful for tables with less insert activity and/or insert activity at the
end of the cluster (e.g. if you're clustering on an IDENTITY column or other
sequential value).
This article has more information that might help you:
http://www.sql-server-performance.com/rebuilding_indexes.asp
"Aziz Karim" <goAziz@.yahoo.com> wrote in message
news:824701c48531$e2de7ce0$a501280a@.phx.gbl...
> I know default fillfactor is better. But can anyone
> suggest me when it is good idea to change this. I have
> some big tables. Several Mill rows.I like to fine tune it.
> SQL 2000 and 7 both.
> Thank you for your kind reply.

Fill Factor

I know default fillfactor is better. But can anyone
suggest me when it is good idea to change this. I have
some big tables. Several Mill rows.I like to fine tune it.
SQL 2000 and 7 both.
Thank you for your kind reply.Aziz Karim wrote:
> I know default fillfactor is better. But can anyone
> suggest me when it is good idea to change this. I have
> some big tables. Several Mill rows.I like to fine tune it.
> SQL 2000 and 7 both.
> Thank you for your kind reply.
Well if you have a table with a clustered index and inserts or updates
to the table will likely cause page splits, then you leave some free
space on the pages by specifying a fill factor when you create the
index. The fill factor only applies when the index is created. It's not
something that is maintained by SQL Server. Having some free space can
help prevent excessive page splitting and reduced performance, but this
will require you maintain the free space on the pages by reindexing on a
regular basis. If the table is not highly transactional or if the
clustered key is unique on something like an identity column, then
specifying a fill factor may not be necessary. In fact, most times it's
not necessary. Adding extra pages to a table can slow multi-read
operations.
David G.|||Aziz,
This is a fairly vauge question. There are no hard and fast rules. But in
general, you would want a lower fillfactor for tables that are going to get
a lot of inserts in-between existing data. A higher fillfactor will be
useful for tables with less insert activity and/or insert activity at the
end of the cluster (e.g. if you're clustering on an IDENTITY column or other
sequential value).
This article has more information that might help you:
http://www.sql-server-performance.c...ing_indexes.asp
"Aziz Karim" <goAziz@.yahoo.com> wrote in message
news:824701c48531$e2de7ce0$a501280a@.phx.gbl...
> I know default fillfactor is better. But can anyone
> suggest me when it is good idea to change this. I have
> some big tables. Several Mill rows.I like to fine tune it.
> SQL 2000 and 7 both.
> Thank you for your kind reply.

Filegroups and BLOBs

Hi,
As books online mentions, BLOBs are not stored in row by default but they
are out of the row. I'm wondered that if they are separate from searchable
data(rows), how placing them into other filegroup can help performance?
Basically does it?
For example:
CREATE TABLE Table1 (
C1 int,
C2 ...
...
Logo Image) ON FG1 TextImage_On FG2
Is it recommended to keep BLOBs in other filegroup for performance?
Thanks,
Leila
In addition to what I mentioned in your other post placing data in separate
filegroups does nothing for performance in and of itself. If the filegroup
is on a separate drive array and the current one is overburdened it may
help. But only if you have I/O issues now.
Andrew J. Kelly SQL MVP
"Leila" <leilas@.hotpop.com> wrote in message
news:%23DEbknApEHA.2032@.TK2MSFTNGP10.phx.gbl...
> Hi,
> As books online mentions, BLOBs are not stored in row by default but they
> are out of the row. I'm wondered that if they are separate from searchable
> data(rows), how placing them into other filegroup can help performance?
> Basically does it?
> For example:
> CREATE TABLE Table1 (
> C1 int,
> C2 ...
> ...
> Logo Image) ON FG1 TextImage_On FG2
> Is it recommended to keep BLOBs in other filegroup for performance?
> Thanks,
> Leila
>
>

Filegroups and BLOBs

Hi,
As books online mentions, BLOBs are not stored in row by default but they
are out of the row. I'm wondered that if they are separate from searchable
data(rows), how placing them into other filegroup can help performance?
Basically does it?
For example:
CREATE TABLE Table1 (
C1 int,
C2 ...
...
Logo Image) ON FG1 TextImage_On FG2
Is it recommended to keep BLOBs in other filegroup for performance?
Thanks,
LeilaIn addition to what I mentioned in your other post placing data in separate
filegroups does nothing for performance in and of itself. If the filegroup
is on a separate drive array and the current one is overburdened it may
help. But only if you have I/O issues now.
--
Andrew J. Kelly SQL MVP
"Leila" <leilas@.hotpop.com> wrote in message
news:%23DEbknApEHA.2032@.TK2MSFTNGP10.phx.gbl...
> Hi,
> As books online mentions, BLOBs are not stored in row by default but they
> are out of the row. I'm wondered that if they are separate from searchable
> data(rows), how placing them into other filegroup can help performance?
> Basically does it?
> For example:
> CREATE TABLE Table1 (
> C1 int,
> C2 ...
> ...
> Logo Image) ON FG1 TextImage_On FG2
> Is it recommended to keep BLOBs in other filegroup for performance?
> Thanks,
> Leila
>
>

filegroup

Hi,

I have created a database with two filegroups called FG_GroupData, FG_GroupHistory.

FG_GroupData is set as default.

FG_GroupData contains two secondary data files i.e. GroupData1.ndf and GroupData2.ndf

I can create a table so that it is stored in FG_GroupHistory. i.e.

CREATE TABLE dbo.OrdersHistory
(
OrderID int NOT NULL,
ProductID int NOT NULL,
CustomerID int NOT NULL,
UnitPrice money NOT NULL,
OrderQty smallint NOT NULL
)
ON FG_GroupHistory

Questions:
1)
How do I add tables to each .ndf file inside a group i.e. FG_GroupData
For example, how do I add a table to GroupData1.ndf and one to GroupData2.ndf ?

2)
I guess there is no need to place the file name i.e. .ndf inside the select query

Thanks

You can only assign the tables to a filegroup not a specific file, this will all be managed by SQL Server.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

You cannot control where a table is placed on file level only on filegroup level.

The only option you have (if it is really necessary to separate the two tables) is to create a third filegroup.

Files within a filegroup get proportionally filled so if you add 2 files to the filegroup the tables will be spread over these 2 files.

*EDIT*

Oops too slow for Jens :-)

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

Filegroup

When a database is created, all system objects will be stored in this
default filegroup, and also the later user tables, right ?
So if I then create another filegroup as default filegroup, are all previous
user tables also move to this default filegroup ?No. (Re)-create the clustered index on a table to move it.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Alan" <NOSPAMalan_pltse@.yahoo.com.au> wrote in message
news:unj8VHgzEHA.3376@.TK2MSFTNGP12.phx.gbl...
> When a database is created, all system objects will be stored in this
> default filegroup, and also the later user tables, right ?
> So if I then create another filegroup as default filegroup, are all previous
> user tables also move to this default filegroup ?
>|||So how about:
When I create a databbase in EM, I also create secondary database file in
secondary file group in the dialog box.
Wiil all user tables be stored in the secondary database file in the
secondary file group ?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:Oyc10tgzEHA.2804@.TK2MSFTNGP15.phx.gbl...
> No. (Re)-create the clustered index on a table to move it.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Alan" <NOSPAMalan_pltse@.yahoo.com.au> wrote in message
> news:unj8VHgzEHA.3376@.TK2MSFTNGP12.phx.gbl...
> > When a database is created, all system objects will be stored in this
> > default filegroup, and also the later user tables, right ?
> > So if I then create another filegroup as default filegroup, are all
previous
> > user tables also move to this default filegroup ?
> >
> >
>|||Current or future tables? To have current tables move to the filegroup, (re) create the tables
clustered index (as I mentioned earlier). For future tables, either specify ON <FGNAME> when you
create the table or index, or make the file groups the default filegroups for the database (see the
ALTER DATABASE command).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Alan" <NOSPAMalan_pltse@.yahoo.com.au> wrote in message
news:uCcjB8B0EHA.3808@.TK2MSFTNGP15.phx.gbl...
> So how about:
> When I create a databbase in EM, I also create secondary database file in
> secondary file group in the dialog box.
> Wiil all user tables be stored in the secondary database file in the
> secondary file group ?
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:Oyc10tgzEHA.2804@.TK2MSFTNGP15.phx.gbl...
> > No. (Re)-create the clustered index on a table to move it.
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > "Alan" <NOSPAMalan_pltse@.yahoo.com.au> wrote in message
> > news:unj8VHgzEHA.3376@.TK2MSFTNGP12.phx.gbl...
> > > When a database is created, all system objects will be stored in this
> > > default filegroup, and also the later user tables, right ?
> > > So if I then create another filegroup as default filegroup, are all
> previous
> > > user tables also move to this default filegroup ?
> > >
> > >
> >
> >
>

FILE SIZE IN MODEL

Hi,
where Can I see the default size of new database in database MODEL ?

In which table ?

Saimon(Florence)Unless you specify otherwise, new databases will be created the same size as
model. You can query the model database properties with:

EXEC sp_helpdb 'model'

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Saimon" <saimon181072@.supereva.it> wrote in message
news:c53q0m$on9$1@.newsreader.mailgate.org...
> Hi,
> where Can I see the default size of new database in database MODEL ?
> In which table ?
> Saimon(Florence)

File Location Change

Hello, I was wondering how to change the default storage location for the
data and log files. I relaize that as I create new tables, I can override
th "default" location, but I was wanting to change the "default" value to
be the new location where most SQL databases will be stored. How do I
accomplish this?In SQL 2000 Right click on your Server in EM and choose
Properties. Select the Database settings tab and you will
see the input boxes for Default data and default log file location
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Jim Heavey" <JHeavey@.nospam.com> wrote in message
news:Xns949578AF4C7E7JHeaveyBDUP@.207.46.248.16...
> Hello, I was wondering how to change the default storage location for the
> data and log files. I relaize that as I create new tables, I can override
> th "default" location, but I was wanting to change the "default" value to
> be the new location where most SQL databases will be stored. How do I
> accomplish this?

File Location Change

Hello, I was wondering how to change the default storage location for the
data and log files. I relaize that as I create new tables, I can override
th "default" location, but I was wanting to change the "default" value to
be the new location where most SQL databases will be stored. How do I
accomplish this?In SQL 2000 Right click on your Server in EM and choose
Properties. Select the Database settings tab and you will
see the input boxes for Default data and default log file location
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Jim Heavey" <JHeavey@.nospam.com> wrote in message
news:Xns949578AF4C7E7JHeaveyBDUP@.207.46.248.16...
> Hello, I was wondering how to change the default storage location for the
> data and log files. I relaize that as I create new tables, I can override
> th "default" location, but I was wanting to change the "default" value to
> be the new location where most SQL databases will be stored. How do I
> accomplish this?