How often shoud we run reorganize index and update statistics...

We have a 20 GB database and reorganize indexes and update statistics maintainance takes about 4 hours and the log files grows out of control what is a serious problem since it can not be truncated (database mirroring).

Ivan

First of all if your log file is growing it just means that it is not properly sized for your environment. In fact, have a proper sized log file will cut down the reorganize time.

If you have not changed anything to the autostats setting your statistics will be updated after enough records have changed for SQL Server to consider it usefull to update them.

Are you talking about a reorganize or a rebuild of your indexes? You should rebuild your indexes once in a while and the frequency entirely depends on the fillfactor and the amount of data that is changed between your rebuilds.

Depending on how much time it takes I would say reindex as frequently as you can afford to (daily/weekly?).

WesleyB

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

|||

I think we have an equivalent question : How often do we use indexes or querying database ?

What are the most importants queries used by users ? You can answer at this question.

If you can do a top of this queries you can reorganize/rebuild the indexes used by first queries of the top. Later or rarely another indexes.

From other part don't farget "avg_fragmentation_in_percent value " that implies if you do reorganize or rebuild of indexes (see Books Online).

You can do this thing using a job (or Database Maintenance Plan) nightly when the people sleep.

sql

How often an index is hit

Anyone have a script/process that would count how often indexes are actually
used? Are there sys optimizer tabs or logs or such?
Mike
If data falls in the woods and nobody is there to see it ...... ?Are you talking SQL 2005? If so then yes there are some dynamic management
views that give you all sorts of details for index usage. In 2000 there is
no built in way to do this.
Andrew J. Kelly SQL MVP
"Tigermikefl" <Tigermikefl@.discussions.microsoft.com> wrote in message
news:D209E173-1D85-4969-B2FE-C17A2F4B63A9@.microsoft.com...
> Anyone have a script/process that would count how often indexes are
> actually
> used? Are there sys optimizer tabs or logs or such?
> --
> Mike
> If data falls in the woods and nobody is there to see it ...... ?|||In SQL Server 2005 you have a variety of DMVs to use, for example:
DECLARE @.TableName SYSNAME;
SET @.TableName = N'YourTableName';
DECLARE
@.ObjectID INT,
@.DBID INT;
SELECT
@.ObjectID = OBJECT_ID(@.TableName),
@.DBID = DB_ID();
SELECT
-- s.* just to illustrate:
i.name, s.*
FROM sys.dm_db_index_usage_stats s
INNER JOIN sys.indexes i
ON s.object_id = i.object_id
AND s.index_id = i.index_id
WHERE s.database_id = @.DBID
AND s.object_id = @.ObjectID
AND i.object_id = @.ObjectID;
This will tell you number of and most recent scan/s/lookup/update, both
user and system. Variety of useful applications for this data, if you are
heavy into data mining for tuning opportunities.
"Tigermikefl" <Tigermikefl@.discussions.microsoft.com> wrote in message
news:D209E173-1D85-4969-B2FE-C17A2F4B63A9@.microsoft.com...
> Anyone have a script/process that would count how often indexes are
> actually
> used? Are there sys optimizer tabs or logs or such?
> --
> Mike
> If data falls in the woods and nobody is there to see it ...... ?|||Its surprising that we don't have any in SQL Server 2000

How much space should I give to tempdb for rebuilding index?

Hi, all.
I scheduled a schedule for index rebuilding for a database which is about
60GB.
I know that tempdb is required for index rebuilding, but don't know how much
space would be adequate.
How much space should the tempdb have for index rebuilding?
Any help would be appreciated.> I know that tempdb is required for index rebuilding, but don't know how
much
> space would be adequate.
That depends on how you rebuild the index. If you use DBCC INDEXDEFRAG or
DBCC DBREINDEX, then tempdb is not used. And this is what most are using.
Below section in the updated books online has some info:
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Kim Keuk Tae" <zyuuzika@.korea.com> wrote in message
news:ODELj%230pDHA.3504@.TK2MSFTNGP11.phx.gbl...
> Hi, all.
> I scheduled a schedule for index rebuilding for a database which is about
> 60GB.
> I know that tempdb is required for index rebuilding, but don't know how
much
> space would be adequate.
> How much space should the tempdb have for index rebuilding?
> Any help would be appreciated.
>
>sql

How much space does MS SQL 7.0 need for reorganizing data

We are running MS SQL 7.0 and we use a database maintenance plan that
does, among other things, the "Reorganize data and index pages" (under
the "Optimizations" tab). We have had this optimization fail for some
of the databases due to file space shortages. I have a couple of
questions that may help to resolve this issue and aid my understanding
of how MS SQL Server 7.0 allocates space.
Assumptions: We have a single database TEST which has a single
filegroup (PRIMARY) and that this filegroup has only two files (one
for the data and one for the transaction log). Furthemore, there is an
infinite amount of free disk space available to the system.
1. If the data file has size N (in megabytes) how much of this file
must be free space to guarantee that the above optimization will not
fail due to lack of space in the PRIMARY filegroup? (For example, if
the data file has size 1000 megabytes of which 700 is currently used,
is 300 megabytes enough free space?)
2. The same question for the database's transaction log file.
3. If the data and transaction files are full and the "Reorganize data
and index pages" is run (so, there is not enough space), will checking
the "Automatically grow file" property be enough to ensure that the
system will grow the two files sufficiently to allow the re-indexing
to complete? Or do the files have to have enough space _before_ the
re-indexing?
Thanks, A. LewenbergIt is not really the size of the data file, but the size of the largest
table you wish to re-index... You need 1.25 * table size to drop/recreate
the clustered index...If you intend to do 2 tables at the same time, you
have to have extra space for both... This is in addition to the current size
used by the tables. But the 1.25 * space is used only temporarily...
The log file shouldn't be much ( but i am not sure).
The reorging can use lots of log space however...same as the size of the
index (I think)...
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"A. Lewenberg" <adam@.macrotex.net> wrote in message
news:6bd7331b.0403011328.7618c1de@.posting.google.com...
> We are running MS SQL 7.0 and we use a database maintenance plan that
> does, among other things, the "Reorganize data and index pages" (under
> the "Optimizations" tab). We have had this optimization fail for some
> of the databases due to file space shortages. I have a couple of
> questions that may help to resolve this issue and aid my understanding
> of how MS SQL Server 7.0 allocates space.
> Assumptions: We have a single database TEST which has a single
> filegroup (PRIMARY) and that this filegroup has only two files (one
> for the data and one for the transaction log). Furthemore, there is an
> infinite amount of free disk space available to the system.
> 1. If the data file has size N (in megabytes) how much of this file
> must be free space to guarantee that the above optimization will not
> fail due to lack of space in the PRIMARY filegroup? (For example, if
> the data file has size 1000 megabytes of which 700 is currently used,
> is 300 megabytes enough free space?)
> 2. The same question for the database's transaction log file.
> 3. If the data and transaction files are full and the "Reorganize data
> and index pages" is run (so, there is not enough space), will checking
> the "Automatically grow file" property be enough to ensure that the
> system will grow the two files sufficiently to allow the re-indexing
> to complete? Or do the files have to have enough space _before_ the
> re-indexing?
> Thanks, A. Lewenberg

How much space does MS SQL 7.0 need for reorganizing data

We are running MS SQL 7.0 and we use a database maintenance plan that
does, among other things, the "Reorganize data and index pages" (under
the "Optimizations" tab). We have had this optimization fail for some
of the databases due to file space shortages. I have a couple of
questions that may help to resolve this issue and aid my understanding
of how MS SQL Server 7.0 allocates space.
Assumptions: We have a single database TEST which has a single
filegroup (PRIMARY) and that this filegroup has only two files (one
for the data and one for the transaction log). Furthemore, there is an
infinite amount of free disk space available to the system.
1. If the data file has size N (in megabytes) how much of this file
must be free space to guarantee that the above optimization will not
fail due to lack of space in the PRIMARY filegroup? (For example, if
the data file has size 1000 megabytes of which 700 is currently used,
is 300 megabytes enough free space?)
2. The same question for the database's transaction log file.
3. If the data and transaction files are full and the "Reorganize data
and index pages" is run (so, there is not enough space), will checking
the "Automatically grow file" property be enough to ensure that the
system will grow the two files sufficiently to allow the re-indexing
to complete? Or do the files have to have enough space _before_ the
re-indexing?
Thanks, A. LewenbergIt is not really the size of the data file, but the size of the largest
table you wish to re-index... You need 1.25 * table size to drop/recreate
the clustered index...If you intend to do 2 tables at the same time, you
have to have extra space for both... This is in addition to the current size
used by the tables. But the 1.25 * space is used only temporarily...
The log file shouldn't be much ( but i am not sure).
The reorging can use lots of log space however...same as the size of the
index (I think)...
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"A. Lewenberg" <adam@.macrotex.net> wrote in message
news:6bd7331b.0403011328.7618c1de@.posting.google.com...
> We are running MS SQL 7.0 and we use a database maintenance plan that
> does, among other things, the "Reorganize data and index pages" (under
> the "Optimizations" tab). We have had this optimization fail for some
> of the databases due to file space shortages. I have a couple of
> questions that may help to resolve this issue and aid my understanding
> of how MS SQL Server 7.0 allocates space.
> Assumptions: We have a single database TEST which has a single
> filegroup (PRIMARY) and that this filegroup has only two files (one
> for the data and one for the transaction log). Furthemore, there is an
> infinite amount of free disk space available to the system.
> 1. If the data file has size N (in megabytes) how much of this file
> must be free space to guarantee that the above optimization will not
> fail due to lack of space in the PRIMARY filegroup? (For example, if
> the data file has size 1000 megabytes of which 700 is currently used,
> is 300 megabytes enough free space?)
> 2. The same question for the database's transaction log file.
> 3. If the data and transaction files are full and the "Reorganize data
> and index pages" is run (so, there is not enough space), will checking
> the "Automatically grow file" property be enough to ensure that the
> system will grow the two files sufficiently to allow the re-indexing
> to complete? Or do the files have to have enough space _before_ the
> re-indexing?
> Thanks, A. Lewenberg

how much index are reasonable on a table?

I have a table with about 25 colums. In my application, we have provided an
interface through which , search can be made on different column, either
alone or with combination of different columns.
Now there are about 10 columns , upon which search can be made, either
indicidually colum, or combined colums.
Now in order to have a fast search, I have to apply index on different
columns.
Is it ok to apply index on all the 10 columns that are used in search or
what else is the solution to have a quick search. I cant change Database
schema.examnotes (SOHAIL MALIK@.discussions.microsoft.com)
writes:
> I have a table with about 25 colums. In my application, we have provided
> an interface through which , search can be made on different column,
> either alone or with combination of different columns. Now there are
> about 10 columns , upon which search can be made, either indicidually
> colum, or combined colums.
> Now in order to have a fast search, I have to apply index on different
> columns.
> Is it ok to apply index on all the 10 columns that are used in search or
> what else is the solution to have a quick search. I cant change Database
> schema.
Not all indexes might be useful. Say for instance that one column
you can search for is a gender column. If the search is for "all males",
then that index will not help anyway.
If searching for "all" is not meaningful, you may to want to constrain
users by saying "you must include one of A, B, C, D or E in the search",
where A, B, C, D and E are the column on which you have indexes.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||so it means, i should apply index on all 10 columns, that are used in search
.
"Erland Sommarskog" wrote:

