First Time Speaker

So this week was another first for me, I finally bit the bullet and gave a talk at the Leeds SQL Server User Group and it was great, I thoroughly enjoyed it!

Public speaking is something that I have wanted to do for a while now but have put off because of nerves to be honest.  I have given plenty talks to colleagues at a number of previous employers but never publicly, I had the mindset that I would be more comfortable speaking to my peers, rather than as I had previously envisaged a "rowdy bunch of data professionals".  My talk was about indexes and in particular demonstrating practices that I employ in my position as a production DBA.  You can find the PowerPoint presentation and accompanying scripts here.  The scripts were developed and used on SQL Server 2008 R2.

I can honestly say that it was an enjoyable experience, I wanted to involve the audience so rather than just listening there could be some interaction between us, which I thought worked very well.  I have taken a lot away from the evening and learned a few things about myself as well as how I can improve the next time I give a talk.  Yes I want to continue to do public speaking, why?  Well, I want to continue to give back to the fantastic SQL Server Community we have, to teach and also to learn.  I believe this will make me not only a better DBA, but a better person.

Enjoy!

Chris

Tables without a Clustered Index

Yesterday while adding some new logic to an existing process I noticed an unacceptable level of performance degradation with the routine.  Investigation led me to find that a new table had slipped through the net and been created without a clustered index.  Needless to say adding a clustered index and rebuilding the non-clustered indexes on the table improved the performance of the routine significantly.

Being a DBA, this got me thinking about how this had slipped through the net, how I could prevent it in future and equally as important how many other existing tables didn't have a Clustered Index?  I knocked together the below script which will create a SQL Job that is scheduled to run every Monday at 07:07 and email an operator if there are any tables without a clustered index in all user databases.

There are a few things that will need changing;

·         @notify_email_operator_name=N'Chris'

·         SET @EmailProfile = ''Chris''

·         SET @EmailRecipient = ''Chris@SQLServer365.co.uk''

You can also obviously change the schedule accordingly to suit your needs.

I would be interested to know the results on your servers, leave me a comment with the email subject text from the resulting email.

/*

      -----------------------------------------------------------------

      Find tables without a clustered index

      -----------------------------------------------------------------

    

      For more SQL resources, check out SQLServer365.blogspot.co.uk

      -----------------------------------------------------------------

      You may alter this code for your own purposes.

      You may republish altered code as long as you give due credit.

      You must obtain prior permission before blogging this code.

      THIS CODE AND INFORMATION ARE PROVIDED "AS IS"

    

      -----------------------------------------------------------------

*/

-- Set database context

USE [msdb]

GO

IF  EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N'Alert - TablesWithoutClusteredIndex')

EXEC msdb.dbo.sp_delete_job @job_name = N'Alert - TablesWithoutClusteredIndex', @delete_unused_schedule=1

GO

BEGIN TRANSACTION

DECLARE @ReturnCode INT

SELECT @ReturnCode = 0

IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)

BEGIN

EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)

EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'Alert - TablesWithoutClusteredIndex',

            @enabled=1,

            @notify_level_eventlog=0,

            @notify_level_email=2,

            @notify_level_netsend=0,

            @notify_level_page=0,

            @delete_level=0,

            @description=N'This job runs on a Monday morning at 07:07 and will email if there are any tables without a clustered index in any user database.',

            @category_name=N'[Uncategorized (Local)]',

            @owner_login_name=N'sa', @notify_email_operator_name=N'Chris', @job_id = @jobId OUTPUT

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

/****** Object:  Step [Find tables Without a Clustered Index]    Script Date: 11/29/2012 12:20:42 ******/

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Find tables Without a Clustered Index',

            @step_id=1,

            @cmdexec_success_code=0,

            @on_success_action=1,

            @on_success_step_id=0,

            @on_fail_action=2,

            @on_fail_step_id=0,

            @retry_attempts=0,

            @retry_interval=0,

            @os_run_priority=0, @subsystem=N'TSQL',

            @command=N'-- Set database context

USE master;

GO

-- Declare variables

DECLARE @EmailProfile VARCHAR(255)

DECLARE @EmailRecipient VARCHAR(255)

DECLARE @EmailSubject VARCHAR(255)

DECLARE @IndexUsageStats INT

DECLARE @TableCount INT

DECLARE @DatabaseCount INT

-- Set variables

SET @EmailProfile = ''Chris''

SET @EmailRecipient = ''Chris.McGowan@SQLServer365.co.uk''

-- Drop temporary table if exists

IF OBJECT_ID(''tempDB.dbo.#NoClusteredIndex'') IS NOT NULL

    DROP TABLE #NoClusteredIndex;

     

-- Create Temporary Table

CREATE TABLE #NoClusteredIndex

    (

      DatabaseName VARCHAR(255) ,

      SchemaName VARCHAR(255) ,

      TableName VARCHAR(1000)

    );

INSERT INTO #NoClusteredIndex

EXEC sp_msforeachdb ''USE [?];

IF ''''?'''' NOT IN (''''master'''', ''''model'''',''''msdb'''', ''''tempdb'''', ''''ReportServer'''', ''''ReportServerTempDB'''', ''''Distribution'''')

BEGIN

SELECT DISTINCT  DB_NAME() AS DatabaseName, SCHEMA_NAME(s.[schema_id]) AS SchemaName, OBJECT_NAME(i.[object_id]) AS TableName

FROM sys.indexes i

INNER JOIN sys.objects o ON o.[object_id] = i.[object_id]

INNER JOIN sys.schemas s ON s.[schema_id] = o.[schema_id]

WHERE i.INDEX_ID = 0

AND OBJECTPROPERTY(i.[object_id],''''IsUserTable'''') = 1

ORDER BY DatabaseName, SchemaName, TableName ASC;

END

''

-- Check for tables without a clustered index

IF EXISTS ( SELECT  1

            FROM #NoClusteredIndex)

    BEGIN

        DECLARE @tableHTML NVARCHAR(MAX); 

        SET @tableHTML = N''<style type="text/css">''

            + N''.h1 {font-family: Arial, verdana;font-size:16px;border:0px;background-color:white;} ''

            + N''.h2 {font-family: Arial, verdana;font-size:12px;border:0px;background-color:white;} ''

            + N''body {font-family: Arial, verdana;} ''

            + N''table{font-size:12px; border-collapse:collapse;border:1px solid black; padding:3px;} ''

            + N''td{background-color:#F1F1F1; border:1px solid black; padding:3px;} ''

            + N''th{background-color:#99CCFF; border:1px solid black; padding:3px;}''

            + N''</style>'' + N''<table border="1">'' + N''<tr>''

            + N''<th>DatabaseName</th>''

            + N''<th>SchemaName</th>''

            + N''<th>TableName</th>''

            + N''</tr>''

            + CAST(( SELECT td = DatabaseName,

                            '''',

                            td = SchemaName,

                            '''',

                            td = TableName,

                            ''''                        

                     FROM   #NoClusteredIndex

                   FOR

                     XML PATH(''tr'') ,

                         TYPE

                   ) AS NVARCHAR(MAX)) + N''</table>''; 

     

            -- Count tables

            SELECT @TableCount = COUNT(1) FROM #NoClusteredIndex;

           

            -- Count tables

            SELECT @DatabaseCount = COUNT(DISTINCT DatabaseName) FROM #NoClusteredIndex;

     

            -- Set subject

            SET @EmailSubject = ''ALERT - '' + CAST(@TableCount AS VARCHAR(100)) +  '' Tables without a Clustered Index on '' + @@SERVERNAME + '' accross '' + CAST(@DatabaseCount AS VARCHAR(100)) + '' databases''

           

            -- Email results 

        EXEC msdb.dbo.sp_send_dbmail @profile_name = @EmailProfile,

            @recipients = @EmailRecipient, @subject = @EmailSubject,

            @body = @tableHTML, @body_format = ''HTML''; 

    END

    GO',

            @database_name=N'master',

            @flags=12

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'(07:07) Monday',

            @enabled=1,

            @freq_type=8,

            @freq_interval=2,

            @freq_subday_type=1,

            @freq_subday_interval=0,

            @freq_relative_interval=0,

            @freq_recurrence_factor=1,

            @active_start_date=20121129,

            @active_end_date=99991231,

            @active_start_time=70700,

            @active_end_time=235959

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

COMMIT TRANSACTION

GOTO EndSave

QuitWithRollback:

    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION

EndSave:

GO

Enjoy!

Chris

A Script A Day - Day 17 - Possible Poor Indexes

Today’s script is one I’ve taken from a job I use to collect possible poor indexes.  I’ve tweeked it slightly so you can choose the database to run it against. 

It goes without saying that you should never just remove an index without some considerable investigation doesn't it???!!!

/*

      -----------------------------------------------------------------

      Possible poor indexes

      -----------------------------------------------------------------

     

      For more SQL resources, check out SQLServer365.blogspot.co.uk

      -----------------------------------------------------------------

      You may alter this code for your own purposes.

      You may republish altered code as long as you give due credit.

      You must obtain prior permission before blogging this code.

 

      THIS CODE AND INFORMATION ARE PROVIDED "AS IS"

     

      -----------------------------------------------------------------

*/

-- Change db context

USE DatabaseNameHere;

GO

-- Get Possible poor indexes

SELECT

      OBJECT_NAME(S.[object_id]) AS [Table Name],

      I.name AS [Index Name],

      I.index_id,

    user_updates AS [Total Writes],

    (user_seeks + user_scans + user_lookups) AS [Total Reads],

    (user_updates - (user_seeks + user_scans + user_lookups)) AS [Difference]

FROM

      sys.dm_db_index_usage_stats AS S WITH (NOLOCK)

      INNER JOIN sys.indexes AS I WITH (NOLOCK) ON S.[object_id] = I.[object_id] AND I.index_id = s.index_id

WHERE

      OBJECTPROPERTY(s.[object_id],'IsUserTable') = 1

      AND s.database_id = DB_ID()

      AND user_updates > (user_seeks + user_scans + user_lookups)

      AND I.index_id > 1

ORDER BY

      [Difference] DESC,

      [Total Writes] DESC,

      [Total Reads] ASC

OPTION (RECOMPILE);

GO

Enjoy!

Chris

A Script A Day - Day 9 - Index Compression Estimations

Today's script provides amongst other information Index compression estimates based on existing index information.  All you need to do is set the database context and specify a schema, table and compression type.  This script has been useful several times in the past so hopefully you will also find it useful.

/*

      -----------------------------------------------------------------

      Index Compression Estimations

      -----------------------------------------------------------------

     

      For more SQL resources, check out SQLServer365.blogspot.co.uk

      -----------------------------------------------------------------

      You may alter this code for your own purposes.

      You may republish altered code as long as you give due credit.

      You must obtain prior permission before blogging this code.

 

      THIS CODE AND INFORMATION ARE PROVIDED "AS IS"

     

      -----------------------------------------------------------------

*/

USE DatabaseNameHere;

GO

-- Get estimated data compression savings and other index info

-- for every index in the specified table

SET NOCOUNT ON;

DECLARE @SchemaName SYSNAME = N'SchemaNameHere';                          -- Specify schema name

DECLARE @TableName SYSNAME = N'TableNameHere';                           -- Specify table name

DECLARE @IndexID INT = 1;

DECLARE @CompressionType NVARCHAR(60) = N'CompressionTypeHere'                 -- Specify data compression type (PAGE, ROW, or NONE)

-- Get table name, row count, and compression status

-- for clustered index or heap table

SELECT      OBJECT_NAME([object_id]) AS [ObjectName],

            SUM([Rows]) AS [RowCount],

            data_compression_desc AS [CompressionType]

FROM sys.partitions

WHERE index_id < 2 -- ignore the partitions from the non-clustered index if any

AND OBJECT_NAME([object_id]) = @TableName

GROUP BY [object_id], data_compression_desc

ORDER BY SUM([Rows]) DESC;

-- Breaks down buffers used by current database by object (table, index) in the buffer pool

SELECT      OBJECT_NAME(p.[object_id]) AS [ObjectName],

            p.index_id,

            COUNT(*)/128 AS [Buffer size(MB)],

            COUNT(*) AS [BufferCount],

            p.data_compression_desc AS [CompressionType]

FROM  sys.allocation_units AS a

            INNER JOIN sys.dm_os_buffer_descriptors AS b ON a.allocation_unit_id = b.allocation_unit_id

            INNER JOIN sys.partitions AS p ON a.container_id = p.hobt_id

WHERE b.database_id = DB_ID()

            AND OBJECT_NAME(p.[object_id]) = @TableName

            AND p.[object_id] > 100

GROUP BY p.[object_id], p.index_id, p.data_compression_desc

ORDER BY [BufferCount] DESC;

-- Shows you which indexes are taking the most space in the buffer cache

-- Get current and estimated size for every index in specified table

DECLARE curIndexID CURSOR

FAST_FORWARD

FOR

    -- Get list of index IDs for this table

    SELECT  s.index_id

    FROM    sys.dm_db_index_usage_stats AS s

    WHERE   OBJECT_NAME(s.[object_id]) = @TableName

                  AND s.database_id = DB_ID()

    ORDER BY s.index_id;

       

OPEN curIndexID;

FETCH NEXT

FROM curIndexID

INTO @IndexID;

-- Loop through every index in the table and run sp_estimate_data_compression_savings

WHILE @@FETCH_STATUS = 0

    BEGIN

        -- Get current and estimated size for specified index with specified compression type

        EXEC sp_estimate_data_compression_savings @SchemaName, @TableName, @IndexID, NULL, @CompressionType;

        FETCH NEXT

        FROM curIndexID

        INTO @IndexID;

    END

CLOSE curIndexID;

DEALLOCATE curIndexID;

-- Index Read/Write stats for a single table

SELECT      OBJECT_NAME(s.[object_id]) AS [TableName],

            i.name AS [IndexName],

            i.index_id,

            SUM(user_seeks) AS [User Seeks],

            SUM(user_scans) AS [User Scans],

            SUM(user_lookups)AS [User Lookups],

            SUM(user_seeks + user_scans + user_lookups)AS [Total Reads],

            SUM(user_updates) AS [Total Writes]    

FROM  sys.dm_db_index_usage_stats AS s

            INNER JOIN sys.indexes AS i ON s.[object_id] = i.[object_id]

            AND i.index_id = s.index_id

WHERE OBJECTPROPERTY(s.[object_id],'IsUserTable') = 1

            AND s.database_id = DB_ID()

            AND OBJECT_NAME(s.[object_id]) = @TableName

GROUP BY OBJECT_NAME(s.[object_id]), i.name, i.index_id

ORDER BY [Total Writes] DESC, [Total Reads] DESC;

-- Get basic index information (does not include filtered indexes or included columns)

--EXEC sp_helpindex @TableName;

-- Get size and available space for files in current database

SELECT      name AS [File Name],

            physical_name AS [Physical Name],

            size / 128.0 AS [Total Size in MB],

            size / 128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0 AS [Available Space In MB],

            [file_id]

FROM  sys.database_files;

Enjoy!


Chris