As promised I’m posting the code we used for doing some of our analysis for tuning SQL Server applications. In today’s webcast we started out by assuming we didn’t know much about the schema we were working with, so we captured a profile trace of what was running on the server (to simulate the server load I ran a Load Test against the Adventure Works web application with a couple different scenarios). From that trace we ran some queries to give us an idea of which stored procedure calls are the most expensive in terms of CPU, reads and writes.
Next we took a look at Data Velocity or the rate at which our database is growing. We did this by creating a script to count the tables with a timestamp and persist that info in the database for future use. Next we wrote some create SQL to show us the rate of growth of that data.
Finally we used the trace capture as a feed into the SQL Performance Tuning wizard to see what the system found as places we could make changes to get better performance.
If you want to see the webcast you can get to it by clicking here, and you can download the code by registered for the site and going to the downloads section.