SQL optimization

Optimizing SQL Server Performance {1}

by Kathy S
In this journal article the authors state that many times efficiency and performance are the last criteria considered when designing and developing new applications using a database. Sometimes the application does not display the information requested to the database in a reasonable time or completely fails to display it. The reasons may be related to the application design, but in many cases the DBMS does not return the data quickly enough, due to the non-use of indexes, deficient design of the queries and/or database schema, excessive fragmentation, use of inaccurate statistics, failure to reuse the execution plans or improper use of cursors. The authors then review the objectives that should be considered in order to improve performance of SQL server instances. The most important objectives are: 1) Designing an efficient data schema, 2) Optimizing indexes, stored procedures and transactions, 3) Analyzing execution plans and avoiding recompiling them, 4) Monitoring access to data, 5) Optimizing queries. The authors conclude that optimization is an iterative process and includes identifying bottlenecks, solving them, measuring the impact of changes and reassessing the system from the first step as to determine if satisfactory performance is achieved. They also highlight the fact that a superior performance can be obtained by writing an efficient code at the application level and properly using the design and database development techniques. read more...

Optimizing SQL {2}

by Brian B
The article I picked this week is titled “Improving SQL Server Performance” by Victor Vladucu and Nicolae Mercioiu. The article starts off by saying that in some cases efficiency and performance are neglected until the end of the development process. They only become important once the system starts being used in the real world. The article says that this could be caused by anything from the design of the database to bad management of the system. The author says that when trying to optimize performance you should aim for “good enough” instead of trying to aim for the theoretical maximum. In the second part of the article they talks about the importance of using indexed locations to speed up queries along with a few other measures to keep performance at its peak. The third part of the article talks about query optimization.  It gives some examples of how to structure queries and what to avoid.  The article offers a couple of different ways to change your queries to optimize them, but they removed the actual queries from the article. The fourth part of the article deals with what it calls “new optimizing options” for SQL server 2008. The article ends by rounding out the optimization process and that it is iterative in nature. read more...