Subtitles of the Movie

Keep in mind that when working with the Database Tuning Advisor that it's going to be necessary to actually have information running in the background. We're going to run a query or run a particular procedure. We're going to capture that procedure within the trace and then we're going to analyze that within the Database Tuning Advisor. Now, I have included some sample files for AdventureWorks if you do not have your own query that you wish to run this on. You will note that in the Work Files, there is the actual code that will set up some sample files. There's a sample procedure and the sample trace file. I recommend, however, that you use the sample for the setup of the data and the maneuvering of the data but actually go ahead and set up your own trace and your own session in the Tuning Advisor to get the practice. So, the first thing I've done is I've created some actual information in AdventureWorks. We just created some tables. Then, I went ahead and ran a procedure against those tables. Now, when you create your tables, don't run your actual query right away. We wanna actually turn that on when we're running the trace itself. Step number 2, I'm going to go to the Tools menu and under Tools, I'm going to go to the Profiler. When you open the Profiler, we're going to run a new trace. So, I'm going to go to File and New Trace and connect. For this example, I'm going to run the same type of trace that I actually ran in our early example on the index. I'm going to use a Tuning template. I am going to save it to a file itself and since I already have a sample trace, I'll call it Sample2. When I have this sample trace in the background, I'm gonna limit the file size so it doesn't get exceedingly large and I'll enable the file rollover. I'm now going to start the trace itself. Now, remember when your trace is running, it's assuming you have some information going on in the background. Here is where you'll wanna go into your SQL Server in the management studio and actually run your query or start working. Trace is all about actually taking snapshots of the workload itself so it requires some activity in the background. Also keep in mind you'll see the jobs that are running on the screen in front of us. It takes about 20 minutes to a half an hour to actually get a good trace. So, once you start your trace, relax. The whole idea here is to capture the activity as it's going on in the server itself. Once we've set up our trace, the next step that we wanna do is capture that session information in the Tuning Advisor so I'm going to go ahead, I'm going to pretend that half an hour has passed and I'm going to stop the trace. I am now going to switch over to the actual Database Engine Tuning Advisor. Now, you will see on the left-hand side that it actually stores different traces that I have run. What we will do is on the General tab, we're going to go ahead and create a new session. When we create this new session, we're simply going to go to File and to New Session. When we're in the new session, you'll notice that it appears on the top. We can name our session and we're gonna choose a file. We're choosing a file because we would like to just go in the background and actually take the trace file that we just established. The database that we're actually working against is going to be AdventureWorks so I'm going to choose AdventureWorks and I'll select the database tables to actually go through on the bottom. In this case, I've taken all 80. Notice that I could select individual tables if I wanted to. Now, I'm ready to start the analysis. Once again, keep in mind that this is a long-running procedure. You'll need to give it time to actually go through its steps. Let's go ahead and go down to one of the past procedures so you can see what happened. When I go into the progress, it tells me what has happened. Out of 5 total processes, 4 were successful, one has a warning. When I have a warning, you'll notice on the side it says 5% of consumed workload has syntax errors. In other words, I need to check my coding. Let's go to recommendations. In the recommendation now, it tells you what it believes you should do. In this particular instance, what you'll notice on most of these, it's requesting a new index. Notice how it's telling you what it's recommending the index should be on. On the top one, you'll notice it's asking for a new statistics. Once we go through the recommendations, we can actually see our tuning summary. You'll notice here it tells you what your timing was, what space recommendations are so you have all the information at your fingertips. Notice on the bottom, you have a number of different reports that you can actually choose from. You can select anyone of those reports that you want to. When you do, it brings up the information on the screen itself. So, when you're working with the Database Engine Tuning Advisor, remember, there are 2 steps involved. First, we're going to create the trace. Then, we're going to go in and analyze the results of that trace in the Tuning Advisor. Again, keep in mind the key here is to actually track your workload as it's running so once you start your trace, keep it going for a while, minimum of half an hour, then stop your trace, pull it into your Database Tuning Advisor and read and analyze the recommendations.