Tip

Maximize SQL Server database performance by streamlining your data

As the databases behind websites and applications become bigger and busier, application architects and developers often need to figure out very quickly how to scale the database before the application becomes unusable under heavy traffic.

One of the best ways to avoid this scenario is to think big from the beginning and avoid storing everything in a single database. Classifying data into logical groups and storing it in databases separated by function will make scaling easier in the future. A well-designed application should be able to handle errors and, in addition to displaying an error to the user, log the error to the database. Also, many websites now track how visitors use the website, such as what they click on, so there are many events an

    Requires Free Membership to View

application must save to the database. In big and busy applications, this could add up to millions of rows very quickly. Over the years I have noticed that in many cases, even commercial applications save this type of data into the same database that drives the application. As a result, logging and event-capturing data contributes a lot to database growth. This approach results in adding to the cost required to host and maintain these databases.

Separating logging and event-capturing types of data from the data used by database applications gives you several advantages:

  1. If the database server becomes too busy to handle the load, you can move logging databases to another server. Moreover, you can spread logging databases across multiple servers to spread the load even further.
  2. This architecture makes it easier to move logging and event-capturing data into a central data warehouse and periodically purge the data from the logging database.
  3. Keeping this data separate allows the application database to remain smaller, hence easier to manage, administer or move a copy to the development, QA or staging environment.
  4. If a database server becomes busy, applications tend to log more errors due to timeouts and other issues on the busy server. If your application logs errors, it contributes to the server load even further and can potentially make the problem worse.
  5. If you are running logging databases on separate servers, you might be able to use the Standard Edition of SQL Server, since in many cases you will not need to have the same high availability as you might need with databases behind your applications.

More on SQL Server database performance

Learn the source of your database performance problems

Tips for tweaking SQL Server SharePoint database performance

This architectural approach to event logging also allows you to centralize logging data from multiple applications into a single place. Rather than going to multiple places to analyze data captured by your applications, you could log into one central location and then analyze data. I like the idea of keeping multiple, cheap logging servers and then pulling data daily into a data warehouse. This architecture makes it easy to add logging servers to the mix and also gives the option to redirect logging from one server to another, just by modifying a connection string in the application that uses the logging framework. Since each type of logged data may need to capture different pieces of data, you could initially store part of the data in an XML document. Then, as you move your data to the data warehouse, you can parse the XML nodes based on the event type and extract data into relational tables that make it easier to query and report on.

In terms of writing code for logging errors, you do not need to reinvent the wheel. There are several open source and publicly available libraries you can download and incorporate into your solution. One example is Log4Net, a .NET library that has been ported from the Apache log4j logging Framework. It is fairly easy to use and provides a good starting point. You can use it as is or extend it with your own code to handle your logging requirements.

Based on my experience, I would also advise you to develop a purging strategy right from the start. If you capture a lot of data, you might find yourself in a situation where you accumulate terabytes of logging data very quickly, making it harder to manage and purge. One solution is to classify your events and determine how long you want to keep each type of logged data, and then implement a purging job that will delete expired rows based on the thresholds you have defined. In some cases, you may need to keep only aggregations of the data, such as how many times per day someone clicked a button or how many database timeouts occurred during a certain time period. This architecture provides several advantages, among them spreading the load, centralized reporting and simplified high availability requirements. Following these steps will lead to improved SQL Server database performance.

This was first published in February 2013

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.