Tricks to increase SQL Server query performance


Thanks to the natural language roots of the SQL language, writing queries has become extremely easy for just about anyone to pick up. But its simplicity also makes it easy to write poorly performing queries. Here are some simple changes you can make to improve not only query performance, but, in some cases, overall SQL Server system performance as well.

CREATE TABLE vs. SELECT INTO

Oftentimes, within stored procedures or other SQL scripts, temp tables must be created and loaded with data. When writing these queries, many SQL Server DBAs and developers like to use the SELECT INTO method, like this:

SELECT *
INTO #TempTable
FROM sysobjects

While this technique works fine for small tables, when dealing with large record sets or long-running queries, it creates locks on the system objects within the tempdb database. As a result, other queries and procedures that need to create objects within the tempdb database will have to wait for the long-running query to complete. This is because when an object is created, an exclusive lock is taken against the sysobjects, syscolumns, sysindexes, etc system tables (SQL Server 2000) or the sysallocunits, syscolpars, syshobtcolumns, sysschobjs, sysserefs, etc system tables (SQL Server 2005). You can see this easily by opening two query windows and running the following:

(First window)
begin tran
create table #test1 (c1 int)
(Second window SQL 2005)
select object_name(rsc_objid), *
from sys.syslockinfo
where req_spid = 52 /*Where 52 = the SPID of the first window*/
order by 1

(Second window SQL Server 2000)
sp_lock 52 /*Where 52 = the SPID of the first window*/

When you have a very long-running query in a temporary table using the SELECT INTO format, those same system table locks are held until the query completes and data loads into the temp table. You can avoid system table locking by manually creating the table with the CREATE TABLE command—before loading the data into the table.
For example, this code…

CREATE TABLE #TempTable
(spid int)
INSERT INTO #TempTable
SELECT spid
FROM sys.objects

…will require much less locking than this code:

SELECT spid
INTO #TempTable
FROM sys.objects

While the total number of locks taken is the same, the length of time the locks are held for the first query will be much shorter. This allows other processes to create temp tables.
Typically, when developing SQL code the development server has only a single user or few users. When working on SQL code, it’s important to know when the code will impact sessions other than the current session. And unexpected interaction can cause major performance issues.

Accessing data across linked servers

Linked servers are an excellent way to get data in real-time from one server to another. However, incorrectly written linked server queries can quickly decrease system performance on one or both servers. While it’s easy to write these queries across linked servers, the query optimizer doesn’t always work as you would expect. I often see queries that join a local table to two remote tables and the queries take hours to run. That’s because the local optimizer doesn’t know which records to request from the remote table.
It therefore requests that the remote server transmit the entire table, and all that data is then loaded into a temporary table and the join is done locally. Unfortunately, because the local table is a temporary table—and not a physical table on the source system—the indexes on the remote table do not get created on the temporary table. Because of the lack of indexes, expected query execution time skyrockets.

There are a couple of techniques you can use to improve query response time. The first is to create a stored procedure on the remote database and have it return a record set, being a subset of the remote tables, which is then loaded into a local temporary table. It can then be indexed as needed. The trick with this method is to provide an input variable to the remote procedure where input values can be passed to. Thus, you will reduce the number of returned records by as much as possible. Fewer records will reduce the run time of that stored procedure as well as
the network latency on transferring those records from the remote system to the local system.
The second technique you can use is a variation of the first method. You create local temporary tables for each of the remote tables and transfer over the columns and records needed from each of the remote tables. Next, index the tables as needed and join the temp tables locally.
While the second technique is easier and faster to set up and implement, the first method gives you a greater performance savings, as typically less data needs to be transferred between servers.

Subqueries as join partners

When working with joins, you may want to manually control the order that data is selected. An easy (and usually safe) way to do this is to use subqueries as the join object instead of joining directly to a table. In some instances, you can decrease your query execution time by forcing the SQL Server to prefilter data in the table. This method is not foolproof and if used incorrectly it can increase the execution time of your query. The method should be fully tested before moving it to your production environment.

As we have seen, there are some quick and easy methods for improving query performance for some long-running processes. While these techniques will not apply to every issue you run across, they will help in some instances.

Prevent duplicate entry when using SqlBulkCopy


As we know, the fastest way to insert data in Database is to insert bulk data. SqlBulkCopy Class in System.Data.SqlClient Namespace provides simplest and fastest way to copy large amounts of data to SqlServer. Instead of inserting all data row by row, SqlBulkCopy process all data at once, thus making the insertions fast.

An example should be given below:

public void CopyData(DataTable TableToCopy, string SqlTableName)

{

        SqlConnection con = new SqlConnection(ConfigurationManager.AppSettings["conStr"].ToString());

if (con.State == ConnectionState.Open)

{

con.Close();

}

con.Open();

SqlBulkCopy bc = new SqlBulkCopy(con);

bc.ColumnMappings.Add(0, 0);

bc.ColumnMappings.Add(1, 1);

bc.ColumnMappings.Add(2, 2);

bc.ColumnMappings.Add(3, 3);

bc.ColumnMappings.Add(4, 4);

bc.ColumnMappings.Add(5, 5);

if (SqlTableName != “DBTable”)

{

bc.ColumnMappings.Add(6, 6);

bc.ColumnMappings.Add(7, 7);

bc.ColumnMappings.Add(8, 8);

}

bc.DestinationTableName = SqlTableName;

bc.WriteToServer(TableToCopy);

con.Close();

}

Here, WriteToServer() method, do bulk insert in database. So SqlBulkCopy is very useful in the case that when we want to insert number rows in the Database at the same time not one by one. But in some situation we are in need to check for Duplicate records while doing multiple insertion. But SqlBulkCopy does not provide any built-in method or event to do that.

One possible solution is to make the column(s) of the table unique. And for that we have to create a Index on the Table with column names that we want to check for Uniqueness.

Following is the example for the same:

 

CREATE INDEX myIndex on [Table Name]

(

[Column name1],  [Column name2], ..

)

WITH (IGNORE_DUP_KEY = ON)

 

This index would create a unique index for the columns specified and it checks automatically that if any duplicate rows are when we do BulkCopy. If yes then do nothing else inserts bulk data into the Database.

Alternative way to write dynamic SQL queries


Introduction

When we write Dynamic queries, most of the time only “WHERE” clause needs to be dynamic in the stored procedure rest of the part remains static, I mean “SELECT” and “FROM” Clause remain static most of the time.

To write dynamic query, we are choosing variables so that we can write “WHERE” clause using appropriate conditions and then putting a query in variables. Writing queries like this generates so much confusion while debugging and maintenance of the query.

Here is one of the process by which we can eliminate use of variable while writing queries which provide the same result as a dynamically written query provides.

We can use this technique if the “SELECT” list is going to be static and we need to take care of only “WHERE” clause. Otherwise we can use variables to store SQL query if the columns in the “SELECT” list will be generated dynamically and/or name of the source table will be decided at runtime. To understand this approach let us see one example.

Example

Original Stored Procedure:

We have one stored procedure as below

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

GO

— exec [SC_Report_GetDataByCustomerLounge] 0,1,0,’2009-01-01′,’2009-03-01′

— exec [SC_Report_GetDataByCustomerLounge] 0,0,0,”,”

ALTER PROCEDURE [dbo].[SC_Report_GetDataByCustomerLounge]

@CustomerId int = 0,

@LoungeId int = 0,

@CountryId int = 0,

@FromDate datetime,

@ToDate datetime

AS

DECLARE @StrQuery nvarchar(3000)

DECLARE @WhereCondition nvarchar(3000)

SET @WhereCondition =

BEGIN

IF(@CustomerId != 0)

BEGIN

SET @WhereCondition = @WhereCondition + ‘ Trans.CustomerId = ‘ + Convert(varchar,@CustomerId)

END

IF(@LoungeId != 0 AND @WhereCondition != )

