SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Partitioning in SQL Server 2008


Partitioning in SQL Server 2008

Author
Message
Sai Viswanath
Sai Viswanath
Valued Member
Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)

Group: General Forum Members
Points: 66 Visits: 124
Hi All,
I have got few queries on partitioning in SQL Server.
- Why should we go for partition in SQL Server?
- How do we decide on which column should the table get partitioned?
- Can I place few tables in db on partitioned, and rest on Primary? Or should I place the rest of the tables on NonPartition?
- Once I partitioned my db, what should I do on my db server i.e., do I need to run any dbcc commands etc.?
- If not partitioning, do we have any options through which we can improve the performance of an db?

Regards,
Sai Viswanath
Jason-299789
Jason-299789
SSCarpal Tunnel
SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)

Group: General Forum Members
Points: 4775 Visits: 3232
Partitioning table in SQL server is generally considered a maintenance process.

As for the column, most people I can think of use a Time based solution (Period, Year, Day) depending on data volumes.

You can place the Paritions either on seperate file groups or on the same filegroup, Seperate file groups tends to make more sense. Un- partitioned tables would ideally be left in thier own file group (Primary or others).

Once paritioned you shouldnt need to run any DBCC commands unless you are moving data and want to check the integrity afterwards.

Partitioning tables is not a performance tuning tool, work on the queries and tune them using indexes, or writing better SQL. However splitting the database into seperate file groups for Data, Indexes and binaries can help but I would suggest reading up on MS best practices for this.

_________________________________________________________________________
SSC Guide to Posting and Best Practices
SQL Show
SQL Show
UDP Broadcaster
UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)

Group: General Forum Members
Points: 1443 Visits: 1078
This is a excellent url I have seen.It may help.

http://www.sqlskills.com/resources/whitepapers/partitioning%20in%20sql%20server%202005%20beta%20ii.htm
GilaMonster
GilaMonster
SSC Guru
SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)

Group: General Forum Members
Points: 215704 Visits: 46270
Jason-299789 (6/17/2013)
Once paritioned you shouldnt need to run any DBCC commands unless you are moving data and want to check the integrity afterwards.


???

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


Jason-299789
Jason-299789
SSCarpal Tunnel
SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)

Group: General Forum Members
Points: 4775 Visits: 3232
GilaMonster (6/18/2013)
Jason-299789 (6/17/2013)
Once paritioned you shouldnt need to run any DBCC commands unless you are moving data and want to check the integrity afterwards.


???


Sorry Gail,

If you are adding a partition to the table or spliting a table into partitions that have data already, then you might want to run a DBCC to check the integrity once the split has occured and the data has been reassigned, only if you're paranoid or as a belt and braces check.

_________________________________________________________________________
SSC Guide to Posting and Best Practices
GilaMonster
GilaMonster
SSC Guru
SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)

Group: General Forum Members
Points: 215704 Visits: 46270
Jason-299789 (6/18/2013)
If you are adding a partition to the table or spliting a table into partitions that have data already, then you might want to run a DBCC to check the integrity once the split has occured and the data has been reassigned, only if you're paranoid or as a belt and braces check.


What exactly prevents the IO subsystem from corrupting said existing, static, read only data?

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


Sai Viswanath
Sai Viswanath
Valued Member
Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)

Group: General Forum Members
Points: 66 Visits: 124
Hi All,
Thanks for the response, and now one more doubt which might look funny. The db on which am assigned to work is used for report generation. The tables that I need to check do not have :
- Any official primary keys defined in the table.
- No reference i.e., foreign keys available.
- Almost all the columns have NonClustered index defined on Primary file group.
- One of the column (or group of columns i.e., composite) is defined as Unique, Clustered index on Primary.

In this scenario how do I go for defining of Partition on the table(s). My client is hell bent to have the partitioning concept implemented, reason he got his db's migrated from SQL 2K to 2K8 and wanted to implement the features.

Regards,
Sai Viswanath
GilaMonster
GilaMonster
SSC Guru
SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)

Group: General Forum Members
Points: 215704 Visits: 46270
Sai Viswanath (6/18/2013)
My client is hell bent to have the partitioning concept implemented, reason he got his db's migrated from SQL 2K to 2K8 and wanted to implement the features.


Why? What's the goal, what does he want to achieve?

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


Sai Viswanath
Sai Viswanath
Valued Member
Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)

Group: General Forum Members
Points: 66 Visits: 124
Hi Gail,
Simple he wants to improve performance (as told by the development team). The application is quite old, and the existing db design dose not suit the current requirement. Simple example - for generation of reports the developers have written dynamic queries, linking multiple db's and storing the query in MS-Access, and then creating a View in SQL Server and execute them to display the report. Sorry, it looks very confusion, but that's what I had understood on having discussion with the development team for the last 2 weeks. Now, I have another 10 days left to do miracles Hehe

Regards,
Sai Viswanath
GilaMonster
GilaMonster
SSC Guru
SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)

Group: General Forum Members
Points: 215704 Visits: 46270
Sai Viswanath (6/18/2013)
Hi Gail,
Simple he wants to improve performance (as told by the development team).


Then suggest to him that tuning the queries is the appropriate path as partitioning is not for performance.
https://www.simple-talk.com/sql/database-administration/gail-shaws-sql-server-howlers/

Now, I have another 10 days left to do miracles Hehe


Hire a consultant. There are no silver bullets for performance

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


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search