Posts

SQL Server Database Index optimization – maintenance plan

/in , , /by

Properly optimized table indexes are crucial in database performance tuning. This is especially true when the database is growing overtime causing indexes to be fragmented. The more an index is fragmented, the longer time is needed for database reads.

When should the indexes be rebuilt? Microsoft recommends to reorganize indexes when fragmentation level is between 5% and 30%. When fragmentation is above 30% then the index should be rebuilt.

The best way of ensuring low fragmentation levels is to setup maintenance plan for the database that will be triggered periodically (preferably at night). The script below can be used in scheduled job. You can use @PrintInformationOnly param to print the index information first, for all database tables. When you set it to 0, the actual rebuild or reorganize process will start. I have also added @ExcludeTableList to exclude some tables that should be skipped (for example are too big or not used).

DECLARE	@return_value int

EXEC	@return_value = [dbo].[IndexOptimize]
		@databaseName = N'myDatabase',
		@IndexFragmentationMin = 5,
		@IndexFragmentationRebuildMin = 30,
		@PrintInformationOnly = 1

SELECT	'Return Value' = @return_value

The actual script starts here. You may want to adjust FILLFACTOR params separately for each tables as this should be configured depending on data update frequency. By setting MAXDOP= 1 param, we disable parallel threads during rebuild process, so the server CPU will not go to high.

I have successfully tested this script on database of size about 150 GB. Be aware that initial index rebuild may take a few hours, depending on fragmentation levels.

 
CREATE PROCEDURE [dbo].[IndexOptimize] 
@databaseName varchar(200) = 'MyTable',
@IndexFragmentationMin int = 5,
@IndexFragmentationRebuildMin int = 30,
@skipLargeTables bit = 0,
@ExcludeTableList nvarchar(max) = 'MyBigTable1,MyBigTable2',
@PrintInformationOnly bit = 0
AS
BEGIN

BEGIN TRY
	declare @dbid int = 0;
	set @dbid = (select [dbid] from [Master].dbo.[sysdatabases] where name = @databaseName);
    ------------------
	DECLARE @Schema VARCHAR(200);  
	DECLARE @Table NVARCHAR(200);  
	DECLARE @Index VARCHAR(200);  
	DECLARE @avg_fragmentation_in_percent int; 
	DECLARE @page_count int; 

	DECLARE db_cursor CURSOR FOR  
		SELECT 
		dbschemas.[name] as 'Schema', 
		dbtables.[name] as 'Table', 
		dbindexes.[name] as 'Index',
		indexstats.avg_fragmentation_in_percent,
		indexstats.page_count
		FROM sys.dm_db_index_physical_stats (@dbid, NULL, NULL, NULL, NULL) AS indexstats
		INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
		INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
		INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
		AND indexstats.index_id = dbindexes.index_id
		WHERE dbindexes.[name] is not null and indexstats.database_id = @dbid  
		ORDER BY indexstats.avg_fragmentation_in_percent desc

	OPEN db_cursor   
	FETCH NEXT FROM db_cursor INTO @Schema, @Table, @Index, @avg_fragmentation_in_percent, @page_count 

	WHILE @@FETCH_STATUS = 0   
	BEGIN  
	   if(@skipLargeTables = 1 and CHARINDEX(@Table, @ExcludeTableList) > 0)
	   begin
		 print 'skipped index (' + CONVERT(varchar(10),@avg_fragmentation_in_percent) + '%) ' + @Index + ' for table ' + @Table + ', page count ' + CONVERT(varchar(10),@page_count)
	   end
	   else
	   begin
			if(@avg_fragmentation_in_percent <= @IndexFragmentationRebuildMin and @avg_fragmentation_in_percent > @IndexFragmentationMin and LEN(@Index) > 3)
			begin
			   print 'reorganizing index (' + CONVERT(varchar(10),@avg_fragmentation_in_percent) + '%) ' + @Index + ' for table ' + @Table + ', page count ' + CONVERT(varchar(10),@page_count)
			  
			   if(@PrintInformationOnly = 0)
			   begin
			      exec('ALTER INDEX ' + @Index + ' ON dbo.' + @Table + ' REORGANIZE;');
			   end
			end
		    
			if(@avg_fragmentation_in_percent > @IndexFragmentationRebuildMin and LEN(@Index) > 3)
			begin
			   print 'rebuilding index (' + CONVERT(varchar(10),@avg_fragmentation_in_percent) + '%) ' + @Index + ' for table ' + @Table + ', page count ' + CONVERT(varchar(10),@page_count)
			  
			   if(@PrintInformationOnly = 0)
			   begin
			      exec('ALTER INDEX ' + @Index + ' ON dbo.' + @Table + ' REBUILD WITH (MAXDOP=1,FILLFACTOR = 90)');
			   end
			end
		end
			
		FETCH NEXT FROM db_cursor INTO @Schema, @Table, @Index, @avg_fragmentation_in_percent, @page_count  
	END   

	CLOSE db_cursor   
	DEALLOCATE db_cursor
	
	END TRY 
