September 28, 2016 at 11:45 am
Hi all,
We are planning consolidation, I have a good idea of the considerations but would be grateful to read how others gathered usage stats. I'm thinking of either using a programmatically-fired Profiler trace /EE, or one of the Redgate tools.
Any 'Best Practice' hints warmly welcomed!! 😉
JB
September 29, 2016 at 6:25 am
Of course using Redgate tools is the ultimate answer. SQL Monitor would be the one to use in this situation. You can link to a server without installing anything to capture the metrics over a period of time.
If you're looking at 2008 servers, Extended Events are preferable in my opinion, but on 2008, they're quite a bit harder to work with. You'll probably be ok with Trace to capture query metrics (although again, Redgate). After that, you need to capture server & SQL Server metrics using perfmon (although Redgate). Focus on waits & queues, but, since we're talking consolidation, you're also going to want general performance metrics like CPU percentage, etc.. You'll never see a simply additive, this server + this server = my new server, but, that's generally how you plan it out. It gives you the best first measure to know how the consolidation is going to work.
DISCLAIMER: For those who don't know, I work for Redgate.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
September 29, 2016 at 6:43 am
Another thing to consider when you're benchmarking for CPU would be the comparative performance of the CPU. If you're on something like E5-2640 v3 CPU's you may be getting something like 4-5 times the bang per core compared to your 5 year old general use boxes. When we dis a similar project we did look to try and take that into account as far as we could, as we hope for 60-70 ish as a target on our VM's when busy to try and help our CPU ready time (and make effective use of resources).
I'm certainly not pretending this is an exact science though. It's a guesstimate, at best at times.
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply