I've plan to apply Table Partitioned

Question

I've plan to apply Table Partitioned

Little Nick

SSCrazy

Points: 2202
More actions
January 14, 2012 at 9:26 pm

#253598

Hi,
My rows become increase day by day. FYI, I've 70 million rows saved into 1 table. Looks like it's not healthy. I've plan to apply Table Partitioned
I provide some info as following,
1. My present database, there was NO filesgroup. It's only PRIMARY
2. My tLeaveTrnx table have 70 million row
3. I've plan to partition this tLeaveTrnx table within month and year
4. Within month and year means all the data between Jan 2002 to Mar 2002 will be into A partition. Apr 2002 to Jul 2002 will be into B partition, Aug 2002 to Dec 2002 will be into another partition, and so on
5. I'm using SQL Server 2005 Enterprise Edition (64 bit)
Before to do that. I've several question as following,
1. Did I required to adjust my database filegroup?
2. It's a good idea or not to partition the data within month and year?
3. How many filegroup we can create? Up to how many?
4. How many partition we can create? Up to how many?
5. Did I need to create a NEW table with partition
6. If yes, how to transfer tLeaveTrnx data into new tLeaveTrnx table?
7. If no, it's possible to adjust the current table into partition?
What's can I say is I'm very junior for Table Partitioned

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 1

If you spend some time reading following article, you would be able to find the answers to your questions yourself. Ultimately you should understand the concepts before you implement Partitioning.

Partitioned Tables and Indexes in SQL Server 2005

http://msdn.microsoft.com/en-US/library/ms345146(v=SQL.90).aspx

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Before you spend lots of time doing this... What is the goal for partitioning? What is it you're trying to achieve? What problem are you trying to alleviate?

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Little Nick SSCrazy Points: 2202 More actions · Answer 3

GilaMonster (1/15/2012)
Before you spend lots of time doing this... What is the goal for partitioning? What is it you're trying to achieve? What problem are you trying to alleviate?

1. What is the goal for partitioning?

My answer: My application can query very fast and it's significant

2. What is it you're trying to achieve?

My answer: To boost my application performance

3. What problem are you trying to alleviate?

My answer: The damn slownest of my application

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Then forget about partitioning (at least for now).

While partitioning can improve query performance the queries mostly have to be of particular forms to take advantage of it (partition elimination) or there need to be multiple cores for paralleling over partitions, bitmap filters for partitions, etc. It they're not, partitioning can even slow queries down (if SQL has to seek on all of the partitions because the predicate doesn't include the partition column, for eg)

Partitioning is mostly about improved maintenance, fast loading into a table, fast removal of data (sliding window), rebuilding or reorganising indexes by partition rather than table, etc. It's not a silver bullet that you implement and suddenly your queries are fast.

For general performance, start here

http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/

http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-2/

If you need help with any queries, post them and someone will almost certainly help.

Also, consider archiving data. If all of those 70 million rows aren't required, you can start deleting ones that are not.

Lastly, consider getting someone in. I do a lot of this 'help, my application is terribly slow' work, and there will be people doing the same where ever you happen to be in the world. It won't be cheap, but good help never is.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

p.s. have a read through these two: http://msdn.microsoft.com/en-us/library/ms345146.aspx, http://msdn.microsoft.com/en-us/library/dd578580.aspx

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 6

@nick-2: I assume you didn’t explain the business case for partitioning but it might be just because you are new to DBA job (as you mentioned below). No worries. Please note the valuable suggestions in the thread and work upon them. They will help you anyways even if you implement or don’t implement partitioning.

I still believe your case needs partitioning. It’s a guess and only you can justify it. The article I suggested below has few business cases where partitioning is required. If you can just compare your business requirements with case studies in the article, you would know whether you need it or not.

Please feel free to revert back.

Little Nick SSCrazy Points: 2202 More actions · Answer 7

If 1 table contains 70 million rows, what should I do? As a result, I can query very fast

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

Little Nick (1/15/2012)
If 1 table contains 70 million rows, what should I do? As a result, I can query very fast

Write optimal queries that have appropriate indexes. I can't give you a more specific answer to such a general question. See the performance links I posted earlier for a 2-part article on identifying the worst performance problems in a database and some general advice on fixing it.

As I said, you can post here with specific questions.

There is no silver bullet for performance problems, no magical switch or setting that makes things go faster.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Little Nick SSCrazy Points: 2202 More actions · Answer 9

ok Mam.

I'll fix the

1. Query

2. Put an appropriate index