Wednesday, September 5, 2012

SSIS 2012 Deployment Frustrations?



I went to deploy a large SSIS project recently and received this error from the Deployment Wizard:

Failed to deploy project. For more information, query the operation_messages view for the operation identifier ’219′. (Microsoft SQL Server, Error: 27203)

Hoping that I would find out more I queried the catalog.operation_messages table in the SSISDB database.


The message column reads as follows:
Failed to deploy the project. Fix the problems and try again later.:Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
The statement has been terminated.

As the person deploying the project, I can’t “help” the Deployment Wizard with tweaking any queries, but I could see what it was doing using SQL Profiler.  The query that caught my attention is the following:
exec [internal].[sync_parameter_versions] @project_id=2,@object_version_lsn=10

Notice the number of reads(1.7M) and the duration(30 seconds).  I acquired the query plan for this stored procedure and found that it would run much faster if the following two indexes are applied to the SSISDB database.

USE
 [SSISDB]
GO

CREATE NONCLUSTERED INDEX [ix1_internal_object_parameters_inc] ON [internal].[object_parameters]
(
        [project_id] ASC ,
        [project_version_lsn] ASC
)
INCLUDE (      [parameter_id],
        [object_type],
        [object_name],
        [parameter_name],
        [parameter_data_type],
        [required],
        [sensitive]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF , ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [ix2_internal_object_parameters_inc] ON [internal].[object_parameters]
(
        [project_id] ASC ,
        [project_version_lsn] ASC ,
        [object_type] ASC ,
        [object_name] ASC ,
        [parameter_data_type] ASC ,
        [required] ASC ,
        [sensitive] ASC
)
INCLUDE (      [parameter_name],
        [default_value],
        [sensitive_default_value],
        [value_type],
        [value_set],
        [referenced_variable_name]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF , ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO


After these indexes were applied the deployment wizard had no more issues.

Wednesday, August 8, 2012

Webpage cache warmer with Powershell


Have you ever needed to cache web pages after a web server reboot?  Here is a great little PowerShell script that can be added to the start-up script of a server.  This is also great for getting SharePoint pages cached after a reboot.

##############################################################
##############################################################
##############################################################
#
#     Open a list of webpages to warm the web server cache
#
##############################################################

# Created Date: 8/6/2012
# Created By: Jim Bennett

##############################################################
##############################################################
##############################################################


#Variables

#Enter a comma separated list of webpages to hit
$WebpageList = "http://www.google.com","http://www.capstonebi.com"


$WebpageList | ForEach-Object {
      Write-Host "Opening page: $_" -ForegroundColor Yellow -nonewline
      $temp = [System.Math]::Round((Measure-Command {(new-object net.webclient).DownloadString($_)}).totalmilliseconds)
      Write-Host " (Opened in $temp milliseconds)" -ForegroundColor Yellow
}