Things I learned today
I'm involved with some tuning as usually tends to be the case and amongst the work I've been doing a couple of interesting points arose.
Firstly we upgraded the memory in the production cluster, what was unusual for me was that we added more memory than we had databases, I've never worked a production server that way before so it was with some interest to monitor memory usage, what has developed so far is that memory usage has climbed to about 1.6 times the data size of the production database. I'd always wondered about being able to sit a database in cache; we've obviously seen a dramatic drop in io and it appears there's also a drop in cpu - there's more time for monitoring to continue, it's a trended server so we have over a year of performance data collected so later on the comaprisions will be interesting.
Secondly, I've been looking at "worst" procs, this is a SQL 2000 server and the top worst report by io was using between 700k and 1 million io. The query itself is an 18 table join unioned to an 18 table join, when I extracted the plan almost every table was on the receiving end of a table/clustered index scan and there were no seeks at all. After eliminating parameter sniffing as a possible issue I started on the highest io table and added some indexes in an effort to remove the table scan ( 171k io ), however I didn't seem to be making much progress so i figured to port the database and query to SQL 2005, run the query a few times and make use of the index dmv's to give me a few clues.
I noticed after initial compilation the proc returned the result set ( 41 rows ) instantaneously, however the 2005 box is a bit more powerful and not getting the same usage as the 2000 dev/test box I was using so after a few calls I had a look at suggested indexes and to my surprise there was only one small index reported as missing.
Next stop was to examine the query plan and output the stats io to compare table io; to my surprise all my scans were now seeks and fair enough I had only one scan and this was the table shown to have a missing index. I couldn't be bothered to add all the io together but approx best guess probably puts it at around 100k or so ( there's some more changes to be made but I was looking for a quick win ).
Well we know the 2005 optimiser has been rewritten but it has to be said this was an absolute total shock, I'll be dropping in a SQL 2008 build and comparing here too, just for interest's sake.
Oh and before I get any comments, yes I'd updated stats, indexes and such on both databases and it was Enterprise 2000 vs Std 2005.