Sql server 2000

vishesh · 2006-09-26

Hi all,
I want some help abt indexes in the sql server.
and also i have just read abt the update statistic in slq 2005 is it possible in sql 2000.

Please reply.

kriki · 2006-09-26

It is also possible in SQL2000 (and also very much necessary for performance).

vishesh · 2006-09-26

Thanks kriki

how can i find any help regarding this.

I have never worked on indexes and updating statistics.

Please provide any feedback so that i can work on this topic.

Thanks

Marije_Brummel · 2006-09-26

This is from the SQL Performance DOC

SQL indexes rebuilt
You must rebuild SQL Indexes regularly for all Navision tables. You can use the following SQL Transact
to rebuild indexes:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
CREATE UNIQUE CLUSTERED INDEX "CompanyName$TableName$0" ON
"DatabaseName"."dbo"."CompanyName$TableName" ("Field1InPrimaryKey”,”
Field2InPrimaryKey”,…) WITH DROP_EXISTING
CREATE UNIQUE NONCLUSTERED INDEX "$SecondaryKey1" ON "DatabaseName "."dbo"."
CompanyName$TableName" ("Field1InSecondaryKey1","Field2InSecondaryKey1",…) WITH
DROP_EXISTING
CREATE UNIQUE NONCLUSTERED INDEX "$SecondaryKey2" ON "DatabaseName "."dbo"."
CompanyName$TableName" ("Field1InSecondaryKey2","Field2InSecondaryKey2",…) WITH
DROP_EXISTING
…
IF @@TRANCOUNT > 0
COMMIT TRAN

You must schedule the rebuilt of indexes for a table based on:
&#56256;&#56450; Whether or not records can be deleted or modified in the table.
&#56256;&#56450; Whether or not records are inserted sequentially in the index.
&#56256;&#56450; The number of indexes (keys) in the table.
&#56256;&#56450; The number of records in the table.

vishesh · 2006-09-27

Please reply this post
I need feed back for indexes and updating statistics

thanks and regards.

Marije_Brummel · 2006-09-27

What more do you need? Have you downloaded all doc's from the download selection and the Toolkit from partnerguide?

Waldo · 2006-09-27

Mark,

About rebuilding the indexes, can you have a look at this thread?
http://www.mibuso.com/forum/viewtopic.php?t=11653

There is a question about the execution plans which worries me a bit... .

Marije_Brummel · 2006-09-27

What is the exact worry? :?

Waldo · 2006-09-27

GoMad wrote:

Kriki,

a quick question before I deploy the weekly rebuild at one of our customers.

I was always told that you have to "warm up" a sql database by performing a database test. (Which loads the execution plan of all the indexes into the cache of the SQL Server)
At our customer this alone takes about 1 full day.

My question is: Is the execution plan still in the cach after I rebuild the indexes using the rebuild index task of SQL 2005?

Regards,

At our customer, when the database isn't warmed up, there are serious performance issues...

vishesh · 2006-09-27

Thanks to all of you for your valuable replies.
My concern is how to regarding the performance of the sql 2000.
How can i do indexing and also updating the statistics.

I have not got anything on the download section.

Please provide me some study material for the performance tuning for Navision on sql server options.

Thanks and Regards.

Marije_Brummel · 2006-09-27

http://www.mibuso.com/dlinfo.asp?FileID=356

This is the SQL doc.

Do you have access to partnersource for the Toolkit?

Marije_Brummel · 2006-09-27

Waldo wrote:

GoMad wrote:

Kriki,

a quick question before I deploy the weekly rebuild at one of our customers.

I was always told that you have to "warm up" a sql database by performing a database test. (Which loads the execution plan of all the indexes into the cache of the SQL Server)
At our customer this alone takes about 1 full day.

My question is: Is the execution plan still in the cach after I rebuild the indexes using the rebuild index task of SQL 2005?

Regards,

At our customer, when the database isn't warmed up, there are serious performance issues...

After some investigation and some pm's we have found out that after rebuilding the index the execution plan get's an obsolete flag. This means that when the query will be executed a new execution plan will be generated.

To avoid this runtime, the execution plan needs to be recompiled after rebuilding the indexes.

More information at:

http://www.sql-server-performance.com/n ... istics.asp

http://msdn2.microsoft.com/en-us/library/ms181055.aspx

Does anybody have some statemens for that?

vishesh · 2006-09-28

Hi all,

Thanks for the material link posted by by regarding the performance tuning.

But after going through the material it seems it require insight into sql server. I m new for database administrator.

Let me do some homework and if I have any query ll let you guys know.

Thanks

Waldo · 2006-09-28

Good luck and keep us posted

vishesh · 2006-09-29

Hi all,

I have done the following things to maintain the indexes in sql server 2000.
In the Enterprise Manager on the rightclick of the table i got the manage indexes in the all task.
There all the keys defined on the table are shown. and i can edit any key i like, for that i just click on the key and then click edit and i have shown the all the field in the key now if i click on ok then it takes some time and process is successful.

I have certain questions to ask:

Is this the index management.

There is a boolean field there where it asked abt the dont recompute statistics(not recommended) its clearly says that if i click this flag then the statistics ll not be recomputed.

Second question is regarding this flag is this the statistics i was referring in my previous post.

Sorry for giving so much details

Please give some guidance for this.

Thanks and Regards.

kriki · 2006-10-03

NEVER touch the Navision-tables from enterprise manager! You can (at best) lose the things you changed in enterprise manager.

For Index-statistics and rebuilding, you need to create a SQL job or a SQL maintainance plan. The SQL-wizards can be handy to do it.

Sql server 2000

Comments

Categories