> examnotes (SOHAIL MALIK@.discussions.microsoft.com)
> writes:
> Not all indexes might be useful. Say for instance that one column
> you can search for is a gender column. If the search is for "all males",
> then that index will not help anyway.
> If searching for "all" is not meaningful, you may to want to constrain
> users by saying "you must include one of A, B, C, D or E in the search",
> where A, B, C, D and E are the column on which you have indexes.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp
>|||In addition to Erland's suggestions, look at my answer in the
"paralel-universe" post. ;)
ML|||SOHAIL MALIK (SOHAILMALIK@.discussions.microsoft.com) writes:
> so it means, i should apply index on all 10 columns, that are used in
> search.
Nah, what I tried to imply that you should investigate whether all these
indexes are meaningful, and particularly whether searching for all rows
is meaningful.
The result of this investigate could very well be that you should index
all ten columns. I don't the table, nor the data distribution, so I
cannot say.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsql

how much index are reasonable on a table?

I have a table with about 25 colums. In my application, we have provided an
interface through which , search can be made on different column, either
alone or with combination of different columns.
Now there are about 10 columns , upon which search can be made, either
indicidually colum, or combined colums.
Now in order to have a fast search, I have to apply index on different
columns.
Is it ok to apply index on all the 10 columns that are used in search or
what else is the solution to have a quick search. I cant change Database
schema.Hi Malik
Changing the schema would be a better option. If you create too many indexes
it brings down the performance of the system.
Its not advisible to use too many indexes
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"SOHAIL MALIK" wrote:

> I have a table with about 25 colums. In my application, we have provided a
n
> interface through which , search can be made on different column, either
> alone or with combination of different columns.
> Now there are about 10 columns , upon which search can be made, either
> indicidually colum, or combined colums.
> Now in order to have a fast search, I have to apply index on different
> columns.
> Is it ok to apply index on all the 10 columns that are used in search or
> what else is the solution to have a quick search. I cant change Database
> schema.|||The OP sort of suggests changing the schema is not an option.
Indexes can affect performance negatively - that's true, yet in this
particular case they pretty much seem to be the only option.
Malik,
Consider using indexed views if appropriate, and/or full-text indexing (for
character-based columns) - both as an addition to existing indexes.
Of course another good option is to simply use the profiler and let the
index tuining wizard suggest appropriate indexes based on your workload.
ML|||Hi Malik,
As suggested indexed Views would be better choice. Create a unique clustered
index on the key numeric column. And add non clustered indexes on the other
columns.
Thanks,
Siva
"ML" wrote:

> The OP sort of suggests changing the schema is not an option.
> Indexes can affect performance negatively - that's true, yet in this
> particular case they pretty much seem to be the only option.
> Malik,
> Consider using indexed views if appropriate, and/or full-text indexing (fo
r
> character-based columns) - both as an addition to existing indexes.
> Of course another good option is to simply use the profiler and let the
> index tuining wizard suggest appropriate indexes based on your workload.
>
> ML

How many rows before optimizer will use index

Hi
2 questions for you Gentlemen ;-)
1:
I think I have heared that SQL Server optimizer (always ?)
will use a table scan, if the row count of a table is
under a certain value even if there exist an - othervise
appropriate - index, simply because this is considered
faster.
How many rows do a table need to have before indexes are
considered usefull - does a roughly value exist?
(I think I read the number 8000 rows once)
Is there any differences between indextypes (CL, NCL,
single/multicolumn...)in the minimum "required" row count
2:
In a table A with many rows a column colA1 exists (with
a "code type" value), referencing table B with few rows
containing the list of codes.
Though a FK (and sometimes used in queries, joins etc.)
woundn't it be superfluous to index the FK column colA1,
because of the relative bad/low selectivity of the
different domain values (both if an fairly even spread of
the different few codes are assumed or if one of the codes
are in majority).
kind regards
Jakob Persson
Denmarkactually,
SQL Server will almost always try to use an index unless
there are atleast 6-12 rows, and the plan involves a NC
index and bookmark lookups
see:
http://www.sql-server-
performance.com/jc_sql_server_quantative_analysis1.asp
for more info
2. if you don't expect a plan to use the index, it may not
be necessary. i am assuming you do not delete from table B.
>--Original Message--
>Hi
>2 questions for you Gentlemen ;-)
>1:
>I think I have heared that SQL Server optimizer
(always ?)
>will use a table scan, if the row count of a table is
>under a certain value even if there exist an - othervise
>appropriate - index, simply because this is considered
>faster.
>How many rows do a table need to have before indexes are
>considered usefull - does a roughly value exist?
>(I think I read the number 8000 rows once)
>Is there any differences between indextypes (CL, NCL,
>single/multicolumn...)in the minimum "required" row count
>2:
>In a table A with many rows a column colA1 exists (with
>a "code type" value), referencing table B with few rows
>containing the list of codes.
>Though a FK (and sometimes used in queries, joins etc.)
>woundn't it be superfluous to index the FK column colA1,
>because of the relative bad/low selectivity of the
>different domain values (both if an fairly even spread of
>the different few codes are assumed or if one of the
codes
>are in majority).
>kind regards
>Jakob Persson
>Denmark
>.
>|||Jakob Persson wrote:
<snip>
> 2:
> In a table A with many rows a column colA1 exists (with
> a "code type" value), referencing table B with few rows
> containing the list of codes.
> Though a FK (and sometimes used in queries, joins etc.)
> woundn't it be superfluous to index the FK column colA1,
> because of the relative bad/low selectivity of the
> different domain values (both if an fairly even spread of
> the different few codes are assumed or if one of the codes
> are in majority).
This is only true if the values of colA1 are evenly distributed. If the
data distribution is skewed (for example, many 'A' but few 'B'), then
the index can still be beneficial when querying on 'B'.
And (as Joe mentioned), the index is also useful when deleting rows from
the referenced table.
Gert-Jan

How long to create a non-clustered index?

I need to know how long it will take to create a non-clustered index given:
I know how many columns the index will affect, the column width in bytes, how many rows, an average row size, the number of hard disks, their speed in RPM, and they are setup in RAID 5.

Thanksdepends on lots of stuff|||What else do you need to know?|||there are too many variables. it could be 2 seconds to x number of hours. Hardware. configuration. is the table a terabyte in size or a megabyte in size. Is the column an int or varchar etc...(numbers process faster), what is already happening on the machine. Are the moons of saturn in alignment. is there a MLB conspiracy to have the cubs win the series this year now that red sox won.

just do it. if you are worried about what is going to ahppen to the computer, do it when everyone goes home or schedule it as job to run at midnight.|||The non-clustered index is created on 10 columns - each 100 bytes wide. The table is 6 X 10^6 rows and average 2000 bytes per row - so 12 X 10^9 bytes total. I have 5 15,000 RPM disks in RAID 5.

Take everything else out of the picture. I just need a mathematical way to estimate a best case/worst case scenario.|||I just need a mathematical way to estimate a best case/worst case scenario.

No you don't.

It's a bad idea in the first place

Don't do it.|||Can you explain how you came to this humble conclusion?|||I am tiddy bit curious about why anyone would need a 10 column index but I decided to keep my mouth shut.|||if memory serves (and it often does'nt) multi column idexes only do any good if all of the columns are searched in the same query and are queried in the same order that they appear in the index.

Seems to me something like this would be a performance hit on inserts and deletes and updates and such.

Am I close Brett.|||Can you explain how you came to this humble conclusion?

Let's see, a thousand byte + wide index...

I would think that would be very ineffecient.

Also, if you needed anything else from the row, I imagine, the optimizer might choose to ignore it anyway.

humble, not humble, whatever, it has nothing to do with it.

It's a bad idea.

Post the DDL of this table.

And who told you that you need that index?|||Seems to me something like this would be a performance hit on inserts and deletes and updates and such.

:eek:

Ya think?

Am I close to Brett.

Let's not start picking out wallpaper or anything... :)

But if you're buying...I'll have a 'rita on the rocks... :D

How long does it take to drop a FT index?

Yesterday I created a full text index on a single field in my database with
365000 rows in order to do some performance testing to see if moving to a
full text index would provide significant improvement over LIKE clauses for
looking up words in titles of products. I now need to create indexes on a
couple more rows, and so I need to drop the existing index and create a new
one. However, it's now 3 hours later and iSQL is still running my drop
statement. How long does it normally take to drop a full text index of 98045
unique words with 364704 items, totalling 13MB? If I right click on the
catalog in SQL Ent Manager and choose Properties it shows the catalog as
idle. No errors are being returned by iSQL, and there is nothing in either
the W2K Event Log or the SQL log to indicate there is a problem. Please help
:|
Dan
This should happen well within a minute. run sp_lock to see if there is any
locking.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Daniel Crichton" <msnews@.worldofspack.co.uk> wrote in message
news:emlT0SGrEHA.868@.TK2MSFTNGP10.phx.gbl...
> Yesterday I created a full text index on a single field in my database
with
> 365000 rows in order to do some performance testing to see if moving to a
> full text index would provide significant improvement over LIKE clauses
for
> looking up words in titles of products. I now need to create indexes on a
> couple more rows, and so I need to drop the existing index and create a
new
> one. However, it's now 3 hours later and iSQL is still running my drop
> statement. How long does it normally take to drop a full text index of
98045
> unique words with 364704 items, totalling 13MB? If I right click on the
> catalog in SQL Ent Manager and choose Properties it shows the catalog as
> idle. No errors are being returned by iSQL, and there is nothing in either
> the W2K Event Log or the SQL log to indicate there is a problem. Please
help
> :|
> Dan
>
|||"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:eX8NLVGrEHA.896@.TK2MSFTNGP12.phx.gbl...
> This should happen well within a minute. run sp_lock to see if there is
any
> locking.
I knew I'd forgotten to do something :|
Thanks for the help. I found a stuck connection from a process that ran a
9:26 last night that for reason hadn't cleared - the process ended after
only a few seconds, yet SQL Server was maintaining the lock. In the end I
had to restart the SQL Server service to get rid of it, then the index
dropped in 5 seconds.
Dan
|||You have to be very careful with locking. SQL FTS does apply row level
locking while doing populations which can aggravate deadlocks and
maintenance operations like the one you have discovered.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Daniel Crichton" <msnews@.worldofspack.co.uk> wrote in message
news:eSpySqGrEHA.452@.TK2MSFTNGP09.phx.gbl...
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:eX8NLVGrEHA.896@.TK2MSFTNGP12.phx.gbl...
> any
> I knew I'd forgotten to do something :|
> Thanks for the help. I found a stuck connection from a process that ran a
> 9:26 last night that for reason hadn't cleared - the process ended after
> only a few seconds, yet SQL Server was maintaining the lock. In the end I
> had to restart the SQL Server service to get rid of it, then the index
> dropped in 5 seconds.
> Dan
>

How I can make trigger to Add Row 4 to my data base?

Hi

I need help about this problem

I have a data grid view like this:

Index

Code(releted to other table)

Name

Price +

Price _

1

1

BMW X5

1000000 $

2

1

BMW X3

500000 $

3

1

BMW Z3

250000 $

4

1

SUM

1750000 $

Row 4 must add by code automatically.
How? I must Use trigger or . . .?

my user may want to edit the data (some row OR price) I have a Problem.

How I can make a Code for Make SUM add row 4 and Delete this row when it loaded to edit? (Select and delete)

Or I can do some thing better than delete the row and write it again?

This doesn't seem like a SQL Server problem. If you do not want to persist the results in the backend you should do it in the client-side and add the extra row to the DataGrid from code. You should post this question in the Visual Studio or .NET Framework newsgroups.