BEGIN CATCH	
	DECLARE @ErrorMessage NVARCHAR(4000);
	DECLARE @ErrorSeverity INT;
	DECLARE @ErrorState INT;
	SELECT @ErrorMessage = 'Message: '+ ERROR_MESSAGE() + CHAR(13) + CHAR(10) + 
						   'Procedure: ' + CAST(ERROR_PROCEDURE() AS VARCHAR(MAX)) + CHAR(13) + CHAR(10) +  
						   'Line: ' + CAST(ERROR_LINE() AS VARCHAR(MAX)) + CHAR(13) + CHAR(10) +  
						   'Number: ' + CAST(ERROR_NUMBER() AS VARCHAR(MAX)),  @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE();	

	RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
	RETURN 0
END CATCH

END

Microsoft-SQL-Server

1 Star2 Stars3 Stars4 Stars5 Stars (1 votes, average: 4.00 out of 5)
Loading...Loading...

Terminate background stored procedure session

/in /by

If you are executing background stored procedure, for example when run asynchronously by the Service Broker, there is often a need to stop the process, especially when it is time consuming operation. In order to do that, we have to find the session id of the process we want and then use KILL function to terminate it.

In order to display currently running queries let’s execute following. 

 SELECT sqltext.TEXT as SQLText,
	req.session_id,
	req.database_id,
	req.status,
	req.command,
	req.cpu_time,
	req.total_elapsed_time
	FROM sys.dm_exec_requests req
	CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext

After we’ve seen how to display running queries, we can now create stored procedure allowing us to stop an session id related to the query name we want.

 CREATE PROCEDURE [dbo].[proc_com_KillBackgroundProcedure] (
 @DatabaseName nvarchar(50) = '',
 @ProcedureName nvarchar(50) = ''
)
AS

declare @SessionToBeKilled int = null;
declare @ProcessName nvarchar(max) = '';

declare dataset cursor for

select SQLText, session_id
from (SELECT sqltext.TEXT as SQLText,
	req.session_id,
	req.database_id,
	req.status,
	req.command,
	req.cpu_time,
	req.total_elapsed_time
	FROM sys.dm_exec_requests req
	CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext
	inner join master.dbo.sysdatabases m on m.dbid = database_id
	where m.name = @databasename and req.status = 'background' 
        and [TEXT] like '%' + @ProcedureName + '%'
	) as T

open dataset
fetch next from dataset into  @ProcessName, @SessionToBeKilled
while @@fetch_status = 0
begin
 
  EXEC('KILL ' + @SessionToBeKilled);
  print 'killed: ' + convert(varchar(max),@SessionToBeKilled);

fetch next from dataset into  @ProcessName, @SessionToBeKilled
end
close dataset
deallocate dataset

Above query simply gets the session id by the procedure name and then just kills the process. Be careful to not to kill an system query. I hope this has been useful for you 🙂

1 Star2 Stars3 Stars4 Stars5 Stars (No Ratings Yet)
Loading...Loading...

Using mssql Service Broker in Service-Oriented Architecture

/in /by

Main principle of SOA is separation between the service interface and it’s implementation. In order to achieve that separation on the database level we can use Mssql Service Broker messaging.

Using Service Broker allows us to send internal or external messages between or withing the mssql database. We can also use the massages to activate stored procedure and execute tasks or even external web service by invoking c# custom assembly method. Internal messaging lets us trigger asynchronous events that we can use to perform time consuming operations or integration tasks (for example by activation database trigger that starts the event chain).

In our example I will show you how to execute time consuming task asynchronously. It is especially useful when you need to calculate a lot of data when some table content changes (trigger events).

Below is the example of triggering time consuming procedure synchronously and asynchronously using Service Broker.

servicebroker

In order to implement our solution we need to first create self activating stored procedure that will be activated by service broker queue each time we send the message to.

 CREATE PROCEDURE proc_BrokerTargetActivProc
 AS
  DECLARE @RecvReqDlgHandle UNIQUEIDENTIFIER;
  DECLARE @RecvReqMsg NVARCHAR(100);
  DECLARE @RecvReqMsgName sysname;

  WHILE (1=1)
  BEGIN

    BEGIN TRANSACTION;

    WAITFOR
    ( RECEIVE TOP(1)
        @RecvReqDlgHandle = conversation_handle,
        @RecvReqMsg = message_body,
        @RecvReqMsgName = message_type_name
      FROM TASK_QUEUE
    ), TIMEOUT 5000;

    IF (@@ROWCOUNT = 0)
    BEGIN
      ROLLBACK TRANSACTION;
      BREAK;
    END
     
    declare @targetpProcedure nvarchar(100);
    set @targetpProcedure = CAST(@RecvReqMsg AS nvarchar(100))
    
    IF LEN(@targetpProcedure) > 0
    BEGIN
        --execute target procedure (no need to send the reply)
       exec @targetpProcedure;
     
    END
    ELSE IF @RecvReqMsgName =
        N'https://schemas.microsoft.com/SQL/ServiceBroker/EndDialog'
    BEGIN
       END CONVERSATION @RecvReqDlgHandle;
    END
    ELSE IF @RecvReqMsgName =
        N'https://schemas.microsoft.com/SQL/ServiceBroker/Error'
    BEGIN
       END CONVERSATION @RecvReqDlgHandle;
    END
      
    COMMIT TRANSACTION;

  END
GO

When we have the activation procedure ready, the next step is to create basic Service Broker objects we will use when sending the messages.

