SQL 2012 :: Re-Indexed With Wrong Fill Factor And Now All Databases Are Huge
Jan 24, 2015
As the title says I re-indexed all of my databases using the wrong fill factor. Instead of using 90% as the fill factor I misunderstood and set this at 10%. So I believe my databases are now packed with a ton of unused space. The DB sizes should be about 5-6 GB but have since grown to 20-40GB. I am very new to SQL administration and don't know of a safe way to remove this unused space so that my databases return to their normal sizes. The databases do not grow very much at all so the free space is not really that necessary.
View 9 Replies
ADVERTISEMENT
Apr 3, 2001
Currently we have tables (in sql 6,5), many of them do not have primary keys.
While I was trying to re-index (re-org), many of them got an error:
"fillfactor 204 is not a valid percentage; fillfactor must be between 1 and 100."
(many tables' fillfactor exceed 100 or more...)
How can I fix them so I can upgrade to sql 7 ?
Thank you for your help.
View 3 Replies
View Related
Aug 13, 2001
I am really confused about this whole fill factor thing. The way I understand it, is if you have a table whose data remains pretty much static, you should use a higher fill factor. Suppose you had a database where you had at most 150 transactions a day that changed the data, should the fill factor be left at the default(0) or increased? How do you determine how much to increase it? Is there a rule of thumb that suggests if you have x number of changes against a table, you should have a fill factor between y and z percent?
Please Help
Chris
View 2 Replies
View Related
Mar 12, 2001
Hi all,
While creating indexes for a table, I specified a fill factor of 70%. I then inserted a few hundred rows into the table. Is it possible to check to what percent the pages are full after the rows have been inserted?
Thanks in advance,
Praveena
View 1 Replies
View Related
Jul 16, 1999
You have a db with 50,000 records and you want to add 100,000 more. What should the right fill factor be? Is there a way to "calculate" a fill factor if you don't want to use default? Any help is appreciated. Thank you.
View 3 Replies
View Related
Jan 18, 2005
If fill factor is specified as 100 for a table what will be the impact of this?I want to know any updation or insertion will be possible or not?
View 3 Replies
View Related
Aug 27, 2007
Hellow, everyone"
I have a web online table that is inserted about 1500 record one day. Each night, a DST is running to pull all data to anther database. How to set fill factor on a one column index to get the best performance? Current fill factor is 80%.
Thanks
ZYT
View 7 Replies
View Related
Jul 13, 2007
Hi experts, I would like to ask regarding FILL FACTOR. I observed that our system's loading is a bit slow, and some of the modules take 1 to 2 minutes loading. Maintenance activity is regularly executed based on the scheduled sets. Then I tried to checked the tables indexes/keys turns out that the FILL FACTOR is set to ZERO(0). I would like to know if the FILL FACTOR set to zero will be a factor for the system to slow down..????
Darren Bernabe Blanco
View 2 Replies
View Related
Jul 14, 2006
How do i find out how much fill facot has been spcified in given table?.
View 1 Replies
View Related
Apr 4, 2001
What are the criteria to decide the optimal(or close to optimal) fill factor?
Any input is appreciated.
View 2 Replies
View Related
Aug 9, 2001
You have 50,000 records in a database file and you know you want to add another 100,000 records in the next several weeks. What fill factor would you use to maximize performance?
A.0(default setting)
B.30
C.70
D.100
which one is correct? And how to calculate fill factor?
View 1 Replies
View Related
Sep 14, 1998
I have some non-clustered , non-unique indexes on a medium sized table (25,000 rows).
The fill factore is showing 248% on these indexes. I have tried setting the fill factor to various values 100% or less. The re build index seems to work, however the est min/avg/max size of the index all show appx 160Kb, whilst the actual size is in excess of 50Mb !!
We run a weekly rebuild of all indexes overnight, without any fill params and following this the fill on these indexes goes back to 248% !
I have also dropped and re-created the index with a fill of 100%, and it has still reverted to 248% following the weekly rebuild.
I have also looked at the server config, and the fill factor there shows a running value of 171% !!, although the current config is set to 0.
The server is stopped every evening, so no way should the config have a value of 171, especially since the max allowed value is 100.
Any advice/assistance would be gratefully received.
View 1 Replies
View Related
Jun 7, 2002
I know what fill factor is ... and know that I should set it high when I have static data tables (where the data rarely changes) and low when I expect to have page splits ...
but does anyone know what affect on performance this setting has ? I don't quite get what Books Online says about it.
Thanks
Carol
View 1 Replies
View Related
Aug 25, 2004
Hi ,
Can u plz tell me what is fill factor and what its role in defining the indexes. It is by default 0% and can be set upto 100%, but what it makes difference if i change the percentage? Where it is exactly impacts? If u know any links then plz forward me.
Thanks And Regards,
Shailesh
View 3 Replies
View Related
Oct 13, 2004
I am trying to set up the relationship (Primary Key and Forign Key) in several tables. I would like to find a way also be able to set 'Fill Factor = 90%' in the script. Here is the code that i have so far:
ALTER TABLE Req
ADD CONSTRAINT FK_Req_Bow FOREIGN KEY ( BowID )
REFERENCES Bow ( BowID )
Any help would be greatly appreciated!
J827
View 1 Replies
View Related
Feb 21, 2008
Hi there
Is there any such thing that you can find the current Fill Factor for each indexing? The only thing that you have an indication is by looking at DBCC SHOWCONTIF > Scan Density [Best Count:Actual Count].......: 100.00% [0:0] and if this value is not reaching 100% means may have an issue with fill factor ?!!?
Also, how about SQL2000?
Thanks
View 4 Replies
View Related
Jul 20, 2005
HiThere are a lot of articles about the fillfactor.I did change the fill factor and that did not work as intended.How do I get back to the default fill factorI am using sp_msforeachtable undocumented database procedureand when the indexes are rebuilt the fill factor that shows upin origfillfactor is the one I am trying to move away fromYour help will be appreciatedVince
View 4 Replies
View Related
Jul 31, 2007
Turn away pure key zealots
I have a clustered index that starts with an INT IDENTITY(1,1) column and therefore you can only add data to the end of the cluster. What I'm confused about is the relationship between this and the fill factor. In a normal fill factor scenario you'd be worried about inserts causing page splits but if you can only append to this cluster does this mean I should set the factor to 100% even if I'm expecting a large number of inserts? Basically I don't understand what happens when you run out of space on a page on a B-tree if it's based upon an ever increasing number.
View 3 Replies
View Related
Aug 19, 2002
The tables in my database somehow are getting set with a fill factor of 90. In the properties of the server/Database Setting, the "Fixed" option is unchecked. Last Friday, I reset the each table to have a fill factor of zero, but when I came in today, the tables reset themselves to having a fill factor = 90. Any ideas of why this is happening and how I can stop this? Your help is greatly appreciated.
Mark
View 1 Replies
View Related
Jul 20, 2004
If I have a clustered index on only one column of the table, the column being the primary key and an identity column ( int datatype, Ascending CIDX), then can I go ahead and have the Fill Factor be 100%. I may add more rows at a later date to the table.
Let me know.
View 1 Replies
View Related
May 27, 2008
Hi,
Now i am doing a R&D on indexes in SQL 2005. I want to know clearly what is the actual uses of Fill Factor? What is advantage and disadvantage of it?
Where it should use this fill factor?
Please answer me as soon as possible.
Ganesh.
View 17 Replies
View Related
Jun 14, 2015
I am about to rebuild all my indexes on a database that is very heavily fragmented. In looking at the report, seems that 80% or more tables are 90%+ fragmented.
My understanding is that fill value value is used for performance reasons. Our shiny new backend SAN is 100% SSD. If solid state can provide a sub-millisecond response, is fill factor still necessary at the cost of additional space being used used?
View 0 Replies
View Related
Mar 21, 2007
Are there any rules of thumb to apply when entering the Index Fill Factor?
I just want to make sure I'm enter a optimal value.
Using MS SQL 2000.
Thanks!
--PhB
View 2 Replies
View Related
Aug 21, 2007
Is the "Fill Factor" percentage is same as the "change free space per page percentage to" option in the Rebuild Index task wizard?
OR Is it inverse of it ?
Any comments and suggestions are welcome.....
Jas
View 2 Replies
View Related
Mar 7, 2005
Hi all,
i have a very general question about databases. What is the advantage and disadvantage of using a heavily indexed database?
The advantage i could think is that search operations will be fast. The disadvantage (according to me who is a newbie) is that the size of the database will increase.
My teacher however is not very happy with this answer and wants me to research more. Any help will be greatly appreciated.
Thanks
View 1 Replies
View Related
Sep 27, 2015
I would like to migrate around 15 databases in production server to the new production server. The biggest database is 80 GB .
Wondering is there any fastest way to do that with very low downtime ?
What I can think about is shrink databases files and perform attach – detach .
View 8 Replies
View Related
Aug 30, 2005
Has anyone implemented split data for an application between two databases because the data size is extremely large? If so could you please point me to relevant information.In this split data scenario, a table will automatically carry over to another database whenever the size limit for the current database is reached. The challenge is here for the DAL (data access layer) to automatically look into the appropriate database when the next row of data is in another database. OR Perhaps there is another solution to this terasize data problem..Any help on this would be greatly appreciated.
View 8 Replies
View Related
Apr 9, 2008
HI
I need help
how can i fill data in textboxes from sql databases but two different tables when i select a name that is inside a dropdownlist
my controls are as follows
<asp:DropDownList ID="ddl" runat="server" DataSourceID="SqlDataSource13" DataTextField="fullname" DataValueField="fullname">
</asp:DropDownList>
<asp:SqlDataSource ID="SqlDataSource13" runat="server" ConnectionString="<%$ ConnectionStrings:NPI Employee MasterConnectionString2 %>"
SelectCommand="SELECT [FirstName]+' '+ [Surname] as fullname FROM [Employee] where CurrentEmployee_YN=1 order by FirstName "></asp:SqlDataSource><table bordercolor="#111111" cellpadding="0" cellspacing="0" style="width: 100%;
border-collapse: collapse; height: 32px; visibility: hidden;" id="table0">
<tr>
<td style="width: 159px; visibility: hidden;">
</td>
<td style="width: 170px">
</td>
<td bgcolor="#eeeddb" style="width: 20%; height: 25px">
<strong>
Order No：</strong></td>
<td bgcolor="#eeeddb" style="width: 26%; height: 25px">
<asp:Label ID="OrderNo" runat="server" Width="104px"></asp:Label></td>
</tr>
<tr>
<td bgcolor="#eeeddb" style="width: 159px; height: 25px">
<strong>
Account No：</strong></td>
<td bgcolor="#eeeddb" style="width: 170px">
<asp:TextBox ID="AccountNo" runat="Server" MaxLength="10" Width="130px"></asp:TextBox>
<asp:RequiredFieldValidator ID="RequiredFieldValidator6" runat="server" ControlToValidate="AccountNo"
Display="Static" ErrorMessage="Enter Acc No." Text="*"></asp:RequiredFieldValidator></td>
<td bgcolor="#eeeddb" style="width: 20%; height: 25px">
<strong>
Today's Date:</strong></td>
<td>
<asp:Label ID="Label1" runat="server" Text="Label" Width="200px"></asp:Label></td>
</tr>
<tr>
<td bgcolor="#eeeddb" style="width: 159px; height: 25px">
<strong>
Travel Consultant：</strong></td>
<td bgcolor="#eeeddb" style="width: 170px">
<asp:TextBox ID="Consultant" runat="Server" MaxLength="30" Width="128px"></asp:TextBox>
<asp:RequiredFieldValidator ID="RequiredFieldValidator7" runat="server" ControlToValidate="Consultant"
Display="Static" ErrorMessage="Enter Travel Consultant." Text="*"></asp:RequiredFieldValidator></td>
</tr>
</table>
<center>
</center>
<center>
</center><table bordercolor="#111111" cellpadding="0" cellspacing="0" style="width: 80%;
border-collapse: collapse; height: 32px; display: block; visibility: hidden;" id="table2">
<tr>
<td align="center" bgcolor="#ffcc33" colspan="3" style="width: 90%; height: 29px">
<font color="#000000" size="5">Enter Passenger(s) Details</font></td>
</tr>
<tr>
<td bgcolor="#eeeddb" style="width: 31%; height: 25px">
<strong>
Surname：</strong></td>
<td bgcolor="#eeeddb" style="width: 57%; height: 25px">
<asp:TextBox ID="Surname" runat="Server" MaxLength="30" Width="148px"></asp:TextBox>
<asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server"
ControlToValidate="Surname" Display="Static" ErrorMessage="Enter Surname." Text="*"></asp:RequiredFieldValidator></td>
</tr>
<tr>
<td bgcolor="#eeeddb" style="width: 31%; height: 20px">
<strong>
Name：</strong></td>
<td bgcolor="#eeeddb" style="width: 57%; height: 20px">
<asp:TextBox ID="Name" runat="Server" MaxLength="30" Width="148px"></asp:TextBox>
<asp:RequiredFieldValidator ID="RequiredFieldValidator2" runat="server" ControlToValidate="Name"
Display="Static" ErrorMessage="Enter Name." Text="*"></asp:RequiredFieldValidator></td>
</tr>
<tr>
<td bgcolor="#eeeddb" style="width: 31%; height: 25px">
<strong>
Initials：</strong></td>
<td bgcolor="#eeeddb" style="width: 57%; height: 25px">
<asp:TextBox ID="Initials" runat="Server" MaxLength="5" Width="148px"></asp:TextBox>
</td>
</tr>
<tr>
<td bgcolor="#eeeddb" style="width: 31%; height: 25px">
<strong>
Title：</strong></td>
<td bgcolor="#eeeddb" style="width: 57%; height: 25px">
<asp:DropDownList ID="DropDownList1" runat="server" Width="156px">
<asp:ListItem></asp:ListItem>
<asp:ListItem Value="Mr"></asp:ListItem>
<asp:ListItem Value="Mrs"></asp:ListItem>
<asp:ListItem Value="Ms"></asp:ListItem>
<asp:ListItem Value="Dr"></asp:ListItem>
<asp:ListItem Value="Prof"></asp:ListItem>
<asp:ListItem Value="Min"></asp:ListItem>
<asp:ListItem Value="Other"></asp:ListItem>
</asp:DropDownList>
<asp:RequiredFieldValidator ID="RequiredFieldValidator3" runat="server" ControlToValidate="Dropdownlist1"
Display="Static" ErrorMessage="Select Title." Text="*" Width="20px"></asp:RequiredFieldValidator>
</td>
</tr>
<tr><td bgcolor="#eeeddb">
<strong>
Department</strong>
</td>
<td bgcolor="#eeeddb" style="width: 57%; height: 25px">
<asp:TextBox ID="Department" runat="server"></asp:TextBox></td>
</tr>
<tr><td bgcolor="#eeeddb">
<strong>
Cost Centre</strong>
</td>
<td bgcolor="#eeeddb" style="width: 57%; height: 25px">
<asp:TextBox ID="CostCentre" runat="server"></asp:TextBox></td>
</tr>
<tr>
<td bgcolor="#eeeddb" style="width: 31%; height: 25px">
<strong>
Tel：</strong></td>
<td bgcolor="#eeeddb" style="width: 57%; height: 25px">
<input id="Tel" runat="SERVER" maxlength="15" name="Tel" onkeypress="if((event.keyCode<48)|| (event.keyCode>57))event.returnValue=false"
style="width: 143px" type="text" />
</td>
</tr>
<tr>
<td bgcolor="#eeeddb" style="width: 31%; height: 25px">
<strong>
Fax：</strong></td>
<td bgcolor="#eeeddb" style="width: 57%; height: 25px">
<input id="Fax" runat="SERVER" maxlength="15" name="Fax" onkeypress="if((event.keyCode<48)|| (event.keyCode>57))event.returnValue=false"
style="width: 143px" type="text" />
</td>
</tr>
</table>
cost centre and department are from cost table
and the rest are from employee table
View 10 Replies
View Related
Jul 13, 2014
I have a question regarding the locking behavior of indexed views. We have a 3rd party application and something like the following table:
CREATE TABLE [Person].[Person](
[BusinessEntityID] int NOT NULL,
[FirstName] varchar(20) NOT NULL,
[MiddleName] varchar(20) NULL,
[code]....
Also the 3rd party application uses an indexed view, which is based on the following query and has the same structure as the base table:
SELECT [BusinessEntityID],
[FirstName],
[MiddleName],
[LastName],
SUM([Quantity]) AS [SUMQuantity]
FROM [Person].[Person]
GROUP BY [BusinessEntityID], [FirstName], [MiddleName], [LastName]
The result is, that the indexed view has nearly the same data / entries as the base table. The indexed view is often queried like this:
SELECT SUM(SUMQuantity) FROM [vPerson] WITH(UPDLOCK, NOEXPAND) WHERE [BusinessEntityID] = 45 AND [FirstName] = 'test'
The base table is also queried very often with update locks (UPDLOCK). Because the indexed view is nearly just a copy of the base table and there is no performance gain (read), I would like to drop the indexed view. I'll then experience more locks / blocks, because now the queries are seperated on two objects.
View 4 Replies
View Related
Aug 20, 2014
I have an indexed view and i can't get why after "fixing" one of index it became slower then before.
Goal was to switch index to INT column instead of VARCHAR, so the theory looks perfect !!!(?).
I drop index and built new one with same name. Could it be because of Statistics? should I refresh it all ?
The only thing I changed is one of 3 idx:
-- CustTypeCode VARCHAR(10) /* 1,2,3,4,5 */
-- CustTypeID INT /* 1,2,3,4,5 */
-- index Before:
CREATE NONCLUSTERED INDEX [IdxLookTypeCode] ON [dbMacros].[vw_Lookup_Customer]
([CustTypeCode] ASC)
-- index After:
CREATE NONCLUSTERED INDEX [IdxLookTypeCode] ON [dbMacros].[vw_Lookup_Customer]
([CustTypeID] ASC)
-- usage before:
SELECT C1, C2, FROM vw_Lookup_Customer WITH (NOXPAND)
WHERE CustTypeCode = 2
-- usage after
SELECT C1, C2, FROM vw_Lookup_Customer WITH (NOXPAND)
WHERE CustTypeID = 2
View 3 Replies
View Related
Apr 28, 2014
I work for a federal government client who restricts us to using virtual machines for dev work. Now the VM team has gotten MUCH more aggressive with "right-sizing" (which always means down-sizing) our virtual machines. I have a Dev server with 8 GB RAM and I need that much when developing, but I don't develop all the time. So I want it to look like it needs all that RAM before they bust me down to 1 GB, which they will do.How can I get SQL Server to use up all the memory I've allocated to it? Is there a relatively easy query to do this?
View 8 Replies
View Related
Sep 10, 2014
So we got ServerA.dbA and ServerB.dbB and looks like nobody thought about that MVew. IT works thru linked server now, so surely enough it sucks..
Then even more: we got piece of DW on ServerRep with replcicated pieces of ServerA and ServerB, how it works I asked: "Don't worry man, we took care of everything, go directly to ServerRep for everything..."
And I see that our MView sucks even deeper, practically 0 performance, I think what going on it's still hit that linked server between ServerA.dbA and ServerB.dbB on background.I still can use it on ServerRep WITH NOEXPAND hint, which means it was properly defined...
Can we have that scenario at all? Or we can replicate all Tables on ServerRep and then define/redefine this view directly on replicated server ?
View 4 Replies
View Related
Dec 1, 2015
I have created a table from another table where I specified that one of the fields, an number field, is sorted in ascending order and have NOT specified that it is to be an indexed field and there are 10 million records, from 1 to 10,000,000 exactly.
Now, if I query that table, asking to return records 1-1,000 from that non indexed number field that I sorted in ascending order (where number field <= 1,000) , will it run as fast as if it were indexed?
In other words, does SQL know somehow that these records are sorted in ascending order and so will not do a full table scan, stopping at 1,000 to return my data set?
Or is there no way for SQL to know this and only specifying an indexed field allows SQL to know that its in some order and so it doesn't have to do the full scan?
View 15 Replies
View Related
