How to do basic Performance Tuning on Microsoft SQL Server

1)  FIND THE CULPRITS Like other software’s, we need to understand that MS SQL server is also a computer program but a complex program. Here Microsoft written this complex program. So if there is any problem with the SQL server, we need to understand why this program is not running as we expected. From SQL Server we need to pull and push data as fast & accurate as possible. If we face any issues, reasons may be SQL Server (complex program) needs certain hardware and installation settings which we are not providing properly. The way SQL Server implemented and the way it understands T-SQL code, we are not providing proper T-SQL code to it Even though MS SQL Server is a proprietary software, they provided us a lot of ways to understand the Server and what’s going on inside so that we can use it efficiently. If the SQL server is running without errors, first we need to calculate wait statistics of different threads. SQL server uses threads for every user request. Again a thread is nothing but another program inside complex program which is called SQL server (This thread is not Operating system thread on which SQL server installed. This is related to SQLOS thread which is a pseudo operating system for the SQL Server). We can find wait statistics using “sys.dm_os_wait_stats” DMV. There are many scripts online to query this view as per your needs. I like Paul Randal script(http://www.sqlskills.com/blogs/paul/wait-statistics-or-please-tell-me-where-it-hurts) [cc lang=”sql] WITH [Waits] AS (SELECT [wait_type], [wait_time_ms] […]