Table Partitioning
-
Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/awarren/3135.asp
-
Nice article. I'll be waiting for you next articles on the topic to get a glimpse into the lower level details and implementation of the concepts described esp: Horizontal Partitioning.
Best regards.
-
It is a nice article. Concerning vertical partitioning I'd recommend SQL Refactor. It has a table split refactoring that takes a table, allows you to specify which columns should stay/moved/copied to a new table, what the future relationship will be (1:1, 1:n, m:1), and creates a script for you that
- create the new table
- sets up automatically a primary key on this table (based on shared columns)
- copies unique data into this new table
- removes the necessary columns from the original table
- sets up the foreign keys as specified by the "future relationship"
- rewrites all your stored procedures, views, etc to reference a joined version of these
two new tables (if both of them are needed) or only the relevant table.
More about this at http://www.simple-talk.com/sql/sql-tools/faster-database-development-with-sql-refactor/
Regards,
Andras
Andras Belokosztolszki, MCPD, PhD
GoldenGate Software -
Great article. I just wanted to say that I have had some very large performance gains to query times when employing horizontal partitioning on biggish fact tables in a datawarehouse environment using SQL2000. When I first looked into this, a number of seasoned SQL professionals at my workplace had never heard of the 'partitioned view' functionality in SQL2000, allowing the 'union all' view over the tables to essentially mimic full table functionality, including provisions for insert/update/deletes without specifiying which underlying table we need to work on. My workmates didn't believe this was achievable until I put an example together, showing differences in query plans. Once I did this, they were mighty impressed (with the functionality, not just me!).
As mentioned in the article, other benefits available including shortening the time window required for backup and re-indexing. We also got these benefits thrown in for free, even though we were only targetting query performance.
So I encourage everyone to eagerly await the rest of the articles, as I will be.
Matt.
-
Great article Andy, thanks for taking the time to go over this topic (very timely for me
).I'm looking forward to more depth and details soon!
-Ron
-
Thanks Andy! I cannot wait for the followups.
-- Cory
-
Nice Article. Thanks Andy.
-
This is a very good article.
I just have one question or clarification. When you say
“In SQL 2005 we have true partitions, where we can define a table as residing on a partition and all the sub tables are managed by SQL, there is no view.”
Isn’t this only in SQL 2005 Enterprise Edition?
-
Im sorry if I didnt make that clear, you're correct; can only partition in Enterprise (or Developer of course) edition.
-
this article is good
i want some help to improve the performance of DTS package that holding Store procedure and before connection to flate file drpping the index and after that recreating the index.
In which are i have to work out for improving performance
-
I am sorry to pitch-in late, and would like to point out an important aspect. This is strictly with SQL Server 2005. In SQL Server 2005, Microsoft have introduced a new storage methodology called 'Row-overflow data'. With Sql 2005 we can now store rows that exceed 8060 bytes in size using variable length data types. With Sql 2000, a single row's data HAS to fit on a single page. However, in Sql 2005, and variable length data in a single row that exceeds the available page-space is pushed into a separate page, called a row-overflow page. A pointer is left in the original page of In-row data for the record pointing to the row-overflow page(s) that contain the given column's data for the row. When a request is made to fetch the data for the given row, the engine reads through the in-row data as normal, recognizes the pointer to the overflow data, jumps to the Row-overflow page(s) to fetch the data, then returns to continue normal operations. Now, I would like to point out few facts. To utilize the partitioning options there needs to be a triggered change in most part of your application. Also, the post has rightly concluded that it should be the last option to partition a table. That is why Microsoft has come out with this solution. This is an important point, which will let the DBA's forget about partitions. Also, more no of tables means more no. of joins while extracting the data.....I have never seen any partitioning scheme work properly in my life till now.....and how do you expect the indexes to be implemented......Partitioning is mostly effective if the table is not normalized....that tell that its a problem in grammer....archiving is also never a good way....
the best way?
Leave it on to SQL regarding how it saves( only tell it about the cluster index), and just scratch your head of how to retrieve data.....use lots of indexes......if you think that indexes only slow down transactions, you are only partially correct.....if properly used indexes in turn improves data delete(with a where clause) and update queries along with increasing the performance of select queries....
Also, SQL Server 2005 will save data in a separate page only if it exceeds 8090 bytes. But by partitioning the table, you are telling SQL Server to save it in different pages. Also, if two columns from two separate sub-tables are fetched, indexes will mostly fail...on the other hand if your query will be fast when you don't include bigger columns...that is all you have to do....replace bigger text data types, which by default store data outside the page, using varchar(max)....
To avoid table scans, you need to use column ids, wherever possible.....never use a star.....for complex joins of huge tables, create indexed views.....you will get an overwhelming performance improvement.....but partitioning....a big NO....
Chandrachurh Ghosh
DBA – MS SQL Server
Ericsson India Global Services Limited
Quality is not an act, it is a habit.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply