April 1, 2015 at 3:43 am
I need expertise help in giving some pointer to identity SQL memory issues.
I have environment setup on AWS.
Config: Windows Server 2012, 16 core, 30 GB RAM(24 GB allocated to SQL), SQL 2012.
Problem Statement:
With above config we experience server load when multiple queries are running. We are dealing with huge amount of data (consider single table has 60 million of records, we have 5-6 databases with this much volume.). Further investigation lead us to memory issue where we observed server facing excessive paging & avg disk queue length reach to 1000. We verified performance counters but not much help.
Appreciate expertise help to diagnosis the issue.
Abhijit - http://abhijitmore.wordpress.com
April 1, 2015 at 3:46 am
What, exactly, do you mean by 'excessive paging'?
Disk queue length is a useless counter, it tells you nothing of value.
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
April 1, 2015 at 4:40 am
I ran the performance counter and checked the reports and it shows "the system is experiencing excessive memory pressure". I am not sure which other counter should I look into:doze:
Abhijit - http://abhijitmore.wordpress.com
April 1, 2015 at 5:24 am
I can't see your report, I don't know what counters it's looking at and I can't see your screen. So unless you can provide a lot more details, there's little chance that anyone is going to be able to help.
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
April 1, 2015 at 5:30 am
I won't be able to publish all details...but hope this helps
Abhijit - http://abhijitmore.wordpress.com
April 1, 2015 at 5:35 am
Nope. Tells me nothing at all.
What counter, what values, what pattern? That's to start. What, other than the attached statement, are you basing the conclusion of memory pressure on?
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
April 1, 2015 at 5:35 am
Quick questions, what is the max memory settings for the sql server, what other services are running on the server (turn off anything not needed!)?, are you using RDP (kill all sessions!)? or are you logged on in a desktop session (log out!)?
😎
April 1, 2015 at 5:43 am
GilaMonster (4/1/2015)
Disk queue length is a useless counter, it tells you nothing of value.
Hi Gail,
Unrelated to the question, slightly off topic but, could you please mention the counters that we should actually use to get some meaningful data when diagnosting the perfomance issues like this? Also, as you pointed out that Disk Queue Length is of no value, should we stop looking at it ? Thanks.
April 1, 2015 at 5:46 am
Eirikur Eiriksson (4/1/2015)
Quick questions, what is the max memory settings for the sql server, what other services are running on the server (turn off anything not needed!)?, are you using RDP (kill all sessions!)? or are you logged on in a desktop session (log out!)?😎
max memory set to 24 GB. there are no other services, yes we are doing RDP.
em looking into Buffer Cache hit ratio, Page Life Expectancy, CheckpointPages/sec, Memory Grants Pending, Total server memory, Target server memory. but all sounds acceptable values.
I observed problem when I run 2 simultaneously or single heavy query on the server. sometime server response time is quick.
for example,
Single Query 1 running on 60 mil data took 13 min. But if I initiate any other query it takes 56 mins.
With multiple queries server responds badly.
Abhijit - http://abhijitmore.wordpress.com
April 1, 2015 at 5:49 am
To begin to get an understanding of what is needed to help out, I'd like to suggest getting a copy of the book "Troubleshooting for the Accidental DBA." It's free to download and will give you a lot of the initial guidance for the types of counters and information you, and we, need to figure out what's going on.
"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
April 1, 2015 at 5:49 am
Abhijit More (4/1/2015)
Eirikur Eiriksson (4/1/2015)
Quick questions, what is the max memory settings for the sql server, what other services are running on the server (turn off anything not needed!)?, are you using RDP (kill all sessions!)? or are you logged on in a desktop session (log out!)?😎
max memory set to 24 GB. there are no other services, yes we are doing RDP.
em looking into Buffer Cache hit ratio, Page Life Expectancy, CheckpointPages/sec, Memory Grants Pending, Total server memory, Target server memory. but all sounds acceptable values.
Can you tell us what each of those say when you're running more than 1 query?
I observed problem when I run 2 simultaneously or single heavy query on the server. sometime server response time is quick.
for example,
Single Query 1 running on 60 mil data took 13 min. But if I initiate any other query it takes 56 mins.
With multiple queries server responds badly.
What are the queries? What are their execution plans? Does this hold true if you run a simple query that pulls back 3 columns from a small table?
April 1, 2015 at 5:53 am
Abhijit, get the following book, read through chapter 1 to start and apply what it discusses to your system. That should get you at least to the point of being able to identify where something might be wrong.
http://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
April 1, 2015 at 5:54 am
Best performance trace template I ever had. Andy Warren gave it to me.
April 1, 2015 at 7:57 am
Abhijit More (4/1/2015)
Eirikur Eiriksson (4/1/2015)
Quick questions, what is the max memory settings for the sql server, what other services are running on the server (turn off anything not needed!)?, are you using RDP (kill all sessions!)? or are you logged on in a desktop session (log out!)?😎
max memory set to 24 GB. there are no other services, yes we are doing RDP.
em looking into Buffer Cache hit ratio, Page Life Expectancy, CheckpointPages/sec, Memory Grants Pending, Total server memory, Target server memory. but all sounds acceptable values.
I observed problem when I run 2 simultaneously or single heavy query on the server. sometime server response time is quick.
for example,
Single Query 1 running on 60 mil data took 13 min. But if I initiate any other query it takes 56 mins.
With multiple queries server responds badly.
Some quick observations. 24GB seems to be too high for a system with 30gb memory. I could be fine, but 22GB seems better for me.
That said, your data of 60 million records does not seem like that much. If multiple queries are running and it suddenly takes 56 minutes, have you bothered to look at execution plans, statistics, disk io, locking and blocking?
The query that is producing this performance, is it a query that was created by something like entity framework? Does the query hit nested views? Have you verified cpu use during these query runs (little things like the power plan can massively impact query performance and cause the type of thing you are seeing).
Above all else though, it really seems more like an opportunity to tune this code rather than trying to troubleshoot server settings.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 1, 2015 at 9:14 am
Abhijit More (4/1/2015)
Eirikur Eiriksson (4/1/2015)
Quick questions, what is the max memory settings for the sql server, what other services are running on the server (turn off anything not needed!)?, are you using RDP (kill all sessions!)? or are you logged on in a desktop session (log out!)?😎
max memory set to 24 GB. there are no other services, yes we are doing RDP.
em looking into Buffer Cache hit ratio, Page Life Expectancy, CheckpointPages/sec, Memory Grants Pending, Total server memory, Target server memory. but all sounds acceptable values.
I observed problem when I run 2 simultaneously or single heavy query on the server. sometime server response time is quick.
for example,
Single Query 1 running on 60 mil data took 13 min. But if I initiate any other query it takes 56 mins.
With multiple queries server responds badly.
Quick point, RDP onto a memory starved system which doesn't have dedicated graphics hardware is about the last thing you want to do, looking at those counters is not going to be too helpful either as there is roughly only one fix, add/free up memory/lower the max mem for the sql. If your host OS craps out, SQL Server Service will not work regardless of how much "dedicated" memory SQL Server Service has.
😎
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply