Search results matching tags 'Performance' and 'indexes'

Speaking at the Charlotte Users Group

Andrew Kelly — Sat, 27 Aug 2011 00:33:55 GMT

I will be giving a presentation in between Earthquakes and Hurricanes in Charlotte NC. this Wednesday the 31st of August on Understanding Indexes in SQL Server 2008. If you are in the area please drop by and say hello. You can find out more information and register for the event here.

Andy

SQL Server, Seeks, and Binary Search

Paul White — Mon, 08 Aug 2011 20:10:27 GMT

The following table summarizes the results from my last two blog entries, showing the CPU time used when performing 5 million clustered index seeks:

In test 1, making the clustered index unique improved performance by around 40%. In test 2, making the same change reduced performance by around 70% (on 64-bit systems – more on that later). As a reminder, both tests use nested loops to join a single-column BIGINT table of numbers to itself:

In test 1, the table contains numbers from 1 to 5 million inclusive; for test 2, the table contains the even numbers from 2 to 10 million (still 5 million rows). In case you were wondering, the test 2 results are the same if we populate the table with odd numbers from 1 to 9,999,999 instead of even numbers. How can we explain these results?

Performing an Index Seek

Perhaps we need to look a bit deeper into how SQL Server performs an index seek. Most people know that SQL Server indexes are a B+ tree, so let’s work through an example of locating the row containing the value 1 million, when the table contains values from 1 to 5 million, with a unique clustered index.

Index seeks start at the index root page, which we can find in a number of ways (e.g. sys.system_internals_allocation_units). I happened to use DBCC IND to determine that the root of the index was page 117482 in file 1 (in Denali, we can use a new DMF: sys.dm_db_database_page_allocations). Anyway, whichever way you find it, we can use DBCC PAGE to look at the contents of the root page:

The col1 (key) column shows the lowest key value that can possibly be present in the lower-level page specified by Child Page Id. We can see that the lowest possible key value on page 117485 is 906305, and the next lowest key value possible is 1132881 on page 117486. So, if a row containing the value 1 million exists, the next page to look at is 117485:

We are now at level 1 of the index, and have to scroll down the output a bit further to see that the next page to look at is 119580. This page is at the leaf level of the index, and we find our target value at position 399:

Binary Search

When performing an index seek, SQL Server obviously doesn’t run DBCC PAGE and scroll down the output window to find the next page to look at like we just did. You may know that SQL Server can use binary search to locate index entries, but it’s worth taking a closer look at the index pages to see how this works in practice:

The slot array contains a series of two-byte offsets pointing to the index records. The index records are not necessarily stored in any particular order on the page; only the slot array entries are guaranteed to be in sorted order. Note that the sorted slot array entries do not contain the index keys themselves, just offsets.

Performance

When SQL Server uses binary search to locate an index entry, it starts by checking the middle element of the slot array (shown in red in the example above). It then follows that offset pointer to the index record, and compares the value it finds there with the sought value. Assuming the value is not the one we are looking for, the sorted nature of the slot array allows SQL Server to narrow the search range in half after this first comparison.

If the red index record happens to contain a value higher than the sought value, we know the value we are looking for is in that half of the slot array that sorts lower than the red entry. This process of cutting the search space in half continues until we find the record we are looking for, or until it becomes clear that the value does not exist.

The average and worst case performance of binary search is very close to log₂N comparisons to find the item of interest in a sorted list of N items. For the 476 index entries found in our test example index structure at the leaf level, that means at most 9 comparison operations. That compares well to a linear search of the sorted slot array, which might require as many as 476 comparisons if we are unlucky, 238 on average.

None of this offers any particular insight into why a binary search into a unique indexes containing only even (or odd!) numbers should be so slow. One of the advantages of binary search is that it expects to perform around the same amount of work, regardless of the distribution of the values in the index. Luckily, binary search is not the only game in town.

Linear Interpolation

As efficient as binary search is in the general case, in some cases we can do better.

When searching for the value 1 million, we know from level 1 of the index that the lowest key value that can appear on the leaf level page we are interested in is 999,601. We also know that the lowest key value on the next page is 1,000,077. From the header information on our target page, we also know there are 476 entries in the slot array on that page. Given these facts, we can make an immediate guess at which slot the value 1,000,000 ought to appear in:

