October 27, 2021 at 7:25 pm
Hi
I have a very large database (600GB) that I am looking at creating. I will be running finance data analytic algorithms. I am considering AI algorithms too as the second project once the main one (data analytics) is completed. The AI will also be finance related. I have not decided if I should go with SQL Server or Oracle (or other company). I have only heard of these two. I still have to buy the workstation. There are so many configuration options available so I need some help. What kinds of hardware configuration will significantly boost the performance of a SQL Server 600 GB DB? Some ideas I have are:
Anything else?
What kind of processor should I be using? Xeon? I7? multi-core?
Would a GPU help? If yes, which type? I don't know if SQL Server 2019 is designed to take advantage of GPUs or not
I listed some ideas but I am open to all ideas.
Any suggestions will be much appreciated.
Thank you
October 27, 2021 at 7:56 pm
Yes to the most RAM you can get and actually use for SQL Server: 128GB for Standard Edition, as much as you can afford for Enterprise.
Note that, if Standard Ed, you'll want more RAM in the server itself so that you can still allocate a full 128 to SQL. For example, put 192GB on the box and allocate 128 to SQL.
As much as I love SQL Server, I have to admit that it's likely not the best platform for number crunching. I suspect Oracle isn't either. I'm not sure which db would be.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 27, 2021 at 8:16 pm
Thank you!
Does the CPU type matter? Xeon vs I7 etc?
October 27, 2021 at 8:54 pm
Every CPU these days is multi-core. Obviously, faster clock speeds is generally better than slower for similar core counts. Look at https://www.cpubenchmark.net/ for CPU ratings & comparisons, including performance/price ratios.
Glen Berry has some (now-year-old) recommendations/ratings for Intel & AMD processors: https://glennsqlperformance.com/2020/05/21/recommended-intel-processors-for-sql-server/ & https://glennsqlperformance.com/2020/05/20/recommended-amd-processors-for-sql-server/.
I'd love to see some newer recommendations, & Glenn's thoughts on the question of desktop vs. server CPUs.
Xeon motherboards are going to be more likely to give you the ability to use more RAM, more disk storage, & faster ethernet than Core i-7/i-9 boards. Not so sure about NVMe/PCIE SSD storage.
Likewise for Threadripper vs. Ryzen motherboards.
Will you have many simultaneous workloads, or will this essentially be a single process?
If multiple workloads, then more cores will certainly help. They will also help even a single workload if processing is more efficient with parallel processing (not always true, unfortunately -- sometimes costs of parallelism outweigh the benefits).
October 27, 2021 at 9:33 pm
The links you provided are great. Thank you for sharing!
The licensing costs are nuts for multi-core processors. I am not a commercial business nor I do intend to sell anything I make. It is purely for research purposes and for my own use only. Do I qualify for the developer version? Will the developer version allow me to have a 600 GB DB?
October 28, 2021 at 2:08 am
SQL Server Developer Edition is free and it has all the features of Enterprise Edition. You just can't use it as a back-end for a customer facing product. If you're going to be doing data analysis, I'd recommend looking into columnstore indexes.
October 28, 2021 at 3:05 pm
One thing about the CPU - a lot of SQL Server operations are single-threaded, so having a lot of cores may not benefit you. There are exceptions (as queries can go parallel), but for the most part SQL performs best with a CPU that has fast single core processing. (my information on this may be outdated, but that was the case with versions prior to 2017 for sure).
That being said, I agree with Scott - SQL isn't going to be the workhorse you want for doing data analytics. SQL Server (and Oracle) are more just containers for holding the data. They can retrieve and process the data, but they are likely not the right tools for the analytics part. Think of the database like an Excel worksheet - it can hold the data and can do some of the processing, but when you start doing large data analytics on it, you end up having it chug along. That being said, SQL Server will be TONS faster than Excel at this, but depending on the number crunching you are doing, you may benefit from a full fledged analytics and reporting tool (Tableau, SAS, Power BI, etc). Those things though can be very CPU and memory heavy while they do their analytics.
That being said, SQL Server may be able to do the number crunching for you successfully depending on your data and the number crunching and the queries you are writing. An efficient query in SQL Server will perform better than poorly designed analytics in Tableau for example. It does depend on your data and your number crunching requirements and how you optimize things. Since you are doing number crunching, you can build a lot of good indexes in SQL Server and get some pretty good SELECT performance. Depending on how you design things, you could store the data as you go and have essentially staging tables along the way and reduce future processing (unless you need to re-process a staging table).
But, my opinion, since this is not for commercial purposes and for your own research, I wouldn't be too concerned about making it stupid fast; I would spend more time making sure it accurate and the data is presenting what you are wanting to show.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
October 28, 2021 at 3:06 pm
Thank you for confirming!!
October 28, 2021 at 3:10 pm
One thing about the CPU - a lot of SQL Server operations are single-threaded, so having a lot of cores may not benefit you. There are exceptions (as queries can go parallel), but for the most part SQL performs best with a CPU that has fast single core processing. (my information on this may be outdated, but that was the case with versions prior to 2017 for sure).
That being said, I agree with Scott - SQL isn't going to be the workhorse you want for doing data analytics. SQL Server (and Oracle) are more just containers for holding the data. They can retrieve and process the data, but they are likely not the right tools for the analytics part. Think of the database like an Excel worksheet - it can hold the data and can do some of the processing, but when you start doing large data analytics on it, you end up having it chug along. That being said, SQL Server will be TONS faster than Excel at this, but depending on the number crunching you are doing, you may benefit from a full fledged analytics and reporting tool (Tableau, SAS, Power BI, etc). Those things though can be very CPU and memory heavy while they do their analytics.
That being said, SQL Server may be able to do the number crunching for you successfully depending on your data and the number crunching and the queries you are writing. An efficient query in SQL Server will perform better than poorly designed analytics in Tableau for example. It does depend on your data and your number crunching requirements and how you optimize things. Since you are doing number crunching, you can build a lot of good indexes in SQL Server and get some pretty good SELECT performance. Depending on how you design things, you could store the data as you go and have essentially staging tables along the way and reduce future processing (unless you need to re-process a staging table).
But, my opinion, since this is not for commercial purposes and for your own research, I wouldn't be too concerned about making it stupid fast; I would spend more time making sure it accurate and the data is presenting what you are wanting to show.
Thank you for this. It is very helpful
Question....you raised a quote point re single threaded vs parallel. how is this done? Do I have to structure my queries in a certain way? Or does SQL Server do it on its own? I am new so please excuse if this is elementary question.
December 18, 2021 at 10:28 pm
I have seen some websites that rank i9 quite high. Glen's site doesn't mention any i9 processors. Only Xeon. is there any particular reason why i9 are not mentioned?
December 19, 2021 at 2:15 pm
I MAY be mistaken on this, but I think that an i9 is a desktop processor not a server processor. Generally, you would want to have SQL Server running on a server not a desktop.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
December 19, 2021 at 8:38 pm
I MAY be mistaken on this, but I think that an i9 is a desktop processor not a server processor. Generally, you would want to have SQL Server running on a server not a desktop.
That is what I am trying to figure out. I took a quick look at Intel specs and looks like memory is different. I9 support max 128GB but Xeon support 1TB. How important is memory to SQL Server performance?
December 19, 2021 at 9:05 pm
My opinion - VERY. In general, what you are going to be doing the most of in SQL is NOT going to be CPU heavy operations but memory and disk heavy operations. The more memory you can give SQL Server, the better it can perform. For example, you can use memory optimized tables which give a huge performance boost to data lookup as memory is a TON faster than disk. BUT you need to get that configured and set up prior to using it and it does have some drawbacks.
Another consideration is I am pretty sure (not certain, but I'd say 90% sure) that any motherboard that supports an i9 will be a single CPU motherboard. Xeon motherboards can support multiple CPU's.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
December 19, 2021 at 11:39 pm
Post withdrawn. I thought this for a company that was going to be selling things.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 20, 2021 at 5:38 am
This was removed by the editor as SPAM
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply