PSSUG: Dark art of performance tuning scripts from Peter Ward


Earlier in January 2012 saw Peter Ward (WardyIT, SQL MVP, Awesome Guy) present for the Perth SQL Server User Group (PSSUG) for a full house. One of the requests was to get the scripts that Peter had created made available to all the members that attended.

So, here it is, you can find the scripts.

The T-SQL scripts include the following functions, which are obviously very handy when doing performance tuning on SQL Server:

  • Find what version of SQL Server you are running
  • Get an overview of what hardware you are running
  • Get a snapshot of the memory allocation
  • Check the SQL Server configurations
  • Get info about each database file
  • Check the Waits stats
  • Signal waits for CPU pressure
  • Check the Page Life Expectancy
  • Check the Buffer cache hit ration for default instance
  • Find high value for CACHESTORE_SQLCP (for ad-hoc query plans)
  • Find ad-hoc queries that are bloating the plan

I want to thank Peter for an extremely informative sessionĀ and well presented topic (as usual!).

Download the scripts here (.txt file):
PSSUG – Peter Ward SQL Server Performance Tuning Scripts