expected slot = (1000077 - 999601) / 476 * (1000000 - 999601) = 399

This simple calculation takes us immediately to slot 399. As we saw earlier, slot 399 does indeed contain the value 1,000,000:

Naturally, the quality of the linear interpolation guess depends on how evenly distributed the values are within the page. To a lesser extent, it also depends on how closely the minimum key value information at level 1 of the index matches reality. In both tests shown in earlier blog entries, the values are precisely evenly distributed and the level 1 index key information is spot on.

Performance

Linear interpolation has the potential to be more efficient even than binary search, finding the target value in one comparison in this example, compared to nine comparisons for binary search. Even where the data is not perfectly distributed, there is scope for linear interpolation to be superior, simply by applying the technique repeatedly on successively narrower ranges.

A reasonable practical compromise might be to try linear interpolation two or three times, before falling back to linear or binary search on the remaining range. The advantage of linear interpolation may not seem much, but consider that SQL Server makes these comparisons for every index seek operation – when doing many millions of seeks, the difference can soon add up. It is probably not coincidence that the OLTP TPC benchmark tests perform a very great number of singleton index seeks.

Linear Regression

A natural extension to the idea of linear interpolation is to apply a linear regression (line of best fit) instead:

In the diagram, the blue dotted line represents a linear interpolation based on the values of the smallest and highest values only. The black line is obtained by a linear regression of all the data values, and results in a much better fit.

Since every straight line can be represented by a formula of the form y = mx + b, we can completely specify it by recording the values of the ‘m’ (slope) and ‘b’ (y-axis intercept) parameters. The R² value gives an idea of how good a fit the linear regression line is to the data. In the present context, the ‘y’ value represents the indexed value, and the ‘x’ value is the slot position where that value can be found. It’s easy to see that once we have a linear regression line, we can estimate the slot position for a particular sought index value by solving x = (y – b) / m.

To use linear regression in a database, we might imagine storing the ‘b’ and ‘m’ parameter values somewhere (perhaps in a compact format in the page header), and deciding whether to apply the technique based on the R² value.

What Does SQL Server Do?

All this is fine in theory, but does SQL Server really ever use anything except binary search? To see, I ran tests with a debugger attached to the SQL Server process, and stepped through the calls made while the seek test queries were running. The call stack below was obtained when running 64-bit SQL Server:

By contrast, the following call stack comes from 32-bit SQL Server:

Interestingly, SQL Server follows the same code paths (at least at the call stack level, which is the best we can do without source code) regardless of whether the index is defined as unique or not, and for all the numeric data types I tested (INT, BIGINT, REAL, FLOAT, DECIMAL with a zero scale).

The performance problem on x64 servers appears to be most pronounced when the key values have a small fixed gap between them. As we have seen, performance is very much worse on x64 servers compared with x86 versions when the table contains just even or odd numbers (i.e. with a gap of 1 between index entries). If we change the test to multiply the original entries by a factor of ten (to produce a sequence of 10, 20, 30, 40…) the performance penalty all but disappears, and the x64 unique index test is around 30% faster than the non-unique test.

Conclusions and Further Reading

From the evidence available, my best guess is that 64-bit SQL Server does use linear interpolation and/or linear regression instead of binary search under suitable conditions, but that the implementation has edge-case poor performance where the index keys are separated by a small fixed offset. It seems that we can achieve best performance by using a unique index where possible, and ensuring that keys are, as far as practicable, sequential and contiguous.

B-tree Indexes, Interpolation Search, and Skew – Microsoft Research
Interpolation Search – a log log N Search (PDF)

email: SQLkiwi@gmail.com
twitter: @SQL_Kiwi

Watch those clustered indexes

BuckWoody — Wed, 11 Aug 2010 16:27:52 GMT

I had a discussion with a friend in Microsoft IT this morning, and as we were evaluating some query plans we ran into more clustered index woes. Remember that clustered indexes affect the base table itself (which is why there can be only one) and are expensive to update. They are fantastic for reading data, but you need to carefully evaluate if they meet the criteria for having an efficient use of this type of index.

More here: http://www.sqlskills.com/BLOGS/PAUL/category/Indexes-From-Every-Angle.aspx

Ola Hallengren's maint procedures now supports exclusions

TiborKaraszi — Mon, 09 Aug 2010 05:02:00 GMT

Ola has a set of stored procedures to do maint operations, see http://ola.hallengren.com/. Ola has now updated them to support exclusions or inclusions of tables, indexes or even whole schemas from index rebuild/reorg. Check out http://ola.hallengren.com/Documentation.html#SelectingIndexes for info on how to define exclusions and http://ola.hallengren.com/Versions.html for version overview.

How selective do we need to be for an index to be used?

TiborKaraszi — Thu, 01 Apr 2010 07:39:00 GMT

You know the answer already: It depends. But I often see some percentage value quoted and the point of this post is to show that there is no such percentage value.

To get the most out of this blog post, you should understand the basic structure for an index, i.e. how the b+ tree look like. You should also understand the difference between a clustered and a non-clustered index. In essence, you should be able to visualize these structures and searches through them as you read the text. If you find that difficult, draw a few versions on a piece of paper and "navigate" through them by tracing through them with a pen or your finger. After a while, you will do this in your mind. For instance, check out the sections under this.

I'm not saying that we shouldn't consider selectivity when designing indexes - of course we should! I'm not saying that one shouldn't have some vague "feeling" about how much data to be return when making such decisions. What I will prove is that there is in reality no set percentage that the optimizer uses. The comment we usually see is something like:

"If we return more than 5% of the rows, then an index will not be used."

Where did that 5% number came from? I can assure you that this is not some hard-wired number in the optimizer (except for an edge-case, see below). The optimizer aims at running the query with as low cost as possible. Considering the data access part (think WHERE clause and the condition), this is pretty much about reading as few pages as possible (few page-accesses).

Just to cut down a bit on the thread that might follow these types of blogs ("Hey, when I do this, your observations doesn't match, your blog post is incorrect!"), let us first consider some special cases:

Clustered index
The clustered index *is* the data. If the search condition (SARG) is SEEKable, then SQL Server will obviously seek through a clustered index instead of scan it. Anything else would be stupid. There can be *other* search conditions that are more efficient, but we are considering one search condition at-a-time.

Non-clustered index that covers the query
This is pretty much the same argument as for above. Since all data is in the index ("covers the query"), not seeking it would be stupid. Again, there can be cheaper alternatives for any of the other search conditions, but we are considering one condition at-a-time.

The value is not known to the optimizer
This is what happens when you have a TSQL variable you compare against. Something like "colname = @v". The optimizer has no knowledge of the contents of this variable. Either it uses density (where applicable, like "="), as stored in the statistics information of the index. Where not applicable (like ">", "<", "BETWEEN" etc), then the optimizer actually do use some hard-wired percentage value. This value can change between versions so give it a spin of you want to know what value you have for your version/build number. Note that a variable is not the same thing as a parameter. SQL Server sniffs parameters (parameter sniffing). Read this for elaboration: http://msdn.microsoft.com/en-us/library/ee343986.aspx.

The search expression is not seek-able
I hope you know this already, but just to point it out. In most cases, having some calculation at the column side will void the ability to seek through the index. This should ideally be known to all T-SQL developers: Never do calculations at the column side! So, things to avoid are like "colname * 23 > 45" or "SOMEFUNCTION(colname) = 44".

Hopefully by now we all understand that there are always special cases and exceptions. The more of Kalen's books you have read, the more you understand this. What we are discussing here is the typical situation. OK? Fine. So, "Why is there no percentage value that the optimizer uses?", you ask. Because the value will differ. In short, SQL Server wants to read as few pages as possible. In the most simple example, the alternative to an index seek is a table scan. So we will use this as basis for your discussion. There can be other alternatives to the table scan (using some other index for some other condition), but that doesn't change the principal "it depends" concept.

In essence, it is all about the alternative. As I said, our example wil use a table scan as alternative. A table scan (or clustered index scan if it is a clustered table) means that SQL Server will look at every page and see what rows satisfies the search condition on each page.

My example has two different tables, both with 100,000 rows. These tables both have an integer column with consecutive increasing unique values, which also has a non-clustered index. I will see how selective I need to be when searching on this column in order for an index search to be done, compared to a table scan. I.e, find this percentage cut-off value.

