I am trying to manage some SQL server 2005/2008 boxes which have performance issues. The applications running on top of sql server are BI/BW types which run overnight reports/queries. They struggle to complete before morning 9 am deadline & I have been asked to investigate. The app servers don't do much but I can see high CPU peaks overnight thru Windows performance monitor.
I can see there is an issue and now task is to underpin where exactly the problem is. It may be related to a rouge query, wrong parameters, unusable tables/indexes, slow disks/network or something else.
I wish to know if there is repository within SQL server very similar to Oracle grid control which snapshots hourly performance related data. If Yes, how can I access it?