can I combine several partitions into one in a cube?

Hi,
I got 4 partitions corresponding to four fact tables.
Does it affect cube query performance? most of time, i need to access all
partitions.
If so, how can I combine them into one partition?
Thanks,
GuangmingIf you are using MOLAP storage (which should be mostly the case) then the
underlying relational fact tables are never touched during a query.
Partitioning is very important for smooth even response times. See the AS
Performance Guide here:
http://www.microsoft.com/technet/pr...n/ansvcspg.mspx
when it discusses partitioning. It is also discussed extensively in the SQL
Server 2000 Resource Kit which has an entire chapter on partitioning.
--
Dave Wickert [MSFT]
dwickert@.online.microsoft.com
Program Manager
BI Systems Team
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Word 2003 memory Leakage" <Word2003memoryLeakage@.discussions.microsoft.com>
wrote in message news:F71962A3-6EE7-45E8-A80B-CCB22DBA8F51@.microsoft.com...
> Hi,
> I got 4 partitions corresponding to four fact tables.
> Does it affect cube query performance? most of time, i need to access all
> partitions.
> If so, how can I combine them into one partition?
> Thanks,
>
> Guangming|||In management studio, go to cube - partition, right click you'll get 'merge
partition ...'.
it seems that if there are multiple partitions in one measure group, you can
merge them. If partitions are for different measure groups, you can not.
Most time, merging is not necessary, I guess.
Guangming
"Dave Wickert [MSFT]" wrote:

> If you are using MOLAP storage (which should be mostly the case) then the
> underlying relational fact tables are never touched during a query.
> Partitioning is very important for smooth even response times. See the AS
> Performance Guide here:
> [url]http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ansvcspg.mspx[/ur
l]
> when it discusses partitioning. It is also discussed extensively in the SQ
L
> Server 2000 Resource Kit which has an entire chapter on partitioning.
> --
> Dave Wickert [MSFT]
> dwickert@.online.microsoft.com
> Program Manager
> BI Systems Team
> SQL BI Product Unit (Analysis Services)
> --
> This posting is provided "AS IS" with no warranties, and confers no rights
.
>
> "Word 2003 memory Leakage" <Word2003memoryLeakage@.discussions.microsoft.co
m>
> wrote in message news:F71962A3-6EE7-45E8-A80B-CCB22DBA8F51@.microsoft.com..
.
>
>|||
> In management studio, go to cube - partition, right click you'll get 'merg
e
> partition ...'.
> it seems that if there are multiple partitions in one measure group, you c
an
> merge them. If partitions are for different measure groups, you can not.
Usually you have different measure groups, because the tables have
different columns. So by definition you have a different structure in
the partition therefore it does not make sense to merge them
If you do have multiple measure groups of identically structured fact
tables then they probably should be setup as multiple partitions under
the on measure group, rather then multiple measure groups each with a
single partition.

> Most time, merging is not necessary, I guess.
In AS2k, the server would read and resolve the data from each partition
using a separate thread so having multiple partitions can give you a
performance boost, especially on a multi processor machine with a fast
disk sub system.
Regards
Darren Gosbell [MCSD]
<dgosbell_at_yahoo_dot_com>
Blog: http://www.geekswithblogs.net/darrengosbellsql

can I combine several partitions into one in a cube?

Hi,
I got 4 partitions corresponding to four fact tables.
Does it affect cube query performance? most of time, i need to access all
partitions.
If so, how can I combine them into one partition?
Thanks,
Guangming
If you are using MOLAP storage (which should be mostly the case) then the
underlying relational fact tables are never touched during a query.
Partitioning is very important for smooth even response times. See the AS
Performance Guide here:
http://www.microsoft.com/technet/pro.../ansvcspg.mspx
when it discusses partitioning. It is also discussed extensively in the SQL
Server 2000 Resource Kit which has an entire chapter on partitioning.
Dave Wickert [MSFT]
dwickert@.online.microsoft.com
Program Manager
BI Systems Team
SQL BI Product Unit (Analysis Services)
This posting is provided "AS IS" with no warranties, and confers no rights.
"Word 2003 memory Leakage" <Word2003memoryLeakage@.discussions.microsoft.com >
wrote in message news:F71962A3-6EE7-45E8-A80B-CCB22DBA8F51@.microsoft.com...
> Hi,
> I got 4 partitions corresponding to four fact tables.
> Does it affect cube query performance? most of time, i need to access all
> partitions.
> If so, how can I combine them into one partition?
> Thanks,
>
> Guangming
|||In management studio, go to cube - partition, right click you'll get 'merge
partition ...'.
it seems that if there are multiple partitions in one measure group, you can
merge them. If partitions are for different measure groups, you can not.
Most time, merging is not necessary, I guess.
Guangming
"Dave Wickert [MSFT]" wrote:

