February 16, 2017 at 9:22 am
Kevin,
Resource contention and overload is a KILLER when MX/batch stuff is running.
What do you mean by " ...MX/batch stuff is running" ?
Also , . . . tell them to give you a fixed reservation for RAM. . . .
They say it's complicated, it means they have to review the whole VM Infrastruceture and so on . . . .
They want justification why the have to do it.
February 16, 2017 at 9:58 am
Kevin,
I was running this query yesterday
and notice every 2-3 minutes it returns record where granted_memory is like 21GB ?1
And when I get QueryText for that SPID - it's a tiny simple SELECT that returns one row in milliseconds.
Query I run:SELECT * FROM sys.dm_exec_query_memory_grants ORDER BY granted_memory_kb DESC
Returns a record with SPID 265. granted_memory_kb = 21488896 KB !!
QueryText for SPID=265:select * from emarketing.ApplicationConfiguration where name = 'SCXXXX'
Also,
PLE drops to 30 seconds (!) a few times a day.
February 16, 2017 at 10:18 am
RVO - Thursday, February 16, 2017 9:58 AMKevin,
I was running this query yesterday
and notice every 2-3 minutes it returns a records where granted_memory is like 21GB ?1
And when I get QueryText for that SPID - it's a tiny simple SELECT that returns one row in milliseconds.Query I run:
SELECT * FROM sys.dm_exec_query_memory_grants ORDER BY granted_memory_kb DESC
Returns a record with SPID 265. granted_memory_kb = 21488896 KB !!
QueryText for SPID=265:select * from emarketing.ApplicationConfiguration where name = 'SCXXXX'
Could be bad statistics. What does the execution plan look like? does it show a lot of rows being estimated to be returned?
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.
February 16, 2017 at 12:12 pm
RVO - Thursday, February 16, 2017 11:56 AMHi bmg002,Estimated Number of Rows = 1
Is that the estimated number of rows for each of the query steps? like the table seek/scan and the select or just the select? The full execution plan may help solve this.
What is the estimated row size?
also what is the requested_memory_kb and required_memory_kb and max_used_memory_kb and used_memory_kb?
I am curious if it is using up the entire memory it is requesting.
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.
February 16, 2017 at 12:44 pm
Kevin,
So our Cache is tiny, I guess.
Can you give me a rough idea of what the numbers should be like in your opinion?
This is today's numbers.
CacheType-------Total-----------PlansTotal MBs
=================================================
Proc-------------1720------------3267.820311
Adhoc-----------5040------------163.898437
Prepared-------417-------------51.882812
Rule--------------29--------------0.960937
Trigger----------42--------------15.296875
ReplProc--------3---------------0.140625
View------------43--------------245.726562
UsrTab----------20--------------0.656250
February 17, 2017 at 3:18 pm
RVO - Thursday, February 16, 2017 9:58 AMKevin,
I was running this query yesterday
and notice every 2-3 minutes it returns record where granted_memory is like 21GB ?1
And when I get QueryText for that SPID - it's a tiny simple SELECT that returns one row in milliseconds.Query I run:
SELECT * FROM sys.dm_exec_query_memory_grants ORDER BY granted_memory_kb DESC
Returns a record with SPID 265. granted_memory_kb = 21488896 KB !!
QueryText for SPID=265:select * from emarketing.ApplicationConfiguration where name = 'SCXXXX'Also,
PLE drops to 30 seconds (!) a few times a day.
1) Unless you are running those two in a single statement you are not guaranteed that by the time you run the second the same query is executing.
2) Did you get the VM admins to give you reservations for this machine, even if it is for a little while to test this theory.
3) You have been going at this for EIGHT DAYS now. There are numerous consultants that likely could have found the cause of this in a matter of hours.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 21, 2017 at 9:02 am
Hi Kevin,
No. We didn't get "FIXED" Reservation for RAM yet.
It's not going to be busy. I am sure we'll get a lot of resistance from Infrastructure team.
Are you suggesting to get "fixed" reservation only for RAM?
Not CPU?
February 21, 2017 at 9:35 am
Guys,
From all your responses, it looks like shrinking TEMPDB is worth trying.
My question is:
What should I do right after SHRINKING?
Watch when TEMPDB files starting to grow a lot. Let's say 5 GB to 30 GB.
And do what? Try to guess which SQL caused that TEMPDB files dramatic growth?
February 21, 2017 at 9:36 am
RVO - Tuesday, February 21, 2017 9:02 AMHi Kevin,No. We didn't get "FIXED" Reservation for RAM yet.
It's not going to be busy. I am sure we'll get a lot of resistance from Infrastructure team.
Are you suggesting to get "fixed" reservation only for RAM?
Not CPU?
I already answered that question a ways back in the thread:
tell them to give you a fixed reservation for RAM. Do the same for every resource that can be reserved. You simply cannot be successful with your production SQL Servers having their main resources throttled or yanked out from under them
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 21, 2017 at 1:03 pm
We just had a meeting with Infrastructure team.
They say there is no evidence of "main resources throttled or yanked out from under them".
They say they monitor it. It never happened.
If I still insist - it will sound like I don't trust them . . .
February 21, 2017 at 1:28 pm
We are still just flopping and twitching here on the forum, and are no closer to a resolution. Some likely final comments:
1) shrinking tempdb cannot possibly help improve performance. If space is needed it will simply grow again, slowly if you don't have instant file initialization on.
2) You can find queries to see what is consuming tempdb online.
3) If you are not on a recent build of your version of SQL Server consider getting there.
4) You identified a 21+GB memory grant. Find out why that happened and make it stop.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 21, 2017 at 2:03 pm
Your original post was roughly 2 weeks ago.
I am assuming that performance is currently slower on that server, correct?
My thoughts at this point would be to prove if it is SQL or the VM. Since it has been 2 weeks, you likely have the slowness again. Look at open transactions and open connections and for databases that have done some form of autogrow. Check both the SQL logs and windows logs for anything unusual. Even check task manager and perfmon to see how much of your memory is being paged to disk.
And as TheSQLGuru pointed out - make sure you are on the latest SP's and CU's.
What does your server look like as per various suggestions on the forum (active queries, open transactions, etc) and does running the DBCC commands from page 1 of this thread by jonathan.ellison offer any help?
How do your statistics look? and how frequently do you upadte them? Does force-updating them bring the performance back to post-reboot levels?
Does restarting the SQL Server Instance (not the VM) have any impact on performance?
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.
February 22, 2017 at 8:01 am
Performance did not get substantially worse yet since last reboot on January 29th.
My guess one of these things could help:
----We ran UPDATE STATISTICS 2-3 times since last reboot
----Moved Transaction Log file of one of busy databases to a proper drive (before it was sitting together with data files)
Maybe I was wrong when said performance gradually gets worse.
I'm afraid it might be pretty sudden.
Like 2-3 days of slower than usual and then big slowdown.
We don't see a big Disk latency now. Like we saw last time before reboot.
Overall it stays on the same level it was right after reboot.
February 22, 2017 at 8:15 am
RVO - Wednesday, February 22, 2017 8:01 AMPerformance did not get substantially worse yet since last reboot on January 29th.
My guess one of these things could help:
----We ran UPDATE STATISTICS 2-3 times since last reboot
----Moved Transaction Log file of one of busy databases to a proper drive (before it was sitting together with data files)Maybe I was wrong when said performance gradually gets worse.
I'm afraid it might be pretty sudden.
Like 2-3 days of slower than usual and then big slowdown.
You say you updated statistics 2 to 3 times since last reboot. How frequently were you doing this before? I believe best practice is to update these weekly. Bad statistics will generate bad execution plans which will give you poor performance.
And if disk I/O was your bottleneck before, moving your logs to a different drive will improve performance. Presuming it was disk I/O, did you move TEMPDB to it's own disk as well?
Mind you, best practice is to have log and data on separate disk and each DB should have its own disk as well.
Are your databases in FULL recovery mode? If so, how often do you back up the transaction logs? Excessively large log files that are not being backed up will slow down your database gradually I believe.
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.
Viewing 15 posts - 46 through 60 (of 114 total)
You must be logged in to reply to this topic. Login to reply