Files and Filegroups

Figure 10-1 provides a high-level depiction of the objects used to organize the files (I’m ignoring logs in this chapter, because you don’t have direct access to them).

At the top level of a SQL Server instance, we have the database. The database is comprised of one or more filegroups, which are logical groupings of one or more files. We can place different filegroups on different disk drives (hopefully on a different disk drive controller) to distribute the I/O load evenly across the available hardware. It’s possible to have multiple files in the filegroup, in which case SQL Server allocates space across each file in the filegroup. For best performance, it’s generally best to have no more files in a filegroup than you have physical CPUs (not including hyperthreading, though the rules with hyperthreading are changing as processors continue to improve faster than one could write books on the subject).

A filegroup contains one or more files, which are actual operating system files. Each database has at least one primary filegroup, whose files are called primary files (commonly suffixed as .mdf, although there’s no requirement to give the files any particular names or extension). Each database can possibly have other secondary filegroups containing the secondary files (commonly suffixed as .ndf), which are in any other filegroups. Files may only be a part of a single filegroup. SQL Server proportionally fills files by allocating extents in each filegroup equally, so you should make all of the files the same size if possible. (There is also a file type for full-text indexing and a filegroup type we used in Chapter 7 for filestream types of data that I will largely ignore in this chapter as well. I will focus only on the core file types that you will use for implementing your structures.)

You control the placement of objects that store physical data pages at the filegroup level (code and metadata is always stored on the primary filegroup, along with all the system objects). New objects created are placed in the default filegroup, which is the PRIMARY filegroup (every database has one as part of the CREATE DATABASE statement, or the first file specified is set to primary) unless another filegroup is specified in any CREATE <object> commands. For example, to place an object in a filegroup other than the default, you need to specify the name of the filegroup using the ON clause of the table- or index-creation statement:

 CREATE TABLE <tableName>

 (…) ON <fileGroupName>

This command assigns the table to the filegroup, but not to any particular file. Where in the files the object is created is strictly out of your control.

Use code like the following to create indexes and specify a filegroup:

 CREATE INDEX <indexName> ON <tableName> (<columnList>) ON <filegroup>;

Use the following type of command (or use ALTER TABLE) to create constraints that in turn create indexes (UNIQUE, PRIMARY KEY):

 CREATE TABLE <tableName>

 (

      …

      <primaryKeyColumn> int CONSTRAINT PKTableName ON <fileGroup>

      …

 );

For the most part, having just one filegroup and one file is the best practice for a large number of databases. If you are unsure if you need multiple filegroups, my advice is to build your database on a single filegroup and see if the data channel provided can handle the I/O volume (for the most part, I will avoid making too many such generalizations, as tuning is very much an art that requires knowledge of the actual load the server will be under). As activity increases and you build better hardware with multiple CPUs and multiple drive channels, you might place indexes on their own filegroup, or even place files of the same filegroup across different controllers.

In the following example, I create a sample database with two filegroups, with the secondary filegroup having two files in it (I put this sample database in an SQLData folder in the root of the C drive to keep the example simple (and able to work on the types of drives that many of you will probably be testing my code on), but it is rarely a good practice to place your files on the C: drive when you have others available. I generally put a sql directory on every drive and put everything SQL in that directory in subfolders to keep things consistent over all of our servers. Put the files wherever works best for you.):

 CREATE DATABASE demonstrateFilegroups ON

 PRIMARY ( NAME = Primary1, FILENAME = 'c:sqldatademonstrateFilegroups_primary.mdf',

           SIZE = 10MB),

 FILEGROUP SECONDARY

           ( NAME = Secondary1, FILENAME = 'c:sqldatademonstrateFilegroups_secondary1.ndf',

           SIZE = 10MB),

           ( NAME = Secondary2, FILENAME = 'c:sqldatademonstrateFilegroups_secondary2.ndf',

           SIZE = 10MB)

 LOG ON ( NAME = Log1,FILENAME = 'c:sqllogdemonstrateFilegroups_log.ldf', SIZE = 10MB);

You can define other file settings, such as minimum and maximum sizes and growth. The values you assign depend on what hardware you have. For growth, you can set a FILEGROWTH parameter that allows you to grow the file by a certain size or percentage of the current size, and a MAXSIZE parameter, so the file cannot just fill up existing disk space. For example, if you wanted the file to start at 1GB and grow in chunks of 100MB up to 2GB, you could specify the following:

 CREATE DATABASE demonstrateFileGrowth ON

 PRIMARY ( NAME = Primary1,FILENAME = 'c:sqldatademonstrateFileGrowth_primary.mdf',

           SIZE = 1GB, FILEGROWTH=100MB, MAXSIZE=2GB)

 LOG ON ( NAME = Log1,FILENAME = 'c:sqlldatademonstrateFileGrowth_log.ldf', SIZE = 10MB);

