Five DMV Queries That Will Make You A Superhero! | Glenn Berry's SQL Server Performance

Five DMV Queries That Will Make You A Superhero! | Glenn Berry's SQL Server Performance

SQL SERVER – 2005 – Database Table Partitioning Tutorial – How to Horizontal Partition Database Table « Journey to SQLAuthority

SQL SERVER – 2005 – Database Table Partitioning Tutorial – How to Horizontal Partition Database Table « Journey to SQLAuthority

Index fragmentation and re-indexing them

SET NOCOUNT ON;

declare @tbl nvarchar(100) = ''
if(OBJECT_ID('tempdb..#tmp')>1)
drop table #tmp

create table #tmp (servername nvarchar(100),dbname nvarchar(100), tblname nvarchar(100), indexid int, indexname nvarchar(1000), avg_fragmentation_in_percent numeric(15,10), string nvarchar(1000))

declare @mid int , @comid int
declare tmp_cur cursor for
select name From sys.tables where type_desc = 'USER_TABLE' and name not like '[__]%'

open tmp_cur
Fetch next from tmp_cur into @tbl

while @@fetch_status = 0
begin
--print @tbl

--dbcc showcontig(@tbl)
insert into #tmp
SELECT
@@servername,db_name(), @tbl, a.index_id, name, avg_fragmentation_in_percent,
(case
when avg_fragmentation_in_percent < 30 and avg_fragmentation_in_percent > 5 then 'ALTER INDEX '+name+' ON '+@tbl+' REORGANIZE ;'
when avg_fragmentation_in_percent > 30 then 'ALTER INDEX '+name+' ON '+@tbl+' REBUILD WITH (ONLINE=ON) ;'
else 'go'
end)
FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID(N'dbo.'+@tbl+''),
NULL, NULL, NULL) AS a
JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id;



Fetch next from tmp_cur into @tbl
--print '---'
end

close tmp_cur
deallocate tmp_cur

--select * from #tmp where string != 'go'
----------------------------------------------------

declare @string nvarchar(1000), @avgfrag numeric(20,15), @tblname nvarchar(100), @idx nvarchar(100)
declare tmp_cur cursor for
select string, avg_fragmentation_in_percent,tblname,indexname from #tmp where string != 'go'

open tmp_cur
Fetch next from tmp_cur into @string ,@avgfrag,@tblname,@idx

while @@fetch_status = 0
begin
print '--' +@tblname+' : '+@idx+' : '+convert(nvarchar(20),@avgfrag)

print @string
print 'go'
print 'dbcc dbreindex('''+@tblname+''','''',90)'
print 'go'

print '---'

Fetch next from tmp_cur into @string, @avgfrag,@tblname,@idx
end

close tmp_cur
deallocate tmp_cur

Microsoft SQL Server 2000 Index Defragmentation Best Practices

Microsoft SQL Server 2000 Index Defragmentation Best Practices

Reorganizing and Rebuilding Indexes

Reorganizing and Rebuilding Indexes

High Availability Options for SQL Server 2008

High Availability Options for SQL Server 2008

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

DBCC SHOWCONTIG (Transact-SQL)

DBCC SHOWCONTIG (Transact-SQL)

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

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