September 22, 2016 at 9:03 am
Hi
we have WS 2012 which has
WS2008R2/3 processors/6 GB RAM
On it we have SQL server 2012. Currently we have 30 (yes 30) SQL DB's on there. It's our test DB system so not heavily used. However I'm concerned that the server spec isn't enough to be running this amount of DB's (not much data in them).
Any thoughts please ? And what should we be checking to see if indeed we need to up the spec(processsors/RAM etc) on the box ?
Thanks for any help in advance
September 22, 2016 at 9:26 am
page853 (9/22/2016)
Hiwe have WS 2012 which has
WS2008R2/3 processors/6 GB RAM
On it we have SQL server 2012. Currently we have 30 (yes 30) SQL DB's on there. It's our test DB system so not heavily used. However I'm concerned that the server spec isn't enough to be running this amount of DB's (not much data in them).
Any thoughts please ? And what should we be checking to see if indeed we need to up the spec(processsors/RAM etc) on the box ?
Thanks for any help in advance
Not nearly enough information to guide you. I can kill your server with a 10MB database and a bad query. I could run a 500GB database on your server with no issues in another case.
What really matters: is performance acceptable to the users of the server? If so, move on. If not, is there an urgency (and budget) to address the issue? If yes to both then have at it. If not then there is nothing to be done - move on. 🙂
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
September 22, 2016 at 10:11 am
Hi TheSQLGuru - thanks
Performance is slow - but I think the 3rd party app that uses the SQL server runs slow.
We don't run queries as such, but the 3rd party app obviously read/writes to/from the DB.
I just want peace of mind that we're not heaping a lot of DB's onto the system and we don't know what metrics to check to see whether we have a problem.
September 22, 2016 at 11:07 am
page853 (9/22/2016)
Hi TheSQLGuru - thanksPerformance is slow - but I think the 3rd party app that uses the SQL server runs slow.
We don't run queries as such, but the 3rd party app obviously read/writes to/from the DB.
I just want peace of mind that we're not heaping a lot of DB's onto the system and we don't know what metrics to check to see whether we have a problem.
1) The 3rd party app could be slow because it is running on a server that is underpowered.
2) Get a copy of sp_whoisactive. Awesome freebie from Adam Machanic for seeing what is going on right now and how badly queries are suffering. Also grab a copy of Glenn Berry's SQL Server Diagnostics Script for your version of SQL Server. Find the file IO stalls and wait stats queries to see how badly those things are doing. There is a wealth of other useful queries in there!
3) With so little RAM you likely are doing a LOT of IO for reads and that kills most of my client's performance.
4) Speaking of clients, consider getting a performance tuning consultant on board for a few hours to a few days to give your stuff a review. It is amazing how helpful that can be!!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
September 22, 2016 at 11:13 am
Hi thanks
unfortunately I can't put anything on the server that isn't cleared by the institution I work for.
We're just eyeballing task manger - but I'm not sure that is the whole story.
September 22, 2016 at 11:18 am
It is a test system, it may be slow but it is acceptable for what you are doing? I'd be more concerned about it if it was a production system and it was running slow.
September 22, 2016 at 11:49 am
page853 (9/22/2016)
Hi thanksunfortunately I can't put anything on the server that isn't cleared by the institution I work for.
We're just eyeballing task manger - but I'm not sure that is the whole story.
Nothing I mentioned are "things you put on the server". Well, sp_whoisactive is a stored procedured that does actually compile there, but you can drop it. It is a glorified sp_who2 that every single one of the client's I have gone to since it came out immediately starts using.
Glenn's stuff is just plain ol' TSQL queries.
If you have "powers that be" that clear things to put on servers, why aren't they also responsible for monitoring said server and ensuring it's performance is acceptable?!? :w00t:
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
September 23, 2016 at 2:37 am
Hi same applies to our prod system where we have 14 DB's in a single instance with the box spec'd at
WS2008R2/8 GB RAM - SQL Server 2008 R2 installed
I'm guessing using windows task manager won't give us details on how the DB's are performing ?
September 23, 2016 at 4:37 am
It should be relatively cheap to upgrade the amount of memory in the servers to 32GBytes.
(I think that is the maximum for the standard version of WS2008R2.)
It is also worth monitoring latency and waits:
http://www.sqlskills.com/blogs/paul/how-to-examine-io-subsystem-latencies-from-within-sql-server/
http://www.sqlskills.com/blogs/paul/wait-statistics-or-please-tell-me-where-it-hurts/
Also, Brent Ozar's sp_Blitz might highlight some pinch points:
September 23, 2016 at 4:58 am
Hi - thanks
My IT Manager is just using task manager.
I'm guessing using windows task manager won't give us details on how each/all the DB's are performing ?
September 23, 2016 at 6:08 am
When using Windows utilities, Performance Monitor, perfmon, will be better at showing server bottlenecks.
September 23, 2016 at 8:20 am
Ken McKelvey (9/23/2016)
(I think that is the maximum for the standard version of WS2008R2.)
It is also worth monitoring latency and waits:
http://www.sqlskills.com/blogs/paul/how-to-examine-io-subsystem-latencies-from-within-sql-server/
http://www.sqlskills.com/blogs/paul/wait-statistics-or-please-tell-me-where-it-hurts/
Also, Brent Ozar's sp_Blitz might highlight some pinch points:
https://www.brentozar.com/blitz/%5B/quote%5D
Making it easier for others:
http://www.sqlskills.com/blogs/paul/how-to-examine-io-subsystem-latencies-from-within-sql-server/
http://www.sqlskills.com/blogs/paul/wait-statistics-or-please-tell-me-where-it-hurts/
September 23, 2016 at 8:50 am
Thanks for all your replies.
Could someone answer me the question;
Will looking at server task manager give any clue as to how the DB is performing ?
September 23, 2016 at 10:55 am
page853 (9/23/2016)
Thanks for all your replies.Could someone answer me the question;
Will looking at server task manager give any clue as to how the DB is performing ?
Task Manager? No.
September 23, 2016 at 12:09 pm
Thnks.
So in order to find out how the instance is performing
a) do i need to use Perfmon - and if so which parameters do i need to monitor? ( also if i do use perfmon will it be resource hungry?)
b) use some of the other stored procedure type scripts suggested previously?
Basically my manager thinks we can just use server task manager to determine how the db is performing - which my gut feel said was invalid
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply