Thanks to everyone who came out to the user group meeting.  It was great talking to you all.

The slides and sample code used tonight are included in this post.  If you have any questions please drop me a line through my contact page.

ColumbusPASS_Jun23_CDC.zip (1.31 mb)

A quick post of something I get asked from time to time and have to pause to remember.

To get the name of the current login use SYSTEM_USER

To get the security context of the current login use CURRENT_USER.  This function is equivalent to USER_NAME().

SELECT SYSTEM_USER AS [SYSTEM_USER]
    ,CURRENT_USER AS [CURRENT_USER]
    ,USER_NAME() AS [USER_NAME];

For a Windows authenticated user SYSTEM_USER will return domain/username. 

For a SQL authenticated user you will get the SQL login name.

Super handy if you need to insert into a "who did this?" style column.

For some reason I always try to reverse their usage.  Now I can quick reference.  That's one of the best parts of having a blog - off mind storage.  :)

The answer, in SQL Server 2008, is quite a few.  A day or so ago Paul Randall tweeted about the lack of consistency within the column naming in system objects that reference databases (id or name).  It got me to thinking about the ones I knew which naturally led to being bothered that there might be others I don't know about (sometimes I really wish I could turn that off, oh well).

So to satisfy my curiosity (and hopefully enlighten/entertain you) here is the path I took to get the answer.

First, query the catalog view sys.all_columns for columns containing db or database in their names.

SELECT c.name AS ColumnName
FROM sys.all_columns AS c
    INNER JOIN sys.all_objects AS o ON o.[object_id] = c.[object_id]
WHERE c.name LIKE '%db%' OR c.name LIKE '%database%'
GROUP BY c.name;

After a quick look through those results I boiled it down to a meaningful list and went back to sys.all_columns.  The query below returns 65 rows for my instance.

SELECT c.name AS ColumnName
    ,SCHEMA_NAME(o.[schema_id]) AS SchemaName
    ,o.name AS ObjectName
    ,o.type_desc
FROM sys.all_columns AS c
    INNER JOIN sys.all_objects AS o ON o.[object_id] = c.[object_id]
WHERE c.name IN(
                'database'
                ,'Database Name'
                ,'database_id'
                ,'database_name'
                ,'DatabaseID'
                ,'DatabaseName'
                ,'db_id'
                ,'db_name'
                ,'dbid'
                ,'dbname'
                ,'Master DBID'
                ,'orig_db'
                ,'referenced_database_name'
                ,'resource_database_id'
                ,'SourceDatabaseID'
                )
ORDER BY ColumnName
    ,SchemaName
    ,ObjectName;

There were a couple I had forgotten about but most items are DMVs.  Still some fun detail to have in the back of your head for the next time you need it.  Or just remember this blog post.  Either way. :)

My current project has hit that interesting point where the database design is pretty stable and we're starting to go at the objects in multiple directions with code.  I'm really outside my comfort zone this time as we're doing predominantly LINQ to SQL for the application.  I suspect I will have a series of posts about those experiences.  But back to the topic...I usually try to avoid "guessing" about most indexing for my tables and let the access pattern of the application present itself.  Since I'm not writing all the code interacting with these tables I need another manner of finding these patterns.

In SQL Server 2005 the product team introduced the Missing Index Feature.  The Missing Index Feature consists of two components:

• A set of four dynamic management objects that can be queried to return information about missing indexes.
• The MissingIndexes element in XML Showplans, which correlate indexes that the query optimizer considers missing with the queries for which they are missing

The feature is on by default and can only be disabled by starting your instance of SQL Server using the -x argument with the sqlservr command-prompt utility.

For our purposes I'm going to focus on the DMVs and show how we can get some suggestions about where our database might improve with additional indexes.  Note the use of "suggestions" and "might improve". The information gathered by the DMVs is only accurate from the last restart of the service so the detail could vary wildly in sample size ( if you don't have a set period for restarts ).

Here is our query:

SELECT sc.name AS schemaName
    ,t.name AS tableName   
    ,d.equality_columns AS equalityColumns
    ,d.inequality_columns AS inequalityColumns
    ,d.included_columns AS includedColumns
    ,s.user_seeks AS userSeeks
    ,s.user_scans AS userScans
    ,s.avg_user_impact AS averageUserImpact
FROM sys.dm_db_missing_index_details AS d 
    INNER JOIN  sys.dm_db_missing_index_groups AS g 
        ON d.index_handle = g.index_handle 
    INNER JOIN sys.dm_db_missing_index_group_stats AS s 
        ON g.index_group_handle = s.group_handle 
    INNER JOIN sys.tables AS t 
        ON t.[object_id] = d.[object_id]
    INNER JOIN sys.schemas AS sc 
        ON sc.[schema_id] = t.[schema_id]
WHERE d.database_id = DB_ID() 
ORDER BY avg_user_impact DESC;

The main areas we want to take note of are equality, inequality, and included columns.  These will form the basis of our key decision.  We also can get the number of seeks and scans as well as an average statement of impact to the queries in our current workload.

Again, make sure you look at the overall access patterns of your application before running wild with a bunch of CREATE INDEX scripts.  As I pointed out in my indexing series a few weeks back, a bad index can create just as many problems as no index.

There are multiple limitations to the Missing Index Feature discussed here.  It is required reading before adding on to the query above for creating indexes.

If you have questions feel free to drop me line.  Happy indexing to ya...

I recently helped a colleague of mine with some troubleshooting surrounding triggers.  Because he is more app code focused he wasn’t immediately thinking “trigger” with the data issues he was seeing.

If you are working in an database that is not your creation you might find this script helpful.  The following T-SQL touches four catalog views and returns all triggers and what function they support:

SELECT s.name AS SchemaName
    ,ta.name AS TableName
    ,t.name AS TriggerName
    ,CASE
        WHEN t.is_disabled = 1 THEN 'Yes'
        ELSE 'No'
    END AS IsDisabled
    ,CASE
        WHEN t.is_instead_of_trigger = 1 THEN 'Yes'
        ELSE 'No'
    END AS IsInsteadOfTrigger
    ,te.type_desc AS TriggerType
    ,t.create_date AS DateCreated
    ,t.modify_date AS LastModifiedDate
FROM sys.triggers AS t
    INNER JOIN sys.trigger_events AS te ON te.[object_id] = t.[object_id]
    INNER JOIN sys.tables AS ta ON ta.[object_id] = t.parent_id
    INNER JOIN sys.schemas AS s ON s.[schema_id] = ta.[schema_id];

As a follow up to my talk on CDC for the Nashville PASS group I wanted to point out two DMVs that support CDC processing in our instance.

The first is sys.dm_cdc_errors which returns errors encountered in each log scan session.  This DMV requires VIEW DATABASE STATE permissions and returns the errors for the past 32 sessions.  The column list for the DMV is pretty straightforward but we can add one wrinkle:

SELECT e.session_id
    ,e.phase_number
    ,e.entry_time
    ,e.[error_number]
    ,e.[error_severity]
    ,e.[error_state]
    ,e.[error_message]
    ,e.start_lsn
    ,sys.fn_cdc_map_lsn_to_time(e.start_lsn) AS StartLSNTime
    ,e.begin_lsn
    ,sys.fn_cdc_map_lsn_to_time(e.begin_lsn) AS BeginLSNTime
    ,e.sequence_value
FROM sys.dm_cdc_errors AS e;

We can make use of the system functions sys.fn_cdc_map_lsn_to_time which will give us a datetime representation of the log sequence number (LSN).

The second DMV is sys.dm_cdc_log_scan_sessions which returns a row for each log scan session in the database.  There are two important points to remember when dealing with this DMV:

1. The session_id of 0 is an aggregate row for all sessions since the last restart of the SQL Server service.
2. The column empty_scan_count can be greater than 1 for successive scans that yield no changes.

We can run the following two queries to either get detail or summary data on our CDC processing:

--Aggregate CDC information since last restart
SELECT session_id
    ,start_time
    ,end_time
    ,scan_phase
    ,error_count
    ,tran_count
    ,last_commit_cdc_lsn
    ,last_commit_time
    ,log_record_count
    ,command_count / COALESCE(NULLIF(duration, 0), 1) AS Throughput
    ,latency
    ,empty_scan_count
    ,failed_sessions_count
FROM sys.dm_cdc_log_scan_sessions
WHERE session_id = 0;

--Detailed CDC information since restart
SELECT session_id
    ,start_time
    ,end_time
    ,scan_phase
    ,error_count
    ,tran_count
    ,last_commit_cdc_lsn
    ,last_commit_time
    ,log_record_count
    ,command_count
    ,duration
    ,latency
    ,empty_scan_count
    ,failed_sessions_count
FROM sys.dm_cdc_log_scan_sessions
WHERE session_id > 0;

Between the two DMVs we can manage our CDC implementation for both errors and general performance. 

Next up, a look at the metadata that CDC uses to manage what data is captured and stored.