So this is where you can go download free script write an email to me. Many of you come afterwards. It can you share your name with us so I will share right now take a photo OK? I don't have a slide about me because what are you going to do about me I mean? What do you want to know like in a fifth standard fifth Grade I did a pretty poor.
Metamatics all I know it. You don't have to know so let's start talking about SQL server performance tuning. That's why you are here? What I am it doesn't matter only thing I want to do is that I speak louder than the guy next to us.
Everybody has a different goal, I Totale Totale understand and I'm totally fine with it. So I'm going to ask you a series of questions because there are so much interruption. People are coming in-n-out. Let's do a quick round of it, so that way we set a tone and language of over solve OK. So I'm going to ask you set of question in your to rapid fire answer me. You ever only one.
There is a power issue with my computer and any moment it can die.
II'd send the guy to get me a power adapter. He got a power adapter, which won't fit my plug.
So what will be a answer of this.
Say it loudly were very, very good at least for do charger phones are so.
I appreciate it, you're offering me that one, but that can only charge your phone. Look at the size of it OK. So now now just run this one what will be answered.
It's a 2 OK very good now, what will be answered.
Oh, you guys are smart, actually so I'm not going to zoom out and do it, what will be answer. Now come on say it loudly don't say don't be like this OK after lunch? What will be answer one plus a?
Edit who said in are very, very good, Sir I know you're not cheating.
I said good thing about him and you guys are laughing.
Now.
1 / 0 what will be answer common fast.
One good job, yeah, because remember if I don't think so. You guys studied well mathematics. I wanted to check it. Anything after the look at zero has no value haven't you learned this so if you remove zero it's one.
OK, this is the final question of the day, I don't want to waste your time there. Other things to talk see when I run this kind of thing only to see how smart you guys are after this, I will present my session based on your answer right as I have to do level setting because if you're too. Smart and I show you 22 simple stuff, then I know you will not understand but when you guys answer.
And you get everything wrong. The way you're getting it. I know what kind of demonstration. I need to show.
It is not I'm just respecting you with this thing, So what will be answer of this.
How many for error?
Or there is always a few guys you know every guy is also raising hand. I love it.
All right answer is one you know why the answer is one.
Let me teach you basic mathematics and after I teach it. I will start with SQL server. I now know how you guys are OK, So what is 4 / 4 is?
One very, very good what is 0 / 0 is one.
So now, what is 1 / 1 is?
One so that means count one and which we already know what was the answer. So this is count 11 and 1 / 0 is one so I think now you know, we're friends right. I totally understand if you guys are very really you guys really know how mathematic works good job man good job very proud of you final question of the day.
The answer of this.
It's an all remember what you learn about nonlinear past anywhere, you passed knowledge you know what happens.
You guys know OK what will be answered how many for error.
One guy OK how many for that one.
Oh come on raise the hand? How many for other answer what will be the other answer.
What is it?
Now very good how many porn all very, very good. Many of you got it right good job. You guys are very smart bunch of people I'm very, very proud of you.
It's always an error.
I don't know where you come up with it because you guys got a wrong answer only forgot got it right one more final question what will be the answer of this one.
Error.
None.
One.
Answer is zero you go you're very, very right. Thank you very much seems like your meds is very weak. I will never send my kid to learn anything to you.
She is at 9 years old, she is very smart, she learns with me now. You know one divided one gnarly zero OK very, very good. Let's start with the SQL Server 2005.
I know I just thought level, setting right, I thought I would start with you guys there. There 17 is a little difficult for you guys since like basic mathematics is very important. Still, which we should have it. You know what I'll tell you, honestly this is not real world problem. I want to talk seriously. I think we have enough fun talk and I think you guys are also not as smart as I thought but my point.
Here is that very, very simple.
How many of you has ever faced its quest not for you question? How many of you have ever faced the situation. When you upgrade your SQL server, and now it's running slow.
Good job so you have a new hardware your new SQL server. You're more memory more CPU and your SQL server runs slow who's fault is it?
Yours OK good job. Why did you upgrade there is no reason for upgrading SQL server when your magic basic mathematics is not good?
Like II will came fine so let's see a very important demo one of the most important thing. I see people struggling with the performance tuning. I'm going to show you so still SQL server has been out for a while, but I ask a lot of like when I go people. Show me a query right people. Show me query and say look at my queries running so.
And I always ask them what is what do you think is a problem?
You know what people say something one of the operator is a problem, so let's see this one right now.
What I'm going to do is that I'm going to run this query this query is going to take some time to run it and after it is when it's going to give me an execution plan so let's see what it does.
This is water easier will give me this is like a gym right, I mean, you do that.
I mean, I mean, the water between these 2 and they say water is here. You try to take a water your discord. You have to lift it like what they called Kettle Bell and you have to do, I mean? What is this you guys as many pudding me for punishment or what if this is recorded people will love what I just did. I mean would take some water.
But I think SQL bit doesn't like they want people to be fit.
Look at this query execution plan and you tell me which is the most expensive operator here.
What do you guys think is the most expensive operator here?
Nested loop right.
Tablespoon Yes,
When we look at it any query like this thing execution plan. We always try to think or what should I optimize should I optimize. This should I be optimizing. This should I be optimizing this that's not how you start. You know how you should start very, very simple thing if you have a query you should not be looking at exit.
Exaction plan is only for the people who are not so smart right who don't.
Why thank you I said thank you I totally agree with you I attended your session?
What does that mean I did not say anything I did I said bad thing about him yesterday night no?
OK, so this, this is our we saw the query execution plan. One of the most important and ignored feature is this one? What is this anybody know.
Alright let me just click it and show you.
So I disable this actual Egyptian plan. I click over here, which says live query statistics. It has been introduced since many years, but I see one of the least used feature meter. The Frick, it gives you a live view of which operator is taking maximum amount of the time and reach operator is taking least amount of the time I particularly allow to run.
Becaus this query gives us way more information than you want to see sometimes when we run the query. We think one of the operator is actually taking time. But the reality is always always very, very different. You can see live. They're going when you run the query and you can see which operatories so this is something I strongly advise but I see people.
Very little so my recommendation is that start using this feature of SQL Server 2017 or 16. I think it was in today's even the right 16 and a lot of people don't use it and I can bet you right now. Many of you know about this thing, but don't use it for your business so start using it now let.
Through of one demonstration, I'm going to do this is the my favorite one.
How many of you?
Are familiar with compatibility level?
SQL server compatibility level do you all keep your SQL server compatibility level to latest version?
Yes, or no why not?
OK let's start talking about it, I'm going to make this demonstration, simple I'm not going to go too much deep into it because my fun demo is right coming. After this so I'm going to make it very simple. One very, very easy. One when you leave your compatibility level to the previous version. That means you're telling your SQL server to use pre.
Algorithm.
Is that bad? Is it good you know what? I'm going to tell you this way. There is no right or wrong answer here. But what I like to do is that I always like to keep my compatibility level to the latest version because just like any software don't you want to upgrade it like are you confortable with the latest driving Tesla.
Having your software, which is very, very old.
No right do you want your phone to use your older version of the software know right? We all want the latest one that's why it is recommended that you go and change the compatibility level to the latest one so you can use the optimal performance. So what I'm going to show you is this and I'm going to keep it very quick because the fun demo is.
So this is for adventure works. I'm going to live that compatibility level of the previous version, which is 42012. Let's This is a stored procedure, which is out of the box available in SQL server.
So what I'm going to do, I'm going to run that query along with 27 teens compatibility level and let's see the performance of both of this query. Now, some of you perfection is say I should look at statistics. IO will look at it afterwards.
And that was for you.
So let's see those 2 querying and see the execution plan.
Well look at this, the query. Onatopp is taking 96% of the resources and query on the bottom is only taking 4 do you know why? Because the bottom query has a better algorithm to render the same data if you go and check the statistics.
Look at this oh here it is.
I need to highlight so you guys can see it look at this person table. We are reading 137, Page in a previous version. Where is right now in the latest version? We're only reading 6. That means we got a quite a lot of performance improvement becaus in a newer version newer compatibility level, we have a better algorithm which.
Yeah, that's why your query is fast.
So this is what everybody believes this is why people say, Oh, Yeah, you need to go and update your compatibility level. I see this all the time, no this is not the reason. This is not the reason you should update your compatibility level. There are other reason why you want your compatibility level to the.
And that's what I'm going to talk about if you think we change compatibility level just for performance that is totaly misunderstanding. I attended 3 session yesterday and all of them talked about this. I was really laughing. I was like no that's not the reason you would do that.
Yeah, no no yeah, you, you know you didn't talk about it. Actually, there was 2 errors, but you know how to handle them value that we talk afterwards.
Yeah, so really this is not the reason you should change compatibility level. Let's try something else. There is another reason why you want your SQL server to use our latest compatibility level. I go I have a 400 plus customer. I entertained last year not entertained a consultant last year.
Is sometimes I feel? I don't know why they hire me and like sometimes I did people talk to talking to me for half an hour. I said, are we going to look at your sequencer or not, he said. No, we're having fun. I said, Yeah. That's good, then pay me. More yeah, so being a send up really helps more really better for your career then SQL server consultant like you know, I can promise you I make more than Eric just becaus arm.
You know what anybody when I go out. Please guy just cover me so.
I OK.
There are other reason why you want to update your compatibility level and that's what I want to talk about.
If you're running SQL Server 2017 or how many of you are running 2019 in production.
Alright only 6 and good job.
I should have taken photo at that point of time because this 6 people need to live their company. I need to inform their manager.
Yeah, I mean, it was just released 2.3 CDB was just released.
So DB yeah, this is the production version, yeah, CDs for production version. Yeah, you should install on a production version next to your real server, and run a load on the CDB and so how does it work anyway? Let me ask the question again? How many of you are running SQL Server 2019 in your production.
Alright many of you raise hand good job this time to just to come in a photograph will do anything you know, there are always people news readers are there and people behind him like like I'm giving a sad news. What are you doing actually so let's see a demonstration about why you want your compatibility level to the latest version of the SQL server. Let's see it OK.
I want to show you amazing demonstration, which I believe is about.
Adaptive join how many how many of you are familiar with adaptive join in SQL Server 2017 very, very good. How many of you use that in a production right now?
Only one.
How do you write adaptive join? What is the syntax for it?
OK, silence. I love this thing you know, Microsoft released feature 3 years ago and nobody know the syntax till today.
And you are talking about running 2019, I mean seriously.
Not a slave. Let me be very serious how many of you know how to write adaptive join in your query.
OK then this session is for you, good good so something new coming up.
Why are you laughing Sir?
You also don't know syntax right now, I knew that other reliable do you be here so let's not doing it so I'm going to show you a query? Which is for SQL Server 2014. I will run this for sequence or 20:14 living compatibility level to 1:20, then I will change the compatibility level to 1:30.
2016 and the same thing I will run it forward 140 and for 20172 thousand 17 and 19 is exactly identical query plans. So I'm not showing you 191. It's right now. It's absolutely same. One actually when I applied for this session. I thought 19 will be out right.
I thought it's a March and Microsoft is doing amazing job think about it sequels or 2002 thousand 55 years 5283 years 8 or 2.
Or that was forever and I do 20122 years.
1416. 17.
And then I thought that I thought it will come 19, but it did not come. I think they're they're really working on it. This time they're going to release bug free probably you will have no updates. Yeah, this is the version OK so.
Let's see a query, which I'm going to run 43 different compatibility level ready.
So when I run this one.
Let's see how they fail with each other.
OK, the queries really taking longer period of time done when I see the exemption plan look at the query number one.
48% of the batch query number 226 query number 326.
Correct. That means this query is better. I can show you the status ticks and Sadistics will absolutely reflect this thing, so this is the same query.
Which runs much better in a later version of the SQL server correct now look at this beauty part in a SQL Server 2012.
In SQL Server 20 or 2014.
Query optimizer thought.
Mark join is the best way to write this query here. It is in 2016. It thought has joined has met is the best way to write this query in later version of 2017.
It says adaptive join so adaptive join what kind of joint is this.
What is it?
Is it inner join outer join? It definitely not join it's easier? Is it hash match is it? What kind of joint is it?
OK let's figure it out OK. Yes, Sir or the second I mean. Sorry.
This is more join this is hash join this is.
Actually, adaptive joint bought it is doing hash join you guys see that so technically this is has joined you guys are not excited fine fine. That's good job no problem. Now let me change the parameter. Let's let's assume I run. This query with a different set of parameters, so I'm just changing what I'm passing.
Now you see what happens, I'm running the same query one more time.
Done.
OK, on a top you can see it's still more to join on a bottom. It's a hash join an finally do you know? What kind of joint is this?
If you look at it, it's still adaptive join right.
Let me mouse over.
Nested loop join.
SQL server knows what kind of join is most optimal for you and automatically changes for you. You do not have to write those kind of hints which you right. I see lot of people. You know in a performance during class. You know what annoys me. Most the guy who comes in says when I see the query and you say.
With a lot of plan do you know what they will say you know what? I think Nested loop should be has joined can you provide a has as much hint I get all the time when I hear this thing you know what I do. I think that customer is really can use my help you don't.
Any of this without doing this thing our SQL server takes care of it for you So what is difference between these 3 query see in earlier case if you look at it SQL server is still using the same kind of join but in a newer case you can see SQL server automatically change what it needs to do isn't it?
What is the one thing different between top query and bottom query?
What you guys think?
Compatibility level that's it. It is that simple, guys come on Energie. Show me energee. How many of you understand what is compatibility level now good job OK So what it is is that it actually tells SQL server to what to use algorithm to deliver your query and results and in the first case it was older algorithm.
Yes, it was still older in the latest one becaus in a 2017 the adoptive join was introduced that's why it can use this intelligent way to rewrite any of the joint and always try to give you the best possible performance and the way you can achieve is if you keep your compatibility level to the.
I keep on telling people that I want you to have a latest compatibility level. The reason is not too. For your query torrent faster, but the reason so you can use the new features.
Just for new features that's why you want it not for any other reason Alright. Let's see one more. I think you guys are not feeling good about this one, you guys are pretty dead.
OK, what should I tell you so you guys can come back to the picture.
OK let's do this thing I think it's becoming very, very boring. I need a volunteer with going to volunteer.
OK, you OK come here. Let's see so he's a volunteer. I am going to do a task and I want to make sure you don't see whatever I do first so please come that's why I'm calling you and you come fordable coming on a stage OK very, very good stand here.
No no no don't do that, yeah, he says look at this so don't rush. Unlike OK, you will read it now. You go little bit that site further OK now you look says front turn 90 degree right.
And 10:45 degree further.
Right side OK send there alright what we're going to do is this and he's not going to see and let's see if he's smart or I am smart, but you guys are allowed to see what I'm going to do, we will figure it out in a one minute? Which one is smarter.
So I'm going to run this particular query of SQL server, and look at this.
It is for SQL server? How many of you heard the feature called memory grunt.
A good do you guys know how it works OK very, very good everybody know how it works except him so let's start now we are going to do it, what I want to do is this whenever I run. This query with compatibility level earlier version at that time and I run the query.
2014 when I run it, you can see, there is an small error about warning right. It says memory grant error. Now, if I run it again still error is there if I run it again.
Still, error is there.
I think this feature was fixed in 2017 do you guys know that if not I'm going to show you now so let's do this thing and after 32nd I will ask my volunteer to help me out so please see when I run this query for 2017 memory grant error first time will come.
And when I re run it again. It will go away ready ready for sure.
Let's run it.
And now you can see, there are OK, one more time it was in a cash.
Now you can see the error.
One more time hold on.
Please if you get cut this 30 seconds.
So now one more time we run this.
Oh, OK, fine OK now I can take your help. Let's see if you can fix it. So, in SQL Server 2017. We had a feature when you run a query. The memory grant will automatically fix it. You know what is the memory grant issue? Let me explain you so you can also participate so this is what happens when you run.
Query like let me show you I will run this query when you run a query or let me just run it for again. This is for compatibility. You can see. Now when you run it for competitive level of 2017. I run this query OK and then I will run the query and you can see, there is a small.
Bang over here or yellow bank or yellow warning and it says access if grant memory what it does is this my query only need 288 KB but SQL server is giving how much memory.
62 megabyte think about if I run thousands of this query in parallel what will happen.
What will happen if you go? What will happen?
Don't say it depends.
Or absolutely you will get a lot of resource Sema for weight and that will bring your entire system down or actually growling whole. It's going to run so slow like I usually walk out when you see me is that slow.
But that feature was fixed in a 2017.
But I think there is a bug in the system. It's not working that way. Now it's your turn I couldn't fix. It so look at what I was showing to the people. When I run. This query right and I run this query with compatibility level 140.
When I run this particular query this error should actually go away.
For earlier version it will not go away.
For earlier version for like this is a 1:30 right when I run it for 1:30. It will not go away. I get it. I get it. That's because it's 1:30 is for 20:16. This error doesn't go away look at yellow banks will remain but for 2017 when you run it. It should go away, but somehow it is not going away and I think.
Very hard to run this query. It did not fix. It now, it's your done see if you can fix it.
All yours.
Let's see what happens.
His time starts now.
No nobody's out, I already called you for help.
If you can don't do it, I will write to your manager that you are not worthy of this job.
Select the query first line number 48 to 57.
Now do what you want to do you are only allowed to press one button?
Now.
Now fix it.
Whoa.
Amazing clap for him.
Are you a good job. Thank you very, very much good here good job. You do well man. I think I? How did he fix it and I could not fix it?
How did he fix it and I could not fix it?
Say it again.
OK, so this is a hunger for all of you what I would like you guys to do is that you already know the answer and to people around you for sure, but rest of the people. This is a homework for you, please go and figure it out? What I was doing which was wrong and what he did was correct, so please go and figure it out.
One more reason to watch this videos, which will be out like in a week.
Right anybody know what he did, and why did fix except him and the first line in U 2?
Yes.
You are saying it's a setting which kicks in after one minute.
It's a one of the threading for query store.
It's for memory, I would like to see that setting once you find it.
No. No, you, maybe or something right see. I don't know the source code you probably know it may be possible if there is one you. Let me know there is none joke. I'm just making him happy.
Alright I'll tell you secret are you guys ready for it, what I was doing is this, I was selecting this entire thing.
See this so this error was coming this error will only come when you change your compatibility level. So this setting was always there, but when you re run this thing this error will go away.
That's why I was absolutely sure when you run it again. It will work fine.
I would not keep you here and you look and make you loser OK. I have other ways to make you lose.
Alright so you guys got this point right so this kind of feature you need it. That's why you want. Lettuce compatibility level, not for anything else OK. Let me show you one more feature, which is amazing? How many of you were there in my pre conference.
Many of you know, one of the demonstration, which we are seeing which was very, very dear. I'm going to re run. The demo half and then I will show you the same demo in a 2019 and this is where you will learn something new OK. Let's so hold on for this how many of you know what is scalar function?
Good do you guys know scalar function is very, very bad? What do they do bad not you know know know you go OK?
Why scalar functions are bad?
Say it again.
OK let's talk about spelling function why their bed? What is it?
What is it?
Oh, there, black box for sequels are very, very good what is it?
There should be not using a very close whi? What will happen if we use that you know very close.
What is it?
It won't work what else.
It misses the cardinality is it messes up cardinality estimator and your query will give you a wrong. Results absolutely right now. Let's see how we can fix it in a 2019. But before that. I totally get it. It should not be used in a where clause so I'm not going to use in a very close. I'm going to use in a select close let's see that.
How we can do and this time I'm going to use a SQL Server 2017 or 19 so this is a sequence or 2019 installation.
Is the previous one CDP? I thought this was latest with this morning?
Really till 9 AM this was like this.
They have to pick today only to release this so now 2.3, is out, so you can try that one as well.
Now let's see.
What I'm going to do is this?
I am going to create a very complicated function look at the function and tell me what this function do.
Very complicated function come on what does it do anybody from this side?
It take this value end?
Returns it that's it, it takes the value and returns it.
Now let me create it.
Next thing I'm going to set the compatibility level of this 22017.
Fine is a 17.
Next I'm going to run this simple query where the function is not used the function. I created is not used here. Let me just run. This query without the function and let's see how long this query takes to run it.
How long?
5 seconds now, I will use this function which is extremely simple.
Anybody want to what want me to clear the cache.
I'll do it now do you want me to clear cache or not? Yes, or no? Yes. OK, I'll do it OK? Some people said no so I will not do it.
And I am going to run the same query again, but this time, I'm going to apply the function here.
Alright let's see how long this query text to run would you guys think?
Anybody.
How long would you guys think this it's not even nowhere close and the function is so simple is going to give me output. The same number, which I'm passing in see it again.
Long how long that I knew 20 seconds very good answer you win a gift basket today.
So I'm letting this query run and I think I promise you till next presenter come this query will run.
It's grandparent that bad look at this is just going just going. It's going to go.
Forever as long as we want.
OK, I will let it run OK, I will read it run.
But if you are using 2019.
How long you guys want me to run it and stop it?
No, you tell me.
Stop how about I stop it one minute.
Actually, this query takes 9 minutes and 35 seconds, I run this morning multiple times.
But we will not run for 9 minutes will stop OK now.
50 seconds will stop.
No, I will change the compatibility level of SQL Server 22019. Now I will run the same query. One more time just look at this same function right? What you guys think it will run now in.
Let's count the time together 12345 done.
So if you were one of those guys who really had a problem with a scalar function. And when you use any of the simplest possible color scalar function in even in your select statement, even in your select statement?
This could be a problem for you so in next 10 minutes. We're going to learn one more thing right so.
Do you guys get it? Why you should change the compatibility level not just for the query performance Microsoft introduced so many new feature and all these features to take advantage of all these new features. You need to change the compatibility level to let this version if you have a real real problem with cardinality estimator or algorithm.
Is running I'll show you a shortcut? Now this is? What something you can consider but if you said your compatibility level to the previous version and the one guy who doesn't. Appreciate it just so you know, but there are some negative thing can happen when you change the compatibility level to previous one. But those are very, very minor. One of the most popular one is that if there is.
Which will what is breaking God? Have you ever ridden a code so complicated or Have you ever use the function which is so difficult that when Microsoft new version was released. They remove that kind of code. It doesn't happen. We are not that smart.
Seriously, so I strongly suggest that you guys go ahead change the compatibility level of the production system. When nobody is looking at it, let it run for a day if everything goes fine paper credit.
If something goes wrong just switch it back and don't tell anybody.
But if I would say that that would be wrong, so please test. It properly do do your unit test is check on your Dell system staging system run it on a production equal and workload take permission of your manager in change it.
Nobody does that nobody does that I feel I go to my customer and most of the 50 + 50 percent of my customer. Even doesn't have a development system. I go there and say or do you ever actually replica we said no no replica production system? I said I want to test something said sure let's do it.
I said, I do convertible he said with what?
I like II'm going to do some tests to their production. Sure, sure go ahead. Do you want username password no? I don't think username password that's not my style? I will teach you? How to do it sure. And when I said, OK, let's create but then I want to be safe right. I said how about this you take your stored procedure and copied identically and at the end. Add one right or test one.
You are done you can drop the stored procedure and he said sure so I say I get create a stored procedure. What is the name of the stored procedure get some value so get some value test one or stored procedure already exist try again. There's 2 it exists then what do you do test 11?
It exists there are so many places you can see, things which exist and then I ask him, which one is the latest one which one is real one he said. I had to check my code base. I even don't know so they don't drop the older one what they do. They go in a code and there. There are many places. I went back and I see the code he said get item.
We value by aggregate by some column underscore penal 94.
I like what is the 94 stand for is that Oh? Yeah, we remember it September 4th. We worked on it like you forgot to put here now you should've put it down. Also, 2004 right. I mean, people do that 94 I see this kind of thing in a code and at one particular time. One of the customer, he wrote and I gotta run.
E Commerce company people put in my email address for their support when their website goes down.
I was like so upset this is some random guy like looking at let's call it. Jo Jo Anna right. Jonah was just let's see assume she's buying a barbell OK and Eric has a website where there is a He's selling a barbell OK, you wanna vent on a website and say, I want to buy a barbell error came and there is a display if the error comes.
Now let's equalthirty.com in a subject like import performance unique consultation and people write it. Some porn on people ready. So I what should I do? I'm I'm really then I have to forward it to eat it? I mean, I want to suggest you better buy some monitoring tool don't use me for that right because I tell people that I responded at 30 minutes people. Take me.
Miss advantage of know what is the core miss advantage is not good word? What do what do we call that?
Take advantage of it, yeah, OK, yeah, thank you very much your English is better than me.
So, please update your compatibility level to the latest version because it is very, very important OK last one thing I want to show you in.
How many of you are than the previous session and come here to learn about the select statement mystery?
Very very good guys, I think I run out of the time again this time.
But I'm going to still show you because I have 3 minutes minutes to show you alright last 3 minutes and then you guys can go home and I will once again show you my powerpoints.
OK, do you guys want to go out it's OK or you want to learn the most coolest thing which I showed earlier right. Nobody wants to go. Let's see that alright so I'm going to go very, very quickly what I showed last time and a lot of people come to me all the time asking for that select statement improves when you create.
The performance of select statement that's what I hear and it's one of the biggest lie out there. Whoever wrote this statement. I think that guy was really the person who wanted to create I think there is a word for it who like other people to see in a pain.
What is the word saidist OK very very good he was that I can't say those words on the screen when it is being recorded, if I said, is not look good right so I will not say that you said it.
So they are the person would say index improves the performance of select I think that's 1 of the one of the funniest and lie statement. So I'm going to show you index does not improve the performance of select all the time once in awhile. It does when you are lucky, but most of the time it does not let's see that very quickly in a next to me.
Tell you the answer so my 120 second starts now, so I will show you a query.
Alright not good idea, I should have dropped it.
Linux is alright let's try I will show you quickly query. Please while I'm doing this thing, please fill up your feedback form because the guy was going to go out will not let you go without feedback form if you have at least 15 things. Please write it down. Whatever you learned OK. This is your test based on this you might get a job offer as well.
Oh, no it won't happen right. GDP rules OK good job. No, it won't happen don't worry. But if you like it or whatever you think you want to write it down write it down. So this is the query. When I run this query. You can see that it's doing a lot of logical read and there is a work table usage so matter. The fact this query is actually running much, much lower.
And fix this logical read for that I will create an index and I will call it first try index name is first. Roy I created that index. And when I run. This query now might vary is running fine only reason I'm going very, very fast here because this demonstration was in detail cover in a previous session if you are new to this one, I strong.
And watch the recording of the previous session, where I cover this thing in little bit deep. This is the bonus round for anybody who are coming up again to learn this small secret, so when I run. This query again. You can see over here. It is giving me much better performance and it is using force try index in firstly index. And when I see the messages you can see the.
Is much more reduced previously it was over 300,000? Which is gone now?
When I create a second index when I create the second index. I would tell you this index is created and I run the same query again once again. The original execution. Blonde comes back even though my second try index is not used in this query any where so my index was not used.
Messages you can see the logical reads are once again restored to very, very high. I think my secondary index was bad. That's why to check it. I drop this one again. And when I run. This query again to make sure that I was not seeing ghost at that time, I was definitely prove that I do not hallucinate and clearly.
I create the index the second try index. And once again query start giving me poor performance. It took me over 6 days to figure out the mystery of this problem. I don't and once I figure it out. I decided that I will teach this thing in my pre conference so next time.
The result of this one.
No no no, I'm going to tell you now I have a 3:30 second time he this guy has a 32nd. Yeah, it's a one the number which you are showing it's a 9 actually and it's a reverse.
So yeah, so let's see this one. The answer of this is very, very simple alright. The reason the second index, even though it was not used.
In the first query and still reduce performance worse. It was not the index. It was the status tricks of that particular index was misguiding SQL server, and to just show you I will show you this query execution plan when I go to.
Show exemption blonde XML you can see optimizer uses index or all the statistics. And here it goes. It uses statistics off second, try so that particular statistics actually misguided.
The SQL server execution plan so here you go if you have a too many indexes and you are one of those person who believe when you create indexes and it improves performance while all your life. You are wrong, OK, so go back and now start looking into your system and start dropping indexes when you have no uh.
Yes, I will tell you this go ahead and enjoy your day. Have a great sequel bits leave a feedback download your scripts to drop indexer with this we're done. Thank you very much.