SQL Server : Small Tips for Performance

Last week a friend of mine discussed on the slow performance of his web application and he was quite sure that the database operations were expensive.

Usually people do give very less importance to the procedures , functions & the data storing and retrieval mechanism when working with SQL Server.

In this post, i will write few things that can be done on top of your performance tune up procedures. I am sure you already know the things like creating indexes, avoiding cursors etc etc. Mostly i have not considered the common tips which any one usually know.There are plenty of websites giving it.

Reduce the Calls

  • From your web application, it is always better to hit the database as less number of times as possible. Say for example, in you screen, if you want to fill two dropdown lists, Write one procedure to return two result sets and use it instead of seperately calling two stored procedures by making two calls.
  • If you are using SQL Server 2008, make sure you use User Defined table types so that you can send many records to your stored procedure in 1 call. This helps you to avoid hitting the database from your code multiple times.

  • Close the connections without miss after a db operation completes. This is a old time tip but still do not forget it!
  • Above tips you might have read many times!. Here are few things to consider which you might have not read before.

Queries
  • Write queries that utilize defined table indexes. Using indexes minimizes the amount of table-scan which in most cases will be much slower than an index scan.
  • When you write User defined functions in sql server, make sure they return very minimal records. For example, If your UDF returns 10 records and if this udf is used with a query which returns 1000 rows, UDF is called 1000 times!.

  • Relate the tables with foreign keys. When you use joins, it doesn't take much resources. You may want to relate tables in your own way. But that doesn't help you in any way to boost the performance.
  • When you want to delete all the rows in a table to clean it up, use TRUNCATE instead of DELETE if you dont have a foreign key defined in the table.
  • Do cascade delete operations instead of making a trigger to delete the related records

  • Minimize the number of tables used in the trigger and the rows being affected. Make sure triggers wont execute for long time.
  • Run the sql server in native SQL Server mode and not in Compatibility mode. All new performance boost functions are not supported in compatibility mode.
  • Although SQL Server supports multiple instances of server, create only 1 instance. Resources are shared when you use multiple instances taking up the memory.
  • When you have tables with large amount of data, do not use COUNT(*) to get the count of records. When you do a count (*) it actually calculates the count.
    Instead of this, use "SELECT rows FROM sysindexes WHERE id = OBJECT_ID(‘table_nam>’) AND indid < 2". Count is already present in a table, why to count again!



  • Use the SQL Server Profiler Create Trace Wizard to run the “Identify Scans of Large Tables” trace to find out which table needs indexes.




  • When you are using two tables frequently and doing join operation on it, make sure the columns used in join has a index set on it.


General
  • Dont retrieve more data than you need. (If you need employee id of 1 employee, just do a scalar operation and get only id. Not 1 complete record.)

  • Use sp_ as the prefix for your stored procedures. Procedures with sp_ prefix is first searched in master database to execute. No more delay!
  • When calling a stored proc, use exec dbo.sp_myprocedure instead of exec myprocedure. Prefixing with owner name prevents sql server to create a compile lock.
  • Keep only required things inside Transactions in your procedure. Push the unwanted things out. Try to make the transactions as short as possible.

  • Use BETWEEN instead of IN clause when you are sure, both return same records. Say select * from emp where empid in( 200,201,202,203,204) is slower than select * from emp where empid BETWEEN 200 AND 204.
  • When you are using WHERE Class with multiple AND clauses, use the filter criteria First which filters most of the records. SQL Server applies second filter on top of first one. If you reverse it, 2nd filter should be applied on more records thus making a small delay!

  • Don't use LIKE operator with a leading wild-card.

Popular posts from this blog

Facebook Javascript API : Feed and Share Dialog for Beginners

Real time Push Notifications with SignalR & PNotify (Pines Notify)

What's new and expected in .NET Framework 4.5