Maintain Performance Statistics

Maintain Performance Statistics
If you maintain a history of the performance statistics on your database servers there are a number of uses for that data. Here is a quick brainstorm of things I have done or read that others are doing.

1. When performance appears to degrade you can compare it to a baseline and historical trend to determine possible causes and solutions
2. The history can be used to provide trend analysis. The trends may help you predict future capacity needs, recommend SLA capabilities, determine maintenance windows
3. History of multiple machines can be used to balance resources
4. The data may be used to optimize the configurations based on actual usage

I found one easy and inexpensive way to gather and mine these statistics is to create a windows Performance Monitor task with the output redirected to a database. With the data in SQL Server mining becomes a matter of preference.

You can write queries for specific scenarios. That data can be rolled up or brought out in detail to tools like Excel or even reporting services. Meaningful graphs and charts help see the big picture.

This doesn’t take a lot of overhead. If you are maintaining these statics over a long enough period of time you don’t need to capture as often. For long term statistics you could take a snapshot ever minute or even every five minutes.

If you are trying to diagnose periods of peak performance issues, you could capture on a much smaller scale, for a shorter period of time. Perhaps you could take a snapshot every second for a few hours.

Regardless of the periodicity used, historical statistical data is ultimately a real gem for those responsible for the performance and reliability of a SQL Server engine, and this can be done effectively in a few minutes or hours, depending on how fancy your results need to be.

Reader Feedback – Year End Tasks
John:
I currently have a SSIS running a query on table sizes that saves the results to a table on another then mails the. I al*so have a Powershell script that (I haven’t wired this up for mail delivery………….yet):

1. Sweeps both sql logs and events logs on all servers
2. Combines them into one uniform file so I can see the SQL and Eventlogs in a "stream of time" type of format

Checks the log space and memory utilization

Have you got your monitoring configured? What tools are you using? Do you find canned products to be your preference, or have you rolled your own monitoring techniques? Share your ideas by writing to btaylor@sswug.org.

Cheers,

Ben

$$SWYNK$$

Featured Article(s)
Using Oracle XQuery to construct XML from relational data
In some situations, you may need to convert your relational data into XML format. For example, you might need to generate XML from relational data if you want, for example, to submit your data to a Web service. To do this, before the introduction of Oracle Database 10gR2, you had to use SQL/XML generation functions, such as XMLElement and XMLAgg(). However, starting with Oracle Database 10gR2, you can use XQuery instead.

Featured White Paper(s)
How to Use SQL Server’s Extended Events and Notifications to Proactively Resolve Performance Issues
read more)