Alter Index...Rebuild

Running SQL 2005, SP1
In migrating over to SQL 2005, we also took on a different indexing strategy,
going from DBCC DBREINDEX to ALTER INDEX...REBUILD.
There are a couple of things that are worrisome. A sample of the ALTER INDEX
statement I use is included at the end of this message. I only run this on
indexes that are fragmented as per sys.dm_db_index_physical_stats. The
following are the two items of concern.
1. The weekly ALTER INDEX takes longer to complete than the weekly DBCC
DBREINDEX. I have SORT_IN_TEMPDB = ON, but there is relatively little growth,
if any to TempDB. So I assume it may be done in memory. TempDB is on drive C,
a set of internal SCSI disks. The data and log files are on the SAN, spread
across 26 disks.
2. The data file growth is significant (sometimes the log files, but mostly
the data files) when running ALTER INDEX. As noted in concern 1,
SORT_IN_TEMPDB = ON, so I am perplexed why there is such growth on the data
files?
SAMPLE:
ALTER INDEX [name of index] ON [name of database].dbo.[name of table]
REBUILD WITH ( FILLFACTOR = 90, SORT_IN_TEMPDB = ON)
Message posted via http://www.droptable.com
When you rebuild an index with either DBREINDEX or ALTER INDEX REBUILD it
completely rebuilds the indexes (this includes the table if there is a
clustered index) by creating a new copy of them in the data file(s). This
means you need free space in the data files to hold the old and new indexes
at the same time. Only when it is completed does it drop the original
indexes. So you always need plenty of free space in the data files for
operations such as these. You have been around here long enough to know by
know you should ALWAYS have plenty of free space in the database files both
data & Log. If you are getting growth in the data files it sounds like you
shrunk it at some point. This is also a bad idea to shrink the files. The
SORT_IN_TEMPDB can help reduce time and some of the space needed in the data
files as it can serve as a work space to sort the indexes etc during the
processing. But it does not remove the need for the place to rebuild the
index itself. You say there is little growth to Tempdb during this process.
Well again if there is any growth you didn't size the files correctly to
begin with. Lack of growth of the files has nothing to do with how much work
they are performing. As long as there is room in the data files for what it
has to do there should be no growth. That is the proper and expected
behavior.
Andrew J. Kelly SQL MVP
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:7675a0e8fcb67@.uwe...
> Running SQL 2005, SP1
> In migrating over to SQL 2005, we also took on a different indexing
> strategy,
> going from DBCC DBREINDEX to ALTER INDEX...REBUILD.
> There are a couple of things that are worrisome. A sample of the ALTER
> INDEX
> statement I use is included at the end of this message. I only run this on
> indexes that are fragmented as per sys.dm_db_index_physical_stats. The
> following are the two items of concern.
> 1. The weekly ALTER INDEX takes longer to complete than the weekly DBCC
> DBREINDEX. I have SORT_IN_TEMPDB = ON, but there is relatively little
> growth,
> if any to TempDB. So I assume it may be done in memory. TempDB is on drive
> C,
> a set of internal SCSI disks. The data and log files are on the SAN,
> spread
> across 26 disks.
> 2. The data file growth is significant (sometimes the log files, but
> mostly
> the data files) when running ALTER INDEX. As noted in concern 1,
> SORT_IN_TEMPDB = ON, so I am perplexed why there is such growth on the
> data
> files?
> SAMPLE:
> ALTER INDEX [name of index] ON [name of database].dbo.[name of table]
> REBUILD WITH ( FILLFACTOR = 90, SORT_IN_TEMPDB = ON)
> --
> Message posted via http://www.droptable.com
>

Alter Index...Rebuild

Running SQL 2005, SP1
In migrating over to SQL 2005, we also took on a different indexing strategy
,
going from DBCC DBREINDEX to ALTER INDEX...REBUILD.
There are a couple of things that are worrisome. A sample of the ALTER INDEX
statement I use is included at the end of this message. I only run this on
indexes that are fragmented as per sys.dm_db_index_physical_stats. The
following are the two items of concern.
1. The weekly ALTER INDEX takes longer to complete than the weekly DBCC
DBREINDEX. I have SORT_IN_TEMPDB = ON, but there is relatively little growth
,
if any to TempDB. So I assume it may be done in memory. TempDB is on drive C
,
a set of internal SCSI disks. The data and log files are on the SAN, spread
across 26 disks.
2. The data file growth is significant (sometimes the log files, but mostly
the data files) when running ALTER INDEX. As noted in concern 1,
SORT_IN_TEMPDB = ON, so I am perplexed why there is such growth on the data
files?
SAMPLE:
ALTER INDEX [name of index] ON [name of database].dbo.[name of t
able]
REBUILD WITH ( FILLFACTOR = 90, SORT_IN_TEMPDB = ON)
Message posted via http://www.droptable.comWhen you rebuild an index with either DBREINDEX or ALTER INDEX REBUILD it
completely rebuilds the indexes (this includes the table if there is a
clustered index) by creating a new copy of them in the data file(s). This
means you need free space in the data files to hold the old and new indexes
at the same time. Only when it is completed does it drop the original
indexes. So you always need plenty of free space in the data files for
operations such as these. You have been around here long enough to know by
know you should ALWAYS have plenty of free space in the database files both
data & Log. If you are getting growth in the data files it sounds like you
shrunk it at some point. This is also a bad idea to shrink the files. The
SORT_IN_TEMPDB can help reduce time and some of the space needed in the data
files as it can serve as a work space to sort the indexes etc during the
processing. But it does not remove the need for the place to rebuild the
index itself. You say there is little growth to Tempdb during this process.
Well again if there is any growth you didn't size the files correctly to
begin with. Lack of growth of the files has nothing to do with how much work
they are performing. As long as there is room in the data files for what it
has to do there should be no growth. That is the proper and expected
behavior.
Andrew J. Kelly SQL MVP
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:7675a0e8fcb67@.uwe...
> Running SQL 2005, SP1
> In migrating over to SQL 2005, we also took on a different indexing
> strategy,
> going from DBCC DBREINDEX to ALTER INDEX...REBUILD.
> There are a couple of things that are worrisome. A sample of the ALTER
> INDEX
> statement I use is included at the end of this message. I only run this on
> indexes that are fragmented as per sys.dm_db_index_physical_stats. The
> following are the two items of concern.
> 1. The weekly ALTER INDEX takes longer to complete than the weekly DBCC
> DBREINDEX. I have SORT_IN_TEMPDB = ON, but there is relatively little
> growth,
> if any to TempDB. So I assume it may be done in memory. TempDB is on drive
> C,
> a set of internal SCSI disks. The data and log files are on the SAN,
> spread
> across 26 disks.
> 2. The data file growth is significant (sometimes the log files, but
> mostly
> the data files) when running ALTER INDEX. As noted in concern 1,
> SORT_IN_TEMPDB = ON, so I am perplexed why there is such growth on the
> data
> files?
> SAMPLE:
> ALTER INDEX [name of index] ON [name of database].dbo.[name of
table]
> REBUILD WITH ( FILLFACTOR = 90, SORT_IN_TEMPDB = ON)
> --
> Message posted via http://www.droptable.com
>

Alter Index...Rebuild

Running SQL 2005, SP1
In migrating over to SQL 2005, we also took on a different indexing strategy,
going from DBCC DBREINDEX to ALTER INDEX...REBUILD.
There are a couple of things that are worrisome. A sample of the ALTER INDEX
statement I use is included at the end of this message. I only run this on
indexes that are fragmented as per sys.dm_db_index_physical_stats. The
following are the two items of concern.
1. The weekly ALTER INDEX takes longer to complete than the weekly DBCC
DBREINDEX. I have SORT_IN_TEMPDB = ON, but there is relatively little growth,
if any to TempDB. So I assume it may be done in memory. TempDB is on drive C,
a set of internal SCSI disks. The data and log files are on the SAN, spread
across 26 disks.
2. The data file growth is significant (sometimes the log files, but mostly
the data files) when running ALTER INDEX. As noted in concern 1,
SORT_IN_TEMPDB = ON, so I am perplexed why there is such growth on the data
files?
SAMPLE:
ALTER INDEX [name of index] ON [name of database].dbo.[name of table]
REBUILD WITH ( FILLFACTOR = 90, SORT_IN_TEMPDB = ON)
--
Message posted via http://www.sqlmonster.comWhen you rebuild an index with either DBREINDEX or ALTER INDEX REBUILD it
completely rebuilds the indexes (this includes the table if there is a
clustered index) by creating a new copy of them in the data file(s). This
means you need free space in the data files to hold the old and new indexes
at the same time. Only when it is completed does it drop the original
indexes. So you always need plenty of free space in the data files for
operations such as these. You have been around here long enough to know by
know you should ALWAYS have plenty of free space in the database files both
data & Log. If you are getting growth in the data files it sounds like you
shrunk it at some point. This is also a bad idea to shrink the files. The
SORT_IN_TEMPDB can help reduce time and some of the space needed in the data
files as it can serve as a work space to sort the indexes etc during the
processing. But it does not remove the need for the place to rebuild the
index itself. You say there is little growth to Tempdb during this process.
Well again if there is any growth you didn't size the files correctly to
begin with. Lack of growth of the files has nothing to do with how much work
they are performing. As long as there is room in the data files for what it
has to do there should be no growth. That is the proper and expected
behavior.
--
Andrew J. Kelly SQL MVP
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:7675a0e8fcb67@.uwe...
> Running SQL 2005, SP1
> In migrating over to SQL 2005, we also took on a different indexing
> strategy,
> going from DBCC DBREINDEX to ALTER INDEX...REBUILD.
> There are a couple of things that are worrisome. A sample of the ALTER
> INDEX
> statement I use is included at the end of this message. I only run this on
> indexes that are fragmented as per sys.dm_db_index_physical_stats. The
> following are the two items of concern.
> 1. The weekly ALTER INDEX takes longer to complete than the weekly DBCC
> DBREINDEX. I have SORT_IN_TEMPDB = ON, but there is relatively little
> growth,
> if any to TempDB. So I assume it may be done in memory. TempDB is on drive
> C,
> a set of internal SCSI disks. The data and log files are on the SAN,
> spread
> across 26 disks.
> 2. The data file growth is significant (sometimes the log files, but
> mostly
> the data files) when running ALTER INDEX. As noted in concern 1,
> SORT_IN_TEMPDB = ON, so I am perplexed why there is such growth on the
> data
> files?
> SAMPLE:
> ALTER INDEX [name of index] ON [name of database].dbo.[name of table]
> REBUILD WITH ( FILLFACTOR = 90, SORT_IN_TEMPDB = ON)
> --
> Message posted via http://www.sqlmonster.com
>

almost done but stuck

Ok I have created a 2005 sql advanced database with text indexing. I have create the database like so

created a new database with text indexing enabled and the following table

createtable support
(problemIdVARCHAR(50)NOTNULLPRIMARYKEY,
problemTitlevarchar(50)NOTNULL,
problemBodytextNOTNULL,
linkOnevarchar(50),
linkTwovarchar(50),
linkThreevarchar(50),
linkFourvarchar(50),ftid int NOT NULL)

next

createfulltextcatalog remoteSupportCatalog

createuniqueindex ui_remotesupportON support(ftid)

then

createfulltextindexon support(problemBody)keyindex PK__support__7C8480AEon remoteSupportCatalog

---

I then populated some rows and issues a quesry

Select * from support where freetext(problemBody, 'test database')

it works pulls back all the data I expected it to pull back

In my asp page I created a database connection with the folling select command

<asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:rsdb2ConnectionString2 %>"

SelectCommand="SELECT * FROM support WHERE FREETEXT(problemBody, @.srchBox)">

created the search parameter

<SelectParameters>

<asp:ControlParameterControlID="srchBox"PropertyName="Text"Type="String"Name="srchBox"/> //this is a text box that is searchable with a button

</SelectParameters>

and it doesnt give me back an error or data it does nothing. What am I missing???

here is the whole asp page

<%@.PageLanguage="C#" %>

<!DOCTYPEhtmlPUBLIC"-//W3C//DTD XHTML 1.0 Transitional//EN""http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<scriptrunat="server">

</script>

<htmlxmlns="http://www.w3.org/1999/xhtml">

<headrunat="server">

<title>Untitled Page</title>

</head>

<bodybgcolor="#e4e4e4">

<formid="form1"runat="server">

<div>

<asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:rsdb2ConnectionString2 %>"

SelectCommand="SELECT * FROM support WHERE freetext(problemBody, @.srchBox ) ">

<SelectParameters>

<asp:ParameterName="srchBox"/>

</SelectParameters>

</asp:SqlDataSource>

<tablestyle="z-index: 100; left: 107px; position: absolute; top: 144px; width: 695px; height: 443px;"bgcolor="#000000">

<tr>

<tdstyle="width: 475px; height: 125px">

<asp:DetailsViewID="DetailsView1"runat="server"AllowPaging="True"AutoGenerateRows="False"

CellPadding="4"DataKeyNames="problemId"DataSourceID="SqlDataSource1"ForeColor="#333333"

GridLines="None"Height="52px"Width="679px">

<FooterStyleBackColor="#1C5E55"Font-Bold="True"ForeColor="White"/>

<CommandRowStyleBackColor="#C5BBAF"Font-Bold="True"/>

<EditRowStyleBackColor="#7C6F57"/>

<RowStyleBackColor="#E3EAEB"/>

<PagerStyleBackColor="#666666"ForeColor="White"HorizontalAlign="Center"/>

<Fields>

<asp:BoundFieldDataField="problemId"HeaderText="Id:"ReadOnly="True"SortExpression="problemId">

<ItemStyleWidth="600px"BorderColor="White"BorderStyle="Solid"BorderWidth="1px"/>

<HeaderStyleBorderStyle="Solid"BorderWidth="1px"/>

</asp:BoundField>

<asp:BoundFieldDataField="problemTitle"HeaderText="Description:"SortExpression="problemTitle">

<ItemStyleBorderStyle="Solid"BorderWidth="1px"/>

<HeaderStyleBorderStyle="Solid"BorderWidth="1px"/>

</asp:BoundField>

<asp:BoundFieldDataField="problemBody"HeaderText="Resolution:"SortExpression="problemBody">

<ItemStyleBorderStyle="Solid"BorderWidth="1px"/>

<HeaderStyleBorderStyle="Solid"BorderWidth="1px"/>

</asp:BoundField>

<asp:BoundFieldDataField="linkOne"HeaderText="Links:"SortExpression="linkOne">

<ItemStyleBorderStyle="Solid"BorderWidth="1px"/>

<HeaderStyleBorderStyle="Solid"BorderWidth="1px"/>

</asp:BoundField>

<asp:BoundFieldDataField="linkTwo"HeaderText="linkTwo"SortExpression="linkTwo"ShowHeader="False">

<ItemStyleBorderStyle="Solid"BorderWidth="1px"/>

<HeaderStyleBorderStyle="Solid"BorderWidth="1px"/>

</asp:BoundField>

<asp:BoundFieldDataField="linkThree"HeaderText="linkThree"SortExpression="linkThree"ShowHeader="False">

<ItemStyleBorderStyle="Solid"BorderWidth="1px"/>

<HeaderStyleBorderStyle="Solid"BorderWidth="1px"/>

</asp:BoundField>

<asp:BoundFieldDataField="linkFour"HeaderText="linkFour"SortExpression="linkFour"ShowHeader="False">

<ItemStyleBorderStyle="Solid"BorderWidth="1px"/>

<HeaderStyleBorderStyle="Solid"BorderWidth="1px"/>

</asp:BoundField>

</Fields>

<FieldHeaderStyleBackColor="#D0D0D0"Font-Bold="True"/>

<HeaderStyleBackColor="#1C5E55"Font-Bold="True"ForeColor="White"/>

<AlternatingRowStyleBackColor="White"/>

</asp:DetailsView>

<asp:LabelID="Label1"runat="server"Font-Bold="True"ForeColor="White"Style="z-index: 103;

left: 13px; position: absolute; top: 59px"Width="521px"></asp:Label>

<asp:TextBoxID="srchBox"runat="server"Style="z-index: 101; left: 11px; position: absolute;

top: 31px"Width="520px"></asp:TextBox>

<asp:ButtonID="Button1"runat="server"Style="z-index: 102; left: 555px; position: absolute;

top: 31px"Text="Search It"/>

<asp:SqlDataSourceID="SqlDataSource2"runat="server"></asp:SqlDataSource>

</td>

</tr>

</table>

</div>

</form>

</body>

</html>

|||

here is the whole asp page

<%@.PageLanguage="C#" %>

<!DOCTYPEhtmlPUBLIC"-//W3C//DTD XHTML 1.0 Transitional//EN""http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<scriptrunat="server">

</script> <htmlxmlns="http://www.w3.org/1999/xhtml">

<headrunat="server">

<title>Untitled Page</title> </head>

<bodybgcolor="#e4e4e4">

<formid="form1"runat="server">

<div>

<asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:rsdb2ConnectionString2 %>"

SelectCommand="SELECT * FROM support WHERE freetext(problemBody, @.srchBox ) ">

<SelectParameters>

<asp:ParameterName="srchBox"/>

</SelectParameters>

</asp:SqlDataSource>

<tablestyle="z-index: 100; left: 107px; position: absolute; top: 144px; width: 695px; height: 443px;"bgcolor="#000000">

<tr>

<tdstyle="width: 475px; height: 125px">

<asp:DetailsViewID="DetailsView1"runat="server"AllowPaging="True"AutoGenerateRows="False"

CellPadding="4"DataKeyNames="problemId"DataSourceID="SqlDataSource1"ForeColor="#333333"

GridLines="None"Height="52px"Width="679px">

<FooterStyleBackColor="#1C5E55"Font-Bold="True"ForeColor="White"/>

<CommandRowStyleBackColor="#C5BBAF"Font-Bold="True"/>

<EditRowStyleBackColor="#7C6F57"/>

<RowStyleBackColor="#E3EAEB"/>

<PagerStyleBackColor="#666666"ForeColor="White"HorizontalAlign="Center"/>

<Fields>

<asp:BoundFieldDataField="problemId"HeaderText="Id:"ReadOnly="True"SortExpression="problemId">

<ItemStyleWidth="600px"BorderColor="White"BorderStyle="Solid"BorderWidth="1px"/>

<HeaderStyleBorderStyle="Solid"BorderWidth="1px"/>

</asp:BoundField>

<asp:BoundFieldDataField="problemTitle"HeaderText="Description:"SortExpression="problemTitle">

<ItemStyleBorderStyle="Solid"BorderWidth="1px"/>

<HeaderStyleBorderStyle="Solid"BorderWidth="1px"/>

</asp:BoundField>

<asp:BoundFieldDataField="problemBody"HeaderText="Resolution:"SortExpression="problemBody">

<ItemStyleBorderStyle="Solid"BorderWidth="1px"/>

<HeaderStyleBorderStyle="Solid"BorderWidth="1px"/>

</asp:BoundField>

<asp:BoundFieldDataField="linkOne"HeaderText="Links:"SortExpression="linkOne">

<ItemStyleBorderStyle="Solid"BorderWidth="1px"/>

<HeaderStyleBorderStyle="Solid"BorderWidth="1px"/>

</asp:BoundField>

<asp:BoundFieldDataField="linkTwo"HeaderText="linkTwo"SortExpression="linkTwo"ShowHeader="False">

<ItemStyleBorderStyle="Solid"BorderWidth="1px"/>

<HeaderStyleBorderStyle="Solid"BorderWidth="1px"/>

</asp:BoundField>

<asp:BoundFieldDataField="linkThree"HeaderText="linkThree"SortExpression="linkThree"ShowHeader="False">

<ItemStyleBorderStyle="Solid"BorderWidth="1px"/>

<HeaderStyleBorderStyle="Solid"BorderWidth="1px"/>

</asp:BoundField>

<asp:BoundFieldDataField="linkFour"HeaderText="linkFour"SortExpression="linkFour"ShowHeader="False">

<ItemStyleBorderStyle="Solid"BorderWidth="1px"/>

<HeaderStyleBorderStyle="Solid"BorderWidth="1px"/>

</asp:BoundField>

</Fields>

<FieldHeaderStyleBackColor="#D0D0D0"Font-Bold="True"/>

<HeaderStyleBackColor="#1C5E55"Font-Bold="True"ForeColor="White"/>

<AlternatingRowStyleBackColor="White"/>

</asp:DetailsView>

<asp:LabelID="Label1"runat="server"Font-Bold="True"ForeColor="White"Style="z-index: 103;

left: 13px; position: absolute; top: 59px"Width="521px"></asp:Label>

<asp:TextBoxID="srchBox"runat="server"Style="z-index: 101; left: 11px; position: absolute;

top: 31px"Width="520px"></asp:TextBox>

<asp:ButtonID="Button1"runat="server"Style="z-index: 102; left: 555px; position: absolute;

top: 31px"Text="Search It"/>

<asp:SqlDataSourceID="SqlDataSource2"runat="server"></asp:SqlDataSource>

</td></tr>

</table>

</div>

</form> </body>

</html>

|||

quarinteen:

<SelectParameters>

<asp:ParameterName="srchBox"/>

</SelectParameters>

Have you tried to use Control parameter ? One more thing, what is the code you have written to execute the select method ? Can you post the code behind code also ?

|||

There is not any c# or vb to control the select statement. Just the asp.

|||

What I think is that you must be executing the datasource.select() method on the button click event, otherwise when would you bind the data to your grid after the user writes something in the textbox ? You've created everything and shown everything to the user.

Now, when user writes something in the textbox and presses the "Search It" button, you must execute the datasource.select() method. This is my assumption after reading your code. Post some more details as to what you are doing now and what is happing ( does any error occur ? ) so that I can help you better.

|||

no there is no c# or vb on this page that I have writter. Also I know varyations of this works I I put in the select statement "Select * from support where ((problemBody '%' + @.srchBox + '%') OR @.srchBox IS NULL)" it brings back the data

|||

Take the page you posted in the 2nd message. Change the parameter to a control parameter like you posted in the first message. Then change the sqldatasource, so that the "CancelSelectOnNullParameter" property is set to false. Change the search textbox, so it's autopostback property is set to true.

You may also need to change the parameter's properties to keep it from changing empty string to null, depending on how the freetext function interprets null parameters. For that matter, it might not like the empty string either.