April 15, 2007 at 11:05 pm
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
April 16, 2007 at 8:11 am
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
April 18, 2007 at 8:59 pm
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.
April 19, 2007 at 5:22 am
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