January 17, 2021 at 7:22 am
This may be a bit non-typical, but I am currently running SQL 2019 Developer on my old gaming rig, and I've hit the point where I need to migrate it to a stronger machine.
Has anyone got any hardware tips for running a standalone 2019 instance that won't for the most part be connecting to anything but localhost at most?
I do a lot of analysis of government data and the files/logs/indexes etc are at the point where I just need more beef and so I need to pick a good CPU/Motherboard/Drive etc. Given that I am not servicing customers or even remote or in-network workstations my needs are pretty minimal. Anyone have any experience doing this sort of thing and if so any hardware recommendations on cpu/motherboard/drives etc?
My data is self-ETL'd (mostly flat files via bcp) but the datasets are not that small (85m records in some resultsets) and performance is terrible currently on queries. I've been adding indexes (and frequently having to clear logs) where necessary but its at the breaking point now and the hardware has to change.
Any feedback would be lovely.
January 17, 2021 at 11:51 am
get an I7/I9 with 6 cores+ and 32GB+ ram and some SSD disks.
you may also need to tweek max DOP and CTFP so your queries to not overload the machine. defaults are bad.
but I wonder - if your job is to analyze gov data and if you are getting paid for that then you are most likely in breach of licensing terms by using a developer license.
if it is just a hobby then you are fine.
January 17, 2021 at 7:14 pm
Already running all that sadly, and already using MAXDOP where necessary. It's just not enough for some of the bigger queries due to the poor data structure.
Also, at no point in my post did I say what my job is, and I am 100% certain I am not in violation of the license. Thanks for the response 🙂
January 17, 2021 at 7:51 pm
if you already have a good spec (you didn't tell us what you have) then you might be better off supplying us with some of your "bad behaved" queries and their actual explain plan along side with tables, indexes and record count.
some of us may be able to give you suggestions to improve their performance.
January 18, 2021 at 5:09 pm
I'm not looking for query optimization, I am looking for hardware tips from people who may have stood up a SQL 2019 instance outside of a production environment and not using server based cpu/etc.
January 18, 2021 at 5:20 pm
thats fine -
what I have, which is what I told you to get, is a I7, 6 core, 32GB ram , ssd disks.
I do heavy processing on it (db is currently 1.2 TB in size, with tables going up to 250 Million rows.
processing is a mix of standard joins (plenty of them) and string manipulation in SQL - cpu hardly over 80% when I'm running the heavy ones.
as we don't know what you are doing, neither how you are doing, if the above spec is not enough for your needs you may need to consider buying a server level machine with 20 + cores and 128GB+
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply