Is there a way we can identify most frequently hit tables in the database. We
would like to pin most frequently used tables in memory.
We have 6GB physical memory and like to allocate as 4-5 GB of memory to SQL
server.
Note: 4 or 5 of the tables have at least a million records.
Thanks in advance
Regards,
Amar
Amar
SQL Server Profiler is your tool.
"Amar" <Amar@.discussions.microsoft.com> wrote in message
news:08AF5B1E-3DE4-452B-8FF0-AF2C68EE4CDF@.microsoft.com...
> Is there a way we can identify most frequently hit tables in the database.
We
> would like to pin most frequently used tables in memory.
> We have 6GB physical memory and like to allocate as 4-5 GB of memory to
SQL
> server.
> Note: 4 or 5 of the tables have at least a million records.
> Thanks in advance
> Regards,
> Amar
|||Hi
Pinning very small tables can be argued for under extreme circumstances, but
tables with millions of rows, forget it. If you have queries that need to
process so many rows, you have a basic data architecture problem.
Pinning a table results in that memory not being available to other table
caches, so in effect, you might starve the cache and worsen performance.
SQL Server keeps track of how many times a page has been accessed, so it
will not discard heavily used pages.
Only after turning every query, and optimizing every index and table, should
you consider pinning tables. By then you would know what tables are problems.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Amar" wrote:
> Is there a way we can identify most frequently hit tables in the database. We
> would like to pin most frequently used tables in memory.
> We have 6GB physical memory and like to allocate as 4-5 GB of memory to SQL
> server.
> Note: 4 or 5 of the tables have at least a million records.
> Thanks in advance
> Regards,
> Amar
|||Hi Mike,
Thanks for your quick response. Couple of points I wanted to mention here
are we are using a product which is supplied by our supplier and hence
changing/tuning query needs to go through our QA/testing. We were looking at
this approach of pinning the table as a temporary fix while our supplier
works on tuning the queries. Would you think that is a good idea?
Other thing we noticed was our SQL is using only 1.7 GB of memory when we
have lot more unused memory. As you have said earlier we are noticing very
high cache hit at the same time we are also seeing very high disk read that
is 200M Bytes per sec.
We were looking for a quick fix as our users are finding it very difficult
to work with slow performing system which is affecting the business.
Thanks again for your help.
Regards,
Amar
"Mike Epprecht (SQL MVP)" wrote:
[vbcol=seagreen]
> Hi
> Pinning very small tables can be argued for under extreme circumstances, but
> tables with millions of rows, forget it. If you have queries that need to
> process so many rows, you have a basic data architecture problem.
> Pinning a table results in that memory not being available to other table
> caches, so in effect, you might starve the cache and worsen performance.
> SQL Server keeps track of how many times a page has been accessed, so it
> will not discard heavily used pages.
> Only after turning every query, and optimizing every index and table, should
> you consider pinning tables. By then you would know what tables are problems.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "Amar" wrote:
|||Amar wrote:
> Hi Mike,
> Thanks for your quick response. Couple of points I wanted to mention
> here are we are using a product which is supplied by our supplier and
> hence changing/tuning query needs to go through our QA/testing. We
> were looking at this approach of pinning the table as a temporary fix
> while our supplier works on tuning the queries. Would you think that
> is a good idea?
> Other thing we noticed was our SQL is using only 1.7 GB of memory
> when we have lot more unused memory. As you have said earlier we are
> noticing very high cache hit at the same time we are also seeing very
> high disk read that is 200M Bytes per sec.
> We were looking for a quick fix as our users are finding it very
> difficult to work with slow performing system which is affecting the
> business.
>
As Mike said, you can pin very small tables if necessary, but you're not
likely to get much improved performance. If you pin the large tables
(the 4 or 5 with millions of rows), and those tables fill up available
SQL Server memory, you might find performance degraded as SQL Server has
to continually grab all other data from disk. You'd need to measure how
large the tables are and weight that against the size of the remaining
table and available memory.
What server version and edition and SQL edition are you using?
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||Hi David,
Thanks for the response.
We are using SQL Enterprise edition and Windows 2003 Enterprise edition.
It is clustred sql with 2 nodes
Is there a way we can identify most frequently hit tables in the database?
"David Gugick" wrote:
> Amar wrote:
> As Mike said, you can pin very small tables if necessary, but you're not
> likely to get much improved performance. If you pin the large tables
> (the 4 or 5 with millions of rows), and those tables fill up available
> SQL Server memory, you might find performance degraded as SQL Server has
> to continually grab all other data from disk. You'd need to measure how
> large the tables are and weight that against the size of the remaining
> table and available memory.
> What server version and edition and SQL edition are you using?
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>
|||Amar wrote:
> Hi David,
> Thanks for the response.
> We are using SQL Enterprise edition and Windows 2003 Enterprise
> edition. It is clustred sql with 2 nodes
> Is there a way we can identify most frequently hit tables in the
> database?
>
"Frequently hit" is a somewhat nebulous term. Those tables that are
accessed most frequently may not be best candidates for pinning if they
are always accessed in an index optimzed fashion. Proper indexing keeps
page reads to a minimum and keeps the data cache fresh with usable data.
Conversely, a table that is accessed infrequently, but is accessed by
table scan or clustered index scan operations, can cause the data cache
to get flushed of its good data and replaced with data that has no
business being there.
So my recommendation for a temporary fix is get SQL Server to use as
much memory as possible and at the same time start performance tuning
the database. I think you'll find that looking for candidates now for
pinning is a waste of time since it's going to be time consuming and
difficult to determine the benefits.
I realize you are looking for a quick, temporary fix to performance
issues, but these problems are almost always related to query and index
design. There is little in the way of quick fixes that can help, short
of adding/utilizing all available memory.
I'm not sure if it was you that started another thread on your memory
issues, but are you running AWE and SP4. If so, have you download the
latest SP4 AWE hotfix?
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||Hi Mike and David,
Thanks a lot for quick response and all the suggestions.
Regards,
Amar
"David Gugick" wrote:
> Amar wrote:
> "Frequently hit" is a somewhat nebulous term. Those tables that are
> accessed most frequently may not be best candidates for pinning if they
> are always accessed in an index optimzed fashion. Proper indexing keeps
> page reads to a minimum and keeps the data cache fresh with usable data.
> Conversely, a table that is accessed infrequently, but is accessed by
> table scan or clustered index scan operations, can cause the data cache
> to get flushed of its good data and replaced with data that has no
> business being there.
> So my recommendation for a temporary fix is get SQL Server to use as
> much memory as possible and at the same time start performance tuning
> the database. I think you'll find that looking for candidates now for
> pinning is a waste of time since it's going to be time consuming and
> difficult to determine the benefits.
> I realize you are looking for a quick, temporary fix to performance
> issues, but these problems are almost always related to query and index
> design. There is little in the way of quick fixes that can help, short
> of adding/utilizing all available memory.
> I'm not sure if it was you that started another thread on your memory
> issues, but are you running AWE and SP4. If so, have you download the
> latest SP4 AWE hotfix?
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>
Showing posts with label identify. Show all posts
Showing posts with label identify. Show all posts
Friday, March 30, 2012
Performance turning
Is there a way we can identify most frequently hit tables in the database. We
would like to pin most frequently used tables in memory.
We have 6GB physical memory and like to allocate as 4-5 GB of memory to SQL
server.
Note: 4 or 5 of the tables have at least a million records.
Thanks in advance
Regards,
AmarAmar
SQL Server Profiler is your tool.
"Amar" <Amar@.discussions.microsoft.com> wrote in message
news:08AF5B1E-3DE4-452B-8FF0-AF2C68EE4CDF@.microsoft.com...
> Is there a way we can identify most frequently hit tables in the database.
We
> would like to pin most frequently used tables in memory.
> We have 6GB physical memory and like to allocate as 4-5 GB of memory to
SQL
> server.
> Note: 4 or 5 of the tables have at least a million records.
> Thanks in advance
> Regards,
> Amar|||Hi
Pinning very small tables can be argued for under extreme circumstances, but
tables with millions of rows, forget it. If you have queries that need to
process so many rows, you have a basic data architecture problem.
Pinning a table results in that memory not being available to other table
caches, so in effect, you might starve the cache and worsen performance.
SQL Server keeps track of how many times a page has been accessed, so it
will not discard heavily used pages.
Only after turning every query, and optimizing every index and table, should
you consider pinning tables. By then you would know what tables are problems.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Amar" wrote:
> Is there a way we can identify most frequently hit tables in the database. We
> would like to pin most frequently used tables in memory.
> We have 6GB physical memory and like to allocate as 4-5 GB of memory to SQL
> server.
> Note: 4 or 5 of the tables have at least a million records.
> Thanks in advance
> Regards,
> Amar|||Hi Mike,
Thanks for your quick response. Couple of points I wanted to mention here
are we are using a product which is supplied by our supplier and hence
changing/tuning query needs to go through our QA/testing. We were looking at
this approach of pinning the table as a temporary fix while our supplier
works on tuning the queries. Would you think that is a good idea?
Other thing we noticed was our SQL is using only 1.7 GB of memory when we
have lot more unused memory. As you have said earlier we are noticing very
high cache hit at the same time we are also seeing very high disk read that
is 200M Bytes per sec.
We were looking for a quick fix as our users are finding it very difficult
to work with slow performing system which is affecting the business.
Thanks again for your help.
Regards,
Amar
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> Pinning very small tables can be argued for under extreme circumstances, but
> tables with millions of rows, forget it. If you have queries that need to
> process so many rows, you have a basic data architecture problem.
> Pinning a table results in that memory not being available to other table
> caches, so in effect, you might starve the cache and worsen performance.
> SQL Server keeps track of how many times a page has been accessed, so it
> will not discard heavily used pages.
> Only after turning every query, and optimizing every index and table, should
> you consider pinning tables. By then you would know what tables are problems.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "Amar" wrote:
> > Is there a way we can identify most frequently hit tables in the database. We
> > would like to pin most frequently used tables in memory.
> > We have 6GB physical memory and like to allocate as 4-5 GB of memory to SQL
> > server.
> >
> > Note: 4 or 5 of the tables have at least a million records.
> >
> > Thanks in advance
> > Regards,
> > Amar|||Amar wrote:
> Hi Mike,
> Thanks for your quick response. Couple of points I wanted to mention
> here are we are using a product which is supplied by our supplier and
> hence changing/tuning query needs to go through our QA/testing. We
> were looking at this approach of pinning the table as a temporary fix
> while our supplier works on tuning the queries. Would you think that
> is a good idea?
> Other thing we noticed was our SQL is using only 1.7 GB of memory
> when we have lot more unused memory. As you have said earlier we are
> noticing very high cache hit at the same time we are also seeing very
> high disk read that is 200M Bytes per sec.
> We were looking for a quick fix as our users are finding it very
> difficult to work with slow performing system which is affecting the
> business.
>
As Mike said, you can pin very small tables if necessary, but you're not
likely to get much improved performance. If you pin the large tables
(the 4 or 5 with millions of rows), and those tables fill up available
SQL Server memory, you might find performance degraded as SQL Server has
to continually grab all other data from disk. You'd need to measure how
large the tables are and weight that against the size of the remaining
table and available memory.
What server version and edition and SQL edition are you using?
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Hi David,
Thanks for the response.
We are using SQL Enterprise edition and Windows 2003 Enterprise edition.
It is clustred sql with 2 nodes
Is there a way we can identify most frequently hit tables in the database?
"David Gugick" wrote:
> Amar wrote:
> > Hi Mike,
> > Thanks for your quick response. Couple of points I wanted to mention
> > here are we are using a product which is supplied by our supplier and
> > hence changing/tuning query needs to go through our QA/testing. We
> > were looking at this approach of pinning the table as a temporary fix
> > while our supplier works on tuning the queries. Would you think that
> > is a good idea?
> >
> > Other thing we noticed was our SQL is using only 1.7 GB of memory
> > when we have lot more unused memory. As you have said earlier we are
> > noticing very high cache hit at the same time we are also seeing very
> > high disk read that is 200M Bytes per sec.
> >
> > We were looking for a quick fix as our users are finding it very
> > difficult to work with slow performing system which is affecting the
> > business.
> >
> As Mike said, you can pin very small tables if necessary, but you're not
> likely to get much improved performance. If you pin the large tables
> (the 4 or 5 with millions of rows), and those tables fill up available
> SQL Server memory, you might find performance degraded as SQL Server has
> to continually grab all other data from disk. You'd need to measure how
> large the tables are and weight that against the size of the remaining
> table and available memory.
> What server version and edition and SQL edition are you using?
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>|||Amar wrote:
> Hi David,
> Thanks for the response.
> We are using SQL Enterprise edition and Windows 2003 Enterprise
> edition. It is clustred sql with 2 nodes
> Is there a way we can identify most frequently hit tables in the
> database?
>
"Frequently hit" is a somewhat nebulous term. Those tables that are
accessed most frequently may not be best candidates for pinning if they
are always accessed in an index optimzed fashion. Proper indexing keeps
page reads to a minimum and keeps the data cache fresh with usable data.
Conversely, a table that is accessed infrequently, but is accessed by
table scan or clustered index scan operations, can cause the data cache
to get flushed of its good data and replaced with data that has no
business being there.
So my recommendation for a temporary fix is get SQL Server to use as
much memory as possible and at the same time start performance tuning
the database. I think you'll find that looking for candidates now for
pinning is a waste of time since it's going to be time consuming and
difficult to determine the benefits.
I realize you are looking for a quick, temporary fix to performance
issues, but these problems are almost always related to query and index
design. There is little in the way of quick fixes that can help, short
of adding/utilizing all available memory.
I'm not sure if it was you that started another thread on your memory
issues, but are you running AWE and SP4. If so, have you download the
latest SP4 AWE hotfix?
--
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Hi Mike and David,
Thanks a lot for quick response and all the suggestions.
Regards,
Amar
"David Gugick" wrote:
> Amar wrote:
> > Hi David,
> > Thanks for the response.
> > We are using SQL Enterprise edition and Windows 2003 Enterprise
> > edition. It is clustred sql with 2 nodes
> > Is there a way we can identify most frequently hit tables in the
> > database?
> >
> "Frequently hit" is a somewhat nebulous term. Those tables that are
> accessed most frequently may not be best candidates for pinning if they
> are always accessed in an index optimzed fashion. Proper indexing keeps
> page reads to a minimum and keeps the data cache fresh with usable data.
> Conversely, a table that is accessed infrequently, but is accessed by
> table scan or clustered index scan operations, can cause the data cache
> to get flushed of its good data and replaced with data that has no
> business being there.
> So my recommendation for a temporary fix is get SQL Server to use as
> much memory as possible and at the same time start performance tuning
> the database. I think you'll find that looking for candidates now for
> pinning is a waste of time since it's going to be time consuming and
> difficult to determine the benefits.
> I realize you are looking for a quick, temporary fix to performance
> issues, but these problems are almost always related to query and index
> design. There is little in the way of quick fixes that can help, short
> of adding/utilizing all available memory.
> I'm not sure if it was you that started another thread on your memory
> issues, but are you running AWE and SP4. If so, have you download the
> latest SP4 AWE hotfix?
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>
would like to pin most frequently used tables in memory.
We have 6GB physical memory and like to allocate as 4-5 GB of memory to SQL
server.
Note: 4 or 5 of the tables have at least a million records.
Thanks in advance
Regards,
AmarAmar
SQL Server Profiler is your tool.
"Amar" <Amar@.discussions.microsoft.com> wrote in message
news:08AF5B1E-3DE4-452B-8FF0-AF2C68EE4CDF@.microsoft.com...
> Is there a way we can identify most frequently hit tables in the database.
We
> would like to pin most frequently used tables in memory.
> We have 6GB physical memory and like to allocate as 4-5 GB of memory to
SQL
> server.
> Note: 4 or 5 of the tables have at least a million records.
> Thanks in advance
> Regards,
> Amar|||Hi
Pinning very small tables can be argued for under extreme circumstances, but
tables with millions of rows, forget it. If you have queries that need to
process so many rows, you have a basic data architecture problem.
Pinning a table results in that memory not being available to other table
caches, so in effect, you might starve the cache and worsen performance.
SQL Server keeps track of how many times a page has been accessed, so it
will not discard heavily used pages.
Only after turning every query, and optimizing every index and table, should
you consider pinning tables. By then you would know what tables are problems.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Amar" wrote:
> Is there a way we can identify most frequently hit tables in the database. We
> would like to pin most frequently used tables in memory.
> We have 6GB physical memory and like to allocate as 4-5 GB of memory to SQL
> server.
> Note: 4 or 5 of the tables have at least a million records.
> Thanks in advance
> Regards,
> Amar|||Hi Mike,
Thanks for your quick response. Couple of points I wanted to mention here
are we are using a product which is supplied by our supplier and hence
changing/tuning query needs to go through our QA/testing. We were looking at
this approach of pinning the table as a temporary fix while our supplier
works on tuning the queries. Would you think that is a good idea?
Other thing we noticed was our SQL is using only 1.7 GB of memory when we
have lot more unused memory. As you have said earlier we are noticing very
high cache hit at the same time we are also seeing very high disk read that
is 200M Bytes per sec.
We were looking for a quick fix as our users are finding it very difficult
to work with slow performing system which is affecting the business.
Thanks again for your help.
Regards,
Amar
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> Pinning very small tables can be argued for under extreme circumstances, but
> tables with millions of rows, forget it. If you have queries that need to
> process so many rows, you have a basic data architecture problem.
> Pinning a table results in that memory not being available to other table
> caches, so in effect, you might starve the cache and worsen performance.
> SQL Server keeps track of how many times a page has been accessed, so it
> will not discard heavily used pages.
> Only after turning every query, and optimizing every index and table, should
> you consider pinning tables. By then you would know what tables are problems.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "Amar" wrote:
> > Is there a way we can identify most frequently hit tables in the database. We
> > would like to pin most frequently used tables in memory.
> > We have 6GB physical memory and like to allocate as 4-5 GB of memory to SQL
> > server.
> >
> > Note: 4 or 5 of the tables have at least a million records.
> >
> > Thanks in advance
> > Regards,
> > Amar|||Amar wrote:
> Hi Mike,
> Thanks for your quick response. Couple of points I wanted to mention
> here are we are using a product which is supplied by our supplier and
> hence changing/tuning query needs to go through our QA/testing. We
> were looking at this approach of pinning the table as a temporary fix
> while our supplier works on tuning the queries. Would you think that
> is a good idea?
> Other thing we noticed was our SQL is using only 1.7 GB of memory
> when we have lot more unused memory. As you have said earlier we are
> noticing very high cache hit at the same time we are also seeing very
> high disk read that is 200M Bytes per sec.
> We were looking for a quick fix as our users are finding it very
> difficult to work with slow performing system which is affecting the
> business.
>
As Mike said, you can pin very small tables if necessary, but you're not
likely to get much improved performance. If you pin the large tables
(the 4 or 5 with millions of rows), and those tables fill up available
SQL Server memory, you might find performance degraded as SQL Server has
to continually grab all other data from disk. You'd need to measure how
large the tables are and weight that against the size of the remaining
table and available memory.
What server version and edition and SQL edition are you using?
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Hi David,
Thanks for the response.
We are using SQL Enterprise edition and Windows 2003 Enterprise edition.
It is clustred sql with 2 nodes
Is there a way we can identify most frequently hit tables in the database?
"David Gugick" wrote:
> Amar wrote:
> > Hi Mike,
> > Thanks for your quick response. Couple of points I wanted to mention
> > here are we are using a product which is supplied by our supplier and
> > hence changing/tuning query needs to go through our QA/testing. We
> > were looking at this approach of pinning the table as a temporary fix
> > while our supplier works on tuning the queries. Would you think that
> > is a good idea?
> >
> > Other thing we noticed was our SQL is using only 1.7 GB of memory
> > when we have lot more unused memory. As you have said earlier we are
> > noticing very high cache hit at the same time we are also seeing very
> > high disk read that is 200M Bytes per sec.
> >
> > We were looking for a quick fix as our users are finding it very
> > difficult to work with slow performing system which is affecting the
> > business.
> >
> As Mike said, you can pin very small tables if necessary, but you're not
> likely to get much improved performance. If you pin the large tables
> (the 4 or 5 with millions of rows), and those tables fill up available
> SQL Server memory, you might find performance degraded as SQL Server has
> to continually grab all other data from disk. You'd need to measure how
> large the tables are and weight that against the size of the remaining
> table and available memory.
> What server version and edition and SQL edition are you using?
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>|||Amar wrote:
> Hi David,
> Thanks for the response.
> We are using SQL Enterprise edition and Windows 2003 Enterprise
> edition. It is clustred sql with 2 nodes
> Is there a way we can identify most frequently hit tables in the
> database?
>
"Frequently hit" is a somewhat nebulous term. Those tables that are
accessed most frequently may not be best candidates for pinning if they
are always accessed in an index optimzed fashion. Proper indexing keeps
page reads to a minimum and keeps the data cache fresh with usable data.
Conversely, a table that is accessed infrequently, but is accessed by
table scan or clustered index scan operations, can cause the data cache
to get flushed of its good data and replaced with data that has no
business being there.
So my recommendation for a temporary fix is get SQL Server to use as
much memory as possible and at the same time start performance tuning
the database. I think you'll find that looking for candidates now for
pinning is a waste of time since it's going to be time consuming and
difficult to determine the benefits.
I realize you are looking for a quick, temporary fix to performance
issues, but these problems are almost always related to query and index
design. There is little in the way of quick fixes that can help, short
of adding/utilizing all available memory.
I'm not sure if it was you that started another thread on your memory
issues, but are you running AWE and SP4. If so, have you download the
latest SP4 AWE hotfix?
--
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Hi Mike and David,
Thanks a lot for quick response and all the suggestions.
Regards,
Amar
"David Gugick" wrote:
> Amar wrote:
> > Hi David,
> > Thanks for the response.
> > We are using SQL Enterprise edition and Windows 2003 Enterprise
> > edition. It is clustred sql with 2 nodes
> > Is there a way we can identify most frequently hit tables in the
> > database?
> >
> "Frequently hit" is a somewhat nebulous term. Those tables that are
> accessed most frequently may not be best candidates for pinning if they
> are always accessed in an index optimzed fashion. Proper indexing keeps
> page reads to a minimum and keeps the data cache fresh with usable data.
> Conversely, a table that is accessed infrequently, but is accessed by
> table scan or clustered index scan operations, can cause the data cache
> to get flushed of its good data and replaced with data that has no
> business being there.
> So my recommendation for a temporary fix is get SQL Server to use as
> much memory as possible and at the same time start performance tuning
> the database. I think you'll find that looking for candidates now for
> pinning is a waste of time since it's going to be time consuming and
> difficult to determine the benefits.
> I realize you are looking for a quick, temporary fix to performance
> issues, but these problems are almost always related to query and index
> design. There is little in the way of quick fixes that can help, short
> of adding/utilizing all available memory.
> I'm not sure if it was you that started another thread on your memory
> issues, but are you running AWE and SP4. If so, have you download the
> latest SP4 AWE hotfix?
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>
Performance turning
Is there a way we can identify most frequently hit tables in the database. W
e
would like to pin most frequently used tables in memory.
We have 6GB physical memory and like to allocate as 4-5 GB of memory to SQL
server.
Note: 4 or 5 of the tables have at least a million records.
Thanks in advance
Regards,
AmarAmar
SQL Server Profiler is your tool.
"Amar" <Amar@.discussions.microsoft.com> wrote in message
news:08AF5B1E-3DE4-452B-8FF0-AF2C68EE4CDF@.microsoft.com...
> Is there a way we can identify most frequently hit tables in the database.
We
> would like to pin most frequently used tables in memory.
> We have 6GB physical memory and like to allocate as 4-5 GB of memory to
SQL
> server.
> Note: 4 or 5 of the tables have at least a million records.
> Thanks in advance
> Regards,
> Amar|||Hi
Pinning very small tables can be argued for under extreme circumstances, but
tables with millions of rows, forget it. If you have queries that need to
process so many rows, you have a basic data architecture problem.
Pinning a table results in that memory not being available to other table
caches, so in effect, you might starve the cache and worsen performance.
SQL Server keeps track of how many times a page has been accessed, so it
will not discard heavily used pages.
Only after turning every query, and optimizing every index and table, should
you consider pinning tables. By then you would know what tables are problems
.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Amar" wrote:
> Is there a way we can identify most frequently hit tables in the database.
We
> would like to pin most frequently used tables in memory.
> We have 6GB physical memory and like to allocate as 4-5 GB of memory to SQ
L
> server.
> Note: 4 or 5 of the tables have at least a million records.
> Thanks in advance
> Regards,
> Amar|||Hi Mike,
Thanks for your quick response. Couple of points I wanted to mention here
are we are using a product which is supplied by our supplier and hence
changing/tuning query needs to go through our QA/testing. We were looking at
this approach of pinning the table as a temporary fix while our supplier
works on tuning the queries. Would you think that is a good idea?
Other thing we noticed was our SQL is using only 1.7 GB of memory when we
have lot more unused memory. As you have said earlier we are noticing very
high cache hit at the same time we are also seeing very high disk read that
is 200M Bytes per sec.
We were looking for a quick fix as our users are finding it very difficult
to work with slow performing system which is affecting the business.
Thanks again for your help.
Regards,
Amar
"Mike Epprecht (SQL MVP)" wrote:
[vbcol=seagreen]
> Hi
> Pinning very small tables can be argued for under extreme circumstances, b
ut
> tables with millions of rows, forget it. If you have queries that need to
> process so many rows, you have a basic data architecture problem.
> Pinning a table results in that memory not being available to other table
> caches, so in effect, you might starve the cache and worsen performance.
> SQL Server keeps track of how many times a page has been accessed, so it
> will not discard heavily used pages.
> Only after turning every query, and optimizing every index and table, shou
ld
> you consider pinning tables. By then you would know what tables are proble
ms.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "Amar" wrote:
>|||Amar wrote:
> Hi Mike,
> Thanks for your quick response. Couple of points I wanted to mention
> here are we are using a product which is supplied by our supplier and
> hence changing/tuning query needs to go through our QA/testing. We
> were looking at this approach of pinning the table as a temporary fix
> while our supplier works on tuning the queries. Would you think that
> is a good idea?
> Other thing we noticed was our SQL is using only 1.7 GB of memory
> when we have lot more unused memory. As you have said earlier we are
> noticing very high cache hit at the same time we are also seeing very
> high disk read that is 200M Bytes per sec.
> We were looking for a quick fix as our users are finding it very
> difficult to work with slow performing system which is affecting the
> business.
>
As Mike said, you can pin very small tables if necessary, but you're not
likely to get much improved performance. If you pin the large tables
(the 4 or 5 with millions of rows), and those tables fill up available
SQL Server memory, you might find performance degraded as SQL Server has
to continually grab all other data from disk. You'd need to measure how
large the tables are and weight that against the size of the remaining
table and available memory.
What server version and edition and SQL edition are you using?
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Hi David,
Thanks for the response.
We are using SQL Enterprise edition and Windows 2003 Enterprise edition.
It is clustred sql with 2 nodes
Is there a way we can identify most frequently hit tables in the database?
"David Gugick" wrote:
> Amar wrote:
> As Mike said, you can pin very small tables if necessary, but you're not
> likely to get much improved performance. If you pin the large tables
> (the 4 or 5 with millions of rows), and those tables fill up available
> SQL Server memory, you might find performance degraded as SQL Server has
> to continually grab all other data from disk. You'd need to measure how
> large the tables are and weight that against the size of the remaining
> table and available memory.
> What server version and edition and SQL edition are you using?
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>|||Amar wrote:
> Hi David,
> Thanks for the response.
> We are using SQL Enterprise edition and Windows 2003 Enterprise
> edition. It is clustred sql with 2 nodes
> Is there a way we can identify most frequently hit tables in the
> database?
>
"Frequently hit" is a somewhat nebulous term. Those tables that are
accessed most frequently may not be best candidates for pinning if they
are always accessed in an index optimzed fashion. Proper indexing keeps
page reads to a minimum and keeps the data cache fresh with usable data.
Conversely, a table that is accessed infrequently, but is accessed by
table scan or clustered index scan operations, can cause the data cache
to get flushed of its good data and replaced with data that has no
business being there.
So my recommendation for a temporary fix is get SQL Server to use as
much memory as possible and at the same time start performance tuning
the database. I think you'll find that looking for candidates now for
pinning is a waste of time since it's going to be time consuming and
difficult to determine the benefits.
I realize you are looking for a quick, temporary fix to performance
issues, but these problems are almost always related to query and index
design. There is little in the way of quick fixes that can help, short
of adding/utilizing all available memory.
I'm not sure if it was you that started another thread on your memory
issues, but are you running AWE and SP4. If so, have you download the
latest SP4 AWE hotfix?
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Hi Mike and David,
Thanks a lot for quick response and all the suggestions.
Regards,
Amar
"David Gugick" wrote:
> Amar wrote:
> "Frequently hit" is a somewhat nebulous term. Those tables that are
> accessed most frequently may not be best candidates for pinning if they
> are always accessed in an index optimzed fashion. Proper indexing keeps
> page reads to a minimum and keeps the data cache fresh with usable data.
> Conversely, a table that is accessed infrequently, but is accessed by
> table scan or clustered index scan operations, can cause the data cache
> to get flushed of its good data and replaced with data that has no
> business being there.
> So my recommendation for a temporary fix is get SQL Server to use as
> much memory as possible and at the same time start performance tuning
> the database. I think you'll find that looking for candidates now for
> pinning is a waste of time since it's going to be time consuming and
> difficult to determine the benefits.
> I realize you are looking for a quick, temporary fix to performance
> issues, but these problems are almost always related to query and index
> design. There is little in the way of quick fixes that can help, short
> of adding/utilizing all available memory.
> I'm not sure if it was you that started another thread on your memory
> issues, but are you running AWE and SP4. If so, have you download the
> latest SP4 AWE hotfix?
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>
e
would like to pin most frequently used tables in memory.
We have 6GB physical memory and like to allocate as 4-5 GB of memory to SQL
server.
Note: 4 or 5 of the tables have at least a million records.
Thanks in advance
Regards,
AmarAmar
SQL Server Profiler is your tool.
"Amar" <Amar@.discussions.microsoft.com> wrote in message
news:08AF5B1E-3DE4-452B-8FF0-AF2C68EE4CDF@.microsoft.com...
> Is there a way we can identify most frequently hit tables in the database.
We
> would like to pin most frequently used tables in memory.
> We have 6GB physical memory and like to allocate as 4-5 GB of memory to
SQL
> server.
> Note: 4 or 5 of the tables have at least a million records.
> Thanks in advance
> Regards,
> Amar|||Hi
Pinning very small tables can be argued for under extreme circumstances, but
tables with millions of rows, forget it. If you have queries that need to
process so many rows, you have a basic data architecture problem.
Pinning a table results in that memory not being available to other table
caches, so in effect, you might starve the cache and worsen performance.
SQL Server keeps track of how many times a page has been accessed, so it
will not discard heavily used pages.
Only after turning every query, and optimizing every index and table, should
you consider pinning tables. By then you would know what tables are problems
.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Amar" wrote:
> Is there a way we can identify most frequently hit tables in the database.
We
> would like to pin most frequently used tables in memory.
> We have 6GB physical memory and like to allocate as 4-5 GB of memory to SQ
L
> server.
> Note: 4 or 5 of the tables have at least a million records.
> Thanks in advance
> Regards,
> Amar|||Hi Mike,
Thanks for your quick response. Couple of points I wanted to mention here
are we are using a product which is supplied by our supplier and hence
changing/tuning query needs to go through our QA/testing. We were looking at
this approach of pinning the table as a temporary fix while our supplier
works on tuning the queries. Would you think that is a good idea?
Other thing we noticed was our SQL is using only 1.7 GB of memory when we
have lot more unused memory. As you have said earlier we are noticing very
high cache hit at the same time we are also seeing very high disk read that
is 200M Bytes per sec.
We were looking for a quick fix as our users are finding it very difficult
to work with slow performing system which is affecting the business.
Thanks again for your help.
Regards,
Amar
"Mike Epprecht (SQL MVP)" wrote:
[vbcol=seagreen]
> Hi
> Pinning very small tables can be argued for under extreme circumstances, b
ut
> tables with millions of rows, forget it. If you have queries that need to
> process so many rows, you have a basic data architecture problem.
> Pinning a table results in that memory not being available to other table
> caches, so in effect, you might starve the cache and worsen performance.
> SQL Server keeps track of how many times a page has been accessed, so it
> will not discard heavily used pages.
> Only after turning every query, and optimizing every index and table, shou
ld
> you consider pinning tables. By then you would know what tables are proble
ms.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "Amar" wrote:
>|||Amar wrote:
> Hi Mike,
> Thanks for your quick response. Couple of points I wanted to mention
> here are we are using a product which is supplied by our supplier and
> hence changing/tuning query needs to go through our QA/testing. We
> were looking at this approach of pinning the table as a temporary fix
> while our supplier works on tuning the queries. Would you think that
> is a good idea?
> Other thing we noticed was our SQL is using only 1.7 GB of memory
> when we have lot more unused memory. As you have said earlier we are
> noticing very high cache hit at the same time we are also seeing very
> high disk read that is 200M Bytes per sec.
> We were looking for a quick fix as our users are finding it very
> difficult to work with slow performing system which is affecting the
> business.
>
As Mike said, you can pin very small tables if necessary, but you're not
likely to get much improved performance. If you pin the large tables
(the 4 or 5 with millions of rows), and those tables fill up available
SQL Server memory, you might find performance degraded as SQL Server has
to continually grab all other data from disk. You'd need to measure how
large the tables are and weight that against the size of the remaining
table and available memory.
What server version and edition and SQL edition are you using?
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Hi David,
Thanks for the response.
We are using SQL Enterprise edition and Windows 2003 Enterprise edition.
It is clustred sql with 2 nodes
Is there a way we can identify most frequently hit tables in the database?
"David Gugick" wrote:
> Amar wrote:
> As Mike said, you can pin very small tables if necessary, but you're not
> likely to get much improved performance. If you pin the large tables
> (the 4 or 5 with millions of rows), and those tables fill up available
> SQL Server memory, you might find performance degraded as SQL Server has
> to continually grab all other data from disk. You'd need to measure how
> large the tables are and weight that against the size of the remaining
> table and available memory.
> What server version and edition and SQL edition are you using?
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>|||Amar wrote:
> Hi David,
> Thanks for the response.
> We are using SQL Enterprise edition and Windows 2003 Enterprise
> edition. It is clustred sql with 2 nodes
> Is there a way we can identify most frequently hit tables in the
> database?
>
"Frequently hit" is a somewhat nebulous term. Those tables that are
accessed most frequently may not be best candidates for pinning if they
are always accessed in an index optimzed fashion. Proper indexing keeps
page reads to a minimum and keeps the data cache fresh with usable data.
Conversely, a table that is accessed infrequently, but is accessed by
table scan or clustered index scan operations, can cause the data cache
to get flushed of its good data and replaced with data that has no
business being there.
So my recommendation for a temporary fix is get SQL Server to use as
much memory as possible and at the same time start performance tuning
the database. I think you'll find that looking for candidates now for
pinning is a waste of time since it's going to be time consuming and
difficult to determine the benefits.
I realize you are looking for a quick, temporary fix to performance
issues, but these problems are almost always related to query and index
design. There is little in the way of quick fixes that can help, short
of adding/utilizing all available memory.
I'm not sure if it was you that started another thread on your memory
issues, but are you running AWE and SP4. If so, have you download the
latest SP4 AWE hotfix?
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Hi Mike and David,
Thanks a lot for quick response and all the suggestions.
Regards,
Amar
"David Gugick" wrote:
> Amar wrote:
> "Frequently hit" is a somewhat nebulous term. Those tables that are
> accessed most frequently may not be best candidates for pinning if they
> are always accessed in an index optimzed fashion. Proper indexing keeps
> page reads to a minimum and keeps the data cache fresh with usable data.
> Conversely, a table that is accessed infrequently, but is accessed by
> table scan or clustered index scan operations, can cause the data cache
> to get flushed of its good data and replaced with data that has no
> business being there.
> So my recommendation for a temporary fix is get SQL Server to use as
> much memory as possible and at the same time start performance tuning
> the database. I think you'll find that looking for candidates now for
> pinning is a waste of time since it's going to be time consuming and
> difficult to determine the benefits.
> I realize you are looking for a quick, temporary fix to performance
> issues, but these problems are almost always related to query and index
> design. There is little in the way of quick fixes that can help, short
> of adding/utilizing all available memory.
> I'm not sure if it was you that started another thread on your memory
> issues, but are you running AWE and SP4. If so, have you download the
> latest SP4 AWE hotfix?
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>
Tuesday, March 20, 2012
Performance problem - can't identify cause
Hi
We host a series of databases (SQL Server 2000 sp4) for customers; they are
all of identical schema. Recently, one of them became extremely slow. We
tried to find out what was wrong, but couldn't. I'll give you a quick
account of what we did in the hope that you might be able to suggest
something we overlooked.
After a bit of investigative work, we found that there were loads of locks
present and that CPU on the server was maxed out.
1 - we know that the DB schema is very optimised, so used SQL Compare to
compare this database's schema with another of our customer's in case an
index had been dropped. SQL Compare showed that the schemas were identical.
2 - we rebooted the Windows server in case there were any environmental
problems
3 - we updated the usage/statistics for the database (though these are set
to update automatically I believe) and rebuilt all the indexes.
4 - we ran a script to increase the free-space in the tables (normally set @.
10% free space - I think this script set this to 15% and forced that to
implement there and then)
5 - we ran the DBCC scripts for checking the database etc. This reported
that everything was fine.
6 - we moved the database onto another server (backup & restore) - the
performance problems exhibited themselves on this server too so we knew that
the problem (whatever it was) was specific to this database.
We then built a brand new DB using t-SQL statements and then migrated the
data from the old DB into this new one using DTS packages (only about 4 GB
of data). SQL Compare showed that the new DB has exactly the same schema as
the original DB but thankfully the new DB has NONE of the performance
problems.
So - there evidently was a problem with the database but none of the tools
we used showed up the problem. What did we miss?
Thanks everyone for any suggestions
GriffDid u Run the DBCC SHOWCONTIG or similar to check for Index
Fragmentation that could be one of the issues:
you said you recreated the schema and Copied the Data, well this is
most likely the cause of the Problem.
now these several similar databases, are they accessed in a Similar
pattern by the application, are they in a High availability scenario
etc.
Resting doubt, the TOP Issue (might) be index fragmentation:
Maninder
MCDBA
On Jan 29, 12:21 pm, "Griff" <griffithsj_...@.hotmail.com> wrote:
> Hi
> We host a series of databases (SQL Server 2000 sp4) for customers; they are
> all of identical schema. Recently, one of them became extremely slow. We
> tried to find out what was wrong, but couldn't. I'll give you a quick
> account of what we did in the hope that you might be able to suggest
> something we overlooked.
> After a bit of investigative work, we found that there were loads of locks
> present and that CPU on the server was maxed out.
> 1 - we know that the DB schema is very optimised, so used SQL Compare to
> compare this database's schema with another of our customer's in case an
> index had been dropped. SQL Compare showed that the schemas were identical.
> 2 - we rebooted the Windows server in case there were any environmental
> problems
> 3 - we updated the usage/statistics for the database (though these are set
> to update automatically I believe) and rebuilt all the indexes.
> 4 - we ran a script to increase the free-space in the tables (normally set @.
> 10% free space - I think this script set this to 15% and forced that to
> implement there and then)
> 5 - we ran the DBCC scripts for checking the database etc. This reported
> that everything was fine.
> 6 - we moved the database onto another server (backup & restore) - the
> performance problems exhibited themselves on this server too so we knew that
> the problem (whatever it was) was specific to this database.
> We then built a brand new DB using t-SQL statements and then migrated the
> data from the old DB into this new one using DTS packages (only about 4 GB
> of data). SQL Compare showed that the new DB has exactly the same schema as
> the original DB but thankfully the new DB has NONE of the performance
> problems.
> So - there evidently was a problem with the database but none of the tools
> we used showed up the problem. What did we miss?
> Thanks everyone for any suggestions
> Griff|||You've tried a bunch of different things without first identifying the cause
of the problem. Now that it's gone, it'll be pretty tough trying to figure
it out.
If this happens again, check to see what's using up the CPU resources, disk
IO activity and whether you're having memory pressure (at SQL Server and/or
OS level in case you locked SQL Server memory settings). Also, check for
blocking and recompilation activity.
Perf tuning is a lot of very invovled work.
http://msdn2.microsoft.com/en-us/sql/aa336291.aspx is a good place to start.
joe.
"Griff" <griffithsj_520@.hotmail.com> wrote in message
news:euJVgn8QHHA.4188@.TK2MSFTNGP06.phx.gbl...
> Hi
> We host a series of databases (SQL Server 2000 sp4) for customers; they
> are all of identical schema. Recently, one of them became extremely slow.
> We tried to find out what was wrong, but couldn't. I'll give you a quick
> account of what we did in the hope that you might be able to suggest
> something we overlooked.
> After a bit of investigative work, we found that there were loads of locks
> present and that CPU on the server was maxed out.
> 1 - we know that the DB schema is very optimised, so used SQL Compare to
> compare this database's schema with another of our customer's in case an
> index had been dropped. SQL Compare showed that the schemas were
> identical.
> 2 - we rebooted the Windows server in case there were any environmental
> problems
> 3 - we updated the usage/statistics for the database (though these are set
> to update automatically I believe) and rebuilt all the indexes.
> 4 - we ran a script to increase the free-space in the tables (normally set
> @. 10% free space - I think this script set this to 15% and forced that to
> implement there and then)
> 5 - we ran the DBCC scripts for checking the database etc. This reported
> that everything was fine.
> 6 - we moved the database onto another server (backup & restore) - the
> performance problems exhibited themselves on this server too so we knew
> that the problem (whatever it was) was specific to this database.
> We then built a brand new DB using t-SQL statements and then migrated the
> data from the old DB into this new one using DTS packages (only about 4 GB
> of data). SQL Compare showed that the new DB has exactly the same schema
> as the original DB but thankfully the new DB has NONE of the performance
> problems.
> So - there evidently was a problem with the database but none of the tools
> we used showed up the problem. What did we miss?
> Thanks everyone for any suggestions
> Griff
>|||Griff (griffithsj_520@.hotmail.com) writes:
> So - there evidently was a problem with the database but none of the tools
> we used showed up the problem. What did we miss?
You did a lot of things, but not the things I would have done. I would
have set up a trace, preferably server-side, and traced for statements
with a duration over some limit, say 100 ms, and then analysed that
trace to find out where the main bottlenecks are. I would then have looked
at the query plans for these queries and compared with the other databases.
I would also have looked at blocking.
My guess is that statistics for the data in some places led the optimizer
astray for one or more queries. It was resolved when you reloaded the data
through DTS, because data may have been loaded in slightly different order.
It may sound crazy, but if the optimizer has a close call between two plans,
small differences in statistics may matter.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||I don't know, Erland, if he updated the stats and rebuilt with more
free space, that should tickle the recompiles.
Remember, he said he had a lot of blocks, presumably more than the
similar systems, though these could be a secondary sign of slow
performance. So, it could just be the data, but then rebuilding it
onto a clean server wouldn't help.
Of course I agree I'd do a profiler/plan based analysis, but just
speculating, it's hard to guess what it would turn up.
So I have no idea, but I wonder if the problems will recur in short
order, if they are related to data mal-distributions causing bad
plans, or different traffic patterns.
J.
On Tue, 30 Jan 2007 08:20:08 +0000 (UTC), Erland Sommarskog
<esquel@.sommarskog.se> wrote:
>Griff (griffithsj_520@.hotmail.com) writes:
>> So - there evidently was a problem with the database but none of the tools
>> we used showed up the problem. What did we miss?
>You did a lot of things, but not the things I would have done. I would
>have set up a trace, preferably server-side, and traced for statements
>with a duration over some limit, say 100 ms, and then analysed that
>trace to find out where the main bottlenecks are. I would then have looked
>at the query plans for these queries and compared with the other databases.
>I would also have looked at blocking.
>My guess is that statistics for the data in some places led the optimizer
>astray for one or more queries. It was resolved when you reloaded the data
>through DTS, because data may have been loaded in slightly different order.
>It may sound crazy, but if the optimizer has a close call between two plans,
>small differences in statistics may matter.|||Hi Erland & "J" (and everyone else on this thread)
I did mention briefly that we performed "a bit of investigative work". This
included using the performance analyser and getting traces for all stored
procedures and any dynamic SQL. There were some very slow running
queries....
We used the index tuning wizard and it suggested no new indexes were
required. As mentioned, the schema is identical for other customers (some
much larger DBs) and they have great performance. We also checked the
execution plan for one or two queries and they was the same on our problem
database and it was on another customer's database. So, we were fairly
confident that things were okay as far as the schema was concerned.
That's when we started running out of ideas. Are there any other symptoms
that we should have checked for - things that should have shouted to us to
look at the SHOWCONTIG output?
Griff|||One place to start would be the query plans. Compare the actual
execution plans for the problematic queries from the old database and the
newly built database. This will provide clues as to what might be wrong.
Before doing this, please make sure that the stats are updated and the
databases are identical in all respects (indexes, constraints, data etc). In
SQL Server 2000, you can use the SET STATISTICS PROFILE command to get the
actual execution plan. You can also use the "Show Plan Statistics" profiler
event.
--
Umachandar Jayachandran
Microsoft SQL Server Performance Team
SQL Server Engine Team Tips Blog at
http://blogs.msdn.com/sqltips/default.aspx
This posting is provided "AS IS" with no warranties, and confers no rights.
Griff wrote on Mon, 29 Jan 2007 17:21:10 -0000:
G> Hi
G> We host a series of databases (SQL Server 2000 sp4) for customers;
G> they are all of identical schema. Recently, one of them became
G> extremely slow. We tried to find out what was wrong, but couldn't.
G> I'll give you a quick account of what we did in the hope that you
G> might be able to suggest something we overlooked.
G> After a bit of investigative work, we found that there were loads of
G> locks present and that CPU on the server was maxed out.
G> 1 - we know that the DB schema is very optimised, so used SQL Compare
G> to compare this database's schema with another of our customer's in
G> case an index had been dropped. SQL Compare showed that the schemas
G> were identical.
G> 2 - we rebooted the Windows server in case there were any
G> environmental problems
G> 3 - we updated the usage/statistics for the database (though these
G> are set to update automatically I believe) and rebuilt all the
G> indexes.
G> 4 - we ran a script to increase the free-space in the tables
G> (normally set @.
G> 10% free space - I think this script set this to 15% and forced that
G> to implement there and then)
G> 5 - we ran the DBCC scripts for checking the database etc. This
G> reported that everything was fine.
G> 6 - we moved the database onto another server (backup & restore) -
G> the performance problems exhibited themselves on this server too so
G> we knew that the problem (whatever it was) was specific to this
G> database.
G> We then built a brand new DB using t-SQL statements and then migrated
G> the data from the old DB into this new one using DTS packages (only
G> about 4 GB of data). SQL Compare showed that the new DB has exactly
G> the same schema as the original DB but thankfully the new DB has
G> NONE of the performance problems.
G> So - there evidently was a problem with the database but none of the
G> tools we used showed up the problem. What did we miss?
G> Thanks everyone for any suggestions
G> Griff|||Griff (griffithsj_520@.hotmail.com) writes:
> We used the index tuning wizard and it suggested no new indexes were
> required. As mentioned, the schema is identical for other customers (some
> much larger DBs) and they have great performance. We also checked the
> execution plan for one or two queries and they was the same on our problem
> database and it was on another customer's database. So, we were fairly
> confident that things were okay as far as the schema was concerned.
If the particular queries you looked at were slow due to blocking, it is
not surprising the plans were the same. Else it sounds very strange.
Difference in execution time is usually due to bad plans or looking. But
really bad fragmentation can play in as well.
> That's when we started running out of ideas. Are there any other symptoms
> that we should have checked for - things that should have shouted to us to
> look at the SHOWCONTIG output?
You said that you reindexed all tables, and in such case you should have
resolved such problems. Provided that all your tables has clustered indexes.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||OK. You really need to do a trace of your system and find the
slow-running queries. Make a list of them. I will bet that there's a
lot of table scanning or hashing going on. Do you have a sample of one
of the queries that you can post? Can you post the DDL of the base
tables being queried, as well as some distributions of key data? The
Index Tuning Wizard is just not something that I'd run or trust for
telling me how to run my production system...
-Dave
Griff wrote:
> Hi Erland & "J" (and everyone else on this thread)
> I did mention briefly that we performed "a bit of investigative work". This
> included using the performance analyser and getting traces for all stored
> procedures and any dynamic SQL. There were some very slow running
> queries....
> We used the index tuning wizard and it suggested no new indexes were
> required. As mentioned, the schema is identical for other customers (some
> much larger DBs) and they have great performance. We also checked the
> execution plan for one or two queries and they was the same on our problem
> database and it was on another customer's database. So, we were fairly
> confident that things were okay as far as the schema was concerned.
> That's when we started running out of ideas. Are there any other symptoms
> that we should have checked for - things that should have shouted to us to
> look at the SHOWCONTIG output?
> Griff
>
-Dave Markle
http://www.markleconsulting.com/blog|||Are you in a hyperthreading mode ? If yes, try to turn it off. See Slava
Ocks paper.
A +
Griff a écrit :
> Hi
> We host a series of databases (SQL Server 2000 sp4) for customers; they are
> all of identical schema. Recently, one of them became extremely slow. We
> tried to find out what was wrong, but couldn't. I'll give you a quick
> account of what we did in the hope that you might be able to suggest
> something we overlooked.
> After a bit of investigative work, we found that there were loads of locks
> present and that CPU on the server was maxed out.
> 1 - we know that the DB schema is very optimised, so used SQL Compare to
> compare this database's schema with another of our customer's in case an
> index had been dropped. SQL Compare showed that the schemas were identical.
> 2 - we rebooted the Windows server in case there were any environmental
> problems
> 3 - we updated the usage/statistics for the database (though these are set
> to update automatically I believe) and rebuilt all the indexes.
> 4 - we ran a script to increase the free-space in the tables (normally set @.
> 10% free space - I think this script set this to 15% and forced that to
> implement there and then)
> 5 - we ran the DBCC scripts for checking the database etc. This reported
> that everything was fine.
> 6 - we moved the database onto another server (backup & restore) - the
> performance problems exhibited themselves on this server too so we knew that
> the problem (whatever it was) was specific to this database.
> We then built a brand new DB using t-SQL statements and then migrated the
> data from the old DB into this new one using DTS packages (only about 4 GB
> of data). SQL Compare showed that the new DB has exactly the same schema as
> the original DB but thankfully the new DB has NONE of the performance
> problems.
> So - there evidently was a problem with the database but none of the tools
> we used showed up the problem. What did we miss?
> Thanks everyone for any suggestions
> Griff
>
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************
We host a series of databases (SQL Server 2000 sp4) for customers; they are
all of identical schema. Recently, one of them became extremely slow. We
tried to find out what was wrong, but couldn't. I'll give you a quick
account of what we did in the hope that you might be able to suggest
something we overlooked.
After a bit of investigative work, we found that there were loads of locks
present and that CPU on the server was maxed out.
1 - we know that the DB schema is very optimised, so used SQL Compare to
compare this database's schema with another of our customer's in case an
index had been dropped. SQL Compare showed that the schemas were identical.
2 - we rebooted the Windows server in case there were any environmental
problems
3 - we updated the usage/statistics for the database (though these are set
to update automatically I believe) and rebuilt all the indexes.
4 - we ran a script to increase the free-space in the tables (normally set @.
10% free space - I think this script set this to 15% and forced that to
implement there and then)
5 - we ran the DBCC scripts for checking the database etc. This reported
that everything was fine.
6 - we moved the database onto another server (backup & restore) - the
performance problems exhibited themselves on this server too so we knew that
the problem (whatever it was) was specific to this database.
We then built a brand new DB using t-SQL statements and then migrated the
data from the old DB into this new one using DTS packages (only about 4 GB
of data). SQL Compare showed that the new DB has exactly the same schema as
the original DB but thankfully the new DB has NONE of the performance
problems.
So - there evidently was a problem with the database but none of the tools
we used showed up the problem. What did we miss?
Thanks everyone for any suggestions
GriffDid u Run the DBCC SHOWCONTIG or similar to check for Index
Fragmentation that could be one of the issues:
you said you recreated the schema and Copied the Data, well this is
most likely the cause of the Problem.
now these several similar databases, are they accessed in a Similar
pattern by the application, are they in a High availability scenario
etc.
Resting doubt, the TOP Issue (might) be index fragmentation:
Maninder
MCDBA
On Jan 29, 12:21 pm, "Griff" <griffithsj_...@.hotmail.com> wrote:
> Hi
> We host a series of databases (SQL Server 2000 sp4) for customers; they are
> all of identical schema. Recently, one of them became extremely slow. We
> tried to find out what was wrong, but couldn't. I'll give you a quick
> account of what we did in the hope that you might be able to suggest
> something we overlooked.
> After a bit of investigative work, we found that there were loads of locks
> present and that CPU on the server was maxed out.
> 1 - we know that the DB schema is very optimised, so used SQL Compare to
> compare this database's schema with another of our customer's in case an
> index had been dropped. SQL Compare showed that the schemas were identical.
> 2 - we rebooted the Windows server in case there were any environmental
> problems
> 3 - we updated the usage/statistics for the database (though these are set
> to update automatically I believe) and rebuilt all the indexes.
> 4 - we ran a script to increase the free-space in the tables (normally set @.
> 10% free space - I think this script set this to 15% and forced that to
> implement there and then)
> 5 - we ran the DBCC scripts for checking the database etc. This reported
> that everything was fine.
> 6 - we moved the database onto another server (backup & restore) - the
> performance problems exhibited themselves on this server too so we knew that
> the problem (whatever it was) was specific to this database.
> We then built a brand new DB using t-SQL statements and then migrated the
> data from the old DB into this new one using DTS packages (only about 4 GB
> of data). SQL Compare showed that the new DB has exactly the same schema as
> the original DB but thankfully the new DB has NONE of the performance
> problems.
> So - there evidently was a problem with the database but none of the tools
> we used showed up the problem. What did we miss?
> Thanks everyone for any suggestions
> Griff|||You've tried a bunch of different things without first identifying the cause
of the problem. Now that it's gone, it'll be pretty tough trying to figure
it out.
If this happens again, check to see what's using up the CPU resources, disk
IO activity and whether you're having memory pressure (at SQL Server and/or
OS level in case you locked SQL Server memory settings). Also, check for
blocking and recompilation activity.
Perf tuning is a lot of very invovled work.
http://msdn2.microsoft.com/en-us/sql/aa336291.aspx is a good place to start.
joe.
"Griff" <griffithsj_520@.hotmail.com> wrote in message
news:euJVgn8QHHA.4188@.TK2MSFTNGP06.phx.gbl...
> Hi
> We host a series of databases (SQL Server 2000 sp4) for customers; they
> are all of identical schema. Recently, one of them became extremely slow.
> We tried to find out what was wrong, but couldn't. I'll give you a quick
> account of what we did in the hope that you might be able to suggest
> something we overlooked.
> After a bit of investigative work, we found that there were loads of locks
> present and that CPU on the server was maxed out.
> 1 - we know that the DB schema is very optimised, so used SQL Compare to
> compare this database's schema with another of our customer's in case an
> index had been dropped. SQL Compare showed that the schemas were
> identical.
> 2 - we rebooted the Windows server in case there were any environmental
> problems
> 3 - we updated the usage/statistics for the database (though these are set
> to update automatically I believe) and rebuilt all the indexes.
> 4 - we ran a script to increase the free-space in the tables (normally set
> @. 10% free space - I think this script set this to 15% and forced that to
> implement there and then)
> 5 - we ran the DBCC scripts for checking the database etc. This reported
> that everything was fine.
> 6 - we moved the database onto another server (backup & restore) - the
> performance problems exhibited themselves on this server too so we knew
> that the problem (whatever it was) was specific to this database.
> We then built a brand new DB using t-SQL statements and then migrated the
> data from the old DB into this new one using DTS packages (only about 4 GB
> of data). SQL Compare showed that the new DB has exactly the same schema
> as the original DB but thankfully the new DB has NONE of the performance
> problems.
> So - there evidently was a problem with the database but none of the tools
> we used showed up the problem. What did we miss?
> Thanks everyone for any suggestions
> Griff
>|||Griff (griffithsj_520@.hotmail.com) writes:
> So - there evidently was a problem with the database but none of the tools
> we used showed up the problem. What did we miss?
You did a lot of things, but not the things I would have done. I would
have set up a trace, preferably server-side, and traced for statements
with a duration over some limit, say 100 ms, and then analysed that
trace to find out where the main bottlenecks are. I would then have looked
at the query plans for these queries and compared with the other databases.
I would also have looked at blocking.
My guess is that statistics for the data in some places led the optimizer
astray for one or more queries. It was resolved when you reloaded the data
through DTS, because data may have been loaded in slightly different order.
It may sound crazy, but if the optimizer has a close call between two plans,
small differences in statistics may matter.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||I don't know, Erland, if he updated the stats and rebuilt with more
free space, that should tickle the recompiles.
Remember, he said he had a lot of blocks, presumably more than the
similar systems, though these could be a secondary sign of slow
performance. So, it could just be the data, but then rebuilding it
onto a clean server wouldn't help.
Of course I agree I'd do a profiler/plan based analysis, but just
speculating, it's hard to guess what it would turn up.
So I have no idea, but I wonder if the problems will recur in short
order, if they are related to data mal-distributions causing bad
plans, or different traffic patterns.
J.
On Tue, 30 Jan 2007 08:20:08 +0000 (UTC), Erland Sommarskog
<esquel@.sommarskog.se> wrote:
>Griff (griffithsj_520@.hotmail.com) writes:
>> So - there evidently was a problem with the database but none of the tools
>> we used showed up the problem. What did we miss?
>You did a lot of things, but not the things I would have done. I would
>have set up a trace, preferably server-side, and traced for statements
>with a duration over some limit, say 100 ms, and then analysed that
>trace to find out where the main bottlenecks are. I would then have looked
>at the query plans for these queries and compared with the other databases.
>I would also have looked at blocking.
>My guess is that statistics for the data in some places led the optimizer
>astray for one or more queries. It was resolved when you reloaded the data
>through DTS, because data may have been loaded in slightly different order.
>It may sound crazy, but if the optimizer has a close call between two plans,
>small differences in statistics may matter.|||Hi Erland & "J" (and everyone else on this thread)
I did mention briefly that we performed "a bit of investigative work". This
included using the performance analyser and getting traces for all stored
procedures and any dynamic SQL. There were some very slow running
queries....
We used the index tuning wizard and it suggested no new indexes were
required. As mentioned, the schema is identical for other customers (some
much larger DBs) and they have great performance. We also checked the
execution plan for one or two queries and they was the same on our problem
database and it was on another customer's database. So, we were fairly
confident that things were okay as far as the schema was concerned.
That's when we started running out of ideas. Are there any other symptoms
that we should have checked for - things that should have shouted to us to
look at the SHOWCONTIG output?
Griff|||One place to start would be the query plans. Compare the actual
execution plans for the problematic queries from the old database and the
newly built database. This will provide clues as to what might be wrong.
Before doing this, please make sure that the stats are updated and the
databases are identical in all respects (indexes, constraints, data etc). In
SQL Server 2000, you can use the SET STATISTICS PROFILE command to get the
actual execution plan. You can also use the "Show Plan Statistics" profiler
event.
--
Umachandar Jayachandran
Microsoft SQL Server Performance Team
SQL Server Engine Team Tips Blog at
http://blogs.msdn.com/sqltips/default.aspx
This posting is provided "AS IS" with no warranties, and confers no rights.
Griff wrote on Mon, 29 Jan 2007 17:21:10 -0000:
G> Hi
G> We host a series of databases (SQL Server 2000 sp4) for customers;
G> they are all of identical schema. Recently, one of them became
G> extremely slow. We tried to find out what was wrong, but couldn't.
G> I'll give you a quick account of what we did in the hope that you
G> might be able to suggest something we overlooked.
G> After a bit of investigative work, we found that there were loads of
G> locks present and that CPU on the server was maxed out.
G> 1 - we know that the DB schema is very optimised, so used SQL Compare
G> to compare this database's schema with another of our customer's in
G> case an index had been dropped. SQL Compare showed that the schemas
G> were identical.
G> 2 - we rebooted the Windows server in case there were any
G> environmental problems
G> 3 - we updated the usage/statistics for the database (though these
G> are set to update automatically I believe) and rebuilt all the
G> indexes.
G> 4 - we ran a script to increase the free-space in the tables
G> (normally set @.
G> 10% free space - I think this script set this to 15% and forced that
G> to implement there and then)
G> 5 - we ran the DBCC scripts for checking the database etc. This
G> reported that everything was fine.
G> 6 - we moved the database onto another server (backup & restore) -
G> the performance problems exhibited themselves on this server too so
G> we knew that the problem (whatever it was) was specific to this
G> database.
G> We then built a brand new DB using t-SQL statements and then migrated
G> the data from the old DB into this new one using DTS packages (only
G> about 4 GB of data). SQL Compare showed that the new DB has exactly
G> the same schema as the original DB but thankfully the new DB has
G> NONE of the performance problems.
G> So - there evidently was a problem with the database but none of the
G> tools we used showed up the problem. What did we miss?
G> Thanks everyone for any suggestions
G> Griff|||Griff (griffithsj_520@.hotmail.com) writes:
> We used the index tuning wizard and it suggested no new indexes were
> required. As mentioned, the schema is identical for other customers (some
> much larger DBs) and they have great performance. We also checked the
> execution plan for one or two queries and they was the same on our problem
> database and it was on another customer's database. So, we were fairly
> confident that things were okay as far as the schema was concerned.
If the particular queries you looked at were slow due to blocking, it is
not surprising the plans were the same. Else it sounds very strange.
Difference in execution time is usually due to bad plans or looking. But
really bad fragmentation can play in as well.
> That's when we started running out of ideas. Are there any other symptoms
> that we should have checked for - things that should have shouted to us to
> look at the SHOWCONTIG output?
You said that you reindexed all tables, and in such case you should have
resolved such problems. Provided that all your tables has clustered indexes.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||OK. You really need to do a trace of your system and find the
slow-running queries. Make a list of them. I will bet that there's a
lot of table scanning or hashing going on. Do you have a sample of one
of the queries that you can post? Can you post the DDL of the base
tables being queried, as well as some distributions of key data? The
Index Tuning Wizard is just not something that I'd run or trust for
telling me how to run my production system...
-Dave
Griff wrote:
> Hi Erland & "J" (and everyone else on this thread)
> I did mention briefly that we performed "a bit of investigative work". This
> included using the performance analyser and getting traces for all stored
> procedures and any dynamic SQL. There were some very slow running
> queries....
> We used the index tuning wizard and it suggested no new indexes were
> required. As mentioned, the schema is identical for other customers (some
> much larger DBs) and they have great performance. We also checked the
> execution plan for one or two queries and they was the same on our problem
> database and it was on another customer's database. So, we were fairly
> confident that things were okay as far as the schema was concerned.
> That's when we started running out of ideas. Are there any other symptoms
> that we should have checked for - things that should have shouted to us to
> look at the SHOWCONTIG output?
> Griff
>
-Dave Markle
http://www.markleconsulting.com/blog|||Are you in a hyperthreading mode ? If yes, try to turn it off. See Slava
Ocks paper.
A +
Griff a écrit :
> Hi
> We host a series of databases (SQL Server 2000 sp4) for customers; they are
> all of identical schema. Recently, one of them became extremely slow. We
> tried to find out what was wrong, but couldn't. I'll give you a quick
> account of what we did in the hope that you might be able to suggest
> something we overlooked.
> After a bit of investigative work, we found that there were loads of locks
> present and that CPU on the server was maxed out.
> 1 - we know that the DB schema is very optimised, so used SQL Compare to
> compare this database's schema with another of our customer's in case an
> index had been dropped. SQL Compare showed that the schemas were identical.
> 2 - we rebooted the Windows server in case there were any environmental
> problems
> 3 - we updated the usage/statistics for the database (though these are set
> to update automatically I believe) and rebuilt all the indexes.
> 4 - we ran a script to increase the free-space in the tables (normally set @.
> 10% free space - I think this script set this to 15% and forced that to
> implement there and then)
> 5 - we ran the DBCC scripts for checking the database etc. This reported
> that everything was fine.
> 6 - we moved the database onto another server (backup & restore) - the
> performance problems exhibited themselves on this server too so we knew that
> the problem (whatever it was) was specific to this database.
> We then built a brand new DB using t-SQL statements and then migrated the
> data from the old DB into this new one using DTS packages (only about 4 GB
> of data). SQL Compare showed that the new DB has exactly the same schema as
> the original DB but thankfully the new DB has NONE of the performance
> problems.
> So - there evidently was a problem with the database but none of the tools
> we used showed up the problem. What did we miss?
> Thanks everyone for any suggestions
> Griff
>
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************
Saturday, February 25, 2012
Performance monitoring specific database
I have a server with over a hundred databases, let's say I want to identify
which databases are the most active and which ones use the more resources
(CPU, IO and memory). What counters or at what data should I look at?
I don't need anything very precise, simply an overall view and I will then
look more precisely at individual databases.
ThanksIf you are using sql2005 check out the dynamic management views in BOL. If
2000, your easiest bet would be to purchase a third-party monitoring tool.
You could set up a profiler run and perform some statistical analysis off of
the results, or store snapshots of sysprocesses over time and analyze that
perhaps.
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"olig" <olig@.discussions.microsoft.com> wrote in message
news:1C1EF508-97B3-48C8-8A12-EB20540D9D3E@.microsoft.com...
>I have a server with over a hundred databases, let's say I want to identify
> which databases are the most active and which ones use the more resources
> (CPU, IO and memory). What counters or at what data should I look at?
> I don't need anything very precise, simply an overall view and I will then
> look more precisely at individual databases.
> Thanks|||Maybe this could help you to get started
select *
from sys.dm_os_performance_counters
where object_name = 'SQLServer:Databases'
and counter_name = 'Transactions/sec'
order by cntr_value desc
sys.dm_os_performance_counters is a DMV on SQL Server 2005. You should use
the sysperfinfo system table for SQL Server 2000. In any case change
object_name for named instances.
Then you can use some other DMVs or tools to focus on specific databases.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"olig" wrote:
> I have a server with over a hundred databases, let's say I want to identify
> which databases are the most active and which ones use the more resources
> (CPU, IO and memory). What counters or at what data should I look at?
> I don't need anything very precise, simply an overall view and I will then
> look more precisely at individual databases.
> Thanks|||I'm using sql2005 and I know about dynamic management views but I don't
really know which one to look at. I think most of the data show of snapshot
of what is going on right now, for example the list of query executing in
each database. Are any of them cumulative? E.g. number of requests executed
in a database since last server restart of something like that.
One possible way I am thinking using the System Monitor is to start a
counter log and log some counters to a database for a period of time (let's
say an hour). I could then query that table to get some average or maximum
values.
I guess I could monitor the SQLServer:Databases Transaction/sec counter, but
I'm not sure that the number of transaction is that much a good indicator of
activity in a database. Can I have the equivalent of SQLServer:SQL Statistics
Batch Requests/sec but for a single database?
Thanks
"TheSQLGuru" wrote:
> If you are using sql2005 check out the dynamic management views in BOL. If
> 2000, your easiest bet would be to purchase a third-party monitoring tool.
> You could set up a profiler run and perform some statistical analysis off of
> the results, or store snapshots of sysprocesses over time and analyze that
> perhaps.
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
>
> "olig" <olig@.discussions.microsoft.com> wrote in message
> news:1C1EF508-97B3-48C8-8A12-EB20540D9D3E@.microsoft.com...
> >I have a server with over a hundred databases, let's say I want to identify
> > which databases are the most active and which ones use the more resources
> > (CPU, IO and memory). What counters or at what data should I look at?
> >
> > I don't need anything very precise, simply an overall view and I will then
> > look more precisely at individual databases.
> >
> > Thanks
>
>|||... just be aware that the "per sec" counters doesn't reflect per second values in the dynamic
management view. They are accumulative. So to get a per sec value, you need to know a prior value
and the number of seconds elapsed since you captured that prior value.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
news:D33709DC-EE6D-4CBE-A197-CBD9C0F50B5D@.microsoft.com...
> Maybe this could help you to get started
> select *
> from sys.dm_os_performance_counters
> where object_name = 'SQLServer:Databases'
> and counter_name = 'Transactions/sec'
> order by cntr_value desc
> sys.dm_os_performance_counters is a DMV on SQL Server 2005. You should use
> the sysperfinfo system table for SQL Server 2000. In any case change
> object_name for named instances.
> Then you can use some other DMVs or tools to focus on specific databases.
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "olig" wrote:
>> I have a server with over a hundred databases, let's say I want to identify
>> which databases are the most active and which ones use the more resources
>> (CPU, IO and memory). What counters or at what data should I look at?
>> I don't need anything very precise, simply an overall view and I will then
>> look more precisely at individual databases.
>> Thanks|||You can do something like this:
select db_name(qp.dbid)
, sum(total_worker_time) as CPU
, sum(total_elapsed_time) as Duration
, sum(total_logical_reads+total_physical_reads) as IO
from sys.dm_exec_query_stats as qs
cross apply sys.dm_exec_query_plan(qs.plan_handle) as qp
group by db_name(qp.dbid)
order by 3 desc
If you have memory pressure or a plan reuse problem a trace would probably
be better.
select db_name(databaseid), sum(cpu), sum(reads), sum(duration)
from YourTrace
group by db_name(databaseid),
order by 2 desc
Jason Massie
Web: http://statisticsio.com
RSS: http://statisticsio.com/Home/tabid/36/rssid/1/Default.aspx
"olig" <olig@.discussions.microsoft.com> wrote in message
news:1C1EF508-97B3-48C8-8A12-EB20540D9D3E@.microsoft.com...
>I have a server with over a hundred databases, let's say I want to identify
> which databases are the most active and which ones use the more resources
> (CPU, IO and memory). What counters or at what data should I look at?
> I don't need anything very precise, simply an overall view and I will then
> look more precisely at individual databases.
> Thanks|||I have a SQL Server DBA Dashboard that has some of those things you are
looking for. You can download the dashboard from website below.
--
If you are looking for SQL Server examples or a free SQL Server DBA
Dashboard tool check out my Website at http://www.sqlserverexamples.com
"olig" wrote:
> I have a server with over a hundred databases, let's say I want to identify
> which databases are the most active and which ones use the more resources
> (CPU, IO and memory). What counters or at what data should I look at?
> I don't need anything very precise, simply an overall view and I will then
> look more precisely at individual databases.
> Thanks
which databases are the most active and which ones use the more resources
(CPU, IO and memory). What counters or at what data should I look at?
I don't need anything very precise, simply an overall view and I will then
look more precisely at individual databases.
ThanksIf you are using sql2005 check out the dynamic management views in BOL. If
2000, your easiest bet would be to purchase a third-party monitoring tool.
You could set up a profiler run and perform some statistical analysis off of
the results, or store snapshots of sysprocesses over time and analyze that
perhaps.
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"olig" <olig@.discussions.microsoft.com> wrote in message
news:1C1EF508-97B3-48C8-8A12-EB20540D9D3E@.microsoft.com...
>I have a server with over a hundred databases, let's say I want to identify
> which databases are the most active and which ones use the more resources
> (CPU, IO and memory). What counters or at what data should I look at?
> I don't need anything very precise, simply an overall view and I will then
> look more precisely at individual databases.
> Thanks|||Maybe this could help you to get started
select *
from sys.dm_os_performance_counters
where object_name = 'SQLServer:Databases'
and counter_name = 'Transactions/sec'
order by cntr_value desc
sys.dm_os_performance_counters is a DMV on SQL Server 2005. You should use
the sysperfinfo system table for SQL Server 2000. In any case change
object_name for named instances.
Then you can use some other DMVs or tools to focus on specific databases.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"olig" wrote:
> I have a server with over a hundred databases, let's say I want to identify
> which databases are the most active and which ones use the more resources
> (CPU, IO and memory). What counters or at what data should I look at?
> I don't need anything very precise, simply an overall view and I will then
> look more precisely at individual databases.
> Thanks|||I'm using sql2005 and I know about dynamic management views but I don't
really know which one to look at. I think most of the data show of snapshot
of what is going on right now, for example the list of query executing in
each database. Are any of them cumulative? E.g. number of requests executed
in a database since last server restart of something like that.
One possible way I am thinking using the System Monitor is to start a
counter log and log some counters to a database for a period of time (let's
say an hour). I could then query that table to get some average or maximum
values.
I guess I could monitor the SQLServer:Databases Transaction/sec counter, but
I'm not sure that the number of transaction is that much a good indicator of
activity in a database. Can I have the equivalent of SQLServer:SQL Statistics
Batch Requests/sec but for a single database?
Thanks
"TheSQLGuru" wrote:
> If you are using sql2005 check out the dynamic management views in BOL. If
> 2000, your easiest bet would be to purchase a third-party monitoring tool.
> You could set up a profiler run and perform some statistical analysis off of
> the results, or store snapshots of sysprocesses over time and analyze that
> perhaps.
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
>
> "olig" <olig@.discussions.microsoft.com> wrote in message
> news:1C1EF508-97B3-48C8-8A12-EB20540D9D3E@.microsoft.com...
> >I have a server with over a hundred databases, let's say I want to identify
> > which databases are the most active and which ones use the more resources
> > (CPU, IO and memory). What counters or at what data should I look at?
> >
> > I don't need anything very precise, simply an overall view and I will then
> > look more precisely at individual databases.
> >
> > Thanks
>
>|||... just be aware that the "per sec" counters doesn't reflect per second values in the dynamic
management view. They are accumulative. So to get a per sec value, you need to know a prior value
and the number of seconds elapsed since you captured that prior value.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
news:D33709DC-EE6D-4CBE-A197-CBD9C0F50B5D@.microsoft.com...
> Maybe this could help you to get started
> select *
> from sys.dm_os_performance_counters
> where object_name = 'SQLServer:Databases'
> and counter_name = 'Transactions/sec'
> order by cntr_value desc
> sys.dm_os_performance_counters is a DMV on SQL Server 2005. You should use
> the sysperfinfo system table for SQL Server 2000. In any case change
> object_name for named instances.
> Then you can use some other DMVs or tools to focus on specific databases.
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "olig" wrote:
>> I have a server with over a hundred databases, let's say I want to identify
>> which databases are the most active and which ones use the more resources
>> (CPU, IO and memory). What counters or at what data should I look at?
>> I don't need anything very precise, simply an overall view and I will then
>> look more precisely at individual databases.
>> Thanks|||You can do something like this:
select db_name(qp.dbid)
, sum(total_worker_time) as CPU
, sum(total_elapsed_time) as Duration
, sum(total_logical_reads+total_physical_reads) as IO
from sys.dm_exec_query_stats as qs
cross apply sys.dm_exec_query_plan(qs.plan_handle) as qp
group by db_name(qp.dbid)
order by 3 desc
If you have memory pressure or a plan reuse problem a trace would probably
be better.
select db_name(databaseid), sum(cpu), sum(reads), sum(duration)
from YourTrace
group by db_name(databaseid),
order by 2 desc
Jason Massie
Web: http://statisticsio.com
RSS: http://statisticsio.com/Home/tabid/36/rssid/1/Default.aspx
"olig" <olig@.discussions.microsoft.com> wrote in message
news:1C1EF508-97B3-48C8-8A12-EB20540D9D3E@.microsoft.com...
>I have a server with over a hundred databases, let's say I want to identify
> which databases are the most active and which ones use the more resources
> (CPU, IO and memory). What counters or at what data should I look at?
> I don't need anything very precise, simply an overall view and I will then
> look more precisely at individual databases.
> Thanks|||I have a SQL Server DBA Dashboard that has some of those things you are
looking for. You can download the dashboard from website below.
--
If you are looking for SQL Server examples or a free SQL Server DBA
Dashboard tool check out my Website at http://www.sqlserverexamples.com
"olig" wrote:
> I have a server with over a hundred databases, let's say I want to identify
> which databases are the most active and which ones use the more resources
> (CPU, IO and memory). What counters or at what data should I look at?
> I don't need anything very precise, simply an overall view and I will then
> look more precisely at individual databases.
> Thanks
Subscribe to:
Posts (Atom)