Subtitles of the Movie

Let's finish up our section on testing and troubleshooting by including some tips. Now, these are very generalized tips. They will, of course, increase your optimization. I haven't included them in any particular order. Just wanna talk about some techniques that will help you in the testing and the troubleshooting phase. First, if at all possible, refrain from user-defined functions. Keep in mind that these use row-by-row processing. That will of course hurt your performance. If you had a function that would result in 10,000 rows, keep in mind that it's going to call that procedure 10,000 times. This, of course, will slow down the database itself. Also remember to properly utilize those indexes also. Indexes are a key. That sorting in the grouping is very important but don't underestimate the complexities of working with indexing. Make sure you're thoroughly researching your choices before randomly placing indexes into a table. Also consider multiple drives if you can. Performance is always increased by spreading the data, the logs and the files across different drives or even different servers. Think in terms of what your situation is and what you can do in the background. Also, use your Primary Keys. Remember, the Primary Key is what needs to link to that Foreign Key. Make sure that you're using the Primary Keys and that they are indeed linking to the Foreign Key itself. Now, you also wanna keep in mind that we only wanna be retrieving what we have to. Here's where you want to use the WHERE statement with SELECT. You wanna eliminate duplicates. Remember, there's always the ability to use SELECT DISTINCT. Don't pull up any more data than you really need in the report. It's going to just cost you resources. Now, also remember the performance efficiency of the operators, equal, greater than, LIKES, not equal to, that's the order that you wanna keep in mind. Always use the Performance Monitor also. Remember, it gives you information on how your server is performing. It also lets you know how Windows Server is doing. Don't, however, keep it turned On always. Remember, the idea here is to come up with some baselines, capture some data. If you have it on 100 percent of the time, it is of course eating up some of the resources, too. Now, do keep in mind, too, that the backup should take place at the time of the day when you have the slowest activity. Traditionally, we do this in the evening. Backup and restores can definitely be an issue. That's because it really takes a lot of time to actually back up a database itself. Remember, too, that if users start activity when you're through any type of procedure that needs to be in exclusive mode, it's gonna be canceled the minute they start doing something. That's why it's so important to have somebody monitor and test those backups. You may be running the process every night but it may not be completing a good backup. Testing, verification exceptionally important. Also keep in mind that if you have one query that is run over and over and over again on the same table, that you want a covering index. The covering index includes all of the columns that are referenced in the statement itself. This is used in those instances where frequency is the key. Finally, don't use a trigger to try to enforce referential integrity. Referential integrity should have been designed into the structure of the database itself. Very often when we're running procedures, we try to do our integrity checks at that stage. This is not the best time for that to take place. The best time is in your design stage, so make sure that your structure is correct and the rest of your database will work great.