Subtitles of the Movie

A good portion of optimization actually surrounds fixing problems and coming up with good solutions to issues that exist. Let's take a few moments and talk about the most common problems and some of the solutions. Of course, throughout the videos we will go deeper and deeper into problem solving and possible solutions. First, network connections ? they fail. One of the things that you might want to check is that if you can connect to your server locally rather than on the network you may have a port problem. For instance, ports are defaulted in SQL Server 21433. If I go in and change that port number because of security issues you are referencing the old port. We'll need to change your machine reference to the new port or you can use named pipes in SQL to be able to connect to another component. Now of course with the network connection there's always that possibility of the cable just being unplugged. Remember, always start with that physical look of the computer to make sure that the simple components are not overlooked. Another potential is out of space errors. This happens, of course, when your drive runs out of space. Now remember, there are two different areas that we have to think about. We have the database itself, but we also have a transaction log. One or the two of those can fail at any given time. Keep in mind that your maximum file size also could have been set so that they stop at a certain point. This is to give you an indication that they're growing very large to give you enough time to put a solution into place. Now given that the maximum file was set as a reminder you can increase the limit but remember, of course, immediately go to work in coming up with a potential solution. Problem solving is all about giving ourselves a little bit of breathing room so that we can fix the issue. As much as possible try to use reminders and things to let you know of these different components that happen so that we buy enough time to actually put some solutions into place. You'll occasionally also see a suspect mode. If a database is in this suspect mode it's usually because there's corruption. Perhaps a file was moved, the master database now incorrectly references them. Maybe a disk drive has been changed, something new has been added to the system that has changed things around. One of the first things we do is detach the database and then reattach it again. If it was because of a moved drive, referenced component, name change, that detaching and reattaching can solve that problem. What if your query is taking a little bit longer than usual? Here we're going to go to the Query Analyzer, and again, every database has their own component. We'll go deeply into the SQL Server version of the Query Analyzer later in the videos. Now when you're using these analyzers what we're really going to do is create an Execution Plan. This is going to capture the movements of our query. We can then go back and analyze the results. Now the true key to using these is to be able to compare it to a baseline. In other words, having a snapshot of the query when it's working at its best is a real vital resource. I can now go in, compare the query to what's happening today and it just allows me to see certain possibilities of what could have gone wrong. There are also times when your jobs might suddenly fail. Let's take the instance of a user who's been listed as the owner of a job and then that user either leaves the company where their name is actually deleted, or perhaps they changed positions so that their name has been changed. The job now will fail. If I was no longer part of accounting and now I am part of accounting and I've just been moved in the system there's the possibility that the naming convention has referenced me but did not let our actual Server know. In this particular case check your job name. Make sure that the owner's name is cross referenced correctly. How about a new query that just totally slows down your system? OK, here we're going to go to our Performance Monitor to see what the utilization is and again, we'll be covering this later in the videos. In this particular case, the currently running procedures Execution Plan may be incorrect. We could also need to change the degree of parallelism for this query. Remember, we always have lots of things running at the same time within our system. We may need to repeatedly change these values to find out what the best performance is. In other words, our system is constantly progressing and growing. We need to keep up with it. How about denied errors? Denial of use? OK, users' report permissions can come up with denied errors after being granted rights. Whenever you're changing anything, even if you give user rights to execute stored procedures, you also have to make sure you've given them the rights on the object itself. For instance, to run a new procedure I give you a right to that procedure but you never had the rights to the underlying table. I have to make sure that I am changing all the components that go with those procedures themselves. When you're going through all these different components remember again that every single system is slightly different. I'd like to show you an example now of SQL. In SQL there is the Configuration Manager. This is one of the first places that we look at to make sure services and things are running correctly. For instance, notice how on the server browser in the Agent I have the red Within in SQL it's basically just telling me that the services are stopped. It can tell that I need to turn them on before progressing. As with all computer programs also, very often we just stop and restart the different services to make sure that everything is running correctly. Be familiar with the different configuration managers that are with your database itself. In this particular case notice that I can look at the services, I can see the Network Configuration, Protocol, Client Configuration, Client Protocols and the Aliases. We'll be delving into all of these as we go through the videos. What I'd like you to remember at this point is know what your common problems are and know where to find the solutions within your particular database.