May 14, 2013 at 11:30 pm
Dear All,
We are having a SQL server which is being used through out day and night from local and outside the country. We planned to upgrade because of its low performance since the data got huge nowadays. We are having around 20 databases with ranging from 100MB to 100GB, in which few DBs have binary data. We are unable to do index maintenance as the files tend to grow more than we expect.
Could someone suggest me a very good configuration of hardware for the server in terms of Disk for data and log files, RAM and CPU.
Thanks.
KB
______________________________________________________________Every Problem has a Solution; Every Solution has a Problem: 🙂
May 15, 2013 at 2:01 am
There is not nearly enough information to say definitively what you should buy. It will depend on the data access patterns you have in your environment, the number of transactions per second, the throughput, lots of things we cannot know from your post. Honestly, if you are asking a public forum for this kind of help on this important of a decision for your environment I would suggest you hire a consultant that specializes in sizing SQL Server hardware. It will be money well spent.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 15, 2013 at 2:43 am
Thanks for your suggestion, anyways I am responsible to recommend so and so hardware requirements. But our server consultant is not providing sufficient resources that I demand.
So, could you mention some of the ways to prove my requirement.
Thanks.
KB
______________________________________________________________Every Problem has a Solution; Every Solution has a Problem: 🙂
May 15, 2013 at 8:04 am
Look into your split of read to writes by database. Check performance counters over time to see where your bottleneck is, all systems are constrained by something. Look at your growth estimates and project space needs into the future in line with your hardware replacement/upgrade schedule. Good luck.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 16, 2013 at 2:57 am
Sure, I will try to do that. However, how will you conclude on the requirement if reads are more. The performance counter shows 99.8%-100% for SQL Buffer Manager in all our Servers having SQL Server. The memory we have in the server is 4GB to 12GB. Should I ask for more physical memory? Or can I manage by increasing the virtual memory?
Also please let me know how to perform a load test on the SQL Server.
We have scaled the growth of each database in the future 🙂
Thanks.
KB
______________________________________________________________Every Problem has a Solution; Every Solution has a Problem: 🙂
May 16, 2013 at 4:33 am
karthik babu (5/14/2013)
We planned to upgrade because of its low performance
What kind of performance problem you are facing? like low memory, huge IO, CPU hike, blocking, deadlocks? Before going for hardware upgrade, Do you make yourself sure about that You do not have poorly designed queries?
We are unable to do index maintenance as the files tend to grow more than we expect.
Are you getting any error while index rebuild.? Please post more details.
May 16, 2013 at 5:30 am
Most of our queries use lot and lots of temp tables, however the tempdb size will not exceed 2GB. When I try to rebuild index, it takes more than 2 days to complete and till that time applications become very slow. We have only locks and never faced blocking or deadlocks. Hopefully, the queries should be alright though they are not perfect.
Our problem is this - The queries takes more reads in the user database and writes in tempdb - to avoid this we need more indexes to be built and we need enough disk space to create indexes.
My conclusion is to increase RAM and Disk Space for which I want to know how much?
______________________________________________________________Every Problem has a Solution; Every Solution has a Problem: 🙂
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply