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
>>>
>>>
>>
>>
>>
>>
>>
>