Setting Up SQL Server 2019 for Optimal Performance

  • 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:

    • have lots of RAM
    • SSD

    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

  • 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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • Thank you!

    Does the CPU type matter?  Xeon vs I7 etc?

  • 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).

  • 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?

    • This reply was modified 1 month ago by  water490.
  • 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.

  • 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!

  • Thank you for confirming!!

  • Mr. Brian Gale wrote:

    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.

     

    • This reply was modified 4 weeks, 1 day ago by  water490.

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply