Sql Server Database Performance Tuning And Optimization

The performance of database systems eventually determines their availability and survivability. Most of them will bring about some performance problems more or less after running for a period of time, which mainly involve database hardware , database server , database memory , applications , operating systems and database parameters, etc.Therefore, performance tuning and optimization of database systems ,which concern multiple aspects ,are very vital to the normal running of the whole system . Through a unified plan and systematical analysis to make appropriate adjustment , the stability and availability of database will be improved , high running efficiency will be guaranteed , system bottleneck will be solved , system overhead will be reduced, considerable applicability and in the meanwhile will be obtained , and some guidelines for theoretical research will be provided.Based on the above-mentioned idea, by problems meeting in teaching and experience of using SQL database , the paper principally pays attention to the research on the performance tuning and optimization of SQL statements ,memory allocation of SQL Server and disk I/O etc. , and makes a deep analysis and discussion . It suggests in general some performance tuning strategies and optimization approaches of SQL Server application systems to drive the development of SQL Server performance management and optimization technology .Firstly , the paper introduces the corresponding research background of performance tuning of database , significance of selected topic and framework of performance tuning and optimization of database including their concepts , performance evaluation index and primary aspects of optimization .Secondly , several common optimization techniques are introduced and explained in detail . This paper summarizes CPU optimization , disk I/O optimization , memory optimization , optimization of database logical structure and physical structures , network environment optimization , high performance backup and recovery , purposes, methods and techniques of SQL optimization, relevant rules of index creation, and intelligent use of SQL Server partitioning techniques and clustering methods. The fore-and-aft performance indexes of various optimization techniques are analyzed and compared. An overall tuning strategy of system performance for SQL Server databases is put forward in the end.Finally, this paper summarizes and prospects the problems of performance tuning and optimization of SQL SERVER databases.