Thursday, March 31, 2011
Monday, March 28, 2011
function to create a random string
set ANSI_NULLS ON set QUOTED_IDENTIFIER ON
go
create view RandomHelper as select rand( ) as r
GO
CREATE function [dbo].[CreateRandomString]
(
@passwordLength as smallint
)
RETURNS varchar(100)
AS
Begin
DECLARE @password varchar(100)
declare @characters varchar(100)
declare @count int set @characters = ''
-- load up numbers 0 - 9
set @count = 48
while @count <=57
begin
set @characters = @characters + Cast(CHAR(@count) as char(1))
set @count = @count + 1 end
-- load up uppercase letters A - Z
set @count = 65
while @count <=90
begin
set @characters = @characters + Cast(CHAR(@count) as char(1))
set @count = @count + 1 end
-- load up lowercase letters a - z
set @count = 97
while @count <=122
begin
set @characters = @characters + Cast(CHAR(@count) as char(1))
set @count = @count + 1
end
set @count = 0
set @password = ''
while @count < @passwordLength
begin
set @password = @password + SUBSTRING(@characters,CAST((SELECT r FROM RandomHelper)*LEN(@characters) as int)+1,1)
set @count = @count + 1
end
RETURN @password
end
GO
SELECT dbo.CreateRandomString(10)
go
go
create view RandomHelper as select rand( ) as r
GO
CREATE function [dbo].[CreateRandomString]
(
@passwordLength as smallint
)
RETURNS varchar(100)
AS
Begin
DECLARE @password varchar(100)
declare @characters varchar(100)
declare @count int set @characters = ''
-- load up numbers 0 - 9
set @count = 48
while @count <=57
begin
set @characters = @characters + Cast(CHAR(@count) as char(1))
set @count = @count + 1 end
-- load up uppercase letters A - Z
set @count = 65
while @count <=90
begin
set @characters = @characters + Cast(CHAR(@count) as char(1))
set @count = @count + 1 end
-- load up lowercase letters a - z
set @count = 97
while @count <=122
begin
set @characters = @characters + Cast(CHAR(@count) as char(1))
set @count = @count + 1
end
set @count = 0
set @password = ''
while @count < @passwordLength
begin
set @password = @password + SUBSTRING(@characters,CAST((SELECT r FROM RandomHelper)*LEN(@characters) as int)+1,1)
set @count = @count + 1
end
RETURN @password
end
GO
SELECT dbo.CreateRandomString(10)
go
Friday, March 25, 2011
The Rambling DBA: Jonathan Kehayias : Digging into the SQL Plan Cache: Finding Missing Indexes
The Rambling DBA: Jonathan Kehayias : Digging into the SQL Plan Cache: Finding Missing Indexes
WITH XMLNAMESPACES
(DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT query_plan,
n.value('(@StatementText)[1]', 'VARCHAR(4000)') AS sql_text,
n.value('(//MissingIndexGroup/@Impact)[1]', 'FLOAT') AS impact,
DB_ID(REPLACE(REPLACE(n.value('(//MissingIndex/@Database)[1]', 'VARCHAR(128)'),'[',''),']','')) AS database_id,
OBJECT_ID(n.value('(//MissingIndex/@Database)[1]', 'VARCHAR(128)') + '.' +
n.value('(//MissingIndex/@Schema)[1]', 'VARCHAR(128)') + '.' +
n.value('(//MissingIndex/@Table)[1]', 'VARCHAR(128)')) AS OBJECT_ID,
n.value('(//MissingIndex/@Database)[1]', 'VARCHAR(128)') + '.' +
n.value('(//MissingIndex/@Schema)[1]', 'VARCHAR(128)') + '.' +
n.value('(//MissingIndex/@Table)[1]', 'VARCHAR(128)')
AS statement,
( SELECT DISTINCT c.value('(@Name)[1]', 'VARCHAR(128)') + ', '
FROM n.nodes('//ColumnGroup') AS t(cg)
CROSS APPLY cg.nodes('Column') AS r(c)
WHERE cg.value('(@Usage)[1]', 'VARCHAR(128)') = 'EQUALITY'
FOR XML PATH('')
) AS equality_columns,
( SELECT DISTINCT c.value('(@Name)[1]', 'VARCHAR(128)') + ', '
FROM n.nodes('//ColumnGroup') AS t(cg)
CROSS APPLY cg.nodes('Column') AS r(c)
WHERE cg.value('(@Usage)[1]', 'VARCHAR(128)') = 'INEQUALITY'
FOR XML PATH('')
) AS inequality_columns,
( SELECT DISTINCT c.value('(@Name)[1]', 'VARCHAR(128)') + ', '
FROM n.nodes('//ColumnGroup') AS t(cg)
CROSS APPLY cg.nodes('Column') AS r(c)
WHERE cg.value('(@Usage)[1]', 'VARCHAR(128)') = 'INCLUDE'
FOR XML PATH('')
) AS include_columns
INTO #MissingIndexInfo
FROM
(
SELECT query_plan
FROM (
SELECT DISTINCT plan_handle
FROM sys.dm_exec_query_stats WITH(NOLOCK)
) AS qs
OUTER APPLY sys.dm_exec_query_plan(qs.plan_handle) tp
WHERE tp.query_plan.exist('//MissingIndex')=1
) AS tab (query_plan)
CROSS APPLY query_plan.nodes('//StmtSimple') AS q(n)
WHERE n.exist('QueryPlan/MissingIndexes') = 1
-- Trim trailing comma from lists
UPDATE #MissingIndexInfo
SET equality_columns = LEFT(equality_columns,LEN(equality_columns)-1),
inequality_columns = LEFT(inequality_columns,LEN(inequality_columns)-1),
include_columns = LEFT(include_columns,LEN(include_columns)-1)
SELECT *
FROM #MissingIndexInfo
DROP TABLE #MissingIndexInfo
WITH XMLNAMESPACES
(DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT query_plan,
n.value('(@StatementText)[1]', 'VARCHAR(4000)') AS sql_text,
n.value('(//MissingIndexGroup/@Impact)[1]', 'FLOAT') AS impact,
DB_ID(REPLACE(REPLACE(n.value('(//MissingIndex/@Database)[1]', 'VARCHAR(128)'),'[',''),']','')) AS database_id,
OBJECT_ID(n.value('(//MissingIndex/@Database)[1]', 'VARCHAR(128)') + '.' +
n.value('(//MissingIndex/@Schema)[1]', 'VARCHAR(128)') + '.' +
n.value('(//MissingIndex/@Table)[1]', 'VARCHAR(128)')) AS OBJECT_ID,
n.value('(//MissingIndex/@Database)[1]', 'VARCHAR(128)') + '.' +
n.value('(//MissingIndex/@Schema)[1]', 'VARCHAR(128)') + '.' +
n.value('(//MissingIndex/@Table)[1]', 'VARCHAR(128)')
AS statement,
( SELECT DISTINCT c.value('(@Name)[1]', 'VARCHAR(128)') + ', '
FROM n.nodes('//ColumnGroup') AS t(cg)
CROSS APPLY cg.nodes('Column') AS r(c)
WHERE cg.value('(@Usage)[1]', 'VARCHAR(128)') = 'EQUALITY'
FOR XML PATH('')
) AS equality_columns,
( SELECT DISTINCT c.value('(@Name)[1]', 'VARCHAR(128)') + ', '
FROM n.nodes('//ColumnGroup') AS t(cg)
CROSS APPLY cg.nodes('Column') AS r(c)
WHERE cg.value('(@Usage)[1]', 'VARCHAR(128)') = 'INEQUALITY'
FOR XML PATH('')
) AS inequality_columns,
( SELECT DISTINCT c.value('(@Name)[1]', 'VARCHAR(128)') + ', '
FROM n.nodes('//ColumnGroup') AS t(cg)
CROSS APPLY cg.nodes('Column') AS r(c)
WHERE cg.value('(@Usage)[1]', 'VARCHAR(128)') = 'INCLUDE'
FOR XML PATH('')
) AS include_columns
INTO #MissingIndexInfo
FROM
(
SELECT query_plan
FROM (
SELECT DISTINCT plan_handle
FROM sys.dm_exec_query_stats WITH(NOLOCK)
) AS qs
OUTER APPLY sys.dm_exec_query_plan(qs.plan_handle) tp
WHERE tp.query_plan.exist('//MissingIndex')=1
) AS tab (query_plan)
CROSS APPLY query_plan.nodes('//StmtSimple') AS q(n)
WHERE n.exist('QueryPlan/MissingIndexes') = 1
-- Trim trailing comma from lists
UPDATE #MissingIndexInfo
SET equality_columns = LEFT(equality_columns,LEN(equality_columns)-1),
inequality_columns = LEFT(inequality_columns,LEN(inequality_columns)-1),
include_columns = LEFT(include_columns,LEN(include_columns)-1)
SELECT *
FROM #MissingIndexInfo
DROP TABLE #MissingIndexInfo
Find Missing Indexes in Stored Procs with T-SQL « SQL Fool
Find Missing Indexes in Stored Procs with T-SQL « SQL Fool
use master
go
/* Create a stored procedure skeleton */
IF OBJECTPROPERTY(OBJECT_ID('dbo.dba_missingIndexStoredProc_sp'), N'IsProcedure') IS Null
BEGIN
EXECUTE ('Create Procedure dbo.dba_missingIndexStoredProc_sp As Print ''Hello World!''')
RAISERROR('Procedure dba_missingIndexStoredProc_sp created.', 10, 1);
END;
Go
/* Drop our table if it already exists */
IF Exists(SELECT OBJECT_ID FROM sys.tables WHERE [name] = N'dba_missingIndexStoredProc')
BEGIN
DROP TABLE dbo.dba_missingIndexStoredProc
PRINT 'dba_missingIndexStoredProc table dropped!';
END
/* Create our table */
CREATE TABLE dbo.dba_missingIndexStoredProc
(
missingIndexSP_id INT IDENTITY(1,1) Not Null
, databaseName VARCHAR(128) Not Null
, databaseID INT Not Null
, objectName VARCHAR(128) Not Null
, objectID INT Not Null
, query_plan xml Not Null
, executionDate SMALLDATETIME Not Null
CONSTRAINT PK_missingIndexStoredProc
PRIMARY KEY CLUSTERED(missingIndexSP_id)
);
PRINT 'dba_missingIndexStoredProc Table Created';
/* Configure our settings */
SET ANSI_Nulls ON;
SET Quoted_Identifier ON;
Go
ALTER PROCEDURE dbo.dba_missingIndexStoredProc_sp
/* Declare Parameters */
@lastExecuted_inDays INT = 7
, @minExecutionCount INT = 7
, @logResults BIT = 1
, @displayResults BIT = 0
AS
/*********************************************************************************
Name: dba_missingIndexStoredProc_sp
Author: Michelle Ufford, http://sqlfool.com
Purpose: Retrieves stored procedures with missing indexes in their
cached query plans.
@lastExecuted_inDays = number of days old the cached query plan
can be to still appear in the results;
the HIGHER the number, the longer the
execution time.
@minExecutionCount = minimum number of executions the cached
query plan can have to still appear
in the results; the LOWER the number,
the longer the execution time.
@logResults = store results in dba_missingIndexStoredProc
@displayResults = return results to the caller
Notes: This is not 100% guaranteed to catch all missing indexes in
a stored procedure. It will only catch it if the stored proc's
query plan is still in cache. Run regularly to help minimize
the chance of missing a proc.
Called by: DBA and/or SQL Agent Job
Date User Description
----------------------------------------------------------------------------
2009-03-02 MFU Initial Release for public consumption
*********************************************************************************
Exec dbo.dba_missingIndexStoredProc_sp
@lastExecuted_inDays = 30
, @minExecutionCount = 5
, @logResults = 1
, @displayResults = 1;
*********************************************************************************/
SET NOCOUNT ON;
SET XACT_Abort ON;
SET Ansi_Padding ON;
SET Ansi_Warnings ON;
SET ArithAbort ON;
SET Concat_Null_Yields_Null ON;
SET Numeric_RoundAbort OFF;
BEGIN
/* Declare Variables */
DECLARE @currentDateTime SMALLDATETIME;
SET @currentDateTime = GETDATE();
DECLARE @plan_handles TABLE
(
plan_handle VARBINARY(64) Not Null
);
CREATE TABLE #missingIndexes
(
databaseID INT Not Null
, objectID INT Not Null
, query_plan xml Not Null
--CONSTRAINT PK_temp_missingIndexes PRIMARY KEY CLUSTERED
--(
-- databaseID, objectID
--)
);
BEGIN Try
/* Perform some data validation */
IF @logResults = 0 And @displayResults = 0
BEGIN
/* Log the fact that there were open transactions */
EXECUTE dbo.dba_logError_sp
@errorType = 'app'
, @app_errorProcedure = 'dba_missingIndexStoredProc_sp'
, @app_errorMessage = '@logResults = 0 and @displayResults = 0; no action taken, exiting stored proc.'
, @forceExit = 1
, @returnError = 1;
END;
BEGIN TRANSACTION;
/* Retrieve distinct plan handles to minimize dm_exec_query_plan lookups */
INSERT INTO @plan_handles
SELECT DISTINCT plan_handle
FROM sys.dm_exec_query_stats
WHERE last_execution_time > DATEADD(DAY, -@lastExecuted_inDays, @currentDateTime)
And execution_count > @minExecutionCount;
WITH xmlNameSpaces (
DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan'
)
/* Retrieve our query plan's XML if there's a missing index */
INSERT INTO #missingIndexes
SELECT deqp.[dbid]
, deqp.objectid
, deqp.query_plan
FROM @plan_handles AS ph
Cross Apply sys.dm_exec_query_plan(ph.plan_handle) AS deqp
WHERE deqp.query_plan.exist('//MissingIndex') = 1
And deqp.objectid IS Not Null;
/* Do we want to store the results of our process? */
IF @logResults = 1
BEGIN
INSERT INTO dbo.dba_missingIndexStoredProc
EXECUTE sp_msForEachDB 'Use [?];
Select ''?''
, mi.databaseID
, Object_Name(o.object_id)
, o.object_id
, mi.query_plan
, GetDate()
From sys.objects As o
Join #missingIndexes As mi
On o.object_id = mi.objectID
Where databaseID = DB_ID();';
END
/* We're not logging it, so let's display it */
ELSE
BEGIN
EXECUTE sp_msForEachDB 'Use [?];
Select ''?''
, mi.databaseID
, Object_Name(o.object_id)
, o.object_id
, mi.query_plan
, GetDate()
From sys.objects As o
Join #missingIndexes As mi
On o.object_id = mi.objectID
Where databaseID = DB_ID();';
END;
/* See above; this part will only work if we've
logged our data. */
IF @displayResults = 1 And @logResults = 1
BEGIN
SELECT *
FROM dbo.dba_missingIndexStoredProc
WHERE executionDate >= @currentDateTime;
END;
/* If you have an open transaction, commit it */
IF @@TRANCOUNT > 0
COMMIT TRANSACTION;
END Try
BEGIN Catch
/* Whoops, there was an error... rollback! */
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
/* Return an error message and log it */
EXECUTE dbo.dba_logError_sp;
END Catch;
/* Clean-Up! */
DROP TABLE #missingIndexes;
SET NOCOUNT OFF;
RETURN 0;
END
Go
IF OBJECTPROPERTY(OBJECT_ID('dbo.dba_logError_sp'), N'IsProcedure') = 1
BEGIN
DROP PROCEDURE dbo.dba_logError_sp;
PRINT 'Procedure dba_logError_sp dropped';
END;
Go
IF OBJECTPROPERTY(OBJECT_ID('dbo.dba_errorLog'), N'IsTable') IS Null
BEGIN
CREATE TABLE dbo.dba_errorLog
( errorLog_id INT IDENTITY(1,1)
, errorType CHAR(3)
CONSTRAINT [DF_errorLog_errorType] DEFAULT 'sys'
, errorDate DATETIME
CONSTRAINT [DF_errorLog_errorDate] DEFAULT(GETDATE())
, errorLine INT
, errorMessage NVARCHAR(4000)
, errorNumber INT
, errorProcedure NVARCHAR(126)
, procParameters NVARCHAR(4000)
, errorSeverity INT
, errorState INT
, databaseName NVARCHAR(255)
CONSTRAINT PK_errorLog_errorLogID PRIMARY KEY CLUSTERED
(
errorLog_id
)
);
PRINT 'Table dba_errorLog created';
END;
Go
SET ANSI_Nulls ON;
SET Ansi_Padding ON;
SET Ansi_Warnings ON;
SET ArithAbort ON;
SET Concat_Null_Yields_Null ON;
SET NOCOUNT ON;
SET Numeric_RoundAbort OFF;
SET Quoted_Identifier ON;
Go
CREATE PROCEDURE dbo.dba_logError_sp
(
/* Declare Parameters */
@errorType CHAR(3) = 'sys'
, @app_errorProcedure VARCHAR(50) = ''
, @app_errorMessage NVARCHAR(4000) = ''
, @procParameters NVARCHAR(4000) = ''
, @userFriendly BIT = 0
, @forceExit BIT = 1
, @returnError BIT = 1
)
AS
/***************************************************************
Name: dba_logError_sp
Author: Michelle F. Ufford, http://sqlfool.com
Purpose: Retrieves error information and logs in the
dba_errorLog table.
@errorType = options are "app" or "sys"; "app" are custom
application errors, i.e. business logic errors;
"sys" are system errors, i.e. PK errors
@app_errorProcedure = stored procedure name,
needed for app errors
@app_errorMessage = custom app error message
@procParameters = optional; log the parameters that were passed
to the proc that resulted in an error
@userFriendly = displays a generic error message if = 1
@forceExit = forces the proc to rollback and exit;
mostly useful for application errors.
@returnError = returns the error to the calling app if = 1
Called by: Another stored procedure
Date Initials Description
----------------------------------------------------------------------------
2008-12-16 MFU Initial Release
****************************************************************
Exec dbo.dba_logError_sp
@errorType = 'app'
, @app_errorProcedure = 'someTableInsertProcName'
, @app_errorMessage = 'Some app-specific error message'
, @userFriendly = 1
, @forceExit = 1
, @returnError = 1;
****************************************************************/
SET NOCOUNT ON;
SET XACT_Abort ON;
BEGIN
/* Declare Variables */
DECLARE @errorNumber INT
, @errorProcedure VARCHAR(50)
, @dbName sysname
, @errorLine INT
, @errorMessage NVARCHAR(4000)
, @errorSeverity INT
, @errorState INT
, @errorReturnMessage NVARCHAR(4000)
, @errorReturnSeverity INT
, @currentDateTime SMALLDATETIME;
DECLARE @errorReturnID TABLE (errorID VARCHAR(10));
/* Initialize Variables */
SELECT @currentDateTime = GETDATE();
/* Capture our error details */
IF @errorType = 'sys'
BEGIN
/* Get our system error details and hold it */
SELECT
@errorNumber = Error_Number()
, @errorProcedure = Error_Procedure()
, @dbName = DB_NAME()
, @errorLine = Error_Line()
, @errorMessage = Error_Message()
, @errorSeverity = Error_Severity()
, @errorState = Error_State() ;
END
ELSE
BEGIN
/* Get our custom app error details and hold it */
SELECT
@errorNumber = 0
, @errorProcedure = @app_errorProcedure
, @dbName = DB_NAME()
, @errorLine = 0
, @errorMessage = @app_errorMessage
, @errorSeverity = 0
, @errorState = 0 ;
END;
/* And keep a copy for our logs */
INSERT INTO dbo.dba_errorLog
(
errorType
, errorDate
, errorLine
, errorMessage
, errorNumber
, errorProcedure
, procParameters
, errorSeverity
, errorState
, databaseName
)
OUTPUT Inserted.errorLog_id INTO @errorReturnID
VALUES
(
@errorType
, @currentDateTime
, @errorLine
, @errorMessage
, @errorNumber
, @errorProcedure
, @procParameters
, @errorSeverity
, @errorState
, @dbName
);
/* Should we display a user friendly message to the application? */
IF @userFriendly = 1
SELECT @errorReturnMessage = 'An error has occurred in the database (' + errorID + ')'
FROM @errorReturnID;
ELSE
SELECT @errorReturnMessage = @errorMessage;
/* Do we want to force the application to exit? */
IF @forceExit = 1
SELECT @errorReturnSeverity = 15
ELSE
SELECT @errorReturnSeverity = @errorSeverity;
/* Should we return an error message to the calling proc? */
IF @returnError = 1
RAISERROR
(
@errorReturnMessage
, @errorReturnSeverity
, 1
) WITH NoWait;
SET NOCOUNT OFF;
RETURN 0;
END
Go
use master
go
/* Create a stored procedure skeleton */
IF OBJECTPROPERTY(OBJECT_ID('dbo.dba_missingIndexStoredProc_sp'), N'IsProcedure') IS Null
BEGIN
EXECUTE ('Create Procedure dbo.dba_missingIndexStoredProc_sp As Print ''Hello World!''')
RAISERROR('Procedure dba_missingIndexStoredProc_sp created.', 10, 1);
END;
Go
/* Drop our table if it already exists */
IF Exists(SELECT OBJECT_ID FROM sys.tables WHERE [name] = N'dba_missingIndexStoredProc')
BEGIN
DROP TABLE dbo.dba_missingIndexStoredProc
PRINT 'dba_missingIndexStoredProc table dropped!';
END
/* Create our table */
CREATE TABLE dbo.dba_missingIndexStoredProc
(
missingIndexSP_id INT IDENTITY(1,1) Not Null
, databaseName VARCHAR(128) Not Null
, databaseID INT Not Null
, objectName VARCHAR(128) Not Null
, objectID INT Not Null
, query_plan xml Not Null
, executionDate SMALLDATETIME Not Null
CONSTRAINT PK_missingIndexStoredProc
PRIMARY KEY CLUSTERED(missingIndexSP_id)
);
PRINT 'dba_missingIndexStoredProc Table Created';
/* Configure our settings */
SET ANSI_Nulls ON;
SET Quoted_Identifier ON;
Go
ALTER PROCEDURE dbo.dba_missingIndexStoredProc_sp
/* Declare Parameters */
@lastExecuted_inDays INT = 7
, @minExecutionCount INT = 7
, @logResults BIT = 1
, @displayResults BIT = 0
AS
/*********************************************************************************
Name: dba_missingIndexStoredProc_sp
Author: Michelle Ufford, http://sqlfool.com
Purpose: Retrieves stored procedures with missing indexes in their
cached query plans.
@lastExecuted_inDays = number of days old the cached query plan
can be to still appear in the results;
the HIGHER the number, the longer the
execution time.
@minExecutionCount = minimum number of executions the cached
query plan can have to still appear
in the results; the LOWER the number,
the longer the execution time.
@logResults = store results in dba_missingIndexStoredProc
@displayResults = return results to the caller
Notes: This is not 100% guaranteed to catch all missing indexes in
a stored procedure. It will only catch it if the stored proc's
query plan is still in cache. Run regularly to help minimize
the chance of missing a proc.
Called by: DBA and/or SQL Agent Job
Date User Description
----------------------------------------------------------------------------
2009-03-02 MFU Initial Release for public consumption
*********************************************************************************
Exec dbo.dba_missingIndexStoredProc_sp
@lastExecuted_inDays = 30
, @minExecutionCount = 5
, @logResults = 1
, @displayResults = 1;
*********************************************************************************/
SET NOCOUNT ON;
SET XACT_Abort ON;
SET Ansi_Padding ON;
SET Ansi_Warnings ON;
SET ArithAbort ON;
SET Concat_Null_Yields_Null ON;
SET Numeric_RoundAbort OFF;
BEGIN
/* Declare Variables */
DECLARE @currentDateTime SMALLDATETIME;
SET @currentDateTime = GETDATE();
DECLARE @plan_handles TABLE
(
plan_handle VARBINARY(64) Not Null
);
CREATE TABLE #missingIndexes
(
databaseID INT Not Null
, objectID INT Not Null
, query_plan xml Not Null
--CONSTRAINT PK_temp_missingIndexes PRIMARY KEY CLUSTERED
--(
-- databaseID, objectID
--)
);
BEGIN Try
/* Perform some data validation */
IF @logResults = 0 And @displayResults = 0
BEGIN
/* Log the fact that there were open transactions */
EXECUTE dbo.dba_logError_sp
@errorType = 'app'
, @app_errorProcedure = 'dba_missingIndexStoredProc_sp'
, @app_errorMessage = '@logResults = 0 and @displayResults = 0; no action taken, exiting stored proc.'
, @forceExit = 1
, @returnError = 1;
END;
BEGIN TRANSACTION;
/* Retrieve distinct plan handles to minimize dm_exec_query_plan lookups */
INSERT INTO @plan_handles
SELECT DISTINCT plan_handle
FROM sys.dm_exec_query_stats
WHERE last_execution_time > DATEADD(DAY, -@lastExecuted_inDays, @currentDateTime)
And execution_count > @minExecutionCount;
WITH xmlNameSpaces (
DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan'
)
/* Retrieve our query plan's XML if there's a missing index */
INSERT INTO #missingIndexes
SELECT deqp.[dbid]
, deqp.objectid
, deqp.query_plan
FROM @plan_handles AS ph
Cross Apply sys.dm_exec_query_plan(ph.plan_handle) AS deqp
WHERE deqp.query_plan.exist('//MissingIndex') = 1
And deqp.objectid IS Not Null;
/* Do we want to store the results of our process? */
IF @logResults = 1
BEGIN
INSERT INTO dbo.dba_missingIndexStoredProc
EXECUTE sp_msForEachDB 'Use [?];
Select ''?''
, mi.databaseID
, Object_Name(o.object_id)
, o.object_id
, mi.query_plan
, GetDate()
From sys.objects As o
Join #missingIndexes As mi
On o.object_id = mi.objectID
Where databaseID = DB_ID();';
END
/* We're not logging it, so let's display it */
ELSE
BEGIN
EXECUTE sp_msForEachDB 'Use [?];
Select ''?''
, mi.databaseID
, Object_Name(o.object_id)
, o.object_id
, mi.query_plan
, GetDate()
From sys.objects As o
Join #missingIndexes As mi
On o.object_id = mi.objectID
Where databaseID = DB_ID();';
END;
/* See above; this part will only work if we've
logged our data. */
IF @displayResults = 1 And @logResults = 1
BEGIN
SELECT *
FROM dbo.dba_missingIndexStoredProc
WHERE executionDate >= @currentDateTime;
END;
/* If you have an open transaction, commit it */
IF @@TRANCOUNT > 0
COMMIT TRANSACTION;
END Try
BEGIN Catch
/* Whoops, there was an error... rollback! */
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
/* Return an error message and log it */
EXECUTE dbo.dba_logError_sp;
END Catch;
/* Clean-Up! */
DROP TABLE #missingIndexes;
SET NOCOUNT OFF;
RETURN 0;
END
Go
IF OBJECTPROPERTY(OBJECT_ID('dbo.dba_logError_sp'), N'IsProcedure') = 1
BEGIN
DROP PROCEDURE dbo.dba_logError_sp;
PRINT 'Procedure dba_logError_sp dropped';
END;
Go
IF OBJECTPROPERTY(OBJECT_ID('dbo.dba_errorLog'), N'IsTable') IS Null
BEGIN
CREATE TABLE dbo.dba_errorLog
( errorLog_id INT IDENTITY(1,1)
, errorType CHAR(3)
CONSTRAINT [DF_errorLog_errorType] DEFAULT 'sys'
, errorDate DATETIME
CONSTRAINT [DF_errorLog_errorDate] DEFAULT(GETDATE())
, errorLine INT
, errorMessage NVARCHAR(4000)
, errorNumber INT
, errorProcedure NVARCHAR(126)
, procParameters NVARCHAR(4000)
, errorSeverity INT
, errorState INT
, databaseName NVARCHAR(255)
CONSTRAINT PK_errorLog_errorLogID PRIMARY KEY CLUSTERED
(
errorLog_id
)
);
PRINT 'Table dba_errorLog created';
END;
Go
SET ANSI_Nulls ON;
SET Ansi_Padding ON;
SET Ansi_Warnings ON;
SET ArithAbort ON;
SET Concat_Null_Yields_Null ON;
SET NOCOUNT ON;
SET Numeric_RoundAbort OFF;
SET Quoted_Identifier ON;
Go
CREATE PROCEDURE dbo.dba_logError_sp
(
/* Declare Parameters */
@errorType CHAR(3) = 'sys'
, @app_errorProcedure VARCHAR(50) = ''
, @app_errorMessage NVARCHAR(4000) = ''
, @procParameters NVARCHAR(4000) = ''
, @userFriendly BIT = 0
, @forceExit BIT = 1
, @returnError BIT = 1
)
AS
/***************************************************************
Name: dba_logError_sp
Author: Michelle F. Ufford, http://sqlfool.com
Purpose: Retrieves error information and logs in the
dba_errorLog table.
@errorType = options are "app" or "sys"; "app" are custom
application errors, i.e. business logic errors;
"sys" are system errors, i.e. PK errors
@app_errorProcedure = stored procedure name,
needed for app errors
@app_errorMessage = custom app error message
@procParameters = optional; log the parameters that were passed
to the proc that resulted in an error
@userFriendly = displays a generic error message if = 1
@forceExit = forces the proc to rollback and exit;
mostly useful for application errors.
@returnError = returns the error to the calling app if = 1
Called by: Another stored procedure
Date Initials Description
----------------------------------------------------------------------------
2008-12-16 MFU Initial Release
****************************************************************
Exec dbo.dba_logError_sp
@errorType = 'app'
, @app_errorProcedure = 'someTableInsertProcName'
, @app_errorMessage = 'Some app-specific error message'
, @userFriendly = 1
, @forceExit = 1
, @returnError = 1;
****************************************************************/
SET NOCOUNT ON;
SET XACT_Abort ON;
BEGIN
/* Declare Variables */
DECLARE @errorNumber INT
, @errorProcedure VARCHAR(50)
, @dbName sysname
, @errorLine INT
, @errorMessage NVARCHAR(4000)
, @errorSeverity INT
, @errorState INT
, @errorReturnMessage NVARCHAR(4000)
, @errorReturnSeverity INT
, @currentDateTime SMALLDATETIME;
DECLARE @errorReturnID TABLE (errorID VARCHAR(10));
/* Initialize Variables */
SELECT @currentDateTime = GETDATE();
/* Capture our error details */
IF @errorType = 'sys'
BEGIN
/* Get our system error details and hold it */
SELECT
@errorNumber = Error_Number()
, @errorProcedure = Error_Procedure()
, @dbName = DB_NAME()
, @errorLine = Error_Line()
, @errorMessage = Error_Message()
, @errorSeverity = Error_Severity()
, @errorState = Error_State() ;
END
ELSE
BEGIN
/* Get our custom app error details and hold it */
SELECT
@errorNumber = 0
, @errorProcedure = @app_errorProcedure
, @dbName = DB_NAME()
, @errorLine = 0
, @errorMessage = @app_errorMessage
, @errorSeverity = 0
, @errorState = 0 ;
END;
/* And keep a copy for our logs */
INSERT INTO dbo.dba_errorLog
(
errorType
, errorDate
, errorLine
, errorMessage
, errorNumber
, errorProcedure
, procParameters
, errorSeverity
, errorState
, databaseName
)
OUTPUT Inserted.errorLog_id INTO @errorReturnID
VALUES
(
@errorType
, @currentDateTime
, @errorLine
, @errorMessage
, @errorNumber
, @errorProcedure
, @procParameters
, @errorSeverity
, @errorState
, @dbName
);
/* Should we display a user friendly message to the application? */
IF @userFriendly = 1
SELECT @errorReturnMessage = 'An error has occurred in the database (' + errorID + ')'
FROM @errorReturnID;
ELSE
SELECT @errorReturnMessage = @errorMessage;
/* Do we want to force the application to exit? */
IF @forceExit = 1
SELECT @errorReturnSeverity = 15
ELSE
SELECT @errorReturnSeverity = @errorSeverity;
/* Should we return an error message to the calling proc? */
IF @returnError = 1
RAISERROR
(
@errorReturnMessage
, @errorReturnSeverity
, 1
) WITH NoWait;
SET NOCOUNT OFF;
RETURN 0;
END
Go
Thursday, March 17, 2011
Monday, March 07, 2011
Friday, March 04, 2011
Wednesday, March 02, 2011
Monday, February 28, 2011
Thursday, February 24, 2011
Tuesday, February 22, 2011
Friday, February 18, 2011
Thursday, February 17, 2011
ESSENTIAL PERFORMANCE TOOLS FOR SQL SERVER DBA
http://www.idera.com/Downloads/WhitePapers/Essential-Performance-Tools-SQL-Server-DBA.pdf?elq=365bc336c7cf4d3aaa5ec818146aa0a9
Wednesday, February 16, 2011
Monday, February 07, 2011
Tuesday, February 01, 2011
Thursday, January 27, 2011
Find Clustered Indexes that are uniqueidentifier field
SELECT
OBJECT_NAME(i.ID) as tablename
,ISNULL(SYSCOLUMNS.NAME,'') as columnname
,i.name as indexname
, i.indid as isclustered
,systypes.name as columndatatype
--,*
FROM SYSINDEXES I
INNER JOIN SYSINDEXKEYS ON I.ID=SYSINDEXKEYS.ID AND I.INDID=SYSINDEXKEYS.INDID
INNER JOIN SYSCOLUMNS ON SYSINDEXKEYS.ID=SYSCOLUMNS.ID AND SYSINDEXKEYS.COLID=SYSCOLUMNS.COLID
inner join systypes on systypes.xtype = SYSCOLUMNS.xtype
inner join sysobjects on sysobjects.id = i.id
WHERE I.INDID =1
AND I.INDID < 255 AND (I.STATUS & 64)=0 and SYSCOLUMNS.xtype = 36 and i.id > 255
and sysobjects.xtype = 'U'
order by
OBJECT_NAME(i.ID)
OBJECT_NAME(i.ID) as tablename
,ISNULL(SYSCOLUMNS.NAME,'') as columnname
,i.name as indexname
, i.indid as isclustered
,systypes.name as columndatatype
--,*
FROM SYSINDEXES I
INNER JOIN SYSINDEXKEYS ON I.ID=SYSINDEXKEYS.ID AND I.INDID=SYSINDEXKEYS.INDID
INNER JOIN SYSCOLUMNS ON SYSINDEXKEYS.ID=SYSCOLUMNS.ID AND SYSINDEXKEYS.COLID=SYSCOLUMNS.COLID
inner join systypes on systypes.xtype = SYSCOLUMNS.xtype
inner join sysobjects on sysobjects.id = i.id
WHERE I.INDID =1
AND I.INDID < 255 AND (I.STATUS & 64)=0 and SYSCOLUMNS.xtype = 36 and i.id > 255
and sysobjects.xtype = 'U'
order by
OBJECT_NAME(i.ID)
Tuesday, January 25, 2011
drop indexes for all tables in DB
sp_msforeachtable
'
declare @name varchar(50) ,@errorsave int, @tab_name varchar(100) = ''?''
if exists (select name from sysindexes
where id = object_id(@tab_name) and indid > 0 and indid < 255 and (status & 64)=0) begin declare ind_cursor cursor for select name from sysindexes where id = object_id(@tab_name) and indid > 0 and indid < 255 and (status & 64)=0
open ind_cursor
fetch next from ind_cursor into @name
while (@@fetch_status = 0)
begin
print ''drop index '' + @tab_name + ''.'' + @name
print ''go''
fetch next from ind_cursor into @name
end
close ind_cursor
deallocate ind_cursor
end
'
'
declare @name varchar(50) ,@errorsave int, @tab_name varchar(100) = ''?''
if exists (select name from sysindexes
where id = object_id(@tab_name) and indid > 0 and indid < 255 and (status & 64)=0) begin declare ind_cursor cursor for select name from sysindexes where id = object_id(@tab_name) and indid > 0 and indid < 255 and (status & 64)=0
open ind_cursor
fetch next from ind_cursor into @name
while (@@fetch_status = 0)
begin
print ''drop index '' + @tab_name + ''.'' + @name
print ''go''
fetch next from ind_cursor into @name
end
close ind_cursor
deallocate ind_cursor
end
'
Monday, January 24, 2011
sp_alert_jobs
USE [msdb]
GO
/****** Object: StoredProcedure [dbo].[sp_alert_jobs] Script Date: 01/24/2011 07:50:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[sp_alert_jobs]
(
@maxrundurationhour int = 6
)
as
begin
--CAN NOT EXECUTE THIS PROC BY QUERY , ONLY RUNS FROM JOB
if(object_id('msdb..#tmp')>1)
drop table #tmp
if(object_id('msdb..__tmp_final')>1)
drop table __tmp_final
declare @maxrunduration int = 0
--set @maxrunduration = 60 * 60 * 6 -- 6 hours
set @maxrunduration = 60 * 60 * @maxrundurationhour
create table #tmp (job_id uniqueidentifier NOT NULL,last_run_date nvarchar (20) NOT NULL,last_run_time nvarchar (20) NOT NULL,next_run_date nvarchar (20) NOT NULL,next_run_time nvarchar (20) NOT NULL, next_run_schedule_id INT NOT NULL,requested_to_run INT NOT NULL,request_source INT NOT NULL,request_source_id sysname COLLATE database_default NULL, running INT NOT NULL,current_step INT NOT NULL,current_retry_attempt INT NOT NULL,job_state INT NOT NULL);
insert into #tmp EXECUTE master.dbo.xp_sqlagent_enum_jobs 1,'CSODProd\SQLClusterLD4SW2';
UPDATE #tmp SET last_run_time = right ('000000' + last_run_time, 6), next_run_time = right ('000000' + next_run_time, 6);
--select * From #tmp
SELECT @@servername as ServerName , j.name AS JobName, j.enabled AS Enabled,
Case x.running WHEN 1 THEN 'Running' Else Case h.run_status WHEN 2 THEN 'Inactive' WHEN 4 THEN 'Inactive' Else 'Completed' End End AS CurrentStatus,
coalesce (x.current_step, 0) AS CurrentStepNbr,
CASE WHEN x.last_run_date > 0 THEN convert (datetime, substring (x.last_run_date, 1, 4)+ '-' + substring (x.last_run_date, 5, 2)+ '-'+ substring (x.last_run_date, 7, 2)+ ' '+ substring (x.last_run_time, 1, 2) + ':' + substring (x.last_run_time, 3, 2)+ ':' + substring (x.last_run_time, 5, 2)+ '.000',121) Else NULL End AS LastRunTime,
Case h.run_status WHEN 0 THEN 'Fail'WHEN 1 THEN 'Success' WHEN 2 THEN 'Retry' WHEN 3 THEN 'Cancel' WHEN 4 THEN 'In progress' End AS LastRunOutcome,
CASE WHEN h.run_duration > 0 THEN (h.run_duration / 1000000) * (3600 * 24) + (h.run_duration / 10000 % 100) * 3600 + (h.run_duration / 100 % 100) * 60 + (h.run_duration % 100) Else NULL End AS LastRunDuration
into __tmp_final
FROM #tmp x
Left Join msdb.dbo.sysjobs j ON x.job_id = j.job_id
LEFT OUTER JOIN msdb.dbo.syscategories c ON j.category_id = c.category_id
LEFT OUTER JOIN msdb.dbo.sysjobhistory h ON x.job_id = h.job_id AND x.last_run_date = h.run_date AND x.last_run_time = h.run_time AND h.step_id = 0
where --j.name like 'csod%'
h.run_status != 1
or h.run_duration > @maxrunduration
--select * from __tmp_final
--select 'Job = '+jobname+' : Status = '+lastrunoutcome+ ' : Run Duration = '+ convert(nvarchar(10),lastrunduration)+' min' from __tmp_final
if(select COUNT(*) from __tmp_final) >0
begin
declare @subject nvarchar(1000), @recipientslist nvarchar(1000)
set @subject = 'SQL JOB ALERT for '+@@SERVERNAME+' : '+Convert(nvarchar(100),getdate(),101)
set @recipientslist = 'ayegudkin@csod.com;palexander@csod.com;Defcon4@csod.com'
exec msdb.dbo.sp_send_dbmail
@body_format = 'HTML',
@profile_name = 'default',
@Subject = @subject,
--@body = 'test',
@query = 'set nocount on; select ''Job = ''+jobname+'' : Status = ''+lastrunoutcome+ '' : Run Duration = ''+ convert(nvarchar(10),lastrunduration)+'' min
'' from __tmp_final;set nocount off; ',
--@attach_query_result_as_file = 0,
@query_result_header = 0,
--@query_result_width = 1000,
--@exclude_query_output = 1,
--@query_result_separator = ';',
@execute_query_database = 'msdb',
@recipients = @recipientslist
end
--EXEC sys.xp_logininfo @acctname = 'CSODMGMT\palexander', @option ='members'
--EXEC sys.xp_logininfo @acctname = 'CSODProd\SQLClusterLA4SW4'
end
GO
/****** Object: StoredProcedure [dbo].[sp_alert_jobs] Script Date: 01/24/2011 07:50:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[sp_alert_jobs]
(
@maxrundurationhour int = 6
)
as
begin
--CAN NOT EXECUTE THIS PROC BY QUERY , ONLY RUNS FROM JOB
if(object_id('msdb..#tmp')>1)
drop table #tmp
if(object_id('msdb..__tmp_final')>1)
drop table __tmp_final
declare @maxrunduration int = 0
--set @maxrunduration = 60 * 60 * 6 -- 6 hours
set @maxrunduration = 60 * 60 * @maxrundurationhour
create table #tmp (job_id uniqueidentifier NOT NULL,last_run_date nvarchar (20) NOT NULL,last_run_time nvarchar (20) NOT NULL,next_run_date nvarchar (20) NOT NULL,next_run_time nvarchar (20) NOT NULL, next_run_schedule_id INT NOT NULL,requested_to_run INT NOT NULL,request_source INT NOT NULL,request_source_id sysname COLLATE database_default NULL, running INT NOT NULL,current_step INT NOT NULL,current_retry_attempt INT NOT NULL,job_state INT NOT NULL);
insert into #tmp EXECUTE master.dbo.xp_sqlagent_enum_jobs 1,'CSODProd\SQLClusterLD4SW2';
UPDATE #tmp SET last_run_time = right ('000000' + last_run_time, 6), next_run_time = right ('000000' + next_run_time, 6);
--select * From #tmp
SELECT @@servername as ServerName , j.name AS JobName, j.enabled AS Enabled,
Case x.running WHEN 1 THEN 'Running' Else Case h.run_status WHEN 2 THEN 'Inactive' WHEN 4 THEN 'Inactive' Else 'Completed' End End AS CurrentStatus,
coalesce (x.current_step, 0) AS CurrentStepNbr,
CASE WHEN x.last_run_date > 0 THEN convert (datetime, substring (x.last_run_date, 1, 4)+ '-' + substring (x.last_run_date, 5, 2)+ '-'+ substring (x.last_run_date, 7, 2)+ ' '+ substring (x.last_run_time, 1, 2) + ':' + substring (x.last_run_time, 3, 2)+ ':' + substring (x.last_run_time, 5, 2)+ '.000',121) Else NULL End AS LastRunTime,
Case h.run_status WHEN 0 THEN 'Fail'WHEN 1 THEN 'Success' WHEN 2 THEN 'Retry' WHEN 3 THEN 'Cancel' WHEN 4 THEN 'In progress' End AS LastRunOutcome,
CASE WHEN h.run_duration > 0 THEN (h.run_duration / 1000000) * (3600 * 24) + (h.run_duration / 10000 % 100) * 3600 + (h.run_duration / 100 % 100) * 60 + (h.run_duration % 100) Else NULL End AS LastRunDuration
into __tmp_final
FROM #tmp x
Left Join msdb.dbo.sysjobs j ON x.job_id = j.job_id
LEFT OUTER JOIN msdb.dbo.syscategories c ON j.category_id = c.category_id
LEFT OUTER JOIN msdb.dbo.sysjobhistory h ON x.job_id = h.job_id AND x.last_run_date = h.run_date AND x.last_run_time = h.run_time AND h.step_id = 0
where --j.name like 'csod%'
h.run_status != 1
or h.run_duration > @maxrunduration
--select * from __tmp_final
--select 'Job = '+jobname+' : Status = '+lastrunoutcome+ ' : Run Duration = '+ convert(nvarchar(10),lastrunduration)+' min' from __tmp_final
if(select COUNT(*) from __tmp_final) >0
begin
declare @subject nvarchar(1000), @recipientslist nvarchar(1000)
set @subject = 'SQL JOB ALERT for '+@@SERVERNAME+' : '+Convert(nvarchar(100),getdate(),101)
set @recipientslist = 'ayegudkin@csod.com;palexander@csod.com;Defcon4@csod.com'
exec msdb.dbo.sp_send_dbmail
@body_format = 'HTML',
@profile_name = 'default',
@Subject = @subject,
--@body = 'test',
@query = 'set nocount on; select ''Job = ''+jobname+'' : Status = ''+lastrunoutcome+ '' : Run Duration = ''+ convert(nvarchar(10),lastrunduration)+'' min
'' from __tmp_final;set nocount off; ',
--@attach_query_result_as_file = 0,
@query_result_header = 0,
--@query_result_width = 1000,
--@exclude_query_output = 1,
--@query_result_separator = ';',
@execute_query_database = 'msdb',
@recipients = @recipientslist
end
--EXEC sys.xp_logininfo @acctname = 'CSODMGMT\palexander', @option ='members'
--EXEC sys.xp_logininfo @acctname = 'CSODProd\SQLClusterLA4SW4'
end
Tuesday, January 11, 2011
Monday, January 10, 2011
Run an SSIS Package Under a Different Account - SQLServerCentral
Run an SSIS Package Under a Different Account - SQLServerCentral
Run an SSIS Package Under a Different Account
By Polar Bear, 2010/03/25
Total article views: 6535 | Views in the last 30 days: 88
Rate this | Join the discussion | Briefcase | Print
Recently, in a SSIS package, I needed to get some data from a SQL Server database which is used by a third party application. This application uses named user license. The ETLadmin account, which is a domain account that we use it to run SSIS packages, does not have the right permission to access views in that database even if it has sysadmin permission on that SQL Server instance. If we set ETLadmin to an application admin, this will waste an application admin license. And usually ETLadmin account should only have read permission to grab data. Application admin permission will be too much for this account. It will be nice if we can use an existing application admin user account to run the package. This can be done by using SQL Server Agent proxies.
Here are the steps to setup a proxy by using an existing application user account:
Create a credential using the account that having right access to the application database
Open SSMS, connect to the SQL Server instance that the SSIS will be scheduled to run
Go to Security - > Credentials, and click on 'New Credential...' to create a new credential
Zoom in | Open in new window
Enter the credential name - Enter the domain account, and password. Repeat the password in the 'Confirm password'. This account should have the right access to the application database.
Click 'ok' and the new credential should be listed.
Zoom in | Open in new window
Create a proxy using the credential created in the previous step
Right click SQL Server Agent -> Proxies, and select 'New proxy...'
Zoom in | Open in new window
Enter the new proxy name, and choose the credential that created from the previous step from the dropdown list. And check 'SQL Server Integration Services Package' under 'Active to the following subsystems'. The SQL Server Agent proxy can be activated for many sub systems (as listed in the screen shot). In this case we are only enabling it for SSIS packages.
Zoom in | Open in new window
Click 'OK' and the new proxy should be listed.
Setup a job to run the SSIS package using the proxy
Open the job step properties for the step that run the SSIS package
Select the proxy that created in the previous step from the 'Run as' dropdown list
Zoom in | Open in new window
Click 'OK' to save the change
Now you can run the package using the application account.
By using SQL Server Agent proxy, we can run jobs on different databases, different servers using existing accounts, and avoid giving excessive permission to ETL users or developers.
Run an SSIS Package Under a Different Account
By Polar Bear, 2010/03/25
Total article views: 6535 | Views in the last 30 days: 88
Rate this | Join the discussion | Briefcase | Print
Recently, in a SSIS package, I needed to get some data from a SQL Server database which is used by a third party application. This application uses named user license. The ETLadmin account, which is a domain account that we use it to run SSIS packages, does not have the right permission to access views in that database even if it has sysadmin permission on that SQL Server instance. If we set ETLadmin to an application admin, this will waste an application admin license. And usually ETLadmin account should only have read permission to grab data. Application admin permission will be too much for this account. It will be nice if we can use an existing application admin user account to run the package. This can be done by using SQL Server Agent proxies.
Here are the steps to setup a proxy by using an existing application user account:
Create a credential using the account that having right access to the application database
Open SSMS, connect to the SQL Server instance that the SSIS will be scheduled to run
Go to Security - > Credentials, and click on 'New Credential...' to create a new credential
Zoom in | Open in new window
Enter the credential name - Enter the domain account, and password. Repeat the password in the 'Confirm password'. This account should have the right access to the application database.
Click 'ok' and the new credential should be listed.
Zoom in | Open in new window
Create a proxy using the credential created in the previous step
Right click SQL Server Agent -> Proxies, and select 'New proxy...'
Zoom in | Open in new window
Enter the new proxy name, and choose the credential that created from the previous step from the dropdown list. And check 'SQL Server Integration Services Package' under 'Active to the following subsystems'. The SQL Server Agent proxy can be activated for many sub systems (as listed in the screen shot). In this case we are only enabling it for SSIS packages.
Zoom in | Open in new window
Click 'OK' and the new proxy should be listed.
Setup a job to run the SSIS package using the proxy
Open the job step properties for the step that run the SSIS package
Select the proxy that created in the previous step from the 'Run as' dropdown list
Zoom in | Open in new window
Click 'OK' to save the change
Now you can run the package using the application account.
By using SQL Server Agent proxy, we can run jobs on different databases, different servers using existing accounts, and avoid giving excessive permission to ETL users or developers.
Wednesday, January 05, 2011
Tuesday, January 04, 2011
Wednesday, December 29, 2010
Friday, December 10, 2010
Thursday, December 09, 2010
Wednesday, December 08, 2010
SQL Server Security Audit Report
USE master
GO
SET nocount ON
-- Get all roles
CREATE TABLE #temp_srvrole
(ServerRole VARCHAR(128), Description VARCHAR(128))
INSERT INTO #temp_srvrole
EXEC sp_helpsrvrole
-- sp_help syslogins
CREATE TABLE #temp_memberrole
(ServerRole VARCHAR(128),
MemberName VARCHAR(265),
MemberSID VARCHAR(300))
DECLARE @ServerRole VARCHAR(128)
DECLARE srv_role CURSOR FAST_FORWARD FOR
SELECT ServerRole FROM #temp_srvrole
OPEN srv_role
FETCH NEXT FROM srv_role INTO @ServerRole
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #temp_memberrole
EXEC sp_helpsrvrolemember @ServerRole
FETCH NEXT FROM srv_role INTO @ServerRole
END
CLOSE srv_role
DEALLOCATE srv_role
SELECT ServerRole, MemberName FROM #temp_memberrole
-- IF BUILTIN\Administrators is exist and sysadmin
IF EXISTS(SELECT *FROM #temp_memberrole
WHERE MemberName = 'BUILTIN\Administrators'
AND ServerRole = 'sysadmin' )
BEGIN
CREATE TABLE #temp_localadmin (output VARCHAR(8000))
INSERT INTO #temp_localadmin
EXEC xp_cmdshell 'net localgroup administrators'
SELECT output AS local_administrator
FROM #temp_localadmin
WHERE output LIKE '%\%'
DROP TABLE #temp_localadmin
END
DROP TABLE #temp_srvrole
DROP TABLE #temp_memberrole
-- Get individual Logins
SELECT name, 'Individual NT Login' LoginType
FROM syslogins
WHERE isntgroup = 0 AND isntname = 1
UNION
SELECT name, 'Individual SQL Login' LoginType
FROM syslogins
WHERE isntgroup = 0 AND isntname = 0
UNION ALL
-- Get Group logins
SELECT name,'NT Group Login' LoginType
FROM syslogins
WHERE isntgroup = 1
-- get group list
-- EXEC xp_cmdshell 'net group "AnalyticsDev" /domain'
CREATE TABLE #temp_groupadmin
(output VARCHAR(8000))
CREATE TABLE #temp_groupadmin2
(groupName VARCHAR(256), groupMember VARCHAR(1000))
DECLARE @grpname VARCHAR(128)
DECLARE @sqlcmd VARCHAR(1000)
DECLARE grp_role CURSOR FAST_FORWARD FOR
SELECT REPLACE(name,'US\','')
FROM syslogins
WHERE isntgroup = 1 AND name LIKE 'US\%'
OPEN grp_role
FETCH NEXT FROM grp_role INTO @grpname
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sqlcmd = 'net group "' + @grpname + '" /domain'
TRUNCATE TABLE #temp_groupadmin
PRINT @sqlcmd
INSERT INTO #temp_groupadmin
EXEC xp_cmdshell @sqlcmd
SET ROWCOUNT 8
DELETE FROM #temp_groupadmin
SET ROWCOUNT 0
INSERT INTO #temp_groupadmin2
SELECT @grpname, output FROM #temp_groupadmin
WHERE output NOT LIKE ('%The command completed successfully%')
FETCH NEXT FROM grp_role INTO @grpname
END
CLOSE grp_role
DEALLOCATE grp_role
SELECT * FROM #temp_groupadmin2
DROP TABLE #temp_groupadmin
DROP TABLE #temp_groupadmin2
PRINT 'EXEC sp_validatelogins '
PRINT '----------------------------------------------'
EXEC sp_validatelogins
PRINT ''
-- Get all the Database Rols for that specIFic members
CREATE TABLE #temp_rolemember
(DbRole VARCHAR(128),MemberName VARCHAR(128),MemberSID VARCHAR(1000))
CREATE TABLE #temp_rolemember_final
(DbName VARCHAR(100), DbRole VARCHAR(128),MemberName VARCHAR(128))
DECLARE @dbname VARCHAR(128)
DECLARE @sqlcmd2 VARCHAR(1000)
DECLARE grp_role CURSOR FOR
SELECT name FROM sysdatabases
WHERE name NOT IN ('tempdb')
AND DATABASEPROPERTYEX(name, 'Status') = 'ONLINE'
OPEN grp_role
FETCH NEXT FROM grp_role INTO @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
TRUNCATE TABLE #temp_rolemember
SET @sqlcmd2 = 'EXEC [' + @dbname + ']..sp_helprolemember'
PRINT @sqlcmd2
INSERT INTO #temp_rolemember
EXECUTE(@sqlcmd2)
INSERT INTO #temp_rolemember_final
SELECT @dbname AS DbName, DbRole, MemberName
FROM #temp_rolemember
FETCH NEXT FROM grp_role INTO @dbname
END
CLOSE grp_role
DEALLOCATE grp_role
SELECT * FROM #temp_rolemember_final
DROP TABLE #temp_rolemember
DROP TABLE #temp_rolemember_final
GO
SET nocount ON
-- Get all roles
CREATE TABLE #temp_srvrole
(ServerRole VARCHAR(128), Description VARCHAR(128))
INSERT INTO #temp_srvrole
EXEC sp_helpsrvrole
-- sp_help syslogins
CREATE TABLE #temp_memberrole
(ServerRole VARCHAR(128),
MemberName VARCHAR(265),
MemberSID VARCHAR(300))
DECLARE @ServerRole VARCHAR(128)
DECLARE srv_role CURSOR FAST_FORWARD FOR
SELECT ServerRole FROM #temp_srvrole
OPEN srv_role
FETCH NEXT FROM srv_role INTO @ServerRole
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #temp_memberrole
EXEC sp_helpsrvrolemember @ServerRole
FETCH NEXT FROM srv_role INTO @ServerRole
END
CLOSE srv_role
DEALLOCATE srv_role
SELECT ServerRole, MemberName FROM #temp_memberrole
-- IF BUILTIN\Administrators is exist and sysadmin
IF EXISTS(SELECT *FROM #temp_memberrole
WHERE MemberName = 'BUILTIN\Administrators'
AND ServerRole = 'sysadmin' )
BEGIN
CREATE TABLE #temp_localadmin (output VARCHAR(8000))
INSERT INTO #temp_localadmin
EXEC xp_cmdshell 'net localgroup administrators'
SELECT output AS local_administrator
FROM #temp_localadmin
WHERE output LIKE '%\%'
DROP TABLE #temp_localadmin
END
DROP TABLE #temp_srvrole
DROP TABLE #temp_memberrole
-- Get individual Logins
SELECT name, 'Individual NT Login' LoginType
FROM syslogins
WHERE isntgroup = 0 AND isntname = 1
UNION
SELECT name, 'Individual SQL Login' LoginType
FROM syslogins
WHERE isntgroup = 0 AND isntname = 0
UNION ALL
-- Get Group logins
SELECT name,'NT Group Login' LoginType
FROM syslogins
WHERE isntgroup = 1
-- get group list
-- EXEC xp_cmdshell 'net group "AnalyticsDev" /domain'
CREATE TABLE #temp_groupadmin
(output VARCHAR(8000))
CREATE TABLE #temp_groupadmin2
(groupName VARCHAR(256), groupMember VARCHAR(1000))
DECLARE @grpname VARCHAR(128)
DECLARE @sqlcmd VARCHAR(1000)
DECLARE grp_role CURSOR FAST_FORWARD FOR
SELECT REPLACE(name,'US\','')
FROM syslogins
WHERE isntgroup = 1 AND name LIKE 'US\%'
OPEN grp_role
FETCH NEXT FROM grp_role INTO @grpname
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sqlcmd = 'net group "' + @grpname + '" /domain'
TRUNCATE TABLE #temp_groupadmin
PRINT @sqlcmd
INSERT INTO #temp_groupadmin
EXEC xp_cmdshell @sqlcmd
SET ROWCOUNT 8
DELETE FROM #temp_groupadmin
SET ROWCOUNT 0
INSERT INTO #temp_groupadmin2
SELECT @grpname, output FROM #temp_groupadmin
WHERE output NOT LIKE ('%The command completed successfully%')
FETCH NEXT FROM grp_role INTO @grpname
END
CLOSE grp_role
DEALLOCATE grp_role
SELECT * FROM #temp_groupadmin2
DROP TABLE #temp_groupadmin
DROP TABLE #temp_groupadmin2
PRINT 'EXEC sp_validatelogins '
PRINT '----------------------------------------------'
EXEC sp_validatelogins
PRINT ''
-- Get all the Database Rols for that specIFic members
CREATE TABLE #temp_rolemember
(DbRole VARCHAR(128),MemberName VARCHAR(128),MemberSID VARCHAR(1000))
CREATE TABLE #temp_rolemember_final
(DbName VARCHAR(100), DbRole VARCHAR(128),MemberName VARCHAR(128))
DECLARE @dbname VARCHAR(128)
DECLARE @sqlcmd2 VARCHAR(1000)
DECLARE grp_role CURSOR FOR
SELECT name FROM sysdatabases
WHERE name NOT IN ('tempdb')
AND DATABASEPROPERTYEX(name, 'Status') = 'ONLINE'
OPEN grp_role
FETCH NEXT FROM grp_role INTO @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
TRUNCATE TABLE #temp_rolemember
SET @sqlcmd2 = 'EXEC [' + @dbname + ']..sp_helprolemember'
PRINT @sqlcmd2
INSERT INTO #temp_rolemember
EXECUTE(@sqlcmd2)
INSERT INTO #temp_rolemember_final
SELECT @dbname AS DbName, DbRole, MemberName
FROM #temp_rolemember
FETCH NEXT FROM grp_role INTO @dbname
END
CLOSE grp_role
DEALLOCATE grp_role
SELECT * FROM #temp_rolemember_final
DROP TABLE #temp_rolemember
DROP TABLE #temp_rolemember_final
Monday, December 06, 2010
Thursday, December 02, 2010
SQL Server Encryption - Encrypt a database
USE master;
GO
--master key password
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '****';
--DROP MASTER KEY
go
--database encryption key
CREATE CERTIFICATE MyServerCert WITH SUBJECT = 'Certificate'
--DROP CERTIFICATE MyServerCert
go
USE master
GO
BACKUP CERTIFICATE MyServerCert
TO FILE = 'C:\File.cer'
WITH PRIVATE KEY (FILE = 'C:\Key.pvk' ,
ENCRYPTION BY PASSWORD = '*****' )
GO
USE [cleint]
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE MyServerCert
GO
ALTER DATABASE [cleint]
SET ENCRYPTION ON
GO
SELECT db_name(database_id), encryption_state, *
FROM sys.dm_database_encryption_keys with (nolock)
SELECT * FROM sys.symmetric_keys
SELECT * FROM sys.certificates
SELECT * FROM sys.dm_database_encryption_keys
GO
--master key password
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '****';
--DROP MASTER KEY
go
--database encryption key
CREATE CERTIFICATE MyServerCert WITH SUBJECT = 'Certificate'
--DROP CERTIFICATE MyServerCert
go
USE master
GO
BACKUP CERTIFICATE MyServerCert
TO FILE = 'C:\File.cer'
WITH PRIVATE KEY (FILE = 'C:\Key.pvk' ,
ENCRYPTION BY PASSWORD = '*****' )
GO
USE [cleint]
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE MyServerCert
GO
ALTER DATABASE [cleint]
SET ENCRYPTION ON
GO
SELECT db_name(database_id), encryption_state, *
FROM sys.dm_database_encryption_keys with (nolock)
SELECT * FROM sys.symmetric_keys
SELECT * FROM sys.certificates
SELECT * FROM sys.dm_database_encryption_keys
Tuesday, November 30, 2010
SQL 2008 Server Audit - Server and Database Specification
--Create Audit
CREATE SERVER AUDIT [DDL-Audit]
TO FILE
( FILEPATH = N'E:\Audit\'
,MAXSIZE = 0 MB
,MAX_ROLLOVER_FILES = 2147483647
,RESERVE_DISK_SPACE = OFF
)
WITH
( QUEUE_DELAY = 1000
,ON_FAILURE = CONTINUE
,AUDIT_GUID = '877f038b-d57c-44d1-8db4-c2c3dc1321a1'
)
GO
--Create Audit specifics Server Level
CREATE SERVER AUDIT SPECIFICATION [ServerAuditSpecification]
FOR SERVER AUDIT [DDL-Audit]
ADD (DATABASE_OBJECT_ACCESS_GROUP),
ADD (SERVER_ROLE_MEMBER_CHANGE_GROUP),
ADD (AUDIT_CHANGE_GROUP),
ADD (SERVER_OBJECT_PERMISSION_CHANGE_GROUP),
ADD (DATABASE_CHANGE_GROUP),
ADD (DATABASE_OBJECT_CHANGE_GROUP),
ADD (SERVER_OBJECT_CHANGE_GROUP),
ADD (APPLICATION_ROLE_CHANGE_PASSWORD_GROUP),
ADD (SERVER_STATE_CHANGE_GROUP),
ADD (SERVER_OBJECT_OWNERSHIP_CHANGE_GROUP)
WITH (STATE = ON)
GO
--Create Audit specifics Server Level
CREATE SERVER AUDIT SPECIFICATION [ServerAuditSpecification-Login-Logout]
FOR SERVER AUDIT [Login-Logout-Audit]
ADD (FAILED_LOGIN_GROUP),
ADD (SUCCESSFUL_LOGIN_GROUP),
ADD (LOGOUT_GROUP)
WITH (STATE = ON)
GO
--Create Audit Specific Database Level
CREATE DATABASE AUDIT SPECIFICATION [DDL_changes_audit]
FOR SERVER AUDIT [DDL-Audit]
ADD (DATABASE_OBJECT_CHANGE_GROUP),
ADD (SCHEMA_OBJECT_CHANGE_GROUP)
WITH (STATE = ON)
GO
--Read from Audit Trace File
select * FROM sys.fn_get_audit_file('E:\Audit\DDL*',default,default)
--Disable Audit and Server Audit Specification
ALTER SERVER AUDIT [DDL-Audit] with (State = OFF)
ALTER SERVER AUDIT SPECIFICATION [ServerAuditSpecification-Login-Logout] with (State = OFF)
--Enable Audit and Server Audit Specification
ALTER SERVER AUDIT [DDL-Audit] with (State = ON)
ALTER SERVER AUDIT SPECIFICATION [ServerAuditSpecification-Login-Logout] with (State = ON)
CREATE SERVER AUDIT [DDL-Audit]
TO FILE
( FILEPATH = N'E:\Audit\'
,MAXSIZE = 0 MB
,MAX_ROLLOVER_FILES = 2147483647
,RESERVE_DISK_SPACE = OFF
)
WITH
( QUEUE_DELAY = 1000
,ON_FAILURE = CONTINUE
,AUDIT_GUID = '877f038b-d57c-44d1-8db4-c2c3dc1321a1'
)
GO
--Create Audit specifics Server Level
CREATE SERVER AUDIT SPECIFICATION [ServerAuditSpecification]
FOR SERVER AUDIT [DDL-Audit]
ADD (DATABASE_OBJECT_ACCESS_GROUP),
ADD (SERVER_ROLE_MEMBER_CHANGE_GROUP),
ADD (AUDIT_CHANGE_GROUP),
ADD (SERVER_OBJECT_PERMISSION_CHANGE_GROUP),
ADD (DATABASE_CHANGE_GROUP),
ADD (DATABASE_OBJECT_CHANGE_GROUP),
ADD (SERVER_OBJECT_CHANGE_GROUP),
ADD (APPLICATION_ROLE_CHANGE_PASSWORD_GROUP),
ADD (SERVER_STATE_CHANGE_GROUP),
ADD (SERVER_OBJECT_OWNERSHIP_CHANGE_GROUP)
WITH (STATE = ON)
GO
--Create Audit specifics Server Level
CREATE SERVER AUDIT SPECIFICATION [ServerAuditSpecification-Login-Logout]
FOR SERVER AUDIT [Login-Logout-Audit]
ADD (FAILED_LOGIN_GROUP),
ADD (SUCCESSFUL_LOGIN_GROUP),
ADD (LOGOUT_GROUP)
WITH (STATE = ON)
GO
--Create Audit Specific Database Level
CREATE DATABASE AUDIT SPECIFICATION [DDL_changes_audit]
FOR SERVER AUDIT [DDL-Audit]
ADD (DATABASE_OBJECT_CHANGE_GROUP),
ADD (SCHEMA_OBJECT_CHANGE_GROUP)
WITH (STATE = ON)
GO
--Read from Audit Trace File
select * FROM sys.fn_get_audit_file('E:\Audit\DDL*',default,default)
--Disable Audit and Server Audit Specification
ALTER SERVER AUDIT [DDL-Audit] with (State = OFF)
ALTER SERVER AUDIT SPECIFICATION [ServerAuditSpecification-Login-Logout] with (State = OFF)
--Enable Audit and Server Audit Specification
ALTER SERVER AUDIT [DDL-Audit] with (State = ON)
ALTER SERVER AUDIT SPECIFICATION [ServerAuditSpecification-Login-Logout] with (State = ON)
Thursday, November 18, 2010
Jobs on Server and detail information
IF EXISTS (SELECT *
FROM tempdb.dbo.sysobjects
WHERE id = OBJECT_ID(N'[tempdb].[dbo].[Temp1]')
)
DROP TABLE [tempdb].[dbo].[Temp1]
GO
CREATE TABLE [tempdb].[dbo].[Temp1]
(
job_id uniqueidentifier NOT NULL,
last_run_date nvarchar (20) NOT NULL,
last_run_time nvarchar (20) NOT NULL,
next_run_date nvarchar (20) NOT NULL,
next_run_time nvarchar (20) NOT NULL,
next_run_schedule_id INT NOT NULL,
requested_to_run INT NOT NULL,
request_source INT NOT NULL,
request_source_id sysname
COLLATE database_default NULL,
running INT NOT NULL,
current_step INT NOT NULL,
current_retry_attempt INT NOT NULL,
job_state INT NOT NULL)
DECLARE @job_owner sysname
DECLARE @is_sysadmin INT
SET @is_sysadmin = isnull (is_srvrolemember ('sysadmin'), 0)
SET @job_owner = suser_sname ()
INSERT INTO [tempdb].[dbo].[Temp1]
--EXECUTE sys.xp_sqlagent_enum_jobs @is_sysadmin, @job_owner
EXECUTE master.dbo.xp_sqlagent_enum_jobs @is_sysadmin, @job_owner
UPDATE [tempdb].[dbo].[Temp1]
SET last_run_time = right ('000000' + last_run_time, 6),
next_run_time = right ('000000' + next_run_time, 6);
-----
SELECT j.name AS JobName,
j.enabled AS Enabled,
CASE x.running
WHEN 1
THEN
'Running'
ELSE
CASE h.run_status
WHEN 2 THEN 'Inactive'
WHEN 4 THEN 'Inactive'
ELSE 'Completed'
END
END
AS CurrentStatus,
coalesce (x.current_step, 0) AS CurrentStepNbr,
CASE
WHEN x.last_run_date > 0
THEN
convert (datetime,
substring (x.last_run_date, 1, 4)
+ '-'
+ substring (x.last_run_date, 5, 2)
+ '-'
+ substring (x.last_run_date, 7, 2)
+ ' '
+ substring (x.last_run_time, 1, 2)
+ ':'
+ substring (x.last_run_time, 3, 2)
+ ':'
+ substring (x.last_run_time, 5, 2)
+ '.000',
121
)
ELSE
NULL
END
AS LastRunTime,
CASE h.run_status
WHEN 0 THEN 'Fail'
WHEN 1 THEN 'Success'
WHEN 2 THEN 'Retry'
WHEN 3 THEN 'Cancel'
WHEN 4 THEN 'In progress'
END
AS LastRunOutcome,
CASE
WHEN h.run_duration > 0
THEN
(h.run_duration / 1000000) * (3600 * 24)
+ (h.run_duration / 10000 % 100) * 3600
+ (h.run_duration / 100 % 100) * 60
+ (h.run_duration % 100)
ELSE
NULL
END
AS LastRunDuration
FROM [tempdb].[dbo].[Temp1] x
LEFT JOIN
msdb.dbo.sysjobs j
ON x.job_id = j.job_id
LEFT OUTER JOIN
msdb.dbo.syscategories c
ON j.category_id = c.category_id
LEFT OUTER JOIN
msdb.dbo.sysjobhistory h
ON x.job_id = h.job_id
AND x.last_run_date = h.run_date
AND x.last_run_time = h.run_time
AND h.step_id = 0
--where x.running = 1
FROM tempdb.dbo.sysobjects
WHERE id = OBJECT_ID(N'[tempdb].[dbo].[Temp1]')
)
DROP TABLE [tempdb].[dbo].[Temp1]
GO
CREATE TABLE [tempdb].[dbo].[Temp1]
(
job_id uniqueidentifier NOT NULL,
last_run_date nvarchar (20) NOT NULL,
last_run_time nvarchar (20) NOT NULL,
next_run_date nvarchar (20) NOT NULL,
next_run_time nvarchar (20) NOT NULL,
next_run_schedule_id INT NOT NULL,
requested_to_run INT NOT NULL,
request_source INT NOT NULL,
request_source_id sysname
COLLATE database_default NULL,
running INT NOT NULL,
current_step INT NOT NULL,
current_retry_attempt INT NOT NULL,
job_state INT NOT NULL)
DECLARE @job_owner sysname
DECLARE @is_sysadmin INT
SET @is_sysadmin = isnull (is_srvrolemember ('sysadmin'), 0)
SET @job_owner = suser_sname ()
INSERT INTO [tempdb].[dbo].[Temp1]
--EXECUTE sys.xp_sqlagent_enum_jobs @is_sysadmin, @job_owner
EXECUTE master.dbo.xp_sqlagent_enum_jobs @is_sysadmin, @job_owner
UPDATE [tempdb].[dbo].[Temp1]
SET last_run_time = right ('000000' + last_run_time, 6),
next_run_time = right ('000000' + next_run_time, 6);
-----
SELECT j.name AS JobName,
j.enabled AS Enabled,
CASE x.running
WHEN 1
THEN
'Running'
ELSE
CASE h.run_status
WHEN 2 THEN 'Inactive'
WHEN 4 THEN 'Inactive'
ELSE 'Completed'
END
END
AS CurrentStatus,
coalesce (x.current_step, 0) AS CurrentStepNbr,
CASE
WHEN x.last_run_date > 0
THEN
convert (datetime,
substring (x.last_run_date, 1, 4)
+ '-'
+ substring (x.last_run_date, 5, 2)
+ '-'
+ substring (x.last_run_date, 7, 2)
+ ' '
+ substring (x.last_run_time, 1, 2)
+ ':'
+ substring (x.last_run_time, 3, 2)
+ ':'
+ substring (x.last_run_time, 5, 2)
+ '.000',
121
)
ELSE
NULL
END
AS LastRunTime,
CASE h.run_status
WHEN 0 THEN 'Fail'
WHEN 1 THEN 'Success'
WHEN 2 THEN 'Retry'
WHEN 3 THEN 'Cancel'
WHEN 4 THEN 'In progress'
END
AS LastRunOutcome,
CASE
WHEN h.run_duration > 0
THEN
(h.run_duration / 1000000) * (3600 * 24)
+ (h.run_duration / 10000 % 100) * 3600
+ (h.run_duration / 100 % 100) * 60
+ (h.run_duration % 100)
ELSE
NULL
END
AS LastRunDuration
FROM [tempdb].[dbo].[Temp1] x
LEFT JOIN
msdb.dbo.sysjobs j
ON x.job_id = j.job_id
LEFT OUTER JOIN
msdb.dbo.syscategories c
ON j.category_id = c.category_id
LEFT OUTER JOIN
msdb.dbo.sysjobhistory h
ON x.job_id = h.job_id
AND x.last_run_date = h.run_date
AND x.last_run_time = h.run_time
AND h.step_id = 0
--where x.running = 1
Monday, November 15, 2010
Friday, November 12, 2010
Thursday, November 04, 2010
Thursday, October 28, 2010
Getting a SQL Server RowCount Without doing a Table Scan
SELECT [name]
, dbo.[fn_get_table_rowcount] ([name]) as [Row Count]
FROM sysobjects
WHERE type='U' and name != 'dtproperties'
ORDER BY [Row Count] desc
GO
----------------------------------------------------------------------
--FUNCIOTN
----------------------------------------------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_get_table_rowcount]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fn_get_table_rowcount]
GO
CREATE FUNCTION dbo.[fn_get_table_rowcount]
(
@sTableName sysname -- Table to retrieve Row Count
)
RETURNS INT -- Row count of the table, NULL if not found.
AS BEGIN
DECLARE @nRowCount INT -- the rows
DECLARE @nObjectID int -- Object ID
SET @nObjectID = OBJECT_ID(@sTableName)
-- Object might not be found
IF @nObjectID is null RETURN NULL
SELECT TOP 1 @nRowCount = rows
FROM sysindexes
WHERE id = @nObjectID AND indid < 2
RETURN @nRowCount
END
GO
, dbo.[fn_get_table_rowcount] ([name]) as [Row Count]
FROM sysobjects
WHERE type='U' and name != 'dtproperties'
ORDER BY [Row Count] desc
GO
----------------------------------------------------------------------
--FUNCIOTN
----------------------------------------------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_get_table_rowcount]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fn_get_table_rowcount]
GO
CREATE FUNCTION dbo.[fn_get_table_rowcount]
(
@sTableName sysname -- Table to retrieve Row Count
)
RETURNS INT -- Row count of the table, NULL if not found.
AS BEGIN
DECLARE @nRowCount INT -- the rows
DECLARE @nObjectID int -- Object ID
SET @nObjectID = OBJECT_ID(@sTableName)
-- Object might not be found
IF @nObjectID is null RETURN NULL
SELECT TOP 1 @nRowCount = rows
FROM sysindexes
WHERE id = @nObjectID AND indid < 2
RETURN @nRowCount
END
GO
Friday, October 22, 2010
SQL server CPU Allocation and usage
--general sql configuration
EXEC sys.sp_configure
--server available CPU
select cpu_count from sys.dm_os_sys_info
--server used cpu by sql
select scheduler_id,cpu_id, status, is_online from sys.dm_os_schedulers where status='VISIBLE ONLINE'
--server physical memory
select physical_memory_in_bytes from sys.dm_os_sys_info
EXEC sys.sp_configure
--server available CPU
select cpu_count from sys.dm_os_sys_info
--server used cpu by sql
select scheduler_id,cpu_id, status, is_online from sys.dm_os_schedulers where status='VISIBLE ONLINE'
--server physical memory
select physical_memory_in_bytes from sys.dm_os_sys_info
Friday, October 15, 2010
Friday, October 01, 2010
Custom Reports Library - SQL Server
SQL Server Manageability Team Blog
http://blogs.msdn.com/b/sqlrem/
http://blogs.msdn.com/b/sqlrem/archive/2010/06/06/mdw-overview-report-for-data-collector-mdw-report-series-part-1.aspx
http://blogs.msdn.com/b/sqlrem/archive/2010/06/19/mdw-overview-report-for-data-collector-server-activity-mdw-report-series-part-2.aspx
http://blogs.msdn.com/b/sqlrem/archive/2010/06/21/server-activity-history-report-mdw-report-series-part-3.aspx
http://blogs.msdn.com/b/sqlrem/archive/2010/06/28/server-activity-history-report-mdw-report-series-part-4.aspx
http://blogs.msdn.com/b/sqlrem/
http://blogs.msdn.com/b/sqlrem/archive/2010/06/06/mdw-overview-report-for-data-collector-mdw-report-series-part-1.aspx
http://blogs.msdn.com/b/sqlrem/archive/2010/06/19/mdw-overview-report-for-data-collector-server-activity-mdw-report-series-part-2.aspx
http://blogs.msdn.com/b/sqlrem/archive/2010/06/21/server-activity-history-report-mdw-report-series-part-3.aspx
http://blogs.msdn.com/b/sqlrem/archive/2010/06/28/server-activity-history-report-mdw-report-series-part-4.aspx
Thursday, September 30, 2010
sp_who2 - in detail analysis with temp table
drop table #tmp
create table #tmp
(SPID int,
Status nvarchar(100),
Login nvarchar(100),
HostName nvarchar(100),
BlkBy nvarchar(100),
DBName nvarchar(100),
Command nvarchar(100),
CPUTime int,
DiskIO int,
LastBatch nvarchar(100),
ProgramName nvarchar(100),
SPID2 int,
REQUESTID int)
insert into #tmp
exec sp_who2 active
select * from #tmp order by DiskIO desc
select * from #tmp where Login !='sa' order by LastBatch asc
select * from #tmp where Status !='sleeping' and Login !='sa'
select * from #tmp where DBName = 'ces_flextronics'
--dbcc inputbuffer(492)
create table #tmp
(SPID int,
Status nvarchar(100),
Login nvarchar(100),
HostName nvarchar(100),
BlkBy nvarchar(100),
DBName nvarchar(100),
Command nvarchar(100),
CPUTime int,
DiskIO int,
LastBatch nvarchar(100),
ProgramName nvarchar(100),
SPID2 int,
REQUESTID int)
insert into #tmp
exec sp_who2 active
select * from #tmp order by DiskIO desc
select * from #tmp where Login !='sa' order by LastBatch asc
select * from #tmp where Status !='sleeping' and Login !='sa'
select * from #tmp where DBName = 'ces_flextronics'
--dbcc inputbuffer(492)
Finding the Lead Blocker in a Set of Blocking Process Reports
Script 1:
---------------------------------
SELECT sc.session_id AS UserSPID,
sr.blocking_session_id AS BlockingSPID,
DB_NAME(st.dbid) AS DatabaseNm,
ss.program_name AS AccessingProgram,
ss.login_name AS LoginNm,
OBJECT_NAME(st.objectid, st.dbid) AS ObjectNm,
CAST(st.text AS VARCHAR(MAX)) AS RequestSQL
--INTO #Processes
FROM sys.dm_exec_sessions ss
JOIN sys.dm_exec_connections sc ON ss.session_ID = sc.session_ID
LEFT JOIN sys.dm_exec_requests sr ON sc.connection_ID = sr.connection_ID
CROSS APPLY sys.dm_exec_sql_text (sc.most_recent_sql_handle) AS st
WHERE sc.session_id > 50 --gets us user processes
and isnull(sr.blocking_session_id,0) >0
Script 2:
---------------------------------
/*
--************************************************************************************
-- Description: This script list the current locking block chain.
-- It will create 1 function: fn_GetLockChainSubTree
-- 1 table: LockChain
--
--
-- Parameters: NONE
--
-- Returns: Locking block chain
--
-- Created: 9/07/2008 John Liu
--
-- Typical Usage: This script is typically used to find the block chain information
--
-- Comments: It also lists the dead lock (if any) at the end.
-- This script is for SQL 2005 and SQL 2008
--
--
-- Modifications:
--
-- Initials Date Description
-- -------- ---------- -----------------------------------------------------------
--
--************************************************************************************
*/
--***************************************Begin of create functions ********************************
--***************************************** Begin of dbo.fn_GetLockChainSubTree ************************
IF OBJECT_ID('dbo.fn_GetLockChainSubTree') IS NOT NULL DROP FUNCTION dbo.fn_GetLockChainSubTree
go
CREATE FUNCTION dbo.fn_GetLockChainSubTree(@Blocker AS INT)
RETURNS @TREE TABLE
(
Blocker INT NOT NULL,
Blocked INT NOT NULL,
lvl INT NOT NULL
)
AS
BEGIN
WITH Block_Subtree(Blocker,Blocked, lvl)
AS
(
-- Anchor Member (AM)
SELECT
Blocker
,Blocked
,0
FROM
LockChain
WHERE
Blocker = @Blocker
UNION all
-- Recursive Member (RM)
SELECT
l.Blocker
, l.Blocked
, t.lvl+1
FROM
LockChain AS l
INNER JOIN
Block_Subtree AS t
ON
l.Blocker = t.Blocked
)
INSERT INTO
@TREE
SELECT
*
FROM
Block_Subtree
RETURN
END
GO
--***************************************** End of dbo.fn_GetLockChainSubTree ************************
--***************************************End of create functions ********************************
--*************************************************************************************
--********************************** Main section **************************
--*************************************************************************************
-- -----------------------------------------------
-- Core processing
-- -----------------------------------------------
IF OBJECT_ID('LockChain') IS NOT NULL DROP TABLE LockChain
go
--initial the LockChain table
SELECT
Blocker = Blocking_Session_ID
,Blocked = Session_ID
INTO
LockChain
FROM
sys.dm_exec_requests
WHERE
Blocking_Session_ID <> 0
--list the locking chain
SELECT
t.*,s.host_name,s.program_name,s.login_name,s.nt_user_name
FROM
LockChain l
CROSS APPLY
fn_GetLockChainSubTree(l.Blocker) as t
INNER JOIN
sys.dm_exec_sessions s
ON
l.blocker = s.session_id
WHERE
l.blocker NOT IN (
SELECT
blocked
FROM
LockChain
)
--report any dead lock
SELECT
'Dead Lock' = 'Dead Lock'
,*
FROM
LockChain
WHERE
blocker IN (
SELECT DISTINCT
blocked
FROM
LockChain
)
AND
blocked IN (
SELECT DISTINCT
blocker
FROM
LockChain
)
--clean up
--DROP TABLE LockChain
--DROP FUNCTION dbo.fn_GetLockChainSubTree
-- -----------------------------------------------
-- End of Core processing
-- -----------------------------------------------
--***************************************** End of Main section **********************************
---------------------------------
SELECT sc.session_id AS UserSPID,
sr.blocking_session_id AS BlockingSPID,
DB_NAME(st.dbid) AS DatabaseNm,
ss.program_name AS AccessingProgram,
ss.login_name AS LoginNm,
OBJECT_NAME(st.objectid, st.dbid) AS ObjectNm,
CAST(st.text AS VARCHAR(MAX)) AS RequestSQL
--INTO #Processes
FROM sys.dm_exec_sessions ss
JOIN sys.dm_exec_connections sc ON ss.session_ID = sc.session_ID
LEFT JOIN sys.dm_exec_requests sr ON sc.connection_ID = sr.connection_ID
CROSS APPLY sys.dm_exec_sql_text (sc.most_recent_sql_handle) AS st
WHERE sc.session_id > 50 --gets us user processes
and isnull(sr.blocking_session_id,0) >0
Script 2:
---------------------------------
/*
--************************************************************************************
-- Description: This script list the current locking block chain.
-- It will create 1 function: fn_GetLockChainSubTree
-- 1 table: LockChain
--
--
-- Parameters: NONE
--
-- Returns: Locking block chain
--
-- Created: 9/07/2008 John Liu
--
-- Typical Usage: This script is typically used to find the block chain information
--
-- Comments: It also lists the dead lock (if any) at the end.
-- This script is for SQL 2005 and SQL 2008
--
--
-- Modifications:
--
-- Initials Date Description
-- -------- ---------- -----------------------------------------------------------
--
--************************************************************************************
*/
--***************************************Begin of create functions ********************************
--***************************************** Begin of dbo.fn_GetLockChainSubTree ************************
IF OBJECT_ID('dbo.fn_GetLockChainSubTree') IS NOT NULL DROP FUNCTION dbo.fn_GetLockChainSubTree
go
CREATE FUNCTION dbo.fn_GetLockChainSubTree(@Blocker AS INT)
RETURNS @TREE TABLE
(
Blocker INT NOT NULL,
Blocked INT NOT NULL,
lvl INT NOT NULL
)
AS
BEGIN
WITH Block_Subtree(Blocker,Blocked, lvl)
AS
(
-- Anchor Member (AM)
SELECT
Blocker
,Blocked
,0
FROM
LockChain
WHERE
Blocker = @Blocker
UNION all
-- Recursive Member (RM)
SELECT
l.Blocker
, l.Blocked
, t.lvl+1
FROM
LockChain AS l
INNER JOIN
Block_Subtree AS t
ON
l.Blocker = t.Blocked
)
INSERT INTO
@TREE
SELECT
*
FROM
Block_Subtree
RETURN
END
GO
--***************************************** End of dbo.fn_GetLockChainSubTree ************************
--***************************************End of create functions ********************************
--*************************************************************************************
--********************************** Main section **************************
--*************************************************************************************
-- -----------------------------------------------
-- Core processing
-- -----------------------------------------------
IF OBJECT_ID('LockChain') IS NOT NULL DROP TABLE LockChain
go
--initial the LockChain table
SELECT
Blocker = Blocking_Session_ID
,Blocked = Session_ID
INTO
LockChain
FROM
sys.dm_exec_requests
WHERE
Blocking_Session_ID <> 0
--list the locking chain
SELECT
t.*,s.host_name,s.program_name,s.login_name,s.nt_user_name
FROM
LockChain l
CROSS APPLY
fn_GetLockChainSubTree(l.Blocker) as t
INNER JOIN
sys.dm_exec_sessions s
ON
l.blocker = s.session_id
WHERE
l.blocker NOT IN (
SELECT
blocked
FROM
LockChain
)
--report any dead lock
SELECT
'Dead Lock' = 'Dead Lock'
,*
FROM
LockChain
WHERE
blocker IN (
SELECT DISTINCT
blocked
FROM
LockChain
)
AND
blocked IN (
SELECT DISTINCT
blocker
FROM
LockChain
)
--clean up
--DROP TABLE LockChain
--DROP FUNCTION dbo.fn_GetLockChainSubTree
-- -----------------------------------------------
-- End of Core processing
-- -----------------------------------------------
--***************************************** End of Main section **********************************
Subscribe to:
Posts (Atom)