Subtitles of the Movie

Let's talk about significance on performance and the ways in which the optimization of the indexes will help. OK first, do keep in mind that we need to limit those indexes. Every index increases performance time for your inserts, for your updates and for your deletes. As a result, a limit that you should look at is 45 indexes in one particular table. If the table is Read-only, you can increase the number of indexes because we won't have the transaction processing going on within the table itself. Next, keep your indexes as narrow as possible. Remember that this will help reduce the size of the index. If the index is smaller the number of reads that are required is also reduced. Also try to create indexes on columns that have integer values rather than your character values. Remember the short key. We want to think in terms of short keys. If you create a composite or multi-columned index the order of those columns are very important. It is recommended however that you minimize the use of these as much as possible. When you order the columns and key, use the most selective first. Make sure that you minimize the amount of data that's pulled up from that index itself. If you need to join several tables, it's always recommended that you try to create a surrogate integer key and then create your indexes on their columns. In other words, do everything possible to turn your multiple columns into one short key for the index itself. Creating the surrogate integer primary key is a big advantage to performance. Here's where we'll take an identity for example, set up an auto number and let the number program actually see the identification of our primary key. When this happens, you don't have as much type of draw for the insert operation themselves. Clustered indexes are always more preferable than non-clustered. If you can get away with just a clustered index, do it. However if you need to select a range of values or you need to sort with results such as GROUP BY and ORDER BY, everything will be taken care of with that clustered index. If your application is performing the same query over and over again, then you might wanna consider creating a covering index on the table itself. Remember in that situation there's a type of report that is created that avoids having to have the data paging going on within the database itself. There's also the Profiler. Remember in comparing the indexing that you can use the SQL Profiler to create the trace. There's a wizard available to do this and you can identify scans of large tables. We introduce the Profiler when it came to the index. We did not get through every individual component that was possible on the index. Look at the one to Identify Scans of Large Tables. This may help you determine the tables that need those indexes. It will show which tables are being scanned by the queries instead of the index. Also you can use the stored procedure that is for each table. Now this is an undocumented stored procedure that allows us to do a variety of different tasks including rebuilding all of your indexes. Let's take an example of this within SQL itself. On the screen you will notice that I have the Query Analyzer open. On the top of the screen I'm using Adventureworks. I'm executing the stored procedure MSforeachtable. I'm executing this on the command -UPDATE statistics ? WITH ALL? On the bottom of the screen you can see that the command actually completed itself. Now this is not the type of command that will produce results within the text order the grid. What it's done however is gone into the background and updated all of the statistics. This could be one of the fastest ways to actually recompile the data in the background. Remember we want to do that every time we're modifying our actual indexes. It may be the type of query that you want to save so you can recall it each time you're working with your indexes. Another example of this same exact command is to actually go in and see what kind of space has been used within our database. In this case, it's once again the same stored procedure except we're executing for each table the stored procedure space used. It will then list out the spaces that have actually been used within the table and you'll notice it also gives me the data, index and unused space. Now there are even more commands that are available when using the stored procedure. You can do additional research and find out all of the different components that you can use this with. It's a great aspect of working with your indexes, cleaning up your tables, seeing the amount of space that's used and updating statistics automatically. As you know we go through courses and we find out that even more education is necessary. Programs are progressive. We need to grow with them. We learn thing and immediately start doing our education on the next component available.