Thanks to all the people that came out today, it was a great time!  If you have any questions feel free to shoot me something through the contact form.

NashvillePASS_May29_CDC.zip (1.35 mb)

I will be speaking to the Columbus, GA SQL Users Group next month on Change Data Capture.  If you are in the Columbus area swing on by and join the conversation.

I was recently accepted into the Amazon Kindle store in their blog section.  If you are interested you can add it through the web here.  I haven?t looked for it on a Kindle but I?m pretty sure a name search would work.  Enjoy!

I’ve been a fan of Jamie’s blog for a while now as he not only gives you great SSIS detail but his interests in outlying technology match up well with my own.  He’s got another great post about using LiveMesh to share a code repository.  Brilliant!

Check out the details here.

We’re finishing up on the thread of index operations and DMVs today with another management function, this time sys.dm_db_index_operational_stats.  This function is similar to the management view sys.dm_db_index_usage_stats in column output but has differing logic in how rows are written.  The main difference between these two objects is that sys.dm_db_index_operational_stats is taking its detail from the storage engine while sys.dm_index_usage_stats is generated from the query processor.  Additionally, items in sys.dm_db_operational_stats are only persisted while they are in the metadata cache.  This means we cannot use this function in the same manner I did in Part 1 to show whether an index has been used or not.

There are three queries I want to show today as interactions with this function.  First, we will get general access detail:

--Look for access patterns in indexes
SELECT s.database_id
    ,t.name
    ,i.name
    ,i.type_desc
    ,s.leaf_insert_count 
    ,s.leaf_delete_count 
    ,s.leaf_update_count 
    ,s.leaf_ghost_count 
    ,s.range_scan_count 
    ,s.singleton_lookup_count 
FROM sys.dm_db_index_operational_stats(db_id(), null, null, null) AS s
    INNER JOIN sys.tables AS t ON t.[object_id] = s.[object_id]
    INNER JOIN sys.indexes AS i ON i.index_id = s.index_id
        AND i.[object_id] = s.[object_id]
WHERE t.is_ms_shipped = 0;

You will notice we interact with the function much the same as sys.dm_db_physical_stats in Part 2, just without the parameter for scan mode.  If you missed that post the parameter options are as follows:

• database_id | NULL | 0 | DEFAULT
• object_id | NULL | 0 | DEFAULT
• index_id | 0 | NULL | -1 | DEFAULT
• partition_number | NULL | 0 | DEFAULT

If any of the parameters are set to NULL the parameters following it must be NULL.

Another use of the function is in analyzing contention:

--Look for contention
SELECT s.database_id
    ,t.name
    ,i.name
    ,i.type_desc
    --latch contention
    ,s.page_latch_wait_count
    ,s.page_latch_wait_in_ms
    --# of times we tried to acquire row/page locks
    ,s.row_lock_count
    ,s.page_lock_count
    --lock contention
    ,s.row_lock_wait_count
    ,s.row_lock_wait_in_ms
    ,s.page_lock_wait_count
    ,s.page_latch_wait_in_ms
FROM sys.dm_db_index_operational_stats(db_id(), null, null, null) AS s
    INNER JOIN sys.tables AS t ON t.[object_id] = s.[object_id]
    INNER JOIN sys.indexes AS i ON i.index_id = s.index_id
        AND i.[object_id] = s.[object_id]
WHERE t.is_ms_shipped = 0;

Last, we can check physical I/O on an index

--Look for physical IO detail
SELECT s.database_id
    ,t.name
    ,i.name
    ,i.type_desc
    ,s.page_io_latch_wait_count
    ,s.page_io_latch_wait_in_ms
FROM sys.dm_db_index_operational_stats(db_id(), null, null, null) AS s
    INNER JOIN sys.tables AS t ON t.[object_id] = s.[object_id]
    INNER JOIN sys.indexes AS i ON i.index_id = s.index_id
        AND i.[object_id] = s.[object_id]
WHERE t.is_ms_shipped = 0;

Those are just a few of the bits of joy that can be gleaned from this management function.  I really urge you to look through the full output list in BOL and see what you can apply to your instance. 

I hope this series has given you a couple thoughts on how to better interact with your indexes and gauge their affect on your database.  As always, I welcome your questions or comments.  Happy querying…

I will be speaking to the Nashville PASS Chapter next week on Change Data Capture.  The meeting starts at 11:30 CST and I will be speaking from 12:00 to 1:00.  If you are in the Nashville area or want to take a drive that day stop on by.

Continuing the indexing theme, today we will be looking at a Dynamic Management Function (still lumped in as a DMV if you are new to these) called sys.dm_db_index_physical_stats.  It is the first step down your maintenance path, if you consider my last post as a fork in the road for deciding whether or not the index should be kept.

We interact with the function as you would any other table valued function.  The function takes the following parameters:

• database_id | NULL | 0 | DEFAULT
  • If NULL is specified all databases are returned
  • If you specify NULL for database_id you must also specify NULL for object_id, index_id, and partition_number
• object_id  | NULL | 0 | DEFAULT
  • If NULL is specified all table objects are returned
  • If you specify NULL for object_id you must also specify NULL for index_id and partition_number
• index_id | 0 | NULL | -1 | DEFAULT
  • If NULL is specified all indexes are returned
  • If you specify NULL for index_id you must also specify NULL for partition_number
• partition_number | NULL | 0 | DEFAULT
• mode | LIMITED | SAMPLED | DETAILED

Here are a few examples of differing mode values and their uses:

LIMITED

If you are primarily interested in fragmentation levels use the LIMITED mode.

SELECT *
FROM sys.dm_db_index_physical_stats(db_id(), null, null, null, 'LIMITED');

This is the most simple method of interaction, with only a database ID being passed and LIMITED scan level.  This will return all the objects in the database with the following output:

Note, in the LIMITED scan level the columns avg_page_space_used_in_percent, record_count, ghost_record_count, version_ghost_record_count, min_record_size_in_bytes, max_record_size_in_bytes, avg_record_size_in_bytes, forwarded_record_count, and compressed_page_count are always NULL.

SAMPLED

If you are interested in the effectiveness of PAGE compression, you can execute the following query:

SELECT DB_NAME(s.database_id) AS DatabaseName
    ,OBJECT_NAME(s.[object_id]) AS ObjectName
    ,i.name
    ,s.partition_number
    ,s.index_type_desc
    ,s.alloc_unit_type_desc
    ,s.index_depth
    ,s.page_count
    ,s.compressed_page_count
FROM sys.dm_db_index_physical_stats(db_id(), null, null, null, 'SAMPLED') AS s
    INNER JOIN sys.indexes AS i ON i.[object_id] = s.[object_id]
        AND i.index_id = s.index_id;

DETAILED

If you are interested in getting details on disk space usage you can execute the following query:

SELECT DB_NAME(s.database_id) AS DatabaseName
    ,OBJECT_NAME(s.[object_id]) AS ObjectName
    ,i.name
    ,s.partition_number
    ,s.index_type_desc
    ,s.alloc_unit_type_desc
    ,s.index_depth
    ,s.page_count
    ,s.avg_page_space_used_in_percent
FROM sys.dm_db_index_physical_stats(db_id(), null, null, null, 'DETAILED') AS s
    INNER JOIN sys.indexes AS i ON i.[object_id] = s.[object_id]
        AND i.index_id = s.index_id;

There are a few other points to be aware of with this function:

• As your mode changes (LIMITED—>SAMPLED—>DETAILED) your cost for execution climbs.  This is due to further levels of the index being traversed.  Be extremely careful about when you execute this in a production environment.
• Be as specific as possible with your parameters.  If you can achieve your result with less scans, the better you are for it.
• Don’t take the results as a silver bullet.  For certain usage patterns fragmentation isn’t reason enough to rebuild/reorganize an index.

Next up…part 3 of course! :)

It was time to leave the nest of the Magenic blog site and start my own branded site.  I have pulled over my previous Magenic content and will mirror to that site as well.  Going forward, this will be the main site for my content though so update RSS accordingly.  I’m really excited about some of the new possibilities here, hopefully you will be too.

One of the more important (and contentious) structures for database applications are indexes.  When used well they can aid an application greatly, but when used poorly they can cause greater overhead than if no index existed at all. 

There are two scenarios I will be discussing today that make use of the Dynamic Management View (DMV from here) sys.dm_db_index_usage_stats.  This DMV records counts of index operations by type.  It is important to note that the counters of this DMV are emptied with each restart of the SQL Server service.

The first scenario is a good introduction to this DMV if you are unfamiliar with it.  We simply want to find our most active indexes.  We can use the following query to return this detail:

--Get most used indexes Insert/Update/Delete and Selects
SELECT SCHEMA_NAME(o.[schema_id]) AS SchemaName
    ,o.name AS ObjectName
    ,i.name AS IndexName
    ,i.type_desc AS IndexType
    ,(COALESCE(us.user_seeks, 0) + 
        COALESCE(us.user_scans, 0) + 
        COALESCE(us.user_lookups, 0) + 
        COALESCE(us.user_updates, 0)
    ) AS Activity
FROM sys.objects AS o
    INNER JOIN sys.indexes AS i 
        ON i.[object_id] = o.[object_id]
    LEFT JOIN sys.dm_db_index_usage_stats AS us 
        ON us.[object_id] = o.[object_id]
        AND us.index_id = i.index_id
WHERE o.is_ms_shipped = 0
ORDER BY Activity DESC;

In sys.dm_db_index_usage_stats we get counters for each seek, scan, and lookup by a user query.  Each “update” query also gets a counter.  This can be misleading at first as you might think the name is exact to its usage.  This isn’t the case.  In actuality, INSERT/UPDATE/DELETE activities all get recorded under this column.  So in the above query we are able to find all activity against an index and rank them accordingly.  This can then be factored in to your overall maintenance strategy.

Next, we will look at a slightly different type of analysis.  One of the hidden costs of indexing is maintenance.  I’ve seen this blown off during modeling/development and assumed as a zero cost item.  Unfortunately, everything in life has a cost.  With data (and data access) growing, it’s vitally important that we don’t waste time on operations that add no value.  To that point, I will use the same DMV but this time finding indexes with a higher maintenance cost than usage.

--Indexes with more Insert/Update/Delete operations than Selects
;WITH IndexAnalysis AS
(
SELECT SCHEMA_NAME(o.[schema_id]) AS SchemaName
    ,o.name AS ObjectName
    ,i.name AS IndexName
    ,i.type_desc AS IndexType
    ,(COALESCE(us.user_seeks, 0) + 
        COALESCE(us.user_scans, 0) + 
        COALESCE(us.user_lookups, 0)
    ) AS QueryActivity
    ,COALESCE(us.user_updates, 0) AS MaintenanceActivity
FROM sys.objects AS o
    INNER JOIN sys.indexes AS i 
        ON i.[object_id] = o.[object_id]
    LEFT JOIN sys.dm_db_index_usage_stats AS us 
        ON us.[object_id] = o.[object_id]
        AND us.index_id = i.index_id
WHERE o.is_ms_shipped = 0
)
SELECT *
FROM IndexAnalysis
WHERE MaintenanceActivity > QueryActivity
ORDER BY MaintenanceActivity DESC;

By combining the query activities and then comparing against maintenance cost we can clearly state the “value” of an index.  From here it is simply a matter of pruning the indexes that have a greater overhead.  For large or older implementations this can usually net quite a boost in resource time as maintenance activities decrease.

I’m going to continue with the this general topic in following posts as there are many other DMVs that can help us make the most of our indexes.

I am happy to report that I’m writing this on a snappy new install of Windows 7.  I installed on a Dell Latitude D620, with a dual core and 3GB of RAM.  This isn’t exactly a cutting edge device so I hope my experiences will keep you from rolling your eyes and thinking “yeah, right…”. 

So here is a quick list of thoughts from this install and other conversations during the Windows 7 beta process:

1.  Use Live Mesh.  I can’t express how much effort this takes off your process.  When your data is replicated elsewhere you really save some time.  Of course, this does have a 5GB limit, but that leads me to my second point.

2. Off-load the non day to day stuff to an external drive.  Unless you are an incredible pack rat you should be able to pick up a drive under $125 in a range of sizes.  In the metro Atlanta area you can now get 160 gigs for under $70.

3. Install from USB.  I was able to go from boot to completed setup in 14 minutes today.  No joke.  Again, this was not some NEC sponsored craziness…this was a $12 no name 8 gig flash drive I got last night as Sam’s.  Here’s a link for the setup of the drive, I don’t wish to rehash.

4. Do a clean install.  For the sake of all of us, format the drive first.

5.  Investigate the power options.  Especially if you are coming from XP and/or installing on a laptop, have a look at the settings here.  You can set a number of options of how your device will behave.  My personal favorite is “Choose what closing the lid does”.

6. Don’t skip the desktop.  Have a look at the preloaded desktop options, someone in the development team obviously spent a little time here.  And boy did they have an odd sense of humor.

Those are my quick list.  I would be curious to hear other people’s experiences.  As I come across others I will post again.  Until then, happy installing!