The fewrows table only fit one row per data page. This means 100,000 data pages. My tests show that the cutoff for fewrows is about 31,000 rows. I.e., we need to be more selective than 31% for the index to be used.

The manyrows table fit 384 rows per page. This means 260 pages. My tests show that the cutoff for fewrows is about 155 rows. I.e., we need to be more selective than 0.16% for the index to be used.

You might end up with different exact numbers, depending on what you have in the statistics, the build number of your SQL Server etc. But what you will see that a similar pattern. A huge difference between the two.

It is all about the alternative
If I look at my indexes using sys.dm_db_index_physical_stats, I will see that the non-clustered index on the int column for the two tables are exactly the same (same number of pages in the index, etc). So, two indexes with the same characteristics have very different cut-off values. How can that be? It is because the alternative differs. The alternative for this example is a table scan. For the bigrows table, the alternative means reading 100,000 pages. But for the smallrows table, the alternative means reading only 260 pages. There can of course be other alternatives, like using some other index for some other search condition. This is, in the end, why we don't have a set percentage value: it is all about the alternative!

Conclusion
The typical cases will of course fall somewhere between my more extreme examples. But my examples show that there is no set percentage value used by the optimizer. I showed that for my test, the percentage value can be as low as 0.15% or as high as 31%. What matter is the alternative!

T-SQL

USE tempdb GO IF OBJECT_ID('manyrows') IS NOT NULL DROP TABLE manyrows IF OBJECT_ID('fewrows') IS NOT NULL DROP TABLE fewrows GO CREATE TABLE manyrows(c1 INT IDENTITY PRIMARY KEY, c2 INT, c3 INT) CREATE TABLE fewrows(c1 INT IDENTITY PRIMARY KEY, c2 INT, c3 CHAR(4500)) GO INSERT INTO manyrows SELECT TOP 100000 ROW_NUMBER() OVER (ORDER BY a.OBJECT_ID) AS c2, 5 AS c3 FROM sys.columns AS a, sys.columns AS b INSERT INTO fewrows SELECT TOP 100000 ROW_NUMBER() OVER (ORDER BY a.OBJECT_ID) AS c2, 'hi' AS c3 FROM sys.columns AS a, sys.columns AS b CREATE INDEX x ON manyrows (c2) CREATE INDEX x ON fewrows (c2) --Number of pages etc: EXEC sp_indexinfo 'manyrows' -- Data: 265 pages (2.07 MB) -- Index x: 187 pages (1.46 MB) EXEC sp_indexinfo 'fewrows' -- Data: 100385 pages (784 MB) -- Index x: 187 pages (1.46 MB) SELECT OBJECT_NAME(OBJECT_ID), *, OBJECT_NAME(OBJECT_ID) FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED') --Run below with showplan: SELECT * FROM manyrows WHERE c2 BETWEEN 1 AND 155 --155 = ix search, 156 = ts --Cut-off is 0.16% SELECT * FROM fewrows WHERE c2 BETWEEN 1 AND 31000 --31000 = ix search, 32000 = ts --Cut-off is 31%

Adding a PK online?

TiborKaraszi — Wed, 13 Jan 2010 08:53:00 GMT

I just read in a forum about a user who want to replikate a table, but the table doesn't have a PK. The table is pretty large, and having the table not available while adding the PK is undesireable. The table has a clustered index already, and there are other columns which are known to be unique (presence of unique indexes).

What I wanted to test is whether we can just add the PK constraint using the ONLINE option. Show answer is "yes". We can't turn a unique index into a PK using some meta-data only operation, unfortunately. That would be the easiest step. But we can add a unique constraint using the ONLINE option - there's even an example syntax for this in BOL. We can then remove the pre-existing unique index using ONLINE. Since we are using ONLINE, we need to be on Enterprise or Developer Edition.

I wanted to test this, and below is my test script:

USE tempdb 
SET NOCOUNT ON 
GO 

IF OBJECT_ID('t') IS NOT NULL DROP TABLE t 
GO 
CREATE TABLE t(c1 INT NOT NULL, c2 CHAR(100)) 
CREATE UNIQUE CLUSTERED INDEX x ON t(c1) 

