July 2, 2009 at 7:28 am
HI masters,
A question that i have is related to the procedure that i will do to upgrade my databases from SQL server 2000 to SQL Server 2005.
I have two diferente databases , suppose one is called db1 and the other is called db2.
I have db1 and db2 on several diferente places.
db1 is placed in at least 30 differente places (physical places) and db2 the same.
We don't have any DBAs there to do the upgrade, so i need to do like this:
1) i ask for a person of my company in each place , to backup the SQL Server 2000 database.
2) with the backup made in the earlier step, restore it on a SQL Server 2005 engine.
3) run a script that i will send to do the rest of the work (convert to 2005).
This script will change the compatibility level to 90 and make many other things like see if the auto update statistics are ON, and if not, it puts to ON.
But i have a dought about indexs.
Should the script recreate the index of the entire databases? what about the statistics?
thank you,
Pedro
July 2, 2009 at 7:39 am
Update all statistics. With fullscan if possible
Run CheckDB with the data_purity option
Set page verify to checksum
Run DBCC updateusage
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 2, 2009 at 10:25 am
Thank you very much for reply master.
I have seen your recomendations and i want to apply all to my script that will convert the databases to SQL Server 2005.
As to the options:
Run CheckDB with the data_purity option - I inserted in my script
Set page verify to checksum - it was already on my script
Run DBCC updateusage - I insert in my script
No doughts about it.
But i still have questions about update statistics.
The first question is :
Do i realy need to update statistics in the script? my database as the option auto_update statistics ON.
If the answer is yes, i have second question:
is this command the correct one:
UPDATE STATISTICS table_name WITH FULLSCAN, NORECOMPUTE
Do you know any way that i can update my statistics without having to put a statement for each object that i want to update my statistics?
thank you,
Pedro
P.s - what about indexs? i don't need to rebuild my indexs? why?
Once again thank you very much
July 2, 2009 at 10:40 am
river (7/2/2009)
The first question is :Do i realy need to update statistics in the script? my database as the option auto_update statistics ON.
Yes. If it wasn't necessary I wouldn't have said that it was.
SQL 2005 keeps more detailed stats than SQL 200 did. While the 2005 optimiser can use the stats from SQL 2000, it does so badly. It can result in slow performance. If you just put autostats on, the stats will be updated sometime between immediately and never and, until they are, you're risking poor performance
UPDATE STATISTICS table_name WITH FULLSCAN, NORECOMPUTE
If you specify NORECOMPUTE, those statistics will never be automatically updated. The stats-level setting overrides the database level setting. If you run that command, you'll be setting all of the stats in your database to never update automatically.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 3, 2009 at 2:17 am
Ok master Gail,
In that case i will just use the command like this:
UPDATE STATISTICS table_name WITH FULLSCAN
Should i update only tables statistics? or should i update every table and every index statistics?
Are there any other object (other then tables and indexes) that i should update their statistics too?
Other question , even making this command in my script (UPDATE STATISTICS table_name WITH FULLSCAN) i will leave the option of auto_update_statistics set to ON , at database level, because i want that my statistics are updated automaticly, correct?
thank you,
Pedro
July 3, 2009 at 5:49 am
river (7/3/2009)
In that case i will just use the command like this:UPDATE STATISTICS table_name WITH FULLSCAN
Should i update only tables statistics? or should i update every table and every index statistics?
If you check Books Online, you'll notice that if you just specify the table name, all stats on that table are updated
Other question , even making this command in my script (UPDATE STATISTICS table_name WITH FULLSCAN) i will leave the option of auto_update_statistics set to ON , at database level, because i want that my statistics are updated automaticly, correct?
Absolutely, because if you set that option off, the stats will never be updated automatically and will get out of date unless they're manually updated on a regular basis.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 3, 2009 at 6:32 am
ok master,
But in this case, what i was asking was, what types of objects have statistics and i have to update?
Shoud i update statistics for all the tables in my databases and all indexes?
Are there any other objects type that i should update statistics too?
What about system tables and indexs of system tables present on my user database? should i update their statistics too?
thnk you
July 3, 2009 at 6:51 am
river (7/3/2009)
Shoud i update statistics for all the tables in my databases and all indexes?
As I said, if you check books online, you'll see that you can either update all stats on a table by specifying just the table name in which case all stats on that table are updated, or you can specify a table name and an index or statistics name, in which case just that set of statistics get updated.
Hence if you run it against all tables, everything will get updated.
Are there any other objects type that i should update statistics too?
No. Only tables have statistics.
What about system tables and indexs of system tables present on my user database? should i update their statistics too?
None of the system tables are accessible anymore, so you can't update stats on them
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 3, 2009 at 7:47 am
yes, that part of statistics i understud after you explained.
But in this case, what i was asking was another thing:
e.g:
if i do a comand like :
UPDATE STATISTICS table_name WITH FULLSCAN
All the statistics of this table will be updated with the option FullScan.
If i want this comand to run agains all my user tables i would create a cursor
that executes this comand for each table.
But , for instance i know that the comand below updates statistics too, but for all tables at once:
EXEC sp_updatestats
But i don't know if it as the same efeect as doing the other command but with cursor.
e.g:
does this command:
EXEC sp_updatestats
updates the statistics of all my tables with the FULLSCAN option?
What do you advice? using the first command and make a cursor to update the statistics? or can i just run the SP because the behavior is the same?
thank you master.
July 3, 2009 at 8:18 am
river (7/3/2009)
UPDATE STATISTICS table_name WITH FULLSCANAll the statistics of this table will be updated with the option FullScan.
Correct
But , for instance i know that the comand below updates statistics too, but for all tables at once:
EXEC sp_updatestats
But i don't know if it as the same efeect as doing the other command but with cursor.
It doesn't have the same effect. Check Books online.
sp_updatestats will only update stats that are considered to be out of date, same as the auto-update stats.
does this command:
EXEC sp_updatestats
updates the statistics of all my tables with the FULLSCAN option?
No it does not.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 3, 2009 at 10:37 am
Ok master,
One last check please, to validate if this part is correct:
My script (to convert the DB from 2000 to 2005) will include this two cursors:
use db_name
go
set nocount on
--Cursor that reindexes all the indexs of each table
declare @table varchar(256)
declare tables cursor for
select table_name from information_schema.tables
where table_type = 'base table'
open tables
fetch next from tables into @table
while @@fetch_status = 0
begin
exec('DBCC DBREINDEX ('+@table+', " ", 0)')
fetch next from tables into @table
end
CLOSE tables
DEALLOCATE tables
go
--Cursor that updates the statistics of each table
declare @table varchar(256)
declare tables cursor for
select table_name from information_schema.tables
where table_type = 'base table'
open tables
fetch next from tables into @table
while @@fetch_status = 0
begin
exec('UPDATE STATISTICS '+@table+' WITH FULLSCAN')
fetch next from tables into @table
end
CLOSE tables
DEALLOCATE tables
Note - the first script reindexes all the indexes of the database because the database as fragmentation.
The option on DBCC DB reindex fillfactor is set to "0" because i don't know witch value i shoud put.
I read that if the fillfactor is set to "0" then the fill factor of each index will be exactly as it was befour it is rebuilt.
I know that DBCC will not be a future command in other DB engines, but because this is only to convert from 2000 to 2005 i think that this issue is no problem.
The other cursor updates the statistisc after the indexes are rebuil.
Can you please validate master?
thank you very much.
July 3, 2009 at 1:13 pm
river (7/3/2009)
Can you please validate master?
Looks fine.
I don't know why you're calling me 'master' though. Honestly, I'd prefer if you didn't use that, my name is at the bottom ad all my posts.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 5, 2009 at 9:00 am
I just call you master because of your knowledge.
But i will start to call you, for your name.
Thank you very much for all your help,
Pedro
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply