This blog post will discuss 15 best practices for improving database performance. I have listed 14 best practices below. Read through them and let me know what you think should be the 15th best practice.
- Store only relevant and necessary data in the database
Avoid storing data in application structures or arrays when it belongs in the database. - Use normalized tables
Normalize your database to eliminate redundancy and maintain data integrity. Smaller, related tables are often better than one large table. - Create lookup tables for enumerated fields
For enumerated fields, use lookup tables to enforce consistency and maintain database integrity. - Use small and efficient primary keys
Choose short primary keys, such as integers or small character fields, to optimize performance. - Store image paths or URLs instead of images
Save image paths or URLs in the database and store the images in file storage to reduce overhead. - Select appropriate data types
Use precise data types for fields, such asDATETIMEfor date fields instead ofVARCHAR(20). - **Avoid SELECT ***
Specify column names inSELECTstatements to improve performance and readability. - Use LIKE clauses sparingly
When an exact match is required, use the=operator instead ofLIKEfor better efficiency. - Write SQL keywords in uppercase
Using uppercase for keywords such asSELECT,WHERE, andJOINenhances code readability. - Prefer JOINs over subqueries
UseJOINinstead of subqueries or nested queries for improved performance and clarity. - Utilize stored procedures
Stored procedures improve execution speed, simplify maintenance, and enhance security. - Comment and document your code
Add comments to your SQL scripts for clarity and as a guide for future developers. Proper application documentation is equally important. - Use proper indexing
Create and maintain indexes to improve query performance, keeping in mind the trade-off with write operations. - Test all changes thoroughly
Test any database programming or administrative changes in a non-production environment before deployment. - Monitor and optimize query performance
Use tools like SQL Server Profiler or Query Store to identify and optimize slow-performing queries. Regularly review execution plans to ensure efficient operations.
These practices will help you design, maintain, and optimize databases and for database performance and long-term reliability. Let me know your thoughts or if you have additional tips to share!
Reference: Pinal Dave (https://blog.sqlauthority.com)
108 Comments. Leave new
#15 should be “…and remember, ‘IT DEPENDS’ will be an often-used answer”.
Particularly for #s 5,9 and 10!
> 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.
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.
Agree with most of these guidelines.
But there is a great discussion on 11) in Frans Boumas Blog
Stored procedures are bad ‘kay
15. Set up an appropriate backup / restore procedure. Test this procedure. Be ready to recover whatever happens.
16. Document the database. For example, add extended properties to tables, columns, etc.
…and read
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.
15. Surely it must be – if it is important (it must be you just spent time on it right?) – back it up.
#15 Write queries with all operators eg:
INSERT INTO TABLE () VALUES instead of INSERT TABLE
This makes the code more readable.
No. 15 could perhaps be to run MSSQL and SQLServerAgent service under a different account than the default
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?
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
The following reference can help you to store images in database and retrieve them in GridView or DataGrid control
#5 See https://www.microsoft.com/en-us/research/publication/to-blob-or-not-to-blob-large-object-storage-in-a-database-or-a-filesystem/ 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.
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
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.
15 . Use Computed columns for columns which are prefix by functions and frequently used in where clause.
16. Use Index hint if necessary.
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.
15. Plan use of nulls
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
How can i add a image in the table and display in the datagrid like controls…..???
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
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