I received an e-mail from Microsoft today presenting me with the 2009 Microsoft MVP Award for the work I’ve done with SQL Server.  I thrilled beyond believe to have this honor.  I think this means I should buy myself a new jet ski… is three of them really enough?

Thanks to everyone that has helped me as I’ve played with SQL Server over the years.  From my start at US Bank to my current role at Digineer there are so many people to mention that I’m afraid I’ll miss someone if I start listing people individually. 

Also… my ability to focus on anything today is gone.

I haven’t blogged much since getting back from vacation.  It seems like a good restart after the two week hiatus would be to address an issue that has arisen countless times.  And while addressing it, I’ll put it into the DBADiagnostics database that I’ve blogged about a few times before.

Sudden File Growth

In a number of SQL Server environments that I’ve worked in there either isn’t a method for monitoring file size or the process is more of a rubber stamp morning check.  The DBA gets in and looks at the size of the files and if there isn’t a log of changes to update nothing is really done and changes aren’t investigated.

In most cases this won’t be a problem.  For instance, if you’ve gone out and pre-grown your data and log files to appropriate sizes then nothing will grow and there is nothing to see here.

In cases were the unexpected happens, though, database files can and will grow.  And in the worst of these cases, which only occur at night or on vacations, the files will grow to a point where there is no longer any disk space available.  And if your annual review is next week, this will happen to the log file and force your database offline.

Knowing Is Half the Battle

In these types of situations, I like to recall the last couple minutes of the GI Joe epos ides that I watched as a kid.  They almost always ended with the quote, “Knowing is half the battle!  Yo, Joe!”.  If I know that a file growth has happened then I can do something about it.

Now the best case is to know, monitor, and plan for upcoming file growths.  This is what might be called a best practice and if you’re not doing it I’d really recommend putting a process in place.  But we need to be prepared for the unexpected.  And even in the most best, rock solid environment, I’d recommend a file growth monitoring process.

The Solution

This procedure for the DBADiagnostics database differs slightly from previous alerts.  Instead of sending out an e-mail that aggregates all of the changes an error is raised for each file change in question.  This method was selected because this allows the process to be tool agnostic.

If the client has a log file monitoring process, then the error can be picked up that way.  Or if all of the alerts are generated from the SQL Server instances, then a SQL Agent Alert can be created to notify people about the issue.

USE [DBADiagnostics]
GO

IF NOT EXISTS(SELECT * FROM sys.schemas WHERE name = 'Monitor')
    EXEC('CREATE SCHEMA [Monitor] AUTHORIZATION [dbo]')
GO

IF OBJECT_ID('Monitor.FileGrowth') IS NULL
BEGIN
    CREATE TABLE [Monitor].[FileGrowth]
        (
        DatabaseName sysname NOT NULL,
        DatabaseFileName nvarchar(260) NOT NULL,
        FileSizeMB decimal(18,3) NOT NULL
        CONSTRAINT PK_MonitorFileGrowth PRIMARY KEY(DatabaseName, DatabaseFileName)
        )
END

IF OBJECT_ID('Alert.FileGrowth') IS NOT NULL
    DROP PROCEDURE [Alert].[FileGrowth]
GO

/*================================================================================
Procedure:    [Alert].[FileGrowth] 
Author:        Jason Strate
Date:        2007-11-14

Synopsis:
    Procedures monitors the size of each file for all user databases and tempdb.  In
    the event of file growrh for any of the monitored databases, a error is raised
    that can be captured through either tools monitoring SQL Servers log files or 
    through SQL Agent Alerts.
         

================================================================================
Revision History:
Date:        By            Description
----------------------------------------------------------------------------------
================================================================================*/
CREATE PROCEDURE [Alert].[FileGrowth] 

As

SET NOCOUNT ON

-- Validate that necessary error message exists
IF NOT EXISTS (SELECT * FROM master..sysmessages WHERE error = 70000)
    EXEC master..sp_addmessage  @msgnum = 70000, @severity = 12, @with_log = 'true', 
        @msgtext = 'File growth has occured in the database %s on the file %s.  The size has increased from %d to %d.  If the file growth was unplanned, please review for unexpected issues.', @replace = 'REPLACE'

-- Insert files for each database that are not currently being watched
INSERT INTO [Monitor].[FileGrowth] (DatabaseName, DatabaseFileName, FileSizeM
SELECT d.name, mf.name, CAST(mf.size as float)*8/1024
FROM sys.databases d
    INNER JOIN sys.master_files mf ON d.database_id = mf.database_id
    LEFT OUTER JOIN Monitor.FileGrowth fg ON d.name = fg.DatabaseName AND mf.name = fg.DatabaseFileName
WHERE d.state_desc = 'ONLINE'
AND source_database_id IS NULL
AND d.name NOT IN ('model', 'master', 'msdb')
AND fg.FileSizeMB IS NULL

-- Delete databases that no longer exist
DELETE FROM Monitor.FileGrowth 
FROM Monitor.FileGrowth fg 
    LEFT OUTER JOIN sys.databases d ON fg.DatabaseName = d.name
WHERE d.name IS NULL

DECLARE @FileGrowthDelta table
    (
    DatabaseName sysname
    ,DatabaseFileName nvarchar(260) 
    ,OldFileSizeMB decimal(18,3)
    ,NewFileSizeMB decimal(18,3)
    )

-- Update files that changed size and output delta rows
UPDATE fg
SET FileSizeMB = CAST(CAST(mf.size as float)*8/1024 as decimal(18,3))
OUTPUT INSERTED.DatabaseName, INSERTED.DatabaseFileName, INSERTED.FileSizeMB, DELETED.FileSizeMB
INTO @FileGrowthDelta
FROM Monitor.FileGrowth fg 
    INNER JOIN sys.databases d ON d.name = fg.DatabaseName 
    INNER JOIN sys.master_files mf ON d.database_id = mf.database_id AND mf.name = fg.DatabaseFileName
WHERE d.state_desc = 'ONLINE'
AND source_database_id IS NULL
AND d.name NOT IN ('model', 'master', 'msdb')
AND FileSizeMB <> CAST(CAST(mf.size as float)*8/1024 as decimal(18,3))

--Declare variables section for triggering error event
DECLARE @DatabaseName sysname
    ,@DatabaseFileName nvarchar(260) 
    ,@OldFileSizeMB int
    ,@NewFileSizeMB int

--For each database name in sysdatabases
DECLARE ALTER_FILE_GROWTH_CURSOR CURSOR LOCAL FAST_FORWARD FOR 
    SELECT DatabaseName 
        ,DatabaseFileName 
        ,OldFileSizeMB 
        ,NewFileSizeMB
    FROM @FileGrowthDelta

OPEN ALTER_FILE_GROWTH_CURSOR
FETCH NEXT FROM ALTER_FILE_GROWTH_CURSOR INTO @DatabaseName, @DatabaseFileName, @OldFileSizeMB, @NewFileSizeMB

WHILE @@FETCH_STATUS = 0
BEGIN
    RAISERROR(70000, 0, 1, @DatabaseName, @DatabaseFileName, @OldFileSizeMB, @NewFileSizeM WITH LOG
    FETCH NEXT FROM ALTER_FILE_GROWTH_CURSOR INTO @DatabaseName, @DatabaseFileName, @OldFileSizeMB, @NewFileSizeMB    
END

CLOSE ALTER_FILE_GROWTH_CURSOR
DEALLOCATE ALTER_FILE_GROWTH_CURSOR
GO

EXEC [Alert].[FileGrowth]

In the next post, I’ll outline the process that I use to check to see if there are any files that may need be running out of available space.

I tweeted it a couple Friday’s ago when I got the notice but I was on vacation and had made the decision to avoid blogging anything during that time. 

But since vacation is over, here’s the session that got accepted:

Getting To Know Your Indexes

Without proper indexing SQL Server can be hard pressed to create efficient and performant execution plans.  Dynamic Management Views (DMV) and system views provide a slew of information about indexes that can be used to analyze indexes within SQL Server.  In this session we’ll go under the hood of SQL Server to look at DMVs and system views to know what indexes you have, should have, and how they feel about the way applications are treating them.

Prerequisites
Understanding of performance tuning needs
Experience with Database design

Goals:
1.    Identify methods to analyze current and potential indexes
2.    Learn how to alleviate stress found on indexes
3.    Demonstrate methods for tuning indexes

Have you registered for the PASS Summit yet?  If not and you need to convince someone, here are some good justifications for going.  My main motivation for going is the opportunity to meet new people in the community that have different visions on implementing SQL Server.  Also

The one last reason to go is project “jager”. 

If you’ve been looking for some free training on SQL Server Integration Services, I’ve got the answer for you.  The details are below and you can register for the event here.

Course Overview

ITCare, delivered and hosted quarterly by our Partners is designed to keep you up to date on the cutting-edge technologies. ITCare is a FREE half-day technical seminar series created by IT Professionals for IT Professionals! We'll address the needs and issues today and show you how to make a real difference in the way that you work.

SQL Server Integration Services (SSIS) offers a wide range of features and functionality that can be used to solve business pains within Extract, Transform, and Load (ETL) processes. These pains can be caused by the performance of current ETL process, the maintainability of ETL processes or represent a new pain that SSIS features can resolve. This presentation will provide a demonstration of real world scenarios where features of SSIS were used to solve business pains through modification of existing SSIS packages or through the creation of new SSIS packages. The session will also discuss current best practices in SSIS package design.

Objectives

1. Understand flexibility that SSIS features provide
2. Demonstrate application of SSIS functionality to business needs
3. Discuss current best practices in SSIS package design

Audience

IT Administrators, IT Managers, Business Intelligence, DBA, SQL Server administrators.

Prerequisites

Understanding of basic ETL concepts and basic T-SQL coding

Course Length

1 Half Day

Speaker: Jason Strate of Digineer, Inc.
Jason Strate has been a Database Architect for over ten years. His experiences include designing and implementing both OLAP and OLTP solutions involving clustering servers, database monitoring and tuning, analysis services, DTS/Integration Services, Reporting Services, and Notification Services. Jason is actively involved with the local PASS chapter (SQL Server User Group); he is currently serving on the Executive Board and has presented at monthly meetings. Jason contributed to Microsoft's published white paper "Empowering Enterprise Solutions with SQL Server 2005 Enterprise Edition and actively blogs about SQL Server and related technologies. Also this past year, Jason participated in the development of Microsoft Certification exams for SQL Server 2008 and presented at the SSWUG SQL Server Launch Event and the last two SSWUG Virtual SQL Server Conferences.

The weather says it’s supposed to be storming today.  What a better way to spend a rainy afternoon that in a conference room with your peers discussing SQL Server.  So come out at 2:30 PM for snacks and socializing and we’ll kick things off at 3:00 PM.

We’ll be hearing about Analysis Services Dimension Creation Best Practices & Disks, Real and Virtual and What is Important for SQL Server. 

Feel free to contact me via e-mail (jstrate@digineer.com) or twitter (@stratesql) with questions or for more information.

If you are still trying to make plans for tomorrow afternoon… there is a PASSMN meeting you could attend.  This month we’ll be hearing about Analysis Services Dimension Creation Best Practices & Disks, Real and Virtual and What is Important for SQL Server.  The meeting starts at 3:00 PM and there will some snacks and socializing before hand.

Feel free to contact me via e-mail (jstrate@digineer.com) or twitter (@stratesql) with questions or for more information.

Did you hear?  The call for speakers for the East Iowa SQL Saturday!

I’ve submitted four sessions and plan on driving down for the weekend.  As Michelle points out it’s only a 5-hour down to the event.  The event is free and will have a number of tracks which are still TBD but should be a good event.

Maybe if enough people from PASSMN head down there, they’ll join us when we try to put our event on.  Though our event is still pretty vapor.

  Some lines got crossed with this month’s New Horizons ITCare event.  The topic and speaker have changed but the date is remaining the same.  Instead of the previous topic, I’ll be presenting on SQL Server Integration Services.  As I mentioned, the date of the event is still June 24th.  The event will be available online and you can register for the event here.

Course Overview

ITCare, delivered and hosted quarterly by our Partners is designed to keep you up to date on the cutting-edge technologies. ITCare is a FREE half-day technical seminar series created by IT Professionals for IT Professionals! We'll address the needs and issues today and show you how to make a real difference in the way that you work.

SQL Server Integration Services (SSIS) offers a wide range of features and functionality that can be used to solve business pains within Extract, Transform, and Load (ETL) processes. These pains can be caused by the performance of current ETL process, the maintainability of ETL processes or represent a new pain that SSIS features can resolve. This presentation will provide a demonstration of real world scenarios where features of SSIS were used to solve business pains through modification of existing SSIS packages or through the creation of new SSIS packages. The session will also discuss current best practices in SSIS package design.

Objectives

1. Understand flexibility that SSIS features provide
2. Demonstrate application of SSIS functionality to business needs
3. Discuss current best practices in SSIS package design

Audience

IT Administrators, IT Managers, Business Intelligence, DBA, SQL Server administrators.

Prerequisites

Understanding of basic ETL concepts and basic T-SQL coding

Course Length

1 Half Day

Speaker: Jason Strate of Digineer, Inc.
Jason Strate has been a Database Architect for over ten years. His experiences include designing and implementing both OLAP and OLTP solutions involving clustering servers, database monitoring and tuning, analysis services, DTS/Integration Services, Reporting Services, and Notification Services. Jason is actively involved with the local PASS chapter (SQL Server User Group); he is currently serving on the Executive Board and has presented at monthly meetings. Jason contributed to Microsoft's published white paper "Empowering Enterprise Solutions with SQL Server 2005 Enterprise Edition and actively blogs about SQL Server and related technologies. Also this past year, Jason participated in the development of Microsoft Certification exams for SQL Server 2008 and presented at the SSWUG SQL Server Launch Event and the last two SSWUG Virtual SQL Server Conferences.

This event is a ways out there but thought I’d start getting the word out now for this free event.  The session will be hosted by yours truly and feature tricks and tips that I’ve picked up and want to spread the word on.

Here is the registration link.

And the details:

Course Overview

ITCare, delivered and hosted quarterly by our Partners is designed to keep you up to date on the cutting-edge technologies. ITCare is a FREE half-day technical seminar series created by IT Professionals for IT Professionals! We'll address the needs and issues today and show you how to make a real difference in the way that you work.

Objectives

While not everyone may not be a great painter, there is an art form to writing great SQL statements. In this session we'll go over tips and tricks in writing SQL. We'll also review some popular bad practices and provide demonstrations to better change those velvet Elvis's to Rembrandts. As every paint brush can't produce a masterpiece, the same goes for SQL statements. Writing good SQL code requires the right brush. After this session, you'll have a few more paintbrushes and probably throw out a few that are no longer useful.

1. Review and discussion of common coding methods can impact performance.
2. Discussion of uncommon coding methods that can be used to improve performance.
3. Review and application of existing T-SQL functions.

Audience

Junior to mid-level SQL Server developers

Prerequisites

Understanding of basic T-SQL coding skills.

Speaker: Jason Strate of Digineer, Inc. Jason Strate has been a Database Architect for over ten years. His experiences include designing and implementing both OLAP and OLTP solutions involving clustering servers, database monitoring and tuning, analysis services, DTS/Integration Services, Reporting Services, and Notification Services. Jason is actively involved with the local PASS chapter (SQL Server User Group); he is currently serving on the Executive Board and has presented at monthly meetings. Jason contributed to Microsoft's published white paper "Empowering Enterprise Solutions with SQL Server 2005 Enterprise Edition and actively blogs about SQL Server and related technologies. Also this past year, Jason participated in the development of Microsoft Certification exams for SQL Server 2008 and presented at the SSWUG SQL Server Launch Event and the last two SSWUG Virtual SQL Server Conferences.

Today’s stored procedure for the DBADiagnostics database is a re-hash of a post I did a couple months ago.  The reason for re-hashing the procedure is that when I used it last week I found out there were some serious issues with its results.  Some plans were being duplicated and the performance statistics weren’t accurate because of this.  And why not fix the issues and make a DBADiagnostics post out of it.

In the previous version, I was using the execution count from sys.dm_exec_query_stats to determine how often a plan was being executed.  In the DMV though the SQL statements in the may execute in varying counts depending on conditional logic.

Instead of using the execution count from sys.dm_exec_query_stats the procedure now uses usecount from sys.dm_exec_cached_plans; which represents how often the plan was used.  The execution count represented how often the statements in the plan were executed.

The stored procedure accepts the following parameters:

• @Database: The database to search for plans within.  This value can be NULL.
• @ObjectName: The name of the procedure to search for plans for.  This value can be NULL.

The procedure can use either or none of the variables.  Obviously with both variables empty the results will be for all cached plans on the server.

USE [DBADiagnostics]
GO

IF NOT EXISTS(SELECT * FROM sys.schemas WHERE name = 'Utility')
    EXEC('CREATE SCHEMA [Utility] AUTHORIZATION [dbo]')
GO

IF EXISTS(SELECT * FROM sys.procedures WHERE object_id = OBJECT_ID('Utility.CachedPlanSearch'))
    DROP PROCEDURE [Utility].[CachedPlanSearch]
GO

/*============================================================
Procedure:    [Utility].[CachedPlanSearch]
Author:       Jason Strate
Date:         June 1, 2009
 
Synopsis:
    Searches cache for all occurances of a plan based on an
    procedure and or database name.  Results assumes that average
    execution time for returned results are less than 24 hours.

Exec Utility.CachedPlanSearch
    @DatabaseName = 'msdb'
    ,@ObjectName = 'sp_sqlagent_get_startup_info'
 
============================================================
Revision History:
Date:         By                Description
------------------------------------------------------------
 
============================================================*/
CREATE PROCEDURE Utility.CachedPlanSearch
    (
    @DatabaseName sysname = NULL
    ,@ObjectName sysname = NULL
    )
AS

;WITH cteExecInfo
AS ( 
    SELECT DB_NAME(st.dbid) AS database_name 
        ,OBJECT_NAME(st.objectid, st.dbid) AS object_name 
        ,cp.usecounts -- Use in place of qs.execution_count for whole plan count
        ,CAST(SUM(qs.total_worker_time)/(cp.usecounts*1.) as decimal(12,2)) AS avg_cpu_time 
        ,CAST(SUM(qs.total_logical_reads + qs.total_logical_writes)/(cp.usecounts*1.) as decimal(12,2)) AS avg_io 
        ,SUM(qs.total_elapsed_time)/(cp.usecounts)/1000 as avg_elapsed_time_ms
        ,st.text AS sql_text
        ,qs.plan_handle 
    FROM sys.dm_exec_query_stats qs 
        INNER JOIN sys.dm_exec_cached_plans cp ON qs.plan_handle = cp.plan_handle
        CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st 
    WHERE (DB_NAME(st.dbid) = @DatabaseName OR NULLIF(@DatabaseName,'') IS NULL)
    AND (OBJECT_NAME(st.objectid, st.dbid) = @ObjectName OR NULLIF(@ObjectName, '') IS NULL)
    GROUP BY st.dbid, st.objectid, cp.usecounts, st.text, qs.plan_handle 
) 
SELECT cte.database_name 
    ,cte.object_name 
    ,cte.usecounts 
    ,cte.avg_cpu_time 
    ,cte.avg_io 
    ,CONVERT(varchar, DATEADD(ms, cte.avg_elapsed_time_ms, 0), 114) AS avg_elapsed_time
    ,qp.query_plan 
    ,cte.sql_text
FROM cteExecInfo cte 
    OUTER APPLY sys.dm_exec_query_plan(cte.plan_handle) qp 
ORDER BY cte.usecounts DESC

I’ve often found this procedure useful when people complain about the execution of a procedure.  Instead of pulling out performance tools and running test versions of the procedure this will provide the last plan that was used for the procedure.