Using the Tuning Advisor to to improve SQL performance

This post is about how to use the DataBase Engine Tuning Advisor.

Performance is a key issue in solutions and performance has very much to do with calling SQL procedures and avoiding deadlocks so tuning up SP can significantly boost performance.

SQL provides the DataBase Engine Tuning Advisor which is a tool to help indexing SQL tables.

In order to evaluate the DB performance we need to have a sample to tune on, this is where the SQL Sever profiler comes in.

How to create a Trace file:

1. On the SQL management studio go to tools -> SQL Profiler.

2. Start a new trace make sure you specify a file to save to and use the Tuning template

clip_image0018_thumb4[10]

3. Select the Events Selection tab and click on Column Filters to bring up the Edit Filter window, now specify the name of the DB to trace

clip_image002_thumb6

4. Press run to start tracing, now is the point where you need to start using your application, in my case pushing messages in to the BizTalk server and the more the better try to cover as much scenarios as possible especially scenarios that interact with SQL Server so the trace can have good statistics.

clip_image003_thumb2

5. After letting the systems run for some time (In my case I let the system run for a couple of minutes)  we can stop the trace and now its time to use the tuning advisor, From the SQL management studio/or the profiler itself go to tools -> DataBase Engine Tuning Advisor.

6. Create a new session specify the trace file created in the Profiler, select the workload database which is you DB and select the same DB to tune(I selected all tables) then press the Start Analysis button. should look like the following

clip_image004_thumb6

7. When the analysis is finished we get a recommendation of which tables to index and actually the script itself, notice the estimated improvement percentage on the top left and bellow are some other screen shots of the report that was generated

clip_image005_thumb2

The reports, other reports are available in the select report drop down

clip_image006_thumb2

After creating the indexes and running the tuning advisor again I managed to get 0% estimated improvement, Note before creating the indexes one of the SP had occasional Dead Locks, after indexing that never happened again.

A good reason to use the tuning Advisor is to sort out Dead Lock errors (in my case I over indexed several columns which can actually decrease performance).

Advertisements

Raise an error in a Stored procedure

How to raise an error on an SP and catch the exception in code

Some times, especially when working in a transactional environment,  we wound need to raise an exception from our SQL server Stored Procedure deliberately and catch this exception, for example, in a c# application within a try catch block in order to Roll Back the changes.

This is common behaviour when having the .NET code manage the transaction logic, when the code will call several Stored Procedures and the Roll Back is coordinated in code.

so our call to the DB will look something like this:

using (SqlDB db = new SqlDB(BusinessLogic.Constants.ConnectString))
{
     try
     {
    SqlParameter p;
    SqlCommand cmd;
         using (cmd = new SqlCommand("usp_MyUsp", db.Connection, db.Transaction))
         {
              p = new SqlParameter("@id", SqlDbType.NVarChar, 50); p.Value = data.id; cmd.Parameters.Add(p);
              p = new SqlParameter("@data1", SqlDbType.NVarChar, 40); p.Value = data.data1; cmd.Parameters.Add(p);
              p = new SqlParameter("@data1", SqlDbType.Int); p.Value = data.data1; cmd.Parameters.Add(p);
              cmd.CommandType = CommandType.StoredProcedure;
              cmd.ExecuteReader()
         }
    db.Commit();
     }
     catch (Exception ex)
     {
         db.Rollback();
     }
}

To raise an error in the SP that will be caught in the catch block we need to call the SQL server method RAISERROR() and immediately return a value. the Stored Procedure to raise the error would look something like this:


  DECLARE @DocID int;
  --find the id
  SELECT @DocID = id FROM table WHERE id = @id
    
  --if not exist send back an error       
  IF @DocID is null  
  BEGIN
      RAISERROR('ID not Found', 16, 1)
      RETURN -1
  END

note: concatenating the error message might create an error.

This example is very general, the two numbered parameters passed in to the method in this case (16 ,1) represent the severity and status of the raised error.

for more detailed information you can read it on the MSDN pages. http://msdn.microsoft.com/en-us/library/ms178592.aspx