SQL Server Performance Tuning Tips
10. SQL Server Performance Monitoring Tips
Processor : % Processor Time - shows CPU loading. In the multiprocessing system diagnostics of loading, both of each processor separately and all together, is possible. In addition, the same parameter can be used for definition of the processor utilization by each stream. If the meter Processor : % Processor Time shows average value in a range from 80 up to 100 percents it is, as a rule, testifies a problem with productivity of a processor subsystem of your server. It is necessary to take measures on scaling the system or re-configuration.
It is necessary to mention, that short-term reaching by the meter Processor : % Processor Time level of 80 percents or even peaks (up to 100 percents) is not necessarily a sign of productivity problems of a processor subsystem. Since operational recourses of the processor are used not only by database server, you should define whether SQL Server is a reason of high utilization of the CPU time. Use SQLServer : CPUtime to define share of SQL server in Processor : % Processor Time. Having figured out, that the reason of increased loading of processors is SQL server, and also what process provokes it, you should analyze the project of query executed at this time. Make sure, that indexes are used optimally in query. It could be that ineptly constructed query does not use or not optimally uses existing index.
The query may be cached well but, at the same time, to overload the system of input-output that abstracts a plenty of CPU cycles. It may mean that at designing the table the index was planned not optimally. If the query project is optimal, it is possible to troubleshoot by scaling, for example, to add processors or to install more productive processor. Naturally, processors can be added only when the RAM size of the server is large enough to satisfy query of SQL server. On the contrary, if Processor : % Processor Time permanently shows very low utilization of the central processing unit, it, as a rule, means existence of problems. Low utilization is possible because of limitations in configuration of SQL server (for example, defaults when their magnification is required are used) or the problem is in your application / the applied task.
Processor : % Privileged Time is convenient for definition of excessive I/O loading. If the average value exceeds 20 %, and Processor : % Processor Time are much lower than 80 %, it means that SQL Server too hardly utilizes a subsystem of input-output. It is necessary for you to analyze a database design, loading RAID of the controller and the network card. Essential influence on Processor : % Privileged Time may also render working parallel with SQL server processes or services, in case when the server is used not only for servicing a DBMS. One of the widespread solutions of a problem of high utilization of input-output is allocation of the system database of Microsoft SQL Server tempdb in the RAM.
System : Processor Queue is intended for diagnostics of queues of processors. If its value is more than two, it means, that the central processing unit works with overload. It is obvious, that additional processor powers are necessary for solution of this problem.
System : Context Switches/sec - context switching when the operating system or a database server switches service by the processor from one stream to the other what causes surge of utilization of the processor. If thus value of the meter System : Processor Queue > 2, try to change number of the streams used SQL Server. Process : Thread Count - number of active streams. Value of this meter along with System : Context Switches/sec can be used for optimal configuration of SQL server to reduce excessive CPU utilization.
Process : Virtual Bytes - allows to define, what memory volume uses SQL server and what applications use it insufficiently effectively. Process : Working Set - memory size used by the process. The modification of configuration customizations of SQL server after the analysis of these meters will allow optimizing a memory allocation between a database server, an operating system and other applications of a database server.
SQLServer : Cache Hit Ratio - for well balanced applications the number of hits in the cache should strive to 100 %. Often, reaching of a high level of hit in the cache is achieved by simple extension of the RAM. For more delicate caching regulation it is possible inspecting trace flag 1081, achieving, that index pages remain in the cache of the data longer, than pages of the data.
And in conclusion some tips to memorize:
To optimize allocation of SQL files of the server try to follow the following rules:
1) Always use disk arrays RAID. Their usage ensures reliability and productivity of a disk subsystem.
2) The more disks are in the array, the higher is the general productivity of the disk array. The more channels are on the RAID-controller, the better the bus is loaded. The more RAID-controllers are there, the more effective is the usage of the disk array. The more buses are on the motherboard, the more of the above-mentioned components it is possible to install in the server and to increase productivity as a whole (if processors ”cope”). Further, there will be already clusters. By the way, even in the cluster less system try to use RAID-controllers which can work in a cluster (for example, do not initialize SCSI bus when power is turned on).
3) Locate files of sequential access and files of a failure access on different disk arrays (not logical disks created on one RAID the array). As a rule, logs of transactions represent files of sequential access, and files of databases - a failure access. For example, you can place LOG-files on RAID 1, and the database on RAID 5 or 10 (= RAID 1 + 0). Further, if you do not want to mix your data with system ones, you may create two data files in two various file groups. The first file group will contain system objects, and the other will contain your objects. It will not bring any noticeable scoring in productivity, but will allow dividing essentially different data types. The principle of diversity of the data in file group according to access type to the information can be also applied to tables. Significant complicating of management will with interest be paid back by growth of productivity of the application.
4) Place files of sequential access on different disk arrays. Often the server should access several tables and - or indexes simultaneously. Optimal allocation of such files with the help of their inclusion in different file groups (located on different disk arrays) may also positively affect general productivity of the application.