BEGIN

SET @WhereCondition = @WhereCondition + ‘ AND Trans.LoungeId = ‘ + Convert(varchar,@LoungeId)

END

ELSE IF(@LoungeId != 0)

BEGIN

SET @WhereCondition = @WhereCondition + ‘ Trans.LoungeId = ‘ + Convert(varchar,@LoungeId)

END

IF(@CountryId != 0 AND @WhereCondition != )

BEGIN

SET @WhereCondition = @WhereCondition + ‘ AND Trans.CountryId = ‘ + Convert(varchar,@CountryId)

END

ELSE IF(@CountryId != 0)

BEGIN

SET @WhereCondition = @WhereCondition + ‘ Trans.CountryId = ‘ + Convert(varchar,@CountryId)

END

IF (@FromDate != ’01/01/1900′ AND @ToDate != ’01/01/1900′ AND @WhereCondition != )

BEGIN

SET @WhereCondition = @WhereCondition + ‘ AND Convert(varchar(10),Trans.AccessDate,101) >= ”’ +

Convert(varchar,Convert(varchar(10),@FromDate,101)) + ”’ AND

Convert(varchar(10),Trans.AccessDate,101) <= ”’ +

Convert(varchar,Convert(varchar(10),@ToDate,101)) + ””

END

ELSE IF (@FromDate != ’01/01/1900′ AND @ToDate != ’01/01/1900′)

BEGIN

SET @WhereCondition = @WhereCondition + ‘ Convert(varchar(10),Trans.AccessDate,101) >= ”’ +

Convert(varchar,Convert(varchar(10),@FromDate,101)) + ”’ AND Convert(varchar(10),

Trans.AccessDate,101) <= ”’ + Convert(varchar,Convert(varchar(10),@ToDate,101)) + ””

END

IF(@WhereCondition != )

BEGIN

Set @StrQuery = ‘Select Trans.TransactionId,Lounge.LoungeId,Cust.CustomerId,

(Cust.FirstName + ” ” + Cust.MiddleName + ” ”+ Cust.LastName ) As CustomerName,

Lounge.LoungeName,City.CityName,Country.CountryName,Cust.Address1,Cust.Address2,

Cust.MobileNo,Cust.EmailAdress,Airport.AirportName,Trans.AccessDate,Trans.Remarks

from dbo.CustomerInformation as Cust

Inner join dbo.TransactionMaster Trans ON Cust.CustomerId = Trans.CustomerId

Inner join dbo.LoungeInformation as Lounge ON Lounge.LoungeId = Trans.LoungeId

Inner join dbo.CityInformation as City ON City.CityId = Trans.CityId

Inner join dbo.CountryInformation as Country ON Country.CountryId = Trans.CountryId

Inner join dbo.AirportInformation as Airport ON Airport.AirportId = Trans.AirportId

WHERE ‘ + @WhereCondition

END

ELSE

BEGIN

Set @StrQuery = ‘Select Trans.TransactionId,Lounge.LoungeId,Cust.CustomerId,

(Cust.FirstName + ” ” + Cust.MiddleName + ” ”+ Cust.LastName ) As CustomerName,

Lounge.LoungeName,City.CityName,Country.CountryName,Cust.Address1,Cust.Address2,

Cust.MobileNo,Cust.EmailAdress,Airport.AirportName,Trans.AccessDate,Trans.Remarks

from dbo.CustomerInformation as Cust

Inner join dbo.TransactionMaster Trans ON Cust.CustomerId = Trans.CustomerId

Inner join dbo.LoungeInformation as Lounge ON Lounge.LoungeId = Trans.LoungeId

Inner join dbo.CityInformation as City ON City.CityId = Trans.CityId

Inner join dbo.CountryInformation as Country ON Country.CountryId = Trans.CountryId

Inner join dbo.AirportInformation as Airport ON Airport.AirportId = Trans.AirportId ‘

END

PRINT @StrQuery

exec sp_executesql @StrQuery

END

