Feed Icon  

Contact

  • Bryant Likes
  • Send mail to the author(s) E-mail
  • twitter
  • View Bryant Likes's profile on LinkedIn
  • del.icio.us
Get Microsoft Silverlight
by clicking "Install Microsoft Silverlight" you accept the
Silverlight license agreement

Hosting By

Hot Topics

Tags

Open Source Projects

Archives

Ads

Tripping Out SQL Server

Posted in Sql and Xml at Tuesday, December 7, 2004 4:01 AM Pacific Standard Time

Much of the work I've been doing lately is DBA type of work rather than development work. Even though I'm a SQL Server MVP, I'm still a newbie when it comes to managing databases where there are lots of developers and users. Because of this I've become a huge fan of fellow MVP Kimberly Tripp. I've watched her TechEd session on partitioning at least 10 ten times and I have used her techniques with great success. One of the best examples was a table which I recreated using horizontal partitioning.

The table had ~280 million rows and used up about 96 GB of space. The data stored in the table was historical data and a simple queries to get the number of rows for a certain date would take approx. 30 minutes. I redesigned the table (optimizing many of the column sizes) and used one table per year and a view to join them all together. The new tables together took up about 25 GB of space. Additionally, since I'm using multiple tables I can also only backup the most recent year which saves even more space since I'm using multiple files. Here is the real kicker though:

The simple query that took over 30 minutes now takes about 15 seconds.

Another example was using vertical partitioning. In this case there was a single table that was populated using data from multiple sources. There was a DTS job that populated the table which ran daily and took approx. 5-6 hours to run. Using vertical partitioning (meaning indexed views) I was able to create one table per data source and then join them with a schemabound view. Addionally I added many more datasources. The result:

The new DTS job completes in just over an hour.

The was due to the fact that I didn't have to pull down the data and do a lot of updates. Instead I could just truncate all the tables, pull in the data, and create my indexed view.

I highly recommend reading Kimberly's blog if you're not already and I would also suggest watching her TechEd sessions (if you have the DVD, if not you can buy it). She also has produced a webcast on indexes which you can watch for free and is also very good. There is lots of good info at her website as well.

 

Tuesday, December 7, 2004 3:36:00 PM (Pacific Standard Time, UTC-08:00)
Kimberly Tripp rocks!
<br>I attended a full day (9 hours) session on indexes by her, in Norway. Indexes has never seemed so interesting before.
<br>
<br>Kaisa
Wednesday, December 22, 2004 1:42:00 PM (Pacific Standard Time, UTC-08:00)
what kind of a name is Kaisa?
Wednesday, December 22, 2004 1:42:00 PM (Pacific Standard Time, UTC-08:00)
That is a pretty impressive reduction of time needed to acess the data. Bet the bosses loved that!
Comments are closed.