0

I have two big tables in my database and for tests i have copied the tables (select * from online_table to backup_table with (NOLOCK) ) to the same DB with postfix _backup. On the backup-tables i start a conversion of a nvarchar(50) column to datetime format. After them i set up primary key and some indexes. Before i start the copy and conversion and setup of indexes i set the Database Backup to Simply and after the steps back to Full backup mode.

The problem is: the database is very slow on the online tables. the stored procedures which have a join between the two tables needs often more than one minute to finish. Normaly they finished in one second or less. One hour after the described steps there are no more problems with speed with the stored procedures. They work as before, the indexes are all good, less than 1 % fragmentation, i copy the table with nolock, so it should have no effects to the online tables. Do anybody know the problem and maybe have a solution? or have other way to do this steps?

Draco Mraz
  • 61
  • 1
  • 7

1 Answers1

0

This may be due to the caching in memory. SQL server uses ram to cache the stored procedure. so once the stored proc is cached, it should be back to normal. Brent explains caching of stored proc nicely, http://www.brentozar.com/archive/2013/12/how-to-cache-stored-procedure-results/

Shai
  • 1
  • 1