November 21, 2015 at 12:03 pm
This whole thing doesn't make sense unless more information is provided, memory configurations and specs are relative to all the work the server has to perform, not exceptions like occasional large batches etc.
😎
November 22, 2015 at 11:56 pm
Grant,
This job is running from front end application and do not provide the provision of splitting the batch as instructed.
November 23, 2015 at 2:01 am
Then leave it as-is. There's nothing you've mentioned in this thread that's a problem. As mentioned before, SQL will use all memory that it's allowed to use. This is expected, documented behaviour and it's a good thing.
Now, you may have it using a little too much and risking starving the OS. You probably want to lower max server memory if it's set to a non-default value. If it's set at default still, you definitely want to change it. See chapter 4 of https://www.red-gate.com/community/books/accidental-dba
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 23, 2015 at 7:20 am
I'm pretty much with Gail. Just because it says it's using 98% of memory doesn't mean there's anything approaching a problem.
If you do know that the batch is a problem, then you need to address the batch. Flipping memory settings is unlikely to do anything about the batch issues.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 23, 2015 at 10:06 am
Grant Fritchey (11/23/2015)
I'm pretty much with Gail. Just because it says it's using 98% of memory doesn't mean there's anything approaching a problem.If you do know that the batch is a problem, then you need to address the batch. Flipping memory settings is unlikely to do anything about the batch issues.
Unless the setting is allocating 200+ more GB of ram. 😀
November 23, 2015 at 3:30 pm
benjamin.reyes (11/23/2015)
Unless the setting is allocating 200+ more GB of ram. 😀
Nothing wrong with allocating 200+GB memory to SQL, providing the server has it. I've got a client with a server with 512GB memory in it. Max server memory is definitely over 200GB on that server.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 24, 2015 at 12:14 pm
benjamin.reyes (11/23/2015)
Grant Fritchey (11/23/2015)
I'm pretty much with Gail. Just because it says it's using 98% of memory doesn't mean there's anything approaching a problem.If you do know that the batch is a problem, then you need to address the batch. Flipping memory settings is unlikely to do anything about the batch issues.
Unless the setting is allocating 200+ more GB of ram. 😀
Well, you got me there. That's a cool setting to have.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 30, 2015 at 2:01 am
How about the idea of partitioning the table on which the batch process is running
November 30, 2015 at 2:19 am
To achieve what (other than additional complexity)?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply