Enjoy the Podcast?
Every DBA, who wants to make sure that their SQL Server runs Faster.
No one wants slow performance of their application.
In this session we will learn about three mistakes which DBA often do which kills SQL Server’s performance. We will understand what are those errors, why those particular errors are prevailing in the industry and how we can fix it.
We will explore various settings as well as code which will kill SQL Server Performance. At the end of this session every attendee will receive scripts which will help them improve their SQL Server’s performance. This session is of intermediate level – you just have to know a little bit about SQL Server and a lot more you will learn in this session. The session is carefully crafted from the real world experience for DBA so they can learn tricks which can help them to improve their server’s performance.
We will discuss about various SQL Server Settings, File Placements as well as ignored details related to SQL Server Performance.
Why I Want to Present This Session:
I have been doing SQL Server Performance Tuning consulting for a while as well as daily write on http://blog.sqlauthority.com. Every day I come across many different performance issues. This particular technology session is built from my experience of solving hundreds of performance problems for SQL Server Users.
Brent Ozar: In this session at GroupBy, Pinal Dave is going to talk about three common mistakes to kill SQL Server performance. Take it away, Pinal.
Pinal Dave: All right. Thank you very much. Thank you. I’m so excited when I had to present this one, and one of the demo sessions which I was doing today, and while I was doing it, I learned something.
So, three common mistakes. I say this, when I say three common mistakes, honestly, I could have called it 30 common mistakes or I could have called it 300 common mistakes. We all make mistakes, right? Mistakes are part of our DNA I guess.
This is what I read on the internet and really like it. “You will never make the same mistake twice. You always come up with a new one.” That’s how I have figured out how to fix things. Matter of the fact, I am the person who has learned everything by making a new mistake every single time. Matter of the fact, I know I am in the middle of a migration project for 2016.
I know a few mistakes we are going to do and most important mistakes I think the program owners are doing is not thoroughly testing our scenarios and testing the system. We know those kinds of things. But sometimes people are pretty comfortable with that and they are like we will fix it when we go.
Well, these three common mistakes – why I come up with these three? Because these three I have seen it on the industry again and again. I go for performance training consultation and I give them a list of the things to do, but as soon as we fix these three or four things, immediately the performance is so good that they say “Okay now we do not have to hire you because you fixed 20% of our performance, that is good enough for a while, we will engage with you again after two months.”
Suddenly I realize by fixing two or three important things, their performance is so improved they do not need me. I was caught into this dilemma of a consultant, “Should I be just doing these three things at the end so they get immediate performance boost?” But then it’s so hard like you know you also want to prove – as a consultant I like to prove immediately that I am a good guy, and I know how to fix your system. In this race only I sometimes lose it and once you have a SQL Server in hand, I think our patience takes over and we do what is right.
This is what Pareto told me and I think I really like what he said. We need to do principle phase. Pareto is saying 20% of the invested input is responsible for 80% of results obtained. For my sake, what I am going to demonstrate today, the three mistakes, these are the three mistakes which are common mistakes, which I see, but they do not apply to the machines which are specific case. I am not covering the disclaimer in a scenario. I am not covering the [inaudible] scenario. I am not covering those special things. I am not covering what you know. I am going to cover what a common person would do, what a normal SQL Server system who does not have expert advice has been set up. Those things we are going to see.
So let’s start with the very first. I think enough chitchat. We have enough three things to show. First mistake about Auto Create Statistics and Auto Update Statistics – this is one of my most asked questions, one of my questions which I also sometimes get confused with it because the amount of arguments I receive from the customer on this setting. I should not be discriminating with any particular point of view, but I see if the system has been running for a while for many, many years and if this setting is set to one particular state in the past, it is so difficult to convince customer to change that because things have changed around us. SQL Server has moved forward. Disc has moved forward. IO has moved forward. Matter of the fact, our phone has more CPU than some of the systems in the past. But we stick to our thought process in the past. We stick to what we have been thinking and that is what it is.
I am going to show you a demo. Let’s not talk on anything besides demo. Here it is. This is SQL Server 2014 but this thing works in ’16 also, in earlier version, and pretty much all the versions of the SQL Server. What I am going to show you next, and here it is: let us see the very first demonstration, extremely simple demonstration and very powerful. What I am doing is that I am, from the scratch, starting right now and I am going to create two databases right now. These databases I am not providing any information, anything at all, just going to create database like not so smart person would create. I know there are few people who are already buzzing on Twitter saying “Where is the Auto Growth value, where is this value, where is the file name? I know, I agree. I would not do this. But this is how the people do. This is what I see. I am just trying to mimic the normal person. I would create this database. Two people already messaged me by the time that this is not your [inaudible]. This is not about the bad practices session. That is another one. You don’t get confused. Let’s continue.
Hit Execute. It is going to create two of the databases without thinking too much. Then I am to make sure these two things I am doing. One is Auto Create Statistics on, on one database and Auto Update Statistics on, on same database. I made sure it is on and for second database I am making sure it is off. Please note this will definitely take the default values for the settings. I see a question saying “How do you do that in SQL Server Management Studio?”
Click on a database, right click and go to the options. Over here there is setting called Auto Create and Auto Update. These two settings which we are talking about, going back to the SQL, I am more comfortable there. Now, I am going to something simple. I am creating a table into one database and create one cluster and non-Cluster Index. Why? It’s not important. It’s just random stuff. This is what it is. We have a table and a Cluster Index and non-Cluster Index on it. Just simulating the real world scenario. Things are not important. I am populating it with some random data. Again data is not important at all.
What I am going to do is this. What I will do is, first of all, I am going to turn on SET STATISTICS IO. This is important for us to see. Now the beautiful part comes, where I am going to compare the execution plan. Before everybody tells me that execution plan is not a good way to compare, I know that, so hold on for that particular thought.
Yeah, so coming to this, here, let’s see the execution plan, because, remember, I am trying to play a guy who has little understanding of SQL Server and this statement is true to some extent for me. I still have limited understanding but it’s okay. That is what makes me special because with limited understanding, still I am presenting here and you are listening to me, isn’t it?
So coming to this one, I am going to run this query together. Let’s assume both the queries are giving us equal performance. If both the queries give us equal performance then the top query will take 50% and the bottom query will take 50% in our execution plan. But you already know the answer. They are not going to be the same. One query will take more percentage of the query execution cost and one will take less. More is bad because any vehicle which takes more amount of the gasoline to go to the same amount of distance – not good. Vehicle using more gas to give you same mileage is expensive. Same thing applies to SQL Server where if it uses more of those resources – not good.
So let me go to execution plan and see over here. Here it is: execution plan says 37% where statistics is on and 63% where statistics is off. Well, that means that my query where I have left the statistics on is good. That is a small triangle on my second query. Let me go and check that triangle which is on the operator and see what the triangle says.
I would move my mouse over here. If you are watching this on your screen, go all the way down on the warning. I cannot zoom it, because there are limitations but just go and check the warning. It says column with no statistics. SQL Server is telling there is no statistics on one particular column.
Seems like a natural reaction of a human would be “I should be creating the statistics,” and that is very similar scenario of the top query. They are both independent database queries and I can clearly see the top query is performing better. As I said, execution plan can be deceiving. It may not be right. I can clearly see over here Index Seek happening and Index Scan is happening. Our general understanding says Seek is better, Scan is bad. There are special cases when there is a smaller table, then Scan is okay and there are certain cases where the Scan is okay. 80% of the time where, when I see Seek I am happy, when I see Scan I initially think that there can be something improved unless proven wrong.
Now, coming back over here, I will click on Messages. Messages, is very, very interesting. Let me click on Message and I see over here the difference in the logical read. I can clearly see the logical read in the top query is much lower than the logical read in the bottom query.
Now, I think you can see, here it says 425 and 533. What is this number? Anybody would like to tell me? The number is simple. Number is number of the page which SQL Server has to read to get us this much data. In the top query, it is 425. In the bottom query it’s 533. Well, that’s the answer. That means the bottom query is expensive. Looks like statistics is what you need if you want to improve the performance. But this is where the demonstration is over and the discussion and argument and disagreement between you and me starts.
Let’s start with this one. First one: If this is so good, why SQL Server does not keep that as a default? They do keep it as a default value. That’s what one way or other way they are thinking. Most of the system, it would be needed. That’s why they are keeping it on. Second argument, which will be: Well, if it is so good, then why it is allowed to turn it off? There are special cases. Remember, the 20% cases which we don’t want to talk today like data warehousing scenarios. Maybe it’s not relevant or maybe there are special cases that when your table is updated your IO is so poor or poor little figure, either of them, it would give you so bad performance that you may want to keep it off and run it in a nightly.
But the only argument which is here to keep it off is that during the normal work hours, by any chance, you are updating a lot of data in your table, then it will fire auto update of statistics slowing down your entire IO really harming your very important critical, crucial queries. Well, if that is the case, leave it off buddies. Don’t bother me for that. How many times that happens? Just put a test on it. It’s quite possible with all the advancements, all the RAIDs, all the SANs, all the SSDs, everything jumping around nowadays. I mean I track [inaudible] what kind of discs nowadays are available, what kind of things you can do, cashing level here, cashing level there and then under cashing – I just lost track of the things people do, network compression and things.
Looking at all this, honestly I just think, leave it on, because that would not hurt immediately. If it is those 1% cases, turn it off. Do not just blindly turn it off because you inherited the database and where it was off. That is the learning which we have.
Any argument? Yes, I am ready to listen, but I would suggest do the correction now. How about we argue on my email address which I will share at the end? You know what, I am going to write to you an email address, I will tell you now, before you send it. Yes, you are probably right, you have seen this. I apologize for incorrect information. I know this thing because I know there will be two or four emails I am going to lend it, saying I think in my case I need to leave it off. If you can justify it, I am happy for you.
Let’s go to the next one. I am just talking about what I see in my consulting places, mistake number 2, File Auto Growth. Matter of the fact, this is also another clear configuration. I see a lot of people making a lot of mistakes. What is the right value for this? Matter of the fact, when I went to Brent’s Pre-Con – amazing Pre-Con, I loved it. Thank you Brent for having me there. In that, I asked Brent, “What should I do?” He gave me one interesting answer. I would try to repeat that answer but I will also add. The way I will say it that all of you will think it is my answer. Wait for the demonstration for that.
I have this old one. Yeah, sometimes people say, “Hey you have a unique ability.” I say “Yeah, I have a unique ability to take somebody’s answer and make it mine, no problem.” As long as I am giving credit, isn’t that a good thing to do? Yeah.
Brent Ozar: Putting your voice too. You bring your voice and your personality to it. It makes it totally different.
Pinal Dave: Absolutely. Yeah, that’s a skill. It’s like a feature. Not a bug. The most important thing, I never forget to give credit. Here is the demonstration where I am now little bit smarter and I am creating my database with a file name. Still I should have more things. Those three people who write to me on Twitter, I see on my second screen, over here, if you do not know. I keep on saying [inaudible] I didn’t talk about file Autoarowth, this is a lesson for you. I knew I am going to talk about it now. Take back your words, take back your tweet, delete it, anything.
Coming to this one, now, I am creating a regular database. I created a regular database. I call it default TBY, because I can name it. In a demo, you can name it. I will just create a table in this one. I am populating 1 million records with one additional zero. Yes, now it’s 1 million. What I am going to do, I am going to populate 1 million records into my table.
Now you will tell me “Why I am doing this thing? There is no context. There is no story? You know what if you are ever going to complain that this demonstration is not leading you anywhere, you should have never watched the movie Sixth Sense, because you sit through that movie for two hours without asking this question to the director. Very similar to that, we turn with the 15 seconds of the experiment. This table where I inserted the data, 1 million data, it took 15 seconds of the time. That is good. Well, what can I say? It’s bad? Good? I have nothing to compare, nothing to relate to. But yes, it took 15 seconds of the time. That’s one thing.
Now I want to show you another thing and I am going to run this demo first and explain to you what is different. I will give you a quick hint. There is one word here called File Growth. There is another word called File Growth and that is the only change. I am going to run that right now for a second. Once the query is over, I am going to do the same experiment one more time. Let us see, where we can go and do it. Here, I am creating the same table, default table and I will insert one million record. As we know it took last time 15 seconds of the time. We have 15 seconds, so anybody wants to grab popcorn can go grab popcorn. There is a problem here. I made a mistake. I had one zero less. It’s okay, I am going to reinsert it with one more zero. Let us see how long it takes.
Well, there is already some data into the table which we already know and it is done. 6 seconds. Think about it this way: why it took 6 seconds and earlier it took 15 seconds? I mean, it’s like putting a movie on fast forward and seeing the end of the movie first and then coming back to this one. That is what we are going to do just like Sixth Sense.
Here it is. There are two changes, only two identical scenarios. Now, let us slow down. In identical scenarios, scenario number one, we have a table, created database, inserted a 1 million record, took 15 seconds. Second time, I created database, only change here is file growth and initial size of the file. By changing these two settings, I have improved a lot of performance of my insert. Matter of the fact, 6 seconds to 15 seconds. It’s almost a lot of improvement. It’s so hard to do math. It’s a 200% improvement. Yeah, so a lot of improvement. It’s just 15 to 6. Yeah, so good thing.
Now my question is: that if this is such a good, what did I change? How did I change? I will go over here and click – this is one thing sometime. I wish the SQL Server team gives us a fresh part, someday they will fix it …
Brent Ozar: Yeah.
Pinal Dave: I am so confident – oh coming to this one I should have clicked on file. Look at here, the Auto Growth is 1 and initial size is 1 and that’s how we are running. You can see my log file went a little bit higher, because I inserted certain data. Initially it was not like that. Yeah, Auto Growth is set up on this database which is called Optimize DB. When I go to my default database right click, and see files here, it is very little. 1 MB, very little and my Glock file is growing to 345 and it is 41. This is the Auto Growth value. It’s such a good thing. In our demonstration, initial size also equally played a role because the data was only 41 MAYBE. It had never reached to the Auto Growth also. If I add one more 0 here – and if you guys have patience to let this query run for around 50 seconds, we can do the example. I know you guys don’t.
Now, what I will do is that this query is available. It’s easily available. Way earlier I had posted on the blog. Nobody reads from the blog sometimes. They just read one or two lines, a summary or things and they make a statement. I am okay with it. Yeah, this is a big, big thing you need to remember. I want to give you real example. What actually happened, and how did this 1 MB got set up? I just created default. This is one of the questions I received yesterday. “No matter what I do when I create new database, Auto Growth is always set to 1 MB where it is coming from?” Answer is it is coming from none other than your dear model database. Those people who ever tried to drop this database, well, I feel bad for you guys. Here it is. This is the database where it is set to 1 MB and it is directly taken from that. Any new database which is created, if you do not specify any value that are taken from the model database. People say charity begins from the home. Same way the cleaning of your data, right configuration and all these things begin from the model database.
There are two things you need to do, when you look at any of the systems. I find it very, very funny because database is the dry, boring, actually – it’s so boring subject. When you go to performance tuning look at everybody’s face, they are already sad because of database problem. There are two jokes I do every single time and people like it. First I talk about this model database and how this model database [inaudible] them out and incorrect setting here can create a lot of issues for them. They love this one. Most of the people do not know. Second thing is that I always open their master database and I will show them who created this table, why you created it – a lot of people, just forget about changing database context, create hundreds of the object in a master database. Matter of the fact, this is what I learned from one of the emails which I received, it says, “Well, our master database is everyday growing by 10-1” We figured it out, they were using the master database for staging and things like because it’s a master, it can do everything.
Yeah, names are confusing and yeah I have a problem with the SQL Server naming thing. You know what, I will not talk openly because sometimes when we say something openly, you can get in a problem, example is max degree of parallelism. It took me one year to understand what is max degree of parallelism. Right now, a lot of you know, it is just maximum CPU available for a single query or operator. Why cannot they write that here? Well, I am sure this thing, we will eventually cut it down in our recording which we are going to post, so I am safe right now.
But yeah, I have my problems with naming things, how people name it. Like Boost SQL Server Priority, you click it and you will never get performance. Why? Why did you name it Boost SQL Server Priority if you click it and it’s not going to boost my SQL Server Priority? It does boost SQL Server Priority by doing something. There is a big blog post. Again internals, I hardly understand, but there is a fantastic blog post of Arvind, my friend. I would eventually send that link. I will post it where it says boosting SQL Server Priority does not do it, it’s not recommended.
Matter of the fact, if you go to the latest, latest documentation it says this is something that cannot go away in the future. I think it’s there for a while, I think in a SQL Server MSDN Help it says this feature going to go away. I wish they would just take it away. Because a lot of people think it’s a turbo button. Click on it and we will wait that when SQL Server will perform and immediately there is no change. They forget – after a while they come back and restart something here and there. They do things and suddenly SQL Server is not working. They are like, “Yeah, did we?” In one of the live consulting projects I see, the COO asking to the developer, “Hey did you turn the Boost SQL Server Priority on or not? Turn it on, and then I think our performance problem will go away.” That guy said, “It’s already set on.” I was like, “Yeah, show me the dollar bill.” I think this is where you need me most. I mean I can just turn off this one setting and immediately get improved performance.
We got off track on a lot of things. We didn’t even finish what we were talking about. I am not going to apologize about it because this is my time.
Brent Ozar: That’s right. It’s your show.
Pinal Dave: Yes, it’s your show. We are just character artists here and there. We will get old but it’s so much fun to be invited, to be honored, to be with you in here presenting in front of you. This is my best thing. I am going to write in the resume tomorrow morning in LinkedIn, I presented in a GroupBy and it’s a LinkedIn thing, and they are so good. I am happy, so thank you. Coming to this one …
Brent Ozar: Oh thank you.
Pinal Dave: Yeah so …
Brent Ozar: You are just awesome.
Pinal Dave: So this is what Brent told me. Remember I told you guys that this is what I am going to make this thing in my own word. The fix – what is the right fix Auto Growth value? I should have it. This is what I learned from him. He said, “You should have it. Measure your weekly file growth and put it there if you want to be just normal. But if you just want to be little bit aggressive and if you no issue with the space you can make it a 15 days file growth or one month even.” Then somebody says “But I do not know what is my weekly file growth.” I think my answer is that “You will know next Sunday, wait for next Sunday.” I mean, “Why are you rushing? You waited for two years with a bad performance. You waited 16 years of the poor replication performance, now you can’t wait for a week to figure it out this one? Such an inpatient boy you have. You are the one who invented the social media with small text and things where the attention span is so less.”
Recently, I got a tweet and I think it was very, very funny. Somebody said “Please write to me everything you know in the Twitter reply.” I was like Yeah, I wrote to him “everything.” What else I am going to write? Everything, everything. What you asked, I gave you. He said “You are rude.” I said “I am not rude, I see what you are saying but look at your questions and tell me everything you know about SQL Server okay, everything.”
Here you go, coming back to this one. Weekly File Growth and if you do not know what is Weekly File Growth and you have set some very small number like 10 MB or 1 MB? You know what, this is what I will tell, set it to 200 MB or set it to 250 MB. Why? Well, it’s better than 2 MB, so that’s why you set it up. Ideal answer is there on your screen. Put it weekly file growth.
Great, now we have third and final thing. These are the two things. Trust me, once I do these two things, quite a lot of time people have not called me back for performance tuning. I keep on wondering did I do something wrong, did I mess it up. Answer is no, actually, these two things make them so happy that suddenly they started to value their little money which they gave me. They are ok to buy the SQL Server very, very expensive but when I ask this little money, they were saying, “No that’s not what we are going to give,” and they are happy with two things.
Finally, I come up with a third one. This is my business secret I am sharing with all of you guys. These two settings were so good that they stopped hiring me again and again. I thought about it, I need to do something. See, I don’t want to be mean person where I don’t tell them everything I know. The entire purpose of creating blog was to give back. Now, that goes against me. If you do not get money at your home, and your 7-year-old daughter and your wife look at you and say “When are you going to get money, so we can go out and have one decent meal,” you start thinking about little bit being mean, isn’t it? This is where I invented the third mistake which I show to every customer. I think since I started to show this, 50% of them started to call me back, because this third one is very, very interesting but it’s only half. I think it’s – oh wow, there are so many attendees there. I think I just blew it up by telling everything I know. I don’t know any of those would ever call me and if this video will be posted I know next day, and so many people will watch it, I don’t know what will happen now. If something goes wrong I am coming to your home Brent for a loan and …
Brent Ozar: We will both be broke. We will both be broke. You are right, people don’t – it’s these same kind of mistakes over and over again, over and over again.
Pinal Dave: Yeah. Absolutely. It’s the same and same and you fix the same thing. I think its fun. Now let’s look at Implicit Conversion. This demo is fun. I had a lot of time building this demo this morning. Though I had this demo earlier built but it’s always fun to revise the code and do it. Here it is, I am just creating a table and inserting some data. I think you guys already know that I am a lazy guy who doesn’t explain lot of things. You don’t need to know why I created a table, what is in it and things. Those are the things. I mean, anyway if I am going to say things you are not going to pay attention. Half of you have already minimized the screen. All right, I see a lot of people now waking up. Okay, good. Thank you for paying attention.
It’s so common, a lot of people are just watching the screen. I mean how do you learn anything by watching screen and not hearing? Some of you are just sharing the screen and not watching the screen and some of you are doing both of them, thank you. Coming to the point here, this table has some data in it, which is random data and I am just creating Index on it. Why? It’s just like normal people do. They don’t think too much. They just keep on working on it. When they face a problem, they are like “Why the problem comes?” Now my question to all of you is that which of this query will be optimal, query number one or query number two? You cannot ask me back any questions or I will not let you ask me any question. I know Brent can ask me. I am just saying, rest of you people now choose one or two. You cannot ask me anything back, like “Go up and show me this, go up, the data type [inaudible] go up.” I cannot – What will you do? See, I told you, I called you out. The guy who does not know what was that thing that happened or what you want to see. I called you out that you were not watching the screen. What were your doing even though I called you out? You still didn’t watch it. You do not know the answer. Rest of the people, now answer we what is the answer. Which query will be faster? Query one or query two? Write it down on a piece of the notepad, because it’s a very, very …
Brent Ozar: I did …
Pinal Dave: No not you …
Brent Ozar: No, no, no, but James asked how much coffee did you drink before this started? They are all jealous of your energy.
Pinal Dave: Oh the coffee part. Actually, thank you, I will pass it to my wife. She doesn’t know how to make good coffee. Yesterday, she made coffee in the evening because I told her – this is a true story. Yesterday evening this happened. I told her that I have to work for my customer in the night and please make me coffee. She made me coffee and after that I had amazing energy, I was almost dancing here and there. I asked her, I said “This is good coffee, show me the coffee.” She brought me back this Seattle’s Best Decaf Coffee which I bought from [inaudible]. I was like “Oh!” Suddenly I felt very lousy. It was decaf. There was no coffee in it, but it looked like coffee, so I got a lot of energy.
Brent Ozar: Oh!
Pinal Dave: Really! James, I do not have an answer for you. Today, I did not even drink coffee because yesterday I got cheated by her on coffee. Looking at this, yeah, so good one. Looking at this, oh yeah which query will be – did you write it down guys? Did you write query one or query two? Please write it down. Because if you don’t write it down, when I show you answer you will say “I was about to say that.” I know that you will do that, so please write it down and be wrong. Wrong is a good thing. Right is even better thing. Let’s run this query.
Now with execution plan enabled which I did, ran it and see the answer. Look at that, top query is 99% and the bottom query is only 1%. Matter of the fact if you aren’t going to say this deceived you then let STATISTICS IO on. I will just do that and we will show you the page read as well. We will prove that query execution plan is almost reflecting what we are seeing here. Look at 390 logical reads and 3 logical reads. Those people who are not able to see now you should be able to see. Look at the percentages. These are directly reflected over here as well, 99 and 1. This time execution plan did a right job, drawing the picture from what is happening under the hood. Good one, plus 1 for SQL Server, minus 1 for everybody who thinks execution plan is always wrong.
Coming to this one, over here, I mouse over, and it says warning. Warning says type conversion implicit. You know what, why I say this is where I get money? This is where I get money, because I showed this kind of behavior into the SQL Server and I say “Look, you have Implicit Conversion happening, what you need to do is that you need to change a few things and make your data type same on both the sides, otherwise you will get such a bad performance.” They are like, “Yeah.” See, other two were settings, they can just do with the knob here and they think done. Now, they did this when I demonstrated a couple of queries like this and said, “This is what is happening in your system, Implicit Conversion. You need to go back and fix it.” Their first question to me is “All right, yeah we want to fix all the queries where Implicit Conversion is happening. If this is what is happening in our system, please help me.” This is where they call me back.
Then, what do I do? There are a couple of things. The smart people here will tell that if reference number, I want to see the data type of it. Reference number is Varchar, so that is why it is giving us data type mismatch. What is happening here is reference number is Varchar and this is integer. SQL Server internally converted this reference number to Varchar so it matter of the fact did Index Scan. It went to every single row in this table, converted from Varchar to integer and did one comparison, said “Yes boss, here is your answer.” In this case it was pretty straightforward. Varchar here, Varchar there, both the Varchar were together.
Here there is a problem – Reference number and Varchar integer Implicit Conversion. A smart person will tell me two things: first, “How do I fix it?” And second, “How do I identify all the queries in my system which are doing this?” Two things – you want to know both the things, and I will help you with both of them.
Let’s see one more time this answer over here. Good job. Now I will show you another query which I run. A query has limitations. This is the query which I don’t give to any of my users. I would definitely share on a GroupBy page so you can take it, but I will never share to any of my customers or any of you, just so you know, because this is where I make my consulting money. When I run this particular query – and I run it on database SQL authority and previously my database was SQL authority – you can see here various things coming from my cache. Warning for the people who are very, very smart, if something happens your cache is cleaned out, you will not see records here. It is only for a brief moment till your cache is there. It’s quite possible, brief moment is hours too. I do not know.
Now, we have to go over here and try to look and find our queries from our system. I am a lazy guy, so I am going to do this. This query does Implicit Conversion. I am going to run it 100 times, going to disable the execution plan and then I think this query will come automatically on top for us. Again, this is only in demonstration you do. Don’t do on production unless you want to find a new job. That’s a great idea too. That’s how I always find a new job. Four jobs, four times, I had to leave it before they asked me to leave.
Look at this one, this is the query which we have run a few seconds ago. Reference number 555 – this query came on the top of it. It has a total worker time, average worker time, max worker time. These are the old generic queries you can find everywhere. This was the creation time. It ran 103 times. We run that. That is not our problem right now, but I can open the execution plan over here. It brings up same execution plan, tells me what are the things mismatched and you can take this mismatch and ask them to fix it. There are multiple ways to fix it, but this is first I solve one problem how do you find those queries. As long as they are in your execution plan, you will find it.
Now, the second part of this one, our earlier query. We have some time left for today, so I am going to show you how you can fix it. Let us say okay –stay with me, we are totally not done, because now I am going to ask you questions which most probably you will get wrong. When you get it wrong thank me, thank GroupBy and thank Brent that we were here to explain to you this.
What I am going to do, let’s assume that reference number is now always going to store integer. A not so smart developer made this as a Varchar. Let’s go and change this. What I am going to do is I am going to drop existing Index, then change that particular column reference number two integer. Good job. It’s going to take some while – done. I will recreate that Index.
Now all the smarty guys out there, I want you to do this. Tell me which query will work faster? Except Brent everybody is allowed to answer. You guys can’t speak anyway. Which query will be fast query? Which query will be fast? Tell me now. You have 1 second to write down your number on a notepad. Write it down and be wrong.. I am telling you, if you write it down, you are going to do erase it and correct it after that.
Tell me now reference number is an integer, reference number is an integer, reference number integer here and it’s a comparison of Varchar. There is conversion going to happen in the second query. Which query will be perfect? Which query will give you right answer? The hint is already on the screen, I am hiding it. Yes.
Now coming to this one, look at this. All of you thought I was not guiding you. Hint was there on the screen till this moment. I never tricked anybody. I don’t have a counter running where I make a note how many people I fooled today. Actually, because if we begin over [inaudible]. Look at this one. Please tell me which query will do best. Oh Twitter is full of people telling me wrong answer. Thank you guys.
Look at this, execution plan, I forgot to enable. Click over here, click here, wait, wait, wait, execution plan and yes you can see very clearly that top query is now doing much better and bottom query is doing very bad. Not really! Both the queries are giving you correct answer. Why are you nodding your head? Both of them are same 50%.
What happened? You will tell me. This is what is interesting. You thought this cannot change. You [inaudible] thought. You were thinking like okay, so 99-1%, now the 99-1% is going to flip. That was a wrong answer. It is 50-50. What happened?
There is one more thing to learn over here which is [inaudible] data type precedence. I am including the link. Microsoft has a lot of different data types and every data type has some rule. I can open the web page. Even, matter of the fact, SQL Server now when I click, it opens inside here, it’s interesting. Anyway, I am not going to open the page because there’s so much information available out. I will tell you this. I don’t want to confuse you, so in simple possible words, if multiple data types are there, Microsoft came up with a rule about which data type will convert to which other data type. That’s how it works.
Over here, if you have integer compared to Varchar, Varchar is always going to be converted to integer. That rule is only true for integer and Varchar. Varchar will be always converted to integer. I always remember this. Now, think about this: previously, this was Varchar. I will move up. Even though it’s the same query, a lot of people, when I move up can relate. When this was Varchar and you were comparing to integer, this entire column of 100,000 record was being converted from Varchar to integer and after the conversion is over, the match was happening. In the second case, this was already integer, so there was no problem here. Here, this was integer and this was Varchar, so only one conversion happened. Look at my finger, one, because the one was not expensive, this went very, very fast.
My point and answer to you is this: SQL Server has data type precedence. You can use this to understand how the Implicit Conversion happens, how you can avoid it, you can learn more about it. This is couple of warning signs which I tell to my customer and that’s why they hire me back saying “Hey this is happening in your system, in your var clauses many times. I ran this query, 45 times it’s happening.”
Also, this can happen in your joints also. What I mean by table 1 – orders and table 2 – orders details. If you are comparing two of the columns with different data type, this particular thing happens, which is called Implicit Conversion. What is the negative part of Implicit Conversion? Yes, one thing: bigger the table you have, slower your query gets. Write it down somewhere. Every single time you make one common mistake, this thing comes into action. Bigger the table you have, slower the performance you get by one mistake like this.
Now, SQL Server will not use no matter what kind of good Index you have created here. It will make sure not to use that Index because conversion has happened. What it will do? It will use the Index which is a Cluster Index probably. This is a table itself with the entire thing and it will scan there. Yeah Index will use, but that’s entire story.
In short, the performance you are expecting from the query because of the Index will not be there. That’s what it is. That’s the mistake. You need to learn about it. You need to make sure the simple one line answer: identify all those Implicit Conversion from a query like this and then fix it one at a time by making sure both the type, both the sides, the data type is same. That’s the mistake and that’s the correction. I would share this particular PPT with Brent and we will have it on GroupBy by tomorrow. Don’t worry about it.
With this, I have reached to the end of the demonstration. We talked about learning about three things but you know what, we are lazy people, we always talk too much. Not only three things we talk about, a couple of my customers – I hope they are not listening. We also talked about Boost SQL Server Priority. We also talked about naming convention, how good we do sometimes, and sometimes it’s me. See I come from the place where English is not – I only am talking English right now because of you guys, like five minutes before I was not talking English, five minutes after this call, I will not talk English. People sometimes have language challenge and when we use the difficult word we don’t get it. Then we are just like whatever!
When I come across any difficult English word in my performance training consultancy, my answer is this. Not knowing the meaning of that does not affect the performance of SQL Server, and it always works. Everybody laughs and we all forget what was the real issue. This is where – if you want to sign up to my newsletter, where I do random stuff, what you just heard, I write all these things. It’s not so funny, but you have to hear this in my own accent. Then you may find it funny. Last time I got this one, that I sound like Big Bang Theory’s – the lead actor, Raj Koothrappali and so “Are you related?” I said “No, no. There are like 1 billion people and you cannot call all of us relative, no. That’s not how it works.” So many people here, I can show you out of this window you will see 20 people, even though its 1 AM.
With this, I think we are done with our presentation. We talked a lot. Thank you. Ever call us, read us, hear us, sign up to this newsletter. You can go to go.sqlauthority.com. You can drop the email address. Meanwhile, the thing which I showed you, particularly one script I will share on a GroupBy with the PPT. Thank you. Over to the questions if I know the answers.
Brent Ozar: Wonderful. Thank you, Pinal. Yes, God, it’s the first time I have ever seen you on webcam or anything like that and you are fantastic. It’s so much fun. We had so many mentions in Slack and in Twitter saying it’s just fantastic to watch there.
Pinal Dave: Thank you. See who I am inspired from? You know that guy is sitting left in front of me. Thank you. I am so motivated. Brent, I will tell you, being very, very honest, one thing I appreciate you because you are like yourself, you express yourself and I learned about this thing in early career. I have to be me if I want to be me. Thank you.
Brent Ozar: Yes. It just comes through, when you get excited about something and you love it and the audience gets excited too. It just totally translates into that.
Pinal Dave: Thank you.
Brent Ozar: Let’s see here. Lots of people said, “Oh I was wrong when I put in the wrong answer. I was one query faster than the other.” LC Gully says, “Thanks for bringing Pinal to this event. The interview question section of his website helped me to get a great job few years’ back so thanks, Pinal.”
Pinal Dave: Thank you. That just makes my day or midnight right now. You guys know anything which you read and I can tell you Brent and every single blogger can relate when you read any of the stuff which we write. Even though you say thank you or if you just wish just sitting staying at home, just wish one good thing for us, I think it just, I think the positive energy reaches to us. Thank you. I can’t express that yes.
Brent Ozar: Because you write it and you hope it helps people, but you just never know until you actually hear back from them.
Pinal Dave: Absolutely. Thank you.
Brent Ozar: Let’s see here. Next up James says “Oh should we turn Auto Update stats asynchronously on or off?”
Pinal Dave: I would leave this one on you because you know what, I am still not sure about the answer of it. I think you can leave it on and off but I personally have not seen much of the immediate impact of it so I can create a demo of it. I do not like to say anything if I cannot generate a demonstration for this. I tried over three days to build a repeatable, predictable demo by changing this setting on and off and I could not figure it out how I can convince you. I am sure the setting works but I don’t know how do I prove it to you. If I cannot prove it to you, how do I explain to you? I leave it Brent to answer that one.
Brent Ozar: I feel a lot the same as you do there. I see some people who turn it on by default and they’ve seen a difference. The idea being when you trip that 20% stat threshold and it starts to do an update of statistics, the query that tripped the update stat threshold is supposed to stop until the update stat finishes and then runs. If you do it asynchronously, it’s supposed to run immediately with the bad stats or old stats, wild stats populating the background. I have never had a situation where that took me across the finish line where the customer said, “Oh now everything is great.”
Pinal Dave: Certainly.
Brent Ozar: Yeah. They have other problems and that’s a bigger issue. I don’t care whether people turn it on or off. I have never seen it hurt anything. I have just never seen it take you across the finish line.
Pinal Dave: I am so glad your answer is very near to me, otherwise I thought – I am making a note here also, I am writing down how many things I just learned being in a presentation. Even though I am presenting, that shows that how much I have to learn, and I even, I am presenting, I am making note of it and so I learned this. Anyway, thank you.
Brent Ozar: That’s the best way – I tell people to learn something is to have to teach it because you nailed it when you said you have to build a demo to – I learn more when I build demos because they are … it didn’t turn out the way I thought it was going to take. I better, I need to change this, yeah.
Pinal Dave: Absolutely.
Brent Ozar: It’s really challenging when it happens live. When you wrote the demo and it doesn’t work the way you thought it did live, that’s a key reliever.
Pinal Dave: Exactly. I want all my demonstrations to repeat. They should behave normally. That is the reason, like one of the questions I received in email said “Are you going to do a demonstration for Boost SQL Server Priority?” No, if I turn it on, my entire OS will struggle and I can’t do the webcast so I [inaudible] build a VM for it and turn it on. Again, if I turn it on, there is no guarantee that right now at this point of time in front of you it will behave badly. It will be like our own kid like when we say “Oh they will not eat this food.” They will immediately look at us and say “Dad I want to eat ice cream. I eat ice cream all the time. Why do you have to accept that in front of everybody when I say no?”
Brent Ozar: Kelly says “I am sorry I had to walk away from this captivating session, but at least I will be smiling in my next meeting. Good job.” That’s excellent feedback.
Pinal Dave: Thank you. Thank you very much.
Brent Ozar: Well, Pinal, I will let you go ahead and go back to your client then. Thank you so much for taking time out of your workday to do this. This was absolutely phenomenal and I know people really loved it and appreciated it. Thank you.
Pinal Dave: Thank you very much. I would just say one last thing, you know what, I am so honored to be here. You just don’t know, like dreams like this that I get opportunity to present in front of everybody. When I say everybody there is one guy, Brent Ozar, also there, it made my day. So far I was always saying, I am so proud that I present in front of everybody minus Brent. Now, I can use the word everybody. Now, I can use the word everybody, so I am happy. It took me 10 years to reach where I am honored to present in front of you. Thank you.
Brent Ozar: That is so funny. Well, I only got the honor to present in front of you – what in November? October-November, I got to present in front of you for the first time. That was really funny. It was awesome.
Pinal Dave: Thank you. Thank you, sir. You are very kind. Thank you, everybody. Thanks for listening to me all the way from India.