sql server performance optimization steps

 sql server performance optimization steps

 Investigate first and take action.

Step : 1  Check block query


              select *from sys.sysprocesses WHERE BLOCKED >=1

             check SP ID which query/SP is blocked

              dbcc inputbuffer(<<SPID>>)

              Kill SP ID

              Kill <<SPID>>


Step : 2   DBCC checkdb

Step : 3  Sp_updatestats

Step : 4  sp_msforeachdb 'checkpoint'

Step : 5  Some use begin_tran forgot to commit  statement. Because of blocking happen.
              Action :  Restart sql service.

Step : 6  CPU usage goes high, No doubt missed index in your database.  using below link to find missed index and create indexes.

http://selvasqlserversolutions.blogspot.in/2014/02/missing-index-script-sql-server.html