If the performance of an SQL server is degrading, it is a reason for concern for the administrator as well as the users. Here are some universal tips that will tune up the performance of your SQL server.
- For accessing remote servers over sluggish links, it is advisable to use stored codes and T-SQL statements. The same applies to managing large databases. Enterprise Manager should be used only if you are high on resources.
- In order to use the locking and logging resources more judiciously, use of table variables is advised as opposed to temporary tables.
- One way to take some load off your server and answer queries in a more comprehensive fashion is to return only required columns of a table for a query and not all columns.
- Using the truncation instead of delete commands for deleting rows from a table makes operation faster. This is because when the delete command is used, entries are added to the log file for individual row deletions.
- Using constraints in place of triggers can also enhance the performance of your server.
- One should understand that using the DISTINCT clause causes degradation of performance.
- To perform row-by-row operations, use of correlation sub-queries or derived tables instead of SQL server cursors should be preferred. SQL server cursors are known to cause performance issues.
- One ingenious way to reduce network traffic is the use of UDFs (User Defined Functions), which have Transact-SQL statements used to capture codes for repetitive use.
- Union all statements are executed faster than UNION statements, since the former does not try to find duplicate rows, unlike the latter.
Once, you master these skills, how about getting recognized for your skills by passing the certification exam? With a holistic e-learning solution by uCertify, passing the certification exam in one attempt is a cake walk! So, save time, money and efforts and enhance your credentials.
