Interesting little improvement on a SQL 2005 system I encountered recently…..

Some background - this system had a fairly ‘traditional OLTP’ workload ie  heavily used during day – till around 9pm, then had a batch window for several hours, then not much activity in the early hours of the day, until normal workload resumed the following morning.

Using perfmon, I noticed that every morning, we would see a big spike in SQL Server I/O when the application started to be used...

As it was 2005 I decided to look at what tables were in cache before and after the overnight batch processing ran… ( using DMV equivalent of dbcc memusage that I posted earlier).

Here’s what I saw :-  

So, contents of data cache split fairly evenly between my 'important/heavily used' tables. 

 After this:- some application batch processing,backups, DBCC checks and reindexes were run.  A fairly standard batch I'd suggest.

Cache contents then looked like this :-

Hmmmm – most of cache is now being used by a table I’ve described as ‘unimportant’. Why ?

Well, that table was the last to be reindexed…. purely due to luck, as  the reindexing stored procedure performing a loop in alphabetical order through all application tables... 

When the application starts to be used again – all this ‘unimportant’ data has to be replaced in cache by data that is heavily used…

So, we changed the overnight reindex scripts –  the most heavily accessed tables are now the last to be reindexed.

Obvious really, but we did see a significant reduction in early-morning I/O after changing the order of our reindexing.