.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
david stephan
Gaurav Pal
Post New Web Links

Partition Strategy

Posted By:      Posted Date: October 28, 2010    Points: 0   Category :Sql Server


We currently have a large cube with quite large amounts of data.  We currently store the last 2 complete years and the current year.  The current partitioning strategy is one partition per year of the first 2 years, the current year excluding the last 21 days, then the last 21 days.  This last partition will build up with the current data and is processed daily.  Once a week we do full cube rebuild and the last partition will reset to just 21 days again.

Current Size
Year Num Rows

We don't have aggregations as we are using Measure Expressions (Exchange Rates) or have Distinct Count measure groups.

This is working ok, but obviously not optimal.  I was going to start by splitting these partitions down smaller (say to by Quarter) and for the distinct count measure groups, using the SQLCat recommended optimizations.  Then I got wondering.  Would it be better to split the non DC measure group by the DC optimizations too?  My thinking is that because we do not have aggregations, we would probably be putting more strain on the storage engine due to repeated fe

View Complete Post

More Related Resource Links

Critique this strategy


I want to populate a gridview by using jQuery and AJAX.  From my calling page jQuery will call a handler (.ashx) that will deliver XML data or HTML markup for the gridview. As I see it I have two choices: 1) deliver XML which is then bound to the design-time gridview on the calling page, or 2) deliver HTML for the gridview.  My first question is: which method is easiest?

Now there are two factors which complicate things. First, the gridview must be sortable on all columns.  Second, the data will be filtered (some columns will be hidded) by user configuration options which are also to be stored in the database.  Knowing this, does your answer to the first question change?

Any comments, insights or gotchas are appreciated.


Partition Query

Hi All, I have cube with 10 partition ,i want to process only last partition with with last 30 days . Now i want to know when i processed this partition of 30 days whether data of 30 days updated or get added with the existing one ...if getdate-1 havunf value 10 in column a before 30 days partition will become 20 after i process only 30 days partition alone   Kindly suggest Amit

how to change a remote partition into local partition

Our production cube is very huge and the process time is very long (10+ hours), and the data volume is still increasing. Since we have some idle server during cube process period, I'd like to try to put some partitions on the idle server to cut the process time. But I also want to bring these remote partitions back to local after the process is completed. Is it possible to change partition from remote to local? if so, how?

ROW_NUMBER() OVER - PARTITION BY should be different from ORDER BY

I have noticed incorrect usage for OVER - PARTITION BY - ORDER BY with same (BY) column lists, making the query nondeterministic as far as the generated ROW_NUMBER() is concerned.  If random sort needed, use NEWID() instead. Demo follows. -- WRONG PARTITION BY ... ORDER BY ... usage - nondeterministic sort SELECT ProductName = Name, Color, PartRowID = ROW_NUMBER() OVER(PARTITION BY Color ORDER BY Color) FROM AdventureWorks2008.Production.Product WHERE Color IS NOT NULL ORDER BY Color, PartRowID /* ProductName Color PartRowID .... Men's Bib-Shorts, L Multi 3 AWC Logo Cap Multi 4 Long-Sleeve Logo Jersey, S Multi 5 .... */ -- CORRECT PARTITION BY ... ORDER BY ... usage - UNIQUE sort (Name is unique) SELECT ProductName = Name, Color, PartRowID = ROW_NUMBER() OVER(PARTITION BY Color ORDER BY Name) FROM AdventureWorks2008.Production.Product WHERE Color IS NOT NULL ORDER BY Color, PartRowID /* ProductName Color PartRowID .... Long-Sleeve Logo Jersey, M Multi 3 Long-Sleeve Logo Jersey, S Multi 4 Long-Sleeve Logo Jersey, XL Multi 5 Men's Bib-Shorts, L Multi 6 .... */ Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM

Confused about Disk Alignment/ Partition Offset

I am all confused by disk alignment, partiton offset.Why do we need to calcualte offset which most of the articles talk about, This is from White Paper  Disk Partition Alignment Best Practices for SQL Server where its stressed that Partition_Offset ÷ Stripe_Unit_Size-- should be an integer 'The performance question here is usually not one of correlation per the formula, but whether the cluster size is the NTFS default of 4,096 bytes or has been explicitly defined at 64 KB, which is a best practice for SQL Server.' Also how do we calculate Stripe Unit Size for SAN. Thanks all for your replies !!  

IS There is any way out to process only one partition of cube and data of other partition remain ava

HI ALL. Please let me know is there is any way out to process one partition only,while data of other partition remain available in the cube with the help of SSIS

What does strategy exist to deploy SSIS package and my own data flow components into a enterparise s

I created a SSIS package and several data flow componenets for this package.    What does strategy exist to deploy SSIS package and data flow components into a enterparise server?   Thanks in advance.

Package Strategy Question

I am developing a rather large ETL solution using SSIS.  So far I have been building everything inside a single package but it occurred to me that perhaps I ought to check and see if that is wise.  Are there performance gains or scale reasons to split an ETL into multiple packages and then have a master package that calls each one and keeps them in sync?  I assume building everything in one package is OK? Thanks in advance... - Charles

Text Search Strategy Question for the SSIS Gurus...

BACKGROUND: As I have mentioned in some of my other posts I am using SSIS 2005 to replace an existing MS Access 2003 / VBA based ETL engine which I developed some years back.   Part of my existing Access-based ETL performs a text search of the source records and I am now attempting to replicate that functionality in SSIS (replicate in terms of the end-result and not necessarily the methods used to get to that end result).  I have an idea of how I plan to go about this but since am relatively new to SSIS so would greatly appreciate the feedback of those more experienced... DETAILED DESCRIPTION: In the source (Sybase ASE15) database, there is an "object" table (not the actual table name but for illustrative purposes it will suffice).  Within the object table there is a "description" column which is a char(60) datatype.  The description column simply represents a description of the object as defined by the source system end-user. For my ETL solution I allow the ETL administrator to define one or many (1...n) key words or phrases which represent search criteria.  These search criteria are stored in a reference table in my target SQL Server 2005 database (the same database to which my ETL will transform results and store them).  My objective, is as follows:  For each of the 1...n search criteria defined, try and find th

sql server 2008 Table Partition

HI i want to implement partitioning in my sql server 2008 database table http://blogs.msdn.com/b/manisblog/archive/2009/01/18/easy-table-partitions-with-sql-server-2008.aspx   i have used this link. but when i try this link then i am able to create only primary file group no secondary file group..so help me also by fault i have created partition in wrong table .so i want to detete tha partition from that table but of i delete the PARTITION SCHEME and PARTITION FUNCTION THEN it throws error..so what shoul i do to remove partition from my current table

sql server 2008 Table Partition

HI http://blogs.msdn.com/b/manisblog/archive/2009/01/18/easy-table-partitions-with-sql-server-2008.aspx  i have used  above link to create partition,but my problem is i want to do partition on daily basic, one column in my table is date(by default has getdate()),on the base of this i want to do partition, eg.in this link i need to give time duration between which one can do partition, and also either daily or monthly or quarterly or yearly need to be specify ..if i give date between 01/08/2010 to 01/08/2012 and then select daily wise partition which i am in need then table create more than 700 partion but problem comes when i need to assign group to them how cud i select Primary file group more than 700 times to assign it to each partiotion.it becomes very lengthy process  Please help me

how to process just one partition along with other measure group and dimension in SSIS package Analy

HI All, i have to process just one partition1  of measure group A ,along with this i suppose to process all the Measure group and dimension with the help of SSIS Package Analysis Services Processing task. Partition1 having a query which fetch data only for previous day only. what i have done i select partition 1 in process data mode,all other measure group in full mode and dimension in process update mode.   i haven't taken measure group of partition1 and also not taken cube in the processing list ,when i run the package ,it runs suceesfully but data not get uplaoded into the Cube.   kindly suggest what other measures should i take to update the data . Amit

Unprocess cube old partition

hi all, i have a cube,and i wanna unprocess old cube partition(last 2 years)how can i do that ?

Which high availability strategy for multi tenant architecture

Hi, I run a multi-tenant application : each client runs the same code but each one hase its own database for personnal parameters and data, that makes about 40-50 databases and growing (i hope so) I want to set up a high availability strategy, for that, I have two VMs each one running SQL Server 2008 Entreprise. I had started to configure log shipping, but I was told that it was not a good idea when there are many dbs involved. How can I choose between HA strategies ? What tests do I have to do to see if i'm still elligible so log shipping (number of transactions per hour ?) If log shipping is not an option, what should I choose ? Failover cluster ? Thanks ! Don't hesitate to ask if you need more information.

Advice needed on backup strategy and implementation

This is my plan: Full database backup is performed at 5AM every day. Then log backups are taken every 30 minutes starting from 5:30AM till 11:30PM What I want to achieve is to have all backups for the same day to be contained in the same file on the disk. That is on Feb 18 2010 file c:\db_backup_02_18_10.BAK will be created at 5AM with full database backup. All backup logs that occur on Feb 18 will be appended to the same file. Next day c:\db_backup_02_19_10.BAK will be created at 5AM. Is this "correct" approach? The reason I like it is that everything needed to restore to any point in time for specific day is contained in one file and can be copied easily to a different computer. I tried to achieve this result via Maintenance Plan wizard by creating one task for full backup (scheduled once a day at 5AM) and another task for log backup (scheduled every 30 minutes starting from 5:30AM till 11:30PM) (both tasks are in the same maintenance plan). This, however, saves result from each job in a separate file called $DBNAME_backup_YYYY_MM_DD_HHMMSS_NNNNNNN.trn (for transaction log backup; will probably have extension .BAK for full backup) Can this even be done via Maintenance plan? I think I can achieve desired result via SSIS where I can generate file name programmatically and pass it as a parameter to "BACKUP DATABASE ..." or "BACKUP LOG ..." stat

Partition sql server 2008

I am designing database for fleet management system. I will be getting n number of records every 3 seconds. Obviously, there will be millions of record in my table where I am going to store current Information of vehicle in the current_location table. Here performance is an BIG issue. To solve this, I received the following suggestions: Create an septate table for each vehicle. Here a table will be created at a run time as as soon as I click on create new table.And all the data related to perticular table will be inserted and retrive from that perticular table Go for partition. Please answer the following questions about these solutions. What is difference between the two? Which is best and why? At what point will the number of rows in the tables cause performance issues? Are there any other solutions? Now ---if I go for range partition in sql server 2008 what should i do to, partition useing varchar(20). i am planning to do partition based on vehicle no.eg MH30 q 1234. Here In vehicle no. lets say mh30 q 1234--only 30 & q going to change....so my question is HOW SHOULD I GO. means how should write the partition function.

Strategy In creating indexes

Hi Team, Need a help strategy in creating indexes.. I have a Query which returns me more then 4 billion rows, which i am trying to insert into a table. There are views and table in the query. I am working on SQL Server 2000. So i thought if we create indexes on primary key columns on the table(which are used in views), on the columns used where condition of the main query. Will creating clustered index on the table which more than one primary key helpful? Is my strategy correct? any suggestions will be of great help! Regards, Eshwar.
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend