SQL profiler usage
SQL profiler is a great tool for finxing problems with your database. It can highlight where errors are occurring, and also to some extent assist with improving performance.
I say to some extent because, once you get running really fast, you need to take into account that profiler does slow down the responses coming from your database. My policy on this is to always test and improve on a slow running machine or virtual machine and then deply the changes to your fast server. Simply throwing more CPU and memory requires you to test with larger volumnes and makes you work harder rather than your tools.
· First you'll need SQL management studio, either the full version or a developer version.(SQL express wont have this)
· Connect to the database using SQL management studio. (You could try remoting to the server and using the management studio thats installed there if it's not on your machine)
· SQL profiler can be found in [Start]->[Programs]->[Microsoft SQL 2005]->[Performance Tools] or through the [Tools] menu option in SQL management studio it's self.
· On initial startup you are presented with a blank screen. You need to create a new trace.(process whereby data is collected)
Click [File]->[New trace]
Select your database server to connect to (usually the one your working on) Note: you will probably need Admin permission or the correct settings to do this.
· Use the standard template if this is your first attemt, but click on the [Events Selection] Tab to have a look what's available.
Here you can add events to be monitored by checking the check boxes. More options are available if you choose "Show all events" and/or "Show all columns".
You can also add filters to narrow down what your looking for by right clicking over a column header and selecting "Edit column filter". I mainly use this to select long running events eg: over 60ms initiallly to filter out neglegable actions.
· When you select okay, the trace starts and the events are displayed onscreen.
· Pause/Stop/COntinue buttons are available at the top to allow you to brows the captures data once it's come in.
· You can use this to gather information on what actions are taking the longest to execute, or... you can save the trace for the Database tuning Advisor to process and make suggestions on inproving performance.
To do this clisk [File]->[Save as]->[trace file].
Once saved go to [Tools]->[Dataabse tuning advisor] and use this to analyse your trace file.