CREATE MESSAGE TYPE [//SBM/RequestMessage] VALIDATION=WELL_FORMED_XML; 
CREATE MESSAGE TYPE [//SBM/ReplyMessage] VALIDATION=WELL_FORMED_XML;

CREATE CONTRACT [//SBM/MSGContract]
(
 [//SBM/RequestMessage]
 SENT BY INITIATOR 
  ,[//SBM/ReplyMessage]
 SENT BY TARGET 
);  

CREATE QUEUE TASK_QUEUE
 WITH STATUS=ON,
 ACTIVATION (
  PROCEDURE_NAME = proc_BrokerTargetActivProc,
  MAX_QUEUE_READERS = 5,
  EXECUTE AS SELF);

CREATE SERVICE [//SBM/TargetService] ON QUEUE TASK_QUEUE([//SBM/MSGContract]);
CREATE SERVICE [//SBM/InitService] ON QUEUE TASK_QUEUE([//SBM/MSGContract]);

The final step is to create helper procedure that will be sending activating messages to the queue passing in the target procedure’s name to be executed.

CREATE PROCEDURE proc_ExecuteProcedureAsync
 @ProcedureName nvarchar(100) = ''
 AS

DECLARE @InitDlgHandle UNIQUEIDENTIFIER;
DECLARE @RequestMsg xml;
BEGIN TRANSACTION;
BEGIN DIALOG @InitDlgHandle
     FROM SERVICE
      [//SBM/InitService]
     TO SERVICE
      N'//SBM/TargetService'
     ON CONTRACT
      [//SBM/MSGContract]
     WITH
         ENCRYPTION = OFF;
 
 SELECT @RequestMsg = @ProcedureName;
       
--Send the Message
SEND ON CONVERSATION @InitDlgHandle MESSAGE TYPE [//SBM/RequestMessage](@RequestMsg);

--Close conversation
END CONVERSATION @InitDlgHandle;

COMMIT TRANSACTION;

I have included complete sql script below that will create all structures and procedures for you. Give it a try.
SetUpServiceBroker1

1 Star2 Stars3 Stars4 Stars5 Stars (No Ratings Yet)
Loading...Loading...

MSSQL database backup/ restore helper scripts

/in , /by

When working with MSSQL server I have spotted some general problems during restoring databases. Below I’m presenting some common solutions that can save you development time.

If you are restoring database backup taken from the same database but running on different server eg. restoring production database on your test server – you may get security error when trying to connect with the old DB user.
This is because DB user account was generated on different server where windows security token was different. The solution is to recreate the user on restored database:

//recreate user
ALTER User DB_user with LOGIN = DB_user;

//set password
ALTER LOGIN DB_user WITH PASSWORD = 'secret'

If you must overwrite you test database with the different one, you need to first close all current database connections, otherwise you may get an error saying that database is in use. The quick solution for that is the script below.
It simply sets database off-line and back on-line. This is sometimes much faster then waiting for all connections to be closed in other ways (note: use it only on the test server)

ALTER DATABASE [MyDB]
SET OFFLINE WITH ROLLBACK IMMEDIATE
ALTER DATABASE [MyDB]
SET ONLINE
GO

If you will get following error: “Cannot execute as the database principal because the principal “dbo” does not exist, this type of principal cannot be impersonated, or you do not have permission”. This is solution: 

 ALTER AUTHORIZATION ON DATABASE::[myDatabase] TO [sa];

If your database backup takes a lot of space but you know that there is not too much data in it, you can simply shrink the log files before creating a backup.

Use MyDB
ALTER DATABASE MyDB
SET RECOVERY SIMPLE;
GO

DBCC SHRINKFILE ('MyDB_log',TRUNCATEONLY);
GO

ALTER DATABASE MyDB
SET RECOVERY FULL;

Finally, if you want to grant execute permissions to all stored procedures at once (on the restored database), you can use following script:

 CREATE PROCEDURE [dbo].[procExecuteOnProcs] (
   @User varchar(max)
 )
 AS
  declare procs cursor for
  select routine_schema, routine_name from information_schema.routines where routine_type = N'PROCEDURE'

  declare @Schema nvarchar(128), @Name nvarchar(128), @SQL nvarchar(512)

  open procs
  fetch next from procs into @Schema, @Name
  while @@fetch_status = 0
  begin
    set @SQL = N'grant execute on [' + @Schema + N'].[' + @Name + N'] to ' + @User
    exec sp_ExecuteSQL @SQL
    fetch next from procs into @Schema, @Name
  end
  close procs
  deallocate procs

Hope these simple tricks will help you. Please note that you can also automate above scripts to be part of other processes.

1 Star2 Stars3 Stars4 Stars5 Stars (No Ratings Yet)
Loading...Loading...

Finding non empty data periods T-SQL

/in , /by

Ever wanted to find non empty periods of data using T-SQL? I this article I will show you how to do it.
Example below shows the periods of days in which there have been at least one order. The days without orders are not being shown. This is very useful when creating advanced sales reports. Having non empty periods we can calculate additional values such as average sales in these ranges etc.

The code below simply gets all distinct dates then creates two partitions and joins them together.
In each partition the data are joined with each other using left and right join. The non null values are next being used in the final inner join.

 with temp(d) as 
 ( 
  select distinct CONVERT(date,OrderDate) d from tblOrders
 )
 select a1.id as id, a1.d as [from], a2.d as [to] from 
 ( 
   select b.d, ROW_NUMBER() over (partition by 1 order by b.d) id from temp a 
   right join temp b on dateadd(day,1,a.d) = b.d 
   where a.d is null 
 )
 a1 
 inner join  
 ( 
   select a.d, ROW_NUMBER() over (partition by 1 order by b.d) id from temp a 
   left join temp b on dateadd(day,1,a.d) = b.d 
   where b.d is null 
 ) 
 a2 on a1.id = a2.id

sql-periods1

In order to calculate sales data in periods, simply join the final results using found dates. Hope I helped you with this sample. Happy coding 🙂

1 Star2 Stars3 Stars4 Stars5 Stars (No Ratings Yet)
Loading...Loading...

Calculate median in stored procedure (T-SQL)

/in /by

When creating reports you may want to calculate median value from the subset of data. The most efficient way to do it is to use stored procedure. To get the median value we simply need to order the data by the sought value and take the number from the middle.

The code below does it’s job. When executing the script the in-memory subset of data is created, then it is being sorted and divided by 50%. Next we get the maximum value from 50% so it is the median value.

 declare @median decimal(18,2);
 with temp as
 ( 
     select dMonthIncomeNetto as salary from tblUserSalaryData 
 )
 SELECT @median = MAX(salary) FROM (SELECT TOP 50 PERCENT salary FROM temp ORDER BY salary) AS H1;

 select @median as median;

median-sql

Calculating median value consumes quite a lot of server resources, so make sure you are calculating it when really needed. It is considered a good practice to store aggregated value of median and recalculate it periodically depending on your requirements.

1 Star2 Stars3 Stars4 Stars5 Stars (No Ratings Yet)
Loading...Loading...