SQL Server 2005 came up with so many tools and ways to keep watch on the performance of your databases. Today I am going to introduce you one interesting tool of SQL Server 2005 which is SQL Server Profiler with which you can create a trace file or table to see what is going in your server at specific time, later on you can use that trace file/table in your Database engine tuning adviser to get help from SQL Server itself to know what area you need to improve.
Let us follow the simple steps given below and check database whether anything is going wrong.
1) Open SQL Server Profiler from Start-> All Programs->Microsoft SQL Server 2005-> Performance Tools->SQL Server Profiler
2.) Click on File->New Trace
3.)It will open Profiler windows where you have to give your SQL Server credential
4.) Give the Trace Name
5.) Select the template, there are many types of templates available, you have to choose right one for you.
6.) Since we can save trace into file/table, we will choose check box of “Save to File” give the name and path of the file, I have save the file to C drive with TestTrace name. have a look at below screen shot.
7.) Go to “Events Selection” tab from where you can select/deselect events and column, I kept everything as it is.
Have a look at it in screen shot below.
8.) click on “Run” button and it will start capturing data, please be informed that, tracing will make your server’s performance bit slow so use this whenever you really wants to do some analysis, don’t keep it running 24*7
9.) When you captured your desired data, stop tracing with red square button seen under “Windows” menu.
10.) Now open your “Database Engine tuning adviser” to get suggestion about how you can improve your T-SQL performance, observe screen capture given below.
11.) Give Name to your trace under “Session Name” and select “File” radio option as we have kept tracing in our file, don’t forget to give file name and path there. Since trace is server level operation, select database for “Workload analysis” and select database for tune.
12.) Once you select everything, click on “Start Analysis” button under “View” menu and you will see SQL Database Engine tuning adviser” to make some process, as soon as that process gets finish, you can see two new tab in right hand side with the name “Recommendations” and “reports”. Under “Recommendation” tab, you will see what table of your selected database needs what, Indes, state update etc. you will not only see recommendation but you will see “Defination” of those recommendation as well and estimated improvement after doing those changes. Have a look at below screen