Got the following error messages when rebuilding indexes on SQL Server 2000 + SP3 server.
WARNING: EC 5c166098, 2 waited 300 sec. on latch 5c1694bc. Not a BUF latch.
Waiting for type 0x2, current count 0xa, current owning EC 0x5C16A098.
Refer to KB-810195, but it should be corrected in SP3. My server has already service pack 3 installed.
Johnny
We have seen this in relation to IO problems, have you checked the windows
system event log?
"Johnny" <Johnny@.discussions.microsoft.com> wrote in message
news:087B2B6A-32A4-4C14-841D-4FE2993B8E75@.microsoft.com...
> Got the following error messages when rebuilding indexes on SQL Server
2000 + SP3 server.
> WARNING: EC 5c166098, 2 waited 300 sec. on latch 5c1694bc. Not a BUF
latch.
> Waiting for type 0x2, current count 0xa, current owning EC 0x5C16A098.
> Refer to KB-810195, but it should be corrected in SP3. My server has
already service pack 3 installed.
> Johnny
>
|||There is no errors in the system & application event log around the time of running index rebuild.
Showing posts with label sec. Show all posts
Showing posts with label sec. Show all posts
Wednesday, March 21, 2012
Parallel Index Operations
Got the following error messages when rebuilding indexes on SQL Server 2000
+ SP3 server.
WARNING: EC 5c166098, 2 waited 300 sec. on latch 5c1694bc. Not a BUF latch.
Waiting for type 0x2, current count 0xa, current owning EC 0x5C16A098.
Refer to KB-810195, but it should be corrected in SP3. My server has alread
y service pack 3 installed.
JohnnyWe have seen this in relation to IO problems, have you checked the windows
system event log?
"Johnny" <Johnny@.discussions.microsoft.com> wrote in message
news:087B2B6A-32A4-4C14-841D-4FE2993B8E75@.microsoft.com...
> Got the following error messages when rebuilding indexes on SQL Server
2000 + SP3 server.
> WARNING: EC 5c166098, 2 waited 300 sec. on latch 5c1694bc. Not a BUF
latch.
> Waiting for type 0x2, current count 0xa, current owning EC 0x5C16A098.
> Refer to KB-810195, but it should be corrected in SP3. My server has
already service pack 3 installed.
> Johnny
>|||There is no errors in the system & application event log around the time of
running index rebuild.
+ SP3 server.
WARNING: EC 5c166098, 2 waited 300 sec. on latch 5c1694bc. Not a BUF latch.
Waiting for type 0x2, current count 0xa, current owning EC 0x5C16A098.
Refer to KB-810195, but it should be corrected in SP3. My server has alread
y service pack 3 installed.
JohnnyWe have seen this in relation to IO problems, have you checked the windows
system event log?
"Johnny" <Johnny@.discussions.microsoft.com> wrote in message
news:087B2B6A-32A4-4C14-841D-4FE2993B8E75@.microsoft.com...
> Got the following error messages when rebuilding indexes on SQL Server
2000 + SP3 server.
> WARNING: EC 5c166098, 2 waited 300 sec. on latch 5c1694bc. Not a BUF
latch.
> Waiting for type 0x2, current count 0xa, current owning EC 0x5C16A098.
> Refer to KB-810195, but it should be corrected in SP3. My server has
already service pack 3 installed.
> Johnny
>|||There is no errors in the system & application event log around the time of
running index rebuild.
Parallel Index Operations
Got the following error messages when rebuilding indexes on SQL Server 2000 + SP3 server.
WARNING: EC 5c166098, 2 waited 300 sec. on latch 5c1694bc. Not a BUF latch.
Waiting for type 0x2, current count 0xa, current owning EC 0x5C16A098.
Refer to KB-810195, but it should be corrected in SP3. My server has already service pack 3 installed.
JohnnyWe have seen this in relation to IO problems, have you checked the windows
system event log?
"Johnny" <Johnny@.discussions.microsoft.com> wrote in message
news:087B2B6A-32A4-4C14-841D-4FE2993B8E75@.microsoft.com...
> Got the following error messages when rebuilding indexes on SQL Server
2000 + SP3 server.
> WARNING: EC 5c166098, 2 waited 300 sec. on latch 5c1694bc. Not a BUF
latch.
> Waiting for type 0x2, current count 0xa, current owning EC 0x5C16A098.
> Refer to KB-810195, but it should be corrected in SP3. My server has
already service pack 3 installed.
> Johnny
>|||There is no errors in the system & application event log around the time of running index rebuild.
WARNING: EC 5c166098, 2 waited 300 sec. on latch 5c1694bc. Not a BUF latch.
Waiting for type 0x2, current count 0xa, current owning EC 0x5C16A098.
Refer to KB-810195, but it should be corrected in SP3. My server has already service pack 3 installed.
JohnnyWe have seen this in relation to IO problems, have you checked the windows
system event log?
"Johnny" <Johnny@.discussions.microsoft.com> wrote in message
news:087B2B6A-32A4-4C14-841D-4FE2993B8E75@.microsoft.com...
> Got the following error messages when rebuilding indexes on SQL Server
2000 + SP3 server.
> WARNING: EC 5c166098, 2 waited 300 sec. on latch 5c1694bc. Not a BUF
latch.
> Waiting for type 0x2, current count 0xa, current owning EC 0x5C16A098.
> Refer to KB-810195, but it should be corrected in SP3. My server has
already service pack 3 installed.
> Johnny
>|||There is no errors in the system & application event log around the time of running index rebuild.
Friday, March 9, 2012
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
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
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
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
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
Saturday, February 25, 2012
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.
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.
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.
Page Splits \ sec
What is a "Bad" Number for Page Splits \sec.
I know ideally, that we would want Zero.
but when she I start being concerned ?
Thanks as usual
Greg Jackson
PDX, ORGreg
I can't find a figure anywhere for what is a bad number. However a number co
uld be missleading anyway. On a large database with lots of tables, you woul
d get a lot more page splits than on a small database with a few tables.
As it just reports how many page splits, they may be mostly on one table or
they could be spread evenly over your database. I think the number of page s
plits just on it's own is not very usefull.
Do you regularly reindex all or some of the tables on your database? If you
are worried about the effects that page splits are having on your database,
usee dbcc showcontig to see how fragmented they are. If some tebles become f
ragmented much quicker than
others ( all tables except static tables will become fragmented to a degree
over time), it might be worth increasing the fill factor. This will increase
the table size but will slow down the page splits and the fragmentation.
Hope this helps
John|||Yeah I totally am with you.
Here is the deal.
I am our Senior DB Architect\Engineer I am NOT the DBA and as such, I dont
have direct access to prod.
However, our prod DB is totally sucking wind. (Sprocs taking in excess of
100,000 ms)
WE NEVER EVER EVER defrag as our DBA insists that it is impossible without
downtime. I have pointed him to a million articles on INDEXDEFRAG, etc. He
says the articles are "Wrong". He SWEARS That he must turn off logging to
run indexdefrag cuz "The log fills up too fast". (NUTS)
ALL Of our tables are fragged bigger then crud. They ALL have 100% fill
factor in a highly transactional system (averaging 44 trnxns \sec).
Most tables are currently 87% fragged. Not sure why, but that seems to be
where fragmentation bottoms out.
I am writing scripts to drop indexes and PKEYS in some cases so we can
recreate them with fill factor settings (Anywhere from 70% to 100% depending
on the table).
Our DBA says I'm nuts and that fill factor will REDUCE performance.
He tells his boss that we have ZERO page splits in production.
I say..."How can that be possible ? Think about what you're saying" We have
100% fill factors, we have 3.8 million transactions per day, Our Database is
growing at a rate of 5GB per month, but we have ZERO Page splits ?
I run perf mon against the server and I am seeing page splits all over the
freaking place.
I need to send numbers to his boss and say "Look, this guy is not right" we
have to implement my scripts or we are going to continue to tank as data and
volume grows.
Here is data from some random samples I obtained.
Date
Average Page Splits\sec
1/27/2004
1.15282921
2/1/2004
0.606016587
2/5/2004
1.344524812
2/6/2004
1.589833663
2/20/2004
0.9
2/21/2004
0.59
2/22/2004
0.78
2/27/2004
1.82
so my question is....
based on these numbers, are these figures high enough to prove my point ?
1.5 page splits per second does not sound like a huge number to me. So It
appears to NOT prove what I "KNOW" to be happening in production.
wouldnt I expect to see thousands and thousands of splits per second if the
problem were as prolific as I know it to be ?
GAJ|||Those figures are well within acceptable limits and will not cause the type
of slowness you are indicating. While you DBA's claims in general are not
true fragmentation in an OLTP system may not be as much an issue as most
people think. If these are random I/O (not range queries) it takes as much
time and effort to read the row from the beginning of the file as the end
etc. There are certainly things the DBA can do to reduce fragmentation it
sounds like your schema / queries just need to be optimized. I can take the
best designed db on the best hardware available and write a poor query that
will bring it to it's knees. If you have a sp that is taking a long time you
should profile it and see what it is doing. The you can see where the
issues lay. Your dba should be doing this without anyone screaming at
him<g>.
Andrew J. Kelly SQL MVP
"Jaxon" <GregoryAJackson@.hotmail.com> wrote in message
news:%23A%23wrcDCEHA.3928@.TK2MSFTNGP11.phx.gbl...
> Yeah I totally am with you.
> Here is the deal.
> I am our Senior DB Architect\Engineer I am NOT the DBA and as such, I dont
> have direct access to prod.
> However, our prod DB is totally sucking wind. (Sprocs taking in excess of
> 100,000 ms)
> WE NEVER EVER EVER defrag as our DBA insists that it is impossible without
> downtime. I have pointed him to a million articles on INDEXDEFRAG, etc. He
> says the articles are "Wrong". He SWEARS That he must turn off logging to
> run indexdefrag cuz "The log fills up too fast". (NUTS)
> ALL Of our tables are fragged bigger then crud. They ALL have 100% fill
> factor in a highly transactional system (averaging 44 trnxns \sec).
> Most tables are currently 87% fragged. Not sure why, but that seems to be
> where fragmentation bottoms out.
> I am writing scripts to drop indexes and PKEYS in some cases so we can
> recreate them with fill factor settings (Anywhere from 70% to 100%
depending
> on the table).
> Our DBA says I'm nuts and that fill factor will REDUCE performance.
> He tells his boss that we have ZERO page splits in production.
> I say..."How can that be possible ? Think about what you're saying" We
have
> 100% fill factors, we have 3.8 million transactions per day, Our Database
is
> growing at a rate of 5GB per month, but we have ZERO Page splits ?
> I run perf mon against the server and I am seeing page splits all over the
> freaking place.
> I need to send numbers to his boss and say "Look, this guy is not right"
we
> have to implement my scripts or we are going to continue to tank as data
and
> volume grows.
> Here is data from some random samples I obtained.
> Date
> Average Page Splits\sec
> 1/27/2004
> 1.15282921
> 2/1/2004
> 0.606016587
> 2/5/2004
> 1.344524812
> 2/6/2004
> 1.589833663
> 2/20/2004
> 0.9
> 2/21/2004
> 0.59
> 2/22/2004
> 0.78
> 2/27/2004
> 1.82
>
> so my question is....
> based on these numbers, are these figures high enough to prove my point ?
> 1.5 page splits per second does not sound like a huge number to me. So It
> appears to NOT prove what I "KNOW" to be happening in production.
> wouldnt I expect to see thousands and thousands of splits per second if
the
> problem were as prolific as I know it to be ?
>
> GAJ
>|||Hey Andrew,
I agree with you on one thing:
1. We have sprocs that definately suck rocks....I will be rewriting many of
them soon. But this is a very large task (some will take upwards of 2 weeks
to rewrite they are nasty)
BUT see below:
1. High Frag levels require SQL Server to read more pages than well
maintained DBs so IO is excessive for reads. Is that not a true statement
(Whether sequential or not) ?
2. Also establishing fill factor settings will reduce Page Splits, hence
reducing IO Load. Is that not a true statement ?
I definately am aware that the existince of HEAPS and huge frag levels are
not our ONLY problem. I just see that issue as "Low Hanging Fruit" (easy and
quick to fix).
The next topic I have to tackle is that MOST of our FKeys are not indexes.
According to our DBA, that is NOT a problem either Even when we look at the
Query plan and see massive Table Scans (we're talking tables with Millions
of records). Again, he says we have "No Table Scans In Production" and
"Indexing the Fkeys will slow us down and I will not allow them".
Here are the perfmon stats for Full Scans:
SQLServer:Access Methods\Full Scans/sec
(Average over the past few weeks = 21.38)
My plan to improve the performance:
1. Remove Heaps from production by ensuring clustered indexes (placed
wisely) on our Important tables
2. Establish Fill Factor Settings for WRITE Intensive tables
3. Perform periodic defrag maintenance
4. Add Indexes to FKeys that are used in Joins
5. Rewrite problem sprocs (remove temp tables, cursors, etc)
I know you've been around a while and I respect your opinion so light me up
!
Thanks in advance
GAJ|||Hi,
Here are just my 2 cents worth ;-)
> 1. High Frag levels require SQL Server to read more pages than well
> maintained DBs so IO is excessive for reads. Is that not a true statement
> (Whether sequential or not) ?
High Frag levels doesn't mean that SQL Server will read more pages. However,
when doing table scans SQL Server will be bouncing all over the disk to grab
the right pages and therefore resulting in more IO. Therefore, I consider
your statement to be partially true.
> 2. Also establishing fill factor settings will reduce Page Splits, hence
> reducing IO Load. Is that not a true statement ?
Reducing the fill factor will indeed reduce page splits and therefore
increase write performance. However, because of the pages that are now
filled with air, SQL Server will need more logical IO when reading pages.
However, on my databases I've seen lots of examples where lower fill factors
increased the performance significantly without any effect on read
performance.
With regard to your plan, I would add clustered and non-clsutered indexes as
soon as possible. Therefore, making point 4 on your list priority 2.
Good luck with your DBA ;-)
HTH
Karl Gram
"Jaxon" <GregoryAJackson@.hotmail.com> wrote in message
news:uuEfqxDCEHA.1812@.TK2MSFTNGP12.phx.gbl...
> Hey Andrew,
> I agree with you on one thing:
> 1. We have sprocs that definately suck rocks....I will be rewriting many
of
> them soon. But this is a very large task (some will take upwards of 2
weeks
> to rewrite they are nasty)
> BUT see below:
> 1. High Frag levels require SQL Server to read more pages than well
> maintained DBs so IO is excessive for reads. Is that not a true statement
> (Whether sequential or not) ?
> 2. Also establishing fill factor settings will reduce Page Splits, hence
> reducing IO Load. Is that not a true statement ?
> I definately am aware that the existince of HEAPS and huge frag levels are
> not our ONLY problem. I just see that issue as "Low Hanging Fruit" (easy
and
> quick to fix).
> The next topic I have to tackle is that MOST of our FKeys are not indexes.
> According to our DBA, that is NOT a problem either Even when we look at
the
> Query plan and see massive Table Scans (we're talking tables with Millions
> of records). Again, he says we have "No Table Scans In Production" and
> "Indexing the Fkeys will slow us down and I will not allow them".
>
> Here are the perfmon stats for Full Scans:
> SQLServer:Access Methods\Full Scans/sec
> (Average over the past few weeks = 21.38)
>
> My plan to improve the performance:
> 1. Remove Heaps from production by ensuring clustered indexes (placed
> wisely) on our Important tables
> 2. Establish Fill Factor Settings for WRITE Intensive tables
> 3. Perform periodic defrag maintenance
> 4. Add Indexes to FKeys that are used in Joins
> 5. Rewrite problem sprocs (remove temp tables, cursors, etc)
>
> I know you've been around a while and I respect your opinion so light me
up
> !
>
> Thanks in advance
>
> GAJ
>|||nicely said Karl.
I agree with your semantics.
#1, 2 and 3 are all being done in one swoop. so really they are all the same
step as far as script building and execution are concerned.
The FKey Index thing is definately killing us. Any argument to the contrary
is really ludicrous. We have about half a dozen very large sprocs that are
called thousands and thousands of times a day that access these tables.
You can see in the Query plan that they are doing scans. I fix this problem
and ALL the sprocs improve dramatically.
I'll keep swingin....
thanks
GAJ|||Jaxon,
Your suggested recommendation sounds wise to me. Just watch out for log size
and available database size when you defrag.
It seems that you are in a "fight" with the DBA, and this will end up in a
political or even prestige issue. Not something I envy, because you will
never win. If you are right (and I bet a beer that you are), the DBA will
not like you as you will make him/her look foolish/incompetent. And if you
are wrong, well... you are wrong. ;-).
A first step can be to implement your suggestions on a test database and see
how much it improves performance. How you then will let the DBA know about
your results, I don't know - you have to think about that for a while.
Ideally, the DBA would think that he/she came up with the solution (possibly
with some assistance from you).
Another note, btw. Having indexes on FK's will also help when you insert and
update the referenced table. SQL Server has to do an "internal" join to
verify the reference constraint.
If you know how SQL Server work, these things aren't really that difficult,
and there is no "magic" involved. Perhaps there's only a communication
problem with the DBA?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Jaxon" <GregoryAJackson@.hotmail.com> wrote in message
news:uuEfqxDCEHA.1812@.TK2MSFTNGP12.phx.gbl...
> Hey Andrew,
> I agree with you on one thing:
> 1. We have sprocs that definately suck rocks....I will be rewriting many
of
> them soon. But this is a very large task (some will take upwards of 2
weeks
> to rewrite they are nasty)
> BUT see below:
> 1. High Frag levels require SQL Server to read more pages than well
> maintained DBs so IO is excessive for reads. Is that not a true statement
> (Whether sequential or not) ?
> 2. Also establishing fill factor settings will reduce Page Splits, hence
> reducing IO Load. Is that not a true statement ?
> I definately am aware that the existince of HEAPS and huge frag levels are
> not our ONLY problem. I just see that issue as "Low Hanging Fruit" (easy
and
> quick to fix).
> The next topic I have to tackle is that MOST of our FKeys are not indexes.
> According to our DBA, that is NOT a problem either Even when we look at
the
> Query plan and see massive Table Scans (we're talking tables with Millions
> of records). Again, he says we have "No Table Scans In Production" and
> "Indexing the Fkeys will slow us down and I will not allow them".
>
> Here are the perfmon stats for Full Scans:
> SQLServer:Access Methods\Full Scans/sec
> (Average over the past few weeks = 21.38)
>
> My plan to improve the performance:
> 1. Remove Heaps from production by ensuring clustered indexes (placed
> wisely) on our Important tables
> 2. Establish Fill Factor Settings for WRITE Intensive tables
> 3. Perform periodic defrag maintenance
> 4. Add Indexes to FKeys that are used in Joins
> 5. Rewrite problem sprocs (remove temp tables, cursors, etc)
>
> I know you've been around a while and I respect your opinion so light me
up
> !
>
> Thanks in advance
>
> GAJ
>|||yeah Tibor,
you are right.
He really "IS" a nice guy. Just politics.
He has been around for a long time and kinda walks on water around here.
Im the new guy on the block and definately am stirring up a bunch of crud
(Kinda like a rooster in his hen house).
My only goal is to improve performance. We are currently dropping anywhere
from 3% up to 30% of our requests as we cant complete them in time due to DB
Bottlenecks.
Your suggestions are wise. I am trying really hard to be positive with him,
but there comes a point where the rubber hits the road too. We are
increasing volume dramatically in production so if we dont get this fixed
fast, we are literally gonna be outta business.
Thanks again, always good to hear from you.
gaj|||Since I got in late I see you have some good answers but I just wanted to
make some of my comments clear.
Fragmentation does not mean more reads unless you are doing range type
queries. While it can mean more reads it's usually not at thelevel you
would expect when doing index seeks. Fill factors can affect the number of
pages read but should not be confused with fragmentation. Proper fill
factors can also avoid or minimize page splits but a few page splits are to
be expected. As Tibor points out an index on a FK can be a real benefit if
you have RI declared. If so and you don't have an appropriate index you can
get scans related to the RI ad not the actual WHERE clause. In general I
think your approach is solid and worth while. But since time is limited
(for most of us) you should prioritize the tasks by findingthe worst
performing queries that are used most often. That will give you the biggest
bang for the buck.
Andrew J. Kelly SQL MVP
"Jaxon" <GregoryAJackson@.hotmail.com> wrote in message
news:uuEfqxDCEHA.1812@.TK2MSFTNGP12.phx.gbl...
> Hey Andrew,
> I agree with you on one thing:
> 1. We have sprocs that definately suck rocks....I will be rewriting many
of
> them soon. But this is a very large task (some will take upwards of 2
weeks
> to rewrite they are nasty)
> BUT see below:
> 1. High Frag levels require SQL Server to read more pages than well
> maintained DBs so IO is excessive for reads. Is that not a true statement
> (Whether sequential or not) ?
> 2. Also establishing fill factor settings will reduce Page Splits, hence
> reducing IO Load. Is that not a true statement ?
> I definately am aware that the existince of HEAPS and huge frag levels are
> not our ONLY problem. I just see that issue as "Low Hanging Fruit" (easy
and
> quick to fix).
> The next topic I have to tackle is that MOST of our FKeys are not indexes.
> According to our DBA, that is NOT a problem either Even when we look at
the
> Query plan and see massive Table Scans (we're talking tables with Millions
> of records). Again, he says we have "No Table Scans In Production" and
> "Indexing the Fkeys will slow us down and I will not allow them".
>
> Here are the perfmon stats for Full Scans:
> SQLServer:Access Methods\Full Scans/sec
> (Average over the past few weeks = 21.38)
>
> My plan to improve the performance:
> 1. Remove Heaps from production by ensuring clustered indexes (placed
> wisely) on our Important tables
> 2. Establish Fill Factor Settings for WRITE Intensive tables
> 3. Perform periodic defrag maintenance
> 4. Add Indexes to FKeys that are used in Joins
> 5. Rewrite problem sprocs (remove temp tables, cursors, etc)
>
> I know you've been around a while and I respect your opinion so light me
up
> !
>
> Thanks in advance
>
> GAJ
>
I know ideally, that we would want Zero.
but when she I start being concerned ?
Thanks as usual
Greg Jackson
PDX, ORGreg
I can't find a figure anywhere for what is a bad number. However a number co
uld be missleading anyway. On a large database with lots of tables, you woul
d get a lot more page splits than on a small database with a few tables.
As it just reports how many page splits, they may be mostly on one table or
they could be spread evenly over your database. I think the number of page s
plits just on it's own is not very usefull.
Do you regularly reindex all or some of the tables on your database? If you
are worried about the effects that page splits are having on your database,
usee dbcc showcontig to see how fragmented they are. If some tebles become f
ragmented much quicker than
others ( all tables except static tables will become fragmented to a degree
over time), it might be worth increasing the fill factor. This will increase
the table size but will slow down the page splits and the fragmentation.
Hope this helps
John|||Yeah I totally am with you.
Here is the deal.
I am our Senior DB Architect\Engineer I am NOT the DBA and as such, I dont
have direct access to prod.
However, our prod DB is totally sucking wind. (Sprocs taking in excess of
100,000 ms)
WE NEVER EVER EVER defrag as our DBA insists that it is impossible without
downtime. I have pointed him to a million articles on INDEXDEFRAG, etc. He
says the articles are "Wrong". He SWEARS That he must turn off logging to
run indexdefrag cuz "The log fills up too fast". (NUTS)
ALL Of our tables are fragged bigger then crud. They ALL have 100% fill
factor in a highly transactional system (averaging 44 trnxns \sec).
Most tables are currently 87% fragged. Not sure why, but that seems to be
where fragmentation bottoms out.
I am writing scripts to drop indexes and PKEYS in some cases so we can
recreate them with fill factor settings (Anywhere from 70% to 100% depending
on the table).
Our DBA says I'm nuts and that fill factor will REDUCE performance.
He tells his boss that we have ZERO page splits in production.
I say..."How can that be possible ? Think about what you're saying" We have
100% fill factors, we have 3.8 million transactions per day, Our Database is
growing at a rate of 5GB per month, but we have ZERO Page splits ?
I run perf mon against the server and I am seeing page splits all over the
freaking place.
I need to send numbers to his boss and say "Look, this guy is not right" we
have to implement my scripts or we are going to continue to tank as data and
volume grows.
Here is data from some random samples I obtained.
Date
Average Page Splits\sec
1/27/2004
1.15282921
2/1/2004
0.606016587
2/5/2004
1.344524812
2/6/2004
1.589833663
2/20/2004
0.9
2/21/2004
0.59
2/22/2004
0.78
2/27/2004
1.82
so my question is....
based on these numbers, are these figures high enough to prove my point ?
1.5 page splits per second does not sound like a huge number to me. So It
appears to NOT prove what I "KNOW" to be happening in production.
wouldnt I expect to see thousands and thousands of splits per second if the
problem were as prolific as I know it to be ?
GAJ|||Those figures are well within acceptable limits and will not cause the type
of slowness you are indicating. While you DBA's claims in general are not
true fragmentation in an OLTP system may not be as much an issue as most
people think. If these are random I/O (not range queries) it takes as much
time and effort to read the row from the beginning of the file as the end
etc. There are certainly things the DBA can do to reduce fragmentation it
sounds like your schema / queries just need to be optimized. I can take the
best designed db on the best hardware available and write a poor query that
will bring it to it's knees. If you have a sp that is taking a long time you
should profile it and see what it is doing. The you can see where the
issues lay. Your dba should be doing this without anyone screaming at
him<g>.
Andrew J. Kelly SQL MVP
"Jaxon" <GregoryAJackson@.hotmail.com> wrote in message
news:%23A%23wrcDCEHA.3928@.TK2MSFTNGP11.phx.gbl...
> Yeah I totally am with you.
> Here is the deal.
> I am our Senior DB Architect\Engineer I am NOT the DBA and as such, I dont
> have direct access to prod.
> However, our prod DB is totally sucking wind. (Sprocs taking in excess of
> 100,000 ms)
> WE NEVER EVER EVER defrag as our DBA insists that it is impossible without
> downtime. I have pointed him to a million articles on INDEXDEFRAG, etc. He
> says the articles are "Wrong". He SWEARS That he must turn off logging to
> run indexdefrag cuz "The log fills up too fast". (NUTS)
> ALL Of our tables are fragged bigger then crud. They ALL have 100% fill
> factor in a highly transactional system (averaging 44 trnxns \sec).
> Most tables are currently 87% fragged. Not sure why, but that seems to be
> where fragmentation bottoms out.
> I am writing scripts to drop indexes and PKEYS in some cases so we can
> recreate them with fill factor settings (Anywhere from 70% to 100%
depending
> on the table).
> Our DBA says I'm nuts and that fill factor will REDUCE performance.
> He tells his boss that we have ZERO page splits in production.
> I say..."How can that be possible ? Think about what you're saying" We
have
> 100% fill factors, we have 3.8 million transactions per day, Our Database
is
> growing at a rate of 5GB per month, but we have ZERO Page splits ?
> I run perf mon against the server and I am seeing page splits all over the
> freaking place.
> I need to send numbers to his boss and say "Look, this guy is not right"
we
> have to implement my scripts or we are going to continue to tank as data
and
> volume grows.
> Here is data from some random samples I obtained.
> Date
> Average Page Splits\sec
> 1/27/2004
> 1.15282921
> 2/1/2004
> 0.606016587
> 2/5/2004
> 1.344524812
> 2/6/2004
> 1.589833663
> 2/20/2004
> 0.9
> 2/21/2004
> 0.59
> 2/22/2004
> 0.78
> 2/27/2004
> 1.82
>
> so my question is....
> based on these numbers, are these figures high enough to prove my point ?
> 1.5 page splits per second does not sound like a huge number to me. So It
> appears to NOT prove what I "KNOW" to be happening in production.
> wouldnt I expect to see thousands and thousands of splits per second if
the
> problem were as prolific as I know it to be ?
>
> GAJ
>|||Hey Andrew,
I agree with you on one thing:
1. We have sprocs that definately suck rocks....I will be rewriting many of
them soon. But this is a very large task (some will take upwards of 2 weeks
to rewrite they are nasty)
BUT see below:
1. High Frag levels require SQL Server to read more pages than well
maintained DBs so IO is excessive for reads. Is that not a true statement
(Whether sequential or not) ?
2. Also establishing fill factor settings will reduce Page Splits, hence
reducing IO Load. Is that not a true statement ?
I definately am aware that the existince of HEAPS and huge frag levels are
not our ONLY problem. I just see that issue as "Low Hanging Fruit" (easy and
quick to fix).
The next topic I have to tackle is that MOST of our FKeys are not indexes.
According to our DBA, that is NOT a problem either Even when we look at the
Query plan and see massive Table Scans (we're talking tables with Millions
of records). Again, he says we have "No Table Scans In Production" and
"Indexing the Fkeys will slow us down and I will not allow them".
Here are the perfmon stats for Full Scans:
SQLServer:Access Methods\Full Scans/sec
(Average over the past few weeks = 21.38)
My plan to improve the performance:
1. Remove Heaps from production by ensuring clustered indexes (placed
wisely) on our Important tables
2. Establish Fill Factor Settings for WRITE Intensive tables
3. Perform periodic defrag maintenance
4. Add Indexes to FKeys that are used in Joins
5. Rewrite problem sprocs (remove temp tables, cursors, etc)
I know you've been around a while and I respect your opinion so light me up
!
Thanks in advance
GAJ|||Hi,
Here are just my 2 cents worth ;-)
> 1. High Frag levels require SQL Server to read more pages than well
> maintained DBs so IO is excessive for reads. Is that not a true statement
> (Whether sequential or not) ?
High Frag levels doesn't mean that SQL Server will read more pages. However,
when doing table scans SQL Server will be bouncing all over the disk to grab
the right pages and therefore resulting in more IO. Therefore, I consider
your statement to be partially true.
> 2. Also establishing fill factor settings will reduce Page Splits, hence
> reducing IO Load. Is that not a true statement ?
Reducing the fill factor will indeed reduce page splits and therefore
increase write performance. However, because of the pages that are now
filled with air, SQL Server will need more logical IO when reading pages.
However, on my databases I've seen lots of examples where lower fill factors
increased the performance significantly without any effect on read
performance.
With regard to your plan, I would add clustered and non-clsutered indexes as
soon as possible. Therefore, making point 4 on your list priority 2.
Good luck with your DBA ;-)
HTH
Karl Gram
"Jaxon" <GregoryAJackson@.hotmail.com> wrote in message
news:uuEfqxDCEHA.1812@.TK2MSFTNGP12.phx.gbl...
> Hey Andrew,
> I agree with you on one thing:
> 1. We have sprocs that definately suck rocks....I will be rewriting many
of
> them soon. But this is a very large task (some will take upwards of 2
weeks
> to rewrite they are nasty)
> BUT see below:
> 1. High Frag levels require SQL Server to read more pages than well
> maintained DBs so IO is excessive for reads. Is that not a true statement
> (Whether sequential or not) ?
> 2. Also establishing fill factor settings will reduce Page Splits, hence
> reducing IO Load. Is that not a true statement ?
> I definately am aware that the existince of HEAPS and huge frag levels are
> not our ONLY problem. I just see that issue as "Low Hanging Fruit" (easy
and
> quick to fix).
> The next topic I have to tackle is that MOST of our FKeys are not indexes.
> According to our DBA, that is NOT a problem either Even when we look at
the
> Query plan and see massive Table Scans (we're talking tables with Millions
> of records). Again, he says we have "No Table Scans In Production" and
> "Indexing the Fkeys will slow us down and I will not allow them".
>
> Here are the perfmon stats for Full Scans:
> SQLServer:Access Methods\Full Scans/sec
> (Average over the past few weeks = 21.38)
>
> My plan to improve the performance:
> 1. Remove Heaps from production by ensuring clustered indexes (placed
> wisely) on our Important tables
> 2. Establish Fill Factor Settings for WRITE Intensive tables
> 3. Perform periodic defrag maintenance
> 4. Add Indexes to FKeys that are used in Joins
> 5. Rewrite problem sprocs (remove temp tables, cursors, etc)
>
> I know you've been around a while and I respect your opinion so light me
up
> !
>
> Thanks in advance
>
> GAJ
>|||nicely said Karl.
I agree with your semantics.
#1, 2 and 3 are all being done in one swoop. so really they are all the same
step as far as script building and execution are concerned.
The FKey Index thing is definately killing us. Any argument to the contrary
is really ludicrous. We have about half a dozen very large sprocs that are
called thousands and thousands of times a day that access these tables.
You can see in the Query plan that they are doing scans. I fix this problem
and ALL the sprocs improve dramatically.
I'll keep swingin....
thanks
GAJ|||Jaxon,
Your suggested recommendation sounds wise to me. Just watch out for log size
and available database size when you defrag.
It seems that you are in a "fight" with the DBA, and this will end up in a
political or even prestige issue. Not something I envy, because you will
never win. If you are right (and I bet a beer that you are), the DBA will
not like you as you will make him/her look foolish/incompetent. And if you
are wrong, well... you are wrong. ;-).
A first step can be to implement your suggestions on a test database and see
how much it improves performance. How you then will let the DBA know about
your results, I don't know - you have to think about that for a while.
Ideally, the DBA would think that he/she came up with the solution (possibly
with some assistance from you).
Another note, btw. Having indexes on FK's will also help when you insert and
update the referenced table. SQL Server has to do an "internal" join to
verify the reference constraint.
If you know how SQL Server work, these things aren't really that difficult,
and there is no "magic" involved. Perhaps there's only a communication
problem with the DBA?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Jaxon" <GregoryAJackson@.hotmail.com> wrote in message
news:uuEfqxDCEHA.1812@.TK2MSFTNGP12.phx.gbl...
> Hey Andrew,
> I agree with you on one thing:
> 1. We have sprocs that definately suck rocks....I will be rewriting many
of
> them soon. But this is a very large task (some will take upwards of 2
weeks
> to rewrite they are nasty)
> BUT see below:
> 1. High Frag levels require SQL Server to read more pages than well
> maintained DBs so IO is excessive for reads. Is that not a true statement
> (Whether sequential or not) ?
> 2. Also establishing fill factor settings will reduce Page Splits, hence
> reducing IO Load. Is that not a true statement ?
> I definately am aware that the existince of HEAPS and huge frag levels are
> not our ONLY problem. I just see that issue as "Low Hanging Fruit" (easy
and
> quick to fix).
> The next topic I have to tackle is that MOST of our FKeys are not indexes.
> According to our DBA, that is NOT a problem either Even when we look at
the
> Query plan and see massive Table Scans (we're talking tables with Millions
> of records). Again, he says we have "No Table Scans In Production" and
> "Indexing the Fkeys will slow us down and I will not allow them".
>
> Here are the perfmon stats for Full Scans:
> SQLServer:Access Methods\Full Scans/sec
> (Average over the past few weeks = 21.38)
>
> My plan to improve the performance:
> 1. Remove Heaps from production by ensuring clustered indexes (placed
> wisely) on our Important tables
> 2. Establish Fill Factor Settings for WRITE Intensive tables
> 3. Perform periodic defrag maintenance
> 4. Add Indexes to FKeys that are used in Joins
> 5. Rewrite problem sprocs (remove temp tables, cursors, etc)
>
> I know you've been around a while and I respect your opinion so light me
up
> !
>
> Thanks in advance
>
> GAJ
>|||yeah Tibor,
you are right.
He really "IS" a nice guy. Just politics.
He has been around for a long time and kinda walks on water around here.
Im the new guy on the block and definately am stirring up a bunch of crud
(Kinda like a rooster in his hen house).
My only goal is to improve performance. We are currently dropping anywhere
from 3% up to 30% of our requests as we cant complete them in time due to DB
Bottlenecks.
Your suggestions are wise. I am trying really hard to be positive with him,
but there comes a point where the rubber hits the road too. We are
increasing volume dramatically in production so if we dont get this fixed
fast, we are literally gonna be outta business.
Thanks again, always good to hear from you.
gaj|||Since I got in late I see you have some good answers but I just wanted to
make some of my comments clear.
Fragmentation does not mean more reads unless you are doing range type
queries. While it can mean more reads it's usually not at thelevel you
would expect when doing index seeks. Fill factors can affect the number of
pages read but should not be confused with fragmentation. Proper fill
factors can also avoid or minimize page splits but a few page splits are to
be expected. As Tibor points out an index on a FK can be a real benefit if
you have RI declared. If so and you don't have an appropriate index you can
get scans related to the RI ad not the actual WHERE clause. In general I
think your approach is solid and worth while. But since time is limited
(for most of us) you should prioritize the tasks by findingthe worst
performing queries that are used most often. That will give you the biggest
bang for the buck.
Andrew J. Kelly SQL MVP
"Jaxon" <GregoryAJackson@.hotmail.com> wrote in message
news:uuEfqxDCEHA.1812@.TK2MSFTNGP12.phx.gbl...
> Hey Andrew,
> I agree with you on one thing:
> 1. We have sprocs that definately suck rocks....I will be rewriting many
of
> them soon. But this is a very large task (some will take upwards of 2
weeks
> to rewrite they are nasty)
> BUT see below:
> 1. High Frag levels require SQL Server to read more pages than well
> maintained DBs so IO is excessive for reads. Is that not a true statement
> (Whether sequential or not) ?
> 2. Also establishing fill factor settings will reduce Page Splits, hence
> reducing IO Load. Is that not a true statement ?
> I definately am aware that the existince of HEAPS and huge frag levels are
> not our ONLY problem. I just see that issue as "Low Hanging Fruit" (easy
and
> quick to fix).
> The next topic I have to tackle is that MOST of our FKeys are not indexes.
> According to our DBA, that is NOT a problem either Even when we look at
the
> Query plan and see massive Table Scans (we're talking tables with Millions
> of records). Again, he says we have "No Table Scans In Production" and
> "Indexing the Fkeys will slow us down and I will not allow them".
>
> Here are the perfmon stats for Full Scans:
> SQLServer:Access Methods\Full Scans/sec
> (Average over the past few weeks = 21.38)
>
> My plan to improve the performance:
> 1. Remove Heaps from production by ensuring clustered indexes (placed
> wisely) on our Important tables
> 2. Establish Fill Factor Settings for WRITE Intensive tables
> 3. Perform periodic defrag maintenance
> 4. Add Indexes to FKeys that are used in Joins
> 5. Rewrite problem sprocs (remove temp tables, cursors, etc)
>
> I know you've been around a while and I respect your opinion so light me
up
> !
>
> Thanks in advance
>
> GAJ
>
Monday, February 20, 2012
Page reads/sec just shows 0
I am trying to monitor the reads and write of my SQL-Server 2000 EE with
Performance Monitor on Windows 2000 AS. I can monitor some counters of
SQLServer:BufferManager just fine (for example Buffer Cache Hit ratio),
but the counters Page reads/sec, Pageahead pages/sec, Page writes/sec,
Checkpoint pages/sec (and maybe others) are not showing just 0.
Does anyone know have to get proper data?
Thanks
Gert-Jan
never mind... it was in fact not reading or writing... This box sure has
a lot of memory!
Gert-Jan
Gert-Jan Strik wrote:
> I am trying to monitor the reads and write of my SQL-Server 2000 EE with
> Performance Monitor on Windows 2000 AS. I can monitor some counters of
> SQLServer:BufferManager just fine (for example Buffer Cache Hit ratio),
> but the counters Page reads/sec, Pageahead pages/sec, Page writes/sec,
> Checkpoint pages/sec (and maybe others) are not showing just 0.
> Does anyone know have to get proper data?
> Thanks
> Gert-Jan
Performance Monitor on Windows 2000 AS. I can monitor some counters of
SQLServer:BufferManager just fine (for example Buffer Cache Hit ratio),
but the counters Page reads/sec, Pageahead pages/sec, Page writes/sec,
Checkpoint pages/sec (and maybe others) are not showing just 0.
Does anyone know have to get proper data?
Thanks
Gert-Jan
never mind... it was in fact not reading or writing... This box sure has
a lot of memory!
Gert-Jan
Gert-Jan Strik wrote:
> I am trying to monitor the reads and write of my SQL-Server 2000 EE with
> Performance Monitor on Windows 2000 AS. I can monitor some counters of
> SQLServer:BufferManager just fine (for example Buffer Cache Hit ratio),
> but the counters Page reads/sec, Pageahead pages/sec, Page writes/sec,
> Checkpoint pages/sec (and maybe others) are not showing just 0.
> Does anyone know have to get proper data?
> Thanks
> Gert-Jan
Page reads/sec just shows 0
I am trying to monitor the reads and write of my SQL-Server 2000 EE with
Performance Monitor on Windows 2000 AS. I can monitor some counters of
SQLServer:BufferManager just fine (for example Buffer Cache Hit ratio),
but the counters Page reads/sec, Pageahead pages/sec, Page writes/sec,
Checkpoint pages/sec (and maybe others) are not showing just 0.
Does anyone know have to get proper data?
Thanks
Gert-Jannever mind... it was in fact not reading or writing... This box sure has
a lot of memory!
Gert-Jan
Gert-Jan Strik wrote:
> I am trying to monitor the reads and write of my SQL-Server 2000 EE with
> Performance Monitor on Windows 2000 AS. I can monitor some counters of
> SQLServer:BufferManager just fine (for example Buffer Cache Hit ratio),
> but the counters Page reads/sec, Pageahead pages/sec, Page writes/sec,
> Checkpoint pages/sec (and maybe others) are not showing just 0.
> Does anyone know have to get proper data?
> Thanks
> Gert-Jan
Performance Monitor on Windows 2000 AS. I can monitor some counters of
SQLServer:BufferManager just fine (for example Buffer Cache Hit ratio),
but the counters Page reads/sec, Pageahead pages/sec, Page writes/sec,
Checkpoint pages/sec (and maybe others) are not showing just 0.
Does anyone know have to get proper data?
Thanks
Gert-Jannever mind... it was in fact not reading or writing... This box sure has
a lot of memory!
Gert-Jan
Gert-Jan Strik wrote:
> I am trying to monitor the reads and write of my SQL-Server 2000 EE with
> Performance Monitor on Windows 2000 AS. I can monitor some counters of
> SQLServer:BufferManager just fine (for example Buffer Cache Hit ratio),
> but the counters Page reads/sec, Pageahead pages/sec, Page writes/sec,
> Checkpoint pages/sec (and maybe others) are not showing just 0.
> Does anyone know have to get proper data?
> Thanks
> Gert-Jan
Subscribe to:
Comments (Atom)