1. Check MSSQL version
Execute: SELECT @@VERSION;
Then search & update fixes from Microsoft for your version if any.
2. Check problems of hard disk
Free space? enough? speed? cache size? etc
Ref: http://www.sqlusa.com/bestpractices/configureharddisk/
3. Check Server Properties configured
On MS SQL Server Management Studio, right click on the server >> select Properties menu. Should check: Memory (enough? optimize?), Processors (balance? optimize?)
4. Check your maintenance plan
Do you have any maintenance plan? If not, should do it soon (on MS SQL Server Management Studio). If having, check if it runs well (check logs). The maintenance plan should do weekly, including:
+ Check DB Integrity
+ Shrink DB
+ Reorganize indexes
+ Rebuild indexes
Here is an example:
5. Check problems on reports's MS SQL server
+ Check in Activity Monitor (MS SQL Server Management Studio >> right click on the server >> select Activity Monitor). It helps to find out top expensive queries.
+ Check Standard Reports of the server (MS SQL Server Management Studio >> right click on the server >> Reports >> Standard Reports)
+ Check Standard Reports of the running DB (right click on the DB >> Reports >> Standard Reports).
6. Check in SQL Profiler
To find out more info (expensive queries? optimize?)
7. Check Parameter Sniffing
To find out if you have any problem on parameter sniffing
Ref:
http://www.sqlusa.com/bestpractices/parameter-sniffing/
http://blogs.msdn.com/b/turgays/archive/2013/09/10/parameter-sniffing-problem-and-workarounds.aspx
http://sqlblog.com/blogs/ben_nevarez/archive/2009/08/27/the-query-optimizer-and-parameter-sniffing.aspx
8. Feedback to development team or your provider
To optimize/re-engineer queries, stored procedures, etc.
Ref:
http://www.sqlusa.com/articles/query-optimization/
http://www.databasejournal.com/features/mssql/article.php/1565961/SQL-Server-Stored-Procedures-Optimization-Tips.htm
Nice week!
