ParallelPeriod, Subcubes, and SP2

We're defining some KPIs that include trend expressions which use the ParallelPeriod function to calculate the change in a KPI value since a past time period. For example, we calculate the change in the KPI value

When we use an MDX SELECT with a WHERE clause that specifies a time slice, the expression evaluates correctly.

However, when we use CREATE SUBCUBE to do the filter, or use BI Studio (which creates a subcube), the result is incorrect. In particular, the there's an IsEmpty check for the parallel period of the KPI value, so we're not doing bogus math when there's no data for the previous period. With the WHERE clause, that works fine, but with the subcube, the ParallelPeriod always evaluates to empty.

As a point of comparison, I tested with the Adventure Works cube, usign the "Internet Revenue" KPI, which includes a similar type of KPI trend (revenue for Current Time Member - Revenue for previous Fiscal Year). I see the same behavior there. This:
select
{KPIValue("Internet Revenue"), KPIGoal("Internet Revenue"), KPIStatus("Internet Revenue"), KPITrend("Internet Revenue")}
on columns
from
[Adventure Works]
where {[Date].[Fiscal Year].&[2004]}

returns 1 for the trend, but this:

CREATE SUBCUBE [Adventure Works] AS ( SELECT ( { [Date].[Fiscal Year].&[2004] } ) ON COLUMNS FROM [Adventure Works])

select
{KPIValue("Internet Revenue"), KPIGoal("Internet Revenue"), KPIStatus("Internet Revenue"), KPITrend("Internet Revenue")}
on columns
from
[Adventure Works]

returns 0.

So after lots of reading, I THOUGHT that SP2 fixed this problem. However, we installed the December CTP of SP2, and it doesn't seem to.Since subselects and CREATE SUBCUBE do not set current coordinate on non-aggregatable attributes, functions which look at them (such as ParallelPeriod, PrevMember, Lag, Parent, LastChild etc) won't apply. There were no change in SP2 in this respect. You can still use WHERE clause to set the current coordinate though.|||Ouch. I'm not sure how I came to believe this was being fixed in SP2.

Does Microsoft consider this a bug? Design limitation? Any plans to address it at some point?

Since we have no control over how query tools generate their MDX, it seems difficult to design a cube that uses these functions and have confidence that it will work correctly for our customers.|||

This is not a bug, but rather a design decision. Perhaps it can be reconsidered in the future versions. I don't know which tool you use, but at least Excel 2007 wihch makes extensive use of subselects, does use WHERE clause when the slice is a single member (and ParallelPeriod won't work on multi-select anyways).

HTH,

Mosha.

|||I was quite concerned about Excel 2007, as I saw lots of mentions that Excel uses subselects and subcubes out on the web. However, I did some quick testing, and as you say, it does seem to use the where clause in this case. That makes me feel a little better. Our QA people have mostly been testing with the BI Studio KPI browser so far, which exhibits the problem. The other client we need to test is Sharepoint 2007.

Can you share any insight about the rationale for SSAS work this way? In my Google I've seen numerous other people discussing this as a problem, so I hope MS seriously considers addressing it in a future release. We're trying very hard to stay query-tool agnostic in our designs - this definitely makes that more difficult.

Thanks,
Kevin|||

> In my Google I've seen numerous other people discussing this as a problem, so I hope MS seriously considers addressing it in a future release.

First, I do not use Google, I use Microsoft Live Search (www.live.com) instead. Subselects were originally designed to solve different class of problems, but the actual usage proved that people wanted to use them as more functionality rich WHERE clause, although we have tried to separate the functionalities of subselects and WHERE. This obviously didn't work out well, therefore SP2 brought many changes that made subselects and WHERE to be closer. It is correct to assume, that Microsoft seriously considers addressing this issue in the future release.

|||>First, I do not use Google, I use Microsoft Live Search
Naturally. But MS Live Search is so much harder to turn into a verb. ;)

>Microsoft seriously considers addressing this issue in the future release.

When you say "future release" - is this something that could theoretically be addressed in a service pack, were there sufficient demand? Or is it too significant a change for that, and we should expect to have to wait for the next full version of SSAS for a fix?

|||We never discuss the future plans or features that may be added in the future. All I said was that Microsoft is aware of the issue and seriously considers it. When and whether something will be done about it - is up to the product team, and will be disclosed through appropriate channels.|||

Thanks for the posting. I have been testing using both BIDS and the MOSS KPI webpart. For KPI's, BIDS implements an explicit CREATE SUBCUBE for slicing while Sharepoint 2007 seems to use an in-line subselect in the FROM clause to apply the selection from the filter webpart. In both cases a KPI based on a non-calculated measure works fine while any calculated measure based on a ParallelPeriod returns a null. Given the behavior in SP2 is there any available method for defining trend-type KPI's that depend on ParallelPeriod (or similar) functionality? Many of our KPI's depend on calculating the % change from the prior period.

Thanks

John

|||BIDS KPI browser is not end-user client tool, and therefore can be safely ignored here. As far MOSS KPI webpart, I suggest you report this issue to Sharepoint team, and ask them to use WHERE clause instead of subselect (or in addition to subselect if they choose so).|||

I have posted a question on the Sharepoint forum to ask about the KPI webpart.

As for BIDS, there's a point I don't understand. The profiler shows BIDS issuing a CREATE SUBCUBE statement as the slicer before the KPI query is excuted. We're using SQL Server 2005 SP2 and the updated BOL from December contains the following text in the description of the CREATE SUBCUBE statement:

"Also, explicit references to cells outside the subcube return cell values that are evaluated in the context of the whole cube. For example, you create a subcube that is limited to the current year. You then use the ParallelPeriod function to compare the current year to the previous year. The difference in values will be returned even though the previous year’s value lies outside the subcube."

Is this statement correct? Should a ParallelPeriod that defines a member outside the current context as specified by a CREATE SUBCUBE still return a value?

Thanks

|||Yes the statement in BOL is correct. The issue here is that subselect doesn't change current coordinate, so you are looking at ParallelPeriod of All member, which is NULL.

pages split

Hi friends,
We have read only DB (no modifications) on sql2k5(sp2) and when running Perf
Monitor we see spikes of pages split counter, sometimes up to 100 or even
higher. I just wonder what might contribute to that behavior? There is one
stored proc where temp table is created, populated and then clustered index
is created on it. But once index is created no more modifications on temp
table are done.
I'd appreciate any input.
Thanks a lot in advanceHi
What is FILLFACTOR you specify when create an index?Consider increasing the
fill factor of your indexes.
"falconer" <me@.isp.net> wrote in message
news:%23x4YWYoUIHA.4440@.TK2MSFTNGP06.phx.gbl...
> Hi friends,
> We have read only DB (no modifications) on sql2k5(sp2) and when running
> Perf Monitor we see spikes of pages split counter, sometimes up to 100 or
> even higher. I just wonder what might contribute to that behavior? There
> is one stored proc where temp table is created, populated and then
> clustered index is created on it. But once index is created no more
> modifications on temp table are done.
> I'd appreciate any input.
> Thanks a lot in advance|||Would this be the reason since it is just the creation of a clustered index?
I am curious about this because I would hope that the engine just stuffs
each page 100% full (assuming that is FF default) with no splitting at all
during creation. And since the OP stated there wasn't any further
modification to the table there should be no splitting after index creation
either.
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23yV73uoUIHA.4880@.TK2MSFTNGP03.phx.gbl...
> Hi
> What is FILLFACTOR you specify when create an index?Consider increasing
> the fill factor of your indexes.
> "falconer" <me@.isp.net> wrote in message
> news:%23x4YWYoUIHA.4440@.TK2MSFTNGP06.phx.gbl...
>> Hi friends,
>> We have read only DB (no modifications) on sql2k5(sp2) and when running
>> Perf Monitor we see spikes of pages split counter, sometimes up to 100 or
>> even higher. I just wonder what might contribute to that behavior? There
>> is one stored proc where temp table is created, populated and then
>> clustered index is created on it. But once index is created no more
>> modifications on temp table are done.
>> I'd appreciate any input.
>> Thanks a lot in advance
>|||He said that there are no modifications on temp table since CI was created.
I was assumed that the OP might have a procedure to rebuild indexes and
there he might change FF from default to smething else.
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:13o9l33o21f2e37@.corp.supernews.com...
> Would this be the reason since it is just the creation of a clustered
> index? I am curious about this because I would hope that the engine just
> stuffs each page 100% full (assuming that is FF default) with no splitting
> at all during creation. And since the OP stated there wasn't any further
> modification to the table there should be no splitting after index
> creation either.
> --
> Kevin G. Boles
> Indicium Resources, Inc.
> SQL Server MVP
> kgboles a earthlink dt net
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23yV73uoUIHA.4880@.TK2MSFTNGP03.phx.gbl...
>> Hi
>> What is FILLFACTOR you specify when create an index?Consider increasing
>> the fill factor of your indexes.
>> "falconer" <me@.isp.net> wrote in message
>> news:%23x4YWYoUIHA.4440@.TK2MSFTNGP06.phx.gbl...
>> Hi friends,
>> We have read only DB (no modifications) on sql2k5(sp2) and when running
>> Perf Monitor we see spikes of pages split counter, sometimes up to 100
>> or even higher. I just wonder what might contribute to that behavior?
>> There is one stored proc where temp table is created, populated and then
>> clustered index is created on it. But once index is created no more
>> modifications on temp table are done.
>> I'd appreciate any input.
>> Thanks a lot in advance
>>
>|||yeah, that's what puzzles me - no inserts, deletes, or updates whatsoever.
I have suspicion something's going on in TempDB.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23aGtcpsUIHA.280@.TK2MSFTNGP03.phx.gbl...
> He said that there are no modifications on temp table since CI was
> created. I was assumed that the OP might have a procedure to rebuild
> indexes and there he might change FF from default to smething else.
>
>
> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
> news:13o9l33o21f2e37@.corp.supernews.com...
>> Would this be the reason since it is just the creation of a clustered
>> index? I am curious about this because I would hope that the engine just
>> stuffs each page 100% full (assuming that is FF default) with no
>> splitting at all during creation. And since the OP stated there wasn't
>> any further modification to the table there should be no splitting after
>> index creation either.
>> --
>> Kevin G. Boles
>> Indicium Resources, Inc.
>> SQL Server MVP
>> kgboles a earthlink dt net
>>
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:%23yV73uoUIHA.4880@.TK2MSFTNGP03.phx.gbl...
>> Hi
>> What is FILLFACTOR you specify when create an index?Consider increasing
>> the fill factor of your indexes.
>> "falconer" <me@.isp.net> wrote in message
>> news:%23x4YWYoUIHA.4440@.TK2MSFTNGP06.phx.gbl...
>> Hi friends,
>> We have read only DB (no modifications) on sql2k5(sp2) and when running
>> Perf Monitor we see spikes of pages split counter, sometimes up to 100
>> or even higher. I just wonder what might contribute to that behavior?
>> There is one stored proc where temp table is created, populated and
>> then clustered index is created on it. But once index is created no
>> more modifications on temp table are done.
>> I'd appreciate any input.
>> Thanks a lot in advance
>>
>>
>|||Forgot to mention that we saw those spikes under stress test simulating
workload of hundreds users. I managed drastically reduce # of split by
replacing INSERT #temp_table EXEC sp_name with INSERT #temp_table SELECT. I
got no explanation to it. Any thoughts?
"Falconer" <me@.isp.net> wrote in message
news:JS7hj.2983$vp3.2661@.edtnps90...
> yeah, that's what puzzles me - no inserts, deletes, or updates
> whatsoever. I have suspicion something's going on in TempDB.
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23aGtcpsUIHA.280@.TK2MSFTNGP03.phx.gbl...
>> He said that there are no modifications on temp table since CI was
>> created. I was assumed that the OP might have a procedure to rebuild
>> indexes and there he might change FF from default to smething else.
>>
>>
>> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
>> news:13o9l33o21f2e37@.corp.supernews.com...
>> Would this be the reason since it is just the creation of a clustered
>> index? I am curious about this because I would hope that the engine just
>> stuffs each page 100% full (assuming that is FF default) with no
>> splitting at all during creation. And since the OP stated there wasn't
>> any further modification to the table there should be no splitting after
>> index creation either.
>> --
>> Kevin G. Boles
>> Indicium Resources, Inc.
>> SQL Server MVP
>> kgboles a earthlink dt net
>>
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:%23yV73uoUIHA.4880@.TK2MSFTNGP03.phx.gbl...
>> Hi
>> What is FILLFACTOR you specify when create an index?Consider increasing
>> the fill factor of your indexes.
>> "falconer" <me@.isp.net> wrote in message
>> news:%23x4YWYoUIHA.4440@.TK2MSFTNGP06.phx.gbl...
>> Hi friends,
>> We have read only DB (no modifications) on sql2k5(sp2) and when
>> running Perf Monitor we see spikes of pages split counter, sometimes
>> up to 100 or even higher. I just wonder what might contribute to that
>> behavior? There is one stored proc where temp table is created,
>> populated and then clustered index is created on it. But once index is
>> created no more modifications on temp table are done.
>> I'd appreciate any input.
>> Thanks a lot in advance
>>
>>
>>
>|||and 2nd measure that finished splits off completely was removing CREATE
INDEX statement on temp table. SORT op showed up in exec plan, but overall
performance in terms of io and exec time remained about the same, even a bit
better. Who'd have thought...
"Falconer" <me@.isp.net> wrote in message
news:LZ8hj.2999$vp3.1848@.edtnps90...
> Forgot to mention that we saw those spikes under stress test simulating
> workload of hundreds users. I managed drastically reduce # of split by
> replacing INSERT #temp_table EXEC sp_name with INSERT #temp_table SELECT.
> I got no explanation to it. Any thoughts?
> "Falconer" <me@.isp.net> wrote in message
> news:JS7hj.2983$vp3.2661@.edtnps90...
>> yeah, that's what puzzles me - no inserts, deletes, or updates
>> whatsoever. I have suspicion something's going on in TempDB.
>>
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:%23aGtcpsUIHA.280@.TK2MSFTNGP03.phx.gbl...
>> He said that there are no modifications on temp table since CI was
>> created. I was assumed that the OP might have a procedure to rebuild
>> indexes and there he might change FF from default to smething else.
>>
>>
>> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
>> news:13o9l33o21f2e37@.corp.supernews.com...
>> Would this be the reason since it is just the creation of a clustered
>> index? I am curious about this because I would hope that the engine
>> just stuffs each page 100% full (assuming that is FF default) with no
>> splitting at all during creation. And since the OP stated there wasn't
>> any further modification to the table there should be no splitting
>> after index creation either.
>> --
>> Kevin G. Boles
>> Indicium Resources, Inc.
>> SQL Server MVP
>> kgboles a earthlink dt net
>>
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:%23yV73uoUIHA.4880@.TK2MSFTNGP03.phx.gbl...
>> Hi
>> What is FILLFACTOR you specify when create an index?Consider
>> increasing the fill factor of your indexes.
>> "falconer" <me@.isp.net> wrote in message
>> news:%23x4YWYoUIHA.4440@.TK2MSFTNGP06.phx.gbl...
>> Hi friends,
>> We have read only DB (no modifications) on sql2k5(sp2) and when
>> running Perf Monitor we see spikes of pages split counter, sometimes
>> up to 100 or even higher. I just wonder what might contribute to that
>> behavior? There is one stored proc where temp table is created,
>> populated and then clustered index is created on it. But once index
>> is created no more modifications on temp table are done.
>> I'd appreciate any input.
>> Thanks a lot in advance
>>
>>
>>
>>
>|||I would have thought. I fairly regularly see client's putting indexes
(especially clustered) on temp tables and then accessing them just ONCE. In
almost every case I have seen like that the work do do the index is MORE
than the query savings when using the index.
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Falconer" <me@.isp.net> wrote in message news:fCchj.559$yQ1.4@.edtnps89...
> and 2nd measure that finished splits off completely was removing CREATE
> INDEX statement on temp table. SORT op showed up in exec plan, but overall
> performance in terms of io and exec time remained about the same, even a
> bit better. Who'd have thought...
>
> "Falconer" <me@.isp.net> wrote in message
> news:LZ8hj.2999$vp3.1848@.edtnps90...
>> Forgot to mention that we saw those spikes under stress test simulating
>> workload of hundreds users. I managed drastically reduce # of split by
>> replacing INSERT #temp_table EXEC sp_name with INSERT #temp_table SELECT.
>> I got no explanation to it. Any thoughts?
>> "Falconer" <me@.isp.net> wrote in message
>> news:JS7hj.2983$vp3.2661@.edtnps90...
>> yeah, that's what puzzles me - no inserts, deletes, or updates
>> whatsoever. I have suspicion something's going on in TempDB.
>>
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:%23aGtcpsUIHA.280@.TK2MSFTNGP03.phx.gbl...
>> He said that there are no modifications on temp table since CI was
>> created. I was assumed that the OP might have a procedure to rebuild
>> indexes and there he might change FF from default to smething else.
>>
>>
>> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
>> news:13o9l33o21f2e37@.corp.supernews.com...
>> Would this be the reason since it is just the creation of a clustered
>> index? I am curious about this because I would hope that the engine
>> just stuffs each page 100% full (assuming that is FF default) with no
>> splitting at all during creation. And since the OP stated there
>> wasn't any further modification to the table there should be no
>> splitting after index creation either.
>> --
>> Kevin G. Boles
>> Indicium Resources, Inc.
>> SQL Server MVP
>> kgboles a earthlink dt net
>>
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:%23yV73uoUIHA.4880@.TK2MSFTNGP03.phx.gbl...
>> Hi
>> What is FILLFACTOR you specify when create an index?Consider
>> increasing the fill factor of your indexes.
>> "falconer" <me@.isp.net> wrote in message
>> news:%23x4YWYoUIHA.4440@.TK2MSFTNGP06.phx.gbl...
>>> Hi friends,
>>>
>>> We have read only DB (no modifications) on sql2k5(sp2) and when
>>> running Perf Monitor we see spikes of pages split counter, sometimes
>>> up to 100 or even higher. I just wonder what might contribute to
>>> that behavior? There is one stored proc where temp table is created,
>>> populated and then clustered index is created on it. But once index
>>> is created no more modifications on temp table are done.
>>> I'd appreciate any input.
>>>
>>> Thanks a lot in advance
>>
>>
>>
>>
>>
>|||Life is all about learning, heh?
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:13od5fhm25ie9de@.corp.supernews.com...
>I would have thought. I fairly regularly see client's putting indexes
>(especially clustered) on temp tables and then accessing them just ONCE.
>In almost every case I have seen like that the work do do the index is MORE
>than the query savings when using the index.
> --
> Kevin G. Boles
> Indicium Resources, Inc.
> SQL Server MVP
> kgboles a earthlink dt net
>
> "Falconer" <me@.isp.net> wrote in message news:fCchj.559$yQ1.4@.edtnps89...
>> and 2nd measure that finished splits off completely was removing CREATE
>> INDEX statement on temp table. SORT op showed up in exec plan, but
>> overall performance in terms of io and exec time remained about the same,
>> even a bit better. Who'd have thought...
>>
>> "Falconer" <me@.isp.net> wrote in message
>> news:LZ8hj.2999$vp3.1848@.edtnps90...
>> Forgot to mention that we saw those spikes under stress test simulating
>> workload of hundreds users. I managed drastically reduce # of split by
>> replacing INSERT #temp_table EXEC sp_name with INSERT #temp_table
>> SELECT. I got no explanation to it. Any thoughts?
>> "Falconer" <me@.isp.net> wrote in message
>> news:JS7hj.2983$vp3.2661@.edtnps90...
>> yeah, that's what puzzles me - no inserts, deletes, or updates
>> whatsoever. I have suspicion something's going on in TempDB.
>>
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:%23aGtcpsUIHA.280@.TK2MSFTNGP03.phx.gbl...
>> He said that there are no modifications on temp table since CI was
>> created. I was assumed that the OP might have a procedure to rebuild
>> indexes and there he might change FF from default to smething else.
>>
>>
>> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
>> news:13o9l33o21f2e37@.corp.supernews.com...
>> Would this be the reason since it is just the creation of a clustered
>> index? I am curious about this because I would hope that the engine
>> just stuffs each page 100% full (assuming that is FF default) with no
>> splitting at all during creation. And since the OP stated there
>> wasn't any further modification to the table there should be no
>> splitting after index creation either.
>> --
>> Kevin G. Boles
>> Indicium Resources, Inc.
>> SQL Server MVP
>> kgboles a earthlink dt net
>>
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:%23yV73uoUIHA.4880@.TK2MSFTNGP03.phx.gbl...
>>> Hi
>>> What is FILLFACTOR you specify when create an index?Consider
>>> increasing the fill factor of your indexes.
>>>
>>> "falconer" <me@.isp.net> wrote in message
>>> news:%23x4YWYoUIHA.4440@.TK2MSFTNGP06.phx.gbl...
>>> Hi friends,
>>>
>>> We have read only DB (no modifications) on sql2k5(sp2) and when
>>> running Perf Monitor we see spikes of pages split counter,
>>> sometimes up to 100 or even higher. I just wonder what might
>>> contribute to that behavior? There is one stored proc where temp
>>> table is created, populated and then clustered index is created on
>>> it. But once index is created no more modifications on temp table
>>> are done.
>>> I'd appreciate any input.
>>>
>>> Thanks a lot in advance
>>>
>>>
>>
>>
>>
>>
>>
>

pages split

Hi friends,
We have read only DB (no modifications) on sql2k5(sp2) and when running Perf
Monitor we see spikes of pages split counter, sometimes up to 100 or even
higher. I just wonder what might contribute to that behavior? There is one
stored proc where temp table is created, populated and then clustered index
is created on it. But once index is created no more modifications on temp
table are done.
I'd appreciate any input.
Thanks a lot in advance
Hi
What is FILLFACTOR you specify when create an index?Consider increasing the
fill factor of your indexes.
"falconer" <me@.isp.net> wrote in message
news:%23x4YWYoUIHA.4440@.TK2MSFTNGP06.phx.gbl...
> Hi friends,
> We have read only DB (no modifications) on sql2k5(sp2) and when running
> Perf Monitor we see spikes of pages split counter, sometimes up to 100 or
> even higher. I just wonder what might contribute to that behavior? There
> is one stored proc where temp table is created, populated and then
> clustered index is created on it. But once index is created no more
> modifications on temp table are done.
> I'd appreciate any input.
> Thanks a lot in advance
|||Would this be the reason since it is just the creation of a clustered index?
I am curious about this because I would hope that the engine just stuffs
each page 100% full (assuming that is FF default) with no splitting at all
during creation. And since the OP stated there wasn't any further
modification to the table there should be no splitting after index creation
either.
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23yV73uoUIHA.4880@.TK2MSFTNGP03.phx.gbl...
> Hi
> What is FILLFACTOR you specify when create an index?Consider increasing
> the fill factor of your indexes.
> "falconer" <me@.isp.net> wrote in message
> news:%23x4YWYoUIHA.4440@.TK2MSFTNGP06.phx.gbl...
>
|||He said that there are no modifications on temp table since CI was created.
I was assumed that the OP might have a procedure to rebuild indexes and
there he might change FF from default to smething else.
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:13o9l33o21f2e37@.corp.supernews.com...
> Would this be the reason since it is just the creation of a clustered
> index? I am curious about this because I would hope that the engine just
> stuffs each page 100% full (assuming that is FF default) with no splitting
> at all during creation. And since the OP stated there wasn't any further
> modification to the table there should be no splitting after index
> creation either.
> --
> Kevin G. Boles
> Indicium Resources, Inc.
> SQL Server MVP
> kgboles a earthlink dt net
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23yV73uoUIHA.4880@.TK2MSFTNGP03.phx.gbl...
>
|||yeah, that's what puzzles me - no inserts, deletes, or updates whatsoever.
I have suspicion something's going on in TempDB.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23aGtcpsUIHA.280@.TK2MSFTNGP03.phx.gbl...
> He said that there are no modifications on temp table since CI was
> created. I was assumed that the OP might have a procedure to rebuild
> indexes and there he might change FF from default to smething else.
>
>
> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
> news:13o9l33o21f2e37@.corp.supernews.com...
>
|||Forgot to mention that we saw those spikes under stress test simulating
workload of hundreds users. I managed drastically reduce # of split by
replacing INSERT #temp_table EXEC sp_name with INSERT #temp_table SELECT. I
got no explanation to it. Any thoughts?
"Falconer" <me@.isp.net> wrote in message
news:JS7hj.2983$vp3.2661@.edtnps90...
> yeah, that's what puzzles me - no inserts, deletes, or updates
> whatsoever. I have suspicion something's going on in TempDB.
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23aGtcpsUIHA.280@.TK2MSFTNGP03.phx.gbl...
>
|||and 2nd measure that finished splits off completely was removing CREATE
INDEX statement on temp table. SORT op showed up in exec plan, but overall
performance in terms of io and exec time remained about the same, even a bit
better. Who'd have thought...
"Falconer" <me@.isp.net> wrote in message
news:LZ8hj.2999$vp3.1848@.edtnps90...
> Forgot to mention that we saw those spikes under stress test simulating
> workload of hundreds users. I managed drastically reduce # of split by
> replacing INSERT #temp_table EXEC sp_name with INSERT #temp_table SELECT.
> I got no explanation to it. Any thoughts?
> "Falconer" <me@.isp.net> wrote in message
> news:JS7hj.2983$vp3.2661@.edtnps90...
>
|||I would have thought. I fairly regularly see client's putting indexes
(especially clustered) on temp tables and then accessing them just ONCE. In
almost every case I have seen like that the work do do the index is MORE
than the query savings when using the index.
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Falconer" <me@.isp.net> wrote in message news:fCchj.559$yQ1.4@.edtnps89...
> and 2nd measure that finished splits off completely was removing CREATE
> INDEX statement on temp table. SORT op showed up in exec plan, but overall
> performance in terms of io and exec time remained about the same, even a
> bit better. Who'd have thought...
>
> "Falconer" <me@.isp.net> wrote in message
> news:LZ8hj.2999$vp3.1848@.edtnps90...
>
|||Life is all about learning, heh?
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:13od5fhm25ie9de@.corp.supernews.com...
>I would have thought. I fairly regularly see client's putting indexes
>(especially clustered) on temp tables and then accessing them just ONCE.
>In almost every case I have seen like that the work do do the index is MORE
>than the query savings when using the index.
> --
> Kevin G. Boles
> Indicium Resources, Inc.
> SQL Server MVP
> kgboles a earthlink dt net
>
> "Falconer" <me@.isp.net> wrote in message news:fCchj.559$yQ1.4@.edtnps89...
>