September 8, 2015 at 8:20 pm
Hi,
I have a multiple sql instance installation which includes:
- 1 x SQL Server 2012 Ent, ver - 11.0.5623.0
min mem 0 MB
max mem 250000 MB
- 1 x SQL Server 2012 Ent, ver - 11.0.5623.0
min mem 0 MB
max mem 128000 MB
- 1 x SQL Server 2008 r2 Ent, ver - 10.50.6000.34
min mem 0 MB
max mem 50000 MB
All instances are using the same service account.
The server is running windows server 2012 r2 with 512 GB memory, the server is regularly patched.
The problem - when using the lock pages in memory option for all instances the server hammers away until it hits a point where the OS reports 200 GB free memory and then the machine stops responding.
The SQL instances report errors like the following before performing a stack dump.
Timeout occurred while waiting for latch: class 'ACCESS_METHODS_DATASET_PARENT', id 0000001CF6C7A4C0, type 4, Task 0x0000000003432188 : 40, waittime 300 seconds, flags 0x1a, owning task 0x000000000361E188. Continuing to wait.
The server itself complains that there are no resources to do anything, even though there appears to be 200 GB of memory unused?
When the lock pages in memory option is removed the issue disappears.
The server does not perform any other tasks than SQL server.
I would appreciate any feedback from others that have seen this type of issue, i was wondering if using the same service account for all the instances could be causing a problem?
September 8, 2015 at 10:10 pm
Quick questions, why use Lock Pages in Memory? Is there anything else running on the server, i.e. SSAS, SSIS or SSRS?
Have a look at these two articles
September 9, 2015 at 2:58 am
How are you determining there's 200GB free? If you're looking at Task Manager, it'll be giving you incorrect data because you're using locked pages for the SQL instances.
Try using the perfmon counter Available MB (under the memory object)
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
September 9, 2015 at 4:42 pm
I’m using the Memory\Available MBytes performance counter to track memory.
Reporting services and analysis services are also running on the machine, they seem to use a few GB.
I’ve looked at the articles above and I understand that I probably don’t need to use locked pages in memory in this case, I was just curious if anyone else had seen this issue before?
Also I was hoping someone could let me know if using the same service account for multiple instances could give me memory issues using lpim?
Thanks
September 10, 2015 at 3:26 am
Couple strange things.
If you are seeing 200GB free (not 200MB), then one or all of those SQL instances aren't using all their memory. The combined max server memory is over 400GB.
The Access Method Dataset Parent latch is parallelism related. Specifically parallel table scans. It's not memory related. You probably want to change cost threshold for parallelism to a sensible value if it's still at its default, and maybe change maxdop to limit cross-NUMA node parallelism (you haven't said anything about the number of processors, so can't give a figure)
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 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy