<body topmargin=0><script type="text/javascript"> function setAttributeOnload(object, attribute, val) { if(window.addEventListener) { window.addEventListener('load', function(){ object[attribute] = val; }, false); } else { window.attachEvent('onload', function(){ object[attribute] = val; }); } } </script> <div id="navbar-iframe-container"></div> <script type="text/javascript" src="https://apis.google.com/js/plusone.js"></script> <script type="text/javascript"> gapi.load("gapi.iframes:gapi.iframes.style.bubble", function() { if (gapi.iframes && gapi.iframes.getContext) { gapi.iframes.getContext().openChild({ url: 'https://www.blogger.com/navbar.g?targetBlogID\0751847318901978695218\46blogName\75Nick+Holmes+a+Court\46publishMode\75PUBLISH_MODE_HOSTED\46navbarType\75BLUE\46layoutType\75CLASSIC\46searchRoot\75http://www.nickhac.com/search\46blogLocale\75en\46v\0752\46homepageUrl\75http://www.nickhac.com/\46vt\0755069520967288021443', where: document.getElementById("navbar-iframe-container"), id: "navbar-iframe" }); } }); </script>

Wednesday, September 1, 2004

create procedure [dbo].[_DBA_Queries_LongestRunning]
 as

SELECT TOP 100
qs.total_elapsed_time / qs.execution_count / 1000000.0 AS average_seconds, qs.total_elapsed_time / 1000000.0 AS total_seconds,
qs.execution_count,
SUBSTRING (qt.text,qs.statement_start_offset/2,
(CASE WHEN qs.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
 ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) AS individual_query,
o.name AS object_name,
DB_NAME(qt.dbid) AS database_name
 FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
LEFT OUTER JOIN sys.objects o ON qt.objectid = o.object_id
where qt.dbid = DB_ID()
 ORDER BY average_seconds DESC;

GO

create procedure [dbo].[_DBA_Queries_MostIO]
 as

SELECT TOP 100
(total_logical_reads + total_logical_writes) / qs.execution_count AS average_IO,
(total_logical_reads + total_logical_writes) AS total_IO,
qs.execution_count AS execution_count,
SUBSTRING (qt.text,qs.statement_start_offset/2,
(CASE WHEN qs.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
 ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) AS indivudual_query,
o.name AS object_name,
DB_NAME(qt.dbid) AS database_name
 FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
LEFT OUTER JOIN sys.objects o ON qt.objectid = o.object_id
where qt.dbid = DB_ID()
 ORDER BY average_IO DESC;

GO

CREATE PROCEDURE [dbo].[_DBA_ViewBlockingTransactions] as

SELECT
st.text
, r.blocking_session_id
, r.session_id, r.status
, r.command
, r.cpu_time
, r.total_elapsed_time
FROM
sys.dm_exec_requests r
CROSS APPLY
sys.dm_exec_sql_text(sql_handle) AS st

GO

CREATE procedure [dbo].[_dba_ViewCurrentlyRunningQueries] as
SELECT sqltext.TEXT,
req.session_id,
req.status,
req.command,
req.cpu_time,
req.total_elapsed_time,
p.name
FROM sys.dm_exec_requests req
Left OUTER JOIN sys.database_principals p on req.user_id = p.principal_id
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext
GO

create procedure [dbo].[_DBA_Indexes_FindMissing]
as

SELECT
[Impact] = (avg_total_user_cost * avg_user_impact) * (user_seeks + user_scans),
[Table] = [statement],
[CreateIndexStatement] = 'CREATE NONCLUSTERED INDEX ix_'
+ sys.objects.name COLLATE DATABASE_DEFAULT
+ '_'
+ REPLACE(REPLACE(REPLACE(ISNULL(mid.equality_columns,'')+ISNULL(mid.inequality_columns,''), '[', ''), ']',''), ', ','_')
+ ' ON '
+ [statement]
+ ' ( ' + IsNull(mid.equality_columns, '')
+ CASE WHEN mid.inequality_columns IS NULL THEN '' ELSE
CASE WHEN mid.equality_columns IS NULL THEN '' ELSE ',' END
+ mid.inequality_columns END + ' ) '
+ CASE WHEN mid.included_columns IS NULL THEN '' ELSE 'INCLUDE (' + mid.included_columns + ')' END
+ ';',
mid.equality_columns,
mid.inequality_columns,
mid.included_columns
FROM sys.dm_db_missing_index_group_stats AS migs
INNER JOIN sys.dm_db_missing_index_groups AS mig ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details AS mid ON mig.index_handle = mid.index_handle
INNER JOIN sys.objects WITH (nolock) ON mid.OBJECT_ID = sys.objects.OBJECT_ID
WHERE (migs.group_handle IN
(SELECT TOP (500) group_handle
FROM sys.dm_db_missing_index_group_stats WITH (nolock)
ORDER BY (avg_total_user_cost * avg_user_impact) * (user_seeks + user_scans) DESC))
AND OBJECTPROPERTY(sys.objects.OBJECT_ID, 'isusertable') = 1
ORDER BY [Impact] DESC , [CreateIndexStatement] DESC

GO


CREATE procedure [dbo].[_DBA_IndexFragmentation] as
SELECT OBJECT_NAME(OBJECT_ID) AS Tablename,
s.name AS Indexname
,index_type_desc
,avg_fragmentation_in_percent
,page_count
,'Alter Index ' + s.name + ' on ' + OBJECT_NAME(OBJECT_ID) + ' REBUILD WITH (Online=on)' as SQLCommand
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, N'LIMITED') d
join sysindexes s ON d.OBJECT_ID = s.id
and d.index_id = s.indid
order by avg_fragmentation_in_percent desc
GO



CREATE Procedure [dbo].[_DBA_Indexes_RebuildFragmentedIndex]

as

Declare @Fragmentation decimal
Declare @TableName nvarchar(4000)
Declare @IndexName nvarchar(4000)

declare @TempDateTime as datetime
declare @IsError as bit
declare @ErrorText as nvarchar(500)


DECLARE curCampaignReProcess CURSOR FAST_FORWARD FOR

SELECT OBJECT_NAME(OBJECT_ID) AS Tablename,
s.name AS Indexname
,avg_fragmentation_in_percent

FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, N'LIMITED') d
join sysindexes s ON d.OBJECT_ID = s.id
and d.index_id = s.indid
where s.name is not null and s.name not like '%Log4Net%'
and avg_fragmentation_in_percent > 30
order by avg_fragmentation_in_percent desc