> If you are using MOLAP storage (which should be mostly the case) then the
> underlying relational fact tables are never touched during a query.
> Partitioning is very important for smooth even response times. See the AS
> Performance Guide here:
> http://www.microsoft.com/technet/pro.../ansvcspg.mspx
> when it discusses partitioning. It is also discussed extensively in the SQL
> Server 2000 Resource Kit which has an entire chapter on partitioning.
> --
> Dave Wickert [MSFT]
> dwickert@.online.microsoft.com
> Program Manager
> BI Systems Team
> SQL BI Product Unit (Analysis Services)
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Word 2003 memory Leakage" <Word2003memoryLeakage@.discussions.microsoft.com >
> wrote in message news:F71962A3-6EE7-45E8-A80B-CCB22DBA8F51@.microsoft.com...
>
>
|||
> In management studio, go to cube - partition, right click you'll get 'merge
> partition ...'.
> it seems that if there are multiple partitions in one measure group, you can
> merge them. If partitions are for different measure groups, you can not.
Usually you have different measure groups, because the tables have
different columns. So by definition you have a different structure in
the partition therefore it does not make sense to merge them
If you do have multiple measure groups of identically structured fact
tables then they probably should be setup as multiple partitions under
the on measure group, rather then multiple measure groups each with a
single partition.

> Most time, merging is not necessary, I guess.
In AS2k, the server would read and resolve the data from each partition
using a separate thread so having multiple partitions can give you a
performance boost, especially on a multi processor machine with a fast
disk sub system.
Regards
Darren Gosbell [MCSD]
<dgosbell_at_yahoo_dot_com>
Blog: http://www.geekswithblogs.net/darrengosbell

Can AUTO UPDATE STATISTICS hold an X-lock?

We are experiencing a non-deterministic (ND) problem. It is happening
regularly, but not consistently. We run a suite of tests for performance
evaluation. Some of the tests insert data into empty tables, and later these
tables are read. Each time we are able to reproduce the hang, it is because
one of the connections is shown as holding an X lock as described below on
one of the tables. The process holding the lock will always be shown as
sleeping by the SQL Server Activity Monitor. At the point that the hang
occurs, we will only be using a single connection to the database. We use a
connection pool, so there will be some connections shown as coming from the
JDBC driver (us) and sleeping. This is because of our connection pool.
However, there should be no outstanding active work for any of those
connections because we always end the transaction, either by commit or
rollback, before getting a new connection or at the end of an individual test
case.
My guess is that the X lock is being acquired by the autostats feature when
SQL Server is updating the statistics on a table during a SQL statement
because 1 of the thresholds (inserts, updates) has been exceeded and that the
lock is not being released as it should be. I am confident that we are not
explicitly updating the statistics on any table through our application until
well after this X lock becomes visible in the SQL Server Activity Monitor.
We are running of SQL Server 2005 SP1, and we experience the problem with
both the 1.0 and 1.1 CTP versions of the Microsoft JDBC driver. The database
has the default settings of AUTO_UPDATE_STATISTICS ON, and
AUTO_CREATE_STATISTICS ON, but AUTO_UPDATE_STATISTICS_ASYNC OFF.
Blocking process is holding an X lock (Type=Object, Subtype=UPDSTATS, Object
ID = 1157579163 â' cc_typekeydatadist, Description = NULL, Owner Type = TRANSACTION, Object = (internal))
Successfully processed 0 ops in 468.0069999694824 secs (running avg 0
ops/secs; last interval avg 0 ops/secs) Detailed Status:
Number of threads: 1, total number of tables: 634, number of tables
processed: 421, total number of commands: 846, number of commands processed:
492, number of command errors: 0
Index name synch started at: Mon Jul 31 17:00:58 PDT 2006, ended at: Mon Jul
31 17:00:58 PDT 2006, duration: 0 seconds
Thread #0: Active: true, number of tables processed: 421, number of commands
processed: 492, number of command errors: 0,
start time: Mon Jul 31 17:00:58 PDT 2006,
start command time: Mon Jul 31 17:01:09 PDT 2006, current command: UPDATE
STATISTICS cc_typekeydatadist WITH FULLSCAN
Successfully processed 0 ops in 478.021999835968 secs (running avg 0
ops/secs; last interval avg 0 ops/secs) Detailed Status:
Number of threads: 1, total number of tables: 634, number of tables
processed: 421, total number of commands: 846, number of commands processed:
492, number of command errors: 0
Index name synch started at: Mon Jul 31 17:00:58 PDT 2006, ended at: Mon Jul
31 17:00:58 PDT 2006, duration: 0 seconds
Thread #0: Active: true, number of tables processed: 421, number of commands
processed: 492, number of command errors: 0,
start time: Mon Jul 31 17:00:58 PDT 2006,
start command time: Mon Jul 31 17:01:09 PDT 2006, current command: UPDATE
STATISTICS cc_typekeydatadist WITH FULLSCAN
We are going to try running with all auto-stats options off, and with the
aync option on, to see if that works around the problem.
We do not issue a commit after a read-only operation, because we do not
expect to be holding any locks, but that we do return the connection to the
connection pool. Does the UPDATE STATISTICS operation occur in the same
transaction, and thus could be holding a lock unless we are running in
autocommit mode or do an explicit commit? Or does it occur on a separate,
server-initiated thread?
I've posted the same question on the Connect Feedback area."Wes Clark" <WesClark@.discussions.microsoft.com> wrote in message
news:85E8BB89-22E7-427A-B263-4155783F125A@.microsoft.com...
> We are experiencing a non-deterministic (ND) problem. It is happening
> regularly, but not consistently. We run a suite of tests for performance
. . .
> We do not issue a commit after a read-only operation, because we do not
> expect to be holding any locks, but that we do return the connection to
> the
> connection pool. Does the UPDATE STATISTICS operation occur in the same
> transaction, and thus could be holding a lock unless we are running in
> autocommit mode or do an explicit commit? Or does it occur on a separate,
> server-initiated thread?
>
In a simple test I did, I did not see any locks enlisted in a transaction
from automatically created statistics, but I suppose it's possible.
In any case, leaving transactions open when you return connections to the
pool is a dangerous and unusual thing to to. As you have discovered, you
have to be 100% sure you have not created any locks, and if one slips in,
you've got a big problem. Why not just commit? If you haven't written any
log records it doesn't really cost anything.
David|||Your suggestion sounds reasonable, and we shall consider it.
Do you know the answer to my questions: Does the UPDATE STATISTICS
operation occur in the same transaction, and thus could be holding a lock
unless we are running in autocommit mode or do an explicit commit? Or does it
occur on a separate, server-initiated thread?
"David Browne" wrote:
> "Wes Clark" <WesClark@.discussions.microsoft.com> wrote in message
> news:85E8BB89-22E7-427A-B263-4155783F125A@.microsoft.com...
> > We are experiencing a non-deterministic (ND) problem. It is happening
> > regularly, but not consistently. We run a suite of tests for performance
> .. . .
> > We do not issue a commit after a read-only operation, because we do not
> > expect to be holding any locks, but that we do return the connection to
> > the
> > connection pool. Does the UPDATE STATISTICS operation occur in the same
> > transaction, and thus could be holding a lock unless we are running in
> > autocommit mode or do an explicit commit? Or does it occur on a separate,
> > server-initiated thread?
> >
>
> In a simple test I did, I did not see any locks enlisted in a transaction
> from automatically created statistics, but I suppose it's possible.
> In any case, leaving transactions open when you return connections to the
> pool is a dangerous and unusual thing to to. As you have discovered, you
> have to be 100% sure you have not created any locks, and if one slips in,
> you've got a big problem. Why not just commit? If you haven't written any
> log records it doesn't really cost anything.
> David
>
>

Can anyone recommend the best resource for performance tuning a SQL cluster?

I have a couple clusters (each with eight processors total, and 16 GB RAM
total), but am not getting the performance desired. I "inherited" these, I
didn't build them. Can anyone recommend any resources on tuning the cluster
and tuning the SQL service for the best performance?
The Cluster itself has no bearing on performance. You would tune the DB's
just like any other. The only exception is possibly the amount of ram you
use if you have a multi-instance cluster. See here for a start:
http://www.microsoft.com/sql/techinf...perftuning.asp
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.co...ance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.co...mance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=q224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/de...rfmon_24u1.asp
Disk Monitoring
http://www.sql-server-performance.co...showcontig.asp Understanding
DBCC SHOWCONTIG
Andrew J. Kelly SQL MVP
"Adam Freden" <Adam.Freden@.cpsa-rbha.org> wrote in message
news:uUH3lnnmEHA.3356@.TK2MSFTNGP14.phx.gbl...
> I have a couple clusters (each with eight processors total, and 16 GB RAM
> total), but am not getting the performance desired. I "inherited" these,
I
> didn't build them. Can anyone recommend any resources on tuning the
cluster
> and tuning the SQL service for the best performance?
>
|||Just my two cents. Clusters are almost always built on advanced storage
technology (Except for SCSI-based clusters, which are evil). As such, the
performance of the storage device comes into play. If you find the system
is suffering from I/O limitations, you may need to dig into the SAN
performance and tuning to solve your problems. I would start with the links
Andrew sent and only go after SAN tuning once you have found and fixed all
your other performance issues.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23jH67KomEHA.3100@.TK2MSFTNGP10.phx.gbl...
> The Cluster itself has no bearing on performance. You would tune the DB's
> just like any other. The only exception is possibly the amount of ram you
> use if you have a multi-instance cluster. See here for a start:
>
> http://www.microsoft.com/sql/techinf...perftuning.asp
> Performance WP's
> http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
> http://www.sql-server-performance.co...ance_audit.asp
> Hardware Performance CheckList
> http://www.sql-server-performance.co...mance_tips.asp
> SQL 2000 Performance tuning tips
> http://www.support.microsoft.com/?id=q224587 Troubleshooting App
> Performance
>
http://msdn.microsoft.com/library/de...rfmon_24u1.asp
> Disk Monitoring
> http://www.sql-server-performance.co...showcontig.asp
Understanding[vbcol=seagreen]
> DBCC SHOWCONTIG
>
> --
> Andrew J. Kelly SQL MVP
>
> "Adam Freden" <Adam.Freden@.cpsa-rbha.org> wrote in message
> news:uUH3lnnmEHA.3356@.TK2MSFTNGP14.phx.gbl...
RAM[vbcol=seagreen]
these,
> I
> cluster
>

can a view of a table imrpove table input performance?

I have a table (sql Server2k) which users connect to through ODBC - read
only. I have to input/write a lot of data to this table everyday. I
observed that it takes a lot longer to populate this table than one which is
not public. I am guessing that people have this table open through ODBC. I
f
I had the users connect to a view of this table (Select * From tbl1) would
this resolve the performance issue?
I realize that people would have to refresh the view each day to get the
most recent data, but I can send out a memo advising them of this.
Any suggestions appreciated. Thanks,
RichUnless it is an indexed view, a view doesn't actually store data. So when
you say SELECT * FROM view, you're actually running SELECT * FROM (SELECT
... view definition) which, long story short, means you're still accessing
the base tables...
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:D734E5C5-3EB6-4767-BDD0-EA4C12640EAA@.microsoft.com...
>I have a table (sql Server2k) which users connect to through ODBC - read
> only. I have to input/write a lot of data to this table everyday. I
> observed that it takes a lot longer to populate this table than one which
> is
> not public. I am guessing that people have this table open through ODBC.
> If
> I had the users connect to a view of this table (Select * From tbl1) would
> this resolve the performance issue?
> I realize that people would have to refresh the view each day to get the
> most recent data, but I can send out a memo advising them of this.
> Any suggestions appreciated. Thanks,
> Rich|||I guess I was thinking about a snapshot view. Is there such a thing?
"Aaron Bertrand [SQL Server MVP]" wrote:

