Blocked Process Report is a new concept that was introduced in SQL server 2005 onwards. It is an enhancement added to the SQL server profiler.
The Blocked Process Report event class in SQL Server profiler indicates that a task has been blocked for more than a specified amount of time. This event class does not include system tasks or tasks that are waiting on non deadlock-detectable resources.
How to Use:-
To configure the threshold value for Blocked Process Report, use sp_configure.
At first we have to enable the advanced configuration options.
This can be done using the below query
EXEC sp_configure 'show advanced options',1
GO
RECONFIGURE
GO
Then configure the Blocked Process Threshold value. This value will be in seconds. The below query sets the “Blocked Process Threshold” value to 5 seconds.
By default the Threshold value will be 0.
EXEC sp_configure 'blocked process threshold',5
GO
RECONFIGURE
GO
To generate the report, use profiler.
1. Start the Profiler
2. Connect to the server
3. In the Trace Properties, go to Event Selection tab and check the option “Show all events“
Introduction:
A SQL Server CMS is just a central repository that holds a list of managed servers. SQL Server 2008 SSMS introduces a new feature, Multiple Server Query Execution, in Query Editor. Centralize the management and administration of a number of SQL Server instances from a single source can allow the DBA to save significant time and effort. This feature intends to increase the productivity of running same query against multiple servers at once. Some of usage includes:
• Configure group of servers
• Generate report or document from multiple servers
• Analyze result from multiple servers
• Run any SQL query against multiple servers
Pre-requisite:
1. SQL Server 2008 for Registering Central Management Server
2. SQL Server 2008 or SQL Server 2005 or SQL Server 2000 servers other than the registered Central Management Server.
To deploy or test Multiple Server Query Execution, you need to setup central management server in SSMS in SQL Server 2008.
How to:
To register a central management server and run the query against all the registered servers follow the below steps.
1. Open the “Registered Servers” from the “View” Menu in the management studio of SQL server 2008.
DBCC DBREINDEX rebuilds one or more indexes for a table in the specified database.
This was the command which we used to rebuild the indexes.
This feature will be removed in the next version (after 10.0 or SQL Server 2008) of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use Alter INDEX command to reorganize or rebuild indexes instead.
Below query would reorganize the table if table fragementation is between 10.0 to 30.0 and Rebuild the index if table fragementation is above 30.0
use DB_name
go
SET NOCOUNT ON;
DECLARE @objectid int;
DECLARE @indexid int;
DECLARE @partitioncount bigint;
DECLARE @schemaname nvarchar(130);
DECLARE @objectname nvarchar(130);
DECLARE @indexname nvarchar(130);
DECLARE @partitionnum bigint;
DECLARE @partitions bigint;
DECLARE @frag float;
DECLARE @command nvarchar(4000);
SELECT
object_id AS objectid,
index_id AS indexid,
partition_number AS partitionnum,
avg_fragmentation_in_percent AS frag
INTO #Temp
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL,'LIMITED')
WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;
DECLARE partitions CURSOR FOR SELECT * FROM #Temp
OPEN partitions
WHILE (1=1)
BEGIN
FETCH NEXT
FROM partitions
INTO @objectid, @indexid, @partitionnum, @frag;
IF @@FETCH_STATUS < 0 BREAK;
SELECT @objectname = QUOTENAME(o.name), @schemaname =QUOTENAME(s.name)
FROM sys.objects AS o
JOIN sys.schemas as s ON s.schema_id = o.schema_id
WHERE o.object_id = @objectid;
SELECT @indexname = QUOTENAME(name)
FROM sys.indexes
WHERE object_id = @objectid AND index_id = @indexid;
SELECT @partitioncount = count (*)
FROM sys.partitions
WHERE object_id = @objectid AND index_id = @indexid;
IF @frag < 30.0
SET @command = N'ALTER INDEX ' + @indexname + N' ON ' +@schemaname + N'.' + @objectname + N' REORGANIZE';
IF @frag >= 30.0
SET @command = N'ALTER INDEX ' + @indexname + N' ON ' +@schemaname + N'.' + @objectname + N' REBUILD';
IF @partitioncount > 1
SET @command = @command + N' PARTITION=' +CAST(@partitionnum AS nvarchar(10));
EXEC (@command);
PRINT N'Executed: ' + @command;
END;
CLOSE partitions;
DEALLOCATE partitions;
DROP TABLE #Temp
GO
Note: Rebuilding a clustered index does not rebuild associated non-clustered indexes unless the keyword ALL is specified
When ALL is specified, all indexes on the table are dropped and rebuilt in a single transaction.
USE DB_name;
GO
ALTER INDEX ALL ON schema.table_name
REBUILD WITH (SORT_IN_TEMPDB = ON,
STATISTICS_NORECOMPUTE = ON);
GO
This piece of information was shared to me by Kumaravyas.
Thanks to Kumaravyas for sharing this useful info.
To Block this Place for your ad Contact Me @
_507_Rez.jpg)