OPEN curCampaignReProcess
FETCH NEXT FROM curCampaignReProcess INTO @TableName, @IndexName, @Fragmentation
WHILE @@FETCH_STATUS = 0
BEGIN
---
BEGIN TRY


SET @TempDateTime = GETDATE()
set @IsError = 0
set @ErrorText = ''

IF @Fragmentation > 30
Begin
PRINT 'REBUILD INDEX ' + @IndexName + ' ' + cast(getdate() as nvarchar(50))
EXEC ('ALTER INDEX ' + @IndexName + ' ON ' + @TableName + ' REBUILD WITH (ONLINE=ON)')
END
IF @Fragmentation < 30 AND @Fragmentation > 5
Begin
PRINT 'REORGANISE INDEX ' + @IndexName + ' ' + cast(getdate() as nvarchar(50))
EXEC ('ALTER INDEX ' + @IndexName + ' ON ' + @TableName + ' REORGANISE WITH (ONLINE=ON)')
END
IF @Fragmentation < 5
Begin
PRINT 'SKIP REBUILD INDEX ' + @IndexName + ' ' + cast(getdate() as nvarchar(50))
END

END TRY
BEGIN Catch
SET @IsError = 1
PRINT ' ERROR ON  ' + @IndexName + ' ' + cast(getdate() as nvarchar(50))
PRINT ERROR_NUMBER()
Print ERROR_SEVERITY()
Print ERROR_STATE()
Print ERROR_PROCEDURE()
Print ERROR_LINE()
Print ERROR_MESSAGE()
SET @ErrorText = ERROR_MESSAGE()

END CATCH


print '------------------------'
---
FETCH NEXT FROM curCampaignReProcess INTO  @TableName, @IndexName, @Fragmentation
END
CLOSE curCampaignReProcess
DEALLOCATE curCampaignReProcess

GO




