There are some references to other links on performance tuning on this page. This page serves as a reminder for me on how to start the process going for setting up a Sql trace. Here are my basic steps and the articles with some details on the whole process and logic are where I Go next.
- Capture a profiler trace for a short period while the server is active. This will identify the queries that constitute a typical workload.
- Import the trace results into a database table and analyze them to find the queries or stored procedures that are having the most impact on the server.
- Run those stored procedures in Management Studio against a test database, and examine the execution plans and query statistics.
- Use the execution plan and statistics to identify queries that need tuning and indexes that need creating.
- 5. Implement the changes and review the effects.
The below provides how to generate the first script. However, I would like to get one for each of the following catagories at some point:
ANALYZING AND DEBUGGING SQL STATEMENTS AND STORED PROCEDURES.
Monitoring slow performance.
General debugging and troubleshooting.
Auditing and reviewing security activity.
Analyzing Stored Procedures:
- Use Profiler to get us a “script”.
- Start Sql Profiler
- File-> New Trace
- Name it and – Chose Blank
- Click the events selection tab
i. Chose Stored Procedures-> RPC Completed
ii. Chose TSQL->SQL:Batch Completed
It will look like the below picture. Then Run it and then stop it.
Then Choose File->Export->Script Trace Definition->For SQL 2005 – 2008
Finally you can open the trace up using SSMS. However, there are a couple of changes that need to be made. Review:
Exporting a SQL Trace from Profiler to create a Performance data collector~! – This cannot be removed on the customer machine as of SQL 2008 SP2. Beware!
Short I know but these are my first two posts so hopefully i will have many more to come and I will get better at it> 🙂