|
Home > Archive > MS SQL Server OLAP > January 2006 > SQL server performance degrades
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
SQL server performance degrades
|
|
| dattatrayhkulkarni@gmail.com 2006-01-26, 4:58 pm |
| Hi,
We are trying to measuer the performance of our cube. It has more than
50 dimensions.
For every day we create a new partition. After creating partitions for
2 days, the cube processing time increases. e.g for first 2 days day it
took around 15 mins and for the third day it's taking around 40 mins.
The number of rows in the fact table for each of the partitions is
same.
But still on third day onwards the cube processing performance is
degrading. Even after using the optimize schema option, the problem
remains.
We analyses the Analysis services logs. The SQL query is taking more
and more time on subsequent runs.
Can anybody suggest some solution for this problem?
regards,
dattatray
| |
| Vladimir Chtepa 2006-01-26, 4:58 pm |
| Hi,
What the box do you have?
What version of AS?
How many rows in you fact table?
Do you make separate table for every cube partition or you make view?
Vladimir Chtepa
< dattatrayhkulkarni@g
mail.com> schrieb im Newsbeitrag
news:1138206163.859897.127390@g49g2000cwa.googlegroups.com...
> Hi,
>
> We are trying to measuer the performance of our cube. It has more than
> 50 dimensions.
> For every day we create a new partition. After creating partitions for
> 2 days, the cube processing time increases. e.g for first 2 days day it
> took around 15 mins and for the third day it's taking around 40 mins.
> The number of rows in the fact table for each of the partitions is
> same.
>
> But still on third day onwards the cube processing performance is
> degrading. Even after using the optimize schema option, the problem
> remains.
>
> We analyses the Analysis services logs. The SQL query is taking more
> and more time on subsequent runs.
>
> Can anybody suggest some solution for this problem?
>
> regards,
> dattatray
>
| |
|
| also, do you use incremental update?
or full partition process?
how many new members are added in the dimensions each day?
are you in MOLAP or ROLAP mode?
have you identify if its the execution of the SQL generated by AS the
problem?
or the aggregation process slow down?
"Vladimir Chtepa" <vc.nospam@diacom-systemhaus.nospam.de> wrote in message
news:uxU8ONeIGHA.3944@tk2msftngp13.phx.gbl...
> Hi,
>
> What the box do you have?
> What version of AS?
> How many rows in you fact table?
> Do you make separate table for every cube partition or you make view?
>
> Vladimir Chtepa
>
> < dattatrayhkulkarni@g
mail.com> schrieb im Newsbeitrag
> news:1138206163.859897.127390@g49g2000cwa.googlegroups.com...
>
>
| |
| dattatrayhkulkarni@gmail.com 2006-01-26, 4:58 pm |
| Hi,
The version of Analysis services is 8.00
We are using incremental update.
We are using MOLAP mode. We create a seperate a fact table for each
partition and there are around 3 Million rows in each fact table.
The analysis services logs show that the actual relational SQL query is
taking longer and longer time. We are using HP's hingh end machine
with xeon dual processor 8 GB RAM and 200 GB RAID hard disk array.
regards,
dattatray.
J=E9j=E9 wrote:[color=darkred
]
> also, do you use incremental update?
> or full partition process?
> how many new members are added in the dimensions each day?
> are you in MOLAP or ROLAP mode?
>
> have you identify if its the execution of the SQL generated by AS the
> problem?
> or the aggregation process slow down?
>
> "Vladimir Chtepa" <vc.nospam@diacom-systemhaus.nospam.de> wrote in message
> news:uxU8ONeIGHA.3944@tk2msftngp13.phx.gbl...
| |
| Vladimir Chtepa 2006-01-26, 4:58 pm |
| Hi,
It is pretty strange that SQL query for processing take more and more time.
If you use the schema one partition - one fact table, without any partition
filter, you should have almost constant processing time per partition.
Could you give a bit more information?
What for a query will be used? I would recommend you using of SQL profiler,
if you get troubles with SQL queries.
Vladimir Chtepa
< dattatrayhkulkarni@g
mail.com> schrieb im Newsbeitrag
news:1138281321.767966.257100@z14g2000cwz.googlegroups.com...
Hi,
The version of Analysis services is 8.00
We are using incremental update.
We are using MOLAP mode. We create a seperate a fact table for each
partition and there are around 3 Million rows in each fact table.
The analysis services logs show that the actual relational SQL query is
taking longer and longer time. We are using HP's hingh end machine
with xeon dual processor 8 GB RAM and 200 GB RAID hard disk array.
regards,
dattatray.
Jéjé wrote:[color=darkred
]
> also, do you use incremental update?
> or full partition process?
> how many new members are added in the dimensions each day?
> are you in MOLAP or ROLAP mode?
>
> have you identify if its the execution of the SQL generated by AS the
> problem?
> or the aggregation process slow down?
>
> "Vladimir Chtepa" <vc.nospam@diacom-systemhaus.nospam.de> wrote in message
> news:uxU8ONeIGHA.3944@tk2msftngp13.phx.gbl...
| |
| dattatrayhkulkarni@gmail.com 2006-01-26, 4:58 pm |
| Hi,
The queries I am referring are captured from the Analysis services
logs. So while doing the cube processing Analysis services has used
these queries.
I analysed the execution plan for queries on both fact tables.
For the first fact table it's using the "Hash Join" and for the second
fact table it uses the "nested loop" join. The nested loop join query
is executing much slower.
There are no indices on any of the fact and dimension tables.
Is there any way by which we can tell the analysis services that while
doing the cube processing use the "Hash Join" method?
regards,
dattatray.
Vladimir Chtepa wrote:
> Hi,
>
>
>
> It is pretty strange that SQL query for processing take more and more tim=
e=2E
>
> If you use the schema one partition - one fact table, without any partiti=
on
> filter, you should have almost constant processing time per partition.
>
>
>
> Could you give a bit more information?
>
> What for a query will be used? I would recommend you using of SQL profile=
r,[color=darkred]
> if you get troubles with SQL queries.
>
>
>
> Vladimir Chtepa
>
>
>
> < dattatrayhkulkarni@g
mail.com> schrieb im Newsbeitrag
> news:1138281321.767966.257100@z14g2000cwz.googlegroups.com...
> Hi,
>
> The version of Analysis services is 8.00
> We are using incremental update.
> We are using MOLAP mode. We create a seperate a fact table for each
> partition and there are around 3 Million rows in each fact table.
>
> The analysis services logs show that the actual relational SQL query is
> taking longer and longer time. We are using HP's hingh end machine
> with xeon dual processor 8 GB RAM and 200 GB RAID hard disk array.
>
> regards,
> dattatray.
>
>
>
> J=E9j=E9 wrote:
age[color=darkred]
an[color=darkred]
or[color=darkred]
it[color=darkred]
| |
| Vladimir Chtepa 2006-01-26, 4:58 pm |
| Try to create indexes on key fields.
< dattatrayhkulkarni@g
mail.com> schrieb im Newsbeitrag
news:1138290202.774055.117310@f14g2000cwb.googlegroups.com...
Hi,
The queries I am referring are captured from the Analysis services
logs. So while doing the cube processing Analysis services has used
these queries.
I analysed the execution plan for queries on both fact tables.
For the first fact table it's using the "Hash Join" and for the second
fact table it uses the "nested loop" join. The nested loop join query
is executing much slower.
There are no indices on any of the fact and dimension tables.
Is there any way by which we can tell the analysis services that while
doing the cube processing use the "Hash Join" method?
regards,
dattatray.
Vladimir Chtepa wrote:[color=darkred
]
> Hi,
>
>
>
> It is pretty strange that SQL query for processing take more and more
> time.
>
> If you use the schema one partition - one fact table, without any
> partition
> filter, you should have almost constant processing time per partition.
>
>
>
> Could you give a bit more information?
>
> What for a query will be used? I would recommend you using of SQL
> profiler,
> if you get troubles with SQL queries.
>
>
>
> Vladimir Chtepa
>
>
>
> < dattatrayhkulkarni@g
mail.com> schrieb im Newsbeitrag
> news:1138281321.767966.257100@z14g2000cwz.googlegroups.com...
> Hi,
>
> The version of Analysis services is 8.00
> We are using incremental update.
> We are using MOLAP mode. We create a seperate a fact table for each
> partition and there are around 3 Million rows in each fact table.
>
> The analysis services logs show that the actual relational SQL query is
> taking longer and longer time. We are using HP's hingh end machine
> with xeon dual processor 8 GB RAM and 200 GB RAID hard disk array.
>
> regards,
> dattatray.
>
>
>
> Jéjé wrote:
| |
|
| also,
verify that your statistics are updated in the database.
bad statistics information result in bad query plan.
"Vladimir Chtepa" <vc.nospam@diacom-systemhaus.nospam.de> wrote in message
news:OsJO1isIGHA.216@TK2MSFTNGP15.phx.gbl...
> Try to create indexes on key fields.
>
> < dattatrayhkulkarni@g
mail.com> schrieb im Newsbeitrag
> news:1138290202.774055.117310@f14g2000cwb.googlegroups.com...
> Hi,
>
> The queries I am referring are captured from the Analysis services
> logs. So while doing the cube processing Analysis services has used
> these queries.
> I analysed the execution plan for queries on both fact tables.
> For the first fact table it's using the "Hash Join" and for the second
> fact table it uses the "nested loop" join. The nested loop join query
> is executing much slower.
> There are no indices on any of the fact and dimension tables.
>
> Is there any way by which we can tell the analysis services that while
> doing the cube processing use the "Hash Join" method?
>
> regards,
> dattatray.
>
>
> Vladimir Chtepa wrote:
>
>
|
|
|
|
|