SQL DBAs (database administrators) are always looking for ways they can optimise their datasets. Even a small increase in performance can deliver dividends that translate into cost savings and efficiency gains.
DBAs should approach the 'tuning' of their datasets as an ongoing process and not a one-time fix. SQL deployments evolve over time and need to be reviewed on a regular basis.
There are some practical steps you can take now. One is to determine a baseline of current performance.
Before you begin to make actual changes to your SQL deployment it's vital to obtain a current baseline. After all, if you don't know the performance of your SQL dataset at the moment, how can you tell whether your changes have made any difference to its performance?
Think about what you need your SQL dataset to achieve and how you will measure this achievement.
Second, look closely at performance counters.
Your SQL datasets have a symbiotic relationship with the servers they are running on. To improve the overall performance of the SQL datasets you need to look at the server's available memory, CPU speed and I/O performance. Measuring these parameters will reveal bottlenecks.
Optimum plan cache
Third, ensure that the plan cache is running at optimum levels.
When a bottleneck is indentified in an SQL dataset, the workload that is causing it must be identified. This is a lot easier to do since the introduction of Dynamic Management Objects (DMOs) in SQL Server 2005.
Fourth is using SQL Profiler. Often an SQL DBA has to act like a detective looking for the reasons an SQL dataset isn't performing as expected.
One of the most useful tools that ships with SQL is the Profiler. DBAs can use this to trace events that could be having an adverse affect on their datasets.
There is a handy video tutorial about how to use Profiler on the SQLServerPedia website.
Finally, make sure that you manage bad indexes.
One of the main reasons that SQL datasets see a drop off in performance is because of bad indexes. Understanding how existing indexes are being use is also a great way to improve performance.
SQL Server 2012 provides some very useful data on indexes, which you can fetch using DMOs (Dynamic Management Objects) implemented in version SQL Server 2005. Indexes can show I/O bottlenecks, as SQL queries wait to be placed in the buffer cache.
Define goals
There are now many ways in which SQL DBAs can take more control of the datasets they are running. The key is to initially define your goals for the datasets you are running, and develop a set of metrics to measure these against.
Any changes that are subsequently made to the SQL datasets themselves, or the servers they are running on, can then be accurately assessed.
