performance tune SQL Server. It useful to create a selective index, as selectivity depends on the qualifying rows. Selectivity can be defined as the ratio of the qualifying rows to the total rows. When the ratio is low the index becomes very selective thus reducing the result set to a greater extent. Create multiple column indexes, these type of indexes are extensions of single column indexes. They are very useful in evaluating filter expressions that match a prefix set of key columns. It is advised to not index small tables; scanning a table is easier. Indexing on the primary keys and the foreign keys will also help in performance tuning SQL Server. The main reason for this is primary and foreign keys are always used to link the tables. Indexes when used with filter clauses can help in speeding the evaluation. All the filter clauses decrease the final set of a query. Some filters help reduce the quantity of data to be scanned. Using sorting operations is another method to help performance tune SQL Server. Different types of sorting operations are DISTICT, ODER BY and GROUP BY. In some cases the query can be rewritten to use JOIN; which assists in performance tuning SQL Server. The primary advantage is evaluating tables in a different order. Thus a sub query returns to the desired output when it finds the data its trying to locate in the first qualifying row. There are several tools available in the market which will help performance tuning. These tools help find problems that are related to the performance but also help in the development process. They can be used as troubleshooting tools for the existing applications. Server performance tools are mainly used to monitor and register the workload of a SQL Server. Indexing workload logs enables necessary changes to be made to improve server performance. There some tools that provide the graphical execution plan for the SQL query analysis. Performance related problems can arise from off the shelf applications. For example, a company has purchased a application which uses the SQL server; in that case performance problems may arise as the application is using the SQL database as the backend. The other methods to performance tune SQL Server involve managing the RAM cache as RAM is a very limited resource. Managing the RAM cache involves controlling the input/output, thus better utilising buffer cache. This is particularly relevant to recall when working with large data sets and indexes, doing so will reduce overall input/output arguments and improves parallel operations. Disk input / output subsystem performance should be constantly monitored as disk queuing contributes towards bad performance. If the server is servicing requests from hundreds and thousands of connections, tuning the application and queries becomes extremely important. Hardware plays an important role that is easily neglected. In some of the applications, issues start only once the application is rolled out. Following application implementation, the design is unable to be changed and thus the only way to improve performance is through hardware. Selecting the optimum hardware for the task depends on a variety of factors such as: the database size, number of end users and how the database is used; through OLTP or OLAP. An experienced DBA developer should be able to estimate optimum specifications for hardware. The best way to understand hardware requirements is to test the application while in development stage. When purchasing a server, make sure it has the ability to expand the number of CPUs. The most important part of a server tuned for better performance is its memory. Another important factor to consider is the input/output subsystem. Purchase smaller drives over larger drives results in more discs in an array that make input/output faster. Now the SQL Server is self tuning, meaning it observes what is running and adjusts itself accordingly for optimal performance. Hence why it is advised that application testing should be performed after allocating time for SQL Server to adjust to the workload. To optimise application design, carry out as many data-centric tasks as possible on SQL Server, these tasks should be in the form of stored procedures. Avoid storing data from the database or creating complex object hierarchies. The design should consider the database connection and object pooling. Design should run queries asynchronously; to ensure that one query is not waiting for the other query to run. ]]>