SQL SERVER – Stored Procedure Optimization Tips – Best Practices

We will go over how to optimize Stored Procedure with making simple changes in the code. Please note there are many more other tips, which we will cover in future articles.

  • Include SET NOCOUNT ON statement: With every SELECT and DML statement, the SQL server returns a message that indicates the number of affected rows by that statement. This information is mostly helpful in debugging the code, but it is useless after that. By setting SET NOCOUNT ON, we can disable the feature of returning this extra information. For stored procedures that contain several statements or contain Transact-SQL loops, setting SET NOCOUNT to ON can provide a significant performance boost because network traffic is greatly reduced.

CREATE PROC dbo.ProcName
AS
SET
NOCOUNT ON;
--Procedure code here
SELECT column1 FROM dbo.TblTable1
-- Reset SET NOCOUNT to OFF
SET NOCOUNT OFF;
GO

  • Use schema name with object name: The object name is qualified if used with schema name. Schema name should be used with the stored procedure name and with all objects referenced inside the stored procedure. This help in directly finding the complied plan instead of searching the objects in other possible schema before finally deciding to use a cached plan, if available. This process of searching and deciding a schema for an object leads to COMPILE lock on stored procedure and decreases the stored procedure’s performance. Therefore, always refer the objects with qualified name in the stored procedure like

SELECT * FROM dbo.MyTable -- Preferred method
-- Instead of
SELECT * FROM MyTable -- Avoid this method
--And finally call the stored procedure with qualified name like:
EXEC dbo.MyProc -- Preferred method
--Instead of
EXEC MyProc -- Avoid this method

  • Do not use the prefix “sp_” in the stored procedure name: If a stored procedure name begins with “SP_,” then SQL server first searches in the master database and then in the current session database. Searching in the master database causes extra overhead and even a wrong result if another stored procedure with the same name is found in master database.
  • Use IF EXISTS (SELECT 1) instead of (SELECT *): To check the existence of a record in another table, we uses the IF EXISTS clause. The IF EXISTS clause returns True if any value is returned from an internal statement, either a single value “1” or all columns of a record or complete recordset. The output of the internal statement is not used. Hence, to minimize the data for processing and network transferring, we should use “1” in the SELECT clause of an internal statement, as shown below:

IF EXISTS (SELECT 1 FROM sysobjects
WHERE name = 'MyTable' AND type = 'U')

  • Use the sp_executesql stored procedure instead of the EXECUTE statement.
    The sp_executesql stored procedure supports parameters. So, using the sp_executesql stored procedure instead of the EXECUTE statement improve the re-usability of your code. The execution plan of a dynamic statement can be reused only if each and every character, including case, space, comments and parameter, is same for two statements. For example, if we execute the below batch:

DECLARE @Query VARCHAR(100)
DECLARE @Age INT
SET
@Age = 25
SET @Query = 'SELECT * FROM dbo.tblPerson WHERE Age = ' + CONVERT(VARCHAR(3),@Age)
EXEC (@Query)

If we again execute the above batch using different @Age value, then the execution plan for SELECT statement created for @Age =25 would not be reused. However, if we write the above batch as given below,

DECLARE @Query NVARCHAR(100)
SET @Query = N'SELECT * FROM dbo.tblPerson WHERE Age = @Age'
EXECUTE sp_executesql @Query, N'@Age int', @Age = 25

the compiled plan of this SELECT statement will be reused for different value of @Age parameter. The reuse of the existing complied plan will result in improved performance.

  • Try to avoid using SQL Server cursors whenever possible: Cursor uses a lot of resources for overhead processing to maintain current record position in a recordset and this decreases the performance. If we need to process records one-by-one in a loop, then we should use the WHILE clause. Wherever possible, we should replace the cursor-based approach with SET-based approach. Because the SQL Server engine is designed and optimized to perform SET-based operation very fast. Again, please note cursor is also a kind of WHILE Loop.
  • Keep the Transaction as short as possible: The length of transaction affects blocking and deadlocking. Exclusive lock is not released until the end of transaction. In higher isolation level, the shared locks are also aged with transaction. Therefore, lengthy transaction means locks for longer time and locks for longer time turns into blocking. In some cases, blocking also converts into deadlocks. So, for faster execution and less blocking, the transaction should be kept as short as possible.
  • Use TRY-Catch for error handling: Prior to SQL server 2005 version code for error handling, there was a big portion of actual code because an error check statement was written after every t-sql statement. More code always consumes more resources and time. In SQL Server 2005, a new simple way is introduced for the same purpose. The syntax is as follows:

BEGIN TRY
--Your t-sql code goes here
END TRY
BEGIN CATCH
--Your error handling code goes here
END CATCH

Reference: Pinal Dave (http://blog.SQLAuthority.com)

About these ads

SQL SERVER – Questions and Answers with Database Administrators

I have been in India for long time now, and at present, I am managing a very large outsourcing project. Recently, we conducted few interviews since the project required more Database Administrators and Senior Developers, and I must say it was an enthralling experience for me! I got the opportunity to meet some very talented and competent programmers from all over the country. Scores of interesting questions were discussed between the interviewers and the candidates, which made the whole interview process nothing short of an enriching occasion! I am listing some of the interesting questions discussed during the interviews. Some are technical and some are purely my personal opinion.

Q. According to you what goes into making the best Database Administrator?

A. The primary job of DBAs is to secure the data. They should be able to keep it safe as well as reproduce it efficiently, whenever required. So as per my view, a Database Administrator who can fulfill the requirements of Securing Data and Retrieving Data is the best DBA.

When I hire a DBA I always ask them questions about backup strategies and efficient restoring methodologies.

Q. I have all the primary data files, secondary data files as well as logs. Now, tell me can I still restore the database without having a full backup?

A. You cannot restore the database without having a full database backup. However, if you have the copy of all the data files (.mdf and .ndf) and logs (.ldf) when database was in working condition (or your desired state) it is possible to  attach that database using sp_attach_db.

Q. As per your opinion what are the five top responsibilities of a DBA?

A.  I rate the following five tasks as the key responsibilities of a DBA.

1. Securing database from physical and logical integrity damage.
2. Restoring database from backup as a part of disaster management plan.
3. Optimizing queries performance by appropriate indexing and optimizing joins, where conditions, select clause etc.
4. Designing new schema, support legacy schema, and legacy database systems.
5. Helping developers improve their SQL-related code writing skill.

Q. One of the developers in my company moved one of the columns from one table to some other table in the same database. How can I find the name of the new table where the column has been moved?

A. This question can be answered by querying system views.

For SQL Server 2005 run the following code:
SELECT OBJECT_NAME(OBJECT_ID) TableName
FROM sys.columns
WHERE name = 'YourColumnName'

The previous query will return all the tables that use the column name specified in the WHERE condition. This is a very small but a very handy script.

Q. What is the difference between SQL Server 2000 object owner and SQL Server 2005 schema?

A. Let us first see the fully qualified query name to access a table for SQL Server 2000 and SQL Server 2005.

SQL Server 2000: [DataBaseServer].[DataBaseName].[ObjectOwner].[Table]

SQL Server 2005: [DataBaseServer].[DataBaseName].[Schema].[Table]

SQL Server 2008: [DataBaseServer].[DataBaseName].[Schema].[Table]

In SQL Server 2000, prior to dropping the user who owns database objects, all the objects belonging to that user either need to be dropped or their owner has to be changed. Every time a user is dropped or modified, system admin has to undergo this inconvenient process.
In SQL Server 2005 and the later versions, instead of accessing a database through database owner, it can be accessed through a schema. Users are assigned to schemas, and by using this schema a user can access database objects. Multiple users can be assigned to a single schema, and they all can automatically receive the same permissions and credentials as the schema to which they are assigned. Because of the same reason in SQL Server 2005 and the later versions – when a user is dropped from database – there is no negative effect on the database itself.

Q. What is BI? I have heard this term before but I have no idea about it?

A. BI stands for Business Intelligence. Microsoft started to promote the acronym BI since the launch of SQL Server 2005. However, it has been in use for a long time. The basic idea of BI is quite similar to Data Warehousing. Business intelligence is a method for storing and presenting accurate and timely key enterprise data to CXO, IT Managers, Business Consultants, and distributed teams of a company, to provide them with up-to-date information to drive intelligent decisions for business success, which ultimately leads to enhanced revenue, reduced risk, decreased cost, and better operational control for business agility and competitiveness. An effective BI empowers end users to use data to understand the cause that led to a particular business result, to decide on the course of action based on past data, and to accurately forecast future results.

Q. What is your recommendation for a query running very slow?

A. Well, your question is very difficult to answer without looking at the code, application and physical server. In such situations, there are a few things that must be paid attention to right away.

  • Restart Server
  • Upgrade Hardware
  • Check Indexes on Tables and Create Indexes if necessary
  • Make sure SQL Server has priority over other operating system processes in SQL Server settings
  • Update statistics on the database tables.

Q. What should be the fill factor for Indexes created on tables?

A. Fill factor specifies a percentage that indicates how full the Database Engine should make the leaf level of each index page during index creation or alteration. Fill factor must be an integer value from 1 to 100. The default is 0. I prefer to keep my servers default fill factor as 90.

Q. Which feature in SQL Server 2008 has surprised you? You can name just one.

A. Plan Freezing is a new feature I never thought of. I find it very interesting!  It is included in SQL Server 2008 CTP5. SQL Server 2008 enables greater query performance stability and predictability by providing new functionality to lock down query plans. This empowers organizations to promote stable query plans across hardware server replacements, server upgrades, and production deployments.

Q. How do you test your database?

This is a very generic question. I would like to describe my generic database testing method as well as stored procedure testing methods.

Testing Databases:

  • Table Column data type and data value validation.
  • Index implementation and performance improvement.
  • Constraints and Rules should be validated for data integrity.
  • Application field length and type should match the corresponding database field.
  • Database objects like stored procedures, triggers, functions should be tested using different kinds of input values and checking the expected output variables.

Testing Stored Procedures:

  • Understand the requirements in terms of Business Logic.
  • Check if the  code follows all the coding standards.
  • Compare the fields’ requirements of application to the fields retrieved by a stored procedure. They must match.
  • Repeatedly run the stored procedures several times with different input parameters and then compare the output with the expected results.
  • Pass invalid input parameters and see if a stored procedure has good error handling.

Reference : Pinal Dave (http://blog.SQLAuthority.com), DotNetSlackers

SQL Server – 2008 – Cheat Sheet – One Page PDF Download

Very frequently I have been asked to create a page, post or article where in one page all the important concepts of SQL Server are covered. SQL Server 2008 is very large subject and can not be even covered 1000 of pages. In daily life of DBA there are few commands very frequently used and for novice developers it is good to keep all the important SQL Script and SQL Statements handy.

I have attempted to create cheat sheet for SQL Server 2008 most important commands. User can print this in one A4 size page and keep along with them. This can be used in interviews where T-SQL scripts are being asked.

Let me know your opinion and if you find this useful.

Download SQL Server 2008 Cheat Sheet

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Guidelines and Coding Standards Complete List Download

SQL SERVER – Guidelines and Coding Standards complete List Download

Coding standards and guidelines are very important for any developer on the path of successful career. A coding standard is a set of guidelines, rules and regulations on how to write code. Coding standards should be flexible enough or should take care of the situation where they should not prevent best practices for coding. They are basically the guidelines that one should follow for better understanding.

The concept behind implementing coding standards and guidelines, is that the consistency and uniformity in programming so that if multiple people are working on the same code, it becomes easier to communicate, share with or understand each other’s work.

With the goal of promoting good coding standards and guidelines I have created document which can guide developers.

SQL SERVER – Guidelines and Coding Standards Part – 1

SQL SERVER – Guidelines and Coding Standards Part – 2

SQL SERVER – Guidelines and Coding Standards complete List Download

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Guidelines and Coding Standards Part – 2

  • To express apostrophe within a string, nest single quotes (two single quotes).

Example:

SET @sExample = 'SQL''s Authority'

  • When working with branch conditions or complicated expressions, use parenthesis to increase readability.

IF ((SELECT 1
FROM TableName
WHERE 1=2) ISNULL)

  • To mark single line as comment use (–) before statement. To mark section of code as comment use (/*…*/).
  • If there is no need of resultset then use syntax that doesn’t return a resultset.

IF EXISTS   (SELECT 1
FROM UserDetails
WHERE UserID = 50)

    Rather than,

IF EXISTS  (SELECT COUNT (UserID)
FROM UserDetails
WHERE UserID = 50)

  • Use graphical execution plan in Query Analyzer or SHOWPLAN_TEXT or SHOWPLAN_ALL commands to analyze SQL queries. Your queries should do an “Index Seek” instead of an “Index Scan” or a “Table Scan”. (Read More Here)
  • Do not prefix stored procedure names with “SP_”, as “SP_” is reserved for system stored procedures.
    Example:
    SP<App Name>_ [<Group Name >_] <Action><table/logical instance>
  • Incorporate your frequently required, complicated joins and calculations into a view so that you don’t have to repeat those joins/calculations in all your queries. Instead, just select from the view. (Read More Here)
  • Do not query / manipulate the data directly in your front end application, instead create stored procedures, and let your applications to access stored procedure.
  • Do not store binary or image files (Binary Large Objects or BLOBs) inside the database. Instead, store the path to the binary or image file in the database and use that as a pointer to the actual file stored on a server.
  • Use the CHAR datatype for a non-nullable column, as it will be the fixed length column, NULL value will also block the defined bytes.
  • Avoid using dynamic SQL statements if you can write T-SQL code without using them.
  • Minimize the use of Nulls. Because they incur more complexity in queries and updates. ISNULL and COALESCE functions are helpful in dealing with NULL values
  • Use Unicode datatypes, like NCHAR, NVARCHAR or NTEXT if it needed, as they use twice as much space as non-Unicode datatypes.
  • Always use column list in INSERT statements of SQL queries. This will avoid problem when table structure changes.
  • Perform all referential integrity checks and data validations using constraints instead of triggers, as they are faster. Limit the use of triggers only for auditing, custom tasks, and validations that cannot be performed using constraints.
  • Always access tables in the same order in all stored procedure and triggers consistently. This will avoid deadlocks. (Read More Here)
  • Do not call functions repeatedly in stored procedures, triggers, functions and batches, instead call the function once and store the result in a variable, for later use.
  • With Begin and End Transaction always use global variable @@ERROR, immediately after data manipulation statements (INSERT/UPDATE/DELETE), so that if there is an Error the transaction can be rollback.
  • Excessive usage of GOTO can lead to hard-to-read and understand code.
  • Do not use column numbers in the ORDER BY clause; it will reduce the readability of SQL query.
    Example: Wrong Statement
    SELECT UserID, UserName, Password
    FROM UserDetails
    ORDER BY 2

Example: Correct Statement
SELECT UserID, UserName, Password
FROM UserDetails
ORDER BY UserName

  • The RETURN statement is meant for returning the execution status only, but not data. If you need to return data, use OUTPUT parameters.
  • If stored procedure always returns single row resultset, then consider returning the resultset using OUTPUT parameters instead of SELECT statement, as ADO handles OUTPUT parameters faster than resultsets returned by SELECT statements.
  • Effective indexes are one of the best ways to improve performance in a database application.
  • BULK INSERT command helps to import a data file into a database table or view in a user‐specified format.
  • Use Policy Management to make or define and enforce your own policies fro configuring and managing SQL Server across the enterprise, eg. Policy that Prefixes for stored procedures should be sp.
  • Use sparse columns to reduce the space requirements for null values. (Read More Here)
  • Use MERGE Statement to implement multiple DML operations instead of writing separate INSERT, UPDATE, DELETE statements.
  • When some particular records are retrieved frequently, apply Filtered Index to improve query performace, faster retrieval and reduce index maintenance costs.
  • EXCEPT or NOT EXIST clause can be used in place of LEFT JOIN or NOT IN for better peformance.

Example:
SELECT EmpNo, EmpName
FROM EmployeeRecord
WHERE Salary > 1000 AND Salary
NOT IN (SELECT Salary
FROM EmployeeRecord
WHERE Salary > 2000);

    (Recomended)

SELECT EmpNo, EmpName
FROM EmployeeRecord
WHERE Salery > 1000
EXCEPT
SELECT
EmpNo, EmpName
FROM EmployeeRecord
WHERE Salery > 2000
ORDER BY EmpName;

© Copyright 2000-2008 Pinal Dave. All Rights Reserved. SQLAuthority.com

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Guidelines and Coding Standards Part – 1

  • Use “Pascal” notation for SQL server Objects Like Tables, Views, Stored Procedures. Also tables and views should have ending “s”.

Example:

UserDetails

Emails

  • If you have big subset of table group than it makes sense to give prefix for this table group. Prefix should be separated by _.

Example:

Page_ UserDetails

Page_ Emails

  • Use following naming convention for Stored Procedure. sp<Application Name>_[<group name >_]<action type><table name or logical instance> Where action is: Get, Delete, Update, Write, Archive, Insert… i.e. verb

Example:

spApplicationName_GetUserDetails

spApplicationName_UpdateEmails

  • Use following Naming pattern for triggers: TR_<TableName>_<action><description>

Example:

TR_Emails_LogEmailChanges

TR_UserDetails_UpdateUserName

  • Indexes : IX_<tablename>_<columns separated by_>

Example:

IX_UserDetails_UserID

  • Primary Key : PK_<tablename>

Example:

PK_UserDetails

PK_ Emails

  • Foreign Key : FK_<tablename_1>_<tablename_2>

Example:

FK_UserDetails_Emails

  • Default: DF_<table name>_<column name>

Example:

DF_ UserDetails _UserName

  • Normalize Database structure based on 3rd Normalization Form. Normalization is the process of designing a data model to efficiently store data in a database. (Read More Here)
  • Avoid use of SELECT * in SQL queries. Instead practice writing required column names after SELECT statement.

Example:

SELECT Username, Password
FROM UserDetails

  • Use SET NOCOUNT ON at the beginning of SQL Batches, Stored Procedures and Triggers. This improves the performance of Stored Procedure. (Read More Here)
  • Properly format SQL queries using indents.

Example: Wrong Format

SELECT Username, Password FROM UserDetails ud INNER JOIN Employee e ON e.EmpID = ud.UserID

Example: Correct Format

SELECT Username, Password
FROM UserDetails ud
INNER JOIN Employee e ON e.EmpID = ud.UserID

  • Practice writing Upper Case for all SQL keywords.

Example:

SELECT, UPDATE, INSERT, WHERE, INNER JOIN, AND, OR, LIKE.

  • It is common practice to use Primary Key as IDENTITY column but it is not necessary. PK of your table should be selected very carefully.
  • If “One Table” references “Another Table” than the column name used in reference should use the following rule :

Column of Another Table : <OneTableName> ID

Example:

If User table references Employee table than the column name used in reference should be UserID where User is table name and ID primary column of User table and UserID is reference column of Employee table.

  • Columns with Default value constraint should not allow NULLs.
  • Practice using PRIMARY key in WHERE condition of UPDATE or DELETE statements as this will avoid error possibilities.
  • Always create stored procedure in same database where its relevant table exists otherwise it will reduce network performance.
  • Avoid server-side Cursors as much as possible, instead use SELECT statement. If you need to use cursor then replace it next suggestion.
  • Instead of using LOOP to insert data from Table B to Table A, try to use SELECT statement with INSERT statement. (Read More Here)

INSERT INTO TABLE A (column1, column2)
SELECT column1, column2
FROM TABLE B
WHERE ....

  • Avoid using spaces within the name of database objects; this may create issues with front-end data access tools and applications. If you need spaces in your database object name then will accessing it surround the database object name with square brackets.

Example:

[Order Details]

  • Do not use reserved words for naming database objects, as that can lead to some unpredictable situations. (Read More Here)
  • Practice writing comments in stored procedures, triggers and SQL batches, whenever something is not very obvious, as it won’t impact the performance.
  • Do not use wild card characters at the beginning of word while search using LIKE keyword as it results in Index scan.
  • Indent code for better readability. (Example)
  • While using JOINs in your SQL query always prefix column name with the table name. (Example). If additionally require then prefix Table name with ServerName, DatabaseName, DatabaseOwner. (Example)
  • Default constraint must be defined at the column level. All other constraints must be defined at the table level. (Read More Here)
  • Avoid using rules of database objects instead use constraints.
  • Do not use the RECOMPILE option for Stored Procedure unless there is specific requirements.
  • Practice to put the DECLARE statements at the starting of the code in the stored procedure for better readability (Example)
  • Put the SET statements in beginning (after DECLARE) before executing code in the stored procedure. (Example)

© Copyright 2000-2008 Pinal Dave. All Rights Reserved. SQLAuthority.com

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – 2008 – Introduction to Policy Management – Enforcing Rules on SQL Server

I have previous written article about SQL SERVER Database Coding Standards and Guidelines Complete List Download. I just received question from one of the blog reader is there any way we can just prevent violation of company policy. Well Policy Management can come into handy in this scenario.

If our company policy is to create all the Stored Procedure with prefix ‘usp’ that developers should be just prevented to create Stored Procedure with any other prefix. Let us see small tutorial how to create conditions and policy which will prevent any future SP to be created with any other prefix.

Let us first create condition which defines that name of the database object should start with ‘usp’.

Now create policy for all the Stored Procedure of the database and assign previously created condition of naming convention to all the Stored Procedure of the database.

Make sure that Policy is enabled.

Now try to create Stored Procedure with incorrect name. It will not let you create it but will create error.

If you try to create Stored Procedure prefixed with ‘usp’ it will create it successfully.

Policy Management is very detailed subject and I will write more tutorial about this subject in future.

Reference : Pinal Dave (http://blog.SQLAuthority.com)