Response time and the throughput time are the two scales on which any Database server’s performance can be measured and Microsoft’s SQL server is of no difference. We can regularly use some monitoring tools to measure the performance of Microsoft’s SQL sever and it can be measure in Seconds for Response time and Transactions per Second for Throughput time. To describe the Response time is the time which is spent between the initiation and completion of any SQL Query. On the other hand the Throughput time is the number of transactions the server can handle in a given period of time which is usually one second. The performance of SQL server depends upon a number of factors ranging from the hardware to the software. When we talk about the hardware it could be the hard drive on which the database is stored or the server’s processor. It could also be the physical connection and the network speed, and when we talk about the software it could be the way the application is coded. Not only the applications coding but also the SQL queries themselves.
Let us discuss the various factors which could lead to poor performance of Microsoft SQL server and ways to improve the performance. First and the foremost thing to be checked is the Database application. The application code should be efficient and should use clustered indexes in its SQL queries. Ensure that the SQL queries written in the application are in such a way that they can be optimised by the server. Always go for multiple narrow indexes than wider indexes. Ensure that the data types are of same type in the database application and in the database. Make sure that all the tables in the database are clustered. Timely check for fragmentations, and especially for the index fragmentations. Use both SQL queries and Windows defragmenter for defragmentation, both physical and logical. Do not let the table rows to row more than 8000 bytes per row. In Microsoft SQL server 8060 bytes is the maximum page extent size, and anything more than this will result in poor performance.
To improve the performances make sure that the TempDB is located on a separate high performance drive. Just have 1 physical file per CPU core in the SQL server as this will result in increased and better input/output throughput from the processors.try using RAID 1 or RAID 10 for improving the performance. Check for database and see if it can be further normalised. Set up performance management tools and constantly monitor the performance in real time. Check for time periods in which the performance degrades and find ways to improve the performance in those times. Try upgrading the network, the processors on the server, memory and the cache memory on the server and the disk drives. Try using SSD or the Flash memory type disks in read type environment. Some tools which can be used for monitoring the Microsoft SQL server’s performance are SQL Server Management Studio, SQL Server Profiler, and Dynamic Management Views.
