Are you over 18 and want to see adult content?
More Annotations
![A complete backup of voxfree.narod.ru](https://www.archivebay.com/archive2/5310385a-51aa-4a0a-b997-390a9bc78187.png)
A complete backup of voxfree.narod.ru
Are you over 18 and want to see adult content?
![A complete backup of paracurarelalma.blogspot.com](https://www.archivebay.com/archive2/515d4111-794e-4b2c-8aef-4b2b76ba951f.png)
A complete backup of paracurarelalma.blogspot.com
Are you over 18 and want to see adult content?
![A complete backup of localgov59.in.th](https://www.archivebay.com/archive2/47d8fa0a-4be1-418b-92c4-9f7179d84296.png)
A complete backup of localgov59.in.th
Are you over 18 and want to see adult content?
![A complete backup of themoneymanual.com](https://www.archivebay.com/archive2/55818d64-875d-4659-b65b-4a143874157e.png)
A complete backup of themoneymanual.com
Are you over 18 and want to see adult content?
![A complete backup of hereugetit.blogspot.com](https://www.archivebay.com/archive2/2958f587-13dc-4d20-9ca4-1c5f13440559.png)
A complete backup of hereugetit.blogspot.com
Are you over 18 and want to see adult content?
Favourite Annotations
![A complete backup of americanrugbypro.info](https://www.archivebay.com/archive2/f70ae2ad-15c1-4ca1-9505-bb58d7e0d39c.png)
A complete backup of americanrugbypro.info
Are you over 18 and want to see adult content?
![A complete backup of freestyleusa.com](https://www.archivebay.com/archive2/e1fb3bd0-ff92-46e6-a528-749e4ffa6dc6.png)
A complete backup of freestyleusa.com
Are you over 18 and want to see adult content?
![A complete backup of australianflying.com.au](https://www.archivebay.com/archive2/abbc887d-8644-408e-9a93-229075ed7cc1.png)
A complete backup of australianflying.com.au
Are you over 18 and want to see adult content?
Text
ARTHUR'S BLOG
This is a series I’m writing as I prepare for SQL Saturday Chicago 2019. My session is called Performance tuning with SQL Server memory grants.. In my last post, I laid out three ideas for the flow. Now it’s time to pick. I’ll move the ideas from post 1 into slides, then I’ll write the final wrap-up slide. Each idea will get its own slide, then I’ll work on developing some content on MEMORY GRANTS PART 4: THE RESOURCE SEMAPHORE SQL Server only has so much memory to distribute to its queries. To decide who gets that memory, by default there’s two things called semaphores in SQL Server. Let’s take a look at the semaphores before we go any deeper. SELECT resource_semaphore_id, total_memory_kb, available_memory_kb, grantee_count, waiter_count, pool_id FROMsys.dm_exec
ONE BAD T-SQL PRACTICE: ISNULL IN THE WHERE CLAUSE The alternative: WHERE Type = 3 or Type is null. That’s a better alternative, but don’t take my word for it. Let’s look at the example again. set statistics io on; SELECT * FROM Teachers WHERE Type = 3 or Type is null. Statistics and execution plan: Table 'Teachers'.Scan count 2,
QUERY STORE INTERNALS QUERY HASH: YOUR QUERY TEXT’S FINGERPRINT Query hash: your query text’s fingerprint. Let’s talk about execution plans and query attributes. Query hash is a way to describe queries that have the same query text, excluding the parameters. I think the best way to show this is examples, so I’ll write some queries and show you the query hashes on my system.MEMORY GRANT
Here’s the new memory grant: Now that we included a larger column in our aggregate, our query requested a couple hundred extra MBs of memory. It only used 20 MB but requested much more, because the Body column is nvarchar (max). If you’re interested in memory grants anddata types, I
BE CAUTIOUS WHEN USING: OPTION(RECOMPILE) OPTIMIZE FOR AD HOC VS SP_EXECUTESQL? MY ALTERNATIVE TO SP_WHOISACTIVE For 99.9% of servers, you want to use sp_WhoIsActive. It’s more user-friendly, it’s more accurate, and it has many more DMVs used to gather information. There’s also the matter of the sql_text vs event_info. In sp_WhoIsActive, you’re seeing the exact snippet ofsql text that’s
WHY NOLOCK COULD BITE YOU If you aren’t familiar with NOLOCK, it’s a query hint that can be applied to each table in a query. This hint tells SQL Server to change isolation level from Read Committed to Read Uncommitted. In plainer words, the query can now read data before and during the process of writing to the storage. The main reason that this is bad is that yourARTHUR'S BLOG
This is a series I’m writing as I prepare for SQL Saturday Chicago 2019. My session is called Performance tuning with SQL Server memory grants.. In my last post, I laid out three ideas for the flow. Now it’s time to pick. I’ll move the ideas from post 1 into slides, then I’ll write the final wrap-up slide. Each idea will get its own slide, then I’ll work on developing some content on MEMORY GRANTS PART 4: THE RESOURCE SEMAPHORE SQL Server only has so much memory to distribute to its queries. To decide who gets that memory, by default there’s two things called semaphores in SQL Server. Let’s take a look at the semaphores before we go any deeper. SELECT resource_semaphore_id, total_memory_kb, available_memory_kb, grantee_count, waiter_count, pool_id FROMsys.dm_exec
ONE BAD T-SQL PRACTICE: ISNULL IN THE WHERE CLAUSE The alternative: WHERE Type = 3 or Type is null. That’s a better alternative, but don’t take my word for it. Let’s look at the example again. set statistics io on; SELECT * FROM Teachers WHERE Type = 3 or Type is null. Statistics and execution plan: Table 'Teachers'.Scan count 2,
QUERY STORE INTERNALS QUERY HASH: YOUR QUERY TEXT’S FINGERPRINT Query hash: your query text’s fingerprint. Let’s talk about execution plans and query attributes. Query hash is a way to describe queries that have the same query text, excluding the parameters. I think the best way to show this is examples, so I’ll write some queries and show you the query hashes on my system.MEMORY GRANT
Here’s the new memory grant: Now that we included a larger column in our aggregate, our query requested a couple hundred extra MBs of memory. It only used 20 MB but requested much more, because the Body column is nvarchar (max). If you’re interested in memory grants anddata types, I
BE CAUTIOUS WHEN USING: OPTION(RECOMPILE) OPTIMIZE FOR AD HOC VS SP_EXECUTESQL? MY ALTERNATIVE TO SP_WHOISACTIVE For 99.9% of servers, you want to use sp_WhoIsActive. It’s more user-friendly, it’s more accurate, and it has many more DMVs used to gather information. There’s also the matter of the sql_text vs event_info. In sp_WhoIsActive, you’re seeing the exact snippet ofsql text that’s
WHY NOLOCK COULD BITE YOU If you aren’t familiar with NOLOCK, it’s a query hint that can be applied to each table in a query. This hint tells SQL Server to change isolation level from Read Committed to Read Uncommitted. In plainer words, the query can now read data before and during the process of writing to the storage. The main reason that this is bad is that your MY ALTERNATIVE TO SP_WHOISACTIVE For 99.9% of servers, you want to use sp_WhoIsActive. It’s more user-friendly, it’s more accurate, and it has many more DMVs used to gather information. There’s also the matter of the sql_text vs event_info. In sp_WhoIsActive, you’re seeing the exact snippet ofsql text that’s
MEMORY GRANT
Here’s the new memory grant: Now that we included a larger column in our aggregate, our query requested a couple hundred extra MBs of memory. It only used 20 MB but requested much more, because the Body column is nvarchar (max). If you’re interested in memory grants anddata types, I
WHY NOLOCK COULD BITE YOU If you aren’t familiar with NOLOCK, it’s a query hint that can be applied to each table in a query. This hint tells SQL Server to change isolation level from Read Committed to Read Uncommitted. In plainer words, the query can now read data before and during the process of writing to the storage. The main reason that this is bad is that your SETTING UP YOUR OWN (FREE) LOCAL SQL SERVER FOR TESTING Setting up your own (free) local SQL Server for testing. This post is designed for anyone who wants instructions on creating a SQL Server instance on any computer you’d like. At the time of writing this post, none of these steps cost any money, just some of your time and disk space on your computer. You’ll need: A free Microsoft account. SQL SERVER MONITORING: WHAT TO START MEASURING Setting up a scheduled query to collect wait statistics. We need a table to store the wait statistics. Here’s a basic script for storing wait statistics. I added all the columns from the DMV above, along with a column for server name and the datetime when the data was inserted. CREATE TABLE wait_statistics_history (wait_statistics_idBIGINT
PARTITIONING 1: NON-PARTITION ALIGNED QUERIES Partitioning 1: Non-partition aligned queries. Here’s my take on partitioning. I’ll be focusing on getting queries to perform on partitioned tables, and not on partition maintenance, which can be its own headache. This is the first part in a series I’m planning towrite,
WHY IS THE QUERY HASH SO USEFUL? An example of how I use the query hash. I wrote a blog on what the query hash is, right here.That post describes the query hash, but it doesn’t help you if you’re looking at a slow query. EXECUTION PLANS: RETRIEVEDFROMCACHE Execution plans: RetrievedFromCache. I first came across the value for RetrievedFromCache when I was reading a confusing execution plan. At first, I thought this could be really useful. If this value was true, you could assume that another query had compiled the execution plan that your query used and your query had retrieved that plan from the PARTITIONING 3: BEYOND JUST PARTITION ELIMINATION Partitioning 3: Beyond just partition elimination. In Partitioning 2, I showed how to analyze which partitions were accessed by our Index Seek. However, we were searching the entire year’s partition for data. What if we filtered more specifically on the partitioning key? EXECUTION PLANS: MORE THAN ONE MISSING INDEX? Let’s run that query and get the actual execution plan. SQL Server is requesting a missing index with an impact of 31, on ActualCost. I’ll refer to this as missing index #1. Here’s the definition of the missing index request. USE GO CREATE NONCLUSTERED INDEX ON .ARTHUR'S BLOG
This is a series I’m writing as I prepare for SQL Saturday Chicago 2019. My session is called Performance tuning with SQL Server memory grants.. In my last post, I laid out three ideas for the flow. Now it’s time to pick. I’ll move the ideas from post 1 into slides, then I’ll write the final wrap-up slide. Each idea will get its own slide, then I’ll work on developing some content on MEMORY GRANTS PART 4: THE RESOURCE SEMAPHORE SQL Server only has so much memory to distribute to its queries. To decide who gets that memory, by default there’s two things called semaphores in SQL Server. Let’s take a look at the semaphores before we go any deeper. SELECT resource_semaphore_id, total_memory_kb, available_memory_kb, grantee_count, waiter_count, pool_id FROMsys.dm_exec
ONE BAD T-SQL PRACTICE: ISNULL IN THE WHERE CLAUSE The alternative: WHERE Type = 3 or Type is null. That’s a better alternative, but don’t take my word for it. Let’s look at the example again. set statistics io on; SELECT * FROM Teachers WHERE Type = 3 or Type is null. Statistics and execution plan: Table 'Teachers'.Scan count 2,
QUERY STORE INTERNALS QUERY HASH: YOUR QUERY TEXT’S FINGERPRINT Query hash: your query text’s fingerprint. Let’s talk about execution plans and query attributes. Query hash is a way to describe queries that have the same query text, excluding the parameters. I think the best way to show this is examples, so I’ll write some queries and show you the query hashes on my system.MEMORY GRANT
Here’s the new memory grant: Now that we included a larger column in our aggregate, our query requested a couple hundred extra MBs of memory. It only used 20 MB but requested much more, because the Body column is nvarchar (max). If you’re interested in memory grants anddata types, I
BE CAUTIOUS WHEN USING: OPTION(RECOMPILE) OPTIMIZE FOR AD HOC VS SP_EXECUTESQL? MY ALTERNATIVE TO SP_WHOISACTIVE For 99.9% of servers, you want to use sp_WhoIsActive. It’s more user-friendly, it’s more accurate, and it has many more DMVs used to gather information. There’s also the matter of the sql_text vs event_info. In sp_WhoIsActive, you’re seeing the exact snippet ofsql text that’s
WHY NOLOCK COULD BITE YOU If you aren’t familiar with NOLOCK, it’s a query hint that can be applied to each table in a query. This hint tells SQL Server to change isolation level from Read Committed to Read Uncommitted. In plainer words, the query can now read data before and during the process of writing to the storage. The main reason that this is bad is that yourARTHUR'S BLOG
This is a series I’m writing as I prepare for SQL Saturday Chicago 2019. My session is called Performance tuning with SQL Server memory grants.. In my last post, I laid out three ideas for the flow. Now it’s time to pick. I’ll move the ideas from post 1 into slides, then I’ll write the final wrap-up slide. Each idea will get its own slide, then I’ll work on developing some content on MEMORY GRANTS PART 4: THE RESOURCE SEMAPHORE SQL Server only has so much memory to distribute to its queries. To decide who gets that memory, by default there’s two things called semaphores in SQL Server. Let’s take a look at the semaphores before we go any deeper. SELECT resource_semaphore_id, total_memory_kb, available_memory_kb, grantee_count, waiter_count, pool_id FROMsys.dm_exec
ONE BAD T-SQL PRACTICE: ISNULL IN THE WHERE CLAUSE The alternative: WHERE Type = 3 or Type is null. That’s a better alternative, but don’t take my word for it. Let’s look at the example again. set statistics io on; SELECT * FROM Teachers WHERE Type = 3 or Type is null. Statistics and execution plan: Table 'Teachers'.Scan count 2,
QUERY STORE INTERNALS QUERY HASH: YOUR QUERY TEXT’S FINGERPRINT Query hash: your query text’s fingerprint. Let’s talk about execution plans and query attributes. Query hash is a way to describe queries that have the same query text, excluding the parameters. I think the best way to show this is examples, so I’ll write some queries and show you the query hashes on my system.MEMORY GRANT
Here’s the new memory grant: Now that we included a larger column in our aggregate, our query requested a couple hundred extra MBs of memory. It only used 20 MB but requested much more, because the Body column is nvarchar (max). If you’re interested in memory grants anddata types, I
BE CAUTIOUS WHEN USING: OPTION(RECOMPILE) OPTIMIZE FOR AD HOC VS SP_EXECUTESQL? MY ALTERNATIVE TO SP_WHOISACTIVE For 99.9% of servers, you want to use sp_WhoIsActive. It’s more user-friendly, it’s more accurate, and it has many more DMVs used to gather information. There’s also the matter of the sql_text vs event_info. In sp_WhoIsActive, you’re seeing the exact snippet ofsql text that’s
WHY NOLOCK COULD BITE YOU If you aren’t familiar with NOLOCK, it’s a query hint that can be applied to each table in a query. This hint tells SQL Server to change isolation level from Read Committed to Read Uncommitted. In plainer words, the query can now read data before and during the process of writing to the storage. The main reason that this is bad is that your MY ALTERNATIVE TO SP_WHOISACTIVE For 99.9% of servers, you want to use sp_WhoIsActive. It’s more user-friendly, it’s more accurate, and it has many more DMVs used to gather information. There’s also the matter of the sql_text vs event_info. In sp_WhoIsActive, you’re seeing the exact snippet ofsql text that’s
MEMORY GRANT
Here’s the new memory grant: Now that we included a larger column in our aggregate, our query requested a couple hundred extra MBs of memory. It only used 20 MB but requested much more, because the Body column is nvarchar (max). If you’re interested in memory grants anddata types, I
WHY NOLOCK COULD BITE YOU If you aren’t familiar with NOLOCK, it’s a query hint that can be applied to each table in a query. This hint tells SQL Server to change isolation level from Read Committed to Read Uncommitted. In plainer words, the query can now read data before and during the process of writing to the storage. The main reason that this is bad is that your SETTING UP YOUR OWN (FREE) LOCAL SQL SERVER FOR TESTING Setting up your own (free) local SQL Server for testing. This post is designed for anyone who wants instructions on creating a SQL Server instance on any computer you’d like. At the time of writing this post, none of these steps cost any money, just some of your time and disk space on your computer. You’ll need: A free Microsoft account. SQL SERVER MONITORING: WHAT TO START MEASURING Setting up a scheduled query to collect wait statistics. We need a table to store the wait statistics. Here’s a basic script for storing wait statistics. I added all the columns from the DMV above, along with a column for server name and the datetime when the data was inserted. CREATE TABLE wait_statistics_history (wait_statistics_idBIGINT
PARTITIONING 1: NON-PARTITION ALIGNED QUERIES Partitioning 1: Non-partition aligned queries. Here’s my take on partitioning. I’ll be focusing on getting queries to perform on partitioned tables, and not on partition maintenance, which can be its own headache. This is the first part in a series I’m planning towrite,
WHY IS THE QUERY HASH SO USEFUL? An example of how I use the query hash. I wrote a blog on what the query hash is, right here.That post describes the query hash, but it doesn’t help you if you’re looking at a slow query. EXECUTION PLANS: RETRIEVEDFROMCACHE Execution plans: RetrievedFromCache. I first came across the value for RetrievedFromCache when I was reading a confusing execution plan. At first, I thought this could be really useful. If this value was true, you could assume that another query had compiled the execution plan that your query used and your query had retrieved that plan from the PARTITIONING 3: BEYOND JUST PARTITION ELIMINATION Partitioning 3: Beyond just partition elimination. In Partitioning 2, I showed how to analyze which partitions were accessed by our Index Seek. However, we were searching the entire year’s partition for data. What if we filtered more specifically on the partitioning key? EXECUTION PLANS: MORE THAN ONE MISSING INDEX? Let’s run that query and get the actual execution plan. SQL Server is requesting a missing index with an impact of 31, on ActualCost. I’ll refer to this as missing index #1. Here’s the definition of the missing index request. USE GO CREATE NONCLUSTERED INDEX ON .Skip to content
ARTHUR'S BLOG
Stay tuned!
Menu
* Contact me
* All recent posts
* Memory grant
* It’s always parameter sniffing* Execution plans
* DMVs
PRESENTATION 5: PRESENTING PERFORMANCE TUNING WITH MEMORY GRANTS So it’s about a week after SQL Saturday Chicago 2019. The event was amazing, tons of great people and in a great venue. Here’s the details of what happened between Presentation 4 and SQL SaturdayChicago.
FINAL STEPS TO PREPARE Between the last post, presentation 4, and that day, I refined a lot of the presentation. Then I presented it to my team. They were very supportive, which was comforting. I also learned what parts I wouldneed to practice.
While practicing on Friday evening, I found that my system would freeze when I ran my resource semaphore demo. Uh oh. There wasn’t enough time for me to completely debug the demo, although I could see that when it happened, my computer’s hard drive was at 100%.RUSHED FOR TIME
Resource semaphore waits are really important for my memory grant presentation. They’re the big problem that’s caused by excessive memory grants, and I wanted to at least show what it looked like. However, with only a few hours remaining, I had to find an alternatesolution.
I decided to capture a screenshot during the issues, as a way to prove that the issue was occurring. Even if my system wouldn’t respond, I could show the screenshot. Here’s that screenshot: SQL SATURDAY CHICAGO AND MY PRESENTATION Then, on Saturday 3/23, I presented in front of a full room. I was really happy to have so many attendees, and the room had a lot ofquestions.
And, as expected I needed to use the screenshot. Not good! But it’s better than nothing, and I could show in my later demos that the queries would run with the improvements I suggested.OVERALL THOUGHTS
The presentation itself was a success, despite the demo issue. I have time now to do some troubleshooting on my laptop, and I have the screenshots for the next time I present.__
__0
__
SHARE AND ENJOY !
0Shares
__0
__
__0
__0
__
__
Author Arthur Daniels Posted on April 1, 2019March 31, 2019 Categories UncategorizedLeave a comment on
Presentation 5: Presenting Performance Tuning with Memory Grants WRITING A PRESENTATION 4: PRACTICE AND IMPROVEMENT My presentation so far is really rough. I need to improve a lot before SQL Saturday Chicago! For now, the
goals are to practice the presentation and write the demos. PRACTICE, PRACTICE, PRACTICE I’ve started to talk through what I have done with the slides. This way, I’m used to talking about the topics that will come up on the slides. I’m also practicing what I can remember during my commute. WRITING A DEMO THAT NEEDS A MEMORY GRANT I need to write at least one really good demo. I hope I can write a few more demos, but I needed a solid yet simple demo that could be used multiple times. I also wanted to highlight a possible real world scenario. This is what I came up with:Transact-SQL
SELECT TOP 500 Title FROM Posts AS p WHERE PostTypeId = 1 ORDER BYCreationDate DESC
1
2
3
4
SELECT TOP 500 TitleFROM Posts AS p
WHERE PostTypeId = 1 ORDER BY CreationDate DESC This is a query on the Stack Overflow database, on the Posts table. PostTypeId = 1 is Posts, and I’m sorting by CreationDate to find the most recent Posts. To me, this is a realistic scenario since I often find applications that want to show users the most recent activity. Assuming there’s either no indexes here or inadequate indexes, this sort will request a memory grant. More on that tomorrow! I’ll show the execution plan and how I’ll use this demo tomorrow. Stay tuned.__
__0
__
SHARE AND ENJOY !
0Shares
__0
__
__0
__0
__
__
Author Arthur Daniels Posted on March 19, 2019March 19, 2019 Categories PresentationLeave a comment on
Writing a presentation 4: Practice and improvement WRITING A PRESENTATION 3: ENTERING THE POWERPOINT WORLD This is a series I’m writing as I prepare for SQL Saturday Chicago 2019. My session is called Performance tuning with SQL Server memorygrants
.
IN MY LAST POST
,
I LAID OUT THREE IDEAS FOR THE FLOW Now it’s time to pick. I’ll move the ideas from post 1into slides, then
I’ll write the final wrap-up slide. Each idea will get its own slide, then I’ll work on developing some content on the slides thatcome to mind.
The idea I liked the most was idea #3. I’ve decided to introduce the idea of memory grants by talking about their most common operator, theSort.
Then, I’ll describe how to monitor the memory grants in an execution plan, demonstrate the issues when memory grants conflict, and show ways to performance tune those pains away. Let’s take a look at what this looks like:NEXT STEPS
So I’ve got the general flow laid out here in the outline but I don’t have clear thoughts on what I want to say for each section, and I don’t have any demos yet. The next step for me is to flesh out the ideas with some thoughts on each slide, then identify which concepts are best displayed in demos, creating those demos or borrowing them from previous blog posts. I might add more slides between completing these steps, it just depends on what the presentation needs.Stay tuned!
__
__0
__
SHARE AND ENJOY !
0Shares
__0
__
__0
__0
__
__
Author Arthur Daniels Posted on March 18, 2019March 16, 2019Categories
Presentation Leave a comment on Writing a presentation 3: Entering the PowerPoint world ONE WEEK BLOGGING HIATUS I’m taking a brief break from daily blogging, even though it hasbeen a ton of fun.
NO EXCUSES
I hope my blog has been enjoyable to read. I’ve learned a lot of things so far, it really has been a great few months of blogging. Daily work stress and the amount of hours I’ve put in over the last week and this weekend have left me exhausted. I love what I do, but the extra commitment of writing a daily blog post(s) over a weekend or after work is currently too much. Thank you to everyone who reads my blog, and thanks especially to the people who have left comments, shared my posts, and complimented my posts to me. It means so much to me to get feedback. WHERE I’M GOING FROM HERE There are no other planned posts this week while I recover from a very long week. That said, I want to continue two blog series I’ve been working on, the memory grants and the presentation I’m writing. There may be posts on those two subjects this week or next week! I feel like I’ve only scratched the surface of query memory grants and I love the idea of passing along what I’ve been learning. I also really like sharing my presentation, even when it’s very early and the information isn’t clear. Anyway, thanks for staying tuned! I’ll be re-focusing next weekend and come back to blogging after a short break.__
__0
__
SHARE AND ENJOY !
0Shares
__0
__
__0
__0
__
__
Author Arthur Daniels Posted on March 11, 2019March 10, 2019 Categories UncategorizedLeave a comment on
One week blogging hiatus FAVORITE COMMUNITY SCRIPTS AND THEIR PARAMETERS Here’s some of my favorite community scripts and how I like to run them. If you haven’t tried the optional parameters on your favorite scripts, there’s definitely some cool features to try.SP_WHOISACTIVE
Written by Adam Machanic and hosted at whoisactive.com This script is great for all kinds of performance troubleshooting. Here’s the parameter I add on: @get_plans = 1 This will slow the procedure down a bit, but it can be super useful in finding the execution plans of currently running queries. Here’s a quick before and after.Transact-SQL
sp_WhoIsActive;
1
sp_WhoIsActive;
Transact-SQL
sp_WhoIsActive @get_plans = 1;1
sp_WhoIsActive @get_plans = 1; Awesome! Now we have the query plan. It won’t contain actual row count or actual executions, although I’d stay tuned for SQL Server 2019 and see what happens for live query executions.SP_BLITZCACHE
It’s no secret that I’m a big fan of the First Responder Kit from Brent Ozar Unlimited, but this procedure in particular isamazing.
When you run sp_BlitzCache with no parameters, you get the top 10 plans from cache along with some information on your plan cache’s health. Add one of my two favorite parameters for more specificsearching.
Transact-SQL
sp_BlitzCache @MinimumExecutionCount = 100;1
sp_BlitzCache @MinimumExecutionCount = 100; Minimum execution count will only analyze queries with your number or greater executions. That’s great for troubleshooting systems with high batch requests per second, because you can exclude expensive queries that only run once or twice a day. Pick the right number basedon your system.
Transact-SQL
sp_BlitzCache @OnlyQueryHashes = ''1
sp_BlitzCache @OnlyQueryHashes = '' I’m a big fan of using the query hash to identify and track problematic queries. Check out my post for why I like it so much. If you have one or more query hashes that you want to track, you can list them here in a comma separated list. Let me know if you have other favorite procedures or parameters! Staytuned.
__
__0
__
SHARE AND ENJOY !
0Shares
__0
__
__0
__0
__
__
Author Arthur Daniels Posted on March 8, 2019March 8, 2019Categories
Uncategorized Leave a comment on Favorite community scripts and their parameters WRITING MY PRESENTATION 2: DESIGNING THE PRESENTATION This series is about my process for writing my upcoming presentationat
SQL Saturday Chicago #825.TODAY’S GOALS
Today I’m going to post some of the ideas for the flow of the presentation. These will be general ideas, based on the abstract. By the time I’m done with this presentation, these could be completely different. IDEA #1: START WITH THE PAIN THAT MEMORY GRANTS CAN CAUSERESOURCE_SEMAPHORE
It can be terrifying to feel like you don’t know what to do, and that fear only gets worse when you’re facing an unknown performanceissue.
When your system is facing too many big memory grants, a number of troubleshooting scripts won’t work. I feel like this could be catchy and interesting, but it’s risky. If I don’t have the right demo, and if I don’t pull it off right, I could miss the point. IDEA #2: START IN FAMILIAR TERRITORY, WITH A NORMAL EXECUTION PLAN ANDDEVELOP FROM THERE
In my prerequisites, I mentioned a basic knowledge of execution plans. That can mean a lot of things to different people, but as long as they are familiar with a few operators, I could work my way from there into requested and grant query memory. This has a lot of potential because it introduces my subject and then I can work into situations where you’d want to tune a query to reduce its memory grant. IDEA #3: PICK A FEW EXECUTION PLAN OPERATORS THAT REQUEST MEMORY I don’t want to spend the whole session discussing execution plan operators, but I think I could start with the Sort operator. I think people are familiar with explicit sorting, using ORDER BY. My fear here is that I want to focus on the impact on the whole SQL Server, and spending too much time on operators could burn a lot ofsession time.
TOMORROW AND THE WEEKEND I’m going to leave these ideas here and let them digest overnight. I’m planning to do some research and testing this weekend, which should help me pick (or add more ideas). What do you think? Am I missing something? Let me know!Stay tuned!
__
__0
__
SHARE AND ENJOY !
0Shares
__0
__
__0
__0
__
__
Author Arthur Daniels Posted on March 7, 2019March 7, 2019Categories
Presentation Leave a comment on Writing my presentation 2: Designing the presentation WRITING MY PRESENTATION PART 1: THE BRAINSTORM/DUMP For this session, I’m relying on a mixture of previous blog posts and new material/demos that I’ll have to write. I’ve written three full presentations, and I usually follow the same pattern. MY METHOD FOR DEVELOPING A PRESENTATION First, I write down all my ideas. Then I sort through them to tell a coherent story, and find what demos I need to write. Finally, I polish and polish, to get to a point where I’m happy with the presentation. This is not the final copy. The final copy is created after I practice the presentation and demos until they’re second-nature. That is the most time-consuming part by far, but it gives me confidence when presenting. MY CURRENT LIST OF IDEAS The session title is Performance Tuning with SQL Server Memory Grants.
This has a big influence on what ideas I want to show. Here’s the list, in no particular order: * Include the information on the max size of a memory grant from thisblog post
* Demonstrate problems with big memory grants, specifically RESOURCE_SEMAPHORE waits * What does a memory grant do * Why is a memory grant requested * How do data types and sizes affect the memory grant * Consider including information on the “excessive memory grant” warning in execution plans * PerfMon counter: Memory Grants Pending* Resource governor
* Currently running queries * Small/large semaphores * Memory grant hints * What operators in execution plans use memory grants * Minimum size of a memory grantNEXT STEP
The next step is to work on connecting the ideas to form a story through the presentation. It’s likely that I’ll have to trim that list down, and there’s probably ideas I’ve forgotten. Please feel free to give feedback as I work on this presentation. Staytuned!
__
__0
__
SHARE AND ENJOY !
0Shares
__0
__
__0
__0
__
__
Author Arthur Daniels Posted on March 6, 2019March 6, 2019Categories
Presentation Leave a comment on Writing my presentation part 1: The brainstorm/dump WRITING A NEW SESSION/PREPARING FOR SQL SATURDAY CHICAGO So, I’m presenting a session at SQL Saturday Chicago on March 23, 2019. This is a new session, called Performance tuning with SQL Servermemory grants
.
BLOGGING ABOUT WRITING A PRESENTATION I’m going to write a couple blog posts as I write this session and practice for it. I hope this is interesting to read. Tomorrow, I’ll brain dump all my ideas into a blog post. HERE’S THE SESSION ABSTRACT When you’re performance tuning queries with memory grants, you want to know where, how and why the memory is granted and used in yourquery.
In this session, we’ll talk about analyzing requested and granted memory inside execution plans, the plan cache, and in currentlyrunning queries.
We’ll also talk about troubleshooting your system when many querieshave memory grants.
Prerequisites: General knowledge of execution plans and query writingStay tuned!
__
__0
__
SHARE AND ENJOY !
0Shares
__0
__
__0
__0
__
__
Author Arthur Daniels Posted on March 5, 2019March 5, 2019 Categories PresentationLeave a comment on
Writing a new session/Preparing for SQL Saturday Chicago CTRL + F IN SSMS EXECUTION PLANS I just discovered this the other day and I had to share it. FIRST, WE NEED A QUERY IN MANAGEMENT STUDIOTransact-SQL
USE StackOverflow2010 GO SELECT * FROM Posts as p JOIN Users as u on u.Id = p.LastEditorUserId WHERE u.Id = 11
2
3
4
5
6
7
USE StackOverflow2010GO
SELECT *
FROM Posts as p
JOIN Users as u on u.Id = p.LastEditorUserIdWHERE u.Id = 1
Okay, now I’ll get the execution plan. It looks like this:Good?
Maybe.
NOW, CLICK ON THE EXECUTION PLAN AND USE THE KEYBOARD SHORTCUT, CTRL+F This menu should come up. If we open the ActualCPUms window, we get even more options. AND NOW FOR A DEMO USING IT: Let’s say we want to find all the operators that used the Posts table. Simply use Contains Posts, and use the arrows to go through the operators. See the video demo below. I recommend opening it in full screen:__
__0
__
SHARE AND ENJOY !
0Shares
__0
__
__0
__0
__
__
Author Arthur Daniels Posted on March 4, 2019March 4, 2019 Categories UncategorizedLeave a comment on
CTRL + F in SSMS execution plans SSMS TIP: SPLITTING THE SAME QUERY WINDOW This is a trick I use in SQL Server Management Studio at least once aweek.
Today’s scripts will be screenshots of Management Studio to show thetrick.
HERE’S OUR SUPER LONG SCRIPT: That’s ok, but now we want to see the SELECT from Users next to the SELECT from PostTypes. We can do that really easily with this trick. CLICK AND DRAG RIGHT HERE And now, clicking and dragging. Then scrolling a bit, and I can align the two SELECTS like this: To me, that’s a lot easier to read. You essentially get two copiesof the same window.
THEY CAN SHOW THE SAME AREA AT THE SAME TIME Okay, hope this was useful. Have a good weekend!Stay tuned!
__
__0
__
SHARE AND ENJOY !
0Shares
__0
__
__0
__0
__
__
Author Arthur Daniels Posted on March 1, 2019March 1, 2019 Categories UncategorizedLeave a comment on
SSMS tip: Splitting the same query windowPOSTS NAVIGATION
Page 1 Page 2 … Page 9Next page
FIND ME HERE
__ __
RECENT POSTS
* Presentation 5: Presenting Performance Tuning with Memory GrantsApril 1, 2019
* Writing a presentation 4: Practice and improvementMarch 19, 2019
* Writing a presentation 3: Entering the PowerPoint worldMarch 18, 2019
* One week blogging hiatusMarch 11, 2019
* Favorite community scripts and their parametersMarch 8, 2019
ARCHIVES
* April 2019
* March 2019
* February 2019
* January 2019
* December 2018
* October 2018
* July 2018
* March 2018
* January 2018
* November 2017
* July 2017
* June 2017
* March 2017
* February 2017
* December 2016
April 2020
M
T
W
T
F
S
S
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
Apr
* Contact me
* All recent posts
* Memory grant
* It’s always parameter sniffing* Execution plans
* DMVs
Arthur's blog Proudly powered by WordPress__ __
By continuing to use the site, you agree to the use of cookies. moreinformation Accept
The cookie settings on this website are set to "allow cookies" to give you the best browsing experience possible. If you continue to use this website without changing your cookie settings or you click "Accept" below then you are consenting to this.Close
Details
Copyright © 2024 ArchiveBay.com. All rights reserved. Terms of Use | Privacy Policy | DMCA | 2021 | Feedback | Advertising | RSS 2.0