How to define performance? We could say that it is basically a response time for a request. While monitoring and tuning the performance, we must not just measure the response time but we need to monitor many factors affecting the performance. What's more, we need to find out the rot cause of why the system slows down. Based on previous experience it's good to consider the following points:
- Amount of requests: Just a benchmark value affecting the level of concurrency on data and CPUs used by requests.
- Space affected by the request: How much data is moved between hard disk and memory? Is this amount of data necessary for request fulfillment or could it be reduced?
- Request types: Is the instance of SQL Server being asked more for a lot of smaller random I/O operations or is it being asked more for long scanning operations such as aggregation...