Question on partitioning indexes

We have two tables that have full text indexes, currently both are using the
same catalog.
One table is much larger and the column being indexed contains more data.
Would there be any advantage of seperating the indexes into tow seperate
catalogs?
Kyle!
Kyle,
This is one of those questions, where the answer is that it depends... First
of all, see SQL Server 2000 BOL title "Full-Text Search Recommendations" -
"There are also full-text indexing and searching considerations when
determining whether to include multiple SQL tables in one full-text catalog
versus one SQL table per full-text catalog. There is a trade-off between
performance and maintenance when considering this design question with large
SQL tables and you may want to test both options for your environment. If
you choose to have multiple SQL tables in one full-text catalog, you incur
the overhead of longer-running full-text search queries as well because
incremental populations will force the full-text indexing of all other SQL
tables in that full-text catalog. If you choose to have a single SQL table
per full-text catalog and have multiple SQL tables full-text indexed, you
have the overhead of maintaining separate full-text catalogs with a total
limit of 256 full-text catalogs per server."
Another consideration is whether or not you are using CONTAINSTABLE or
FREETEXTTABLE with RANK as having multiple tables in one FT Catalog can
affect the Ranking values...
Hope that helps!
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Kyle Jedrusiak" <kjedrusiak@.princetoninformation.com> wrote in message
news:epPuhv3wFHA.3812@.TK2MSFTNGP09.phx.gbl...
> We have two tables that have full text indexes, currently both are using
> the same catalog.
> One table is much larger and the column being indexed contains more data.
> Would there be any advantage of seperating the indexes into tow seperate
> catalogs?
> Kyle!
>
|||This is good stuff. The article was good as well.
We can't seperate the catalog onto a different drive as we only have a RAID5
setup with 6 physical drives and one logical drive.
One table has over 100K records, the other over 94K records. It's not
millions of records, but we are trying to tweak search performace as much as
we can. I don't forsee ever adding 252 more catalogs anywhere in the
future. So seperating the FTI for each table into it's own catalog
shouldn't be an issue.
Thanks
Kyle
"John Kane" <jt-kane@.comcast.net> wrote in message
news:OVq6y79wFHA.1456@.TK2MSFTNGP11.phx.gbl...
> Kyle,
> This is one of those questions, where the answer is that it depends...
> First of all, see SQL Server 2000 BOL title "Full-Text Search
> Recommendations" - "There are also full-text indexing and searching
> considerations when determining whether to include multiple SQL tables in
> one full-text catalog versus one SQL table per full-text catalog. There is
> a trade-off between performance and maintenance when considering this
> design question with large SQL tables and you may want to test both
> options for your environment. If you choose to have multiple SQL tables in
> one full-text catalog, you incur the overhead of longer-running full-text
> search queries as well because incremental populations will force the
> full-text indexing of all other SQL tables in that full-text catalog. If
> you choose to have a single SQL table per full-text catalog and have
> multiple SQL tables full-text indexed, you have the overhead of
> maintaining separate full-text catalogs with a total limit of 256
> full-text catalogs per server."
> Another consideration is whether or not you are using CONTAINSTABLE or
> FREETEXTTABLE with RANK as having multiple tables in one FT Catalog can
> affect the Ranking values...
> Hope that helps!
> John
> --
> SQL Full Text Search Blog
> http://spaces.msn.com/members/jtkane/
>
> "Kyle Jedrusiak" <kjedrusiak@.princetoninformation.com> wrote in message
> news:epPuhv3wFHA.3812@.TK2MSFTNGP09.phx.gbl...
>
|||You're welcome, Kyle,
Actually, I wrote that years ago (before SQL 2000 shipped) while I was at
MSFT. You may want to review the collection of FTS related articles at
SQL Server 2000 Full-Text Search Resources and Links
http://spaces.msn.com/members/jtkane/Blog/cns!1pWDBCiDX1uvH5ATJmNCVLPQ!305.entry
for more information on performance and problems/workarounds.
Enjoy!
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Kyle Jedrusiak" <kjedrusiak@.princetoninformation.com> wrote in message
news:%23VlkPPDxFHA.3124@.TK2MSFTNGP12.phx.gbl...
> This is good stuff. The article was good as well.
> We can't seperate the catalog onto a different drive as we only have a
> RAID5 setup with 6 physical drives and one logical drive.
> One table has over 100K records, the other over 94K records. It's not
> millions of records, but we are trying to tweak search performace as much
> as we can. I don't forsee ever adding 252 more catalogs anywhere in the
> future. So seperating the FTI for each table into it's own catalog
> shouldn't be an issue.
> Thanks
> Kyle
> "John Kane" <jt-kane@.comcast.net> wrote in message
> news:OVq6y79wFHA.1456@.TK2MSFTNGP11.phx.gbl...
>

Question on merging partitions

Hi, all experts here,

Now I am confusing about the need to merge partitions and the significant benefits of merging them.

As partitioning cube obviously helps improving cube processing time and improve query performance. But then what is the reason to merge partitions back to one single partition of a cube?

I am looking forward to hearing from you for your kind advices and thanks a lot in advance.

With best regards,

Yours sincerely,

(this is not the answer for the existence of the merging partitions feature, just a scenario)

There is a best practice for AS2005 to not have more than 5 small partitions in a measure group (a small partition having less than 50 MB or 2,000,000 rows). If testing on the particular configuration (depending on the hardware, the queries that needs to be optimized, ...) shows that indeed it's better to consolidate these small partitions, then merging partitions helps.

Adrian Dumitrascu

|||

Hi, Adrian,

Thank you for your advices.

With best regards,

Yours sincerely,

Question on constraints and partitioned views

Can you use a combination of columns for the partitioning column in a
partition view? This is what I want to do but I cannot get it to work.
I get an error that UNION ALL view 'vw_My_Data' is not updatable
because a partitioning column was not found.
Here's my DDL
--Group1 July 2003
CREATE TABLE [dbo].[Group1_07_2003] (
[Sample_ID] [uniqueidentifier] NOT NULL ,
[Group_Constraint] [int] Check(Group_Constraint = 1) NOT NULL ,
[Month_Constraint] [int] Check (Month_Constraint = 7)NOT NULL ,
[Year_Constraint] [int] Check (Year_Constraint = 2003)NOT NULL ,
[Timestamp] [datetime] NOT NULL ,
[msec] [int] NOT NULL ,
[Device_ID] [bigint] NOT NULL ,
[Topic_ID] [bigint] NOT NULL ,
[Sample_Type_ID] [bigint] NOT NULL ,
[Calculated_Value] [float] NOT NULL ,
[Original_Value] [float] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Group1_07_2003] ADD
CONSTRAINT [PK_Group1_07_2003] PRIMARY KEY NONCLUSTERED
(
[Sample_ID],
[Group_Constraint],
[Month_Constraint],
[Year_Constraint]
) ON [PRIMARY]
GO
I would like to create other tables for Group_Constraint=2, and
Month_Constraint = 7, and Year_Constraint = 2003 and so on. But when
I add in the other group, I start getting the errors when I try to
insert into the view. Can I only use one column?Thank you for your recommendation but it did not fix the problem.
Apparently you can only have one column as your partitioning column.
However, I am ready to ditch the partitioned view design. We have
tried every other way we can to actually apply what we want to do and
either we cannot insert into the view or when we query the data it
goes to all of the tables instead of one table based on the
constraint. Even one of the examples in BOL will not work.
Here's the code from BOL:
CREATE TABLE May1998sales
(OrderID INT,
CustomerID INT NOT NULL,
OrderDate DATETIME NULL
CHECK (DATEPART(yy, OrderDate) = 1998),
OrderMonth INT
CHECK (OrderMonth = 5),
DeliveryDate DATETIME NULL
CHECK(DATEPART(mm, DeliveryDate) = 5)
CONSTRAINT OrderIDMonth PRIMARY KEY(OrderID, OrderMonth)
CREATE VIEW Year1998Sales
AS
SELECT * FROM Jan1998Sales
UNION ALL
SELECT * FROM Feb1998Sales
UNION ALL
SELECT * FROM Mar1998Sales
UNION ALL
SELECT * FROM Apr1998Sales
UNION ALL
SELECT * FROM May1998Sales
UNION ALL
SELECT * FROM Jun1998Sales
UNION ALL
SELECT * FROM Jul1998Sales
UNION ALL
SELECT * FROM Aug1998Sales
UNION ALL
SELECT * FROM Sep1998Sales
UNION ALL
SELECT * FROM Oct1998Sales
UNION ALL
SELECT * FROM Nov1998Sales
UNION ALL
SELECT * FROM Dec1998Sales
SELECT *
FROM Year1998Sales
WHERE OrderMonth IN (5,6) AND CustomerID = 64892
--
I created just two tables and modified the select statement
SELECT *
FROM Year1998Sales
WHERE OrderMonth =5
I didn't put any data in the tables, I just ran the above select
statement. If you look at the execution plan. It does not go directly
to May1998sales.
I have only gotten one example to actually work and that is using the
customer example
-- On Server1:
CREATE TABLE Customers_33
(CustomerID INTEGER PRIMARY KEY
CHECK (CustomerID BETWEEN 1 AND 32999),
... -- Additional column definitions)
-- On Server2:
CREATE TABLE Customers_66
(CustomerID INTEGER PRIMARY KEY
CHECK (CustomerID BETWEEN 33000 AND 65999),
... -- Additional column definitions)
-- On Server3:
CREATE TABLE Customers_99
(CustomerID INTEGER PRIMARY KEY
CHECK (CustomerID BETWEEN 66000 AND 99999),
... -- Additional column definitions)
However for how we want to partition the data it does not seem to
work. It has been a nightmare. Also don't try to make your
partitioning column on datetime. You can insert into the view but look
at your execution plan. If you use a datetime variable in your where
clause to specify a date, it will not work. I can elaborate more if
anyone is interested. If anyone else is looking at using this design,
BEWARE! It is not documented well in BOL. I think I have said enough.
If anyone has actually gotten this to work, feel free to comment.
"Quentin Ran" <ab@.who.com> wrote in message news:<#of9d4sVDHA.2328@.TK2MSFTNGP12.phx.gbl>...
> Loretta,
> try by putting the PK and constraint in your create table statement. Avoid
> alter table if the table participates in a partitioned view. I do not have
> the reason, but alter table tends to spoil the partitioned view / tables.
> hth
> Quentin
>|||We have similar problem. We have a large table which we
always fetch data by giving certain date.
So, we broke the table into per month bases and created a
partitioned view. The date column (datetime datatype)in
each member table has a check constraint.
When I quire to the view by giving the data condition SQL
server access all member table and it takes very long time
for fetching.
You mentioned that datetime column wouldn't work.
But it must be the once of the most typical case one want
to have a partition view scenario, isn't it?
Is there any workaround from Microsoft side?
I am looking forward to hearing from you!!!
>--Original Message--
>Thank you for your recommendation but it did not fix the
problem.
>Apparently you can only have one column as your
partitioning column.
>However, I am ready to ditch the partitioned view design.
We have
>tried every other way we can to actually apply what we
want to do and
>either we cannot insert into the view or when we query
the data it
>goes to all of the tables instead of one table based on
the
>constraint. Even one of the examples in BOL will not work.
>Here's the code from BOL:
>CREATE TABLE May1998sales
> (OrderID INT,
> CustomerID INT NOT NULL,
> OrderDate DATETIME NULL
> CHECK (DATEPART(yy, OrderDate) = 1998),
> OrderMonth INT
> CHECK (OrderMonth = 5),
> DeliveryDate DATETIME NULL
> CHECK(DATEPART(mm, DeliveryDate) = 5)
> CONSTRAINT OrderIDMonth PRIMARY KEY(OrderID,
OrderMonth)
>CREATE VIEW Year1998Sales
>AS
>SELECT * FROM Jan1998Sales
>UNION ALL
>SELECT * FROM Feb1998Sales
>UNION ALL
>SELECT * FROM Mar1998Sales
>UNION ALL
>SELECT * FROM Apr1998Sales
>UNION ALL
>SELECT * FROM May1998Sales
>UNION ALL
>SELECT * FROM Jun1998Sales
>UNION ALL
>SELECT * FROM Jul1998Sales
>UNION ALL
>SELECT * FROM Aug1998Sales
>UNION ALL
>SELECT * FROM Sep1998Sales
>UNION ALL
>SELECT * FROM Oct1998Sales
>UNION ALL
>SELECT * FROM Nov1998Sales
>UNION ALL
>SELECT * FROM Dec1998Sales
>SELECT *
>FROM Year1998Sales
>WHERE OrderMonth IN (5,6) AND CustomerID = 64892
>--
>I created just two tables and modified the select
statement
>SELECT *
>FROM Year1998Sales
>WHERE OrderMonth =5
>I didn't put any data in the tables, I just ran the above
select
>statement. If you look at the execution plan. It does not
go directly
>to May1998sales.
>I have only gotten one example to actually work and that
is using the
>customer example
>-- On Server1:
>CREATE TABLE Customers_33
> (CustomerID INTEGER PRIMARY KEY
> CHECK (CustomerID BETWEEN 1 AND 32999),
> ... -- Additional column definitions)
>-- On Server2:
>CREATE TABLE Customers_66
> (CustomerID INTEGER PRIMARY KEY
> CHECK (CustomerID BETWEEN 33000 AND
65999),
> ... -- Additional column definitions)
>-- On Server3:
>CREATE TABLE Customers_99
> (CustomerID INTEGER PRIMARY KEY
> CHECK (CustomerID BETWEEN 66000 AND
99999),
> ... -- Additional column definitions)
>However for how we want to partition the data it does not
seem to
>work. It has been a nightmare. Also don't try to make your
>partitioning column on datetime. You can insert into the
view but look
>at your execution plan. If you use a datetime variable in
your where
>clause to specify a date, it will not work. I can
elaborate more if
>anyone is interested. If anyone else is looking at using
this design,
>BEWARE! It is not documented well in BOL. I think I have
said enough.
>If anyone has actually gotten this to work, feel free to
comment.
>
>
>
>"Quentin Ran" <ab@.who.com> wrote in message
news:<#of9d4sVDHA.2328@.TK2MSFTNGP12.phx.gbl>...
>> Loretta,
>> try by putting the PK and constraint in your create
table statement. Avoid
>> alter table if the table participates in a partitioned
view. I do not have
>> the reason, but alter table tends to spoil the
partitioned view / tables.
>> hth
>> Quentin
>>
>.
>|||I agree, it does seem like partitioning by date makes the most sense
but we don't know if any work around. What actually got our team
looking at partitioned views was an article called "Add Scalability
with Data Partitioning" by Jon Rauschenberger. I only have a print
out. Sorry I don't have a link. Anyways, this article seems too good
to be true. But with the design we have in mind, it just might be to
complex. We are still considering dividing our data into smaller
tables but we do not plan on using a view for inserting and querying
the data. We are considering writing our own custom apps to do this
but we are still working on other areas and that has its own
drawbacks. Not sure what the work around is for now. Sorry, no help.
"didi" <carlsdottar@.hotmail.com> wrote in message news:<1a1e01c360c9$c23f09b0$3501280a@.phx.gbl>...
> We have similar problem. We have a large table which we
> always fetch data by giving certain date.
> So, we broke the table into per month bases and created a
> partitioned view. The date column (datetime datatype)in
> each member table has a check constraint.
> When I quire to the view by giving the data condition SQL
> server access all member table and it takes very long time
> for fetching.
> You mentioned that datetime column wouldn't work.
> But it must be the once of the most typical case one want
> to have a partition view scenario, isn't it?
> Is there any workaround from Microsoft side?
> I am looking forward to hearing from you!!!
>
> >--Original Message--