Stored procedure taking long time to execute

This can happen due to many factors like lack of indexes, out of dates statistics, if it returns a large number of records, etc.

I observed a similar behavior recently in a production environment. The stored procedure had the code similar to the following;

    SELECT Col3   
    FROM dbo.Tab1  
    WHERE  Col1 = @Col1 
    AND Col2 = @Col2  
    AND RTRIM(LTRIM(REPLACE(REPLACE(REPLACE(REPLACE(CAST(Comments AS VARCHAR(2000)),'<br />',''),CHAR(10),''),CHAR(13),''),'&nbsp;',''))) <> ''  

Let me give you some background information about this table.

• Col2 and Col3 are UNIQUEIDENTIFIER
  
• Comments column is ntext and does not have indexes
  
• All three columns Col1, Col2 and Col3 has indexes
  
• Col2 and the PK is the clustered index of the table
  
• Table has 825,452,256 records (825 million)

The FORMAT function

Recently one of colleagues asked, how to format a date value according to the style he wants. His date style is as below;

10/15/13 - 04:11 pm

The immediate solution that came into my mind was the CONVERT function which you can specify the conversion style. He had already worked out a solution which using that function and the code is as below;

SELECT LEFT(Lower(CONVERT (VARCHAR, CURRENT_TIMESTAMP, 1) + ' - ' + RIGHT( 
       CONVERT ( 
       VARCHAR, CURRENT_TIMESTAMP, 100), 8)), 17) 
       + ' ' 
       + Lower(RIGHT(CONVERT (VARCHAR, CURRENT_TIMESTAMP, 100), 2)) AS 
       "Date/Time" 

However it is bit of complex string concatenation. Wouldn’t there be any simple way of doing it? So after bit of research I found the following code which does the exact same thing that my colleague wants.

DECLARE @d DATETIME = CURRENT_TIMESTAMP; 

SELECT Lower(Format(@d, 'MM/dd/yy - hh:mm tt')) AS 'Date/Time' 

Using FORMAT function, you can format numeric and date values. It also supports the culture. The syntax is as below;

FORMAT ( value, format [, culture ] )

SSMS Limitations

When performing admin tasks or development tasks, some people like to use SQL Server Management Studio (SSMS) while some are like to use scripts. As many of you know, SSMS has limitations when it comes to perform some tasks. I found such instance while setting up replication for a testing purpose.

The requirement is simple, I wanted to setup a replication for a table. The uncommon factor here is, the publisher and subscriber databases are same. I used a AdventureWorks2008R2 as the database.

Identifying slow running queries

This post is for SQL Server Database Administrators who have find difficulty in some situations when identifying slow running queries. The DBAs who work in production environment specially might have faced situations where users or other production monitoring teams complaining that suddenly they see a slowness in their systems. The “slowness in the system” a very generic term. This could be due to various reasons. Such as;

• Network issue
• Application server issue
• Database issue

As a DBA, it is a responsibility to make sure everything is clear in database perspective. Before start troubleshooting the broader phenomena, it is necessary to ask few questions to shed some light into the dark area. Below questions might be useful;

SET XACT_ABORT Option and Transaction Behavior

One of my colleagues asked what if a transaction has only BEGIN TRAN and COMMIT TRAN without a ROLLBACK TRAN. I did not have a proper answer on top of my head however I could remember the behavior is depending on the SET OPTION known as XACT_ABORT.
I initially tried the below query to test the above mentioned situation. Note that I did not change the default setting of XACT_ABORT option. The default is set to OFF.

“Table Alias”, How it behaves?

One of my colleagues asked me a question about a simple T-SQL query which uses table alias. See the below T-SQL code;

USE AdventureWorks2012
GO

SELECT E.loginID,HumanResources.Employee.JobTitle FROM HumanResources.Employee E

The above query used a table alias “E” and in SELECT list one column refers with table alias while the other column referring  full table name.  Seems like technically correct query. However the query returned the following error.

Msg 4104, Level 16, State 1, Line 2
The multi-part identifier "HumanResources.Employee.JobTitle" could not be bound.

As per the error message, use of table name, “HumanResources.Employee” to refer JobTitle column is incorrect. When you remove HumanResources.Employee in the SELECT list then the query works fine.

The theory behind this is when you have a table alias, it logical rename the table to table alias. So if you want to refer the table in the query, it needs to use table alias instead of the actual table name.

Relational algebra explains this more clearly.

The relevant operator for table alias in relational algebra (RA) is RENAME. As SQL derived from RA it is always better to learn RA before learning SQL.

How to get database sizes

There are several ways to get database sizes in a server. Following three system tables has the information to get database sizes.

sys.sysfiles

sys.database_files

sys.dm_db_file_space_usage

You also can use following system stored procedure.

exec sp_spaceused

However the easiest way is to use SP_HELPDB system stored procedure. Below script used that SP to get the database sizes in a server.

create table #spdbdesc

(
    dbname sysname,
    dbsize        nvarchar(13) null,
    owner sysname null,
    dbid    smallint primary key,
    created nvarchar(11),
    dbdesc    nvarchar(600)    null,
    cmptlevel    tinyint
)


INSERT INTO #spdbdesc
exec SP_HELPDB

SELECT *
FROM #spdbdesc