INSERT INTO t 
 SELECT TOP(5000000) ROW_NUMBER() OVER(ORDER BY a.id), 'x' 
 FROM syscolumns AS a  
 CROSS JOIN syscolumns AS b 
 CROSS JOIN syscolumns AS c 
GO 

----------------------------------------------------- 
--Now try to add a PK "online"...: 
----------------------------------------------------- 

--Add a nullable identity? 
ALTER TABLE t ADD c3 INT IDENTITY NULL 
--Msg 8147, Level 16, State 1, Line 1 
--Could not create IDENTITY attribute on nullable column 'c3', table 't'. 
GO 


--Add a PK using ONLINE? 
--Prepare a new connection with following INSERTs 
--to verify it can run simultaneously: 
--INSERT INTO t(c1, c2) VALUES(5000001, 't') 
--INSERT INTO t(c1, c2) VALUES(5000001, 't') 
--INSERT INTO t(c1, c2) VALUES(5000002, 't') 
--GO 
--INSERT INTO t(c1, c2) VALUES(5000003, 't') 

--Above prepared? OK, execute below and jump to 
--other window to verify it is online 
ALTER TABLE t ADD CONSTRAINT PK_t PRIMARY KEY NONCLUSTERED (c1) WITH(ONLINE = ON) 
GO 

--Verify the indexes using my own sp_indexinfo 
EXEC sp_indexinfo 't'

Rebuilding Indexes vs. Updating Statistics

Ben Nevarez — Tue, 06 Oct 2009 07:04:00 GMT

One of the questions I was asked recently while speaking at user groups, was regarding the order that jobs like rebuilding indexes or updating statistics should be performed as part of the database maintenance activities. Then I started writing this post about this topic on the weekend but was interrupted several times, including one of them to watch the premiere on VH1 of the movie Anvil: The Story of Anvil.

In general, the order should not matter, at least if you carefully consider these important points:

1) By default, the UPDATE STATISTICS statement uses only a sample of records of the table. Using UPDATE STATISTICS WITH FULLSCAN will scan the entire table.

2) By default, the UPDATE STATISTICS statement updates both index and column statistics. Using the COLUMNS option will update column statistics only. Using the INDEX option will update index statistics only.

3) Rebuilding an index, for example by using ALTER INDEX … REBUILD, will also update index statistics with the equivalent of using WITH FULLSCAN. Rebuilding indexes does not update column statistics.

4) Reorganizing an index, for example using ALTER INDEX … REORGANIZE, does not update any statistics.

So depending on your maintenance jobs and scripts several scenarios can exist.

The simplest scenario is if you want to rebuild all the indexes and update all the statistics. As mentioned before, if you rebuild all your indexes then all the index statistics will also be updated by scanning all the rows on the table. Then you just need to update your column statistics by running UPDATE STATISTICS WITH FULLSCAN, COLUMNS. Since the first job only updates index statistics and the second one only updates column statistics, it does not matter which one you execute first.

Some other more complicated scenarios include when you have a job which rebuilds your indexes depending on their fragmentation level. In these cases perhaps you want to update only those index statistics that were not touched by the index rebuild job, plus all the column statistics.

Of course, the worst case scenario would be if you first rebuild your indexes, which also updates the index statistics by scanning the entire table, and later you run UPDATE STATISTICS using the default values, which again updates the index statistics but this time with a default sample. Not only are you updating your index statistics twice but you are overwriting the better of the two choices.

Let me show you how these commands work with some examples using the AdventureWorks database. Create a new table dbo.SalesOrderDetail

select * into dbo.SalesOrderDetail

from sales.SalesOrderDetail

The next query uses the sys.stats catalog view and shows that there are no statistics objects for the new table.

select name, auto_created, stats_date(object_id, stats_id) as update_date from sys.stats

where object_id = object_id('dbo.SalesOrderDetail')

Use this query again to inspect the status of the statistics after each of the following commands. Now run the following query

select * from dbo.SalesOrderDetail

where SalesOrderID = 43670 and OrderQty = 1

Use the previous sys.stats query to verify that two statistics objects were created, one for the SalesOrderID column and another one for the OrderQty column (they both have names starting with _WA_Sys as shown in the next figure). Now create the following index and again run the query to verify that a new statistics object for the ProductID column has been created. Notice the value of the auto_created column which tells if the statistics were created by the query optimizer.

create index ix_ProductID on dbo.SalesOrderDetail(ProductID)

Run the next command to update the column statistics only. You can validate that only the column statistics were updated by looking at the update_date column which uses the STATS_DATE function to display the last date the statistics were updated.

update statistics dbo.SalesOrderDetail with fullscan, columns

This command will do the same for the index statistics

update statistics dbo.SalesOrderDetail with fullscan, index

These commands will update both index and column statistics

update statistics dbo.SalesOrderDetail with fullscan

update statistics dbo.SalesOrderDetail with fullscan, all

See how an index rebuild only updates index statistics

alter index ix_ProductID on dbo.SalesOrderDetail rebuild

Here you can verify that reorganizing an index does not update statistics

alter index ix_ProductID on dbo.SalesOrderDetail reorganize

Finally, remove the table you have just created

drop table dbo.SalesOrderDetail

The Missing Indexes Feature

Ben Nevarez — Fri, 25 Sep 2009 06:19:00 GMT

Since I will be speaking about the Query Optimizer at the coming PASS Summit, I have been preparing my presentation and at the same time blogging about it. This time I will describe the Missing Indexes feature, seen from the point of view of the Query Optimizer.

We know that it is the job of the Query Optimizer to find an efficient execution plan for a query. But we rarely see the Query Optimizer directly giving us indications about what it needs to produce a better execution plan. One of these cases is the Missing Indexes feature, which was introduced with SQL Server 2005.

The Query Optimizer defines what the best indexes for a query are, and if these indexes do not exist, it will make this information available in the XML plan and the sys.dm_db_missing_index DMVs. And of course, by showing this information the Query Optimizer is also warning you that it might not be selecting an efficient plan. This information shows which indexes may be helpful to improve the performance of your query. You can even use SQL Server 2008 Management Studio to display the CREATE INDEX commands needed to create these indexes, as shown later.

However, although this information about missing indexes is very helpful, this feature should not be used as a tuning tool and should not replace your own index analysis. Database administrators and developers should be aware of its limitations, as described on the Books Online entry ‘Limitations of the Missing Indexes Feature’.

So let us take a quick look to see how this feature works. Create a dbo.SalesOrderDetail table on the AdventureWorks database with the following command

select *

into dbo.SalesOrderDetail

from sales.SalesOrderDetail

Run this query and ask for a graphical or XML execution plan

select *

from dbo.SalesOrderDetail

where SalesOrderID = 43670

This query can benefit from an index on the SalesOrderID column but no missing indexes information is shown this time. One limitation of the Missing Indexes feature is that it does not work on a trivial plan optimization, like in this case. You can verify that this is a trivial plan by looking at the graphical plan properties (Optimization Level shows as TRIVIAL) or by looking at the XML plan (StatementOptmLevel="TRIVIAL).

You can avoid the trivial plan optimization by using more complex features. In our case we are just going to create a non related index

create index ix_ProductID on dbo.SalesOrderDetail(ProductID)

Note that the index created will not be used by our previous query but the query will no longer qualify for a trivial plan. Run the query again. This time the XML plan will contain something like this

</ColumnGroup>

</MissingIndex>

</MissingIndexGroup>

</MissingIndexes>

And if you look at the graphical plan (only SQL Server 2008 Management Studio) you will see a Missing Index warning and a CREATE INDEX command

You can right-click on the graphical plan and select Missing Index Details to see the CREATE INDEX command that can be used to create this index

Missing Index Details from SQLQuery1.sql

The Query Processor estimates that implementing the following index could improve the query cost by 99.703%.

USE [AdventureWorks]

CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]

ON [dbo].[SalesOrderDetail] ([SalesOrderID])

Create the recommended index after you provide a name to it. This time if you run the same query again and look at the execution plan you will see that an Index Seek operator is using the index you have just created and both the Missing Index warning and the MissingIndex element of the XML plan are gone.

Finally, remove the dbo.SalesOrderDetail table you have just created.

drop table dbo.SalesOrderDetail

Heaps, forwarding pointers, ALTER TABLE REBUILD and non-clustered indexes

TiborKaraszi — Fri, 28 Aug 2009 10:15:00 GMT

Let's start with some background on forwarding pointers:

Forwarding pointers in heaps can be a mess to get rid of. A forwarding pointer happens when you modify a row so that the row doesn't fit on the same page anymore. SQL Server moves the row to a new page and leaves a forwarding pointer where the old row used to be. This means that non-clustered indexes are not affected by the moving of the row - it can still point to the old location. So the modification is less costly than if all non-clustered indexes needed to reflect the row move. But having forwarding pointers can be bad for performance when you read data. A perhaps less known fact is that a scan over a table needs to follow forwarding pointers - essentially "jumping back and forth" a lot if you have many forwarding pointers. That can be really bad for performance (if you have table scans, of course). So, how do we get rid of forwarding pointers? Well, we can shrink the database file, but that is a little like curing a headache by jumping into a big meat grinder. We can export all data and re-import it - not very practical.

Greg Linwood reminded me that in SQL Server 2008, we can do ALTER TABLE ... REBUILD. Now, I knew about this option, and every time I tell about it in class I've been thinking silently for myself "I need to test whether this is a way to get rid of fwd pointers". (You generally talk about ALTER TABLE ... REBUILD when you talk about enabling compression on a heap.) So, doing a REBUILD of a table using ALTER TABLE sounds promising. Will it get rid of forwarding pointers? Will it also rebuild all non-clustered indexes?

Quick answer for those who don't care reading the TSQL script:
ALTER TABLE ... REBUILD will remove forwarding pointers, but for some strange reason it will also rebuild all non-clustered indexes on that table.

See the TSQL code below. It was adapted from a challenge by Linchi Shea to produce a data loading script resulting in worst performance (where I immediately thought of forwarding pointers). See for instance http://sqlblog.com/blogs/linchi_shea/archive/2009/06/07/t-sql-exercise-simplest-data-loading-script-to-produce-worst-query-performance-june-7th-update.aspx.

USE tempdb 
GO 

IF EXISTS (SELECT * FROM sysobjects WHERE name = 'test') 
   DROP TABLE test 
GO 
CREATE TABLE test ( 
   x INT NOT NULL 
  ,x2 INT NOT NULL 
  ,y CHAR(10) NOT NULL DEFAULT ('') 
  ,z CHAR(10) NOT NULL DEFAULT('') 
) 
  
DECLARE @rows INT = 666666, @toKeep INT = 400000, @diff INT 
  
INSERT test (x, x2) 
SELECT TOP(@rows) 
       ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS r 
      ,ROW_NUMBER() OVER (ORDER BY (SELECT 1)) % 10 AS s 
  FROM master..spt_values a CROSS JOIN master..spt_values b 
GO 
ALTER TABLE test ALTER COLUMN y CHAR(892) 
ALTER TABLE test ALTER COLUMN z CHAR(100) 
GO 
  
DECLARE @rows INT = 666666, @toKeep INT = 400000, @diff INT 
DELETE TOP(@rows - @toKeep)  
  FROM test WHERE x2 IN(2, 4, 6, 8) 
GO 

CREATE INDEX x1 ON test(x) 
CREATE INDEX x2 ON test(x2) 

SELECT * FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('test'), NULL, NULL, 'DETAILED') 
--First  run (no nc indexes ): 387157 fwd records (out of 400000 rows), 96104 pages 
--Second run (two nc indexes): 387157 fwd records (out of 400000 rows), 96105 pages 

CHECKPOINT 
DBCC DROPCLEANBUFFERS 
DECLARE @t time = SYSDATETIME() 
ALTER TABLE test REBUILD 
SELECT DATEDIFF(ms, @t, CAST(SYSDATETIME() AS time)) 
--First run, no non-clustered indexes, three subsequent executions (ms): 19351, 20683, 20275 
--Second run, with two non-clustered indexes, three subsequent executions (ms): 31803, 35065, 37511 

SELECT * FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('test'), NULL, NULL, 'DETAILED') 
--First  run, heap = "index" 0 (no nc indexes ): 0 fwd records, 50002 pages 
--Second run, heap = "index" 0 (two nc indexes): 0 fwd records, 50003 pages 
--But: avg_page_space_used_in_percent changes for the nc indexes after the rebuild. 
--That I take as nc indexes are also rebuilt.

Automating dm_exec_query_stats and dm_db_index_usage_stats analysis

jchang — Mon, 22 Jun 2009 04:25:00 GMT

Everyone should know by now how really useful the DMVs dm_exec_query_stats, and dm_db_index_usage_stats and the associated DMFs for obtaining the SQL and XML plan: dm_exec_sql_text, dm_exec_query_plan, and dm_exec_text_query_plan. Of course it has been explained that dm_exec_query_stats is not a replacement for SQL Server Profiler and SQL Trace. The DMV is only reliable if execution plans are not frequently evicted from the procedure cache and if SQL is not frequently recompiled.

Still the DMV dm_exec_query_stats is popular because the results are available with a simple query. The complexity of setting up a trace, making sure it is not adversely impacting server performance, making sure space is available, and most of all, the effort of parsing the trace are all avoided.

So what do we do next? From the top query statistics we can start looking at the SQL and the execution plans. One minor annoyance is that if we use CROSS APPLY to dm_exec_query_plan, each XML plan has to be saved individually. This can be annoying if one has to remote into to a PC in the server room and this connection is slow.
There is much information in the XML plan that would be helpful if we could see it in a grid along with the execution statistics.

From the index usage statistics, we can eliminate unused indexes. There might also be indexes that are infrequently used. Sometimes it is obvious that certain indexes can be consolidated. Other times, it is necessary to examine the execution plan to determine if another index is sufficient. The XML plan analysis cross-references in which plans each index is used. All of this is tedious work which can be automated. So this is the purpose of the SQLExecStats tool.

A few people have tried it on production systems. There are still bugs, and feature requests, but hopefully I can turn this into something useful over time.

lets use this url for the latest build rather than changing the url for each build

http://www.qdpma.com/tools/ExecStats.zip

The above build is now 20090628. The (poor excuse for) documention is also online http://www.qdpma.com/SQLExecStats.html

Feedback is welcome, in particular, send me the output files so I can try to figure out if this is collecting the right information. Support will be based on availability.

Note: SQL Server 2008 sp1 fix now correctly reports parallel execution plan total_worker_time (CPU). It is not fixed on SQL Server 2005 sp3, latest cu?

Limitations:

European Localization The XML plan is stored as a string, with numbers in US-en format. My program extracts plan cost and estimated row numbers from the XML plan. The previous version did not specify any localization, but I think the default is the host environment. Hence the code double.Format(value) is interpreted as whatever the local environment is instead of US-en. The builds since 20090623 specifies the US-en format in most places.

If some one with European number formats could run this program and send me the output, I would appreciate it.

Bug fixes, features etc

2009-06-22: The password now should display *, I don't do security work, and it shows. Some formatting was changed to right align, comma thousands separator, Very large numbers in Excel cannot be changed, 4 bytes integers are represented as numbers, larger numbers are represented as text.
I will do more formatting changes later

2009-06-28

The error described by Zen occurs with case sensitive collations. Apparently one SQL query I issued from the executable was not a case correct with the columns I declared on the receiving side, and this is sufficient to cause the data table load to fail.

The current build is really an intermediate build. I am adding the ability to collect table and index information for multiple databases and not everything is consistent yet.

Be careful on trying to collect for too many databases, and too many execution plans, there are checks for over size yet.

2009-07-09

I am still in the middle of changing the tool to have the option of collecting table and index info from all databases. So some of the UI is not entirely consistent. However, all bug fixes are in the new version.

Previously there were SQL queries that were not case correct with client-side table definitions, and when the default collation is case sensitive, an error occurred. This should be corrected.

Also, previously I issued DBCC SHOW_STATISTICS([schema.table],[stat])

which can generate an error if there is a hyphen '-' in the table or schema. I changed this to

('schema.table',[stat])
I am not sure if this is the most safe format, should it be: ('schema.table','stat')?
anyways, go to the main url, http://www.qdpma.com/

look for SQL Exec Stats Build 2009-07-09

http://www.qdpma.com/tools/ExecStats_20090709.zip