Detecting Memory Pressure – SQL in Sixty Seconds #186

Detecting Memory Pressure - SQL in Sixty Seconds #186 186-MemoryPressure-yt-800x450 Earlier I wrote a blog post and created a video on CPU Running 100% – SQL in Sixty Seconds #185. Lots of good feedback, I had received from this video. Lots of people who watched the video also asked me to build a similar video for Memory. Let us see today a video where I explain how to detect memory pressure.

Detecting Memory Pressure

If you want the scripts used in this blog post, you can get those scripts from here: SQL SERVER – 3 Queries to Detect Memory Issues

Once you find the culprit you can apply the final step of Fixing Query Performance.  Here are a few methods of how you can fix poor performing queries.

  • Wait statistics of the session
  • Scheduler workload
  • IO stalling queries
  • Memory grant for session
  • Blocking scenarios
  • Optional Max degree of parallelism for query
  • Execution plan operators consume a lot of Memory
  • Ad-hoc workload of the server
  • Parameter sniffing configuration
  • etc.

If you have any questions, you can always reach out to me on Twitter. Query tuning is not in the scope of this blog post, however, you are interested in this subject, you can watch my videos on YouTube about query tuning. If at any point in time, you are stuck or not able to move forward with your query tuning exercise, you can always reach out to me to help you. I usually answer very quickly to all the emails if I am not busy with Comprehensive Database Performance Health Check.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

, , ,
Previous Post
CPU Running 100% – SQL in Sixty Seconds #185
Next Post
Get Current Time Zone – SQL in Sixty Seconds #187

Related Posts

1 Comment. Leave new

  • Thanks Pinal for writing such a nice blogs I am reading your blog since last 10 years.

    Please help me in understanding interesting case
    SQL server is running with SSRS and having OS memory 32 gb

    SQL Memory : minimum =0 Maximum=24 GB
    OS Memory 32 GB Every time 15 percent memory free at OS level

    Server reboot (not clean) after that CPU utilization increased 3 times but memory is still stable

    Total and Target server Memory is 24 GB at 05 PM
    Total and Target Server Memory become 12 GB at 05:10 PM without any Change or restart . SQL server is running on VM environment but Memory is Static so VM is not taking any memory

    1) Why Memory Released form SQL Server where as 15 percent memory was still free at OS level

    2) Why 50% percent memory is released and will it can cause High CPU?

    What is your opinion on this

    Best regards
    Mahesh Kumar

    Reply

Leave a Reply

Menu