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 frequently. Show all posts
Showing posts with label frequently. 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
>
Performance Tuning
I am a SQL DBA newbie. I like to tune a database that I
know very little about. Other than creating indexes on
the tables that are frequently used, and rebuilding them
occasionally, can you list the different tuning tasks I
need to do please. Thank you.
Dianemonitor your slowest sprocs and tune them individually. There is a template
in SQL Profiler that will catch sproc duration statistics for you.
check peformance counters (IO, RAM, CPU, Network) for bottlenecks
dont know what to fix if you dont know what is broken.
Greg Jackson
PDX, Oregon|||Thank you.
>--Original Message--
>monitor your slowest sprocs and tune them individually.
There is a template
>in SQL Profiler that will catch sproc duration statistics
for you.
>
>check peformance counters (IO, RAM, CPU, Network) for
bottlenecks
>dont know what to fix if you dont know what is broken.
>
>Greg Jackson
>PDX, Oregon
>
>.
>|||Here are some links that may help:
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/maintain/operate/opsguide/default.asp
http://www.microsoft.com/sql/techinfo/administration/2000/perftuning.asp
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.com/sql_server_performance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.com/best_sql_server_performance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=q224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_perfmon_24u1.asp
Disk Monitoring
Andrew J. Kelly
SQL Server MVP
"Diane Siona" <anonymous@.discussions.microsoft.com> wrote in message
news:abeb01c3ec26$6f7ab150$a301280a@.phx.gbl...
> I am a SQL DBA newbie. I like to tune a database that I
> know very little about. Other than creating indexes on
> the tables that are frequently used, and rebuilding them
> occasionally, can you list the different tuning tasks I
> need to do please. Thank you.
> Diane
know very little about. Other than creating indexes on
the tables that are frequently used, and rebuilding them
occasionally, can you list the different tuning tasks I
need to do please. Thank you.
Dianemonitor your slowest sprocs and tune them individually. There is a template
in SQL Profiler that will catch sproc duration statistics for you.
check peformance counters (IO, RAM, CPU, Network) for bottlenecks
dont know what to fix if you dont know what is broken.
Greg Jackson
PDX, Oregon|||Thank you.
>--Original Message--
>monitor your slowest sprocs and tune them individually.
There is a template
>in SQL Profiler that will catch sproc duration statistics
for you.
>
>check peformance counters (IO, RAM, CPU, Network) for
bottlenecks
>dont know what to fix if you dont know what is broken.
>
>Greg Jackson
>PDX, Oregon
>
>.
>|||Here are some links that may help:
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/maintain/operate/opsguide/default.asp
http://www.microsoft.com/sql/techinfo/administration/2000/perftuning.asp
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.com/sql_server_performance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.com/best_sql_server_performance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=q224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_perfmon_24u1.asp
Disk Monitoring
Andrew J. Kelly
SQL Server MVP
"Diane Siona" <anonymous@.discussions.microsoft.com> wrote in message
news:abeb01c3ec26$6f7ab150$a301280a@.phx.gbl...
> I am a SQL DBA newbie. I like to tune a database that I
> know very little about. Other than creating indexes on
> the tables that are frequently used, and rebuilding them
> occasionally, can you list the different tuning tasks I
> need to do please. Thank you.
> Diane
Performance Tuning
I am a SQL DBA newbie. I like to tune a database that I
know very little about. Other than creating indexes on
the tables that are frequently used, and rebuilding them
occasionally, can you list the different tuning tasks I
need to do please. Thank you.
Dianemonitor your slowest sprocs and tune them individually. There is a template
in SQL Profiler that will catch sproc duration statistics for you.
check peformance counters (IO, RAM, CPU, Network) for bottlenecks
dont know what to fix if you dont know what is broken.
Greg Jackson
PDX, Oregon|||Thank you.
>--Original Message--
>monitor your slowest sprocs and tune them individually.
There is a template
>in SQL Profiler that will catch sproc duration statistics
for you.
>
>check peformance counters (IO, RAM, CPU, Network) for
bottlenecks
>dont know what to fix if you dont know what is broken.
>
>Greg Jackson
>PDX, Oregon
>
>.
>|||Here are some links that may help:
http://www.microsoft.com/technet/tr...ide/default.asp
http://www.microsoft.com/sql/techin.../perftuning.asp
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.c...mance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.c...rmance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=q224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/d.../>
on_24u1.asp
Disk Monitoring
Andrew J. Kelly
SQL Server MVP
"Diane Siona" <anonymous@.discussions.microsoft.com> wrote in message
news:abeb01c3ec26$6f7ab150$a301280a@.phx.gbl...
> I am a SQL DBA newbie. I like to tune a database that I
> know very little about. Other than creating indexes on
> the tables that are frequently used, and rebuilding them
> occasionally, can you list the different tuning tasks I
> need to do please. Thank you.
> Dianesql
know very little about. Other than creating indexes on
the tables that are frequently used, and rebuilding them
occasionally, can you list the different tuning tasks I
need to do please. Thank you.
Dianemonitor your slowest sprocs and tune them individually. There is a template
in SQL Profiler that will catch sproc duration statistics for you.
check peformance counters (IO, RAM, CPU, Network) for bottlenecks
dont know what to fix if you dont know what is broken.
Greg Jackson
PDX, Oregon|||Thank you.
>--Original Message--
>monitor your slowest sprocs and tune them individually.
There is a template
>in SQL Profiler that will catch sproc duration statistics
for you.
>
>check peformance counters (IO, RAM, CPU, Network) for
bottlenecks
>dont know what to fix if you dont know what is broken.
>
>Greg Jackson
>PDX, Oregon
>
>.
>|||Here are some links that may help:
http://www.microsoft.com/technet/tr...ide/default.asp
http://www.microsoft.com/sql/techin.../perftuning.asp
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.c...mance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.c...rmance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=q224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/d.../>
on_24u1.asp
Disk Monitoring
Andrew J. Kelly
SQL Server MVP
"Diane Siona" <anonymous@.discussions.microsoft.com> wrote in message
news:abeb01c3ec26$6f7ab150$a301280a@.phx.gbl...
> I am a SQL DBA newbie. I like to tune a database that I
> know very little about. Other than creating indexes on
> the tables that are frequently used, and rebuilding them
> occasionally, can you list the different tuning tasks I
> need to do please. Thank you.
> Dianesql
performance trace
Does anyone know how to track the actual execution plan the server uses? I
have one query designed for a frequently run report that normally takes about
3 seconds to return. However, the Profiler logs show me that in certain
cases, it took more than 3 minutes to return and it cost a huge amount of CPU
time. However, when I test it manually, it always returns in less than 3
seconds and nothing wrong in the execution plan. I am wondering that at
runtime, the server might decide to choose a different plan, especially when
the report is called by multiple users at the same time. If I can capture the
execution plan at runtime, I might be able to explain why the performance can
vary so dramatically, and find a way to tune it.
Another thing, not sure if it is related or not. I am using table hint to
direct the server to use one specific index in order to speed it up. Because
I noticed if I don't, server sometimes choose a cluster index scan which is
too slow and costly. So, if I use table hint in the query and several
instances of it are called concurrently, is there any performance concerns?
You can run a trace with a filter for that particular sp and include the
showplan event. That way you can see each time it is called what it is
actually using for a plan. The only real performance concern with a hint is
that you never let SQL Server determine what the best way may be.
Conditions and data can change and what may be OK today may not be tomorrow.
If you need to specify a hint it is possible that the query can be rewritten
so that the optimizer always chooses the right plan.
Andrew J. Kelly SQL MVP
"Joseph" <Joseph@.discussions.microsoft.com> wrote in message
news:CD9BE668-ADFD-4960-BAF9-CF31A026B074@.microsoft.com...
> Does anyone know how to track the actual execution plan the server uses? I
> have one query designed for a frequently run report that normally takes
> about
> 3 seconds to return. However, the Profiler logs show me that in certain
> cases, it took more than 3 minutes to return and it cost a huge amount of
> CPU
> time. However, when I test it manually, it always returns in less than 3
> seconds and nothing wrong in the execution plan. I am wondering that at
> runtime, the server might decide to choose a different plan, especially
> when
> the report is called by multiple users at the same time. If I can capture
> the
> execution plan at runtime, I might be able to explain why the performance
> can
> vary so dramatically, and find a way to tune it.
> Another thing, not sure if it is related or not. I am using table hint to
> direct the server to use one specific index in order to speed it up.
> Because
> I noticed if I don't, server sometimes choose a cluster index scan which
> is
> too slow and costly. So, if I use table hint in the query and several
> instances of it are called concurrently, is there any performance
> concerns?
>
|||Thanks, Andrew, for the tip. However, when I include the 'Execution Plan'
event, it does not matter if I set up a filter for that specific SP or not,
it shows all execution plans for all queries running on the server. I tried
to include 'Show Plan Text', but the capture the text data does not show any
details. Any idea?
"Andrew J. Kelly" wrote:
> You can run a trace with a filter for that particular sp and include the
> showplan event. That way you can see each time it is called what it is
> actually using for a plan. The only real performance concern with a hint is
> that you never let SQL Server determine what the best way may be.
> Conditions and data can change and what may be OK today may not be tomorrow.
> If you need to specify a hint it is possible that the query can be rewritten
> so that the optimizer always chooses the right plan.
> --
> Andrew J. Kelly SQL MVP
>
> "Joseph" <Joseph@.discussions.microsoft.com> wrote in message
> news:CD9BE668-ADFD-4960-BAF9-CF31A026B074@.microsoft.com...
>
>
|||Joseph wrote:
> Thanks, Andrew, for the tip. However, when I include the 'Execution
> Plan' event, it does not matter if I set up a filter for that
> specific SP or not, it shows all execution plans for all queries
> running on the server. I tried to include 'Show Plan Text', but the
> capture the text data does not show any details. Any idea?
>
Execution Plan requires only TextData. All other execucution plan events
require the BinaryData column as well. To filter on the procedure, use
the ObjectID. ObjectName does not filter on SP-related events. In any
case, any event you select for the trace that does not map to the
ObjectID column shows up. Try and stick to the
SP:Starting/Completed/StmtStarting/StmtCompleted events to avoid
excessive trace output.
David Gugick
Quest Software
www.imceda.com
www.quest.com
have one query designed for a frequently run report that normally takes about
3 seconds to return. However, the Profiler logs show me that in certain
cases, it took more than 3 minutes to return and it cost a huge amount of CPU
time. However, when I test it manually, it always returns in less than 3
seconds and nothing wrong in the execution plan. I am wondering that at
runtime, the server might decide to choose a different plan, especially when
the report is called by multiple users at the same time. If I can capture the
execution plan at runtime, I might be able to explain why the performance can
vary so dramatically, and find a way to tune it.
Another thing, not sure if it is related or not. I am using table hint to
direct the server to use one specific index in order to speed it up. Because
I noticed if I don't, server sometimes choose a cluster index scan which is
too slow and costly. So, if I use table hint in the query and several
instances of it are called concurrently, is there any performance concerns?
You can run a trace with a filter for that particular sp and include the
showplan event. That way you can see each time it is called what it is
actually using for a plan. The only real performance concern with a hint is
that you never let SQL Server determine what the best way may be.
Conditions and data can change and what may be OK today may not be tomorrow.
If you need to specify a hint it is possible that the query can be rewritten
so that the optimizer always chooses the right plan.
Andrew J. Kelly SQL MVP
"Joseph" <Joseph@.discussions.microsoft.com> wrote in message
news:CD9BE668-ADFD-4960-BAF9-CF31A026B074@.microsoft.com...
> Does anyone know how to track the actual execution plan the server uses? I
> have one query designed for a frequently run report that normally takes
> about
> 3 seconds to return. However, the Profiler logs show me that in certain
> cases, it took more than 3 minutes to return and it cost a huge amount of
> CPU
> time. However, when I test it manually, it always returns in less than 3
> seconds and nothing wrong in the execution plan. I am wondering that at
> runtime, the server might decide to choose a different plan, especially
> when
> the report is called by multiple users at the same time. If I can capture
> the
> execution plan at runtime, I might be able to explain why the performance
> can
> vary so dramatically, and find a way to tune it.
> Another thing, not sure if it is related or not. I am using table hint to
> direct the server to use one specific index in order to speed it up.
> Because
> I noticed if I don't, server sometimes choose a cluster index scan which
> is
> too slow and costly. So, if I use table hint in the query and several
> instances of it are called concurrently, is there any performance
> concerns?
>
|||Thanks, Andrew, for the tip. However, when I include the 'Execution Plan'
event, it does not matter if I set up a filter for that specific SP or not,
it shows all execution plans for all queries running on the server. I tried
to include 'Show Plan Text', but the capture the text data does not show any
details. Any idea?
"Andrew J. Kelly" wrote:
> You can run a trace with a filter for that particular sp and include the
> showplan event. That way you can see each time it is called what it is
> actually using for a plan. The only real performance concern with a hint is
> that you never let SQL Server determine what the best way may be.
> Conditions and data can change and what may be OK today may not be tomorrow.
> If you need to specify a hint it is possible that the query can be rewritten
> so that the optimizer always chooses the right plan.
> --
> Andrew J. Kelly SQL MVP
>
> "Joseph" <Joseph@.discussions.microsoft.com> wrote in message
> news:CD9BE668-ADFD-4960-BAF9-CF31A026B074@.microsoft.com...
>
>
|||Joseph wrote:
> Thanks, Andrew, for the tip. However, when I include the 'Execution
> Plan' event, it does not matter if I set up a filter for that
> specific SP or not, it shows all execution plans for all queries
> running on the server. I tried to include 'Show Plan Text', but the
> capture the text data does not show any details. Any idea?
>
Execution Plan requires only TextData. All other execucution plan events
require the BinaryData column as well. To filter on the procedure, use
the ObjectID. ObjectName does not filter on SP-related events. In any
case, any event you select for the trace that does not map to the
ObjectID column shows up. Try and stick to the
SP:Starting/Completed/StmtStarting/StmtCompleted events to avoid
excessive trace output.
David Gugick
Quest Software
www.imceda.com
www.quest.com
performance trace
Does anyone know how to track the actual execution plan the server uses? I
have one query designed for a frequently run report that normally takes about
3 seconds to return. However, the Profiler logs show me that in certain
cases, it took more than 3 minutes to return and it cost a huge amount of CPU
time. However, when I test it manually, it always returns in less than 3
seconds and nothing wrong in the execution plan. I am wondering that at
runtime, the server might decide to choose a different plan, especially when
the report is called by multiple users at the same time. If I can capture the
execution plan at runtime, I might be able to explain why the performance can
vary so dramatically, and find a way to tune it.
Another thing, not sure if it is related or not. I am using table hint to
direct the server to use one specific index in order to speed it up. Because
I noticed if I don't, server sometimes choose a cluster index scan which is
too slow and costly. So, if I use table hint in the query and several
instances of it are called concurrently, is there any performance concerns?You can run a trace with a filter for that particular sp and include the
showplan event. That way you can see each time it is called what it is
actually using for a plan. The only real performance concern with a hint is
that you never let SQL Server determine what the best way may be.
Conditions and data can change and what may be OK today may not be tomorrow.
If you need to specify a hint it is possible that the query can be rewritten
so that the optimizer always chooses the right plan.
--
Andrew J. Kelly SQL MVP
"Joseph" <Joseph@.discussions.microsoft.com> wrote in message
news:CD9BE668-ADFD-4960-BAF9-CF31A026B074@.microsoft.com...
> Does anyone know how to track the actual execution plan the server uses? I
> have one query designed for a frequently run report that normally takes
> about
> 3 seconds to return. However, the Profiler logs show me that in certain
> cases, it took more than 3 minutes to return and it cost a huge amount of
> CPU
> time. However, when I test it manually, it always returns in less than 3
> seconds and nothing wrong in the execution plan. I am wondering that at
> runtime, the server might decide to choose a different plan, especially
> when
> the report is called by multiple users at the same time. If I can capture
> the
> execution plan at runtime, I might be able to explain why the performance
> can
> vary so dramatically, and find a way to tune it.
> Another thing, not sure if it is related or not. I am using table hint to
> direct the server to use one specific index in order to speed it up.
> Because
> I noticed if I don't, server sometimes choose a cluster index scan which
> is
> too slow and costly. So, if I use table hint in the query and several
> instances of it are called concurrently, is there any performance
> concerns?
>|||Thanks, Andrew, for the tip. However, when I include the 'Execution Plan'
event, it does not matter if I set up a filter for that specific SP or not,
it shows all execution plans for all queries running on the server. I tried
to include 'Show Plan Text', but the capture the text data does not show any
details. Any idea?
"Andrew J. Kelly" wrote:
> You can run a trace with a filter for that particular sp and include the
> showplan event. That way you can see each time it is called what it is
> actually using for a plan. The only real performance concern with a hint is
> that you never let SQL Server determine what the best way may be.
> Conditions and data can change and what may be OK today may not be tomorrow.
> If you need to specify a hint it is possible that the query can be rewritten
> so that the optimizer always chooses the right plan.
> --
> Andrew J. Kelly SQL MVP
>
> "Joseph" <Joseph@.discussions.microsoft.com> wrote in message
> news:CD9BE668-ADFD-4960-BAF9-CF31A026B074@.microsoft.com...
> > Does anyone know how to track the actual execution plan the server uses? I
> > have one query designed for a frequently run report that normally takes
> > about
> > 3 seconds to return. However, the Profiler logs show me that in certain
> > cases, it took more than 3 minutes to return and it cost a huge amount of
> > CPU
> > time. However, when I test it manually, it always returns in less than 3
> > seconds and nothing wrong in the execution plan. I am wondering that at
> > runtime, the server might decide to choose a different plan, especially
> > when
> > the report is called by multiple users at the same time. If I can capture
> > the
> > execution plan at runtime, I might be able to explain why the performance
> > can
> > vary so dramatically, and find a way to tune it.
> >
> > Another thing, not sure if it is related or not. I am using table hint to
> > direct the server to use one specific index in order to speed it up.
> > Because
> > I noticed if I don't, server sometimes choose a cluster index scan which
> > is
> > too slow and costly. So, if I use table hint in the query and several
> > instances of it are called concurrently, is there any performance
> > concerns?
> >
> >
>
>|||Joseph wrote:
> Thanks, Andrew, for the tip. However, when I include the 'Execution
> Plan' event, it does not matter if I set up a filter for that
> specific SP or not, it shows all execution plans for all queries
> running on the server. I tried to include 'Show Plan Text', but the
> capture the text data does not show any details. Any idea?
>
Execution Plan requires only TextData. All other execucution plan events
require the BinaryData column as well. To filter on the procedure, use
the ObjectID. ObjectName does not filter on SP-related events. In any
case, any event you select for the trace that does not map to the
ObjectID column shows up. Try and stick to the
SP:Starting/Completed/StmtStarting/StmtCompleted events to avoid
excessive trace output.
--
David Gugick
Quest Software
www.imceda.com
www.quest.com
have one query designed for a frequently run report that normally takes about
3 seconds to return. However, the Profiler logs show me that in certain
cases, it took more than 3 minutes to return and it cost a huge amount of CPU
time. However, when I test it manually, it always returns in less than 3
seconds and nothing wrong in the execution plan. I am wondering that at
runtime, the server might decide to choose a different plan, especially when
the report is called by multiple users at the same time. If I can capture the
execution plan at runtime, I might be able to explain why the performance can
vary so dramatically, and find a way to tune it.
Another thing, not sure if it is related or not. I am using table hint to
direct the server to use one specific index in order to speed it up. Because
I noticed if I don't, server sometimes choose a cluster index scan which is
too slow and costly. So, if I use table hint in the query and several
instances of it are called concurrently, is there any performance concerns?You can run a trace with a filter for that particular sp and include the
showplan event. That way you can see each time it is called what it is
actually using for a plan. The only real performance concern with a hint is
that you never let SQL Server determine what the best way may be.
Conditions and data can change and what may be OK today may not be tomorrow.
If you need to specify a hint it is possible that the query can be rewritten
so that the optimizer always chooses the right plan.
--
Andrew J. Kelly SQL MVP
"Joseph" <Joseph@.discussions.microsoft.com> wrote in message
news:CD9BE668-ADFD-4960-BAF9-CF31A026B074@.microsoft.com...
> Does anyone know how to track the actual execution plan the server uses? I
> have one query designed for a frequently run report that normally takes
> about
> 3 seconds to return. However, the Profiler logs show me that in certain
> cases, it took more than 3 minutes to return and it cost a huge amount of
> CPU
> time. However, when I test it manually, it always returns in less than 3
> seconds and nothing wrong in the execution plan. I am wondering that at
> runtime, the server might decide to choose a different plan, especially
> when
> the report is called by multiple users at the same time. If I can capture
> the
> execution plan at runtime, I might be able to explain why the performance
> can
> vary so dramatically, and find a way to tune it.
> Another thing, not sure if it is related or not. I am using table hint to
> direct the server to use one specific index in order to speed it up.
> Because
> I noticed if I don't, server sometimes choose a cluster index scan which
> is
> too slow and costly. So, if I use table hint in the query and several
> instances of it are called concurrently, is there any performance
> concerns?
>|||Thanks, Andrew, for the tip. However, when I include the 'Execution Plan'
event, it does not matter if I set up a filter for that specific SP or not,
it shows all execution plans for all queries running on the server. I tried
to include 'Show Plan Text', but the capture the text data does not show any
details. Any idea?
"Andrew J. Kelly" wrote:
> You can run a trace with a filter for that particular sp and include the
> showplan event. That way you can see each time it is called what it is
> actually using for a plan. The only real performance concern with a hint is
> that you never let SQL Server determine what the best way may be.
> Conditions and data can change and what may be OK today may not be tomorrow.
> If you need to specify a hint it is possible that the query can be rewritten
> so that the optimizer always chooses the right plan.
> --
> Andrew J. Kelly SQL MVP
>
> "Joseph" <Joseph@.discussions.microsoft.com> wrote in message
> news:CD9BE668-ADFD-4960-BAF9-CF31A026B074@.microsoft.com...
> > Does anyone know how to track the actual execution plan the server uses? I
> > have one query designed for a frequently run report that normally takes
> > about
> > 3 seconds to return. However, the Profiler logs show me that in certain
> > cases, it took more than 3 minutes to return and it cost a huge amount of
> > CPU
> > time. However, when I test it manually, it always returns in less than 3
> > seconds and nothing wrong in the execution plan. I am wondering that at
> > runtime, the server might decide to choose a different plan, especially
> > when
> > the report is called by multiple users at the same time. If I can capture
> > the
> > execution plan at runtime, I might be able to explain why the performance
> > can
> > vary so dramatically, and find a way to tune it.
> >
> > Another thing, not sure if it is related or not. I am using table hint to
> > direct the server to use one specific index in order to speed it up.
> > Because
> > I noticed if I don't, server sometimes choose a cluster index scan which
> > is
> > too slow and costly. So, if I use table hint in the query and several
> > instances of it are called concurrently, is there any performance
> > concerns?
> >
> >
>
>|||Joseph wrote:
> Thanks, Andrew, for the tip. However, when I include the 'Execution
> Plan' event, it does not matter if I set up a filter for that
> specific SP or not, it shows all execution plans for all queries
> running on the server. I tried to include 'Show Plan Text', but the
> capture the text data does not show any details. Any idea?
>
Execution Plan requires only TextData. All other execucution plan events
require the BinaryData column as well. To filter on the procedure, use
the ObjectID. ObjectName does not filter on SP-related events. In any
case, any event you select for the trace that does not map to the
ObjectID column shows up. Try and stick to the
SP:Starting/Completed/StmtStarting/StmtCompleted events to avoid
excessive trace output.
--
David Gugick
Quest Software
www.imceda.com
www.quest.com
performance trace
Does anyone know how to track the actual execution plan the server uses? I
have one query designed for a frequently run report that normally takes abou
t
3 seconds to return. However, the Profiler logs show me that in certain
cases, it took more than 3 minutes to return and it cost a huge amount of CP
U
time. However, when I test it manually, it always returns in less than 3
seconds and nothing wrong in the execution plan. I am wondering that at
runtime, the server might decide to choose a different plan, especially when
the report is called by multiple users at the same time. If I can capture th
e
execution plan at runtime, I might be able to explain why the performance ca
n
vary so dramatically, and find a way to tune it.
Another thing, not sure if it is related or not. I am using table hint to
direct the server to use one specific index in order to speed it up. Because
I noticed if I don't, server sometimes choose a cluster index scan which is
too slow and costly. So, if I use table hint in the query and several
instances of it are called concurrently, is there any performance concerns?You can run a trace with a filter for that particular sp and include the
showplan event. That way you can see each time it is called what it is
actually using for a plan. The only real performance concern with a hint is
that you never let SQL Server determine what the best way may be.
Conditions and data can change and what may be OK today may not be tomorrow.
If you need to specify a hint it is possible that the query can be rewritten
so that the optimizer always chooses the right plan.
Andrew J. Kelly SQL MVP
"Joseph" <Joseph@.discussions.microsoft.com> wrote in message
news:CD9BE668-ADFD-4960-BAF9-CF31A026B074@.microsoft.com...
> Does anyone know how to track the actual execution plan the server uses? I
> have one query designed for a frequently run report that normally takes
> about
> 3 seconds to return. However, the Profiler logs show me that in certain
> cases, it took more than 3 minutes to return and it cost a huge amount of
> CPU
> time. However, when I test it manually, it always returns in less than 3
> seconds and nothing wrong in the execution plan. I am wondering that at
> runtime, the server might decide to choose a different plan, especially
> when
> the report is called by multiple users at the same time. If I can capture
> the
> execution plan at runtime, I might be able to explain why the performance
> can
> vary so dramatically, and find a way to tune it.
> Another thing, not sure if it is related or not. I am using table hint to
> direct the server to use one specific index in order to speed it up.
> Because
> I noticed if I don't, server sometimes choose a cluster index scan which
> is
> too slow and costly. So, if I use table hint in the query and several
> instances of it are called concurrently, is there any performance
> concerns?
>|||Thanks, Andrew, for the tip. However, when I include the 'Execution Plan'
event, it does not matter if I set up a filter for that specific SP or not,
it shows all execution plans for all queries running on the server. I tried
to include 'Show Plan Text', but the capture the text data does not show any
details. Any idea?
"Andrew J. Kelly" wrote:
> You can run a trace with a filter for that particular sp and include the
> showplan event. That way you can see each time it is called what it is
> actually using for a plan. The only real performance concern with a hint
is
> that you never let SQL Server determine what the best way may be.
> Conditions and data can change and what may be OK today may not be tomorro
w.
> If you need to specify a hint it is possible that the query can be rewritt
en
> so that the optimizer always chooses the right plan.
> --
> Andrew J. Kelly SQL MVP
>
> "Joseph" <Joseph@.discussions.microsoft.com> wrote in message
> news:CD9BE668-ADFD-4960-BAF9-CF31A026B074@.microsoft.com...
>
>|||Joseph wrote:
> Thanks, Andrew, for the tip. However, when I include the 'Execution
> Plan' event, it does not matter if I set up a filter for that
> specific SP or not, it shows all execution plans for all queries
> running on the server. I tried to include 'Show Plan Text', but the
> capture the text data does not show any details. Any idea?
>
Execution Plan requires only TextData. All other execucution plan events
require the BinaryData column as well. To filter on the procedure, use
the ObjectID. ObjectName does not filter on SP-related events. In any
case, any event you select for the trace that does not map to the
ObjectID column shows up. Try and stick to the
SP:Starting/Completed/StmtStarting/StmtCompleted events to avoid
excessive trace output.
David Gugick
Quest Software
www.imceda.com
www.quest.com
have one query designed for a frequently run report that normally takes abou
t
3 seconds to return. However, the Profiler logs show me that in certain
cases, it took more than 3 minutes to return and it cost a huge amount of CP
U
time. However, when I test it manually, it always returns in less than 3
seconds and nothing wrong in the execution plan. I am wondering that at
runtime, the server might decide to choose a different plan, especially when
the report is called by multiple users at the same time. If I can capture th
e
execution plan at runtime, I might be able to explain why the performance ca
n
vary so dramatically, and find a way to tune it.
Another thing, not sure if it is related or not. I am using table hint to
direct the server to use one specific index in order to speed it up. Because
I noticed if I don't, server sometimes choose a cluster index scan which is
too slow and costly. So, if I use table hint in the query and several
instances of it are called concurrently, is there any performance concerns?You can run a trace with a filter for that particular sp and include the
showplan event. That way you can see each time it is called what it is
actually using for a plan. The only real performance concern with a hint is
that you never let SQL Server determine what the best way may be.
Conditions and data can change and what may be OK today may not be tomorrow.
If you need to specify a hint it is possible that the query can be rewritten
so that the optimizer always chooses the right plan.
Andrew J. Kelly SQL MVP
"Joseph" <Joseph@.discussions.microsoft.com> wrote in message
news:CD9BE668-ADFD-4960-BAF9-CF31A026B074@.microsoft.com...
> Does anyone know how to track the actual execution plan the server uses? I
> have one query designed for a frequently run report that normally takes
> about
> 3 seconds to return. However, the Profiler logs show me that in certain
> cases, it took more than 3 minutes to return and it cost a huge amount of
> CPU
> time. However, when I test it manually, it always returns in less than 3
> seconds and nothing wrong in the execution plan. I am wondering that at
> runtime, the server might decide to choose a different plan, especially
> when
> the report is called by multiple users at the same time. If I can capture
> the
> execution plan at runtime, I might be able to explain why the performance
> can
> vary so dramatically, and find a way to tune it.
> Another thing, not sure if it is related or not. I am using table hint to
> direct the server to use one specific index in order to speed it up.
> Because
> I noticed if I don't, server sometimes choose a cluster index scan which
> is
> too slow and costly. So, if I use table hint in the query and several
> instances of it are called concurrently, is there any performance
> concerns?
>|||Thanks, Andrew, for the tip. However, when I include the 'Execution Plan'
event, it does not matter if I set up a filter for that specific SP or not,
it shows all execution plans for all queries running on the server. I tried
to include 'Show Plan Text', but the capture the text data does not show any
details. Any idea?
"Andrew J. Kelly" wrote:
> You can run a trace with a filter for that particular sp and include the
> showplan event. That way you can see each time it is called what it is
> actually using for a plan. The only real performance concern with a hint
is
> that you never let SQL Server determine what the best way may be.
> Conditions and data can change and what may be OK today may not be tomorro
w.
> If you need to specify a hint it is possible that the query can be rewritt
en
> so that the optimizer always chooses the right plan.
> --
> Andrew J. Kelly SQL MVP
>
> "Joseph" <Joseph@.discussions.microsoft.com> wrote in message
> news:CD9BE668-ADFD-4960-BAF9-CF31A026B074@.microsoft.com...
>
>|||Joseph wrote:
> Thanks, Andrew, for the tip. However, when I include the 'Execution
> Plan' event, it does not matter if I set up a filter for that
> specific SP or not, it shows all execution plans for all queries
> running on the server. I tried to include 'Show Plan Text', but the
> capture the text data does not show any details. Any idea?
>
Execution Plan requires only TextData. All other execucution plan events
require the BinaryData column as well. To filter on the procedure, use
the ObjectID. ObjectName does not filter on SP-related events. In any
case, any event you select for the trace that does not map to the
ObjectID column shows up. Try and stick to the
SP:Starting/Completed/StmtStarting/StmtCompleted events to avoid
excessive trace output.
David Gugick
Quest Software
www.imceda.com
www.quest.com
Subscribe to:
Posts (Atom)