database performance

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