index and key: command

Showing posts with label command. Show all posts

Monday, March 26, 2012

index rebuilds

I thought there was a DBCC command that would rebuild all the indexes in a
database with one statement. Can anyone help please.> I thought there was a DBCC command that would rebuild all the indexes in a
> database with one statement. Can anyone help please.
DBCC DBREINDEX can rebuild all indexes for a single table. You can use a
cusros to loop trough all tables and dynamically prepare and execute the
DBCC command for each table. There is also a shortcut:
http://www.mssqlcity.com/FAQ/Devel/sp_msforeachtable.htm.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com|||Before you spend the time doing this, think about why you're doing it. You
should read the whitepaper
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
which explain when and how to reduced fragmentation in indexes.
Thanks
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
message news:#J8ghKrtEHA.1008@.tk2msftngp13.phx.gbl...
> > I thought there was a DBCC command that would rebuild all the indexes in
a
> > database with one statement. Can anyone help please.
> DBCC DBREINDEX can rebuild all indexes for a single table. You can use a
> cusros to loop trough all tables and dynamically prepare and execute the
> DBCC command for each table. There is also a shortcut:
> http://www.mssqlcity.com/FAQ/Devel/sp_msforeachtable.htm.
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> www.SolidQualityLearning.com
>

index rebuilds

I thought there was a DBCC command that would rebuild all the indexes in a
database with one statement. Can anyone help please.
> I thought there was a DBCC command that would rebuild all the indexes in a
> database with one statement. Can anyone help please.
DBCC DBREINDEX can rebuild all indexes for a single table. You can use a
cusros to loop trough all tables and dynamically prepare and execute the
DBCC command for each table. There is also a shortcut:
http://www.mssqlcity.com/FAQ/Devel/s...eachtable.htm.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
|||Before you spend the time doing this, think about why you're doing it. You
should read the whitepaper
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
which explain when and how to reduced fragmentation in indexes.
Thanks
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si > wrote in
message news:#J8ghKrtEHA.1008@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
a
> DBCC DBREINDEX can rebuild all indexes for a single table. You can use a
> cusros to loop trough all tables and dynamically prepare and execute the
> DBCC command for each table. There is also a shortcut:
> http://www.mssqlcity.com/FAQ/Devel/s...eachtable.htm.
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> www.SolidQualityLearning.com
>
sql

index rebuilds

I thought there was a DBCC command that would rebuild all the indexes in a
database with one statement. Can anyone help please.> I thought there was a DBCC command that would rebuild all the indexes in a
> database with one statement. Can anyone help please.
DBCC DBREINDEX can rebuild all indexes for a single table. You can use a
cusros to loop trough all tables and dynamically prepare and execute the
DBCC command for each table. There is also a shortcut:
http://www.mssqlcity.com/FAQ/Devel/...reachtable.htm.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com|||Before you spend the time doing this, think about why you're doing it. You
should read the whitepaper
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
which explain when and how to reduced fragmentation in indexes.
Thanks
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
message news:#J8ghKrtEHA.1008@.tk2msftngp13.phx.gbl...
a[vbcol=seagreen]
> DBCC DBREINDEX can rebuild all indexes for a single table. You can use a
> cusros to loop trough all tables and dynamically prepare and execute the
> DBCC command for each table. There is also a shortcut:
> http://www.mssqlcity.com/FAQ/Devel/...reachtable.htm.
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> www.SolidQualityLearning.com
>

Index Rebuilding

Hi,
I am going to Rebuild the index on tables of production database.
I will use 'dbcc dbreindex' command to do this index rebuilding.
Now, I want to know what are the Prerequisites should i take to do
this job.
And also what are the Precautions shuld i take.
Please guide me in this regard.
Thanks & Regards,
Sajid.DBREINDEX rebuilds indexes completely and is resource intensive. This may
cause blocking and performance issues so you should run it during minimal
activity. Also, DBREINDEX is fully-logged regardless of your database
recovery model so make sure you have sufficient log space available.
--
Hope this helps.
Dan Guzman
SQL Server MVP
<csajid@.gmail.com> wrote in message
news:1149766763.732814.141490@.c74g2000cwc.googlegroups.com...
> Hi,
> I am going to Rebuild the index on tables of production database.
> I will use 'dbcc dbreindex' command to do this index rebuilding.
> Now, I want to know what are the Prerequisites should i take to do
> this job.
> And also what are the Precautions shuld i take.
> Please guide me in this regard.
>
> Thanks & Regards,
> Sajid.
>|||Dan Guzman wrote:
> DBREINDEX rebuilds indexes completely and is resource intensive. This may
> cause blocking and performance issues so you should run it during minimal
> activity. Also, DBREINDEX is fully-logged regardless of your database
> recovery model so make sure you have sufficient log space available.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> <csajid@.gmail.com> wrote in message
> news:1149766763.732814.141490@.c74g2000cwc.googlegroups.com...
> > Hi,
> >
> > I am going to Rebuild the index on tables of production database.
> >
> > I will use 'dbcc dbreindex' command to do this index rebuilding.
> >
> > Now, I want to know what are the Prerequisites should i take to do
> > this job.
> >
> > And also what are the Precautions shuld i take.
> >
> > Please guide me in this regard.
> >
> >
> > Thanks & Regards,
> > Sajid.
> >
It requires downtime, since it locks tables and if you are rebuilding
cluster index then you need to about 1.2 times table space for
rebuilding index or nonclustered index then about 1.2 time space than
size of non clustered index.
Log backup size also increases , so if you do not need log backup
during this time , change recovery model to simple. After completing
activity change recovery model to full again and take a full backup.
In SQL Server 2005 you can rebuild index dynamically , but please check
BOL for it.
Regards
Amish Shah.|||Hi,
Thanks Guys for your quick reply.
Thanks & Regards,
Sajid N. Chhapekar.
amish wrote:
> Dan Guzman wrote:
> > DBREINDEX rebuilds indexes completely and is resource intensive. This may
> > cause blocking and performance issues so you should run it during minimal
> > activity. Also, DBREINDEX is fully-logged regardless of your database
> > recovery model so make sure you have sufficient log space available.
> >
> > --
> > Hope this helps.
> >
> > Dan Guzman
> > SQL Server MVP
> >
> > <csajid@.gmail.com> wrote in message
> > news:1149766763.732814.141490@.c74g2000cwc.googlegroups.com...
> > > Hi,
> > >
> > > I am going to Rebuild the index on tables of production database.
> > >
> > > I will use 'dbcc dbreindex' command to do this index rebuilding.
> > >
> > > Now, I want to know what are the Prerequisites should i take to do
> > > this job.
> > >
> > > And also what are the Precautions shuld i take.
> > >
> > > Please guide me in this regard.
> > >
> > >
> > > Thanks & Regards,
> > > Sajid.
> > >
> It requires downtime, since it locks tables and if you are rebuilding
> cluster index then you need to about 1.2 times table space for
> rebuilding index or nonclustered index then about 1.2 time space than
> size of non clustered index.
> Log backup size also increases , so if you do not need log backup
> during this time , change recovery model to simple. After completing
> activity change recovery model to full again and take a full backup.
> In SQL Server 2005 you can rebuild index dynamically , but please check
> BOL for it.
> Regards
> Amish Shah.

Index Rebuilding

Index rebuild

I run dbcc dbreindex command and send the output to the text file. Is there any other way to check that indexes were in fact rebuild?You could do a before and after DBCC SHOWCONTIG. But, if DBREINDEX retunrs no errors, it ran successfully.

Monday, March 19, 2012

Index Monitoring

I monitor the index fragmentation through dbcc showcontig command , i get result as
Pages Scanned
Extents Scanned
Extent Switches
Avg. Pages per Extent
Scan Density [Best Count:Actual Count]
Logical Scan Fragmentation
Extent Scan Fragmentation
Avg. Bytes Free per Page
Avg. Page Density (full).

But i want to know can we get value of 'extent switches' and logical scan fragmentation' through system tables , because we know that all database details are updated in system tables.As you have posted a question in the article section it ts being moved to SQL Server Forum.

Monday, March 12, 2012

Index is being rebuilt

Usually when I create an index in the Query Analyzer, no message is
returned other than the command completed. But sometimes I get a
message than the index is being rebuilt. I have come to ignore this
message as it appears to not be an issue that needs research.

I am now doing more and more in the VB.NET environment using ADO.NET
to maniupulate SQL objects. (Using SQL7, btw.) When I need to create
an index in VB.NET code I use the ExecuteNonQuery method since I do
not expect to process a result set. However, if the call results in
the index being rebuilt the return message blows up the
ExecuteNonQuery method with a general network error.

As I see it I have two options. One is to use the ExecuteReader method
instead and simply ignore anything returned. The other is to tell SQL
to not tell me that an index is being rebuilt as I don't care to even
know that. I could trap the general network error and ignore it but I
think it would be dangerous to ignore all network errors.

Any suggestions?

Please respond here as email address is invalid to avoid spam.Zack Sessions (zcsessions@.visionair.com) writes:
> Usually when I create an index in the Query Analyzer, no message is
> returned other than the command completed. But sometimes I get a
> message than the index is being rebuilt. I have come to ignore this
> message as it appears to not be an issue that needs research.

You get that message when you create, change or drop on a clustered index
on a table that also has a non-clustered index. Since the clustered index
serves as a the pointer to the data pages, non-clustered indexes must
be rebuilt when the clustered index is changed.

I have to admit that I am a little mystified. I tried first to get
the message on SQL 2000 SP3, but it remained silent. I know that I have
seen this message quite a bit, but I have not worked much with SQL 7,
so I suspect this message might have been silenced with SP3 of SQL 2000.

> I am now doing more and more in the VB.NET environment using ADO.NET
> to maniupulate SQL objects. (Using SQL7, btw.) When I need to create
> an index in VB.NET code I use the ExecuteNonQuery method since I do
> not expect to process a result set. However, if the call results in
> the index being rebuilt the return message blows up the
> ExecuteNonQuery method with a general network error.

That should not happen. I have a little test app for ADO .Net, and I was
not able to reproduce the problem. Which version of the .Net framework
are you using? Have you set up an InfoMessages event handler?

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks for your reply.

Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns94D7F2295295EYazorman@.127.0.0.1>...
> Zack Sessions (zcsessions@.visionair.com) writes:
> > Usually when I create an index in the Query Analyzer, no message is
> > returned other than the command completed. But sometimes I get a
> > message than the index is being rebuilt. I have come to ignore this
> > message as it appears to not be an issue that needs research.
> You get that message when you create, change or drop on a clustered index
> on a table that also has a non-clustered index. Since the clustered index
> serves as a the pointer to the data pages, non-clustered indexes must
> be rebuilt when the clustered index is changed.

Ahh, mystery cleared up. I was wondering what the answer to "why" was.
> I have to admit that I am a little mystified. I tried first to get
> the message on SQL 2000 SP3, but it remained silent. I know that I have
> seen this message quite a bit, but I have not worked much with SQL 7,
> so I suspect this message might have been silenced with SP3 of SQL 2000.

Hmm, if so all the more reason to upgrade, eh? :-)

> > I am now doing more and more in the VB.NET environment using ADO.NET
> > to maniupulate SQL objects. (Using SQL7, btw.) When I need to create
> > an index in VB.NET code I use the ExecuteNonQuery method since I do
> > not expect to process a result set. However, if the call results in
> > the index being rebuilt the return message blows up the
> > ExecuteNonQuery method with a general network error.
> That should not happen. I have a little test app for ADO .Net, and I was
> not able to reproduce the problem. Which version of the .Net framework
> are you using? Have you set up an InfoMessages event handler?

Workstation is XP with VB.NET using ADO.NET. Server is NT4 SP6a, SQL7
SP4. Workstation and server are at .NET Framework 1.1.

Don't understand InfoMessages event handler. Can't find InfoMessages
mentioned in SQL7 or .NET Books Online. I am using a Try/Catch, that's
how I discovered the exception neing raised was a "general network
error".|||Zack Sessions (zcsessions@.visionair.com) writes:
> Erland Sommarskog <sommar@.algonet.se> wrote in message
news:<Xns94D7F2295295EYazorman@.127.0.0.1>...
>> I have to admit that I am a little mystified. I tried first to get
>> the message on SQL 2000 SP3, but it remained silent. I know that I have
>> seen this message quite a bit, but I have not worked much with SQL 7,
>> so I suspect this message might have been silenced with SP3 of SQL 2000.
> Hmm, if so all the more reason to upgrade, eh? :-)

I would say that upgrading because SQL2000 does not produce a message
that wrecks .Net Framework is the most convincing the reason to upgrade
I've seen. :-)

> Workstation is XP with VB.NET using ADO.NET. Server is NT4 SP6a, SQL7
> SP4. Workstation and server are at .NET Framework 1.1.

I have WinXP, and .Net Framwork 1.1. I have no idea, whether NT/XP could
make the difference.

By the way, if you just submit a print statement with ExecuteNonQuery,
does that too give General Network Error?

> Don't understand InfoMessages event handler. Can't find InfoMessages
> mentioned in SQL7 or .NET Books Online. I am using a Try/Catch, that's
> how I discovered the exception neing raised was a "general network
> error".

It's in the SqlConnection class. And it's InfoMessage.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Wednesday, March 7, 2012

index database

In vfp database, i can use the command ' reindex' to index the database
again.
for SQL server, can i do that '
thanks a lotCan you explain what such a "reindex" is supposed to achieve? It is possible that you are looking
for the DBCC DBREINDEX or DBCC INDEXDEFRAG (2000) or for 2005 ALTER INDEX with either the REBUILD or
the REORGANIZE options.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Agnes" <agnes@.dynamictech.com.hk> wrote in message news:e$dcsir9GHA.2268@.TK2MSFTNGP05.phx.gbl...
> In vfp database, i can use the command ' reindex' to index the database again.
> for SQL server, can i do that '
> thanks a lot
>|||Hi Agnes
You don't give the version of SQL Server you are using check out DBCC
DBREINDEX and INDEXDEFRAG in Books Online if you are using SQL 2000 also DBCC
SHOWCONTIG which has an example script to only reindex indexes that are
fragmented by a certain amount. If using SQL 2005 check out the ALTER INDEX
statement and the sys.db_index_physical_stats view.
John
"Agnes" wrote:
> In vfp database, i can use the command ' reindex' to index the database
> again.
> for SQL server, can i do that '
> thanks a lot
>
>|||sp_MSforeachtable 'dbcc dbreindex(''?'')'
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Agnes" <agnes@.dynamictech.com.hk> wrote in message
news:e$dcsir9GHA.2268@.TK2MSFTNGP05.phx.gbl...
> In vfp database, i can use the command ' reindex' to index the database
> again.
> for SQL server, can i do that '
> thanks a lot
>

Friday, February 24, 2012

index database

In vfp database, i can use the command ' reindex' to index the database
again.
for SQL server, can i do that ?
thanks a lot
Can you explain what such a "reindex" is supposed to achieve? It is possible that you are looking
for the DBCC DBREINDEX or DBCC INDEXDEFRAG (2000) or for 2005 ALTER INDEX with either the REBUILD or
the REORGANIZE options.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Agnes" <agnes@.dynamictech.com.hk> wrote in message news:e$dcsir9GHA.2268@.TK2MSFTNGP05.phx.gbl...
> In vfp database, i can use the command ' reindex' to index the database again.
> for SQL server, can i do that ?
> thanks a lot
>
|||Hi Agnes
You don't give the version of SQL Server you are using check out DBCC
DBREINDEX and INDEXDEFRAG in Books Online if you are using SQL 2000 also DBCC
SHOWCONTIG which has an example script to only reindex indexes that are
fragmented by a certain amount. If using SQL 2005 check out the ALTER INDEX
statement and the sys.db_index_physical_stats view.
John
"Agnes" wrote:

> In vfp database, i can use the command ' reindex' to index the database
> again.
> for SQL server, can i do that ?
> thanks a lot
>
>
|||sp_MSforeachtable 'dbcc dbreindex(''?'')'
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Agnes" <agnes@.dynamictech.com.hk> wrote in message
news:e$dcsir9GHA.2268@.TK2MSFTNGP05.phx.gbl...
> In vfp database, i can use the command ' reindex' to index the database
> again.
> for SQL server, can i do that ?
> thanks a lot
>

index database

In vfp database, i can use the command ' reindex' to index the database
again.
for SQL server, can i do that '
thanks a lotCan you explain what such a "reindex" is supposed to achieve? It is possible
that you are looking
for the DBCC DBREINDEX or DBCC INDEXDEFRAG (2000) or for 2005 ALTER INDEX wi
th either the REBUILD or
the REORGANIZE options.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Agnes" <agnes@.dynamictech.com.hk> wrote in message news:e$dcsir9GHA.2268@.TK2MSFTNGP05.phx.g
bl...
> In vfp database, i can use the command ' reindex' to index the database ag
ain.
> for SQL server, can i do that '
> thanks a lot
>|||Hi Agnes
You don't give the version of SQL Server you are using check out DBCC
DBREINDEX and INDEXDEFRAG in Books Online if you are using SQL 2000 also DBC
C
SHOWCONTIG which has an example script to only reindex indexes that are
fragmented by a certain amount. If using SQL 2005 check out the ALTER INDEX
statement and the sys.db_index_physical_stats view.
John
"Agnes" wrote:

> In vfp database, i can use the command ' reindex' to index the database
> again.
> for SQL server, can i do that '
> thanks a lot
>
>|||sp_MSforeachtable 'dbcc dbreindex(''?'')'
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Agnes" <agnes@.dynamictech.com.hk> wrote in message
news:e$dcsir9GHA.2268@.TK2MSFTNGP05.phx.gbl...
> In vfp database, i can use the command ' reindex' to index the database
> again.
> for SQL server, can i do that '
> thanks a lot
>

Monday, March 26, 2012

index rebuilds

index rebuilds

index rebuilds

Index Rebuilding

Index Rebuilding

Index rebuild

Monday, March 19, 2012

Index Monitoring

Monday, March 12, 2012

Index is being rebuilt

Wednesday, March 7, 2012

index database

Friday, February 24, 2012

index database

index database

index and key

Blog Archive

About Me