Too much I/O in the morning ?
- by steveh99999
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.
