SQL SERVER – 15 Best Practices for Better Database Performance

I have written 14 best practices here, read them all and let me know what is as per your opinion should be the 15th best practice.

1. Store relevant and necessary information in the database instead of application structure or array.

2. Use normalized tables in the database. Small multiple tables are usually better than one large table.

3. If you use any enumerated field create look up for it in the database itself to maintain database integrity.

4. Keep primary key of lesser chars or integer. It is easier to process small width keys.

5. Store image paths or URLs in database instead of images. It has less overhead.

6. Use proper database types for the fields. If StartDate is database filed use datetime as datatypes instead of VARCHAR(20).

7. Specify column names instead of using * in SELECT statement.

8. Use LIKE clause properly. If you are looking for exact match use “=” instead.

9. Write SQL keyword in capital letters for readability purpose.

10. Using JOIN is better for performance then using sub queries or nested queries.

11. Use stored procedures. They are faster and help in maintainability as well security of the database.

12. Use comments for readability as well guidelines for next developer who comes to modify the same code. Proper documentation of application will also aid help too.

13. Proper indexing will improve the speed of operations in the database.

14. Make sure to test it any of the database programming as well administrative changes.

15. ______________________________________________________________

Let me know what should be the 15th best practice.

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

About these ads

63 thoughts on “SQL SERVER – 15 Best Practices for Better Database Performance

  1. > 1. Store relevant and necessary information in the
    > database instead of application structure or array.

    I knew a fellow who prefered to keep his enums in code, thus requiring a recompile before adding/changing values to his applications. He didn’t trust his clients’ DBAs, I suppose. In general, I prefer to store things in the db too.

    > 3. If you use any enumerated field create look up for it in
    > the database itself to maintain database integrity.

    Same as 1, no?

    > 5. Store image paths or URLs in database instead of
    > images. It has less overhead.

    How do you handle security? I once did this for .DOC and .XLS files. Each file had a user-specified universe of authorised users, so I renamed all files to prevent someone from downloading documents based simply on filename. I couldn’t use an int, to prevent users from simply iterating upwards until they found what they wanted. Next time, I vowed I would store such documents as blob/image.

    > 9. Write SQL keyword in capital letters for readability
    > purpose.

    Words written in caps are harder to read than words written in lower-case. Now that I have a UI which highlights keywords using colour, I avoid caps.

    I think we got into the habit from the days when our UI was a text-editor and caps were the only way to highlight.

    > 10. Using JOIN is better for performance then using sub
    > queries or nested queries.

    It can also make queries more difficult to maintain. Depending upon complexity I may use subqueries or “pre-queries”.

    > 12. Use comments for readability …
    > 14. Make sure to test any of the database programming

    I have also learned to comment my *tests*. Too often I have returned to a test and had to “invest” time figuring out exactly what I was testing — I was not pleased with myself.

    • what are the data types used for inserting the image paths and urls in a table? Is this applicable for sql2005 onwards or only from 2008?

    • #3 is not the same as number one. #1 is stating don’t use the application to store data definitions. Whereas #3 is saying build lookup tables. We have here too many so-called db designers that use the code a code like C, P, or D in the table because it is supposedly understood that they stand for “complete”, “pending” and “denied”, yet these definitions are not specified anywhere in either the application or the database. .

      As for a number 15, my personal Bete noire, Never never, never, make the primary key, user entered data. If it is so important that user entered data be unique, then use a unique index. I watched once for three hours while an alleged DBA chased through three databases to fix a problem that arose when the user typed in “F14-E” instead of “F-14E”. Since they were using this aircraft type as a primary key in that table and foreign key in about a dozen other tables in three databases, This erroneous data entry caused a major breakdown in several applications.

  2. Hi ,

    I want to add images in my database and show them in datagrid view using asp.net. I used image field in sql server 2005 express , but images were not shown in asp.net.
    please suggest a solution.

  3. If you are importing large volumes of data from other sources, update statistics often, this will help the DB engine to make use of the indexes that were defined.

    Recompile stored procedures after updating the stats.

  4. Don’t put many queries with different execution plans in stored procedures (e.g. using IF’s or CASE). Sql Server caches only one execution plan per sproc.

    However, if you have to do this, consider using WITH RECOMPILE option to get optimum results. Often it is better to recompile each time than use poor plans.

    CREATE PROCEDURE dbo.Products_GetAll WITH RECOMPILE AS

  5. 15. Make use of standard naming conversions for your Table, Fields, View, Store procedures and triggers.
    Example :
    Table : Employee, employee, tbl_employee
    : GeneralLedger, general_ledger, tbl_general_ledger

    Fields – Primary Key : EmployeeId, employee_id, fld_employee_id

    Fields – Others : Surname, surname, fld_surname

    Views – vwEmployee, VWEmployee, vw_employee

    You might not really need to implement a rule for Store procedures and triggers because the are actions(method)

  6. I agree with 3, but the enumerations should be kept in the code as well. Use code generation if you want to keep it DRY.

    I do not agree with 11. Stored procs are much harder to develop, debug, maintain, etc… They offer absolutely no performance benefit in Sql server. Now sure there are uses for stored procs, but for most apps they are few and far between. I would always use parameterized client side sql before I created a stored proc.

  7. Hmmm, I don’t count that many items related to “better database performance”. Maybe the title isn’t quite right :)

    Not that I entirely disagree with the items stated, but I’d make the following observations as food for thought…

    1. Information stored in the database verses the app means extra database hits. It’s a good idea of course, especially if cached, but does make it less efficient.

    2. While a good idea, you’ll get better _performance_ in certain circumstances with database denormalisation. Of course a normalised database will be more maintainable and keeping tables small certainly makes sense. Keeping table sizes under the max row size makes even more sense.

    9. & 12. While a good practice, casing and comments won’t effect performance. Maintenance yes. So still a good idea.

    13. “Proper indexing” is a fairly general statement. Providing indexes for oft queried columns will help as does keeping the more common columns earlier in index definition. Also, choosing the most suitable index to have as the primary key (as a clustered index) will help performance.

    15 (non-performance related). No Hungarian notation on objects – it’s quite absurd. You’ve probably (i hope) named the tables properly (as in suitable for the purpose), views should be named descriptively as well.

    15 (performance related). Don’t hit the database repetitively for the same data within the same context (e.g. a single page request) and cache redundant data where appropriate. This eases load from the server and increases the response time of your app.

  8. Re: #11…Seriously? When will these myths die??

    The same day DBA’s will start programming and/or maintaining the whole application instead of just the database.

    But you are correct. I think you need a new #11….

  9. 15. Be sure, that all data-rules is stored in the database. Use PK, UNIQUE, FK, CHECK or Triggers.

    If not, you must write more complicated DML-statement. Use triggers when you cant’t use PK, FK, … Triggers is just programming .

  10. Great points Pinal! They may not all be performance related, but they are important points nonetheless.

    Someone had mentioned that their IDE changes the colors of keywords so capital casing isn’t required. If the IDE isn’t at hand: Notepad, email, or just making notes on a scratch pad, its easier for the next guy to distinguish objects from keywords (lets not forget about colorblind people).

    #11 – I concur.

    I think #15 should be avoid cursors or batch together multiple concurrent sql queries when possible to avoid numerous round trips when 1 will suffice.

  11. Re: #11… I know that ad-hoc queries might be faster (or the same speed), but with 100s of applications running on 10s of SQL servers, tracking down that ad-hoc query that is running (in some application or from someone’s query analyzer) for 10+ hours is more difficult than finding and fixing a stored procedure.

    In some cases, the source code has been lost for this critical application (or never provided) and there is no way to optimize a query written back when the back-end database was MSSQL 6.5… Telling the underpaid developers to reverse engineer an application just to let the DBAs sleep at night… good luck…

  12. 15. Avoid writing code that is susceptible to SQL injection.

    Concatenated SQL statements transfer the SQL injection risk from the inline code directly to the stored procedure.

    This commonly occurs in reports or in search queries, where the assumption is that a SQL statement in a Stored Procedure needs to be generated dynamically.

    Frequently a WHERE clause may get generated on the client web application, and then passed to the stored procedure. Here is an example:

    CREATE PROCEDURE pMyQuery (
    @Where varchar(8000)
    )
    AS

    DECLARE @sql varchar(8000)
    SET @sql = ‘SELECT * FROM MyTable ‘ + @Where + ‘ AND MyField = 4 ORDER BY This, That‘
    EXEC(@sql)

    Pass this to the WHERE clause and a table could get dropped.
    ‘1 = 1 DROP TABLE MyTable –‘

    Note that the comment dashes at the end of the line will prevent any subsequent code in the concatenated SQL statement from being executed. In the above case the additional filter and ORDER BY clause would not be executed.

    Rather than concatenating SQL statements, use conditionals and case statements within stored procedures to create dynamic queries. One approach is to employ CASE statements in WHERE clauses to handle filters and searches.

    For example, let’s say we have a user table with 6 fields: UserID, FirstName, LastName, NTUserName, Email, and BusinessID. And we want to create one stored procedure capable of returning both single and multiple records, and capable of applying filters or search criteria.

    Presented with that scenario many developers would program a concatenated SQL statement, either on the client or in the stored procedure (bad, bad, bad). Some may write a long stored procedure with a series of IF conditionals (better).

    A very efficient way to address this need is to employ CASE statements in the WHERE clause of the procedure. For example:

    CREATE PROCEDURE [dbo].[p_UserGet] (
    @UserID int,
    @BusinessID int,
    @SearchField varchar(20),
    @SearchText varchar(100)
    )
    AS

    SELECT * FROM tbl_User
    WHERE CASE
    – Return all records.
    WHEN (@BusinessID IS NULL) AND (@UserID IS NULL) AND (@SearchField IS NULL) THEN 1
    – Return all records for this BusinessID.
    WHEN (BusinessID = @BusinessID) AND (@UserID IS NULL) AND (@SearchField IS NULL) THEN 1
    – Search by FirstName.
    WHEN (BusinessID = @BusinessID) AND (@UserID IS NULL)
    AND ((@SearchField = ‘FirstName’) AND (FirstName LIKE ‘%’ + @SearchText + ‘%’)) THEN 1
    – Search by LastName
    WHEN (BusinessID = @BusinessID) AND (@UserID IS NULL)
    AND ((@SearchField = ‘LastName’) AND (LastName LIKE ‘%’ + @SearchText + ‘%’)) THEN 1
    – Return a single record for this UserID.
    WHEN (UserID = @UserID) THEN 1
    ELSE 0
    END = 1

    GO

    Comments reveal the logic. The first case statement returns all records, and the last case statement returns one record. Statements in between apply filters. The procedure could be extended to search by the other fields using statements similar to those that search on FirstName and LastName.

    This approach consolidates 3 potential stored procedures into one. It avoids SQL injection problems that can occur within search procedures that concatenate filters into WHERE clauses. And, it allows SQL to compile an execution plan, which improves performance.

  13. Maybe it’s just that I’m a database newbie, but I don’t quite get #10 – can’t you join to a subquery, or is this referring to linking to subqueries with the IN keyword?

  14. > 5. Store image paths or URLs in database instead of
    > images. It has less overhead.

    I hope in SQL 2008, they have the provision for storing the images in File System and the Security, Permission … will all handled by SQL Server.

    Please let me know if this is correct.

  15. Much of this is about database design, and not “SQL Server” best practice, which is about the DBA function and is technology-specific, for example

    2. Use normalized tables in the database. Small multiple tables are usually better than one large table.

    Assume’s you’re operating an OLTP implementation. Exactly the opposite applies in a multi-TB data warehouse.

    Some of your other comments such as

    9. Write SQL keyword in capital letters for readability purpose.

    this is too specific, the best practice is to create and operate coding standards, and adhere to them for consistency. The specific standards themselves are less important.

  16. Re: #11 and comments about it.

    People are correct in noting that as far as query performance there is not a performance gain over ad-hoc SQL (particularly parameterized), but there are gains in maintainability and security. I have always worked in small shops where I have done both SQL DBA/Development and application development and I have always found it easier to make a change in a stored procedure than changing compiled code and re-releasing.

    I also think that all of these DO refer to performance because the time it takes to develop and maintain the database and application need to be taken into account which is why commenting, naming conventions, and coding standards are important.

    IMO #15 Use set-based solutions whenever possible.

  17. For Mark Brackett:

    As I’ve been learning SQL over the past 8-9 months with virtually no mentor, I’ve been writing feeble queries using mostly temp tables. What would you recommend as a good resource for us self-teaching types to get away from our feeblness?

  18. I want to give answer of prashant. If he wants to show images(stored in database) in datagrid. He has to write a class that will save the image temporarily in a folder and get the URL of that, because datagrid takes the image url to show the images

  19. #5 See http://research.microsoft.com/research/pubs/view.aspx?msr_tr_id=MSR-TR-2006-45 for another viewpoint.

    Even though the paper says that access to docs over 1MB in size performs better when they are stored on the filesystem, this ignores issues of backup and referential integrity that could make it advantageous to keep larger docs in the database.

    An important message from the paper is that databases are able to process larger objects than they could do in the past. IMO this trend will continue, and the DB designer needs to know works best with current software and hardware rather than always confining large objects to the filestore.

  20. On 11) and LINQ to SQL (if I may)

    “… I have always found it easier to make a change in a stored procedure than changing compiled code and re-releasing.”

    Any change to the number or type of input output parameters will require a change to the stored procedure and the application code.

    Incidentally you really should be generating most of the DAL layer in any case and it is purely a matter of whether the code resides inside or outside the database. The actual code required (or generated from metadata) is pretty well identical in either case.

    I’m not suggesting there are never situations where it makes sense to use stored procedures but the Performance/Security/Maintainability mantra is a falsehood that many appear to believe purely because it has been repeated so often.

    I think also relevant here is the LINQ to SQL debate. Microsoft themselves are endorsing dynamic SQL vs Stored procedure code.

    Below taken from Simon Evans Blog

    http://blogs.conchango.com/simonevans/archive/2007/11/11/LINQ-to-SQL_3A00_-Let-the-debate-begin.aspx

    So with LINQ to SQL (and LINQ to Entities) the most hotly debated point was around the auto generation of LINQ’s dynamic SQL vs. using stored procedures. In the old days, one point favoring of using stored procedures was the performance gain from a cached execution plan. But since SQL Server 2005, dynamic SQL is also cached, so this reason for using stored procedures has largely gone away. One benefit dynamic SQL has over stored procedures is that you only select what you need in all cases. In a typical scenario with stored procedures and a DAL populating an object model, stored procedures will tend to be reused even when all of data is not used by the calling DAL method. There ways around this, but it largely involves an ever increasing list of stored procedures (GetObjectByX), make the solution less manageable.

  21. Below is my thought about 15th best practise.

    Use multiple data files for tempdb and if possible place them in seperate disk drives. This will reduce the bottleneck of tempdb.

    The tempdb of SQL Server will be used in many occations, e.g: sorting, use of temp tables and variable tables, worktables, etc.

  22. Point 15: Improve the performance of your upload using following parameters.

    1).Using minimal logging
    2). Max Batch size
    3).Disabling triggers
    4).Disabling Constraints
    5).Controlling the locking behavior
    6).Importing data in native format

    • Private Function fnUploadFile() As String
      Dim objPast As HttpPostedFile = Nothing
      Dim strFileName As String = String.Empty
      Dim intContentlength As Integer = 0
      Dim connection As SqlConnection = Nothing
      Try
      If FileUpload1.PostedFile.FileName “” Then
      strFileName = FileUpload1.FileName
      objPast = FileUpload1.PostedFile
      intContentlength = objPast.ContentLength
      Dim objByte(intContentlength) As Byte
      objPast.InputStream.Read(objByte, 0, intContentlength)

      connection = New SqlConnection(strConn)
      connection.Open()

      Dim cmd As New SqlCommand(“insert into TestTableForImage ” & “(FileName,Content) values (@FileNm,@image)”, connection)
      cmd.Parameters.Add(“@FileNm”, Data.SqlDbType.VarChar, 30).Value = strFileName
      cmd.Parameters.Add(“@image”, Data.SqlDbType.Image, objByte.Length).Value = objByte
      cmd.ExecuteNonQuery()
      End If
      fnUploadFile = True
      Catch ex As Exception
      Throw ex
      Finally
      connection.Close()
      End Try
      End Function

  23. Hello Mr. Pinal,
    I am a asp.net developer. I am developing a community site similar to facebook. using backend sqlserver. I have used lots of dynamic queries in storeprocedures. because i always try to code less line. so i used condition statement if required so as i need to used dynamic query like
    declare @qry = ‘select userid from tbl_userprofile’; exec(@qry).

    My question is that is this good programing or how much the code is safe if i used dynamic. if no then how can i achieve such functionality. Also let me know some performance tips. i like ur suggestions,query answer very much. mail me if u can, it s absolute pleasure

  24. This is for Mr.Prashant:

    Take image field name as n/varchar(max) named mainpic. Insert image name in a that column. Suppose one of the field value is default.jpg. No need to have image datatype.

    Make a folder in Application same directory. And store Image with same name as in database in that folder say D:\\project\images\default.jpg

    Now in datalist data fetch coding, in image tag.
    if at design :

    <asp:Image ID=”imgperson” runat=”server” imageurl=”images/” />

  25. This is for Mr.Prashant:

    Take image field name as n/varchar(max) named mainpic. Insert image name in a that column. Suppose one of the field value is default.jpg. No need to have image datatype.

    Make a folder in Application same directory. And store Image with same name as in database in that folder say D:\\project\images\default.jpg

    Now in datalist data fetch coding, in image tag.
    if at design :

    <asp:Image ID=”imgperson” runat=”server”

    imageurl=”images/” />

  26. There are a lot of best practices foir better DB Performance.
    In additional:

    1.) In finiding the existence of the record: Avoid using the count() instead, just simply write “select a from tb where a=param”

    2.) In optimizing the performance, avoid using the “JOIN”,
    instead you can use “SELECT * FROM t1 WHERE id IN (SELECT id FROM t2)”

    • Selecting just used column names is a good one. Especially if one column is an image. If you use SELECT * the image will be sent too even if you do not need it. A real show stopper if you are just trying to populate a grid with names and it takes an entire Snickers bar to load due to have image in the recordset.

  27. Hello Sarika,

    When * is used in SELECT clause then all the columns are fetched as resultset. But most of time we do not need all columns data and unnecessary data is transferred from server to client. Using column names in SELECT clause only fetch data of required columns. Processing and transferring of lesser data will turn into improved response time.

    Regards,
    Pinal Dave

  28. sir
    right now i doing a documentation management project in asp.net using vb for that i need upload and doc in database i dont know how to the save the doc file in a database sql server so kindly guid me sir ……..

  29. Hello,

    Thank you for these “Best practices”,
    I think that the 15 BP is one of :
    1. Use data base diagrams for big table to focus on table-relationships.
    or
    2. Use constraints like : CHECK and/or ‘ idX IS NULL AND idY IS NOT NULL OR idX IS NOT NULL AND idY iIS NULL’

  30. 15. Use with nolock with every select statement (by default), only remove it if required.

    16. Avoid use of Trigger

  31. In today’s government environment where the owners of the database have no rights on the server, then “5. Store image paths or URLs in database instead of images. It has less overhead.” is not possible. You can’t have an app store a file on the server when it is not allowed any access to the server.

    Because of the same issues regarding the lack of access that developers have to the web servers and the need to jump through 50 million hoops to deploy a recompiled web app on the server, then placing all your queries in stored procedures in a 100% better idea.

    Perfect real life example; when there is the need to change the spelling of a field from contarct to contract, this change can be made in the database in a the stored procedure with no changes being made in the application merely by running a SQL script. When the queries are in the web app (and they were in this case) you have to have the developers chase all through the web app’s code to find every reference to it, recompile the web app, submit a help desk ticket to deploy it to the test server, carry out testing, submit a help desk ticket to get it deployed to the staging server, have the SMEs complete testing on the web app, then submit a help desk ticket to get it deployeyed to the production server. All this to change the spelling of one word.

    That deserves a “Seriously!?!”

  32. When we are using a join on tables which are huge, we need to ensure in real tme that the columns in ON condition are indexed, this helps in fatser retrieval of data..

  33. Please share some Inputs that how to monitor the performance of SQL 2008 and what are the parameter used in same Thanks in advance

  34. My webapp is using SQL2008 and we’ve used most of the tips suggested here. But we are still facing performance issues. Can compression help improve the performance. If so, what are the methods or tools available.

  35. Pingback: SQL SERVER – Weekly Series – Memory Lane – #023 | SQL Server Journey with SQL Authority

  36. Hi All,

    I have a DB ['Student_DB'] with bulk data. Now, i have took backup and restored it another server to optimize the DB which is called ‘Optimized_Student_DB’. Then, i have optimized the ‘Optimized_Student_DB’ DB. Also, i have dropped all data from the ‘Optimized_Student_DB’ DB. Now, it is a complete optimized DB with data.

    Now, i have took script [schema only] from the ‘Optimized_Student_DB’ DB. By using this script, i have created another new DB which is called ‘Final_Student_DB’.

    My question is, which is give best performance? Optimized_Student_DB or Final_Student_DB?

    Please let us know for any questions.

    Thanks,

    Sathish

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s