Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 1234»»»

Partitioning in SQL Server 2008 Expand / Collapse
Author
Message
Posted Monday, June 17, 2013 8:27 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, April 21, 2014 6:54 AM
Points: 36, 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
Post #1464172
Posted Monday, June 17, 2013 8:38 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, July 18, 2014 9:09 AM
Points: 870, Visits: 2,385
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
Post #1464182
Posted Tuesday, June 18, 2013 1:32 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, March 21, 2014 9:46 AM
Points: 387, Visits: 1,078
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
Post #1464483
Posted Tuesday, June 18, 2013 1:41 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 11:54 AM
Points: 42,466, Visits: 35,530
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 2008, MVP
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

Post #1464487
Posted Tuesday, June 18, 2013 5:38 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, July 18, 2014 9:09 AM
Points: 870, Visits: 2,385
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
Post #1464586
Posted Tuesday, June 18, 2013 6:33 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 11:54 AM
Points: 42,466, Visits: 35,530
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 2008, MVP
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

Post #1464614
Posted Tuesday, June 18, 2013 9:05 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, April 21, 2014 6:54 AM
Points: 36, 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
Post #1464719
Posted Tuesday, June 18, 2013 9:12 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 11:54 AM
Points: 42,466, Visits: 35,530
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 2008, MVP
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

Post #1464723
Posted Tuesday, June 18, 2013 9:48 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, April 21, 2014 6:54 AM
Points: 36, 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

Regards,
Sai Viswanath
Post #1464749
Posted Tuesday, June 18, 2013 9:58 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 11:54 AM
Points: 42,466, Visits: 35,530
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


Hire a consultant. There are no silver bullets for performance



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1464758
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse