A SQL Server database administrator (DBA) will be aware of server enhancement techniques to ensure the smooth running of the servers. Being aware of enhancement options available enables to DBA to provide the best server performance to users and clients without interruptions or outages.
The ability to identify the faulty component in a SQL Server environment quickly ensures continued smooth operation. Budget and time factors should also be considered in the pursuit to attain the best performance from the server.
The best way to performance tune SQL Server is using the performance monitor process. Performance monitor is a process where performance statistics are checked regularly and saved in a log file. The DBA can then monitor the saved file and detect problems when they arise. The saved file can also be exported as an Excel file for analysis. This provides increased flexibility to set the basic formulae that can be the key for data analysis.
Performance monitoring is not only performance tuning of SQL Server as tuning can also be performed on Windows. Pptimize the speed by having the application run on the server or client end depending on requirements. Data validations on the client end will enhance the client performance and speed.
Information on performance tuning for SQL Server is a little difficult to come by but Windows optimisation information is abundant on the internet. Each application has its own set of statistics; therefore check the tips related to cluster SQL Servers and how to performance tune SQL Servers based on compatibility.
Performance Tuning SQL Server using Performance Monitor
A DBA frequently has a WorkStation switched on all the time for monitoring things such as logs and performance traces. Set up performance monitor by going to the control panel and accessing the administrative tools. With the administrative tools, performance optimisations can be selected.
The performance window the left side contains performance logs and alerts. Click on it to access the counter logs, from counter logs, select the performance counters set and they can also be periodically logged in a file.
There is one counter log listed by default, to create a new one right click on it and by select New Log Settings for customisations. Name it corresponding to the database server name since the log setting need to be unique to its server.
The Essentials of Performance Monitor Counters
To performance tune SQL Server, the computer name needs to be changed from the workstation name to the Cluster SQL Server name. Following the name change, the server will take a few moments to gather all information such as performance objects list, depending on the software installed on the SQL Server.
Select the Memory object from the dropdown menu in the performance object; observe that the list of counters changing. Click on pages/sec counter and select the Add button; select the ‘Available Mbytes’ counter and click add. Now SQL Server is ready for performance tuning monitoring.
Assuming everything is operating as per the plan, the counter log should be running; confirm this by observing the start option is grayed out by right clicking the new counter log’s name. If this is not done then there may be a problem with the permissions or with the username and password used. Change them by clicking on the properties and specifying new username and passwords.
To performance tune SQL Server, discard any half empty Rows and cells. Do this by highlighting the row and hitting Delete. Similarly, delete unwanted contents of Cells also.
Observe CPU bottlenecks of the cluster SQL Server for performance tuning. By examination of the pressure exerted on the CPU by the Processor Queue Length. If the cluster SQL Server is not working and the number averages 1 or more, it indicates that the system has queued a list of things to do but has currently put them on hold.
Often, the number exceeds 1 as other software is installed while setting up the webserver on the cluster SQL Server, thus the performance of the SQL server is hampered. It is advised to eliminate unwanted installed applications while performance tuning SQL Server.
Monitoring the performance statistics of the SQL Server involves observing when the ‘Processor Queue Length’ is displaying a number less than one. The best method to do this is by enabling remote desktop inside the cluster SQL Server. Precise performance tuning of a SQL Server is akin to practising quantum physics. Where to start is unknown; though the process can be started from anywhere. Performance tuning a SQL Server is based on when and how it’s started. Similar to a mechanic tuning a vehicle before it performs on a racing track. The mechanic, while tuning the vehicle will look at the materials, ensure the engine is in the right mode to race and eliminate other accessories such as music, air conditioning etc, that are not necessary for the race. SQL server performance tuning is done in the same manner. Evaluation of requirements, application of suitable objects and using only what is needed. More often problems will be regarding speed of the system; where the users find it difficult to work on a system that is very slow. Often the DBA immediately suspects the database as the culprit and starts looking for knobs and levers that can be adjusted to stop things adversely affecting the cluster SQL Server speed. Once the Performance tuned SQL Server is in place, adding the cluster SQL Server may be requested. This process involves more than just adjustments in the database as described below. A system is required to tune the cluster SQL Server. This system should contain all the components used in the procedure to help produce the enhanced output that the business requires. The motive to consider the entire system and its elements is that it’s unfeasible to effectively tune a system if the focus is limited to only one region. Regard the performance tuned SQL Server as a traffic system, where all points require examination to ensure the free flow of information. Hence, the entire system of the cluster SQL Server must be evaluated together; but the process understood in simple phases. Never assume that problems can be solved within minutes, it will likely take time to understand the problems. A systematic approach is required and following of entire steps in order. It is unwise to evaluate only the database while performance tuning. To tune the system consider all the parts that are used in the SQL Server. The first step of the process is knowledge of all the parts that have been used in the system. For the task, the manual that mentions all the parts used by the system is required. Evaluation is the next phase of performance tuning. In this phase, a list of parts to be tested is compiled and plans made accordingly. Collect the metrics of every system components although it is not necessary to know the metrics of every part. Check the database and evaluate the applications before comeing to a conclusion regarding where the problem lies. Once all the information is collected, the cluster SQL Server can be tuned to provide the maximum speed for users to work efficiently.
]]>