Performance is the most important thing that you have to think about, if your database performance is low then you will face with a lot of calls, emails, and client (customer) dissatisfaction.
A lot of people when got a problem with performance and customer start complaint about the performance of the system they always choose the easiest (and costly) way to handle this issue by asking about new RAM, CUP, DISK. But does that solve the problem? Of course not, the problem will appear again.
So you need to consider many factors to analysis your database server performance, the problem may be occurred because of many different causes:
· Server Resources.
· Database Design.
· Indexes Design.
· Dead Lock.
· And others.
For each of above you have tool/tools in SQL Server to handle the issue.
Performance Tuning Tools:
· Windows Performance Monitor (perfmon).
· SQL Server Build-in Reports.
· SQL Server Activity Monitor.
· SQL Server Profiler.
· Database Engine Tuning Advisor (DTA).
· Dynamic Management Views (DMVs).
Windows Performance Monitor:
By using performance monitor (perfmon) you can analyze your processor, memory, desk, and network; for instance you can get a good indicator that you have a memory problem by using page life expectancy performance counter but this doesn’t ensure that the problem is actually form your memory may be there is a table scan that affect your memory by fetch a lot of pages in RAM so the page life expectancy will be small.
You can find many of important performance counter in http://elmozamil.blogspot.com/2012/11/system-performance-counter-for-sql.html and the meaning of each one and measurement.
Note: the result form this tool will give you indicator on which area your problem come from, so you need to take this result in your consideration and move to analysis by another tool.
For example if there is a long queue in your processor this indicate that you have processor problem, you don’t need to install another process but you need to optimize your processor by making sure you have a proper indexes, lock escalation, and sometimes this problem occur when you have a full database backup run at that time so all you need may be to move your database backup task to another time and so on.
SQL Server Build-in Reports:
I always start my analysis with SQL server build-in reports, those report provide you with a lot of helpful information that may indicates your cause of the problem.
For instance Disk Usage report (in SQL server 2008) will give you information about your disk space usage by data files and log file, and it provides useful information on file growth this information it really helpful it indicate how your file is frequently grow. If you find you have log file growth every 1 minute you have to think about that why log file grow every one minute you may decide to increase log file space or growth amount option so it will be grow every 12 hour and so on.
To open SQL Server Build-in Reports right click on your server choose report, or right click on a specific database and choose reports. I encourage you to check and be familiar all information in all reports.
SQL Server Activity Monitor:
This tool provide information about Processes, Resource Waits, Data File I/O, and Recent Expensive Queries by using this tool you may find that your data file for specific database has a lot of read write operation, there are many queries that execute in log time.
For example if you find that a data file has a lot of read write operation you may think to move it to a different disk so no one compete with it for resource and reduce the chance of dead log to occur and wait.
This tool give the option to kill a specific process, if you find for example a process that make long wait and hold resource you may kill it and let other processes to do their jobs.
To access SQL Server Activity Monitor right click on the server and choose activity monitor.
SQL Server Profiler:
It works like network package sniffing (package viewer –wireshark-) tools.
Profiler give the ability to view the actual query run against database so you can analyze each query to check which column used in joins, where condition. You will find a lot of good information by using this tool.
To access Profiler go to tools choose SQL Profiler.
Database Engine Tuning Advisor (DTA):
DTA suggest indexes to be created and to be dropped. To use DTA first you have to capture trace by using SQL Server Profiler.
Dynamic Management Views (DMVs):
DMVs provide information relate to database, indexes, memory usage, lock, deadlocks, and many other helpful information.
You can find a lot about it on