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)
