Tag Archives: index

When were the statistics updated?

Just a while ago I replied to one question where the user wanted to know how to find out the date on which the statistics for a given index were updated. The requirement was to reindex the tables based on the last update date of the statistics. The best approach is to rebuild the indexes based on the average fragmentation. But at times we may have to build the indexes based on the statistics update date. I have an instance in my environment where the client wants all the indexes to be rebuilt irrespective of the fragmentation level. Hence the answer to this question was very similar to what I had already implemented.

STATS_DATE and DBCC SHOW_STATISTICS provide the date of the most recent update for statistics on a table.

DBCC SHOW_STATISTICS provides the details of an index. Here is an example of this query in action.

DBCC SHOW_STATISTICS ('backupset', backupsetuuid)

The output is as follows.

Whereas STATS_DATE can be used to get the last updated date of the indexes on a given database. This is the one I make use of always.

For SQL Server 2000:

B.Name,A.Name as IndexName,
STATS_DATE ( a.id , indid ) as StatsDate
FROM sysindexes A,
sysobjects B
WHERE a.id=b.id

For SQL Server 2005 & above:

object_name(object_id)as TableName,
name as IndexName,
STATS_DATE(object_id, stats_id)as StatsDate
FROM sys.stats
ORDER BY TableName, IndexName

The output will be something like this

“select *, %%bmk%% from tablename” while rebuilding the index

When the rebuild index was being rebuilt on Saturday, everyone was coming up with their own version why it was taking so long to complete. Ultimately it turned out to be the issue with the disk subsystems, nothing else. One of the Application Team member pointed out to an interesting observation.

We use Idera Diagnostic Manager to monitor our Production instances. When we were rebuilding the 13th index on that table, they pointed out that in Idera they could see an SQL statement which looked strange.

insert [DatabaseName].[dbo].[TheActualTableName] select *, %%bmk%% from [DatabaseName].[dbo].[TheActualTableName]

Everyone started asking who is running this insert and what is %%bmk%% ? I remembered reading about bmk sometime back on this whitepaper at TechNet. Even though this whitepaper talks about Online Reindex, it talks about how an Index is rebuilt in general. During the Build Phase of rebuilding the index, the new index is built by Bulk Inserting data from the old index. During the bulk insertion, the above insert statement is issued. Since the table in question was 50 GB in size and the disk subsystem was slow, this query ran for a long time and it was captured by Idera Diagnostic Manager.

Even though I knew this on the tip of my tongue, customer needs the “official” documentation to accept it. It took a while to google for this document and pass on the snip to him. It was a nice revisit to the re-index internals.


Mr.DBA what is the status of Rebuild Index?

Within a few hours of my announcement of coming back to business yesterday, my cellphone started ringing. Call from Office on a Saturday evening could not mean nothing but an escalation call. Indeed it was one.

The customer had some important month-end jobs running but the progress of the job was too slow. There are a couple of genii in the Application DBA Team who are part of the Application Development team. For any performance issue on the SQL Server instance they have only two resolutions. Rebuild the Index. If it does not work Update the Statistics. If neither does not work, disagree with whatever the Production DBA Team suggests! For this issue as well, they suggested that the indexes on a table be rebuilt by the Production DBA Team. Halfheartedly, the DBA started the rebuild index job for that table.

This is one of the SQL Server 2000 instances which has the worst possible disk subsystem. In the last one year, the disk subsystem, firmware, faulty cables attributed to atleast 5 high severity incidents. The reindex job had already ran for 12 hours and it was still trying to rebuild the 15 indexes of a table 50 GB in size. No other process was blocking this job, but it was too slow. Customer was curious to know what was going on. Since the job had already run for 12 hours, killing the job would mean that the rollback would take same amount time or possibly more. DBA kept on telling that since this instance is SQL Server 2000, there is no way to check the percentage of completion of this reindex job. Had the DBA written the script to rebuild the indices index by index, at least we could get which index was currently being rebuilt. But customer was in no mood to buy that theory because business critical jobs were on hold because of this.

The physical_io value in sysprocesses was increasing and the waittype, waittime values were also constantly changing. The kpid column had also some value in it. Hence this process was not dead. The waitresource column was the one which helped us to find out what exactly the process was doing.

The waitresource value had a Page address in it. All I had to do was to check the object and index id from the page. Fired up this query in the query analyzer to get that information.

dbcc traceon(3604)
dbcc page(7,4,2044928)

The output had the object and index details.

The object id was that of the table whose indexes were rebuilt and the index id 13 was the 13th index in sysindexes out of the 15 in that table. Huh. Just informed the customer that the reindex job had already completed rebuilding 12 indexes and it was working on index 13. “That is what we were waiting to hear from Mr. DBA” replied the customer. Now that they found someone to keep asking for updates, all night I was Geoffrey Boycott, giving running commentary of the status of the reindex job except for Geoff’s Yorkshire accent.

I also noticed that once the reindex of this index completed and before it started the reindex of the next index, the process would wait on a page which had Object ID 99 on it. select object_name(99) would return the name Allocation. Allocation is not a table and it cannot be found in sysobjects as well. Allocation refers to the GAM, SGAM and the related system tables. I had to explain this as well to the customer.

After completing the activities on Allocation, the process would move on to the next index and the waiting page address would have this index details in it. In my case, it moved on to Index 14. After an hour or so, all the indexes were rebuilt (it took 23 hours to complete!) and as usual got a “Good job team” from the customer and dropped off from the call.

The clock had struck 8 AM already and started my Sunday by dozing off!

Find tables with Large Obejct (LOB) data types in a database

From SQL Server 2005 onwards, the indexes can be re-indexed ONLINE. This ensures that the underlying tables and indexes are available for other users when the re-indexing is taking place. Also Standard and Workgroup editions of SQL

The limitation for ONLINE Re-indexing is that it cannot be run on tables which have Large Object Data type columns (image, text, ntext, varchar(max), nvarchar(max), varbinary(max), xml). The Alter Index documentation on MSDN has more details on this.

Yesterday, I responded to a question which read “How to find out LOB data types in a table?”. There are many ways to find it out, here is the one which I use frequently.

SELECT a.name as [ColumnName],B.name as [Table Name]
	JOIN SYS.OBJECTS B ON A.object_id = B.object_id
	WHERE A.system_type_id in (35,34,241,99)
	AND B.type ='U'
	AND B.name <>'dtproperties'
	SELECT a.name, B.NAME
	JOIN SYS.OBJECTS B ON A.object_id = B.object_id
	WHERE A.system_type_id IN (167,231,165)
	AND A.max_length = -1
	AND B.type ='U'
	AND B.name <>'dtproperties'

With this script, the LOB data type columns can be identified and is useful while trying to reindex selected tables online.

Find index space used details | SQL Server 2000

Today in one of the Online Communities, a question was asked. How to find the size/space used details of an Index in SQL Server 2000?

The places to look for this information are the sysindexes and sysobjects tables. TheĀ  values in the reserved and used columns are in pages. Number of pages * 8 KB will give the actual size. The below query gives the Reserved and Used details for all the indexes in a table.

a.name,sum(reserved)*8 as [sum_reserved (KB)],
sum(used)*8 as [sum_used (KB)]
sysindexes a, sysobjects b
indid in (1,255) and a.id = b.id and b.type='U'
group by
order by object_name(a.id), a.name

Even though this is not the most elegant way of getting the information, this query provides the requisite information.