December 3, 2014 at 9:54 pm
Hello all,
Greetings!
I need suggestion on Dynamic Partition of SQL Table. I have done my homework on this topic but still below questions are still stuck in my mind.
1. What is the best practice for partitioning (on date column)
2. The project on which i am working correctly have a case where in i get the update of my status flag after few days (Say 15 - 30) in that case if my data got into partition table how to update and how to search which partition has my data
3. Is creating partition occupies more disk space?
4. Is every partition would need index?
I am damn confused over this partition thing :crazy:
Please help in understanding the same
Thanks & Regards.
December 3, 2014 at 10:06 pm
BI_NewBie (12/3/2014)
Hello all,Greetings!
I need suggestion on Dynamic Partition of SQL Table. I have done my homework on this topic but still below questions are still stuck in my mind.
1. What is the best practice for partitioning (on date column)
2. The project on which i am working correctly have a case where in i get the update of my status flag after few days (Say 15 - 30) in that case if my data got into partition table how to update and how to search which partition has my data
3. Is creating partition occupies more disk space?
4. Is every partition would need index?
I am damn confused over this partition thing :crazy:
Please help in understanding the same
Thanks & Regards.
Heh... I don't blame you for being confused on this. MS didn't make it easy.
Let's start from the beginning, though. Why do you want to partition the table? What benefit do you expect to realize from a partitioned table?
--Jeff Moden
Change is inevitable... Change for the better is not.
December 3, 2014 at 10:14 pm
Hey Jeff!!
Thanks for your reply.
In my database, there is a transaction table whose row count is 117690142. There are index created on column but still when we fire any query it takes long time. So i was thinking of dividing data into multiple tables based of date; that's how i have come across the concept of SQL Partition.
Regards.
December 3, 2014 at 11:32 pm
BI_NewBie (12/3/2014)
Hey Jeff!!Thanks for your reply.
In my database, there is a transaction table whose row count is 117690142. There are index created on column but still when we fire any query it takes long time. So i was thinking of dividing data into multiple tables based of date; that's how i have come across the concept of SQL Partition.
Regards.
1) Partitioning was not added as a feature to SQL Server to make your queries run faster. It is a data management enhancement that can make loading and maintaining very large tables much more efficient and approachable. It CAN make your queries run faster, but that is NOT it's primary objective. It can also make things worse and comes with a LOT of caveats, provisos, limitations, gotchas, etc.
2) I do not agree with your statement that you have "done your homework" on the topic. You would not be asking the questions you did, or making the statement that you wanted your queries to run faster, if you were sufficiently knowledgeable on the topic.
Don't feel bad though. This is an ENTERPRISE topic that is VERY complex. You really should bring in someone with plenty of real-world experience and training on the topic to guide you in a) evaluating if you need partitioning in the first place (with a table that has just 100M rows I hardly think you do) and if so help set up all the stuff that needs to be handled to make it a win.
My first and overriding thought it that you simply need to tune your hardware, database and application. If performance is still unacceptable my next thought would be that your hardware is completely under powered. I can (and do for clients) process 100M-row+ datasets quite easily on my laptop.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
December 4, 2014 at 12:21 am
BI_NewBie (12/3/2014)
Hey Jeff!!Thanks for your reply.
In my database, there is a transaction table whose row count is 117690142. There are index created on column but still when we fire any query it takes long time. So i was thinking of dividing data into multiple tables based of date; that's how i have come across the concept of SQL Partition.
Regards.
Kevin is right. In fact, partitioning will frequently make your queries slower when compared to a monolithic table.
Do you have an example query that's running slow that you could show us?
--Jeff Moden
Change is inevitable... Change for the better is not.
December 4, 2014 at 12:25 am
Thanks Kevin for sharing your thought.
As i said i am very confused about partitioning thing that's the reason this question came up. Yes i have done my home work, may be the level of questions you were expecting ; they were not.
And i think when we start from A we learn better. i posted this question because i know i am going to get lot of suggestions from experienced or guru's of sql.
Thanks.
December 4, 2014 at 1:36 am
BI_NewBie (12/4/2014)
Thanks Kevin for sharing your thought.As i said i am very confused about partitioning thing that's the reason this question came up. Yes i have done my home work, may be the level of questions you were expecting ; they were not.
And i think when we start from A we learn better. i posted this question because i know i am going to get lot of suggestions from experienced or guru's of sql.
Thanks.
I have seen attempts to do table partitioning by those without experience over and over at clients and on forums and it almost never works out well and is often an unmitigated disaster. You simply cannot read a few blog posts, BOL, ask some forum questions and have a go at it and expect success. Sorry, but that is just the way it goes. 🙁
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
December 4, 2014 at 1:48 am
Partitioning is not a query optimisation technique. It's for data management. If you're trying to make queries faster, stop looking at partitioning.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 12, 2016 at 8:36 am
sort of newbie to SQL Server partitioning, there are replies in this thread that state partitioning will not be something that would help with query performance. However msdn states that one of the benefits of partitioning...
"You may improve query performance, based on the types of queries you frequently run and on your hardware configuration."
aside from making sure that you have the correct hardware and etc, surely the use of a partitioned table based on something like a month would provide a more efficient response in data retrieval. esp if your queries are based on the partitioned column thus avoiding to have to look at the whole table and only focus on the partition that contains the specific period?
wouldn't this case be a scenario that partition could work for both maintenance and query performance
-- Jeff
September 12, 2016 at 8:41 am
jpandrade (9/12/2016)
surely the use of a partitioned table based on something like a month would provide a more efficient response in data retrieval. esp if your queries are based on the partitioned column thus avoiding to have to look at the whole table and only focus on the partition that contains the specific period?wouldn't this case be a scenario that partition could work for both maintenance and query performance
No.
https://www.simple-talk.com/sql/database-administration/gail-shaws-sql-server-howlers/
Regardless of what MSDN states, partitioning is for data management, it does not automatically give performance improvements, it does not give improvements over good index usage in the general case, and without doing a lot more than just partitioning the table (in your example, a table clustered on the date column would avoid the need to look at the whole table and only focus on the specific period)
And MSDN isn't automatically correct. I can still find pages that state that indexes enforce physical storage order and that transactions can be nested, for example.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 12, 2016 at 8:59 am
jpandrade (9/12/2016)
sort of newbie to SQL Server partitioning, there are replies in this thread that state partitioning will not be something that would help with query performance. However msdn states that one of the benefits of partitioning..."You may improve query performance, based on the types of queries you frequently run and on your hardware configuration."
aside from making sure that you have the correct hardware and etc, surely the use of a partitioned table based on something like a month would provide a more efficient response in data retrieval. esp if your queries are based on the partitioned column thus avoiding to have to look at the whole table and only focus on the partition that contains the specific period?
wouldn't this case be a scenario that partition could work for both maintenance and query performance
-- Jeff
To add to what Gail and Kevin have stated and to answer the final question above... NO. Even so called "performance by partition elimination" is mostly a myth, IMHO. To date, I've been able to beat the performance of every instance Partitioned Tables that I've run into so far by quite a bit using monolithic tables and the correct indexing.
That's a part of the myth that partitioning provides performance benefits. Its normally the indexing that you have to do to make partitioning work that does it. Those same indexes work even better on monolithic tables. And, the example that Gail gave is one of the better examples where the application of a narrow Non Clustered Index blows the doors off of Clustered Index usage and you don't need portioning for that. It works on both partitioned and monolithic tables but will work faster on the monolithic tables for reasons that Gail or Kevin could explain much better than I could. I'd only end up mumbling something about multiple B-TREEs and not very accurately.
There can be huge benefits to partitioning but, to date, performance of code isn't one of them.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 12, 2016 at 9:11 am
BI_NewBie (12/3/2014)
Hey Jeff!!Thanks for your reply.
In my database, there is a transaction table whose row count is 117690142. There are index created on column but still when we fire any query it takes long time. So i was thinking of dividing data into multiple tables based of date; that's how i have come across the concept of SQL Partition.
Regards.
Missed this. That's a nice size table but, if your current query is having problems with that even with the index in place, partitioning isn't the answer. Either the wrong index has been chosen or the query is not able to use it properly. Both probably need to be fixed.
That, not withstanding, partitioning that table properly can significantly reduce maintenance and backup requirements (as well as other benefits) and is worth looking into. But, as already stated by many, performance of code isn't one of the reasons to partition because partitioning doesn't improve performance.
Both Gail and Kevin are also correct in saying that even MS/MSDN documentation on the subject is misleading or, in some case such as claims of code performance, is incorrect. And, as Kevin stated, there are a ton of caveats with partitioning. My thought on partitioning is "If you think or thought partitioning was easy, you're either getting ready to do it wrong or already have done it wrong". 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
September 12, 2016 at 9:25 am
Jeff Moden (9/12/2016)
BI_NewBie (12/3/2014)
Hey Jeff!!Thanks for your reply.
In my database, there is a transaction table whose row count is 117690142. There are index created on column but still when we fire any query it takes long time. So i was thinking of dividing data into multiple tables based of date; that's how i have come across the concept of SQL Partition.
Regards.
Missed this. That's a nice size table but, if your current query is having problems with that even with the index in place, partitioning isn't the answer. Either the wrong index has been chosen or the query is not able to use it properly. Both probably need to be fixed.
That, not withstanding, partitioning that table properly can significantly reduce maintenance and backup requirements (as well as other benefits) and is worth looking into. But, as already stated by many, performance of code isn't one of the reasons to partition because partitioning doesn't improve performance.
Both Gail and Kevin are also correct in saying that even MS/MSDN documentation on the subject is misleading or, in some case such as claims of code performance, is incorrect. And, as Kevin stated, there are a ton of caveats with partitioning. My thought on partitioning is "If you think or thought partitioning was easy, you're either getting ready to do it wrong or already have done it wrong". 😉
I will agree with all that partitioning a table isn't going to improve performance of a select. I wouldn't even consider a partition on something as small as 117M rows normally, without testing it to verify that it is going to give me a good bang for my buck.
It is nice to throw out useful information, but I don't think it should be a global rule of thumb. I actually have received performance enhancement on partitioned tables (though I am on a warehouse with billions of rows per table) but it entailed more than simply partitioning the table. I also partitioned the database into various file groups across a variety of spindles.
Did I do this for every table? No. But there was a lot of testing and headaches to get this database where it is today.
Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan
September 12, 2016 at 11:58 am
Sounds like you know what you are doing Steve. Kudos for you! Do note that we don't get a whole lot of that on these forums (because if they did know things they wouldn't be that likely to post up typically), so nothing personal for covering the basics rather bluntly. 🙂
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
September 12, 2016 at 12:09 pm
TheSQLGuru (9/12/2016)
Sounds like you know what you are doing Steve. Kudos for you! Do note that we don't get a whole lot of that on these forums (because if they did know things they wouldn't be that likely to post up typically), so nothing personal for covering the basics rather bluntly. 🙂
Thanks Kevin.
I just think that if someone asks a question that they should get an answer that they can learn from. If they knew the answer they wouldn't be asking the question.
I had stopped coming on here quite awhile ago because of similar things, and I actually found an individual looking to be hired for his answer(s). I do not believe that this forum is for that. It has been less than a month that I came back here.
Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply