There's something important. I want to share with you guys before. We get started because this is not part of the presentation. But it's something that's important to me, there is not a single person in this room.
Not one of you that doesn't have some knowledge that the rest of us can learn from.
In other words, you all know things that I don't and that only changes if you're up here and I'm out there right. It does 2 things one, I get to learn from you and that's awesome.
But the other thing that it does is it allows you to learn more about the subject. You're speaking about than you would otherwise know that by just doing it every day.
So that's a double win, I win because I get to learn from you. You win because you get to know more about your subject because you have to do a little bit more research. 'cause you don't know what kind of questions are going to come up right OK. Find something that it is that's interesting to you.
And put together a presentation on it.
And then go to your boss and say, Hey, I'd like to do a lunch and learn, you guys have those in the UK.
We have lunch and learns in our office right. Let's let's talk about what we know and then you share that knowledge with your colleagues.
And first of all they get to understand a little bit more about what it is, you do.
And you start talking to them about what they do, and you become more collaborative in your job so you get a win that way.
How many guys have a lot of local user group near you that meets regularly abunch of you the rest of you find one if there isn't one within driving distance start one seriously user groups are the lifeblood of our SQL community and it's how we all learn and grow so take.
He put together and go to the user group later and say, Hey, I'd like to do a presentation. They're going to go come on down right everybody every user group. I know is always looking for new content.
So now you become the local expert in your subject and people come to you, that you wouldn't otherwise talk to to ask you questions about the thing you just talked about.
Do you realize how much career growth is involved in that because the best jobs that are out there never make it to the job boards the best jobs are out there come from. Hey, I know somebody who knows this stuff. Let's bring'em in right so all of a sudden your opportunities grow.
Then you've got SQL Saturdays. You've got do you have that the sequel rally in the UK? Where you go from from city to city you've got SQL bits. You've got SQL Conference. That was just last week in Belgium. There are so many opportunities to speak.
It is amazing and you grow your network you get to know, people from all over the world who are interested in the same thing. You are there interested in this technology that we all play with?
My life has changed dramatically since I started doing this 15 years ago, it really has I wouldn't be here.
I wouldn't be here, I'm a little guy from Cleveland, Ohio.
Right Houma LeBron James you guys know the LeBron James Yeah. Yeah, well, he used to play basketball against my daughters boyfriend in high school.
So yeah, it's the only high school basketball games ever sold out on a regular basis.
'cause we knew then so but seriously put together presentation and start sharing your knowledge with the rest of us, we're all going to benefit that way.
We're not quite at our start time. Thank you for all coming in. I appreciate this. There's some really great speakers at this time, outside this room like Andy Kelly and Christina Lee, Oh wow. Thank you this is awesome.
Yeah, there's some seats a couple like 3 seats up here in the front row careful. This is the splash zone.
I was an actor for a lot of years, so I'm used to this happening.
Actually, that was my that was my major in college was theater.
I realized I was going to make any money doing that.
So.
I still love the theater.
I still love music. I went to the Symphony the other night Thursday night. Anybody go to Manchester. The Holy Orchestra. Mozart 31st Symphony and Procopius fifth it was marvelous.
Alright, we're at 12:05 are we ready to start in the back.
We're ready to go.
Excellent alright for those of you who don't know me already. I'm Alan White. I started a consulting practice about a year ago when I left century. One what happened to my screen there, we go, but I've been in it since 1974.
Yeah, I've been working tonight. That was the year that I decided to theater was going to be my thing. So I started working in it, and life was a whole lot. Different then wasn't. It IBM mainframes and punching punching cards and you know, writing programs in Cobol and all that.
Early 90s, I started using Sybase and fell in love with the database technology and I have been with the Microsoft Stack since SQL Server 60 came out in 1995 for the last 12 years I've been awarded Microsoft MVP award in first, the SQL server expertise now.
So, in about 3 weeks. I'll be in on the Microsoft campus in Redmond, Washington, where they'll teach us all the things that we can't talk about until the fall sometime so it's a lot of fun actually being part of that and how did that happen by getting up here in speaking seriously getting out there and?
And so hopefully we're going to get together now.
You guys all have tools, but you know, there's a whole lot of tools out there that are free.
Right my title says find and fix your troublesome queries. The fix part would take a week. I'm going to focus on the find Park. Today, 'cause If you can't find him you can't fix him.
So that's why I'm going to focus Grant Fritchey. I went to a day long session. Grant and I are close friends. We've been close friends for many, many years, but I went to a session. He did in Indianapolis last summer and he said. This no magic will fix crap code and that's absolutely true. There's nothing you can do with bad code X?
Sometimes right so, so let's talk about the tools that we have available to but first of all our our methodology. We need to know what the problem is before we can fix it right so the first thing we do is watch gather numbers user perfmon counters use our.
Then it events are trace data to see what is actually going on? Where are our bottlenecks then fix the query adjust that query either add indexes or rework the queries so that's not using 37 nested cursors because I've seen that. Not that many, but I have seen many nested.
Which caused serious problems OK so adjust the query then watch it again and it's an iterative cycle right?
So the tools that you have to watch that behavior are critical to your process.
So what are our tools available there are some great third party tools out there were not here to talk about those then so to outside the scope right? That's what the vendors pay for sessions to talk about their tools and they are some great tools. I left century. One, a year ago. I was with them for a few years and you know, I love their tool.
We're going to talk about Perfmon in profiler, yeah, I said profiler. I said that evil word. It still has value today. We're going to talk about activity monitor. We're going to talk about something that you may not have been been even aware is there. The performance dashboard report in SSMS. We're going to talk about.
When objects an extended events now the last 2 graphical query plan and query store. I don't have time in 50 minutes to cover those that's a 75 minute session with those in their grant. Fritchey did did a session on graphical query plan yesterday. I think Aaron notes, Yeah Erin Stellato talked about.
Today, there are a number of people talking about query store so I have to cut those 2 out alright.
I have a website data purfpro.com data perpro.com it's pretty simple.
Tonight or tomorrow at my hotel room. I plan I'm going to upload the slide deck and the demo scripts from this session where you and you'll see the slides for the graphical query plan and query store.
But first let's talk about Perfmon in profiler.
This is a capability that if you remember Brad McGee used to. We used to be with Redgate. Brad wrote a book about profiler and he talked about this ability if you do? What's called a replay trace.
Using trace and I don't suggest you use profiler to trace the data use a server side trace and I'll show you? How to do that, but use a server side trace capture the data data to a file go into perfmon capture your performance counters you can pull that data together into profiler.
Up the performance counter data with the trace data and see what happened when.
And then you can identify those problem queries.
So let's take a look at how we do that.
That's activity monitor will come back to that.
First of all.
I'm going to run perfmon.
Run.
Perfmon there we go.
Can you all see that?
Normally, when you come into perfmon you go to this live screen right. I don't want to do that.
What I want to talk about is the data collector sets?
If we go into user defined data collector sets, we can collect whatever we want to.
So I can say new data collector set.
And we'll call it bits.
And I'm going to create this manually.
I want performance counter data.
And here I'm going to add performance counters does this look familiar.
The same thing you do when you're running perfmon in alive mode in this case. I'm going to go to the processor object. Ann I'm going to select percent processor time total and add that to my collector set.
Now, for the for the sake of Brevity, I'm going to stop there. I'll say OK sample intervals 15 seconds. That's fine and I'm going to say next Anne. I'm going to put that into my C work per flogs directory say OK.
Next run as default save and close.
Now I have this collector set and I could start it and anytime.
I've already done this with my health check collector set the reason I want I wanted to show you. This is because what you can now do is save this as a template.
And we'll call this bits.
And will save that what does that look like if we go into demos there's bits dot XML?
It's an XML file.
Shows us where we're going to save it.
Shows this data collector 01.
And down here it shows us the counters and the counter display names.
Well, you can edit this 'cause it's just an XML file or you can.
Run it manually I've got another one that the full size, one that I normally do is called Health Check.
And health check has all these.
Right these are the counters I generally look at when I'm looking for performance issues with my SQL server.
And by running it this way, it captures it to a log file that I can then use later.
So I just go into perfmon my my health check and I say start and that starts to capture and I've already done. This work ahead of time. So I didn't waste your time with this process and in my work directory. I have some per flogs.
From it was February 1st I ran this from home. The other thing I'm going to do is I'm going to do a server side trace now? How do I do a server side trace the first thing I do is I open profiler?
And the reason I'm using trace and not extended events here is because this ability to match the SQL server activity with the performance counter data.
Doesn't exist from extended events yet?
I started talking with Vicki Harp this week here at bits about it will talk more about it. When I get out to Redmond in a couple of weeks. But I'm going to be putting in a feature request for Azure. Data studio to provide disability in with extended events because the data is already there right, it's already.
Just do this that doesn't help your SQL 2008 instances that really don't fully support extended events right and you guys are all running. SQL 2008 still right. It's OK, you can raise your hand a lot of people are so at my customer.
Paint my principle client is a software company that they still have customers running on SQL 2000 for crying out loud right so anyway. So what I'm going to do is I'm going to create a new trace.
Connect to my server, and I'm going to call it bits.
And for the template I mentioned before I'm going to use T SQL replay because that captures enough data that profiler can then join the performance data with the trace data.
I'm going to run this trace and I'm going to stop it immediately.
Why did I do that because now?
I can say export script trace definition for SQL 2005 to 17 it asks for a file name.
I'll call it replay trace def underscore bits.
It was saved.
Well, just minimize that and here's the file it's a sequel file if we open that with notepad.
There is the definition in T SQL of the replay trace all of the objects. We need for replay trace. There's a couple changes. You'll need to make you'll need to insert a file name where you want that trade stated to go. It should be a flat file.
On the server where that race is running and then the performance impact on that server is as minimal as it can be.
Is that fair does that make sense?
OK, so if you do a server that's called a server side trace. The other thing I would need to changes. I need to change this from zero to 2.
Because that 2 says when you fill up the log file that you're working on roll over into another one don't stop.
Here's the Max file size for that log file starts at 5 MB. I usually change that to 100 just for my own benefits. You can change it to a gigabyte. I don't care. It's in megabits in megabytes the number that you said here.
Right so once you've done that then you can start that trace again, I've already done that and the.
That race stated that I want is right.
Right here in perfect collection dot trace so this was the trace data that I captured at that time, so let me show you what the end result becomes.
'cause that's what we're here for right will close this and we're going to open.
That trace file.
That's up here in per flogs its that prove collection dot trace. This is everything that I ran in that session.
But now I can go to the file menu and I can say import performance data. This menu option won't. You can't select it unless there's enough data in the trace file to actually link up with the perfmon data.
So I'm going to import performance data go to 21. My data collector blog. Now it's going to ask me what performance counters do? I want well? I want percent processor time.
I want uhm I don't want processes blocked. I want batch request for second. I need to know how busy the server is and let's go with page life expectancy just for grins.
Now I've got synchronized that race activity from my server with the performance counter activity.
And if I click right there.
Down here at the bottom.
Is the query that cause that performance problem?
I don't have to buy anything that's all this all comes in the box.
Now I can troubleshoot that query and fix that performance problem. That's causing those CPU spikes at those moments that make sense.
Great.
That was what I wanted to show you in this demo.
Activity monitor.
Now I've been working with SQL server like I said, since 60, came out.
When SSMS came out with activity monitor. It was not all that useful in fact, they don't actually cost some performance problems right.
It's gotten significantly improved significantly improved so there's a lot of value that you can get from activity monitor today. That didn't exist before hopefully you guys are on the latest version of SSMS right, 17912, is it that's the latest public public.
You can filter by session ID by user system processes. You can check on resource weights. You can look at expensive queries. Both recent expensive queries and active expensive queries. There's there's a lot of value there to this tool let's stay.
There it is, you know, we've always had the graphs at the top remember that it starts presenting the graph data. When you start running activity monitors so if you're not actively running activity monitor. It won't show anything but these are all user processes? How do I know because there's a one?
That's what it what processes defaults to here's the log in if I wanted to look at system processes. I could change this to zero and those are the system processes? How many guys are still checking to see if this spid number is greater than 50.
Stop doing that there's a user process flag because SQL Server uses spid numbers greater than 50 now for system processes don't do that anymore.
But.
So these are my user processes so I can scroll through those look at the application and see what is going on there that is as useful as it always has been but here's my resource weights if I were reading now. I'm on a demo system. I don't have a tremendous workload going on this box right now, so there's not a lot.
Here in resource weights on a production server, you're going to see a lot more information. Interesting information there, so it's a quick eyeball to see where your your performance problems might exist or what they might be waiting on if I had IO issues. Those would show up here recent expense.
Edit query text here's that query that I just pointed to on that graph.
This is a recent expensive query.
It's pretty ugly 12 don't you think.
Well, you see the the in fact, if I that's another nice thing is I can right click on it, and show the execution plan.
How much fun is that?
Right? Where is it great down here a clustered index seek causing 53% of the performance on that particular query plan yes Sir?
Yes.
That's Fair.
Right and then the question is why don't we just check the top 10 queries or top 20 queries and you know instead of looking to see this, this might be a query that runs once a month absolutely true. I want to see what's going on right now. Somebody says your server is running slow OK? What's going on, it might be something anomalous like that.
Right so any number of reasons. I'm just trying to show you different ways of finding queries that are causing performance problems and this is one way.
Is the question is is this? The estimated plan or the actual plan?
Yes, it is it's it's the the actual plan is the is the estimated plan with statistics. So I'm not going to be showing you this to statistics. All it does is pulls the plan out of the plan cache which is always the estimated plan.
OK.
Is there anything active expensive running now? No. So so this is activity monitor just showing you a little bit of the facility for it. So you can. Maybe if if somebody says. Hey, you got a problem on your server. You can take a take a quick look to see what's going on.
That's available to you now.
Performance dashboard report this was introduced in I think 17th at 2.
Of SMS.
I don't know if if you guys have ever looked at this, but I looked and I went holy cow. Here's here's a tremendous tool.
For the most of my career. I've worked for companies that did not want to invest in performance monitoring tools right. I'm sure that many of you are in similar situations where every time you go. I want I need a performance monitoring tool. They say well. No, we need to spend our money over here. This is a performance monitoring tool that's available to you built into the.
Right download SSMS and you've got it right gives you CP utilization waiting waiting requests at current activity in. I think I think it's a better format to present to you then even activity monitor you can drill into details.
Jump to that.
If you right click on the server go down to reports go to standard reports in the one at the very top is performance dashboard. It's just there.
I think I selected it, yeah, I did.
So here's the activity that's been going on on my server recently.
Right the yellow is other activity other than SQL server. The Blue is SQL server on the CPU Utilization. Again, I don't have a heavily loaded system here. So I'm not looking at current waited waiting request. I can look at expensive queries by CPU for example.
And there's our fun query.
I think I have zoom it in here hold on I should have done that before we started I apologize there, it is.
So there's you know our top 2 queries are these nasty ones that I've been showing you actually still those from century one. It's the ones they always show in their demos in their product. They were written to be poorly performing so they could show off their product, but they also show off Microsoft product pretty well here's another.
I have queries bad have high CPU so you can scroll through these and you get the actual queries that are causing your server problems.
Without buying another product, it's right there.
So scroll through those you can go back.
You know by duration water my long running queries. It gives you the graph number one is of course, this one and it scrolls down again.
So there is a lot of facility here.
And hopefully you'll be able to make use of this when somebody says. Hey, I've got a problem. Just information about your databases right look. I've got a whole bunch of databases in full and a whole bunch of databases in simple recovery model is that really what I want it's a quick eyeball for you.
As a consultant I walk into new shops all the time so I don't know what the situation is going to look like when I walk in there when you guys have been have any but you been hit by Ransomware.
Nobody oh good for you have OK so it's things like this that will help you prepare for that ransomware knowing having a full system inventory knowing what all your databases are where they are how much sides. They take what the physical file names. All that kind of thing the more.
Gather ahead of that time and have again offsite backups that aren't on your network is rather important my clients customers. About 4 or 5 of them in the last few months have been hit by Ransomware. It's really it's really getting ugly and they keep saying.
I don't need offsite backups the ones I've got on the network are just fine. No, they're not 'cause they got hit by Ransomware 2 in their scrambled so but anyways. This is this is a tool that's available to you today.
For anything you want to use their.
Any questions on the dashboard yes.
How long is the historical data kept I think it's just the data that's in cash? I think it's using just DM bees.
Yeah, yeah, so when you reboot. All this has gone. This is where a third party tool comes into play. The third party tools going to store that in its own databases and report that for you so but for quick looks in your own environment. These are these are good tools.
Dynamic management objects were introduced in SQL 2005 and so they are now part of all of our ecosystems. There available in every version of SQL server, including your Azure. SQL databases right and there's a wealth of data that's their the transactional stuff, you know have separate.
What's going on with your current transactions the index related objects will talk about?
Both the indexes that you're using and their statistics as well as missing indexes. Those kinds of things right. We've got it will tell you what based on the queries that you've been running what it feels would be a viable index for you to use don't just blatantly just implement.
Suggest indexes that already exist with maybe an extra column in the include claws or something so use with caution, but also you can get your query stats in your query plans through DMV's these are important tools that are available to you and you don't need to use.
Third party tool to get it this data.
Again, if we bring up for example, these these particular examples. I pulled from the book written by Tim Ford. Ann Lewis Davidson, a while back called performance tuning with SQL server, dynamic management views it's available for.
Website so and I give you a link at the end of the deck to that book. There's a couple books. Grant Fritchey's book on execution plans that that that is excellent. He just updated it. Tim Tim told me that the creation of this book that I'm referring to.
Almost caused the divorce so I don't think it's going to be updated soon.
Writing books are hard and it take take a lot of time so anyways. I stole some of their examples. Here's a simplified? Tell me what my server is waiting on.
There's a better one from Glenn Berry if you guys are using Glen berries queries right go to SQLskills.com and look up. Glenn berries queries. He's got one that list. That excludes a whole. There's a whole lot of weight types that really don't help you that not useful in really determining what your server.
This this, this top line, you know check new checkpoint new log who cares right. But I wanted to give you the simplified version so you can see where you could build from if you wanted to do that on your own.
I need.
There we go alright.
I can bring back the query plans for important queries now before I do that. When I'm going to do is I'm going to open up a new query window and I'm going to clear out.
Right I'm going to clear out my cash because you know all those reports and things that I was running well. Those those those are queries that SQL server is running behind the scenes and they fill up the plan cache and then I'm going to run this.
The set of queries from Anna mechanic that are just ugly queries that Adam wrote just to show performance tuning capabilities. And I just want to get to have them run so that when we come back here to the DMV's.
I can now say OK let's.
'cause this is going to pull from the plan cache.
And again.
Here's the query plan for that same ugly query that I've told you about that I've shown you before.
Right.
One of the queries from Adam mechanic stuff.
Right we've got a couple of clustered index scans brought together on a hash joint this is going to cause performance problems.
So this is a way to quickly pull back all of the plans that are in your cash.
And help you identify what? What is actually causing you performance problems.
Right.
The top 3 CPU intensive queries.
Again.
It's those 2 that I told you about before from.
I'm running these jobs every 3 and 5 minutes, respectively, so these are running constantly on a regular basis. This third one was from that stash of queries that I just ran right so these are my top 3 CPU intensive queries so if somebody says Hey there.
I can run this and quickly see what my top queries are and go digging into why, why those are running now right. It could be that somebody ran something during the day that they're not supposed to run during the day that happens. A lot or it could be that it's just a bad query, it needs to be fixed.
That's really your call as to how to how to address that particular problem, but another really useful tool.
Available only through your DMV's I'm going to start a transaction and not completed.
So I've got an update here without a commit begin transaction without a commit.
Well, what are my open transactions that are causing blocking?
There they are.
Right I've got exclusive locks have got intended exclusive locks on all of these objects. All associated with the product category table. These are things that are blocked that my other users can't continue working until these are resolved.
Right so it's a way to see what my resource limitations are if you will.
Right if I go? I go back an rollback that that transaction.
If I run it again.
No results back right, I've cleared that particular problem now again in a real world environment. I'm probably going to have this is always going to return something but these are just a few examples of the DMV's that.
Tim and Lewis made available to you along with that book so again there's a link to the book.
Make use of that this it's important to know what's going on in your system and these are your window into the system and all of the commercial tools are all using the same tools right there all using DMV's to get the data.
How many guys are using extended events today?
Good not enough, but good so.
And how much time do I have about?
15. OK.
We're actually looks like we have a little time for questions so extended events were introduced in SQL 2008 in a limited form. They were introduced fully in SQL 2012, so if your systems are running SQL 2012 or above.
Except for the scenario, I showed you before where you want to match the Perfmon Data with the with the SQL server activity.
You should be using extended events. Anytime you are looking to see what's going on. On your server far more lightweight than trace. Let me give you an example in both trace and in extended events. You can filter you can say. I only want to see the activity in this database. I only want to see the activity.
Yeah, this particular user is inflicting on my system, I can filter based on these things.
On trac what happens is SQL server gathers everything.
And then once it's got it goes. Oh, I don't need this. I don't need this. I don't need this. I don't need this do you see what I'm saying it's it's an after the fact filter. It's already gathered that data it's already in Curd. The performance cost of gathering that data as opposed to extended events that says.
I only want to see the activity against this database for this user and then the extended event says oh this fired was this database in this user no don't bother was it and then so it checks and it only pulls the data, it only presents to you the data.
The filters so that alone makes it far more lightweight right and then the other thing is trace was actually built.
On top of the product in other words, the product is there and it's kind of kind of digging in and searching what's going on kind of external to the SQL server engine process, whereas extended events they built hooks in the engine?
That are triggered based on your request your requests for the extended events right so it's actually hooks built into the SQL OS that they started building building in. In 08 and they they completed in 2012, they are adding new extended events.
They introduce a new feature there's extended events associated with that as opposed to trace trace is dead. It stopped the only place traces actually continuing to grow is an analysis is an analysis service services because that is external to the SQL server engine.
Right so they still have to do trace for analysis services so.
As far as the components of extended events. It's important that you know that you know packages are just the containers that define the available objects to you. The events are the things that are going on, you know, SP batch batch completed is an event right the predicates are those filters.
Apply the actions are what do I do when I get one of these? What do you want me to do I can save it to a file I can put it to a ring buffer. Those are the 2 most those common actions. The target where where am I putting this what the actual filename for example, and the types in the maps are just ways of defining.
The objects that you're using within extended events.
So let's take a look.
How many of you have said I'm not? I don't have time to learn extended events? I'm going to use trace 'cause it's easy.
Denny Cherry said it publicly like a month ago, right so I know I would have expected to see more hands. I know I'm guilty of that as well. But.
If I look and if I come down here to management.
And there's there's a tab here for extended events.
Under sessions, I'm going to click new session. The new session. Wizard really doesn't help you at all in the new session session dialogue gives you everything you need.
So I'm going to call this session name bits.
I'm going to choose for my template what I normally do when I'm trying to figure out performance problems on a server. I choose the tuning template in trace. I'm sure sure a lot of you have done the same, though what I was talking about earlier was the replay template in profiler the replay template is there in.
As well, which is why I know that when I'm when I'm talking with Vicki about adding this functionality for extended events. It's going to be doable because all of the data is there, but I'm going to use the tuning template. I'm going to select for this case start the event session immediately after session creation and I also want to watch.
On the screen is it's captured.
When I go down to events. I now get to choose.
Customize what events I want to capture so because I chose the tuning template. It brought back. RPC completed SP statement completed an SQL batch completed well. I also want SQL statement completed so I'm going to type in SQL and all of the if eh.
Since that have SQL in the event name come up and their SQL statement completed so I'm going to add that you see how easy that is.
I need to now define my data storage. Where are we going we're going to go to target type event file?
Right and then I'm going to find a file name in this case. I'm going to go to work query tuning. We're just going to call it go to work. We re tuning and we're going to call it bits.
Just for fun.
I don't need anything in the advanced.
Also, it's going to it's maximum size is going to be one gigabyte. An it will roll over up to 5 times based on my settings here, so I've got everything I need. I'm going to click OK.
And it's configuring all of the object so I don't need to know the T SQL to do that. All of this. It helps I mean, you'll find that eventually you set develop a set of scripts that define the extended events. You're normally used to using on a daily basis for some reason my system is running a little slow.
Erekose.
And it's capturing data now.
So it's showing me name and timestamp. What good is that well if you notice down at the bottom there are details.
So down here, one of the details is statement. So I'm going to say, Well, let's. Show me the statement in there. Oh, now, I'm starting to get something useful.
Right the other way, I can go about this is I can say choose columns.
And it gives me all the available columns.
And I definitely want duration.
I want CPU time.
I might want database name.
Now, those all appear.
Is that a little bit more useful those of you? Who are used to using trace in profiler this and I can do this live because of its minimal impact on server performance. Now I don't want to run a replay trace all of that. I'm really don't want to do that because it will impact your server anything.
Right now, I'm going to go ahead and stop this session.
'cause there's a couple things I want to show you first of all.
Now that it stopped.
I can sort this ascending or descending.
My highest duration oh look at this, the statement is no why is the statement is no because this is a batch completed and I need to show the batch text?
Right so there's the batch text of what was going on here is one where the statement text was was filled right. This is these are some significant durations. So I can focus on that. But the other thing that I can do is I can right click on this and say group by this.
And now I've grouped by that statement column.
And then I can go to duration and I can say give me my Max duration.
And now I can see.
That my Max duration here for this particular statement is 11 years 1250. This one is null. That's the one because it was the batch. It was the batch that was impacted but I can look I can aggregate based on any.
I can look at average I can look at men. I can look at Max. It will help me. Find those problem queries and that's the whole goal of this session right.
And I've done that through a graphical interface.
Without using profiler on alive system, which definitely has serious performance implications on your server.
OK.
Thank you.
We do have we do have 5 minutes. I did want to bring up here are the references that I have an again at data per pro. I'm going to load up the deck and the demo scripts that I've provided here. You'll definitely want to look at the execution plans by Grant Fritchey. You want to look at the.
Restore posts by Erin Stellato Aaron was here this morning. Today was her birthday, so she left to go celebrate her birthday with her mother back in London. So Aaron in my wife share birthday. So it's always easy for me to remember Aaron's birthday or it should be right.
So down at the bottom is the link to the book from red gate with with the dynamic management views. Louis Davidson in Tim Ford, so that's extremely useful for you? Are there any questions while we have some time left?
Yes, Sir.
Yes.
Right so, so the the comment was that I that I showed CPU in total and rather than per core and there could be CPU affinity issues. There could be Newman based problems that I would want to look at that at each individual CPU and it absolutely true. I'm just trying to give a quick overview on how to find that.
All the tools you can.
Taylor them to your own individual needs, hopefully right so hopefully, I've opened the door for you so thank you so much for being here.