October 4, 2010 at 7:59 am
Hi all,
My scenario is we have several production servers and are contemplating virtualization or consolidation.
Aside from pretty much only one server, there are really no SQL queries or managed code being implemented on the other servers databases, making SQL profiler not the best choice for analyzing these servers. I am not having much luck playing around with perfmon and the counters to get an idea of how much these servers are consuming as far as resources.
What woud you suggest? Do you use any other 3rd party software to measure performance statistics? I am thinking of looking at Quest softwares Foglight permon anaylsis.
Any thoughts would be much appreciated.
Thanks.
October 4, 2010 at 8:06 am
Which version and edition of SQL? 2005 (since its in the 2005 section)? 🙂
October 4, 2010 at 8:15 am
Yes all the servers I'm looking at are 2005.
October 4, 2010 at 8:55 am
Can you please elaborate as to why Profiler is not appropriate?
October 4, 2010 at 9:15 am
Hi - I work here at Red Gate Software, and just thought I'd mention the performance and health monitoring tool we have, SQL Response. There's a new version (v2) out soon. If you want a sneak peek, you can download an EAP version via:
http://www.red-gate.com/products/sql_response_eap/index.htm
Feel free to try this out and see if this was the sort of thing you were looking for!
October 4, 2010 at 9:27 am
There's also a free add-in to SQL2005 sp2 called SQL Performance Dashboard. I just downloaded it the other day onto a test server. I have not really looked at it yet though.
Does anyone have any experience or feedback on it ?
October 4, 2010 at 9:36 am
Profiler grabs the events and activities occuring, but it doesn't really help monitor performance. Performance Monitor is better, but you need a way to load those log files up and analyze them. There are some MS tools to help load that data into Excel or SQL.
You want something that works across time. The Performance Dashboard can work, but I think it has limited data unless you have Enterprise and implement a Management Data Warehouse, and that's $$. I think you might be better off with something like Red Gate's SQL Response (I work for them), or one of the tools from SQL Sentry, Quest, or IDera that tracks performance data over time.
October 4, 2010 at 1:27 pm
Until you get into the third party tools (my favorites, in no particular order, are Red Gate SQL Response, Confio Ignite, Idera Diagnostic Manager), then I'd suggest a combination of performance monitor counters, and yes, a server-side trace (which you can set up from Profiler, don't use the Profiler gui on your production systems). That way you get server behavior and query behavior, because you need both.
"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
October 4, 2010 at 2:30 pm
Brent Ozar has a great webinar about this on his blog - http://www.brentozar.com/sql-server-training-videos/perfmon-and-profiler-for-sql-server/
October 5, 2010 at 12:44 am
If all you need are enough data to know how/if you need to virtualize it, you can setup a perfmon trace and either save it to a file and then load it in a DB to analyze/report or save it directly to a DB (all this can be automated with logman.exe).
To monitor cpu you can check the \\<machine_name>\Processor(*)\% User Time.
To monitor disk usage: \\<machine_name>\LogicalDisk(*)\Avg. Disk sec/Transfer.
Monitoring memory is harder since SQL server will gladly use as much memory has it can, one of the way is to reduce the maximum memory until you see \\<machine_name>\<InstanceName>:Buffer Manager\Buffer cache hit ratio going down.
Not to be tried on production system obviously 🙂
Once you have it all in a DB, aggregate the data by 15mins step and you can easily display it in a report chart.
Avegage CPU above 50% is starting to be an issue (you might not be able to deal with a spike), it might also be fine as long as the system has no spike or if the reduced performance is cheaper than getting more cpu power.
Avg. Disk sec/Transfer bellow 100 is good enough (depending on what's on the disk), higher than that means the disk subsystem is not keeping up.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply