Troubleshooting SQL-Based Software

The company where I currently work produces a software solution that relies on SQL databases to store its information.  A lot of the issues I encounter on a daily basis have to do with performance: how come a certain module is taking so long to open or why is a certain procedure running long?

Issues with performance are exciting.  In a way, the process of finding optimal performance is a never-ending hunt.  Performance can be improved in a multitude of ways: by enhancing the manner in which the software’s graphic user interface interacts with the underlying tables, changing the indexes used on certain tables, rewriting the queries used to access information, etc.

The possibilities are endless!

But I’m getting ahead of myself.  With this post, I hope to focus on the method I use to determine the source of a performance issue.  Thus, I’ll be walking you through some of the steps I take when troubleshooting such issues.  I hope that these examples can be of use to you.

The Issue

Fictional e-mail:

Hi Moran,

 

I was trying to open a certain module in the application today and noticed that the process was running a bit longer than usual.  Do you know what could be the cause?

 

Thanks,

Boss

So, as copied above, I received a fictional e-mail from my manager this morning.  It seems that he’s having trouble opening a certain module in the application.  The module is able to open, but it takes a long time to do so–longer than it used to take.

Given that I am familiar with the make-up of this application, I know several things and can begin my troubleshooting there.  These are my assumptions:

  1. The software is built on SQL Server databases.
  2. Information within the software is accessed using queries that collect information from the SQL Server databases.
  3. This is not an issue with the configuration of the software given that opening this module is a routine operation.

With this in mind, I formulate my plan of attack.  I decide on the following:

  1. Recreate the issue on my local machine.
  2. While the issue is occurring, conduct a review of the SQL processes.
  3. Dig deeper, as necessary.

Step 1: Recreating the Issue

Given that my manager and I are able to connect to the same servers and access the same user account, I am able to easily recreate the issue on my machine.

In other instances, this might be harder to do. You may need to screen share with the user and watch as they recreate the issue or, in the case that the issue is very unique, have them provide you with a series of detailed steps on how they ran into the issue.

For those short on writing, I recommend using Screencast-o-Matic for quick and easy recording of your desktop. No installation necessary!

Step 2: Initial Backend Review

Once the issue has been recreated, the first thing I like to do is run the following command in SQL Server Management Studio:

sp_who2 active

You can run this command on any database.  The output provides you with a record of all active SPIDs on your server.  When running this, I’m interested in finding which SPID and process is associated with the issue I’m witnessing and whether there are any blocks in the system (represented by SPID values in the “BlkBy” column).  This is my output from running the command:

sp_who2 active with blk

The first thing I notice is that there are no blocks occurring in my system.

Next, I try to discern which activities are of interest to me.  I identify them by the information reported in the HostName, DBName, and LastBatch columns.  I am looking for SPIDs initiated from my machine (MNACHUM), in the “AdventureWorks” database, and that were launched about five minutes ago.  SPID 56 is the one I identify as corresponding to my issue.  SPID 55 comes close, but is actually connected to the sp_who2 command I just launched in SSMS; I am able to discern this fact by noting that the last batch for that SPID was executed just seconds ago.

As reported in my query output, SPID 56 is SUSPENDED.  This might be the reason why my manager’s application is taking a long time to open the module.  I do some more digging by running the following diagnostic command:

DBCC INPUTBUFFER(56)

The value in the parentheses references the SPID for the activity of interest.  The command, when run, returns information about the last command sent out by that particular activity.  This is my output:

dbcc inputbuffer

 

From this, I can discern that the issue is occurring with a particular stored procedure, dbo.sp_run_a_while.  However, this might be a long procedure with many moving parts.  To troubleshoot the exact cause of slow performance within the stored procedure, I’ll need to dig deeper.

Step 3: Digging Deeper

So, I’ve determined that the slowness witnessed by my manager is due to a particular stored procedure.  But what part of it?

If I am able to open the stored procedure, I could review it and look for a command similar to the one associated with the activity SPID reported by my sp_who2 command (if you go back and review the screenshot from earlier, you’ll notice that the associated command for SPID 56 is WAITFOR).

However, I don’t consider this method particularly efficient, since it requires scouring through the code of the stored procedure.  At times, these procedures can be thousands of lines and have many similar commands.

A better procedure to follow is to do one of two things:

  1. Query the sys.dm_exec_requests table with a CROSS APPLY on the sys.dm_exec_sql_text table to determine the last command that was run by the stored procedure.
  2. Set up and run a trace on the stored procedure to determine where it is getting hung up.

For the first option, you can use the following code I developed:

SELECT sql_handle,
statement_start_offset,
statement_end_offset,
plan_handle,
total_elapsed_time,
query_hash,
text
FROM sys.dm_exec_requests
CROSS APPLY sys.dm_exec_sql_text(sql_handle)

The last column (text) of the output specifies the last command initiated by any of the active SPIDs on your server.  Once again, be discerning in recognizing which of the rows actually corresponds to your particular SPID.  In my case, it is the second row:

cross apply

I can expand the column or copy and paste the text into the editor to review it further.

Alternatively, I can set up a trace to see where the procedure gets hung up.  To do so,

  1. From the top menu options, navigate to Tools –> SQL Server Profiler.
  2. In the new window, connect to your server.
  3. Open a new trace and configure it with the following Events under the Event Selection tab (make sure to check the “Show All Events” and “Show All Columns” options in the bottom right-hand corner to access these events):
    1. Maintain the default settings
    2. Under the Stored Procedure heading, select “SP: Completed”, “SP: Starting”, “SP: StmtCompleted”, “SP: StatementStarting”
    3. Under the TSQL heading, select “SQL: StmtCompleted” and “SQL: StmtStarting”
  4. Open the Column Filters window by selecting the option in the bottom right-hand corner and filter on the following items:
    1. ApplicationName: should be like the one for your SPID (i.e. MNACHUM)
    2. DatabaseName: should be like the one for your SPID (i.e. AdventureWorks)
    3. Duration: helpful for issues with slow performance, set to “Greater Than 4000”
    4. ObjectName: should be like your stored procedure (i.e. sp_run_a_while)
  5. Start the trace.

In order to capture the long-running event, you will need to recreate the issue again while the trace is running.

When I did so in my environment, I found that the command within the stored procedure causing my manager slow performance was

WAITFOR DELAY '15:00';

Now that I know what’s causing the slowness I can communicate with the writers of the stored procedure and begin discussions as to whether the command is necessary and what can be done to improve its efficiency.

(Obviously, a WAITFOR command by nature is inefficient in this kind of setting.  Maybe we’ll explore the troubleshooting of more complex queries in a different post.)

A big thank you to my colleague, Maxwell, who trained me and guided me on a lot of these troubleshooting techniques.

Moving Databases in SQL: Two Simple Methods

Recently, as part of a larger task, I was given the responsibility of moving databases from a permanent drive to a SAN drive.  This needed to be done due to the possible increase in the size of the associated DATA and LOG files.

I was assisted in this task by a colleague from my own team and another from my company’s database support group.  However, the process was fairly straight-forward and can probably be done by most anybody on their own.  I’ve included in this post two methods for carrying out the process: via SQL Server Management Studio (SSMS) interface and via commands in the query window.

For this example, I’ll move the AdventureWorks database on my machine from directory “C:\Location1” to “C:\Location2”.

I’ll start with the method that utilizes the SQL interface and then discuss how to do the same procedure using the commands in the query window.

Database: AdventureWorks
Current Location: C:\Location1
Destination Location: C:\Location2

Moving Databases Using SSMS Interface

    1. Open your Server Instance in SSMS, collapse the Databases option, and find the database that you would like to move in your directory.

 

    1. Highlight the database, right-click it, and open up Properties.  Navigate to the Files Tab.  This page shows you information about where the current DATA and LOG files for the database are stored.Image

 

    1. Use the Path information to find where the files are currently placed in your environment.  Open up the directory location and verify that the files are accessible.

 

    1. Close the Properties window in SSMS.  Right-click the database (i.e. AdventureWorks) and navigate to Tasks → Detach…

      In this window, select from the options available for database detachment.  There are two: Drop Connections and Update Statistics.  In order to properly move your database, you will need to drop connections.  Updating statistics can be done at your own discretion.

      Hit “OK” once you’ve made your selections.  The database should disappear from your collapsed database options in SSMS.Image

 

    1. In Windows Explorer, move your LOG and DATA files from their current directory to their new destination directory.  This is done outside of SSMS.Image

 

  1. Once the files have been moved, in SSMS right-click the Databases folder under the server instance name (i.e. MNACHUM\SQL2008R2) and select the Attach… option.

    In the new window, select the Add… option under the “Databases to Attach” frame.  A directory window will come up.  Navigate to the directory where you placed your DATA file and add it.  Information about the DATA and LOG file should be automatically populated in the bottom frame, “[DATABASE NAME] details”.Note: If you notice that the auto-populated information in the bottom frame is incorrect, you can use the ellipsis button next to the individual LOG and DATA file paths to change the files of reference. Image

    Once you have all the information configured correctly, press OK.  You’ve now re-attached the database (i.e. AdventureWorks) with the associated files in the new directory location.  You’ve effectively moved your databases!

Moving Databases Using SSMS Query Window

  1. Start by getting the information about the current database configuration, specifically the associated LOG and DATA file names, file locations, and current statuses.
    USE master
    GO
    SELECT name, physical_name, state_desc
    FROM sys.master_files
    WHERE database_id = DB_ID('AdventureWorks')

     

    Image

    Note: It’s a good idea to keep the results from this query open in one window as a reference while you run the other commands in this guide in a different window.

  2. Next, take the servers offline.
    USE master
    GO
    ALTER DATABASE AdventureWorks SET OFFLINE WITH ROLLBACK IMMEDIATE
    GO

     

    Note: The ROLLBACK IMMEDIATE portion of the command refers to any processes that may be running on the database at the moment of taking it offline.  Instead of stopping the process abruptly, each individual process will be rolled back to its most recent checkpoint.

    If you choose to simply take the database offline without the ROLLBACK IMMEDIATE option, SSMS will wait for all the current processes to get to a logical stopping point before taking the database offline.  This could take a while if you have long-running processes.  Alternatively, if you chose to go offline without the ROLLBACK IMMEDIATE option, you can kill the processes using the “KILL [SPID NUMBER]” option; however, work done by the process will be abruptly interrupted as opposed to rolled back.

    Ultimately, ROLLBACK IMMEDIATE is your friend and your best option in this case.

  3. In Windows Explorer, move your LOG and DATA files from their current directory to their new destination directory.  This is done outside of SSMS.
    Image
  4. In SSMS, update the LOG and DATA information associated with the database (i.e. AdventureWorks) using the following commands.
    USE master
    GO
    ALTER DATABASE AdventureWorks
    MODIFY FILE
    ( NAME = AdventureWorks_Data,
    FILENAME = 'C:\Location2\AdventureWorks_Data.mdf')
    
    USE master
    GO
    ALTER DATABASE AdventureWorks
    MODIFY FILE
    ( NAME = AdventureWorks_Log,
    FILENAME = 'C:\Location2\AdventureWorks_log.ldf') 


    Note
    : The field to be filled for NAME is the information provided under the “name” column in our first query.  In this case,  “AdventureWorks_Data” and “AdventureWorks_Log”.

  5. Put the database back online using the following command.
    USE master
    GO
    ALTER DATABASE AdventureWorks SET ONLINE
    GO 

     

  6. Lastly, using our initial SELECT query, verify that the database was properly transferred and that the physical_name column reports that the DATA and LOG files are now in the new location.
    USE master
    GO
    SELECT name, physical_name, state_desc
    FROM sys.master_files
    WHERE database_id = DB_ID('AdventureWorks')

     

    Image

    You’ve now moved the database via the query window.

A big thanks to Suresh Raavi, who originally posted on this topic on SQLServerZest.com.

If you run into any issues or have any concerns regarding the methods suggested, please don’t hesitate to comment or contact me at morannachum@gmail.com.

Blog at WordPress.com.