SQL - Microsoft SQL Server for Performance

Data is the biggest concern for any organisation to make a significant growth in their business. Business users want to fetch data as fast as possible, and write the data back into the database as fast as possible but database size grows with every passing day and we want to make sure that all operations are executing smoothly. So, we have to take care of the tuning and monitoring our database server for performance.

Microsoft SQL Server for Performance

To work around the SQL Server to improve the performances, we need to take care of the following points-

1)      Finding the Culprits- The first two things, which I need to be checked, are:

a)     The hardware and installation settings, which may need correcting since SQL Server needs are specific

b)     The correct T-SQL code for SQL Server to implement- Need to check all SQL threads which are running behind for the SQL codes. These threads would be helpful for diagnose performance issues with SQL Server and also with specific queries and batches. Need a closer look at CXPACKET wait type thread in SQL Server.

2)  Analysing the SQL Server Plan Cache - The cache management mechanism plays an important role in performance of any system

3)  Windows Reliability and Performance Monitor- Data Collector Sets and Performance Monitoring

4)     Gather IO statistics down to the SQL Server database - We need to determine issues with locking, blocking, fragmentation, missing indexes, deadlocks in SQL Server database file level.

5)     Finding problematic queries and tuning them

6)    Gather IO statistics down to the SQL Server database

7)     Execution Plan Re-use and find out reasons for overload SQL Server

8)     Checks for transaction log, tempdb and memory

9)  Review indexing strategies, maintenance them periodically and removing unnecessary indexes

10)  Fragmentations and Defragmentation of database and truncate the transaction logs