Subtitles of the Movie

We created a checklist for indexes in our preceding video. Now, I'd like to go into a more generalized checklist. The same rules are going to apply. These are only suggestions in an outline form. You want to add to it as necessary for your unique situation. Remember, not everything applies to every database. It's okay to disagree with a point because that's the individual personality of the database itself. Of course, I've included a copy of this in the Work Files. Let's begin by talking about the design stage. Devote appropriate resources to planning. It is very easy to detect a database that has been planned out from the beginning. The flow of it is better. The documentation of it is better. The understanding of it is better. You have to have a clear idea of your purpose before you can begin. Then, normalize that database. Remember, we normalize first then, if necessary for performance, we're going to de-normalize. Always define the primary keys and the foreign key relationships early on. We want to have a primary key in each table and don't forget to choose the most appropriate data type. Keep it as short as possible. Get users used to the idea that they do not have unlimited resources and know the performance and scalability characteristics of the query. Who's using them now? Who's going to be using them in the future? How is your data going to be growing? All of this is necessary to really think through how to work with the database itself. When we start working with these queries, make sure you only return the rows and columns that are needed. Avoid pulling up all the information on the table when you're only using 1 or 2 columns and avoid expensive operators such as NOT LIKE. Remember, positive is always better than negative. Also avoid explicit or implicit functions when using the WHERE clauses and use stored procedures whenever possible. Remember, stored procedures run off of the server, we write them in code, they have better reliability to them as a general rule. And avoid long actions within those triggers also. Remember, triggers are doing a lot of activity in the background and this can hurt the optimization of our database. Use your temporary tables and your variables appropriately. Create indexes based on use. Remember, we don't necessarily wanna put an index on everything. We really have to think through what's being used most. Do not put indexes on rarely used fields. Create an index, though, on all of your foreign keys and consider indexes on the columns where you have your statements such as WHERE, ORDER BY, GROUP BY and DISTINCT. If you are not using an index, make sure that you remove it and do use the Index Tuning Wizard. Once you've gone through your index, then go ahead and evaluate the query execution plan. Avoid anything that has a table or index scan. If you see these scans in the query execution, think about rewriting that query. Keep your statistics up to date so that you know the performance features you're looking at today are in today's database. Use the Profiler to monitor these table and index scans and use the testing tools to perform stress and load tests on your system. Finally, don't forget the Performance Monitor. Use the Performance Monitor to monitor high resource usage. Keep in mind that when you're using the performance monitor that it's not just about the database but we have to keep in mind our hardware, our integration with other applications and the entire system as a whole. I'm sure you can see where you can add many more things to this checklist. The idea is, whenever we document these procedures, it's easier to keep ourselves on track when we're under stress.