April 3, 2003 at 4:04 pm
since when I have upgraded my SQL server 2000 from SQL 7 , I have very serious perforemace issue in my database. Query time has almost 3 times slower then used to be. I also Update the statistics on SQL 2000. Any expert advice.?????
April 3, 2003 at 9:52 pm
Please check the following:
1. memory usage on the server with sp_configure when compared to what used to be. If you don't know how much is used to be in SQL 7.0, you can change the memory setting how much to be used by SQL Server on the Server.
2. How frequently Re-Indexing is being done using DBCC.
3. Is there anything changed from the application side?! Because this might affect the database performance.
4. properties of the databases, if its auto grow by percentage or by space, when compared to what it is earlier.
5. Is the Server same?!
Let us know the feedback......
Hope this Helps!!!
.
April 4, 2003 at 6:20 am
Statistics are stored differently in SQL2K. Index rebuild will fix it, or you can just run sp_updatestats. Other comments by mdamera are good!
Andy
April 4, 2003 at 7:12 am
Thnaks fro your valuable inputs, Can you Pl. tell me how I can check I have number of indexes same in both the datbases. I also needs to know what is the best way of rebuilding the index?. Sorry Guys I am new to SQL SERVER so bear with me?. I can use Enterprises manager and Query analyzer. So Pl. tell me the procesure to rebuiding of indexes.
April 4, 2003 at 2:25 pm
To rebuild an index you can execute DBCC DBREINDEX from QA. Check BOL for more info.
April 7, 2003 at 4:00 am
This will handle it for all tables in the database.
DECLARE TableCursor CURSOR FOR SELECT '' + TABLE_SCHEMA + '.' + table_name FROM information_schema.tables WHERE table_type = 'base table'
DECLARE @TableName varchar(255)
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT 'Reindexing ' + @TableName as DOING
DBCC DBREINDEX(@TableName)
FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
April 8, 2003 at 7:50 am
Hi All,
Thnaks fro your valuable inputs. I think I found the issue. I don't know how but when I I count number of indexes its not matching on both the server. Is there is any way I can reconcile number of indexes on both server. One more think when I tried to rebuild the index with the help of scrip provided by this forum it ran for 20 Hours for this Table, but for all other table its just taking few minutes?. Any help
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply