Hi Experts,

I've started looking options to make one of my databases tables performance improved.

the tbale is of 45 Gb in size, having so many records like 200 Million records. i'm thinking about partitioning horizontally based on a date time column, one partition for every two years.

how can i call that table from application? I mean, from application, is Dev team has to modify their application coding to refer to these partitions? if Yes, the table is being called in so many places in the application, which is a big change.

please suggest on this.

thanks in advance

The table will stay the same after you partition your table. So you do not need to change anything from your application side.

No, you do not need to make any changes to applications. 

Partition logically splits the data based on your partition column\function into different units but it will one table.

All the data really exists in that table only, it is split internally across multiple filegroups. your application does not need to know on this. the query optimizer does that work for you to determine which partition it should access to get the data the query needs. you can still refer it with the table name, just like how you normally would . No application changes are needed. just make sure you choose right partition strategy to reap the benefits. 

I'd recommend you read this article:

https://www.simple-talk.com/sql/database-administration/gail-shaws-sql-server-howlers/

and this article:

http://sqlperformance.com/2013/09/sql-indexes/partitioning-benefits

In summary you'll need to do a lot of work to get to the point of getting big performance benefits from partitioning - unless you have some particularly fast disk you can take advantage of. 

• Proposed as answer by Prashanth Jayaram 16 hours 38 minutes ago

Check your SQL Server version as Partitioning is not a feature available with Standard Edition or any edition inferior to Standard.

>>how can i call that table from application? I mean, from application, is Dev team has to modify their application coding to refer to these partitions? if Yes, the table is being called in so many places in the application, which is a big change.

There is no difference in calling. The procedure remains same. No change required in the application design and coding.

I agree with Tony C-UK. Partition is Enterprise feature. Make sure you check the SQL Version. It might not give a better performance in all the scenario. With proper indexing strategy may benefit in most of the scenarios.

If you think of Partitioning, make sure you have a good test cases built to justify performance improvement.  Partitioning is good where you want to define a archival process.

I would recommend to read and understand the link shared by jmcmullen.

--Prashanth

Experts,

Awesome. thank you so much. i'll start exploring about partitioning. this feature is really awesome. we are using sql server 2008 r2 SP2 (of cource moving to SP3 in few days). adding to that, we are using Merge replication.

Hope merge replication will allow this, and will not degrade any performance impact.

Once again, thanks to you all Experts.

Hello Jmcmullan,

I followed the link. actually iam missing something here. I did not see any relation between these partition function and schema with the tables mentioned. Gail mentioned about PartitionTableDefinition.sql, but i could not find that on the site. please help.

https://www.simple-talk.com/sql/database-administration/gail-shaws-sql-server-howlers/

Thanks in advance

Hi Vinod

I think the filename referenced in the article might be wrong. At the top of the article you'll see a download for Howlers1.sql which has definitions for partition function and schema. 

You'll see there how the clustered index is created on the partition scheme, i.e.

CREATE CLUSTERED INDEX idx_Orders_OrderDate ON Orders_Partitioned (OrderDate)ON PartitionToPrimary (OrderDate);

hope that helps

Wonderful.

thank you so much expert.

You just need to partition the table that would help you in gaining the performance while querying and easier data manageability. From application point of view table would remain the same.So no code change requeired.

You can create the partition on datetime column and add secondary filegroups this will help you in data manageability as well as backup.