In our environment we´ve got a heavily loaded SQL Server as a trunk in the system. Most systems look like this and sooner or later you will probably want to troubleshoot bottlenecks in this database.

From a nice fellow at Microsoft we got handed the scripts from Jimmy May´s blog that gives us great detail in what procedures and queries amongst other things that takes the most time.

Check it out.