From above stored procedure we can see that SELECT query part is static and the query is building dynamically as per change in the WHERE Clause. The above query gives me the following output.

Result before applying the approach

Result before applying the approach

Alternative way to write same query:

We will be able to replace the above stored procedure using the following stored procedure and that will give same result as previous one.

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

GO

— exec [SC_Report_GetDataByCustomerLounge] 0,1,0,’2009-01-01′,’2009-03-01′

— exec [SC_Report_GetDataByCustomerLounge] 0,0,0,”,”

ALTER PROCEDURE [dbo].[Test]

@CustomerId int = NULL,

@LoungeId int = NULL,

@CountryId int = NULL,

@FromDate datetime = NULL,

@ToDate datetime = NULL

AS

DECLARE @StrQuery nvarchar(3000)

DECLARE @WhereCondition nvarchar(3000)

SET @WhereCondition =

BEGIN

Select Trans.TransactionId,Lounge.LoungeId,Cust.CustomerId,

(Cust.FirstName + ‘ ‘ + Cust.MiddleName + ‘ ‘+ Cust.LastName ) As CustomerName,

Lounge.LoungeName,City.CityName,Country.CountryName,Cust.Address1,Cust.Address2,

Cust.MobileNo,Cust.EmailAdress,Airport.AirportName,Trans.AccessDate,Trans.Remarks

from dbo.CustomerInformation as Cust

Inner join dbo.TransactionMaster Trans ON Cust.CustomerId = Trans.CustomerId

Inner join dbo.LoungeInformation as Lounge ON Lounge.LoungeId = Trans.LoungeId

Inner join dbo.CityInformation as City ON City.CityId = Trans.CityId

Inner join dbo.CountryInformation as Country ON Country.CountryId = Trans.CountryId

Inner join dbo.AirportInformation as Airport ON Airport.AirportId = Trans.AirportId

WHERE (Trans.CustomerId = Convert(varchar,@CustomerId) OR Trans.CustomerId = NULL)

AND (Trans.LoungeId = Convert(varchar,@LoungeId) OR Trans.LoungeId = NULL)

AND (Trans.CountryId = Convert(varchar,@CountryId) OR Trans.CountryId = NULL)

AND (Convert(varchar(10),Trans.AccessDate,101) >= Convert(varchar,Convert(varchar(10),@FromDate,101))

AND Convert(varchar(10),Trans.AccessDate,101) <= Convert(varchar,Convert(varchar(10),@ToDate,101)))

END

The above stored procedure gives me the following output:

Result after applying the approach

Result after applying the approach

The benefits of using this technique are:

  • It will reduce the complexity of query.

  • Using this, we can easily maintain the query.

  • Syntax checking will be much simpler as most of the time quote(‘) gives problem to novice developers when query is stored in a variable.

  • And most important, when we use LINQ –to-SQL in our application structure it will reduce the conversion error between ISingleResult and INT in the case of writing dynamic query.

References:

http://www.SQLServerCentral.com

A Script to Find Latest Database backup


USE MASTER

GO

IF OBJECT_ID(‘USP_DBA_RECENTBACKUPS’) IS NOT NULL

DROP PROC USP_DBA_RECENTBACKUPS

GO

exec USP_DBA_RECENTBACKUPS ‘NTERP’,‘R’

CREATE PROC USP_DBA_RECENTBACKUPS

@P_DBNAME VARCHAR(200)= NULL,

@PLAN CHAR(1)= NULL

AS

BEGIN

DECLARE @DB_NAME VARCHAR(200)

SET @DB_NAME=@P_DBNAME

IF @DB_NAME IS NULL

BEGIN

WITH

BACKUP_RECENT AS

(

SELECT MAX(BACKUP_DATE) BACKUP_DATE,MAX(ID) ID,[TYPE],SERVER_NAME,DATABASE_NAME

FROM

(

SELECT ROW_NUMBER() OVER (ORDER BY DATABASE_NAME,[TYPE],BACKUP_FINISH_DATE) ID,

BACKUP_FINISH_DATE BACKUP_DATE, PHYSICAL_DEVICE_NAME,A.MEDIA_SET_ID,

SERVER_NAME,DATABASE_NAME,[TYPE]

FROM MSDB.DBO.BACKUPSET A JOIN MSDB.DBO.BACKUPMEDIAFAMILY B

ON(A.MEDIA_SET_ID=B.MEDIA_SET_ID)

) BACKUPS

GROUP BY [TYPE],SERVER_NAME,DATABASE_NAME

),

BACKUP_ALL AS

(

SELECT ROW_NUMBER() OVER (ORDER BY DATABASE_NAME,[TYPE],BACKUP_FINISH_DATE) ID,

PHYSICAL_DEVICE_NAME

FROM MSDB.DBO.BACKUPSET A JOIN MSDB.DBO.BACKUPMEDIAFAMILY B

ON(A.MEDIA_SET_ID=B.MEDIA_SET_ID)

)

SELECT SERVER_NAME [SERVER],DATABASE_NAME [DATABASE],BAKUPTYPE=

CASE WHEN [TYPE]=‘D’ THEN ‘FULL’

WHEN [TYPE]=‘I’ THEN ‘DIFFERENTIAL’

WHEN [TYPE]=‘L’ THEN ‘LOG’

WHEN [TYPE]=‘F’ THEN ‘FILE / FILEGROUP’

WHEN [TYPE]=‘G’ THEN ‘DIFFERENTIAL FILE’

WHEN [TYPE]=‘P’ THEN ‘PARTIAL’

WHEN [TYPE]=‘Q’ THEN ‘DIFFERENTIAL PARTIAL’

END,BACKUP_DATE [RECENT BACKUP], PHYSICAL_DEVICE_NAME [LOCATION] FROM BACKUP_RECENT,BACKUP_ALL

WHERE BACKUP_RECENT.ID=BACKUP_ALL.ID

ORDER BY SERVER_NAME,DATABASE_NAME,BACKUP_DATE

END

ELSE

BEGIN

IF @PLAN = ‘R’

BEGIN

WITH

BACKUP_RECENT AS

(

SELECT MAX(BACKUP_DATE) BACKUP_DATE,MAX(ID) ID,[TYPE],SERVER_NAME,DATABASE_NAME

FROM

(

SELECT ROW_NUMBER() OVER (ORDER BY DATABASE_NAME,[TYPE],BACKUP_FINISH_DATE) ID,

BACKUP_FINISH_DATE BACKUP_DATE, PHYSICAL_DEVICE_NAME,A.MEDIA_SET_ID,

SERVER_NAME,DATABASE_NAME,[TYPE]

FROM MSDB.DBO.BACKUPSET A JOIN MSDB.DBO.BACKUPMEDIAFAMILY B

ON(A.MEDIA_SET_ID=B.MEDIA_SET_ID)

) BACKUPS

GROUP BY [TYPE],SERVER_NAME,DATABASE_NAME

),

BACKUP_ALL AS

(

SELECT ROW_NUMBER() OVER (ORDER BY DATABASE_NAME,[TYPE],BACKUP_FINISH_DATE) ID,

PHYSICAL_DEVICE_NAME

FROM MSDB.DBO.BACKUPSET A JOIN MSDB.DBO.BACKUPMEDIAFAMILY B

ON(A.MEDIA_SET_ID=B.MEDIA_SET_ID)

)

SELECT * FROM(

SELECT SERVER_NAME [SERVER],DATABASE_NAME [DATABASE],BAKUPTYPE=

CASE WHEN [TYPE]=‘D’ THEN ‘FULL’

WHEN [TYPE]=‘I’ THEN ‘DIFFERENTIAL’

WHEN [TYPE]=‘L’ THEN ‘LOG’

WHEN [TYPE]=‘F’ THEN ‘FILE / FILEGROUP’

WHEN [TYPE]=‘G’ THEN ‘DIFFERENTIAL FILE’

WHEN [TYPE]=‘P’ THEN ‘PARTIAL’

WHEN [TYPE]=‘Q’ THEN ‘DIFFERENTIAL PARTIAL’

END,BACKUP_DATE [RECENT BACKUP], PHYSICAL_DEVICE_NAME [LOCATION] FROM BACKUP_RECENT,BACKUP_ALL

WHERE BACKUP_RECENT.ID=BACKUP_ALL.ID AND DATABASE_NAME=@P_DBNAME AND [TYPE] IN (‘D’,‘I’)

UNION

SELECT

SERVER_NAME,DATABASE_NAME,BAKUPTYPE=‘LOG’,

BACKUP_FINISH_DATE BACKUP_DATE,PHYSICAL_DEVICE_NAME [LOCATION]

FROM MSDB.DBO.BACKUPSET A JOIN MSDB.DBO.BACKUPMEDIAFAMILY B

ON(A.MEDIA_SET_ID=B.MEDIA_SET_ID)

WHERE [TYPE]=‘L’

AND BACKUP_FINISH_DATE>

(

SELECT TOP 1 BACKUP_FINISH_DATE FROM MSDB.DBO.BACKUPSET WHERE [TYPE] IN (‘D’,‘I’)

AND DATABASE_NAME= @DB_NAME ORDER BY BACKUP_FINISH_DATE DESC

)

AND DATABASE_NAME= @DB_NAME ) AS RESTORE_PLAN

ORDER BY [RECENT BACKUP]

END

ELSE

BEGIN

WITH

BACKUP_RECENT AS

(

SELECT MAX(BACKUP_DATE) BACKUP_DATE,MAX(ID) ID,[TYPE],SERVER_NAME,DATABASE_NAME

FROM

(

SELECT ROW_NUMBER() OVER (ORDER BY DATABASE_NAME,[TYPE],BACKUP_FINISH_DATE) ID,

BACKUP_FINISH_DATE BACKUP_DATE, PHYSICAL_DEVICE_NAME,A.MEDIA_SET_ID,

SERVER_NAME,DATABASE_NAME,[TYPE]

FROM MSDB.DBO.BACKUPSET A JOIN MSDB.DBO.BACKUPMEDIAFAMILY B

ON(A.MEDIA_SET_ID=B.MEDIA_SET_ID)

) BACKUPS

GROUP BY [TYPE],SERVER_NAME,DATABASE_NAME

),

BACKUP_ALL AS

(

SELECT ROW_NUMBER() OVER (ORDER BY DATABASE_NAME,[TYPE],BACKUP_FINISH_DATE) ID,

PHYSICAL_DEVICE_NAME

FROM MSDB.DBO.BACKUPSET A JOIN MSDB.DBO.BACKUPMEDIAFAMILY B

ON(A.MEDIA_SET_ID=B.MEDIA_SET_ID)

)

SELECT SERVER_NAME [SERVER],DATABASE_NAME [DATABASE],BAKUPTYPE=

CASE WHEN [TYPE]=‘D’ THEN ‘FULL’

WHEN [TYPE]=‘I’ THEN ‘DIFFERENTIAL’

WHEN [TYPE]=‘L’ THEN ‘LOG’

WHEN [TYPE]=‘F’ THEN ‘FILE / FILEGROUP’

WHEN [TYPE]=‘G’ THEN ‘DIFFERENTIAL FILE’

WHEN [TYPE]=‘P’ THEN ‘PARTIAL’

WHEN [TYPE]=‘Q’ THEN ‘DIFFERENTIAL PARTIAL’

END,BACKUP_DATE [RECENT BACKUP], PHYSICAL_DEVICE_NAME [LOCATION] FROM BACKUP_RECENT,BACKUP_ALL

WHERE BACKUP_RECENT.ID=BACKUP_ALL.ID AND DATABASE_NAME=@P_DBNAME

ORDER BY BACKUP_DATE

END

END

END

Referecne : http://www.SQLServerCentral.com