It's a typical scenario, you have a client / database with a performance issue, and there isn't much information available about what might be wrong apart from 'it's going slowly'. Here is what I tend to do:
- Understand the environment in which the database is running, hardware, installation and network wise
- Try and get more information about where the performance problem lies (i.e. are there specific operations that are slow, is it slow at particular times of day etc)
- Understand as much as possible the applications that are using the data, and find out how they interact with the database
- Get a good handle on any existing maintenance plans that may or (usually) may not be in place
After that, I would start visiting the DMVs (for example
[sys].[dm_exec_query_stats]) to see if there are any obvious outliers, then go onto the plans and inspect those to see if there are any obvious problems.
Where would you start, given a problem like that? Do you find that you have a set of 'starter scripts' that give you some data that you can begin to work with?
The reason I ask is that I had exactly this scenario a couple of weeks back, and I thought I would ask you guys just to make sure I'm not going about it a completely numpty way!