Mixing
Using Resource Governor and allocate more memory to satisfy high Memory Grant request by queries?
Clash Royale CLAN TAG#URR8PPP
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty margin-bottom:0;
up vote
1
down vote
favorite
On a SQL Server 2016 database server, I have few queries which are really slow and which ask for more memory (queries are not optimized). This is making the whole database slow as lots of users are running the same query. This is causing other queries to wait. I get CXCONSUMER wait (due to parallel execution) and RESOURCE_SEMAPHORE.
So this is creating memory pressure (I believe that these slow queries are creating memory contention for other queries and in turn creating memory pressure) and i think this is causing my plan cache to clear in fixed intervals.
I read that Memory Grant % by default is 25%. I am thinking of increasing this 25% to give memory for queries. I am trying to solve the problem temporarily for now.
The queries which are not optimized are LINQ queries and changing that will require some time for the team.
So please suggest whether increasing Memory Grant % in resource governor is a good idea or not?
I have total of 128GB RAM and 75% (102 GB) of it is allocated to SQL Server.
sql-server memory waits resource-governor
edited 8 mins ago
Paul White♦
47.5k14256405
asked 2 hours ago
user9516827
1819
add a comment |Â
up vote
1
down vote
favorite
On a SQL Server 2016 database server, I have few queries which are really slow and which ask for more memory (queries are not optimized). This is making the whole database slow as lots of users are running the same query. This is causing other queries to wait. I get CXCONSUMER wait (due to parallel execution) and RESOURCE_SEMAPHORE.
So this is creating memory pressure (I believe that these slow queries are creating memory contention for other queries and in turn creating memory pressure) and i think this is causing my plan cache to clear in fixed intervals.
I read that Memory Grant % by default is 25%. I am thinking of increasing this 25% to give memory for queries. I am trying to solve the problem temporarily for now.
The queries which are not optimized are LINQ queries and changing that will require some time for the team.
So please suggest whether increasing Memory Grant % in resource governor is a good idea or not?
I have total of 128GB RAM and 75% (102 GB) of it is allocated to SQL Server.
sql-server memory waits resource-governor
edited 8 mins ago
Paul White♦
47.5k14256405
asked 2 hours ago
user9516827
1819
add a comment |Â
up vote
1
down vote
favorite
up vote
1
down vote
favorite
On a SQL Server 2016 database server, I have few queries which are really slow and which ask for more memory (queries are not optimized). This is making the whole database slow as lots of users are running the same query. This is causing other queries to wait. I get CXCONSUMER wait (due to parallel execution) and RESOURCE_SEMAPHORE.
So this is creating memory pressure (I believe that these slow queries are creating memory contention for other queries and in turn creating memory pressure) and i think this is causing my plan cache to clear in fixed intervals.
I read that Memory Grant % by default is 25%. I am thinking of increasing this 25% to give memory for queries. I am trying to solve the problem temporarily for now.
The queries which are not optimized are LINQ queries and changing that will require some time for the team.
So please suggest whether increasing Memory Grant % in resource governor is a good idea or not?
I have total of 128GB RAM and 75% (102 GB) of it is allocated to SQL Server.
sql-server memory waits resource-governor
edited 8 mins ago
Paul White♦
47.5k14256405
asked 2 hours ago
user9516827
1819
On a SQL Server 2016 database server, I have few queries which are really slow and which ask for more memory (queries are not optimized). This is making the whole database slow as lots of users are running the same query. This is causing other queries to wait. I get CXCONSUMER wait (due to parallel execution) and RESOURCE_SEMAPHORE.
So this is creating memory pressure (I believe that these slow queries are creating memory contention for other queries and in turn creating memory pressure) and i think this is causing my plan cache to clear in fixed intervals.
I read that Memory Grant % by default is 25%. I am thinking of increasing this 25% to give memory for queries. I am trying to solve the problem temporarily for now.
The queries which are not optimized are LINQ queries and changing that will require some time for the team.
So please suggest whether increasing Memory Grant % in resource governor is a good idea or not?
I have total of 128GB RAM and 75% (102 GB) of it is allocated to SQL Server.
sql-server memory waits resource-governor
sql-server memory waits resource-governor
edited 8 mins ago
Paul White♦
47.5k14256405
asked 2 hours ago
user9516827
1819
edited 8 mins ago
Paul White♦
47.5k14256405
asked 2 hours ago
user9516827
1819
edited 8 mins ago
Paul White♦
47.5k14256405
edited 8 mins ago
Paul White♦
47.5k14256405
edited 8 mins ago
Paul White♦
47.5k14256405
47.5k14256405
asked 2 hours ago
user9516827
1819
asked 2 hours ago
user9516827
1819
asked 2 hours ago
user9516827
1819
1819
add a comment |Â
add a comment |Â
1 Answer
1
active
oldest
votes
up vote
3
down vote
accepted
This is the opposite of what you should do.
When you have queries waiting on RESOURCE_SEMAPHORE, they're waiting for other queries to finish running, and give up their memory. The queries that are waiting aren't even running.
Letting all queries use more memory will only make the problem worse. You'd wanna configure resource governor to make queries ask for smaller grants. This could also cause problems, though, because any spills to disk would get worse.
answered 2 hours ago
sp_BlitzErik
20.4k1162103
So i guess fixing the query will make the optimizer to calculate ideal memory required by queries and may avoid the memory grant requests.
– user9516827
2 hours ago
@user9516827 It would be a better idea if you posted a new question about the query you're having problems with. See Getting Help With A Slow Query.
– sp_BlitzErik
1 hour ago
@user9516827 yes, that is too broad. The server has many critical problems that it would take detailed analysis to address, likely over several weeks. It's not just a matter of tuning a single query.
– sp_BlitzErik
1 hour ago
add a comment |Â
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
3
down vote
accepted
This is the opposite of what you should do.
When you have queries waiting on RESOURCE_SEMAPHORE, they're waiting for other queries to finish running, and give up their memory. The queries that are waiting aren't even running.
Letting all queries use more memory will only make the problem worse. You'd wanna configure resource governor to make queries ask for smaller grants. This could also cause problems, though, because any spills to disk would get worse.
answered 2 hours ago
sp_BlitzErik
20.4k1162103
So i guess fixing the query will make the optimizer to calculate ideal memory required by queries and may avoid the memory grant requests.
– user9516827
2 hours ago
@user9516827 It would be a better idea if you posted a new question about the query you're having problems with. See Getting Help With A Slow Query.
– sp_BlitzErik
1 hour ago
@user9516827 yes, that is too broad. The server has many critical problems that it would take detailed analysis to address, likely over several weeks. It's not just a matter of tuning a single query.
– sp_BlitzErik
1 hour ago
add a comment |Â
up vote
3
down vote
accepted
This is the opposite of what you should do.
When you have queries waiting on RESOURCE_SEMAPHORE, they're waiting for other queries to finish running, and give up their memory. The queries that are waiting aren't even running.
Letting all queries use more memory will only make the problem worse. You'd wanna configure resource governor to make queries ask for smaller grants. This could also cause problems, though, because any spills to disk would get worse.
answered 2 hours ago
sp_BlitzErik
20.4k1162103
So i guess fixing the query will make the optimizer to calculate ideal memory required by queries and may avoid the memory grant requests.
– user9516827
2 hours ago
@user9516827 It would be a better idea if you posted a new question about the query you're having problems with. See Getting Help With A Slow Query.
– sp_BlitzErik
1 hour ago
@user9516827 yes, that is too broad. The server has many critical problems that it would take detailed analysis to address, likely over several weeks. It's not just a matter of tuning a single query.
– sp_BlitzErik
1 hour ago
add a comment |Â
up vote
3
down vote
accepted
up vote
3
down vote
accepted
This is the opposite of what you should do.
When you have queries waiting on RESOURCE_SEMAPHORE, they're waiting for other queries to finish running, and give up their memory. The queries that are waiting aren't even running.
Letting all queries use more memory will only make the problem worse. You'd wanna configure resource governor to make queries ask for smaller grants. This could also cause problems, though, because any spills to disk would get worse.
answered 2 hours ago
sp_BlitzErik
20.4k1162103
This is the opposite of what you should do.
When you have queries waiting on RESOURCE_SEMAPHORE, they're waiting for other queries to finish running, and give up their memory. The queries that are waiting aren't even running.
Letting all queries use more memory will only make the problem worse. You'd wanna configure resource governor to make queries ask for smaller grants. This could also cause problems, though, because any spills to disk would get worse.
answered 2 hours ago
sp_BlitzErik
20.4k1162103
answered 2 hours ago
sp_BlitzErik
20.4k1162103
answered 2 hours ago
sp_BlitzErik
20.4k1162103
answered 2 hours ago
sp_BlitzErik
20.4k1162103
20.4k1162103
So i guess fixing the query will make the optimizer to calculate ideal memory required by queries and may avoid the memory grant requests.
– user9516827
2 hours ago
@user9516827 It would be a better idea if you posted a new question about the query you're having problems with. See Getting Help With A Slow Query.
– sp_BlitzErik
1 hour ago
@user9516827 yes, that is too broad. The server has many critical problems that it would take detailed analysis to address, likely over several weeks. It's not just a matter of tuning a single query.
– sp_BlitzErik
1 hour ago
add a comment |Â
So i guess fixing the query will make the optimizer to calculate ideal memory required by queries and may avoid the memory grant requests.
– user9516827
2 hours ago
@user9516827 It would be a better idea if you posted a new question about the query you're having problems with. See Getting Help With A Slow Query.
– sp_BlitzErik
1 hour ago
@user9516827 yes, that is too broad. The server has many critical problems that it would take detailed analysis to address, likely over several weeks. It's not just a matter of tuning a single query.
– sp_BlitzErik
1 hour ago
So i guess fixing the query will make the optimizer to calculate ideal memory required by queries and may avoid the memory grant requests.
– user9516827
2 hours ago
So i guess fixing the query will make the optimizer to calculate ideal memory required by queries and may avoid the memory grant requests.
– user9516827
2 hours ago
@user9516827 It would be a better idea if you posted a new question about the query you're having problems with. See Getting Help With A Slow Query.
– sp_BlitzErik
1 hour ago
@user9516827 It would be a better idea if you posted a new question about the query you're having problems with. See Getting Help With A Slow Query.
– sp_BlitzErik
1 hour ago
@user9516827 yes, that is too broad. The server has many critical problems that it would take detailed analysis to address, likely over several weeks. It's not just a matter of tuning a single query.
– sp_BlitzErik
1 hour ago
@user9516827 yes, that is too broad. The server has many critical problems that it would take detailed analysis to address, likely over several weeks. It's not just a matter of tuning a single query.
– sp_BlitzErik
1 hour ago
add a comment |Â
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f220818%2fusing-resource-governor-and-allocate-more-memory-to-satisfy-high-memory-grant-re%23new-answer', 'question_page');
);
Post as a guest
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