-- Show Size, Space Used, Unused Space, and Name of all database files
Create procedure [dbo].[_DBA_ViewDBFileUsage]
as

select
[FileSizeMB] =
convert(numeric(10,2),round(a.size/128.,2)),
[UsedSpaceMB] =
convert(numeric(10,2),round(fileproperty( a.name,'SpaceUsed')/128.,2)) ,
[UnusedSpaceMB] =
convert(numeric(10,2),round((a.size-fileproperty( a.name,'SpaceUsed'))/128.,2)) ,
[DBFileName] = a.name
from
sysfiles a
GO


CREATE procedure [dbo].[_DBA_ShrinkFilesInChunks]

as

/*
This script is used to shrink a database file in
increments until it reaches a target free space limit.

Run this script in the database with the file to be shrunk.
1. Set @DBFileName to the name of database file to shrink.
2. Set @TargetFreeMB to the desired file free space in MB after shrink.
3. Set @ShrinkIncrementMB to the increment to shrink file by in MB
4. Run the script
*/

declare @DBFileName sysname
declare @TargetFreeMB int
declare @ShrinkIncrementMB int

-- Set Name of Database file to shrink
set @DBFileName = 'YourDBName'

-- Set Desired file free space in MB after shrink
set @TargetFreeMB = 5000

-- Set Increment to shrink file by in MB
set @ShrinkIncrementMB = 200

-- Show Size, Space Used, Unused Space, and Name of all database files
select
[FileSizeMB] =
convert(numeric(10,2),round(a.size/128.,2)),
[UsedSpaceMB] =
convert(numeric(10,2),round(fileproperty( a.name,'SpaceUsed')/128.,2)) ,
[UnusedSpaceMB] =
convert(numeric(10,2),round((a.size-fileproperty( a.name,'SpaceUsed'))/128.,2)) ,
[DBFileName] = a.name
from
sysfiles a

declare @sql varchar(8000)
declare @SizeMB int
declare @UsedMB int

-- Get current file size in MB
select @SizeMB = size/128. from sysfiles where name = @DBFileName

-- Get current3 space used in MB
select @UsedMB = fileproperty( @DBFileName,'SpaceUsed')/128.

select [StartFileSize] = @SizeMB, [StartUsedSpace] = @UsedMB, [DBFileName] = @DBFileName

-- Loop until file at desired size
while  @SizeMB > @UsedMB+@TargetFreeMB+@ShrinkIncrementMB
begin

set @sql =
'dbcc shrinkfile ( '+@DBFileName+', '+
convert(varchar(20),@SizeMB-@ShrinkIncrementMB)+' ) '

print 'Start ' + @sql
print 'at '+convert(varchar(30),getdate(),121)

exec ( @sql )

print 'Done ' + @sql
print 'at '+convert(varchar(30),getdate(),121)

-- Get current file size in MB
select @SizeMB = size/128. from sysfiles where name = @DBFileName

-- Get current space used in MB
select @UsedMB = fileproperty( @DBFileName,'SpaceUsed')/128.

select [FileSize] = @SizeMB, [UsedSpace] = @UsedMB, [DBFileName] = @DBFileName

end

select [EndFileSize] = @SizeMB, [EndUsedSpace] = @UsedMB, [DBFileName] = @DBFileName

-- Show Size, Space Used, Unused Space, and Name of all database files
select
[FileSizeMB] =
convert(numeric(10,2),round(a.size/128.,2)),
[UsedSpaceMB] =
convert(numeric(10,2),round(fileproperty( a.name,'SpaceUsed')/128.,2)) ,
[UnusedSpaceMB] =
convert(numeric(10,2),round((a.size-fileproperty( a.name,'SpaceUsed'))/128.,2)) ,
[DBFileName] = a.name
from
sysfiles a


GO

Posted by Nick HaC @ 10:07 PM


1 Comments:
At March 8, 2012 at 2:36 AM, Anonymous Anonymous said...


Couple flaws here:

"REORGANIZE" not "REORGANISE" (throws error) and nothing under 30 % fragmentation gets reorganized because of this line:

"and avg_fragmentation_in_percent > 30"

Change that to > 0, you get both reindex and reorganize'd indexes.

*shrug*

 

Post a Comment

<< Home



Copyright 1998-2010 Nick Holmes a Court