vb net 2d barcode generator Figure 3-57. Index optimization scale in Visual Studio .NET

Generating Denso QR Bar Code in Visual Studio .NET Figure 3-57. Index optimization scale

Figure 3-57. Index optimization scale
Encoding QR-Code In .NET Framework
Using Barcode encoder for .NET framework Control to generate, create QR Code image in .NET applications.
www.OnBarcode.com
Recognize QR-Code In .NET Framework
Using Barcode decoder for VS .NET Control to read, scan read, scan image in VS .NET applications.
www.OnBarcode.com
[View full size image]
Barcode Creation In VS .NET
Using Barcode drawer for VS .NET Control to generate, create barcode image in .NET applications.
www.OnBarcode.com
Scan Barcode In VS .NET
Using Barcode recognizer for .NET framework Control to read, scan read, scan image in .NET framework applications.
www.OnBarcode.com
You might find it easier with this illustration to identify plans that are based on selectivity vs. plans that aren't (represented as a dot), and also to make comparisons between the performance of the different levels of optimization in the scale.
Quick Response Code Generator In C#.NET
Using Barcode creation for Visual Studio .NET Control to generate, create Quick Response Code image in Visual Studio .NET applications.
www.OnBarcode.com
QR Code Generation In Visual Studio .NET
Using Barcode creation for ASP.NET Control to generate, create QR Code ISO/IEC18004 image in ASP.NET applications.
www.OnBarcode.com
Note
Drawing QR Code JIS X 0510 In VB.NET
Using Barcode maker for Visual Studio .NET Control to generate, create QR image in Visual Studio .NET applications.
www.OnBarcode.com
GS1 - 12 Printer In .NET Framework
Using Barcode creator for .NET framework Control to generate, create UCC - 12 image in Visual Studio .NET applications.
www.OnBarcode.com
For simplicity's sake, all statistics and graphs shown in this section were collected against the Performance database I used in this chapter, where the level of fragmentation of indexes was minimal. When you conduct benchmarks and performance tests, make sure you introduce the appropriate levels of fragmentation in the indexes in your test system so that they reflect the fragmentation levels of the indexes in your
Creating USS Code 128 In .NET
Using Barcode drawer for .NET Control to generate, create Code 128A image in Visual Studio .NET applications.
www.OnBarcode.com
Data Matrix 2d Barcode Generation In Visual Studio .NET
Using Barcode maker for VS .NET Control to generate, create DataMatrix image in VS .NET applications.
www.OnBarcode.com
production system adequately. The performance of ordered scans might vary significantly based on the level of fragmentation of your indexes. Similarly, you also need to examine the average page densities in your production system, and introduce similar page densities in the test system.
Bar Code Generator In .NET
Using Barcode generation for VS .NET Control to generate, create barcode image in .NET framework applications.
www.OnBarcode.com
Generating UPC-E Supplement 5 In Visual Studio .NET
Using Barcode creator for Visual Studio .NET Control to generate, create UPC - E1 image in .NET framework applications.
www.OnBarcode.com
Besides having the ability to design good indexes, it is also important to be able to identify which indexes are used more heavily and which are rarely or never used. You don't want to keep indexes that are rarely used, as they do have negative performance effects on modifications. In SQL Server 2000, the only available techniques to determine index usage were very awkward, to say the least. For example, you could take a script that contains a representative sampling of the queries that run in the system and produce textual SHOWPLAN information for the queries in the script. You could write an application that parses the output, extracts the names of the indexes that are used, and calculates index usage statistics. Obviously, it's a problematic technique that also requires a fair amount of effort. Another option you could use in SQL Server 2000 is to run the Index Tuning Wizard (ITW) based on a sample workload. Among the reports that ITW generates is a report that shows you usage statistics of the existing indexes for the input queries. In SQL Server 2005, you have much more powerful and convenient tools to work with. You get a DMF called dm_db_index_operational_stats and a DMV called dm_db_index_usage_stats . The dm_db_index_operational_stats DMF gives you low-level I/O, locking, latching, and access method activity information. You provide the function a database ID, object ID, index ID (or 0 for a heap), and partition ID. You can also request information about multiple entities by specifying a NULL in the relevant argument. For example, to get information about all objects, indexes, and partitions in the Performance database, you would invoke the function as follows: SELECT * FROM sys.dm_db_index_operational_stats( DB_ID('Performance'), null, null, null);
Code 3 Of 9 Generator In Objective-C
Using Barcode generator for iPad Control to generate, create Code 3/9 image in iPad applications.
www.OnBarcode.com
2D Barcode Creation In VB.NET
Using Barcode creator for .NET framework Control to generate, create Matrix Barcode image in VS .NET applications.
www.OnBarcode.com
The dm_db_index_usage_stats DMV gives you usage counts of the different index operations: SELECT * FROM sys.dm_db_index_usage_stats;
UPC-A Supplement 5 Creator In Visual C#
Using Barcode maker for Visual Studio .NET Control to generate, create UPCA image in Visual Studio .NET applications.
www.OnBarcode.com
Make EAN-13 In Objective-C
Using Barcode creator for iPhone Control to generate, create UPC - 13 image in iPhone applications.
www.OnBarcode.com
These dynamic management objects make the analysis of index usage much simpler and more accurate than before.
Make Barcode In .NET Framework
Using Barcode maker for ASP.NET Control to generate, create barcode image in ASP.NET applications.
www.OnBarcode.com
Scanning UPC-A In Java
Using Barcode reader for Java Control to read, scan read, scan image in Java applications.
www.OnBarcode.com
Fragmentation
Generate PDF417 In C#
Using Barcode generator for Visual Studio .NET Control to generate, create PDF-417 2d barcode image in .NET applications.
www.OnBarcode.com
Encoding Code-128 In Java
Using Barcode drawer for Eclipse BIRT Control to generate, create Code 128 Code Set C image in BIRT applications.
www.OnBarcode.com
I referred to index fragmentation on multiple occasions in this chapter. When I mentioned fragmentation, I referred to a type known as logical scan fragmentation or average fragmentation in percent or external fragmentation . As I mentioned earlier, this type reflects the percentage of outof-order pages in the index, in terms of their physical order vs. their logical order in the linked list. Remember that this fragmentation can have a substantial impact on ordered scan operations in indexes. It has no effect on operations that do not rely on the index's linked listfor example, seek operations, lookups, unordered scans, and so on. You want to minimize the fragmentation level of indexes for queries with a substantial portion of their cost involved with ordered scans. You do so by rebuilding or reorganizing indexes.
Another type of fragmentation that you typically care about is what I referred to as average page density. Some database professionals refer to this type of fragmentation as internal fragmentation , but to avoid confusion I consciously didn't use this term earlier. While logical scan fragmentation is never a good thing, average scan fragmentation has two facets. A low percentage (low level of page population) has a negative impact on queries that read data, as they end up reading more pages than they could potentially if the pages were better populated. The positive impact of having some free space in index pages is that insertions of rows to such pages would not cause page splitswhich are very expensive. As you can guess, free space in index pages is bad in systems that involve mostly reads (for example, data warehouses) and good for systems that involve many inserts (for example, OLTP systems). You might even want to introduce some free space in index pages by specifying a fillfactor value when you rebuild your indexes. To determine whether you need to rebuild or reorganize your indexes, you need information about both types of fragmentation. In SQL Server 2005, you can get this information by querying the DMF dm_db_index_physical_stats . For example, the following query will return fragmentation information about the indexes in the Performance database: SELECT * FROM sys.dm_db_index_physical_stats( DB_ID('Performance'), NULL, NULL, NULL, NULL);
The fragmentation types I mentioned will show up in the attributes avg_fragmentation_in_percent and avg_page_space_used_in_percent , and as you can see, the attribute names are self explanatory. In SQL Server 2000, you use the DBCC SHOWCONTIG command to get similar information: DBCC SHOWCONTIG WITH ALL_INDEXES, TABLERESULTS, NO_INFOMSGS;
The two attributes of interest to our discussion are LogicalFragmentation and AveragePageDensity . As I mentioned earlier, to treat both types of fragmentation you need to rebuild or reorganize the index. Rebuilding an index has the optimal defragmentation effect. The operation makes its best attempt to rebuild the index in the same physical order on disk as in the linked list and to make the pages as contiguous as possible. Also, remember that you can specify a fillfactor to introduce some free space in the index leaf pages. In SQL Server 2000, index rebuilds were offline operations. Rebuilding a clustered index acquired an exclusive lock for the whole duration of the operation, meaning that process can neither read nor write to the table. Rebuilding a nonclustered index acquired a shared lock, meaning that writes are blocked against the table, and obviously, the index can not be used during the operation. SQL Server 2005 introduces online index operations that allow you to create, rebuild, and drop indexes online. In addition, these operations allow users to interact with the data while the operation is in progress. Online index operations use the new row-versioning technology introduced in SQL Server 2005. When an index is rebuilt online, SQL Server actually maintains two indexes behind the scenes, and when the operation is done, the new one overrides the old one. As an example, the following code rebuilds the idx_cl_od index on the Orders table online: ALTER INDEX idx_cl_od ON dbo.Orders REBUILD WITH (ONLINE = ON);
Note that online index operations need sufficient space in the database and overall are slower than offline operations. If you can spare a maintenance window for the activity to work offline, you better do so. Even when you do perform the operations online, they will have a performance impact on the
system while they are running, so it's best to run them during off-peak hours. Instead of rebuilding an index, you can also reorganize it. Reorganizing an index involves a bubble sort algorithm to sort the index pages physically on disk according to their order in the index's linked list. The operation does not attempt to make the pages more contiguous (reduce gaps). As you can guess, the defragmentation level that you get from this operation is not as optimal as fully rebuilding an index. Also, overall, this operation performs more logging than an index rebuild and, therefore, is typically slower. So why use this type of defragmentation First, in SQL Server 2000 it was the only online defragmentation utility. The operation grabs short-term locks on a pair of pages at a time to determine whether they are in the correct order, and if they are not, it swaps them. Second, an index rebuild must run as a single transaction, and if it's aborted while it's in process, the whole activity is rolled back. This is unlike an index reorganize operation, which can be interrupted as it operates on a pair of pages at a time. When you later run the reorganize activity again, it will pick up where it left off earlier. Here's how you would reorganize the idx_cl_od index in SQL Server 2005: ALTER INDEX idx_cl_od ON dbo.Orders REORGANIZE;
Copyright © OnBarcode.com . All rights reserved.