There are some rarely used DBCC(Database Consistency Check), that will be useful while tuning the application for performance.

   1:  DBCC MEMORYSTATUS




Will provide detailed information sql server memory statics.



DBCC Resource 



   1:   DBCC TRACEON(3604)


   2:   DBCC Resource


   3:   DBCC TRACEOFF(3604)






Will provide sql server resource utilizations.



DBCC DBREINDEXALL



 DBCC DBREINDEXALL('AdventureWorks')


 


Will rebuild all the indexes for a user Database.




DBCC FLUSHPROCINDB 



 DECLARE @ID INT


 SET @id =DB_ID('pubs')


  DBCC FLUSHPROCINDB(@ID)


 


Will force the stored proc to recompile.