monitor performance parameters

  • Hi

    In Oracle i can get Performance variables 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).It would be of great help if u could provide me with specific parameters which are usually monitored and the query to find the same. 

     Thanks in Advance

  • You can't really get these types of counters directly out of SQL Server 2000. But in 2005, you can reference the dynamic management views such as sys.dm_os_performance_counters. Run the select and you can see what's available & put a where clause in as you see fit. For example, from your list, you'd probably be interested in the Buffer Cache Hit Ratio. It's there.

    You can also drill down into specific areas within the SQL engine itself by referencing sys.dm_os_* views. There are also low level views for transactions, i/o and databases. It really depends on what you're looking for.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • thanks for the reply.....

    I am new to Sql server .Could you help me by suggesting  a few counters which i need to monitor, to check whether my server is performing well.

  • Yow! What a question. Well, everyone has their own opinions. What's more, every situation is a little different. I'm working on an article right now for Simple-Talk on just this topic (I'm not an expert, I'm just willing to write) so I'll put what I think are the bare minimum basics up.

    Memory Pages/sec

    Avg. Disk Queue Length

    %Processor Time

    Buffer Cache Hit Ratio

    Full Scans/sec

    Lock Requests/sec

    Deadlocks/sec (if you experience deadlocks)

    User Connections

    Batch Requests/sec

    Those cover the bare-bones minimum. You will want more. In addition to browsing around here, I'd suggest you look into articles on Simple-Talk.com and Sql-Server-Performance.com. You may also want to go over to the Microsoft site and check out the new article on performance tuning using monitoring on waits: http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/performance_tuning_waits_queues.mspx . I'd also suggest taking a look at the Inside SQL Server books by Kalen Delaney & Itzik Ben-Gan (reviews available here: http://sigs.sqlpass.org/Resources/BookReviews/CompletedBookReviews/tabid/103/Default.aspx).

     

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply