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

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

Partition elimination in 2008

Posted By:      Posted Date: December 04, 2010    Points: 0   Category :Sql Server

We are running 2008 CU5 (10.2.2746) and the DBA has started to partition some of our larger tables.  I was excited about potential performance improvements resulting from partition elimination.  In practice though, we are not getting the results we were expecting.  By way of example I have a table of weekly sales with approx. 20 million rows per week for the past two years.  I also have another table I'll call HistoryTable with approx. 1.2 billion rows per week.  Sales is not partitioned; the history table is partitioned by week (HistoryDate).  For better or worse, the dates in Sales do not align with the dates in HistoryTable.  If I query directly against the history table, I can see partition elimination occuring in the explain plan:

FROM HistoryTable
WHERE HistoryDate = '1/1/2010'

Now if I drive off of sales and join to HistoryTable I also get partition elimination:

FROM Sales s
LEFT JOIN HistoryTable ht
ON s.SalesDate = ht.HistoryDate
AND s.StoreId = ht.StoreId
AND s.ProductId = ht.ProductId
WHERE s.SalesDate = '1/7/2010'

However, as pointed out above, I can't use SalesDate to join to HistoryDate - they don't align.  I have another table to map the dates, giving me:

FROM Sales s
JOIN SalesDateToHistoryDate

View Complete Post

More Related Resource Links

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

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.

Disk Partition Alignment SQL 2005 on Win 2008


Needed some help for Win 2008.


Gives info about disk partition alignment. It states that Win 2008 'ATTEMPTS' to get the partition aligned for us.

If I run,

fsutil fsinfo ntfsinfo e:

on our Win 2008 server, it still gives the partition as 8KB. Do, we need to change it to 64KB?

Choosing Between SQL 2008 R2 on single-partition or multiple-partition SAN Storage



I'm trying to design a storage plan for a new SQL Server 2008 R2 Data Warehouse under Hyper-V, initially at least I'm having to use an existing SAN that wasn't built with SQL in mind and (for now) can't be reconfigured.  I'm going to be getting 2TB of storage that's carved out of one single giant array of disks on the SAN (not sure of the RAID level but again, we can't change that), the space would be carved-up on the SAN and presented to the host as separate logical drives (i.e. not VHDs). 

Unfortunately this means that I can't do the usual thing of isolating TempDB, Transaction Logs and Data Files on physically separate spindles.  My plan (roughly speaking) was going to be to separate as follows:

  • TempDB: 32GB
  • Staging Area: 96GB (text files and some databases)
  • Transaction Logs: 256GB
  • User DB Partition 1: 512GB
  • User DB Partition 2: 512GB
  • User DB Partition 3: 512GB

Thus, my question is - in this scenario is there any benefit in separating the files at all?  Given that the data could be sourced from the same spindles there's not a great deal I can do about it from a performance level. 

Also, on a side note - even if there aren't any performance benefits, would this be a suitable storage plan to use if I were to later migrate to a different SAN?&n

SQL Server 2008 - Alter Table Switch Statement Fails Where Partition Key Defined as a Persisted Comp


Here's a very annoying and poorly (or undocumented) issue in SQL Server 2008 Ent. Ed. post-SP1, CU8:

When creating a table with a persisted computed column as the clustered primary and partitioning key (see below), where the computation includes a call to CONVERT, watch out for the representation of this calculation on sys.computed_columns.  The query parser will modify the definition at table creation to insert a default style parameter of "(0)", if no other style is specified.  If the original definition did not include any style parameter and you later attempt to programmatically recreate the table using the stored definition contained on sys.computed_columns, the query optimizer will throw an error when you then attempt to switch partitions from the original table into the new programmatically created table, as follows:

Msg 4966, Level 16, State 1, Line 72

ALTER TABLE SWITCH statement failed. Computed column '%' defined as '%' in table %' is different from the same column in table %' defined as '%'.

This is so, eventhough the two definitions are completely functionally equivalent, considering the default value of the style parameter.  This can cause a lot of headaches, because correcting the problem requires dropping and rebuilding the computed column with a definition that include

Partition set up in transactional replication SQL Server 2008

Currently The partition strategy with switching works fine on a non replication enviornment.But I know there any few steps that needed to be considered before setting transaction replication.
I know we have to set 'allow_partition_switch' and replicate_partition_switch to true on the publication.
Inspite of setting them my replication fails while doing partition slide saying 'The <archived table> is not found'.
Any help is appreciated

Using Conditional Split data Transfer in SSIS 2008

This article uses the Integration Services Conditional Split Data Transformation element to filter and transfer data from a set of flat text files to SQL Server database table. The concept can be easily extended to apply to any other source or destination such as Microsoft Excel. This scenario is useful in creating denormalized database tables in a reporting and analysis situation.

Sql Server 2008 Tutorials

This Link Provides total Sql Server 2008 Tutorials

Seven reasons to care about SQL Server 2008 R2

Microsoft begins to fully realize its vision of SQL Server as an information platform and not "just" a database. Hence the main theme for this release -- at least according to Microsoft -- is self-service BI. The PowerPivot plug-ins for Excel 2010 and SharePoint 2010 are easily going to make the biggest splash of all the new features, not least because they're the most complete. But then, SQL Server 2008 R2 isn't strictly necessary for PowerPivot for Excel, which works with plenty of other data sources.

Building Re-Usable ASP.NET User Control and Page Libraries with VS 2008


I am having problems with a user control. I want my solution to have a separate project for holding my user controls.

The main project can get a reference to this user control project and can import this user control dll into my main project. 

Thanks in advance,

Dheeraj Mittal


Visual Web Developer 2008


I'm getting th error "unable to connect to the asp.net development server 2008"

It's been working fine for months and all the sudden.I'm fairly new at this so if you have a fix I will need details.



Visual Studio 2008 Memory problems


My memory in task manager reaches about 900,000K  and I don't know why it does this. Definately slows everything down, especially when I rebuild my tableAdapters, takes about 30 seconds sometimes to rebuild the project.

is it possible to convert whole visual studio 2008 web site project to visual studio 2010 web site p


is it possible to convert whole visual studio 2008 web site project to visual studio 2010 web site project


my current project references




i have a very annoying iis problem

iis server stops working until app pool is recycled


i am hoping that new visual studio (net framework 4.0) may solve this very annoying unsolveable problem


Error upgrading SQL Server 2008


i was normally using VS2005 and SQL server 2005...

i upgraded to SQL Server 2008... and then...

i could not build my project anymore... because of this error:

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. 
Exception Details: System.IO.FileNotFoundException: The specified module could not be found. (Exception from HRESULT: 0x8007007E)
Source Error: 

Business Intelligence Reports Project in Visual studio 2008 ?



I wonder how to add a project template called  Intelligence Reports, something like that, to Visual studio 2008. I have SQL 2008 installed. Do I need to install some updates to get Intelligence Reports template to Visual studio 2008 ?

Thank you..

Installing both VS Pro 2008 and VWD2008


I work at an educational institution and in our computing labs we have Visual Studio Professional 2008 installed already. One of our academics has requested me to install Visual Web Developer 2008 Express Edition as they think it's easier for their beginner students to learn, but I've noticed it wants to install it in the same folder as our Visual Studio Professional install.

Can both be installed without the limited VWD interfering with the Professional version?

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