Paging with Stored Procedures

I have been browsing the newsgroups trying to find a good solution for this
problem.
I have a resultset and I need to show that information in pages. I also need
to have this sorted by a specific column (Date for example)
I found the following solution written by Don Arsenault that works very
well:
/*
The above routines assume that the resultset is ordered by the unique key.
If that's not true, a combination of a sort column and the unique key
can be used. Pass the sort column value as well as the unique key to the
next_page and previous_page procedures. Make sure the table has an index
on the combination of the sort column and the unqiue key.
*/
CREATE PROCEDURE next_page
@.current_page_last_row_key int,
@.current_page_last_row_sort int
AS
--return first page if parameters are null.
IF (@.current_page_last_row_key is null)
SELECT TOP 10 *
FROM my_big_table
ORDER BY sort_column, unique_key
ELSE
SELECT TOP 10 *
FROM my_big_table
WHERE
(sort_column >= @.current_page_last_row_sort)
and (
(sort_column > @.current_page_last_row_sort)
or (unique_key > @.current_page_last_row_key)
)
ORDER BY sort_column, unique_key
CREATE PROCEDURE previous_page
@.current_page_first_row_key int,
@.current_page_first_row_sort int
AS
--return last page if parameters are null.
IF (@.current_page_first_row_sort_key is null)
SELECT *
FROM
(
SELECT TOP 10 *
FROM my_big_table
ORDER BY sort_column DESC, unique_key DESC
) AS Reorder
ORDER BY unique_key
ELSE
SELECT *
FROM
(
SELECT TOP 10 *
FROM my_big_table
WHERE
(sort_column <= @.current_page_last_row_sort)
and (
(sort_column < @.current_page_last_row_sort)
or (unique_key < @.current_page_last_row_key)
)
ORDER BY sort_column DESC, unique_key DESC
) AS Reorder
ORDER BY sort_column, unique_key
That works great when you want to move from one page to the next (or to the
previous one), but I don't know
how I can go to a specific page. For example, go to page 100.
Do you guys know how I can get this?
ThanksYou may also want to refer to www.aspfaq.com/2120 for some ideas.
Anith

Paging

What does it mean when your cpu is low (20%ish), you avg
disk queue length is reasonable and your pages/second is
through the roof for about 15 minutes? (50-2000 pages/sec)
Not enough RAM I assume? Or could there be something else
causing it?Could be that data is being read from disk that wasn't in memory, although
you need a lot of data to keep that up for 15 minutes. More likely that
there is an operation going on that doesn't fit in memory completely and SQL
Server is using tempdb to store intermediate results. ORDER BY or DISTINCT
on a large dataset is a common cause for this.
You can use SQL Profiler and trace Error and Warnings:Hash Warning. If you
get these it's a sign of the above mentioned problem.
--
Jacco Schalkwijk
SQL Server MVP
"Baffled" <anonymous@.discussions.microsoft.com> wrote in message
news:0a6301c4ad5f$191bd7d0$a501280a@.phx.gbl...
> What does it mean when your cpu is low (20%ish), you avg
> disk queue length is reasonable and your pages/second is
> through the roof for about 15 minutes? (50-2000 pages/sec)
> Not enough RAM I assume? Or could there be something else
> causing it?|||I just did some research and found three things were going
on during that time period (Sql Server 2000 btw):
1. A checkdb of a good sized database (about 95G).
2. Snapshot replication to two servers.
3. Backups of the same database that was 95g above.
I'm pretty sure #3 is low level - we took compression
out. Can #1 or #2 swallow up a lot of RAM?
Btw, it was at the very end of the checkdb that the paging
occurred, i.e. the last 15-20 minutes of a 2+ hour job.
Do you know if checkdb is particulary ram/cpu intensive at
the very end for some reason?
>--Original Message--
>Could be that data is being read from disk that wasn't in
memory, although
>you need a lot of data to keep that up for 15 minutes.
More likely that
>there is an operation going on that doesn't fit in memory
completely and SQL
>Server is using tempdb to store intermediate results.
ORDER BY or DISTINCT
>on a large dataset is a common cause for this.
>You can use SQL Profiler and trace Error and
Warnings:Hash Warning. If you
>get these it's a sign of the above mentioned problem.
>--
>Jacco Schalkwijk
>SQL Server MVP
>
>"Baffled" <anonymous@.discussions.microsoft.com> wrote in
message
>news:0a6301c4ad5f$191bd7d0$a501280a@.phx.gbl...
>> What does it mean when your cpu is low (20%ish), you avg
>> disk queue length is reasonable and your pages/second is
>> through the roof for about 15 minutes? (50-2000
pages/sec)
>> Not enough RAM I assume? Or could there be something
else
>> causing it?
>
>.
>|||I just did some research and found three things were going
on during that time period (Sql Server 2000 btw):
1. A checkdb of a good sized database (about 95G).
2. Snapshot replication to two servers.
3. Backups of the same database that was 95g above.
I'm pretty sure #3 is low level - we took compression
out. Can #1 or #2 swallow up a lot of RAM?
Btw, it was at the very end of the checkdb that the paging
occurred, i.e. the last 15-20 minutes of a 2+ hour job.
Do you know if checkdb is particulary ram/cpu intensive at
the very end for some reason?
>--Original Message--
>Could be that data is being read from disk that wasn't in
memory, although
>you need a lot of data to keep that up for 15 minutes.
More likely that
>there is an operation going on that doesn't fit in memory
completely and SQL
>Server is using tempdb to store intermediate results.
ORDER BY or DISTINCT
>on a large dataset is a common cause for this.
>You can use SQL Profiler and trace Error and
Warnings:Hash Warning. If you
>get these it's a sign of the above mentioned problem.
>--
>Jacco Schalkwijk
>SQL Server MVP
>
>"Baffled" <anonymous@.discussions.microsoft.com> wrote in
message
>news:0a6301c4ad5f$191bd7d0$a501280a@.phx.gbl...
>> What does it mean when your cpu is low (20%ish), you avg
>> disk queue length is reasonable and your pages/second is
>> through the roof for about 15 minutes? (50-2000
pages/sec)
>> Not enough RAM I assume? Or could there be something
else
>> causing it?
>
>.
>--Original Message--
>Could be that data is being read from disk that wasn't in
memory, although
>you need a lot of data to keep that up for 15 minutes.
More likely that
>there is an operation going on that doesn't fit in memory
completely and SQL
>Server is using tempdb to store intermediate results.
ORDER BY or DISTINCT
>on a large dataset is a common cause for this.
>You can use SQL Profiler and trace Error and
Warnings:Hash Warning. If you
>get these it's a sign of the above mentioned problem.
>--
>Jacco Schalkwijk
>SQL Server MVP
>
>"Baffled" <anonymous@.discussions.microsoft.com> wrote in
message
>news:0a6301c4ad5f$191bd7d0$a501280a@.phx.gbl...
>> What does it mean when your cpu is low (20%ish), you avg
>> disk queue length is reasonable and your pages/second is
>> through the roof for about 15 minutes? (50-2000
pages/sec)
>> Not enough RAM I assume? Or could there be something
else
>> causing it?
>
>.
>|||I don't think there is anything in particular going on towards the end of
DBCC CHECKDB. You might get a lot of activity with DBCC CHECKDB if you have
an error, for example a corrupt index. But that can happen at any time
during DBCC CHECKDB, not specifically at the end.
In the scenario you describe there is of course a lot of activity going on
at the same time. Can you give some more information about your disk set up?
Is everything on the same disk/array or on multiple disks?
--
Jacco Schalkwijk
SQL Server MVP
<anonymous@.discussions.microsoft.com> wrote in message
news:268f01c4ad66$8102cb90$a401280a@.phx.gbl...
>I just did some research and found three things were going
> on during that time period (Sql Server 2000 btw):
> 1. A checkdb of a good sized database (about 95G).
> 2. Snapshot replication to two servers.
> 3. Backups of the same database that was 95g above.
> I'm pretty sure #3 is low level - we took compression
> out. Can #1 or #2 swallow up a lot of RAM?
> Btw, it was at the very end of the checkdb that the paging
> occurred, i.e. the last 15-20 minutes of a 2+ hour job.
> Do you know if checkdb is particulary ram/cpu intensive at
> the very end for some reason?
>
>>--Original Message--
>>Could be that data is being read from disk that wasn't in
> memory, although
>>you need a lot of data to keep that up for 15 minutes.
> More likely that
>>there is an operation going on that doesn't fit in memory
> completely and SQL
>>Server is using tempdb to store intermediate results.
> ORDER BY or DISTINCT
>>on a large dataset is a common cause for this.
>>You can use SQL Profiler and trace Error and
> Warnings:Hash Warning. If you
>>get these it's a sign of the above mentioned problem.
>>--
>>Jacco Schalkwijk
>>SQL Server MVP
>>
>>"Baffled" <anonymous@.discussions.microsoft.com> wrote in
> message
>>news:0a6301c4ad5f$191bd7d0$a501280a@.phx.gbl...
>> What does it mean when your cpu is low (20%ish), you avg
>> disk queue length is reasonable and your pages/second is
>> through the roof for about 15 minutes? (50-2000
> pages/sec)
>> Not enough RAM I assume? Or could there be something
> else
>> causing it?
>>
>>.

Paging

I've got a four way 2000 server that has a buffer cache hit ratio that is 99
or 100 % all the time. However, I am getting Pages/sec quite often on that
server between 20 and 100. Is this a problem? I'm asking because I thought
that as long as the buffer cache hit ratio was 99% or higher, all was well.
But is that necessarily true? Is there any other way that I can check to see
if this server needs more RAM?Pages/Sec has little to do with the buffer cache hit ratio. First off those
numbers are not high at all. But you might want to see if you have other
applications than SQL Server running on the server. If so and they are run
all the time or even frequently you may want to set the max memory setting
for SQL Server to always leave memory for the OS and these other apps.
--
Andrew J. Kelly SQL MVP
"CLM" <CLM@.discussions.microsoft.com> wrote in message
news:6C4D7224-22CE-451B-B5DC-B61C58780CC5@.microsoft.com...
> I've got a four way 2000 server that has a buffer cache hit ratio that is
> 99
> or 100 % all the time. However, I am getting Pages/sec quite often on
> that
> server between 20 and 100. Is this a problem? I'm asking because I
> thought
> that as long as the buffer cache hit ratio was 99% or higher, all was
> well.
> But is that necessarily true? Is there any other way that I can check to
> see
> if this server needs more RAM?|||"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uYvMQYlBGHA.916@.TK2MSFTNGP10.phx.gbl...
> Pages/Sec has little to do with the buffer cache hit ratio. First off
> those numbers are not high at all. But you might want to see if you have
> other applications than SQL Server running on the server. If so and they
> are run all the time or even frequently you may want to set the max memory
> setting for SQL Server to always leave memory for the OS and these other
> apps.
>
Moreover a buffer cache-hit ration of 99% isn't particularly high.
Moreover, moreover an extremely high buffer cache-hit ratio often just means
you have inefficient queries which are reading and re-reading the same pages
over and over again.
David

Paging

I've got a four way 2000 server that has a buffer cache hit ratio that is 99
or 100 % all the time. However, I am getting Pages/sec quite often on that
server between 20 and 100. Is this a problem? I'm asking because I thought
that as long as the buffer cache hit ratio was 99% or higher, all was well.
But is that necessarily true? Is there any other way that I can check to se
e
if this server needs more RAM?Pages/Sec has little to do with the buffer cache hit ratio. First off those
numbers are not high at all. But you might want to see if you have other
applications than SQL Server running on the server. If so and they are run
all the time or even frequently you may want to set the max memory setting
for SQL Server to always leave memory for the OS and these other apps.
Andrew J. Kelly SQL MVP
"CLM" <CLM@.discussions.microsoft.com> wrote in message
news:6C4D7224-22CE-451B-B5DC-B61C58780CC5@.microsoft.com...
> I've got a four way 2000 server that has a buffer cache hit ratio that is
> 99
> or 100 % all the time. However, I am getting Pages/sec quite often on
> that
> server between 20 and 100. Is this a problem? I'm asking because I
> thought
> that as long as the buffer cache hit ratio was 99% or higher, all was
> well.
> But is that necessarily true? Is there any other way that I can check to
> see
> if this server needs more RAM?|||"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uYvMQYlBGHA.916@.TK2MSFTNGP10.phx.gbl...
> Pages/Sec has little to do with the buffer cache hit ratio. First off
> those numbers are not high at all. But you might want to see if you have
> other applications than SQL Server running on the server. If so and they
> are run all the time or even frequently you may want to set the max memory
> setting for SQL Server to always leave memory for the OS and these other
> apps.
>
Moreover a buffer cache-hit ration of 99% isn't particularly high.
Moreover, moreover an extremely high buffer cache-hit ratio often just means
you have inefficient queries which are reading and re-reading the same pages
over and over again.
David

Paging

What does it mean when your cpu is low (20%ish), you avg
disk queue length is reasonable and your pages/second is
through the roof for about 15 minutes? (50-2000 pages/sec)
Not enough RAM I assume? Or could there be something else
causing it?
Could be that data is being read from disk that wasn't in memory, although
you need a lot of data to keep that up for 15 minutes. More likely that
there is an operation going on that doesn't fit in memory completely and SQL
Server is using tempdb to store intermediate results. ORDER BY or DISTINCT
on a large dataset is a common cause for this.
You can use SQL Profiler and trace Error and Warnings:Hash Warning. If you
get these it's a sign of the above mentioned problem.
Jacco Schalkwijk
SQL Server MVP
"Baffled" <anonymous@.discussions.microsoft.com> wrote in message
news:0a6301c4ad5f$191bd7d0$a501280a@.phx.gbl...
> What does it mean when your cpu is low (20%ish), you avg
> disk queue length is reasonable and your pages/second is
> through the roof for about 15 minutes? (50-2000 pages/sec)
> Not enough RAM I assume? Or could there be something else
> causing it?
|||I just did some research and found three things were going
on during that time period (Sql Server 2000 btw):
1. A checkdb of a good sized database (about 95G).
2. Snapshot replication to two servers.
3. Backups of the same database that was 95g above.
I'm pretty sure #3 is low level - we took compression
out. Can #1 or #2 swallow up a lot of RAM?
Btw, it was at the very end of the checkdb that the paging
occurred, i.e. the last 15-20 minutes of a 2+ hour job.
Do you know if checkdb is particulary ram/cpu intensive at
the very end for some reason?

>--Original Message--
>Could be that data is being read from disk that wasn't in
memory, although
>you need a lot of data to keep that up for 15 minutes.
More likely that
>there is an operation going on that doesn't fit in memory
completely and SQL
>Server is using tempdb to store intermediate results.
ORDER BY or DISTINCT
>on a large dataset is a common cause for this.
>You can use SQL Profiler and trace Error and
Warnings:Hash Warning. If you
>get these it's a sign of the above mentioned problem.
>--
>Jacco Schalkwijk
>SQL Server MVP
>
>"Baffled" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:0a6301c4ad5f$191bd7d0$a501280a@.phx.gbl...
pages/sec)[vbcol=seagreen]
else
>
>.
>
|||I just did some research and found three things were going
on during that time period (Sql Server 2000 btw):
1. A checkdb of a good sized database (about 95G).
2. Snapshot replication to two servers.
3. Backups of the same database that was 95g above.
I'm pretty sure #3 is low level - we took compression
out. Can #1 or #2 swallow up a lot of RAM?
Btw, it was at the very end of the checkdb that the paging
occurred, i.e. the last 15-20 minutes of a 2+ hour job.
Do you know if checkdb is particulary ram/cpu intensive at
the very end for some reason?

>--Original Message--
>Could be that data is being read from disk that wasn't in
memory, although
>you need a lot of data to keep that up for 15 minutes.
More likely that
>there is an operation going on that doesn't fit in memory
completely and SQL
>Server is using tempdb to store intermediate results.
ORDER BY or DISTINCT
>on a large dataset is a common cause for this.
>You can use SQL Profiler and trace Error and
Warnings:Hash Warning. If you
>get these it's a sign of the above mentioned problem.
>--
>Jacco Schalkwijk
>SQL Server MVP
>
>"Baffled" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:0a6301c4ad5f$191bd7d0$a501280a@.phx.gbl...
pages/sec)[vbcol=seagreen]
else
>
>.
>--Original Message--
>Could be that data is being read from disk that wasn't in
memory, although
>you need a lot of data to keep that up for 15 minutes.
More likely that
>there is an operation going on that doesn't fit in memory
completely and SQL
>Server is using tempdb to store intermediate results.
ORDER BY or DISTINCT
>on a large dataset is a common cause for this.
>You can use SQL Profiler and trace Error and
Warnings:Hash Warning. If you
>get these it's a sign of the above mentioned problem.
>--
>Jacco Schalkwijk
>SQL Server MVP
>
>"Baffled" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:0a6301c4ad5f$191bd7d0$a501280a@.phx.gbl...
pages/sec)[vbcol=seagreen]
else
>
>.
>
|||I don't think there is anything in particular going on towards the end of
DBCC CHECKDB. You might get a lot of activity with DBCC CHECKDB if you have
an error, for example a corrupt index. But that can happen at any time
during DBCC CHECKDB, not specifically at the end.
In the scenario you describe there is of course a lot of activity going on
at the same time. Can you give some more information about your disk set up?
Is everything on the same disk/array or on multiple disks?
Jacco Schalkwijk
SQL Server MVP
<anonymous@.discussions.microsoft.com> wrote in message
news:268f01c4ad66$8102cb90$a401280a@.phx.gbl...[vbcol=seagreen]
>I just did some research and found three things were going
> on during that time period (Sql Server 2000 btw):
> 1. A checkdb of a good sized database (about 95G).
> 2. Snapshot replication to two servers.
> 3. Backups of the same database that was 95g above.
> I'm pretty sure #3 is low level - we took compression
> out. Can #1 or #2 swallow up a lot of RAM?
> Btw, it was at the very end of the checkdb that the paging
> occurred, i.e. the last 15-20 minutes of a 2+ hour job.
> Do you know if checkdb is particulary ram/cpu intensive at
> the very end for some reason?
>
> memory, although
> More likely that
> completely and SQL
> ORDER BY or DISTINCT
> Warnings:Hash Warning. If you
> message
> pages/sec)
> else

Paging

I've got a four way 2000 server that has a buffer cache hit ratio that is 99
or 100 % all the time. However, I am getting Pages/sec quite often on that
server between 20 and 100. Is this a problem? I'm asking because I thought
that as long as the buffer cache hit ratio was 99% or higher, all was well.
But is that necessarily true? Is there any other way that I can check to see
if this server needs more RAM?
Pages/Sec has little to do with the buffer cache hit ratio. First off those
numbers are not high at all. But you might want to see if you have other
applications than SQL Server running on the server. If so and they are run
all the time or even frequently you may want to set the max memory setting
for SQL Server to always leave memory for the OS and these other apps.
Andrew J. Kelly SQL MVP
"CLM" <CLM@.discussions.microsoft.com> wrote in message
news:6C4D7224-22CE-451B-B5DC-B61C58780CC5@.microsoft.com...
> I've got a four way 2000 server that has a buffer cache hit ratio that is
> 99
> or 100 % all the time. However, I am getting Pages/sec quite often on
> that
> server between 20 and 100. Is this a problem? I'm asking because I
> thought
> that as long as the buffer cache hit ratio was 99% or higher, all was
> well.
> But is that necessarily true? Is there any other way that I can check to
> see
> if this server needs more RAM?
|||"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uYvMQYlBGHA.916@.TK2MSFTNGP10.phx.gbl...
> Pages/Sec has little to do with the buffer cache hit ratio. First off
> those numbers are not high at all. But you might want to see if you have
> other applications than SQL Server running on the server. If so and they
> are run all the time or even frequently you may want to set the max memory
> setting for SQL Server to always leave memory for the OS and these other
> apps.
>
Moreover a buffer cache-hit ration of 99% isn't particularly high.
Moreover, moreover an extremely high buffer cache-hit ratio often just means
you have inefficient queries which are reading and re-reading the same pages
over and over again.
David

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

Pagefile and Paging

My DBA sees paging ( high pages/sec) and thinks of increasing pagefile as a
solution. He believes the pagefile settings should be 1.5 times the amount
of physical RAM which I agree but I seem to find it hard to correlate paging
with pagefile increase.
Also under what conditions would one need ot consider increasing the size of
the pagefile if its not set to 1.5 * Physical RAM ?
We are using SQL 2000/2005
Thank you.
"F" <f@.hotmail.com> wrote in message
news:uBNVAg3lIHA.5684@.TK2MSFTNGP03.phx.gbl...
> My DBA sees paging ( high pages/sec) and thinks of increasing pagefile as
> a solution. He believes the pagefile settings should be 1.5 times the
> amount of physical RAM which I agree but I seem to find it hard to
> correlate paging with pagefile increase.
>
He's 1/2 right. You need more memory. But increasing the pagefile won't
make a difference.
Basically SQL Server can page, or it can ask the OS to page to disk. Both
involve disk I/O.
Get more memory or rewrite your queries.

> Also under what conditions would one need ot consider increasing the size
> of the pagefile if its not set to 1.5 * Physical RAM ?
> We are using SQL 2000/2005
> Thank you.
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
|||Increasing the page file size generally would not be the solution to hard
paging. I'd first try to determine whether hard paging is from the SQL Server
process (i.e. whether Windows is paging out the workign set of the SQL Server
process). If that's the case, try to find whether there is any other
processes that are consuming memory and caused paging.
It's also possible that you may be running into a SQL Server bug. For
instance, http://support.microsoft.com/kb/884593 or
http://support.microsoft.com/kb/918483 are examples. I'm not saying that they
apply to your case, but want to point this out as a possibility.
Linchi
"F" wrote:

> My DBA sees paging ( high pages/sec) and thinks of increasing pagefile as a
> solution. He believes the pagefile settings should be 1.5 times the amount
> of physical RAM which I agree but I seem to find it hard to correlate paging
> with pagefile increase.
> Also under what conditions would one need ot consider increasing the size of
> the pagefile if its not set to 1.5 * Physical RAM ?
> We are using SQL 2000/2005
> Thank you.
>
|||I too agree that increasing the pagefile size will not help at all. Your
DBA's first thought should have been "what is causing the paging" and not
how to get around it. SQL Server is designed to do as much as possible to
avoid paging to begin with. It is likely you have other applications on the
server than SQL Server that require some memory and SQL Server is set to use
most of it. If that is the case you may be able to avoid the paging by
setting the MAX Memory setting in SQL Server to leave room for the other
apps. Adding additional memory may also be an option but you still have to
consider how all the apps play together.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"F" <f@.hotmail.com> wrote in message
news:uBNVAg3lIHA.5684@.TK2MSFTNGP03.phx.gbl...
> My DBA sees paging ( high pages/sec) and thinks of increasing pagefile as
> a solution. He believes the pagefile settings should be 1.5 times the
> amount of physical RAM which I agree but I seem to find it hard to
> correlate paging with pagefile increase.
> Also under what conditions would one need ot consider increasing the size
> of the pagefile if its not set to 1.5 * Physical RAM ?
> We are using SQL 2000/2005
> Thank you.

Pagefile and Paging