The growth settings are fine for smaller systems, but it’s usually better to make the files large enough so that there’s no need for them to grow. File growth can be slow and cause ugly bottlenecks when OLTP traffic is trying to use a file that’s growing. When SQL Server is running on a desktop operating system like Windows XP or greater (and at this point you probably ought to be using something greater like Windows 7—(or presumably Windows 8 or 9 depending on when you are reading this) or on a server operating system such as Windows Server 2003 or greater (again, it is 2012, so emphasize “or greater”), you can improve things by using “instant” file allocation (though only for data files). Instead of initializing the files, the space on disk can simply be allocated and not written to immediately. To use this capability, the system account cannot be LocalSystem, and the user account that the SQL Server runs under must have SE_MANAGE_VOLUME_NAME Windows permissions. Even with the existence of instant file allocation, it’s still going to be better to have some idea of what size data you will have and allocate space proactively, as you then have cordoned off the space ahead of time: no one else can take it from you, and you won’t fail when the file tries to grow and there isn’t enough space. In either event, the DBA staff should be on top of the situation and make sure that you don’t run out of space.

You can query the sys.filegroups catalog view to view the files in the newly created database:

 USE demonstrateFilegroups;

 GO

 SELECT case WHEN fg.name IS NULL

         then CONCAT('OTHER-',df.type_desc COLLATE database_default)

                 ELSE fg.name end as file_group,

      df.name as file_logical_name,

      df.physical_name as physical_file_name

 FROM sys.filegroups fg

        RIGHT JOIN sys.database_files df

            ON fg.data_space_id = df.data_space_id;

This returns the following results:

The LOG file isn’t technically part of a filegroup, so I used a right outer join to the database files and gave it a default filegroup name of OTHER plus the type of file to make the results include all files in the database. You may also notice a couple other interesting things in the code. First, the CONCAT function is new to SQL Server 2012 to add strings together.. Second is the COLLATE database_default. The strings in the system functions are in the collation of the server, while the literal OTHER- is in the database collation. If the server doesn’t match the database, this query would fail.

There’s a lot more information than just names in the catalog views I’ve referenced already in this chapter. If you are new to the catalog views, dig in and learn them. There is a wealth of information in those views that will be invaluable to you when looking at systems to see how they are set up and to determine how to tune them.

These databases won’t be used anymore, so if you created them, just drop them if you desire:

 USE MASTER;

 GO

 DROP DATABASE demonstrateFileGroups;

 GO

 DROP DATABASE demonstrateFileGrowth;

Extents and Pages

As shown in Figure 10-2, files are further broken down into a number of extents , each consisting of eight separate 8-KB pages where tables, indexes, and so on are physically stored. SQL Server only allocates space in a database to extents. When files grow, you will notice that the size of files will be incremented only in 64-KB increments.

Each extent in turn has eight pages that hold one specific type of data each:

• Data: Table data.
• Index: Index data.
• Overflow data : Used when a row is greater than 8,060 bytes or for varchar(max), varbinary(max), text, or image values.
• Allocation map : Information about the allocation of extents.
• Page free space : Information about what different pages are allocated for.
• Index allocation : Information about extents used for table or index data.
• Bulk changed map : Extents modified by a bulk INSERT operation.
• Differential changed map : Extents that have changed since the last database backup command. This is used to support differential backups.

In larger databases, most extents will contain just one type of page, but in smaller databases, SQL Server can place any kind of page in the same extent. When all data is of the same type, it’s known as a uniform extent. When pages are of various types, it’s referred to as a mixed extent.

SQL Server places all table data in pages, with a header that contains metadata about the page (object ID of the owner, type of page, and so on), as well as the rows of data, which I’ll cover later in this chapter. At the end of the page are the offset values that tell the relational engine where the rows start.

Figure 10-3 shows a typical data page from a table. The header of the page contains identification values such as the page number, the object ID of the object the data is for, compression information, and so on. The data rows hold the actual data. Finally, there’s an allocation block that has the offsets/pointers to the row data.

Figure 10-3 shows that there are pointers from the next to the previous rows. These pointers are only used when pages are ordered, such as in the pages of an index. Heap objects (tables with no clustered index) are not ordered. I will cover this a bit later in the “Index Types” section.

The other kind of page that is frequently used that you need to understand is the overflow page . It is used to hold row data that won’t fit on the basic 8,060-byte page. There are two reasons an overflow page is used:

• The combined length of all data in a row grows beyond 8,060 bytes. In versions of SQL Server prior to 2000, this would cause an error. In versions after this, data goes on an overflow page automatically, allowing you to have virtually unlimited row sizes.
• By setting the sp_tableoption setting on a table for large value types out of row to 1, all the (max) and XML datatype values are immediately stored out of row on an overflow page. If you set it to 0, SQL Server tries to place all data on the main page in the row structure, as long as it fits into the 8,060-byte row. The default is 0, because this is typically the best setting when the typical values are short enough to fit on a single page.

For example, Figure 10-4 depicts the type of situation that might occur for a table that has the large value types out of row set to 1. Here, Data Row 1 has two pointers to a varbinary(max) columns: one that spans two pages and another that spans only a single page. Using all of the data in Data Row 1 will now require up to four reads (depending on where the actual page gets stored in the physical structures), making data access far slower than if all of the data were on a single page. This kind of performance problem can be easy to overlook, but on occasion, overflow pages will really drag down your performance, especially when other programmers use SELECT * on tables where they don’t really need all of the data.

The overflow pages are linked lists that can accommodate up to 2GB of storage in a single column. Generally speaking, it isn’t really a very good idea to store 2GB in a single column (or even a row), but the ability to do so is available if needed.

Understand that storing large values that are placed off of the main page will be far more costly when you need these values than if all of the data can be placed in the same data page. On the other hand, if you seldom use the data in your queries, placing them off the page can give you a much smaller footprint for the important data, requiring far less disk access on average. It is a balance that you need to take care with, as you can imagine how costly a table scan of columns that are on the overflow pages is going to be. Not only will you have to read extra pages, you have to be redirected to the overflow page for every row that’s overflowed.

Be careful when allowing data to overflow the page. It’s guaranteed to make your processing more costly, especially if you include the data that’s stored on the overflow page in your queries—for example, if you use the dreaded SELECT * regularly in production code! It’s important to choose your datatypes correctly to minimize the size of the data row to include only frequently needed values. If you frequently need a large value, keep it in row; otherwise, consider placing it off row or even create two tables and join them together as needed.

Data on Pages

When you get down to the row level, the data is laid out with metadata, fixed length fields, and variable length fields, as shown in Figure 10-5. (Note that this is a generalization, and the storage engine does a lot of stuff to the data for optimization, especially when you enable compression.)

The metadata describes the row, gives information about the variable length fields, and so on. Generally speaking, since data is dealt with by the query processor at the page level, even if only a single row is needed, data can be accessed very rapidly no matter the exact physical representation.

The maximum amount of data that can be placed on a single page (including overhead from variable fields) is 8,060 bytes. As illustrated in Figure 10-4, when a data row grows larger than 8,060 bytes, the data in variable length columns was can spill out onto an overflow page. A 16-byte pointer is left on the original page and points to the page where the overflow data is placed.

Partitioning

The last general physical structure concept that I will introduce is partitioning. Partitioning allows you to break a table (or index) into multiple physical structures by breaking them into more manageable chunks. Partitioning can allow SQL Server to scan data from different processes, enhancing opportunities for parallelism. SQL Server 7.0 and 2000 had partitioned views, where you would define a view and a set of tables, with each serving as a partition of the data. If you have properly defined (and trusted) constraints, SQL Server would use the WHERE clause to know which of the tables referenced in the view would have to be scanned in response to a given query. The data in the view was also editable like in a normal table. One thing you still can do with partitioned views is to build distributed partitioned views, which reference tables on different servers.

In SQL Server 2005, you could begin to define partitioning as part of the table structure. Instead of making a physical table for each partition, you define, at the DDL level, the different partitions of the table. Internally, the table is broken into the partitions based on a scheme that you set up. Note, too, that this feature is only included in the Enterprise Edition.

At query time, SQL Server can then dynamically scan only the partitions that need to be searched, based on the criteria in the WHERE clause of the query being executed. I am not going to describe partitioning too much, but I felt that it needed a mention in this edition of this book as a tool at your disposal with which to tune your databases, particularly if they are very large or very active. For deeper coverage, I would suggest you consider on of Kalen Delaney’s SQL Server Internals books. They are the gold standard in understanding the internals of SQL Server.

I will, however, present the following basic example of partitioning. Use whatever database you desire. I used tempdb for the data and AdventureWorks2012 for the sample data on my test machine and included the USE statement in the code download. The example is that of a sales order table. I will partition the sales into three regions based on the order date. One region is for sales before 2006, another for sales between 2006 and 2007, and the last for 2007 and later. The first step is to create a partitioning function. You must base the function on a list of values, where the VALUES clause sets up partitions that the rows will fall into based on the smalldatetime values that are presented to it, for example:

 CREATE PARTITION FUNCTION PartitionFunction$dates (smalldatetime)

 AS RANGE LEFT FOR VALUES ('20060101','20070101'),

    --set based on recent version of

    --AdventureWorks2012.Sales.SalesOrderHeader table to show

    --partition utilization

Specifying the function as RANGE LEFT says that the values in the comma-delimited list should be considered the boundary on the side listed. So in this case, the ranges would be as follows:

• value <= '20060101'
• value > '20060101' and value <= '20070101'
• value > '20070101'

Specifying the function as RANGE RIGHT would have meant that the values lie to the right of the values listed, in the case of our ranges, for example:

• value < '20060101'
• value >= '20060101' and value < '20070101'
• value >= '20070101'

Next, use that partition function to create a partitioning scheme:

 CREATE PARTITION SCHEME PartitonScheme$dates

    AS PARTITION PartitionFunction$dates ALL to ( [PRIMARY] );

which will let you know:

With the CREATE PARTITION SCHEME command, you can place each of the partitions you previously defined on a specific filegroup. I placed them all on the same filegroup for clarity and ease, but in practice, you usually want them on different filegroups, depending on the purpose of the partitioning. For example, if you were partitioning just to keep the often-active data in a smaller structure, placing all partitions on the same filegroup might be fine. But if you want to improve parallelism or be able to just back up one partition with a filegroup backup, you would want to place your partitions on different filegroups.

Next, you can apply the partitioning to a new table. You’ll need a clustered index involving the partition key. You apply the partitioning to that index. Following is the statement to create the partitioned table:

 CREATE TABLE dbo.salesOrder

 (

       salesOrderIdint NOT NULL,

       customerIdint NOT NULL,

       orderAmountdecimal(10,2) NOT NULL,

       orderDatesmalldatetime NOT NULL,

       CONSTRAINT PKsalesOrder primary key nonclustered (salesOrderId)

                                  ON [Primary],

       CONSTRAINT AKsalesOrder unique clustered (salesOrderId, orderDate)

 ) on PartitonScheme$dates (orderDate);

Next, load some data from the AdventureWorks2012.Sales.SalesOrderHeader table to make looking at the metadata more interesting. You can do that using an INSERT statement such as the following:

 INSERT INTO dbo.salesOrder(salesOrderId, customerId, orderAmount, orderDate)

 SELECT SalesOrderID, CustomerID, TotalDue, OrderDate

 FROM AdventureWorks2012.Sales.SalesOrderHeader;

You can see what partition each row falls in using the $partition function. You suffix the $partition function with the partition function name and the name of the partition key (or a partition value) to see what partition a row’s values are in, for example:

 SELECT *, $partition.PartitionFunction$dates(orderDate) as partiton

 FROM dbo.salesOrder;

You can also view the partitions that are set up through the sys.partitions catalog view. The following query displays the partitions for our newly created table:

 SELECT partitions.partition_number, partitions.index_id,

        partitions.rows, indexes.name, indexes.type_desc

 FROM sys.partitions as partitions

       JOIN sys.indexes as indexes

          on indexes.object_id = partitions.object_id

               AND indexes.index_id = partitions.index_id

 WHERE partitions.object_id = object_id('dbo.salesOrder'),

This will return the following:

Partitioning is not a general purpose tool that should be used on every table, which is one of the reasons why it is only included in Enterprise Edition. However, partitioning can solve a good number of problems for you, if need be:

• Performance: If you only ever need the past month of data out of a table with three years’ worth of data, you can create partitions of the data where the current data is on a partition and the previous data is on a different partition.
• Rolling windows: You can remove data from the table by dropping a partition, so as time passes, you add partitions for new data and remove partitions for older data (or move to a different archive table).
• Maintainence: Some maintainance can be done at the partition level rather than the entire table, so once partition data is read-only, you may not need to maintain any longer. Some caveats do apply (you cannot rebuild a partitioned index online, for example.)

Clustered Indexes

A clustered index physically orders the pages of the data in the table. The leaf pages of the clustered indexes are the data pages of the table. Each of the data pages is then linked to the next page in a doubly linked list to provide ordered scanning. The leaf pages of the clustered index are the actual data pages. In other words, the records in the physical structure are sorted according to the fields that correspond to the columns used in the index. Tables with clustered indexes are referred to as clustered tables.

The key of a clustered index is referred to as the clustering key, and this key will have additional uses that will be mentioned later in this chapter. For clustered indexes that aren’t defined as unique, each record has a 4-byte value (commonly known as an uniquifier) added to each value in the index where duplicate values exist. For example, if the values were A, B, and C, you would be fine. But, if you added another value B, the values internally would be A, B + 4ByteValue, B + Different4ByteValue, and C. Clearly, it is not optimal to get stuck with 4 bytes on top of the other value you are dealing with in every level of the index, so in general, you should try to use the clustered index on a set of columns where the values are unique.

Figure 10-9 shows, at a high level, what a clustered index might look like for a table of animal names. (Note that this is just a partial example, there would likely be more second-level pages for Horse and Python at a minimum.)

You can have only one clustered index on a table, because the table cannot be ordered in more than one direction. (Remember this; it is one of the most fun interview questions. Answering anything other than “one clustered index per table” leads to a fun line of followup questioning.)

A good real-world example of a clustered index would be a set of old-fashioned encyclopedias. Each letter is a level of the index, and on each page, there is another level that denotes the things you can find on each page (e.g., Office–Officer). Then. each topic is the leaf level of the index. The encyclopedia is are clustered on the topics in these books, just as the example was clustered on the name of the animal. In essence, the entire book is a table of information in clustered ordered. And indexes can be partitioned as well. The encylopedias are partitioned by letter into multiple books.

Now, consider a dictionary. Why are the words sorted, rather than just having a separate index with the words not in order? I presume that at least part of the reason is to let the readers scan through words they don’t know exactly how to spell, checking the definition to see if the word matches what they expect. SQL Server does something like this when you do a search. For example, back in Figure 10-9, if you were looking for a cat named George, you could use the clustered index to find rows where animal = 'Cat', then scan the data pages for the matching pages for any rows where name = 'George'.

I must caution you that although it’s true, physically speaking, that tables are stored in the order of the clustered index; logically speaking, tables must be thought of as having no order. (I know I promised to not mention this again back in Chapter 1, but it really is an important thing to remember.) This lack of order is a fundamental truth of relational programming: you aren’t required to get back data in the same order when you run the same query twice. The ordering of the physical data can be used by the query processor to enhance your performance, but during intermediate processing, the data can be moved around in any manner that results in faster processing the answer to your query. It’s true that you do almost always get the same rows back in the same order, mostly because the optimizer is almost always going to put together the same plan every time the same query is executed under the same conditions. However, load up the server with many requests, and the order of the data might change so SQL Server can best use its resources, regardless of the data’s order in the structures. SQL Server can choose to return data to us in any order that’s fastest for it. If disk drives are busy in part of a table and it can fetch a different part, it will. If order matters, use an ORDER BY clause to make sure that data is returned as you want.

Nonclustered Indexes

Nonclustered index structures are fully independent of the underlying table. Where a clustered index is like a dictionary with the index physically linked to the table (since the leaf pages of the index are a part of the table), nonclustered indexes are more like indexes in a textbook. A nonclustered index is completely separate from the data, and on the leaf page, there are pointers to go to the data pages much like the index of a book contains page numbers.

Each leaf page in a nonclustered index contains some form of pointer to the rows on the data page. The pointer from the index to a data row is known as a row locator. Exactly how the row locator of a nonclustered indexes is structured is based on whether or not the underlying table has a clustered index.

In this section, I will first show an abstract representation of the nonclustered index and then show the differences between the implementation of a nonclustered index when you do and do not also have a clustered index. At an abstract level, all nonclustered indexes follow the form shown in Figure 10-10.

The major difference between the two possibilities comes down to the row locator being different based on whether the underlying table has a clustered index. There are two different types of pointer that will be used:

• Tables with a clustered index: Clustering key
• Tables without a clustered index: Pointer to physical location of the data

In the next two sections, I’ll explain these in more detail. New in 2012 is a new type of index that I will briefly mention called a columnstore index. Rather than store index data per row, they store data by column. They are typical indexes, in that they don’t change the structure of the data, but they do make the table read only. It is a very exciting feature for data warehouse types of queries, but in relational, OLTP databases, there isn’t really a use for columnstore indexes. In Chapter 14, a columnstore index will be used in an example for the data warehouse overview, but that will be the extent of discussion on the subject. They are structured quite differently from classic B-tree indices, so if you do get to building a data warehouse (or if you are also involved with building dimensional databases), you will want to understand how they work and how they are structured as they will likely provide your star schema queries with a tremendous performance increase.

Nonclustered Indexes on Clustered Tables

When a clustered index exists on the table, the row locator for the leaf node of any nonclustered index is the clustering key from the clustered index. In Figure 10-10, the structure on the right side represents the clustered index, and on the left, the nonclustered index. To find a value, you start at the leaf node of the index and traverse the leaf pages. The result of the index traversal is the clustering key, which you then use to traverse the clustered index to reach the data, as shown in Figure 10-11.

The overhead of the operation I’ve just described is minimal as long as you keep your clustering key optimal and the index maintained. While having to scan two indexes is more work than just having a pointer to the physical location you have to think of the overall picture. Overall, it’s better than having direct pointers to the table, because only minimal reorganization is required for any modification of the values in the table. Consider if you had to maintain a book index manually. If you used the book page as the way to get to an index value, if had to add a page to the book in the middle you would have to update all of the page numbers. But if all of the topics were ordered alphabetically, and you just pointed to the topic name adding a topic would be easy.

The same is true for SQL Server and the structures can be changed thousands of times a second or more. Since there is very little hardware-based information lingering in the structure, data movement is easy for the query processor, and maintaining indexes is an easy operation. Early versions of SQL Server used physical location pointers, and this led to all manners of corruption in our indexes and tables. And let’s face it, the people with better understanding of such things also tell us that when the size of the clustering key is adequately small, this method is remarkably faster overall than having pointers directly to the table.

The primary benefit of the key structure becomes more obvious when we talk about modification operations. Because the clustering key is the same regardless of physical location, only the lowest level of the clustered index need know where the physical data is. Add to this that the data is organized sequentially, and the overhead of modifying indexes is significantly lowered making all of the data modification operations far faster. Of course, this benefit is only true if the clustering key rarely, or never, changes. Therefore, the general suggestion is to make the clustering key a small nonchanging value, such as an identity column (but the advice section is still a few pages away).

Nonclustered Indexes on a Heap

If a table does not have a clustered index, the table is physically referred to as a heap. One definition of a heap is “a group of things placed or thrown one on top of the other.” This is a great way to explain what happens in a table when you have no clustered index: SQL Server simply puts every new row on the end of the last page for the table. Once that page is filled up, it puts a data on the next page or a new page as needed.

When building a nonclustered index on a heap, the row locator is a pointer to the physical page and row that contains the row. As an example, take the example structure from the previous section with a nonclustered index on the name column of an animal table, represented in Figure 10-12.

If you want to find the row where name ='Dog', you first find the path through the index from the top-level page to the leaf page. Once you get to the leaf page, you get a pointer to the page that has a row with the value, in this case Page 102, Row 1. This pointer consists of the page location and the record number on the page to find the row values (the pages are numbered from 0, and the offset is numbered from 1). The most important fact about this pointer is that it points directly to the row on the page that has the values you’re looking for. The pointer for a table with a clustered index (a clustered table) is different, and this distinction is important to understand because it affects how well the different types of indexes perform.

To avoid the types of physical corruption issues that, as I mentioned in the previous section, can occur when you are constantly managing pointers and physical locations, heaps use a very simple method of keeping the row pointers from getting corrupted. Instead of reordering pages, or changing pointers if the page must be split, it moves rows to a different page and a forwarding pointer is left to point to the new page where the data is now. So if the row where name ='Dog' had moved (for example, due to a large varchar(3000) column being updated from a data length of 10 to 3,000), you might end up with following situation to extend the number of steps required to pick up the data. In Figure 10-13, a forwarding pointer is illustrated.

All existing indexes that have the old pointer simply go to the old page and follow the forwarding pointer on that page to the new location of the data. If you are using heaps, which should be rare, it is important to be careful with your structures to make sure that data should rarely be moved around within a heap. For example, you have to be careful if you’re often updating data to a larger value in a variable length column that’s used as an index key, it’s possible that a row may be moved to a different page. This adds another step to finding the data, and if the data is moved to a page on a different extent, another read to the database. This forwarding pointer is immediately followed when scanning the table, causing possible horrible performance over time if it’s not managed.

Space is not reused in the heap without rebuilding the table (by selecting into another table, adding a clustered index temporarily, or in 2008, using the ALTER TABLE command with the REBUILD option). In the last section of this chapter on Indexing Dynamic Management View queries, I will provide a query that will give you information on the structure of your index, including the count of forwarding pointers in your table.

Using Clustered Indexes

The column you use for the clustered index will (because the key is used as the row locator), become a part of every index for your table, so it has heavy implications for all indexes. Because of this, for a typical OLTP system, a very common practice is to choose a surrogate key value, often the primary key of the table, since the surrogate can be kept very small.

Using the surrogate key as the clustering key is usually a great decision, not only because is it a small key (most often, the datatype is an integer that requires only 4 bytes or possibly less using compression), but because it’s always a unique value. As mentioned earlier, a nonunique clustering key has a 4-byte uniquifier tacked onto its value when keys are not unique. It also helps the optimizer that an index has only unique values, because it knows immediately that for an equality operator, either 1 or 0 values will match. Because the surrogate key is often used in joins, it’s helpful to have smaller keys for the primary key.

The clustered index won’t always be used for the surrogate key or even the primary key. Other possible uses can fall under the following types:

• Range queries : Having all the data in order usually makes sense when there’s data that you often need to get a range, such as from A to F. An example where this can make sense is for the child rows in a parent child relationship.
• Data that’s always accessed sequentially: Obviously, if the data needs to be accessed in a given order, having the data already sorted in that order will significantly improve performance.
• Queries that return large result sets: This point will make more sense once I cover nonclustered indexes, but for now, note that having the data on the leaf index page saves overhead.

The choice of how to pick the clustered index depends on a couple factors, such as how many other indexes will be derived from this index, how big the key for the index will be, and how often the value will change. When a clustered index value changes, every index on the table must also be touched and changed, and if the value can grow larger, well, then we might be talking page splits. This goes back to understanding the users of your data and testing the heck out of the system to verify that your index choices don’t hurt overall performance more than they help. Speeding up one query by using one clustering key could hurt all queries that use the nonclustered indexes, especially if you chose a large key for the clustered index.

Frankly, in an OLTP setting, in all but the most unusual cases, I stick with a surrogate key for my clustering key, usually one of the integer types or sometimes even the uniqueidentifier (GUID) type (ideally the sequential type). I use the surrogate key because so many of the queries you do for modification (the general goal of the OLTP system) will access the data via the primary key. You then just have to optimize retrievals, which should also be of generally small numbers of rows, and doing so is usually pretty easy.

Another thing that is good about using the clustered index on a monotonically increasing value is that page splits over the entire index are greatly decreased. The table grows only on one end of the index, and while it does need to be rebuilt occasionally using ALTER INDEX REORGANIZE or ALTER INDEX REBUILD, you don’t end up with page splits all over the table. You can decide which to do by using the criteria stated by SQL Server Books Online. By looking in the dynamic management view sys.dm_db_index_physical_stats, you can use REBUILD on indexes with greater than 30% fragmentation and use REORGANIZE otherwise. Now, let’s look at an example of a clustered index in use. If you have a clustered index on a table, instead of Table Scan, you’ll see a Clustered Index Scan in the plan:

 SELECT *

 FROM   produce.vegetable;

The plan for this query is as follows:

If you query on a value of the clustered index key, the scan will likely change to a seek. Although a scan touches all the data pages, a clustered index seek uses the index structure to find a starting place for the scan and knows just how far to scan. For a unique index with an equality operator, a seek would be used to touch one page in each level of the index to find (or not find) a single value on a single data page, for example:

 SELECT *

 FROM   produce.vegetable

 WHERE vegetableId = 4;

The plan for this query now does a seek:

Note the CONVERT_IMPLICIT of the @1 value. This shows the query is being parameterized for the plan, and the variable is cast to an integer type. In this case, you’re seeking in the clustered index based on the SEEK predicate of vegetableId = 4. SQL Server will create a reusable plan by default for simple queries. Any queries that are executed with the same exact format, and a simple integer value would use the same plan. You can let SQL Server paramaterize more complex queries as well. (For more information, look up “Simple Paramaterization and Forced Parameterization” in Books Online.) Increasing the complexity, now, we search for two rows:

 SELECT *

 FROM   produce.vegetable

 WHERE vegetableId in (1,4);

And in this case, pretty much the same plan is used, except the seek criteria now has an OR in it:

Note that it did not create a parameterized plan this time, but a fixed one with literals for 1 and 4. Note that this plan will be executed as two separate seek operations. If you turn on SET STATISTICS IO before running the query:

 SET STATISTICS IO ON;

 GO

 SELECT *

 FROM   produce.vegetable

 WHERE vegetableId in (1,4);

 Go

 SET STATISTICS IO OFF;

You will see that it did two “scans”, which using STATISTICS IO generally means any operation that probes the table, so a seek or scan would show up the same (note the lob values are for large objects such as (max) types):

But whether any given query uses a seek or scan, or even two seeks, can be a pretty complex question. Why it is so complex will become clearer over the rest of the chapter, and it will become instantly clearer how useful a clustered index seek is in the next section, “Using Nonclustered Indexes.”

A final consideration you need to think about is how the index will be used. There are two types of index usages, equality and inequality. In a query such as the previous ones, we have done as the following:

 SELECT *

 FROM   produce.vegetable

 WHERE vegetableId = 4;

Or even the queries with the IN Boolean expression, these have been equality operators, where the values that were being searched for were known at compile time. For inequality searches, you will look for a value greater than some other value, or between values. So for this query, it has the following plan:

Changing this to an inequality search, such as

 SELECT *

 FROM   produce.vegetable

 WHERE vegetableId > 4;

Now, you will get the following plan:

Note that, this time, even though we did a fairly simple query, it did not automatically parameterize. This is because the plan can change drastically based on the value passed.

Using Nonclustered Indexes

After you have made the ever-important choice of what to use for the clustered index, all other indexes will be nonclustered. In this section, I will cover nonclustered indexes in the following areas:

• General considerations
• Composite index considerations
• Nonclustered indexes with clustered tables
• Nonclustered indexes on heaps

Using Unique Indexes

An important index setting is UNIQUE. In the design of the tables, UNIQUE and PRIMARY KEY constraints were created to enforce keys. Behind the scenes, SQL Server employs unique indexes to enforce uniqueness over a column or group of columns. SQL Server uses them for this purpose because to determine if a value is unique, you have to look it up in the table. Because SQL Server uses indexes to speed access to the data, you have the perfect match.

Enforcing uniqueness is a business rule, and as I covered in Chapter 6, the rule of thumb is to use UNIQUE or PRIMARY constraints to enforce uniqueness on a set of columns. Now, as you’re improving performance, use unique indexes when the data you’re indexing allows it.

For example, say you’re building an index that happens to include a column (or columns) that is already a part of another unique index. Another possibility might be if you’re indexing a column that’s naturally unique, such as a GUID. It’s up to the designer to decide if this GUID is a key or not, and that depends completely on what it’s used for. Using unique indexes lets the optimizer determine more easily the number of rows it has to deal with in an equality operation.

Also note that it’s important for the performance of your systems that you use unique indexes whenever possible, as they enhance the SQL Server optimizer’s chances of predicting how many rows will be returned from a query that uses the index. If the index is unique, the maximum number of rows that can be returned from a query that requires equality is one. This is common when working with joins.

Indexing Foreign Keys

Foreign key columns are a special case where we often need an index of some type. This is because we build foreign keys so we can match up rows in one table to rows in another. For this, we have to take a value in one table and match it to another.

In an OLTP database that has proper constraints on alternate keys, often, we won’t need to index foreign keys beyond what we’re given with the unique indexes that are built as part of the structure of the database. This is probably why SQL Server doesn’t implement indexes for us when creating a foreign key constraint.

However, it’s important to make sure that any time you have a foreign key constraint declared, there’s the potential for need of an index whenever you have a parent table and you want to see the children of the row. A special and important case where this type of access is essential is when you have to delete the parent row in any relationship, even one of a domain type that has a very low cardinality. I will usually apply an index to all foreign keys as a default in my model, removing the index if it is obvious that it is not helping performance. One of the queries that will be presented in the Index Dynamic Management Views section will identify how or if indexes are being used.

Say you have five values in the parent table and five million in the child. For example, consider the case of a click log for a sales database, a snippet of which is shown in Figure 10-15.

Consider that you want to delete a clickType row that someone added inadvertently. Creating the row took several milliseconds. Deleting it shouldn’t take long at all, right? Well, even if there isn’t a single value in the table, if you don’t have an index on the foreign key in the siteClickLog table, it will take just over 10 seconds longer than eternity. Even though the value doesn’t exist in the table, the query processor would need to touch and check all five million rows for the value. From the statistics on the column, it can guess how many rows might exist, but it can’t definitively know that there is or is not a value because statistics are maintained asyncronously. (Ironically, because it is an existence search, it could fail on the first row it checked, but to successfully delete the row, every child row must be touched.)

However, if you have an index, deleting the row (or knowing that you can’t delete it) will take a very short period of time, because in the upper pages of the index, you’ll have all the unique values in the index, in this case, five values. There will be a fairly substantial set of leaf pages for the index, but only one page in each index level, usually no more than three or four pages, will need to be touched before the query processor can determine the existence of a row out of millions of rows. When NO ACTION is specified for the relationship, if just one row is found, the operation could be stopped. If you have cascading operations enabled for the relationship, the cascading options will need the index to find the rows to cascade to.

This adds more decisions when building indexes. Is the cost of building and maintaining the index during creation of millions of siteClickLog rows justified, or do you just bite the bullet and do deletes during off hours? Add a trigger such as the following, ignoring error handling in this example for brevity:

 CREATE TRIGGER clickType$insteadOfDelete

 ON clickType

 INSTEAD OF DELETE

 AS

    INSERT INTO clickType_deleteQueue (clickTypeId)

    SELECT  clickTypeId

    FROM    inserted;

Then, you let your queries that return lists of clickType rows check this table when presenting rows to the users:

 SELECT code, description, clickTypeId

 FROM   clickType

 WHERE NOT EXISTS (SELECT *

         FROM clickType_deleteQueue

         WHERE clickType.clickTypeId =

             clickType_deleteQueue.clickTypeId);

Now, assuming all code follows this pattern, the users will never see the value, so it won’t be an issue (at least not in terms of values being used; performance will suffer obviously). Then, you can delete the row in the wee hours of the morning without building the index. Whether or not an index proves useful generally depends on the purpose of the foreign key. I’ll mention specific types of foreign keys individually, each with their own signature usage:

• Domain tables: Used to implement a defined set of values and their descriptions
• Ownership: Used to implement a multivalued attribute of the parent
• Many-to-many resolution: Used to implement a many-to-many relationship physically
• One-to-one relationships: Cases where a parent may have only a single value in the related table

We’ll look at examples of these types and discuss when it’s appropriate to index them before the typical trial-and-error performance tuning, where the rule of thumb is to add indexes to make queries faster, while not slowing down other operations that create data.

In all cases, deleting the parent row requires a table scan of the child if there’s no index on the child row. This is an important consideration if there are deletes.

Indexing Views

I mentioned the use of persisted calculated columns in Chapter 6 for optimizing denormalizations for a single row, but sometimes, your denormalizations need to span multiple rows and include things like summarizations. In this section, I will introduce a way to take denormalization to the next level, using indexed views .

Indexing a view basically takes the virtual structure of the view and makes it a physical entity, albeit one managed completely by the query processor. The data to resolve queries with the view is generated as data is modified in the table, so access to the results from the view is just as fast as if it were an actual table. Indexed views give you the ability to build summary tables without any kind of manual operation or trigger; SQL Server automatically maintains the summary data for you. Creating indexed views is as easy as writing a query.

The benefits are twofold when using indexed views. First, when you use an indexed view directly in any edition of SQL Server, it does not have to do any calculations (editions less than Enterprise must specify NOEXPAND as a hint). Second, in the Enterprise Edition or greater (plus the Developer Edition), SQL Server automatically considers the use of an indexed view whenever you execute any query, even if the query doesn’t reference the view but the code you execute uses the same aggregates. SQL Server accomplishes this index view assumption by matching the executed query to each indexed view to see whether that view already has the answer to something you are asking for.

For example, we could create the following view on the product and sales tables in the Adventureworks2012 database. Note that only schema-bound views can be indexed as this makes certain that the tables and structures that the index is created upon won’t change underneath the view. (A more complete list of requirements is presented after the example.). Note that you actually have to be in the AdventureWorks2012 database, unlike other examples where I have addressed items to the database, because index views have strict requirements, which I will cover in more detail in the next section of this chapter.

 USE AdventureWorks2012;

 GO

 CREATE VIEW Production.ProductAverageSales

 WITH SCHEMABINDING

 AS

 SELECT Product.ProductNumber,

        SUM(SalesOrderDetail.LineTotal) as TotalSales,

        COUNT_BIG(*) as CountSales --must use COUNT_BIG for indexed view

 FROM Production.Product as Product

         JOIN Sales.SalesOrderDetail as SalesOrderDetail

           ON Product.ProductID=SalesOrderDetail.ProductID

 GROUP BY Product.ProductNumber;

This would do the calculations at execution time. We run the following query:

 SELECT ProductNumber, TotalSales, CountSales

 FROM   Production.ProductAverageSales;

The plan looks like this:

This is a big plan for such a small query, for sure, and it’s hard to follow. It scans the SalesOrderDetail table, computes our scalar values, and then does a hash match aggregate and a hash match join to join the two sets together. For further reading on the join types, again consider a book in Kalen Delaney’s SQL Server Internals series.

This query executes pretty fast on my 1.8-GHz 8-GB laptop, but there’s a slight but noticeable delay. Say this query wasn’t fast enough, or it used too many resources to execute, and it is used extremely often. In this case, we might add an index on the view. Note that it is a clustered index, as the data pages will be ordered based on the key we chose. Consider your structure of the index just like you would on a physical table.

 CREATE UNIQUE CLUSTERED INDEX XPKProductAverageSales on

       Production.ProductAverageSales(ProductNumber);

SQL Server would then materialize the view and store it. Now, our queries to the view will be very fast. However, although we’ve avoided all the coding issues involved with storing summary data, we have to keep our data up to date. Every time data changes in the underlying tables, the index on the view changes its data, so there’s a performance hit in maintaining the index for the view. Hence, indexing views means that performance is great for reading but not necessarily for updating.

Now, run the query again:

 SELECT ProductNumber, TotalSales, CountSales

 FROM   Production.ProductAverageSales;

The plan looks like the following:

No big deal, right? We expected this result because we directly queried the view. On my test system, running the Developer Edition (which is functionally comparable to the Enterprise Edition), you get a great insight into how cool this feature is in the following query for getting the average sales per product:

 SELECT Product.ProductNumber, SUM(SalesOrderDetail.LineTotal) / COUNT(*)

 FROM   Production.Product as Product

     JOIN Sales.SalesOrderDetail as SalesOrderDetail

       ON Product.ProductID=SalesOrderDetail.ProductID

 GROUP BY Product.ProductNumber;

We’d expect the plan for this query to be the same as the first query of the view was, because we haven’t referenced anything other than the base tables, right? I already told you the answer, so here’s the plan:

There are two scalar computes—one for the division and one to convert the bigint from the COUNT_BIG(*) to an integer—and the other to scan through the indexed view’s clustered index. You will notice that the plan references the ProductAverageSales indexed view, and we did not reference it directly in our query. The ability to use the optimizations from an indexed view indirectly is a neat feature that allows you to build in some guesses as to what ad hoc users will be doing with the data and giving them performance they didn’t even ask for.

There are some pretty heavy caveats, though. The restrictions on what can be used in a view, prior to it being indexed, are fairly tight. The most important things that cannot be done are as follows:

• Use the SELECT * syntax—columns must be explicitly named.
• Use a CLR user defined aggregate.
• Use UNION, EXCEPT, or INTERSECT in the view.
• Use any subqueries.
• Use any outer joins or recursively join back to the same table.
• Specify TOP in the SELECT clause.
• Use DISTINCT.
• Include a SUM() function if it references more than one column.
• Use COUNT(*), though COUNT_BIG(*) is allowed.
• Use almost any aggregate function against a nullable expression.
• Reference any other views, or use CTEs or derived tables.
• Reference any nondeterministic functions.
• Reference data outside the database.
• Reference tables owned by a different owner.

And this isn’t all. You must meet several pages of requirements, documented in SQL Server Books Online in the section “Creating Indexed Views.” However, these are the most significant ones that you need to consider before using indexed views.

Although this might all seem pretty restrictive, there are good reasons for all these rules. Maintaining the indexed view is analogous to writing our own denormalized data maintenance functions. Simply put, the more complex the query to build the denormalized data, the greater the complexity in maintaining it. Adding one row to the base table might cause the view to need to recalculate, touching thousands of rows.

Indexed views are particularly useful when you have a view that’s costly to run, but the data on which it’s based doesn’t change a tremendous amount. As an example, consider a decision-support system where you load data once a day. There’s overhead either maintaining the index, or possibly just rebuilding it, but if you can build the index during off hours, you can omit the cost of redoing joins and calculations for every view usage.

Missing Indexes

The first query we’ll discuss will provide you a peek at what indexes the optimizer thought might be useful. It can be very helpful when tuning a database, particularly a very busy databsase executing thousands of queries a minute. I have personally used it to tune third-party systems where I didn’t have a lot of access to the queries in the system and using profiler was simply far too cumbersome with too many queries to effectively tune manually.

It uses three of the dynamic management views that are part of the missing index family of objects. These are:

• sys.dm_db_missing_index_groups: This relates missing index groups with the indexes in the group. There is only one index to a group, as of the 2011 release.
• sys.dm_db_missing_index_group_stats: This provides statistics of how much the indexes in the group (only one in 2005; see the previous section) would have helped (and hurt) the system.
• sys.dm_db_missing_index_details: This provides information about the index that the optimizer would have chosen to have available.

The query is as follows. I won’t attempt to build a scenario where you can test this functionality out in this book, but run the query on one of your development servers and check the results. The results will likely make you want to run it on your production server:

 SELECT ddmid.statement AS object_name, ddmid.equality_columns, ddmid.inequality_columns,

    ddmid.included_columns, ddmigs.user_seeks, ddmigs.user_scans,

    ddmigs.last_user_seek, ddmigs.last_user_scan, ddmigs.avg_total_user_cost,

    ddmigs.avg_user_impact, ddmigs.unique_compiles

 FROM sys.dm_db_missing_index_groups as ddmig

      JOIN sys.dm_db_missing_index_group_stats as ddmigs

     ON ddmig.index_group_handle = ddmigs.group_handle

      JOIN sys.dm_db_missing_index_details as ddmid

       ON ddmid.index_handle = ddmig.index_handle

 ORDER BY ((user_seeks + user_scans) * avg_total_user_cost * (avg_user_impact * 0.01)) DESC;

The query returns the following information about the structure of the index that might have been useful:

• object_name: This is the database and schema qualified object name of the object that the index would have been useful on. The data returned is for all databases on the entire server
• equality_columns: These are the columns that would have been useful, based on an equality predicate. The columns are returned in a comma-delimited list.
• inequality_columns: These are the columns that would have been useful, based on an inequality predicate (which as we have discussed is any comparison other than column = value or column in (value, value1)).
• included_columns: These columns, if added to the index via an INCLUDE clause, would have been useful to cover the query results and avoid a key lookup operation in the plan.

As discussed earlier, the equality columns would generally go first in the index column definition, but it isn’t guaranteed that that will make the correct index. These are just guidelines, and using the next DMV query I will present, you can discover if the index you create turns out to be of any value.

• unique_compiles: The number of plans that have been compiled that might have used the index
• user_seeks: The number of seek operations in user queries might have used the index
• user_scans: The number of scan operations in user queries that might have used the index
• last_user_seek: The last time that a seek operation might have used the index
• last_user_scan: The last time that a scan operation might have used the index
• avg_total_user_cost: Average cost of queries that could have been helped by the group of indexes
• avg_user_impact: The percentage of change in cost that the index is estimated to make for user queries

Note that I sorted the query results as (user_seeks + user_scans) * avg_total_user_cost * (avg_user_impact * 0.01) based on the initial blog I read using the missing indexes, “Fun for the Day” – Automated Auto-Indexing ( http://blogs.msdn.com/queryoptteam/archive/2006/06/01/613516.aspx ). I generally use some variant of that to determine what is most important. For example, I might use order by (user_seeks + user_scans) to see what would have been useful the most times. It really just depends on what I am trying to scan; with all such queries, it pays to try out the query and see what works for your situation.

To use the output, you can create a CREATE INDEX statement from the values in the four structural columns. Say you received the following results:

You could build the following index to satisfy the need:

Next, see if it helps out performance in the way you believed it might. And even if you aren’t sure of how the index might be useful, create it and just see if it has an impact.

Books Online lists the following limitations to consider:

• It is not intended to fine-tune an indexing configuration.
• It cannot gather statistics for more than 500 missing index groups.
• It does not specify an order for columns to be used in an index.
• For queries involving only inequality predicates, it returns less accurate cost information.
• It reports only include columns for some queries, so index key columns must be manually selected.
• It returns only raw information about columns on which indexes might be missing. This means the information returned may not be sufficient by itself without additional processing before building the index.
• It does not suggest filtered indexes.
• It can return different costs for the same missing index group that appears multiple times in XML showplans.
• It does not consider trivial query plans.

Probably the biggest concern is that it can specify a lot of overlapping indexes, particularly when it comes to included columns, since each entry could have been specifically created for distinct queries. For very busy systems, you may find a lot of the suggestions include very large sets of include columns that you may not want to implement.

However, limitations aside, the missing index dynamic management view are amazingly useful to help you see places where the optimizer would have liked to have an index and one didn’t exist. This can greatly help diagnose very complex performance/indexing concerns, particularly ones that need a large amount of INCLUDE columns to cover complex queries. This feature is turned on by default and can only be disabled by starting SQL Server with a command line parameter of –x. This will, however, disable keeping several other statistics like CPU time and cache-hit ratio stats.

Using this feature and the query in the next section that can tell you what indexes have been used, you can use these index suggestions in an experimental fashion, just building a few of the indexes and see if they are used and what impact they have on your performance tuning efforts.

Index Utilization Statistics

The second query gives statistics on how an index has been used to resolve queries. Most importantly, it tells you the number of times a query was used to find a single row (user_seeks), a range of values, or to resolve a non-unique query (user_scans), if it has been used to resolve a bookmark lookup (user_lookups), and how many changes to the index (user_updates). If you want deeper information on how the index was modified, check sys.dm_db_index_operational_stats). The query makes use of the sys.dm_db_index_usage_stats object that provides just what the names says, usage statistics:

 SELECT OBJECT_SCHEMA_NAME(indexes.object_id) + '.' +

         OBJECT_NAME(indexes.object_id) as objectName,

         indexes.name,

         CASE WHEN is_unique = 1 then 'UNIQUE '

      else '' END + indexes.type_desc as index_type,

    ddius.user_seeks, ddius.user_scans, ddius.user_lookups,

    ddius.user_updates, last_user_lookup, last_user_scan, last_user_seek,last_user_update

 FROM sys.indexes

      LEFT OUTER JOIN sys.dm_db_index_usage_stats as ddius

       ON indexes.object_id = ddius.object_id

           AND indexes.index_id = ddius.index_id

           AND ddius.database_id = db_id()

 ORDER BY ddius.user_seeks + ddius.user_scans + ddius.user_lookups DESC;

The query (as written) is database dependent in order to look up the name of the index in sys.indexes, which is a database-level catalog view. The sys.dm_db_index_usage_stats object returns all indexes (including heaps and the clustered index) from the entire server (there will not be a row for sys.dm_db_index_usage_stats unless the index has been used since the last time the server has been started). The query will return all indexes for the current database (since the DMV is filtered on db_id() in the join criteria) and will return:

• object_name: Schema-qualified name of the table.
• index_name: The name of the index (or table) from sys.indexes.
• index_type: The type of index, including uniqueness and clustered/nonclustered.
• user_seeks: The number of times the index has been used in a user query in a seek operation (one specific row).
• user_scans: The number of times the index has been used by scanning the leaf pages of the index for data.
• user_lookups: For clustered indexes only, this is the number of times the index has been used in a bookmark lookup to fetch the full row. This is because nonclustered indexes use the clustered indexes key as the pointer to the base row.
• user_updates: The number of times the index has been modified due to a change in the table’s data.
• last_user_seek: The date and time of the last user seek operation.
• last_user_scan: The date and time of the last user scan operation.
• last_user_lookup: The date and time of the last user lookup operation.
• last_user_update: The date and time of the last user update operation.

There are also columns for system utilizations of the index in operations such as automatic statistics operations: system_seeks, system_scans, system_lookups, system_updates, last_system_seek, last_system_scan, last_system_lookup, and last_system_update.

This is one of the most interesting views that I often use in performance tuning. It gives you the ability to tell when indexes are not being used. It is easy to see when an index is being used by a query by simply looking at the plan. But now, using this dynamic management view, you can see over time what indexes are used, not used, and probably more importantly, updated many, many times without ever being used.

Fragmentation

One of the biggest tasks for the DBA of a system is to make sure that the structures of indexes and tables are within a reasonable tolerance. You can decide whether to reorganize or to rebuild using the criteria stated by SQL Server Books Online in the topic for the dynamic management view sys.dm_db_index_physical_stats. You can check the FragPercent column and REBUILD indexes with greater than 30% fragmentation and REORGANIZE those that are just lightly fragmented.

 SELECT s.[name] AS SchemaName,

        o.[name] AS TableName,

        i.[name] AS IndexName,

        f.[avg_fragmentation_in_percent] AS FragPercent,

        f.fragment_count ,

        f.forwarded_record_count --heap only

 FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, DEFAULT) f

        JOIN sys.indexes as i

            ON f.[object_id] = i.[object_id] AND f.[index_id] = i.[index_id]

        JOIN sys.objects as o

             ON i.[object_id] = o.[object_id]

        JOIN sys.schemas as s

            ON o.[schema_id] = s.[schema_id]

 WHERE o.[is_ms_shipped] = 0

    AND i.[is_disabled] = 0; -- skip disabled indexes

sys.dm_db_index_physical_stats will give you a lot more information about the internal physical structures of your tables and indexes than I am making use of here. If you find you are having a lot of fragmentation, adjusting the fill factor of your tables or indexes (specified as a percentage of page size to leave empty for new rows) in CREATE INDEX and PRIMARY KEY and UNIQUEconstraint CREATE/ALTER DDL statements can help tremendously. How much space to leave will largely depend on your exact situation, but minimally, you want to leave approximately enough space for one full additional row to be added to each page.