December 20, 2010 at 1:17 pm
I need to move an application that is currently running on its own copy of SQL 2005 Express. This application is also running on its own hardware connected to my network. The latest version of this application requires a copy of SQL 2005 Stnd or SQL 2000 MSDE to run, 2005 Express is no longer an option.
My question is this: How would I tell if my current SQL 2005 Stnd server can handle another application? I would like to know I'm not about to add a database application to a server that is already maxed out in terms of performance SQL or Windows (space is not an issue). Is there a SQL report I can run to find this out? Or would this be a Windows server report? I realize many factors play a roll in this decision which is why I’m looking for a report(s) to help me make the call.
Currently I have no issues with the database applications already running on the server in question.
December 20, 2010 at 1:43 pm
Lets see, perf mon counters to look at..
Output Queue Length (on the nic interface) near 0
Page Life Expectancy (SQL: Buffer Manager) I don't like seeing any number under 600, but the higher the better.
Buffer Cache Hit Ration (SQL: Buffer Manager) I like to see 99+%
% Processor Time (Processor) shouldn't be high all the time
Batches Requested/Sec (SQL: SQL Statistics) can't remember the guidance for this one.
Compilations/sec (SQL: SQL Statistics) should be a low number
Recompilations/sec (SQL: SQL Statistics) should be a low number, should be near zero for a sproc driven system.
As always, it depends.
Memory is often an easy way to enhance performance and the Page Life Expectancy is usually a way to detect memory pressure, if there are regularly low numbers then you need to add memory before doing much else.
Output Queue Length can help tell you that your NIC card is saturated.
% Processor Time can help tell you that you are being processor bound.
None of these will guarantee that there will be no performance degradation, they are only tools to see the shape of your server.
CEWII
December 22, 2010 at 7:33 am
Piling on a bit, I agree. You just need to do fundamental data collection of the performance metrics. Use perfmon or, if you have a little money, you might pick up a third party tool like Red Gate's SQL Monitor. It's just going to collect & present the same thing you could on your own, but it'll do a lot of the detail work for you.
"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
December 22, 2010 at 7:57 am
Theres no simple report to to tell you yes, that can go on there.
Even with the advise given, you will still need to be able to interpret the data that comes back. You will also need to understand the additional load from the application that is being moved. You also need to know the future intentions of the system, how long it needs to be around, etc, etc.
Who owns the live environment? I would speak to them (they should now the current state, future plans, etc) and get them to tell you where to put the database. They should be asking questions about load, con-current users, growth, etc.
December 23, 2010 at 8:12 am
"... many factors may play a role ..."
That may qualify as the understatement of the year. You could have a 10MB database that could swamp a quad-quad server or a 500GB database that could run just fine on my laptop. You could also have a server that could handle 10000 complex transactions per second or one that couldn't handle 10 of the same transactions. Add in that there are several hundred wait types you could observe/trend and thousands of windows/sql server performance counters to observe/trend and you are asking for something that is impossible to know with anything other than a gut feel.
Having said that, my guess is that it will be pretty obvious that the existing server is either way over powered or already being taxed. Likewise pretty obvious if the app you are moving is intensive or not.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply