Subtitles of the Movie

Our third method of being able to track the execution of our query is by a statement. Example of this statement is SET SHOWPLAN. When we're working with this particular statement, please understand that it is also part of the profiler. I simply chose to show you the profiler example based on the Execution Plan itself. This is one of the tests that can be turned On via the trace. Which you use are completely up to you. It goes without saying that we can spend an entire movie just talking about the details of each individual function that can be turned On within that trace itself. We're choosing in this particular scenario to just do some recaps on it. When we normally run our query in management studio, we are defaulted to see the results of it. For example, if I select everything on addresses, when I execute it, I get the addresses itself. However, there are times that we want to see the results of how this particular query is running. In this particular case, I am going to turn on the plan. I'm simply going to type in SET SHOWPLAN. I want to see it in text and I want it to be turned On. Now, keep in mind that you must turn this plan on and off. It'll actually stay on until you either turn it off or close out of the particular query that you're running. I'm going to execute the command. Once I execute the command, I'm simply going to go ahead and re-paste in our last statement. Now, remember last time, we had no problems seeing all the individual results. This time with the plan turned On, I'm actually seeing the StmtText telling me what's running in the background. In this particular case, it was simply a table and it's telling me that on this particular query, I have run a Clustered Index. If I were to look at a query with more detail such as the one that's showing the salesperson's sales by fiscal years, I now see all the detail that is a result of running the particular query. Again, it is not showing me the results. It is simply showing me the Execution Plan. Here, it's breaking it down step by step. It's explaining to me cases that are described, any groupings that have been placed in, how I'm sorting, what the order of those are, are there any inner joins, what kind of index scans do I have going on, what kind of Clustered Indexes are being used, what kind of joins do I see in the query itself and it's showing me how these run in order. The idea here is to give me an indication if I'm running too much on scanning entire tables rather than depending on the indexes itself. Remember, your indexes are presort. They will run faster on scanning the entire table itself. The SHOWPLAN is just another method of getting to the Execution Plan. You can work with that plan either by showing the actual execution, by utilizing SQL Profiler or by simply setting the SHOWPLAN to be turned On in a text mode. There are indeed other attributes that can be used with the SHOWPLAN itself. If this is something you can utilize in your situation, please do continue your education and read further into setting these plans themselves. Now, you'll notice that with all of these Execution Plans, indexing has been at the key. The Execution Plans are all about scanning tables and sorting versus having indexes turned On where the sort order is already defined for us. Let's go in now and talk in-depth about what these indexes are and how we can shift them to get better performance within our actual data.