Archive for the 'Chapter 32' Category

14
Sep
10

More on Partitioning

Partitioning for manageability…Having to deal with large tables and expecting performance to be optimal can be a pretty tough task. I was supporting a client that kind of had this scenario where they had a ton of data and no delay when asking for it. It was actually one of my peers on this blog that introduced me to data partitioning. What ended up happening is that the data was partitioned off by date – Jan 1, 2010 – March 31, 2010 on one partition, etc… you get the picture. It helped tremendously by not bogging down the system and kept data access optimal (there was a lot of other “tweaks” which I will not get into).

This next chapter by Dan Guzman does an excellent job of explaining how to manage a large amount of data with partitioning. He goes through execution plans to show the differences with partitioned and non-partitioned data and the costs of each. Of course there are examples that you can try out yourselves. He also writes about some “gotchas” and offers tips so that you can “achieve a successful partitioning implementation”.

This is a great read just like all the others. Don’t forget to try the examples. Thank you for reading this week. Have a good one.

Advertisements
14
Sep
10

Chapter 32 – Partitioning for manageability (and maybe performance)

The second chapter in a row on partitioning and it’s another excellent chapter.  Dan Guzman – Blog – takes us on an adventure with this chapter in looking at execution plans between partitioning in 2005 and 2008.  In addition he takes the time to spell out what some good use cases are for obtaining additional performance gains from partitioning.

I’m glad that he took the time to do that and it’s one of the most mis-understood elements of partitioning.  Partitioning will not always increase your performance.  As a matter of fact, it could really, really degrade your performance if your partition key, storage and research are well planned. 

In my experience, there is almost always a good partition key which can be found in the data you are looking to partition.  Often it’s a datetime field such as CreatedDate, TransactionDate or InsertedDate etc…  you get the picture.  It’s a date that has a natural order to it over time.  It’s not to say that all records will always land in perfect order but it is to say that more often than not, they will.  Imagine using a column like “ModifiedDate” as your partition key…  every time someone or some process updated a record, it would potentially end up in a different partition; could create havoc.  Or even worse, a column like “birth-date”.  Chances are that very, very few of the Select statements against the employee table contain a where clause with a small range of birthdays…. i.e., I want to see all employees who were born from march – june of 1975.   What good would that do except for the monthly query that H.R. runs to send out Happy Birthday cards?

In closing, I’d recommend that you read this set of blog posts in conjunction with the chapters on partitioning to get started and learn a few things along the way.  https://sqlperspectives.wordpress.com/category/chapter-31/




Chapters

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 13 other followers