> Unless it is an indexed view, a view doesn't actually store data. So when
> you say SELECT * FROM view, you're actually running SELECT * FROM (SELECT
> ... view definition) which, long story short, means you're still accessin
g
> the base tables...
>
> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:D734E5C5-3EB6-4767-BDD0-EA4C12640EAA@.microsoft.com...
>
>|||So to create an indexed view is it:
Create Index view1Ind
On view1(columnID)
While I am at it, is it possible to have on the most elemental index on a
table and have the rest of the indexes on the view? where the view would
contain all of the data in the table?
"Tibor Karaszi" wrote:

> That would be creating an index on the view, an indexed view.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:8500A93B-2C02-452B-A8F2-E45BA68B6F82@.microsoft.com...
>|||On Fri, 26 Aug 2005 11:59:06 -0700, Rich wrote:

>So to create an indexed view is it:
>Create Index view1Ind
>On view1(columnID)
Hi Rich,
To create an indexed view, you must use a unique clustered index:
CREATE UNIQUE CLUSTERED INDEX view1Ind
ON view1(columnID)
(assuming that columnID is unique in the view, of course).
Once you have this index in place, you can define additional views on
the index, but these can't be clustered.

>While I am at it, is it possible to have on the most elemental index on a
>table and have the rest of the indexes on the view? where the view would
>contain all of the data in the table?
I guess you can, but what's the point? If the view is an exact copy of
the table, you'll gain some performance by dropping the view and
indexing the table instead.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Basically, I want to have my cake and eat it. I have about 8-9 indexes on
this one table. But data input (only I input the data programatically -
batch operation) is becoming slow on this one table because people are
opening the table through ODBC connection. I wasnt users to be able to quer
y
the table with as little lag as possible, but I also what to be able to writ
e
data to the table without the lag.
By the way, when I create a new index using the GUI tool in table desing, it
has a selection option of "Do not automatically recompute statistics". I
have been checking this option on for each index I creat. What would yield
more performance? to have this option checked on or off?
Thanks,
Rich
"Hugo Kornelis" wrote:

> On Fri, 26 Aug 2005 11:59:06 -0700, Rich wrote:
>
> Hi Rich,
> To create an indexed view, you must use a unique clustered index:
> CREATE UNIQUE CLUSTERED INDEX view1Ind
> ON view1(columnID)
> (assuming that columnID is unique in the view, of course).
> Once you have this index in place, you can define additional views on
> the index, but these can't be clustered.
>
> I guess you can, but what's the point? If the view is an exact copy of
> the table, you'll gain some performance by dropping the view and
> indexing the table instead.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>|||> Basically, I want to have my cake and eat it.
That tend to be difficult in real life. Indexed views is not a way to make m
odifications have less
lag. The opposite. Then you modify the tables, the index on the view also ne
ed to be maintained,
which can be more costly compared to a similar index on the base table (depe
nding on the
circumstances).
How real time does the reporting tables need to be. Why not create snapshots
which you update
regularly?
In general, you do *not* want to turn off auto-update statistics. Statistics
are not updated (in
general) when you modify. They are updated when you SELECT, if they are out-
of-date. This is so that
the optimizer has good information to go on so it can pick a good plan. See
http://msdn.microsoft.com/library/e...l/statquery.asp for more
information.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:54F21B87-C722-45AD-A4E2-BB7DDD3A140F@.microsoft.com...
> Basically, I want to have my cake and eat it. I have about 8-9 indexes on
> this one table. But data input (only I input the data programatically -
> batch operation) is becoming slow on this one table because people are
> opening the table through ODBC connection. I wasnt users to be able to qu
ery
> the table with as little lag as possible, but I also what to be able to wr
ite
> data to the table without the lag.
> By the way, when I create a new index using the GUI tool in table desing,
it
> has a selection option of "Do not automatically recompute statistics".
I
> have been checking this option on for each index I creat. What would yiel
d
> more performance? to have this option checked on or off?
> Thanks,
> Rich
> "Hugo Kornelis" wrote:
>|||you also can:
select * into new_table from old_table;
load data into new_table
drop table old_table;
sp_rename new_table|||This is an interesting idea. Thanks. I could invoke DTS in code (large
table) Actually, well, thinking about it more, I am inserting a few thousan
d
records a day, millions of records. Even with DTS that might be too slow.
But it is an idea. When the table is large enough I may start using OLAP on
it.
"AK" wrote:

> you also can:
> select * into new_table from old_table;
> load data into new_table
> drop table old_table;
> sp_rename new_table
>