Monday, January 08, 2018

Remove SQL 2012 performance dashboard

Microsoft in a recent update to SSMS put the Performance Dashboard directly into SSMS so you no longer need to manually install it on remote servers. Microsoft hasn't included a script to remove it, the easiest way to remove it is to manually edit the original script and rerun it

 Lucky for you I can save you 5 minutes of editing


USE [MSDB]
if OBJECTPROPERTY(object_id('MS_PerfDashboard.tblConfigValues'), 'IsUserTable') = 1drop table MS_PerfDashboard.tblConfigValuesgo
if object_id('MS_PerfDashboard.usp_CheckDependencies', 'P') is not null drop procedure MS_PerfDashboard.usp_CheckDependenciesgo
if not exists (select * from sys.schemas where name = 'MS_PerfDashboard') exec('create schema MS_PerfDashboard')go
if OBJECTPROPERTY(object_id('MS_PerfDashboard.fn_WaitTypeCategory'), 'IsScalarFunction') = 1 drop function MS_PerfDashboard.fn_WaitTypeCategorygo
if OBJECTPROPERTY(object_id('MS_PerfDashboard.fn_QueryTextFromHandle'), 'IsTableFunction') = 1 drop function MS_PerfDashboard.fn_QueryTextFromHandlego
if OBJECTPROPERTY(object_id('MS_PerfDashboard.fn_hexstrtovarbin'), 'IsScalarFunction') = 1 drop function MS_PerfDashboard.fn_hexstrtovarbingo
if OBJECTPROPERTY(object_id('MS_PerfDashboard.fn_DatediffMilliseconds'), 'IsScalarFunction') = 1 drop function MS_PerfDashboard.fn_DatediffMillisecondsgo

if object_id('MS_PerfDashboard.usp_Main_GetCPUHistory', 'P') is not null drop procedure MS_PerfDashboard.usp_Main_GetCPUHistorygo

if object_id('MS_PerfDashboard.usp_Main_GetMiscInfo', 'P') is not null drop procedure MS_PerfDashboard.usp_Main_GetMiscInfogo
if object_id('MS_PerfDashboard.usp_Main_GetSessionInfo', 'P') is not null drop procedure MS_PerfDashboard.usp_Main_GetSessionInfogo
if object_id('MS_PerfDashboard.usp_Main_GetRequestInfo', 'P') is not null drop procedure MS_PerfDashboard.usp_Main_GetRequestInfogo
if object_id('MS_PerfDashboard.usp_Main_GetRequestWaits', 'P') is not null drop procedure MS_PerfDashboard.usp_Main_GetRequestWaitsgo
if object_id('MS_PerfDashboard.usp_GetPageDetails', 'P') is not null drop procedure MS_PerfDashboard.usp_GetPageDetailsgo
if OBJECTPROPERTY(object_id('MS_PerfDashboard.usp_GetPlanGuideDetails'), 'IsProcedure') = 1 drop procedure MS_PerfDashboard.usp_GetPlanGuideDetailsgo
if OBJECTPROPERTY(object_id('MS_PerfDashboard.usp_TransformShowplanXMLToTable'), 'IsProcedure') = 1 drop procedure MS_PerfDashboard.usp_TransformShowplanXMLToTablego
if OBJECTPROPERTY(object_id('MS_PerfDashboard.fn_ShowplanBuildColumnReference'), 'IsScalarFunction') = 1 drop function MS_PerfDashboard.fn_ShowplanBuildColumnReferencego
if OBJECTPROPERTY(object_id('MS_PerfDashboard.fn_ShowplanBuildColumnReferenceList'), 'IsScalarFunction') = 1 drop function MS_PerfDashboard.fn_ShowplanBuildColumnReferenceListgo
if OBJECTPROPERTY(object_id('MS_PerfDashboard.fn_ShowplanBuildDefinedValuesList'), 'IsScalarFunction') = 1 drop function MS_PerfDashboard.fn_ShowplanBuildDefinedValuesListgo
if OBJECTPROPERTY(object_id('MS_PerfDashboard.fn_ShowplanBuildOrderBy'), 'IsScalarFunction') = 1 drop function MS_PerfDashboard.fn_ShowplanBuildOrderBygo
if OBJECTPROPERTY(object_id('MS_PerfDashboard.fn_ShowplanBuildRowset'), 'IsScalarFunction') = 1 drop function MS_PerfDashboard.fn_ShowplanBuildRowsetgo
if OBJECTPROPERTY(object_id('MS_PerfDashboard.fn_ShowplanBuildScalarExpression'), 'IsScalarFunction') = 1 drop function MS_PerfDashboard.fn_ShowplanBuildScalarExpressiongo
if OBJECTPROPERTY(object_id('MS_PerfDashboard.fn_ShowplanBuildScalarExpressionList'), 'IsScalarFunction') = 1 drop function MS_PerfDashboard.fn_ShowplanBuildScalarExpressionListgo
if OBJECTPROPERTY(object_id('MS_PerfDashboard.fn_ShowplanBuildScanRange'), 'IsScalarFunction') = 1 drop function MS_PerfDashboard.fn_ShowplanBuildScanRangego
if OBJECTPROPERTY(object_id('MS_PerfDashboard.fn_ShowplanBuildSeekPredicates'), 'IsScalarFunction') = 1 drop function MS_PerfDashboard.fn_ShowplanBuildSeekPredicatesgo

if OBJECTPROPERTY(object_id('MS_PerfDashboard.fn_ShowplanBuildSeekPredicatesNew'), 'IsScalarFunction') = 1 drop function MS_PerfDashboard.fn_ShowplanBuildSeekPredicatesNewgo
if OBJECTPROPERTY(object_id('MS_PerfDashboard.fn_ShowplanBuildSeekPredicate'), 'IsScalarFunction') = 1 drop function MS_PerfDashboard.fn_ShowplanBuildSeekPredicatego
if OBJECTPROPERTY(object_id('MS_PerfDashboard.fn_ShowplanBuildObject'), 'IsScalarFunction') = 1 drop function MS_PerfDashboard.fn_ShowplanBuildObjectgo
if OBJECTPROPERTY(object_id('MS_PerfDashboard.fn_ShowplanBuildWarnings'), 'IsScalarFunction') = 1 drop function MS_PerfDashboard.fn_ShowplanBuildWarningsgo
if OBJECTPROPERTY(object_id('MS_PerfDashboard.fn_ShowplanFormatAssert'), 'IsScalarFunction') = 1 drop function MS_PerfDashboard.fn_ShowplanFormatAssertgo
if OBJECTPROPERTY(object_id('MS_PerfDashboard.fn_ShowplanFormatBitmap'), 'IsScalarFunction') = 1 drop function MS_PerfDashboard.fn_ShowplanFormatBitmapgo
if OBJECTPROPERTY(object_id('MS_PerfDashboard.fn_ShowplanFormatComputeScalar'), 'IsScalarFunction') = 1 drop function MS_PerfDashboard.fn_ShowplanFormatComputeScalargo
if OBJECTPROPERTY(object_id('MS_PerfDashboard.fn_ShowplanFormatConcat'), 'IsScalarFunction') = 1 drop function MS_PerfDashboard.fn_ShowplanFormatConcatgo

if OBJECTPROPERTY(object_id('MS_PerfDashboard.fn_ShowplanFormatCollapse'), 'IsScalarFunction') = 1 drop function MS_PerfDashboard.fn_ShowplanFormatCollapsego
if OBJECTPROPERTY(object_id('MS_PerfDashboard.fn_ShowplanFormatConstantScan'), 'IsScalarFunction') = 1 drop function MS_PerfDashboard.fn_ShowplanFormatConstantScango
if OBJECTPROPERTY(object_id('MS_PerfDashboard.fn_ShowplanFormatDeletedInsertedScan'), 'IsScalarFunction') = 1 drop function MS_PerfDashboard.fn_ShowplanFormatDeletedInsertedScango
if OBJECTPROPERTY(object_id('MS_PerfDashboard.fn_ShowplanFormatFilter'), 'IsScalarFunction') = 1 drop function MS_PerfDashboard.fn_ShowplanFormatFiltergo
if OBJECTPROPERTY(object_id('MS_PerfDashboard.fn_ShowplanFormatHashMatch'), 'IsScalarFunction') = 1 drop function MS_PerfDashboard.fn_ShowplanFormatHashMatchgo
if OBJECTPROPERTY(object_id('MS_PerfDashboard.fn_ShowplanFormatMerge'), 'IsScalarFunction') = 1 drop function MS_PerfDashboard.fn_ShowplanFormatMergego

if OBJECTPROPERTY(object_id('MS_PerfDashboard.fn_ShowplanFormatNestedLoops'), 'IsScalarFunction') = 1 drop function MS_PerfDashboard.fn_ShowplanFormatNestedLoopsgo

if OBJECTPROPERTY(object_id('MS_PerfDashboard.fn_ShowplanFormatParallelism'), 'IsScalarFunction') = 1 drop function MS_PerfDashboard.fn_ShowplanFormatParallelismgo
if OBJECTPROPERTY(object_id('MS_PerfDashboard.fn_ShowplanFormatSimpleUpdate'), 'IsScalarFunction') = 1 drop function MS_PerfDashboard.fn_ShowplanFormatSimpleUpdatego
if OBJECTPROPERTY(object_id('MS_PerfDashboard.fn_ShowplanFormatRemoteQuery'), 'IsScalarFunction') = 1 drop function MS_PerfDashboard.fn_ShowplanFormatRemoteQuerygo
if OBJECTPROPERTY(object_id('MS_PerfDashboard.fn_ShowplanFormatRemoteScan'), 'IsScalarFunction') = 1 drop function MS_PerfDashboard.fn_ShowplanFormatRemoteScango
if OBJECTPROPERTY(object_id('MS_PerfDashboard.fn_ShowplanFormatRemoteModify'), 'IsScalarFunction') = 1 drop function MS_PerfDashboard.fn_ShowplanFormatRemoteModifygo
if OBJECTPROPERTY(object_id('MS_PerfDashboard.fn_ShowplanFormatSort'), 'IsScalarFunction') = 1 drop function MS_PerfDashboard.fn_ShowplanFormatSortgo
if OBJECTPROPERTY(object_id('MS_PerfDashboard.fn_ShowplanFormatSplit'), 'IsScalarFunction') = 1 drop function MS_PerfDashboard.fn_ShowplanFormatSplitgo
if OBJECTPROPERTY(object_id('MS_PerfDashboard.fn_ShowplanFormatStreamAggregate'), 'IsScalarFunction') = 1 drop function MS_PerfDashboard.fn_ShowplanFormatStreamAggregatego
if OBJECTPROPERTY(object_id('MS_PerfDashboard.fn_ShowplanFormatSegment'), 'IsScalarFunction') = 1 drop function MS_PerfDashboard.fn_ShowplanFormatSegmentgo
if OBJECTPROPERTY(object_id('MS_PerfDashboard.fn_ShowplanFormatSpool'), 'IsScalarFunction') = 1 drop function MS_PerfDashboard.fn_ShowplanFormatSpoolgo
if OBJECTPROPERTY(object_id('MS_PerfDashboard.fn_ShowplanFormatTableScan'), 'IsScalarFunction') = 1 drop function MS_PerfDashboard.fn_ShowplanFormatTableScango
if OBJECTPROPERTY(object_id('MS_PerfDashboard.fn_ShowplanFormatTop'), 'IsScalarFunction') = 1 drop function MS_PerfDashboard.fn_ShowplanFormatTopgo
if OBJECTPROPERTY(object_id('MS_PerfDashboard.fn_ShowplanFormatTVF'), 'IsScalarFunction') = 1 drop function MS_PerfDashboard.fn_ShowplanFormatTVFgo

if OBJECTPROPERTY(object_id('MS_PerfDashboard.fn_ShowplanFormatUDX'), 'IsScalarFunction') = 1 drop function MS_PerfDashboard.fn_ShowplanFormatUDXgo
if OBJECTPROPERTY(object_id('MS_PerfDashboard.fn_ShowplanFormatUpdate'), 'IsScalarFunction') = 1 drop function MS_PerfDashboard.fn_ShowplanFormatUpdatego
if OBJECTPROPERTY(object_id('MS_PerfDashboard.fn_ShowplanFormatRIDLookup'), 'IsScalarFunction') = 1 drop function MS_PerfDashboard.fn_ShowplanFormatRIDLookupgo
if OBJECTPROPERTY(object_id('MS_PerfDashboard.fn_ShowplanFormatGenericUpdate'), 'IsScalarFunction') = 1 drop function MS_PerfDashboard.fn_ShowplanFormatGenericUpdatego
if OBJECTPROPERTY(object_id('MS_PerfDashboard.fn_ShowplanRowDetails'), 'IsTableFunction') = 1 drop function MS_PerfDashboard.fn_ShowplanRowDetailsgo
if object_id('MS_PerfDashboard.usp_DatabaseOverview', 'P') is not null drop procedure MS_PerfDashboard.usp_DatabaseOverviewgo

if object_id('MS_PerfDashboard.usp_LargeIOObjects', 'P') is not null drop procedure MS_PerfDashboard.usp_LargeIOObjectsgo
if object_id('MS_PerfDashboard.usp_DBFileIO', 'P') is not null drop procedure MS_PerfDashboard.usp_DBFileIOgo
if object_id('MS_PerfDashboard.usp_DmOsWaitStats', 'P') is not null drop procedure MS_PerfDashboard.usp_DmOsWaitStatsgo
if object_id('MS_PerfDashboard.usp_MissingIndexes', 'P') is not null drop procedure MS_PerfDashboard.usp_MissingIndexesgo

if object_id('MS_PerfDashboard.usp_QueryText', 'P') is not null drop procedure MS_PerfDashboard.usp_QueryTextgo
if object_id('MS_PerfDashboard.usp_MissingIndexStats', 'P') is not null drop procedure MS_PerfDashboard.usp_MissingIndexStatsgo
if object_id('MS_PerfDashboard.usp_QueryAttributes', 'P') is not null drop procedure MS_PerfDashboard.usp_QueryAttributesgo
if object_id('MS_PerfDashboard.usp_ShowplanAttributes', 'P') is not null drop procedure MS_PerfDashboard.usp_ShowplanAttributesgo
if object_id('MS_PerfDashboard.usp_PlanParameters', 'P') is not null drop procedure MS_PerfDashboard.usp_PlanParametersgo
if object_id('MS_PerfDashboard.usp_QueryStatsTopN', 'P') is not null drop procedure MS_PerfDashboard.usp_QueryStatsTopNgo
if object_id('MS_PerfDashboard.usp_QueryStatsTopN1', 'P') is not null drop procedure MS_PerfDashboard.usp_QueryStatsTopN1go
if object_id('MS_PerfDashboard.usp_QueryStatsRecentActivity', 'P') is not null drop procedure MS_PerfDashboard.usp_QueryStatsRecentActivitygo
if object_id('MS_PerfDashboard.usp_SessionRequestActivity', 'P') is not null drop procedure MS_PerfDashboard.usp_SessionRequestActivitygo
if object_id('MS_PerfDashboard.usp_RequestDetails', 'P') is not null drop procedure MS_PerfDashboard.usp_RequestDetailsgo
if object_id('MS_PerfDashboard.usp_SessionData', 'P') is not null drop procedure MS_PerfDashboard.usp_SessionDatago
if object_id('MS_PerfDashboard.usp_SessionRequests', 'P') is not null drop procedure MS_PerfDashboard.usp_SessionRequestsgo
if object_id('MS_PerfDashboard.usp_LastBatchForIdleSession', 'P') is not null drop procedure MS_PerfDashboard.usp_LastBatchForIdleSessiongo
if object_id('MS_PerfDashboard.usp_SessionDetails', 'P') is not null drop procedure MS_PerfDashboard.usp_SessionDetailsgo
if object_id('MS_PerfDashboard.usp_TraceEventColumns', 'P') is not null drop procedure MS_PerfDashboard.usp_TraceEventColumnsgo
if object_id('MS_PerfDashboard.usp_Blocking', 'P') is not null drop procedure MS_PerfDashboard.usp_Blockinggo
if object_id('MS_PerfDashboard.usp_RequestIoWaits', 'P') is not null drop procedure MS_PerfDashboard.usp_RequestIoWaitsgo
if object_id('MS_PerfDashboard.usp_LargestIoRequests', 'P') is not null drop procedure MS_PerfDashboard.usp_LargestIoRequestsgo
if object_id('MS_PerfDashboard.usp_RequestWaits', 'P') is not null drop procedure MS_PerfDashboard.usp_RequestWaitsgo
if object_id('MS_PerfDashboard.usp_LatchStats', 'P') is not null drop procedure MS_PerfDashboard.usp_LatchStatsgo
if object_id('MS_PerfDashboard.usp_RequestsWithLatchWaits', 'P') is not null drop procedure MS_PerfDashboard.usp_RequestsWithLatchWaitsgo
if object_id('MS_PerfDashboard.usp_XEventSessions', 'P') is not null drop procedure MS_PerfDashboard.usp_XEventSessionsgo
if object_id('MS_PerfDashboard.usp_QueryStatsDetails ', 'P') is not null drop procedure MS_PerfDashboard.usp_QueryStatsDetails go





If you find this article useful please leave me a comment.

No comments :

Post a comment