- The 2005 backups restore cleanly onto R2.
- R2’s BI Studio upgraded the Reporting and Analysis Services solutions without any issues.
- The cubes all deployed and processed just fine.
- R2 BI Studio interacts fine with TFS 2008 version control.
I’m currently looking into dropping unused indexes to reduce unnecessary overhead and I came across a very good point in the excellent SQL Server MVP Deep Dives book that I haven’t seen highlighted anywhere else. I was thinking it was simply a case of dropping indexes that didn’t show as being used in DMV sys.dm_db_index_usage_stats (assuming a solid representative workload had been run since the last service start). But Rob Farley points out that the DMV only shows indexes whose pages have been read or updated. An index that isn’t listed in the DMV might still be useful by providing metadata to the Query Optimizer and thus streamlining query plans.
For example, if you have a query like this:
select au.author_name , count(*) as books from books b inner join authors au on au.author_id = b.author_id group by au.author_name
If you have a unique index on authors.author_name the Query Optimizer will realise that each author_id will have a different author_name so it can produce a plan that just counts the books by author_id and then adds the author name to each row in that small table. If you delete that index the query will have to join all the books with their authors and then apply the GROUP BY – a much more expensive query.
So be cautious about dropping apparently unused unique indexes.
The MSDN downloads for R2 appeared as promised yesterday. Congratulations to everyone on the SQL team. I must have got one of the first downloads of the Developer Edition and it was nice and fast. I’ve just downloaded Standard Edition and it’s still nearly as fast. Nice. I’m guessing they aren’t using GUIDs for the clustered indexes this time! 😉