query for performance monitoring

Hi All

In Oracle i can get Performance varables like Library Cache Hits, Dictionary Cache Hits, Database Buffers Read ,Redolog Buffers Read etc from the system dynamic tables.

I want to know how to get the same / related performance details in sql server 2000 and 2005. ( which are the parameters , Optimal value and which table/dynamic view to query).

Thanks in Advance

You can use dynamic management views, new to 2005.

http://msdn2.microsoft.com/en-us/library/ms188754.aspx|||

Hi,

In SQL 2000 (and 2005), you can query master.dbo.sysperfinfo to find most of what your looking for.

Additionally, sp_monitor and some DBCC commands have additional information, see http://www.sql-server-performance.com/dbcc_commands.asp and http://www.sqldev.net/articles/dbcc_sqlperf.htm for more details.

Hope that helps.

Jamie

|||

In addition to the other references already provided, you might find the examples and information in this white paper useful: http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx

Regards,

Gail

|||

Hi .... Thanks for your reply........

I need a query/queries to find the following in MS sql server.

1) To find most frequently accessed/executed tables/procedures

Please help me

Thanks in advance

|||

You might find that exploring the dynamic system views to be useful.

For example:

SELECT * FROM sys.dm_os_performance_counters

And there are quite a few additional dynamic system views to choose from. In Object Explorer, click on your database, VIEWS, and then System Views.

Often, the quality of the responses received is related to our ability to ‘bounce’ ideas off of each other. In the future, to make it easier for us to offer you assistance, and to prevent folks from wasting time on already answered questions, please don't post to multiple newsgroups. Choose the one that best fits your question and post there. Only post to another newsgroup if you get no answer in a day or two (or if you accidentally posted to the wrong newsgroup –and you indicate that you've already posted elsewhere).

|||

Check the response in your duplicate post in the Transact-SQL forum.

Often, the quality of the responses received is related to our ability to ‘bounce’ ideas off of each other. In the future, to make it easier for us to offer you assistance, and to prevent folks from wasting time on already answered questions, please:

Don't post to multiple newsgroups. Choose the one that best fits your question and post there. Only post to another newsgroup if you get no answer in a day or two (or if you accidentally posted to the wrong newsgroup –and you indicate that you've already posted elsewhere).

|||

Hi Arnie...

I made the post in the other forum by mistake ......

thanks for the reminder

|||

can u send me the reply link for ur question

I need a query/queries to find the following in MS sql server.

1) To find most frequently accessed/executed tables/procedures

sql

Query Cache?

Does SQL Server have a query cache similar to mysql, whereas the query
result is cached, if the table has not been changed?

If so, please refer me to more info.

Thanks.

--
http://www.dbForumz.com/ This article was posted by author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbForumz.com/General-Dis...pict170423.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbForumz.com/eform.php?p=571428steve (UseLinkToEmail@.dbForumz.com) writes:
> Does SQL Server have a query cache similar to mysql, whereas the query
> result is cached, if the table has not been changed?

SQL Server maintains a cache, but certainly not with canned results for a
queries. In a OLTP system, many tables are frequently updated, so such the
likelyhood the of the same query yielding the same result twice is not that
fantastic. And for that matter, the likelyhood of the same query reappering
with the exactly the same parameters is not that extreme either. It is also
worth to keep in mind that most queries refers to more than one table.

What SQL Server has in its cache are recently accessed data pages, as well
as query plan for recently submitted queries and invoked stored procedure.
This makes it possible to retrieve the result of a query without accessing
the disk for frequently accessed tables. Note here that this cache works
better, the bigger it is. Thus, spending cache on less usful information,
such as canned query results, may have adverse results on performance.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp