June 17, 2013 at 8:27 am
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
June 17, 2013 at 8:38 am
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
June 18, 2013 at 1:32 am
This is a excellent url I have seen.It may help.
June 18, 2013 at 1:41 am
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
June 18, 2013 at 5:38 am
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
June 18, 2013 at 6:33 am
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
June 18, 2013 at 9:05 am
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
June 18, 2013 at 9:12 am
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
June 18, 2013 at 9:48 am
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
June 18, 2013 at 9:58 am
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
June 18, 2013 at 4:12 pm
Since it seems you've been harnessed to the wagon of partitioning for good or evil...
The super cliff notes:
Things to consider before moving into partitioning...
-You want to partition on the clustered index, and you want the clustered index to be growing, preferably by date. You do not want to partitioning a GUID column (a whole 'nother conversation).
-I have partitioned extensively on INT/BIGINT columns. This works, but I would only say it would be easy on tables you can partition from the start. If they are telling you to partition old data - it gets messy. I've had to build extensive logic to do this.
-Figure out how you want to break the data up: dayofyear/week/month/year.
-Do you need to put each of these on it's own file/filegroup?
-If so, our methodology is:
<DBNAME>_FILEGROUP_<PARTITIONGROUP>_YEAR_<xxxx>_MONTH_<xx>_WEEK_<xx>_DAYOFYEAR_<xx>
<DBNAME>_FILE_<PARTITIONGROUP>_YEAR_<xxxx>_MONTH_<xx>_WEEK_<xx>_DAYOFYEAR_<xx>.ndf
(Cut it off at the segment you're using...)
While presumptive: unless you must, don't got smaller than a month just so you don't freak out your sysadmins. You'll have to explain 365 new files every year because you're splitting on DAYOFYEAR. Make sure your reasoning is good.
-Partitioning is a hassle on an immature product. If they are adding many indexes, you have to insert yourself to make sure that those new indexes are partition aligned. Otherwise, you lose the ability to switch partitions in/out.
-As soon as I heard MS Access I began to scream at my screen uncontrollably. But that also means it's unlikely that they are using change tracking, but if they are, it also means that you won't be able to switch partitions in or out.
-Partitioning can live with full text indexes, but you must turn it off on any tables you intend to partition for the initial partitioning work.
-Keep in mind moving the data onto partitioning is the same as an index rebuild. It might be too big of a IO bolus.
-Lastly (and I probably should've started with this...), you only mention that they moved to SQL 2008. Partitioning is a SQL enterprise feature. Did they buy that edition?
While this is a big subject... my closing comments:
It's hidden. Only eggheads like yourself (or sysadmins that see your files) will really even know it is there. This is both good and bad. Good: out of sight-out of mind = they typically won't mess with it. Bad: people don't think about if the things/features they want to do to the system will muddle with the partitioning. (adding change tracking, etc)
If you were among those who view the truth as "flexible" (i.e. previously employed as a defense attorney): there is the following "technically accurate" statement:
We've succeeded! It's partitioned! Where is my paycheck? 😀
(Technically every table in SQL 2005+ is partitioned - it just uses a single partition and no visible function/scheme...)
June 18, 2013 at 4:47 pm
Hi Gail,
Am the so called consultant they have hired. My background is - Data Modelling, and they have picked me as Sr. DBA for this project. Fixing of queries for performance is no way possible, reason am not getting a chance to view them while generating the report. So, I had to do something on the db.
Regards,
Sai Viswanath
June 19, 2013 at 12:38 am
Sai Viswanath (6/18/2013)
reason am not getting a chance to view them while generating the report.
Profiler, Extended events, plan cache. Many ways to get the queries without needing to see the report
So, I had to do something on the db.
So you're doing something complex that won't improve performance and you know that it won't improve performance? Ok.....
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
June 19, 2013 at 1:54 am
Hi All,
One more doubt on partitioning.
- Do we need to have a primary key in the table which needs to be partitioned?
- Can I use a composite key index to work with partitioning? Ex - In my table I had columns from Col1 .. Col10, and I had created an Unique, Clustered Index by combining - Col1,Col2,Col3. Now, can I go ahead in partitioning my table.
Regards,
Sai Viswanath
June 19, 2013 at 2:34 am
Sai Viswanath (6/19/2013)
Hi All,One more doubt on partitioning.
- Do we need to have a primary key in the table which needs to be partitioned?
- Can I use a composite key index to work with partitioning? Ex - In my table I had columns from Col1 .. Col10, and I had created an Unique, Clustered Index by combining - Col1,Col2,Col3. Now, can I go ahead in partitioning my table.
Regards,
Sai Viswanath
Please read through the article[/url] that Gail has suggested
There are a few more resources on partitioning here [/url]
Partitioning is not some magic wand to improve performance and you should never expect miracles
You will have to analyze a lot of things before you finalize some strategy to improve performance
As you are a Sr.DBA, you should tell the client the same with reasons failing which you might be in trouble when the 10 days get over.
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Viewing 15 posts - 1 through 15 (of 34 total)
You must be logged in to reply to this topic. Login to reply