asked
Min and Max memory setting
Does max and min memory setting affect the sql server performance? My OS has 128 GB of Ram and the SQL Server instance memory setting for min is set to 100 GB and Max is set to 118 GB. It takes a lot of time for me to bulk insert about 10 million records compare to bulk inserting same amount of records in another server. I’m not sure what affect the min and max has on performance. Does it matter how you set the min and max memory? Thanks.
It does matter how you set the min and max memory. From a performance perspective, the normal "problem" setting is when people don't have a max set at all. You already do, so this setting is unlikely to be your problem. What makes you think that memory is the slow point of the data load process anyway? Have you already eliminated disks and CPU and, more importantly, code? If you really think you are literally running out of memory, you can check this by using the ring buffers. I outline how to do it in [an article here.][1] [1]:
https://www.simple-talk.com/sql/database-administration/why-is-that-sql-server-instance-under-stress/
14 People are following this question.
