Open topic with navigation
Updating database statistics
Accurate and up-to-date statistics are important to the efficient performance of SQL Server. we recommend that the database administrator update statistics for the Chancery SMS system's database at least once a month. When updating statistics, it is also useful to recompile the stored procedures. The database administrator can use a script in Query Analyzer for these tasks.
|
1
|
In Windows, from the Start menu, select All Programs > Microsoft SQL Server > Query Analyzer. |
|
2
|
Connect to the database. |
|
3
|
Enter the following script: DECLARE @table_name varchar(1000),@sql nvarchar(4000) declare c1 cursor for SELECT name FROM sysobjects WHERE xtype = 'U' open c1 fetch next from c1 into @table_name while @@Fetch_Status = 0 begin Select @sql = 'UPDATE STATISTICS ['+ @table_name +'] WITH FULLSCAN' exec sp_executesql @sql fetch next from c1 into @table_name end close c1 deallocate c1 GOdeclare @cmd nvarchar(500)declare cur_sp cursor fast_forward read_only local for select 'exec sp_recompile ['+object_name(o.id)+']' from sysobjects owhere o.xtype = 'U' open cur_sp fetch next from cur_sp into @cmdwhile @@Fetch_Status = 0 begin exec sp_executesql @cmd fetch next from cur_sp into @cmdendclose cur_spdeallocate cur_sp go
|
Pearson
Always Learning
www.pearsonschoolsystems.com
Tel: 866-434-6276
Email: psstechsupp@pearson.com
[You can do this if your role is District Administrator.DA]