My DBA sees paging ( high pages/sec) and thinks of increasing pagefile as a
solution. He believes the pagefile settings should be 1.5 times the amount
of physical RAM which I agree but I seem to find it hard to correlate paging
with pagefile increase.
Also under what conditions would one need ot consider increasing the size of
the pagefile if its not set to 1.5 * Physical RAM ?
We are using SQL 2000/2005
Thank you."F" <f@.hotmail.com> wrote in message
news:uBNVAg3lIHA.5684@.TK2MSFTNGP03.phx.gbl...
> My DBA sees paging ( high pages/sec) and thinks of increasing pagefile as
> a solution. He believes the pagefile settings should be 1.5 times the
> amount of physical RAM which I agree but I seem to find it hard to
> correlate paging with pagefile increase.
>
He's 1/2 right. You need more memory. But increasing the pagefile won't
make a difference.
Basically SQL Server can page, or it can ask the OS to page to disk. Both
involve disk I/O.
Get more memory or rewrite your queries.
> Also under what conditions would one need ot consider increasing the size
> of the pagefile if its not set to 1.5 * Physical RAM ?
> We are using SQL 2000/2005
> Thank you.
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||Increasing the page file size generally would not be the solution to hard
paging. I'd first try to determine whether hard paging is from the SQL Server
process (i.e. whether Windows is paging out the workign set of the SQL Server
process). If that's the case, try to find whether there is any other
processes that are consuming memory and caused paging.
It's also possible that you may be running into a SQL Server bug. For
instance, http://support.microsoft.com/kb/884593 or
http://support.microsoft.com/kb/918483 are examples. I'm not saying that they
apply to your case, but want to point this out as a possibility.
Linchi
"F" wrote:
> My DBA sees paging ( high pages/sec) and thinks of increasing pagefile as a
> solution. He believes the pagefile settings should be 1.5 times the amount
> of physical RAM which I agree but I seem to find it hard to correlate paging
> with pagefile increase.
> Also under what conditions would one need ot consider increasing the size of
> the pagefile if its not set to 1.5 * Physical RAM ?
> We are using SQL 2000/2005
> Thank you.
>|||I too agree that increasing the pagefile size will not help at all. Your
DBA's first thought should have been "what is causing the paging" and not
how to get around it. SQL Server is designed to do as much as possible to
avoid paging to begin with. It is likely you have other applications on the
server than SQL Server that require some memory and SQL Server is set to use
most of it. If that is the case you may be able to avoid the paging by
setting the MAX Memory setting in SQL Server to leave room for the other
apps. Adding additional memory may also be an option but you still have to
consider how all the apps play together.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"F" <f@.hotmail.com> wrote in message
news:uBNVAg3lIHA.5684@.TK2MSFTNGP03.phx.gbl...
> My DBA sees paging ( high pages/sec) and thinks of increasing pagefile as
> a solution. He believes the pagefile settings should be 1.5 times the
> amount of physical RAM which I agree but I seem to find it hard to
> correlate paging with pagefile increase.
> Also under what conditions would one need ot consider increasing the size
> of the pagefile if its not set to 1.5 * Physical RAM ?
> We are using SQL 2000/2005
> Thank you.

PageBreak only before odd pages

Hi all,

I have a report with a few subreports and after each subreport I've added a page-break. But I want to skip a page and to leave it blank if the previous subreport ends at an odd page, so the next subreport will start at the next odd page instead of the even page.

I use a transparent rectangle to add page breaks after each subreport, but because I don't have access to the Global.PageNumber variable in the body section of the report, I don't know when the page is even, so a second rectangle with a custom expression for the Visibility property is unuseful.

Does anyone know how to fix this issue?

Thank you and I look forward to seeing some suggestions.

Radu.

Have you found any solution if yes please let me know

PageBreak only before odd pages

Hi all,

I have a report with a few subreports and after each subreport I've added a page-break. But I want to skip a page and to leave it blank if the previous subreport ends at an odd page, so the next subreport will start at the next odd page instead of the even page.

I use a transparent rectangle to add page breaks after each subreport, but because I don't have access to the Global.PageNumber variable in the body section of the report, I don't know when the page is even, so a second rectangle with a custom expression for the Visibility property is unuseful.

Does anyone know how to fix this issue?

Thank you and I look forward to seeing some suggestions.

Radu.

Have you found any solution if yes please let me know

Page splits/ Dirty pages/ Checkpoint

We have few tables in our application. Basically they are used as temporary
tables. Each day they start with no records in it(empty) and as the day goes
they are filled with the data and at the end of the day they will be
truncated to get ready for the next day. We have a high transaction rate
about 4000/sec. When I noticed the page splits /sec counter it is showing
about 130-160 per second. This is driving the checkpoint to take longer time
.
How Can I reduce this high page splits.
Another question is, we have a char(15) column in those tables and that
column is indexed. It is an Id column but is not unique. Each record has an
unique number Id(generated by our app). But we need to seacrh on the CHAR Id
column so indexed on it. This index is creating/making lot of dirty pages.
This also is a contributing reason for the checkpoint to take longer. How ca
n
I make changes to the index so that it would not create/make many pages
dirty? I tried to change it to VARCHAR and there is not much difference.
The check point is taking about 10-15 seconds and it repeats every 60 second
s.
Your suggestion is greatly appreciated.
Thanks.
Thanks.Just give a try with the following info
1. Check the recovery interval option on the system.
2. make sure that the temporary tables have fixed size by using char
rather than
varchar therefore you can reduce the page spilts.
HTH
Regards
Rajesh Peddireddy.
"Srini" wrote:

> We have few tables in our application. Basically they are used as temporar
y
> tables. Each day they start with no records in it(empty) and as the day go
es
> they are filled with the data and at the end of the day they will be
> truncated to get ready for the next day. We have a high transaction rate
> about 4000/sec. When I noticed the page splits /sec counter it is showing
> about 130-160 per second. This is driving the checkpoint to take longer ti
me.
> How Can I reduce this high page splits.
> Another question is, we have a char(15) column in those tables and that
> column is indexed. It is an Id column but is not unique. Each record has a
n
> unique number Id(generated by our app). But we need to seacrh on the CHAR
Id
> column so indexed on it. This index is creating/making lot of dirty pages.
> This also is a contributing reason for the checkpoint to take longer. How
can
> I make changes to the index so that it would not create/make many pages
> dirty? I tried to change it to VARCHAR and there is not much difference.
> The check point is taking about 10-15 seconds and it repeats every 60 seco
nds.
> Your suggestion is greatly appreciated.
> Thanks.
> Thanks.|||It would really help to show the entire DDL for the table including the
indexes. It sounds like your disk subsystem isn't up to the task. If you
are going to have that many transactions you need a fast disk I/O subsystem,
especially for the transaction logs. Is the log file on it's own RAID 1 or
RAID 10 and is the data on a RAID 10?
Andrew J. Kelly SQL MVP
"Srini" <Srini@.discussions.microsoft.com> wrote in message
news:1F6FF490-C19A-4DFE-BDBC-3AD397CC9D5A@.microsoft.com...
> We have few tables in our application. Basically they are used as
> temporary
> tables. Each day they start with no records in it(empty) and as the day
> goes
> they are filled with the data and at the end of the day they will be
> truncated to get ready for the next day. We have a high transaction rate
> about 4000/sec. When I noticed the page splits /sec counter it is showing
> about 130-160 per second. This is driving the checkpoint to take longer
> time.
> How Can I reduce this high page splits.
> Another question is, we have a char(15) column in those tables and that
> column is indexed. It is an Id column but is not unique. Each record has
> an
> unique number Id(generated by our app). But we need to seacrh on the CHAR
> Id
> column so indexed on it. This index is creating/making lot of dirty pages.
> This also is a contributing reason for the checkpoint to take longer. How
> can
> I make changes to the index so that it would not create/make many pages
> dirty? I tried to change it to VARCHAR and there is not much difference.
> The check point is taking about 10-15 seconds and it repeats every 60
> seconds.
> Your suggestion is greatly appreciated.
> Thanks.
> Thanks.|||We have SAN disk system. Probably the HW is good enough, just trying to see
if I can rearrange some things on the database front to make some improvemen
t.
Coming to the DDL, the tables are not temporary tables the data is temporary
in the sense that the data is kept only for the current day and at the end o
f
the day they are truncated. Each table has about 20 columns. some are decima
l
fields some are datatime columns and others are integer and char type which
includes many char(1)'s and two/three columns char(15 to 20)). One of the
char(15) is indexed which is a kind of Id but is not unique there are no
relationships between these tables and other tables. No triggers no views an
d
anything as such. The data comes into to the system gets inserted to these
standalone tables using some stored procedures. And these tables are queried
using some other stored procedures. The major problem to me looks like is
because of the page splits that it is generating and the dirty pages that it
is generating(about 10000 dirty pages). Is there any thing that can be done
on the table or anything else to make things perform better?
Thanks in advance for your suggestion.
"Andrew J. Kelly" wrote:

> It would really help to show the entire DDL for the table including the
> indexes. It sounds like your disk subsystem isn't up to the task. If you
> are going to have that many transactions you need a fast disk I/O subsyste
m,
> especially for the transaction logs. Is the log file on it's own RAID 1 o
r
> RAID 10 and is the data on a RAID 10?
> --
> Andrew J. Kelly SQL MVP
>
> "Srini" <Srini@.discussions.microsoft.com> wrote in message
> news:1F6FF490-C19A-4DFE-BDBC-3AD397CC9D5A@.microsoft.com...
>
>|||Srini wrote:
> We have SAN disk system. Probably the HW is good enough, just trying
> to see if I can rearrange some things on the database front to make
> some improvement.
> Coming to the DDL, the tables are not temporary tables the data is
> temporary in the sense that the data is kept only for the current day
> and at the end of the day they are truncated. Each table has about 20
> columns. some are decimal fields some are datatime columns and others
> are integer and char type which includes many char(1)'s and two/three
> columns char(15 to 20)). One of the char(15) is indexed which is a
> kind of Id but is not unique there are no relationships between these
> tables and other tables. No triggers no views and anything as such.
> The data comes into to the system gets inserted to these standalone
> tables using some stored procedures. And these tables are queried
> using some other stored procedures. The major problem to me looks
> like is because of the page splits that it is generating and the
> dirty pages that it is generating(about 10000 dirty pages). Is there
> any thing that can be done on the table or anything else to make
> things perform better?
>
The reason it would help to see the DDL is because page splits are a
result of a clustered index and inserts that are not in clustered index
order. You can eliminate the page splitting by chaning the clustered
index to non-clustered or inserting the data in clustered index key
order (if that's possible).
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Just because it is a SAN does not mean it is sufficient or configured
properly for your application. I run across more issues SAN related simply
because people tend to ignore the configuration in thinking it can handle
what ever they need. The DDL was to see what we are dealing with and leave
nothing to imagination. It only takes a second to script the table and
indexes but it goes a long way towards letting us see what is actually
there. Not just what you may think is relevant. This is especially true for
the indexes.
Andrew J. Kelly SQL MVP
"Srini" <Srini@.discussions.microsoft.com> wrote in message
news:623BAACD-7754-4BF4-B9ED-5AA2EDC16F8B@.microsoft.com...
> We have SAN disk system. Probably the HW is good enough, just trying to
> see
> if I can rearrange some things on the database front to make some
> improvement.
> Coming to the DDL, the tables are not temporary tables the data is
> temporary
> in the sense that the data is kept only for the current day and at the end
> of
> the day they are truncated. Each table has about 20 columns. some are
> decimal
> fields some are datatime columns and others are integer and char type
> which
> includes many char(1)'s and two/three columns char(15 to 20)). One of the
> char(15) is indexed which is a kind of Id but is not unique there are no
> relationships between these tables and other tables. No triggers no views
> and
> anything as such. The data comes into to the system gets inserted to these
> standalone tables using some stored procedures. And these tables are
> queried
> using some other stored procedures. The major problem to me looks like is
> because of the page splits that it is generating and the dirty pages that
> it
> is generating(about 10000 dirty pages). Is there any thing that can be
> done
> on the table or anything else to make things perform better?
> Thanks in advance for your suggestion.
> "Andrew J. Kelly" wrote:
>|||David is correct but I just want to caution that changing the clustered
index to a nonclustered will not remove page splits. It may reduce them but
a nonclustered index is implemented just like a clustered index and can page
split as well.
Andrew J. Kelly SQL MVP
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:eC3cUzslFHA.3316@.TK2MSFTNGP14.phx.gbl...
> Srini wrote:
> The reason it would help to see the DDL is because page splits are a
> result of a clustered index and inserts that are not in clustered index
> order. You can eliminate the page splitting by chaning the clustered index
> to non-clustered or inserting the data in clustered index key order (if
> that's possible).
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com|||If we try to insert the data in clustered index order, which sounds to me
like a monotonically increasing clustered index, would n't it create hot
spots on the disk there by reducing the throughput? Currently we can't
control the data insert order. But I think I can change it so that I can
create clustered index on the serial number which is sequential and that is
generated by me so the data insertions will be in the clustered index order.
How about the dirty pages created by the other non clustered indexes? When I
run DBCC MEMUSAGE it is showing lot of dirty pages on the pages related to
the non-clustered indexes. How can I reduce the dirty pages on those? I
understand FILLFACTOR will not help here as that option is useful when there
is some data in the table and we are creating indexes on that table.
Thanks.
"David Gugick" wrote:

> Srini wrote:
> The reason it would help to see the DDL is because page splits are a
> result of a clustered index and inserts that are not in clustered index
> order. You can eliminate the page splitting by chaning the clustered
> index to non-clustered or inserting the data in clustered index key
> order (if that's possible).
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>|||Is there any limit on the number of repplies that one can post in a time
frame... This thing is not letting me post mine. Trying to POST again...
I agree SAN may have some issues we are trying to check on that. But by just
looking at the SQL server front 10000 dirty pages per checkpoint, using
default recovery interval(which is 60 seconds), looks like something can be
done there to reduce that huge number of dirty pages.
DDL looks like this:
SET ANSI_PADDING ON
GO
CREATE TABLE
[dbo].[DAILY_DATA1](
[Event_id] [char] (18) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
--This is unique identifier for the data, currently clustered index is
created on this field
[Category_cd] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Type_cd] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Session_id] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Serial_nb] [int] NOT NULL , App generated serial number, which I can use to
create clustered index
[Order_ts] [datetime] NOT NULL ,
[Data_Id_tx] [char] (14) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
--This is the column that we have index on
.
.
.
[Description_tx] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
.
.
.
[Receipt_ts] [datetime] NOT NULL
CONSTRAINT [PK_DD_1_Evt_ID] PRIMARY KEY CLUSTERED
(
[Event_id]
) ON [PRIMARY]
) ON [PRIMARY]
END
GO
CREATE INDEX [IDX_DD_1_Data_Id_tx] ON [dbo].[Daily_Data1]([Data_Id_tx])
WITH FILLFACTOR = 90 ON [PRIMARY]
GO
Thanks.
"Andrew J. Kelly" wrote:

> Just because it is a SAN does not mean it is sufficient or configured
> properly for your application. I run across more issues SAN related simply
> because people tend to ignore the configuration in thinking it can handle
> what ever they need. The DDL was to see what we are dealing with and leav
e
> nothing to imagination. It only takes a second to script the table and
> indexes but it goes a long way towards letting us see what is actually
> there. Not just what you may think is relevant. This is especially true f
or
> the indexes.
> --
> Andrew J. Kelly SQL MVP
>
> "Srini" <Srini@.discussions.microsoft.com> wrote in message
> news:623BAACD-7754-4BF4-B9ED-5AA2EDC16F8B@.microsoft.com...
>
>|||I repplied to thsi in the morning but it did not get posted...
Interval option is set to default - not changed. Default is 60 seconds. If I
increase it, it is taking way long on the checkpoint. If I decrease it
CHCKPOINT occurs too frequently. Both are problematic.
The tables are not temporary but the data is. Data gets inserted as part of
the daily operations and will be truncated in the evening. All the columns
are set to fixed length CHAR fields(to their maximum possible lenghts). Ther
e
are only two/thress columns with CHAR(15), CHAR(18) and CHAR(10) all other
columns are integer, decimal, datetime, CHAR(1) type.
I need to find a way to reduce the number of dirty pages and the number of
page splits. How can I do that?
Thanks.
"Rajesh" wrote:
> Just give a try with the following info
> 1. Check the recovery interval option on the system.
> 2. make sure that the temporary tables have fixed size by using char
> rather than
> varchar therefore you can reduce the page spilts.
> HTH
> Regards
> Rajesh Peddireddy.
> "Srini" wrote:
>

Page SPlits and finding out Record size and Page Info

I am trying to determine if pages in a table are close to full and likely to
page split.
Using DBCC SHOWCONTIG ( table) WITH TABLERESULTS, ALL_INDEXES, ALL_LEVELS
I get MinimumRecordSize, MaximumRecordSize only for the indexes How can I
get this information on the data itself? If an update is going to change a
the average record size by increasing it 15 bytes, and the avg is 500 bytes,
I would assume that there are on average 8 records per page and the increase
would push one record out, or 1 in 8, so a 25 million record table would
encounter 3,125,000 page splits. With a clustered index, I believe we have
been experiencing some bad performance issues and turning off clustering on
the index and adjusting the free space per page, hopefully will help with
the updates.
Is there anyway to reorganize the data so that it is only using say 60% of
the file pages so that updates are less likely to page split?I may have fired too soon.
My response refers to Index Organization. Not data itself.
Cheers,
GAJ

Page Restoring

In SQL Server 2005 Book on Line, it mentioned that you can restore a database by pages instead of to restore the whole database. But, it also says "Page restore is supported only for read/write filegroups." If this is true, then how about most of the database files are not designed as filegroup (or just primary filegroup only) ? Can they enjoy this convenience too? Also, when you find a suspect page in suspect_pages table in msdb, how you find out in which log_backup file that contains this bad page?

Thanks,

Charley

Hi Charley.

Any read/write filegroup can make use of page-level restores, which includes any filegroup (even the primary), so long as the given filegroup hasn't been set as read-only. All database files are always part of a filegroup, so all database files would be eligible, so long as they are included in a read/write filegroup.

As for how to determine which backup contains a suspect page, there is no way to figure that out easily from the suspect_pages table (in this case, by easily I mean there is nothing in the table that would immediately tell you it is in backup x or y). You could usually assume it would be in any backup after the last_update value from the table, so long as the backup in question contains the page in question (for example, a log backup wouldn't, nor would a diff backup if the page in question hadn't been updated from the last full to the given diff backup). Of course, it also would depend on the event_type associated with the given page as well in some situations.

Repost if still unclear,

HTH,

|||

The sequence for restoring a database page is fundamentally the same as that for restoring a database file, except that you're moving FAR less data.

