May 6, 2003 at 9:35 am
I have a large table: 62 million records, growing by 3.5 million records per month. Query performance has really gone down hill, and it is commonly used by end-users and a number of automated processes.
We recently created quarterly subset tables (~ 10 million rows per quarter) for a specific analysis, and the query performance was substantially better than hitting the big table. This result launched us into discussions on permanently partitioning this table into quarterly tables, and using a partitioned view to query against the entire data set.
So, we've started testing. Everything I read from Microsoft leads me to believe that Standard Edition should support partitioned views. However, even though I've set up the partition tables with the correct CHECK constraints, the partitioned view does not seem to be working as I expected. Specifically, a query that is written against the partitioned view and that uses the partitioning column in the WHERE clause of the query produces an execution plan that indicates SQL Server is searching every partitioned table for relevant records. My understanding from Microsoft's documentation is that the query optimizer should: 1) realize that the query hits a partitioned view; 2) know that the WHERE clause is using the partitioning column as a selection criterion; and 3) only hit the partition tables that are consistent with the WHERE clause.
Am I missing something? Though Microsoft's documentation says that Enterprise Edition is required for a distributed partitioned view (1 table across multiple servers), it looks like Standard Edition should support a local partitioned view.
Thanks in advance for any help that you can offer.
May 6, 2003 at 10:46 am
Well, to be honest with you I'm not sure. If you run the query and look at the execution plan what does it say vs. the estimated plan? Does it still run long? Another thing I would look at is your indexing scheme and other performance related problems that show up on large tables. I am running several databases with tables that have over 150 mil rows with no real performance issues.
Wes
May 6, 2003 at 11:50 am
Have you have chance to test your partitioned views in SQL Server with enterprise edition installed? Can you post your scripts with some sample data here?
May 15, 2003 at 8:53 am
Sorry for the delay responding. We fixed things. Here's what happened, if you're interested.
We started testing using our own tables. I wanted to see my tables as part of a partitioned view, and I wanted to see an execution plan that clearly optimized for SELECTs the way a partitioned view should. That wasn't happening. The SELECT against the partitioned view hit all tables in the view, rather that "optimizing" and hitting only the tables that corresponded with the WHERE clause statement (based on CHECK constraint values ...).
So, we backed up a step and used the sample code that Microsoft provided in their documentation. On both Standard Edition and Enterprise Edition, we created a simple partitioned view with 3 tables and a partitioning column that was a simple integer counter (identity). After we proved that this worked as advertised, we changed the partitioning column to a non-identity varchar column (because that's one of our requirements). After that worked, we created a compound primary key that included our varchar partitioning column and a simple integer identity column. When that succeeded, we declared victory, at least in the R&D phase. I'm loading "real" test data today to do some performance testing.
After reviewing the various successful R&D steps, we concluded that our original test failed because we were making changes to the partition tables (changing primary keys, for example) without "re-validating" the CHECK constraint. Apparently, that's very important. Assuming our "live fire" tests go well, we'll implement the partitioned view in production next week.
One final note... Eventually, everything we tried worked equally well in both Standard Edition and Enterprise Edition. We don't need either a multiple-server partitioned view (federated servers ?) or the ability to insert records into the partitioned view (we're inserting directly into partition tables), so we're sticking with the Standard Edition for cost reasons.
May 15, 2003 at 11:59 am
Thanks for the reply, I look forward to your production runs to see how it really handles load with a true production data. Keep us informed.
Wes
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply