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
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)
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