You need to start with a full backup, but specify only the page you want to restore. You would then apply a differential, and all log backups up to the current time. The page MUST be rolled forward to the same point in time as the rest of the database (i.e. now). That implies that you need to be in full recovery mode. At each stage, you specify the page(s) that you want to restore.

|||

Hi Chad and Kevin,

Thanks for your reply. The first part of my question is quite clear now. The implied question behind the second part of my question is actually how practical to take advantage of the new page restoring features. From your analysis and the example from BOL, we can see the way and the syntax of page restoring is very similar to a traditional restoring except the PAGE clause. As you both mentioned, the page restoring still have to start from full backup, diff backup if any and transaction log backups. My understanding, with provided page IDs in PAGE clause, the page restoring is actually using the full backup, diff backup and all log backups that may or may not be directly related to the suspect pages for scanning where the damage occurred, but only restore the suspect pages (not the whole data file) from the related backup. If the a specific transaction log backup contains the suspect page, in order to move data far less than normal restoring, the page restoring will only restore the question page from the located log backup. Also, as Kevin mentioned, all log backup up to the current time have to be included in page restoring. Therefore, to find out which backup contains the suspect page is not really important any more. Please let me know if my understanding is correct.

Thanks,

Charley

|||

Your understanding is correct. The restore sequence needs to include all the same backups that a complete restore would, with the exception that you have the option of breaking your full/differential backups up by filegroup. In this case, you'd only need the backups for the relevant filegroup.

While you do need to touch all of the same backups, as I pointed out, the volume of data moved (and hence the time to accomplish the restore) is FAR less. Compare restoring a 1TB database to an 8k page!

Also, if you are running Enterprise edition, the entire rest of the database, including all other pages in the file being restored to, stay ONLINE during the restore. The only user that would even notice that anything was happening would be one that happened to need access to data on that one page. This is a huge win in availability!

|||

Hi Kevin,

Thanks so much for your quick response and excellent explanation. You also answered my next question about the the online restoring.

Thanks,

Charley

|||

Hi Kevin,

Sorry, I have one more question about the online restoring. In BOL, it mentioned that a database is considered to be online whenever the primary filegroup is online, even if one or more of its secondary filegroups are offline. It also points out that during an online file restore, any file being restored and its filegroup are offline. My question is if the resotre file is contained in primary filegroup will it be taken offline too? In this case, how to make the online restoring works.

Thanks,

Charley

|||

The limitation of online restores is that any time the primary filegroup is offline (which includes any file in that filegroup being offline), the database is offline.

The way to mitigate this of course, is to lay out the database with a very small primary filegroup containing critical data without which the database is useless anyway. Then in the event of a disaster recovery, you can get the core of the database up quickly, and bring up other filegroups (say historical information) at a more leisurely pace.

|||

Thanks again.

Page Orientation in Multipage report

Tweaking a report that was imported from Crystal Reports. Report has three pages, the first two are landscape, the last page is portrait.

Can this be done in RS or will I have to split up the report to render the 1st two pages separately from the last page (in two reports)?

Thanks in advance.

My two cents worth: It would really really be nice if RS had 1.) a rich text box and 2.) the ability to rotate labels and text boxes.

The page sizes in SSRS are at the report level, so you could not do that within 1 report.

It would be very nice to be able to change that at a page level.

BobP

|||

Thanks for the reply. I guess I should have added my three cents worth!

Joe

Page orientation for physical pages

Hello,
I need to design a single report (PDF) which must have both landscape and portrait orientation.

For what I know the page size is defined and static for all pages of the same report (RDL). Is there any workaround (I'm using Sql2005) ?

Thanks,
Pierre

You are right the page size defined in the rdl can't be expression and apply to all pages. One possible workaround is to use deviceinfo to specify page size and the page range. http://msdn2.microsoft.com/en-us/library/ms154682(SQL.90).aspx. For example, you can specify one page size to get page 1-3 of the report, and a different size to get a different set of pages.

|||

Hello Fang,
thanks for the reply. For what I know I can define one deviceinfo setting per report. How can I define the device info setting the page 1 with a different PageHeight and PageWidth than the page 2 and 3 on the same deviceinfo ?

Thanks,
Pierre

|||Since you can use device info settings (StartPage and EndPage) to retrieve a range of pages, you can get page 1 with certain page size, then page 2 and 3 with a different page size.

Page Numbers in Excel

Hi,
When I export to Excel and Print Preview I get page numbers like 1 of 1 even though there are 2 pages.In both pages I get 1 of 1.
What should to do to correct it.
I don't have this problem with pdf.
ThanxFrom an "Export to Excel" perspective, there is only one page (sheet). When
you actually print (which is outside of Reporting Services), you may have a
different page count.
--
Brian Welcker
Group Program Manager
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Sudha" <Sudha@.discussions.microsoft.com> wrote in message
news:755CFDD0-2980-4AD7-BDB8-DE5DB7635606@.microsoft.com...
> Hi,
> When I export to Excel and Print Preview I get page numbers like 1 of 1
> even though there are 2 pages.In both pages I get 1 of 1.
> What should to do to correct it.
> I don't have this problem with pdf.
> Thanx

Page Number problem

Hello,
I have a problem displaying the correct page numbers (or number of pages). I've inserted the page number field into the page header b, it will always shows 1/1, no matter how many pages i got. on the other hands...how do I solve this problem?
Thanks!Make sure you're using the 'Page N of M' field instead of the 'Page Number' field.
Then use the section expert to check if any sections have the 'Reset Page Number After' box ticked.|||erm...i tried it. it still didn't work.

i tried putting it into the report header, it displays the pages correctly, just that the problem is i dont want it to appear on Report Header, i wanted the page number in the Page Header...due to the way the report was designed (customer's format).

Plz advise?|||oh wait...
i works!
thanks!|||ok..now another problem
let say there are 2 page. the first page displays 1/2. but the 2nd page also displays 1/2

how to solve it? thanks~~~

page number

Hello,

How can I put a page number such as "page of pages" to my report.

Thanks,

Add a textbox with the following formula:

="Page " & Globals!PageNumber & " of " & Globals!TotalPages