Re: sql 2000 - slow response times after creating a new database
From: Darren Green (darren.green_at_reply-to-newsgroup-sqldts.com)
Date: 03/17/04
- Next message: E. Davids: "Re: DTS in ASP"
- Previous message: Wayne Antinore: "XML from DTS Tranform task"
- In reply to: Paul Eccleston: "Re: sql 2000 - slow response times after creating a new database"
- Next in thread: Mario Splivalo: "Re: sql 2000 - slow response times after creating a new database"
- Messages sorted by: [ date ] [ thread ]
Date: Wed, 17 Mar 2004 13:58:39 -0000
SQL server does not create indexes automatically. You may be getting
confused with statistics here, which can maintained by SQL automatically.
Statistics are involved with choosing the query plan and such decisions may
be things like do I use an index or do I just scan the table. A very
simplistic example!
The command to create and index is CREATE INDEX .... This is documented in
Books Online.
Why not script off the old indexes and re-create on the new DB for a start
point.
Another option would be to run a profile trace and pass this through the
index tuning wizard which will then suggest indexes for you. Have a read of
Books Online for more info about the ITW.
-- Darren Green http://www.sqldts.com "Paul Eccleston" <paul.eccleston@get-rid-of-this-bell-centres.com> wrote in message news:4058147a_2@nnrp1.news.uk.psi.net... > hi darren thanks for that, your simple fix sounds like a reasonable idea, > wont sql 2000 create a new index after a period of time?, or do i have to > force it by issuing a command?, if so what would be the command to create a > new imdex?. > > paul. > > "Darren Green" <darren.green@reply-to-newsgroup-sqldts.com> wrote in message > news:Kqi2D+WrV1VAFwgC@sqldts.com... > > In message <4057496f$1_2@nnrp1.news.uk.psi.net>, Paul Eccleston > > <paul.eccleston@get-rid-of-this-bell-centres.com> writes > > >hi i have recently migrated from sql 7.0 standard edition to sql 2000 > > >standard edition, this went ok and i ended up with 4 user databases and > the > > >usual system databases. I then encountered problems with the system and > user > > >databases due to them being different collations, so i used the procedure > of > > >scripting out the objects and then creating a new user database and re > > >imprting them, then using DTS to transfer the data across. What i didnt > copy > > >across was the index from the old database and since i carried out this > work > > >users have complained of slow response times, do i need to re-create an > > >index for this new database?, and if so what is the best way of doing > this?, > > >i dont require anything fancy like full text indexing, so just the basics > > >would be good. > > > > > >many thanks for any responses!!. > > > > > >paul. > > > > > > > > > > Without indexes then the query plan will be using less efficient table > > scans to match your query results. Indexes allow you to match the > > results faster as they can quickly find the matching data as opposed to > > scanning the entire table. > > > > I strongly suggest your read some books on databases or even Books > > Online to gain a greater understanding of the importance of indexes and > > tuning query performance. There are additional resources available on > > various sites and the Microsoft site itself. > > > > For simple fix, script off the old indexes and re-create them on the new > > DB. > > > > -- > > Darren Green (SQL Server MVP) > > DTS - http://www.sqldts.com > > > > PASS - the definitive, global community for SQL Server professionals > > http://www.sqlpass.org > > > >
- Next message: E. Davids: "Re: DTS in ASP"
- Previous message: Wayne Antinore: "XML from DTS Tranform task"
- In reply to: Paul Eccleston: "Re: sql 2000 - slow response times after creating a new database"
- Next in thread: Mario Splivalo: "Re: sql 2000 - slow response times after creating a new database"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
