Saturday 5 October 2013

MSDTC (Distributed Transaction Coordinator)

Few quick notes about MSDTC:

  1. The default full path is (C:\Windows\System32\msdtc.exe)
  2. To start up, use (Net Start MSDTC)
  3. To stop, use (Net Stop MSDTC)
  4. Window service name is (Distributed Transaction Coordinator)
  5. To configure it properly for sql server, we have to enable network DTC access
    1. use (DCOMCNFG)
    2. select (Console Root\Component Services\Distributed Transaction Cooridinator\Loacl DTC\Properties\Security\Security Settings)
    3. Enable "Network DTC Access" and allow remote clients
Posted by at No comments:

Wednesday 28 August 2013

T-SQL example to insert result set returned from remote linked server procedure call to a temporary table

declare @parameter1 int, @parameter2 date
select @parameter1=123, @parameter2='2013-08-28'
IF (object_id('tempdb..#TempTable')) is not null
BEGIN
drop table  #TempTable
END
CREATE TABLE #TempTable(col1 int, col2 varchar(50), primary key clustered(col1))
INSERT INTO #TempTable
EXECUTE [LinkedServerName].[DatabaseName].[SchemaName].[usp_StoredProcedure] @parameter1=@parameter1,  @parameter2=@parameter2
SELECT * FROM #TempTable
Posted by at No comments:

Thursday 8 August 2013

T-SQL funciton to calculate CUSIP check digit

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_GetCusipCheckDigit]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fn_GetCusipCheckDigit]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

---------------------------------------------------------------------------------------------------------------
-- Author Date
-- Steven Rao 2013-08-08
---------------------------------------------------------------------------------------------------------------
-- Return the check digit for a CUSIP number
---------------------------------------------------------------------------------------------------------------
-- Reference:
-- http://social.msdn.microsoft.com/Forums/sqlserver/en-US/c55474df-6908-4d05-bf23-7b341bd7994f/user-defined-function
-----------------------------------------------------------------------------------------------------------------
-- Test script:
--DECLARE @Sample TABLE ( Cusip CHAR(9) );
--INSERT INTO @Sample
--VALUES ('14149YAR9')
-- ,('000020347')
-- ,('126650BG4')
-- ,('254709AC2')
-- ,('437076AQ5') ;
--SELECT *, dbo.[fn_GetCusipCheckDigit](LEFT(Cusip, 8)) as CheckDigit FROM  @Sample ;
-----------------------------------------------------------------------------------------------------------------
CREATE FUNCTION [dbo].[fn_GetCusipCheckDigit]
(
@Cusip CHAR(8)
)
RETURNS CHAR(1)
AS
BEGIN
RETURN
(
SELECT (10 - SUM(s/10 + S%10) % 10) % 10
FROM (
SELECT s=(NULLIF(CHARINDEX(substring(@Cusip, V.number, 1), R.Ref),0) -1) * (2 - V.number % 2)
FROM master..spt_values V CROSS JOIN
(SELECT Ref='0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ*@#') R
WHERE V.type='P' and V.number BETWEEN 1 AND 8
) SQ
)
END

GO


Posted by at No comments:

Saturday 13 July 2013

Enable/Disable a table to be read only on the fly (usp_Util_SetTableReadOnly)

published at:
http://www.sqlservercentral.com/articles/read+only/100618/

USE TEST
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_Util_SetTableReadOnly]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp_Util_SetTableReadOnly]
GO
-- =================================================================================================
--
--  Author Date Description
--  ================================================================================================
--  Steven Rao (StevenZRAO@gmail.com) 2013-07-13 original draft
--  ================================================================================================
--  PURPOSE: Enable/Disable read only property to any give table
-- =================================================================================================
-- Reference:
-- http://www.mssqltips.com/sqlservertip/2711/different-ways-to-make-a-table-read-only-in-a-sql-server-database/
-- http://sqlblogcasts.com/blogs/tonyrogerson/archive/2007/08/26/how-to-make-a-table-read-only-in-sql-server.aspx
-- http://www.sqlservercentral.com/articles/Advanced+Querying/readonlytables/2517/
-- =================================================================================================
CREATE PROCEDURE [dbo].[usp_Util_SetTableReadOnly]
@tableName varchar(50)
,@readOnly tinyint=1
,@schemaName varchar(50)='dbo'
,@debug tinyint=0
AS
BEGIN
if (@readOnly NOT IN (0, 1)) return 10
declare @error int, @rowsCount int, @now datetime2
--
-- verify schema and table name, also trim them if needed
-- warning: don't swtich the order of assignment here!
select
@tableName=ltrim(rtrim(@tableName))
,@schemaName=ltrim(rtrim(@schemaName))
,@tableName=t.name
from sys.schemas s inner join sys.tables t
on s.schema_id=t.schema_id
where s.name=@schemaName
and t.name=@tableName
select @error=@@ERROR, @rowsCount=@@ROWCOUNT, @now=SYSDATETIME()
if (@error<>0) RETURN 20
if (@rowsCount<>1) RETURN 30
declare @createCheckConstraint varchar(8000), @dropCheckConstraint varchar(8000), @createDeleteTrigger varchar(8000), @dropDeleteTrigger varchar(8000)
if (@readOnly=1)
begin
select
@createCheckConstraint='IF NOT EXISTS (SELECT * FROM sys.check_constraints WHERE object_id = OBJECT_ID(N'
+''''+'['+@schemaName+'].[CK_'+@tableName+'_ReadOnly]'+''''+') AND parent_object_id = OBJECT_ID(N'
+''''+'['+@schemaName+'].['+@tableName+']'+''''+'))'+char(13)+
+'ALTER TABLE ['+@schemaName+'].['+@tableName+']  WITH NOCHECK ADD  CONSTRAINT [CK_'+@tableName+'_ReadOnly] CHECK  (1=0)'
,@createDeleteTrigger='IF  NOT EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'+''''+'['+@schemaName+'].[trg_'+@tableName+'_DisableDelete]'+''''+'))'+char(13)
+'EXEC dbo.sp_executesql @statement =N'+
+''''+'CREATE TRIGGER ['+@schemaName+'].[trg_'+@tableName+'_DisableDelete] ON '+'['+@schemaName+'].['+@tableName+']'+char(13)
+'INSTEAD OF DELETE'+char(13)
+'AS'+char(13)
+'BEGIN'+char(13)
+'RAISERROR( '+''''+''''+'Deletion of table '+@tableName+' not allowed.'+''''+''''+', 16, 1 )'+char(13)
+'ROLLBACK TRANSACTION'+char(13)
+'END'+''''

if (@debug=1)
begin
print convert(varchar, @now, 126)+ ' @createCheckConstraint='+@createCheckConstraint+' @createDeleteTrigger='+@createDeleteTrigger
end

exec (@createCheckConstraint)
select @error=@@ERROR, @now=SYSDATETIME()
if (@error<>0)
begin
print convert(varchar, @now, 126)+ ' failed to create check constraint. will return.'
return 110
end

exec (@createDeleteTrigger)
select @error=@@ERROR, @now=SYSDATETIME()
if (@error<>0)
begin
print convert(varchar, @now, 126)+ ' failed to create trigger for delete. will return.'
return 120
end

end
else
begin
select
@dropCheckConstraint='IF  EXISTS (SELECT * FROM sys.check_constraints WHERE object_id = OBJECT_ID(N'
+''''+'['+@schemaName+'].[CK_'+@tableName+'_ReadOnly]'+''''+') AND parent_object_id = OBJECT_ID(N'+''''+'['+@schemaName+'].['+@tableName+']'+''''+'))'+char(13)
+'ALTER TABLE ['+@schemaName+'].['+@tableName+'] DROP CONSTRAINT [CK_'+@tableName+'_ReadOnly]'
,@dropDeleteTrigger='IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'+''''+'['+@schemaName+'].[trg_'+@tableName+'_DisableDelete]'+''''+'))'+char(13)
+'DROP TRIGGER ['+@schemaName+'].[trg_'+@tableName+'_DisableDelete]'

if (@debug=1)
begin
print convert(varchar, @now, 126)+ ' @dropCheckConstraint='+@dropCheckConstraint+' @dropDeleteTrigger='+@dropDeleteTrigger
end

exec (@dropCheckConstraint)
select @error=@@ERROR, @now=SYSDATETIME()
if (@error<>0)
begin
print convert(varchar, @now, 126)+ ' failed to drop check constraint. will return.'
return 130
end

exec (@dropDeleteTrigger)
select @error=@@ERROR, @now=SYSDATETIME()
if (@error<>0)
begin
print convert(varchar, @now, 126)+ ' failed to drop trigger for delete. will return.'
return 140
end
end

return 0
END

Posted by at No comments:

Tuesday 18 June 2013

fn_Util_IsTablePartitionedAtValue: checking if a specific value is at the boundary point of table partition


---------------------------------------------------------------------------------------------------------------
-- Author    Date   
-- Steven Rao   2013-06-18  
---------------------------------------------------------------------------------------------------------------
-- Assumption:
-- 1) if table is partitioned, it will be using LEFT range type
-- 2) partition function used has parameter of integer data type
-- 3) return value:
-- 1: table @tableName is partitioned and @partitionValue is one of the partition boundry point
--  0: table @tableName is partitioned but @partitionValue is not a partition boundary point
--  -1: table @tableName is not partioned at all
---------------------------------------------------------------------------------------------------------------
-- Usage example
-- SELECT [fn_Util_IsTablePartitionedAtValue] ('MyTable', 1000000, 'dbo')
---------------------------------------------------------------------------------------------------------------
CREATE FUNCTION [fn_Util_IsTablePartitionedAtValue]
(
@tableName varchar(255)
,@partitionValue int
,@schemaName varchar(255)='dbo'
)
RETURNS INT
AS
BEGIN
DECLARE @boudaryID int
SELECT
 TOP 1
 -- boundary_id start with 1
 @boudaryID=isnull(rv.boundary_id, 0)
 FROM sys.schemas s
   INNER JOIN sys.tables t
   ON t.schema_id=s.schema_id
   -- check table storage type
   INNER JOIN  sys.indexes i
   ON (i.object_id = t.object_id
   -- 0: heap
   -- 1: clusterd
   and i.index_id in (0,1))
   INNER JOIN sys.partitions p
   ON p.object_id = i.object_id
   AND p.index_id = i.index_id
   INNER JOIN  sys.index_columns  ic
   ON (-- identify partioned column
   ic.partition_ordinal > 0
   and ic.index_id = i.index_id
   and ic.object_id = t.object_id)
   INNER JOIN sys.partition_schemes ps
   ON ps.data_space_id = i.data_space_id
   INNER JOIN sys.partition_functions pf
   ON pf.function_id = ps.function_id
   LEFT JOIN sys.partition_range_values rv
   ON pf.function_id = rv.function_id AND p.partition_number = rv.boundary_id
   -- need exact match, assuming left range type
   AND cast(rv.value as int)=@partitionValue
 WHERE -- only look for heap or clustered index
   i.index_id IN (0, 1) 
   AND s.name=@schemaName
   AND t.name=@tableName
 ORDER BY isnull(rv.boundary_id, 0) DESC
 -- if @boudaryID is null then table is NOT partitioned
 -- if @boudaryID>0 then we fall into a partition boundary point
 -- if @boudaryID=0 table is partitioned but value is not a boundary point
 RETURN ISNULL(case when @boudaryID>0 then 1 else @boudaryID end, -1)
END
Posted by at No comments:

Tuesday 11 June 2013

usp_Util_TruncatePartition: trancate a specified partition from a partitioned table

-- published at
-- http://www.sqlservercentral.com/articles/partition+switch/100013/

USE TEST
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[usp_Util_TruncatePartition]') AND type in (N'P', N'PC'))
 DROP PROCEDURE [usp_Util_TruncatePartition]
GO
--
-- By Steven Rao
-- Date: June 13, 2013
-- StevenZRAO@gmail.com
-- declare @code int
-- A generic procedure to truncate any given partition from a partitioned table
-- Limitation: cannot deal with some data types, such as xml
-- Basic assumption and current limitation: (some minor changes are needed to extend the functionality of this sp)
-- 1) partition parameter has integer type
-- Change history
-- Date Description
-- 2013-07-26 Fix schema name and computed column issues
-- It is now also working both left and right partition type

CREATE PROCEDURE [usp_Util_TruncatePartition]
@tableName varchar(255)
,@partitionValue int
,@schemaName varchar(255)='dbo'
AS
BEGIN
--
-- Is table partitioned at all?
declare @error int, @rowsCount int, @errMsg varchar(1024)
declare @partitionSchema varchar(255), @partitionFunction varchar(255), @partitionColumn varchar(255), @tableStorageType varchar(16), @partitionFunctionID int
declare @partitionFileGroupName varchar(255), @databaseName varchar(255), @partitionNumber int, @cloneTableName varchar(255), @compression tinyint, @compressionQuery varchar(256)
declare @LogHeader varchar(23), @Now datetime
select @tableName=ltrim(rtrim(isnull(@tableName, ''))), @schemaName=ltrim(rtrim(isnull(@schemaName, ''))), @databaseName=db_name(), @schemaName=ltrim(rtrim(@schemaName)), @tableName=ltrim(rtrim(@tableName)), @compressionQuery=''

--
-- find the partition number for the partion value and other information for table partition
SELECT
@tableStorageType=i.type_desc
,@partitionSchema=ps.name
,@partitionFunction=pf.name
,@partitionFunctionID=pf.function_id
-- partition FOR RIGHT and boundary_id begins with id 1
,@partitionNumber=case when pf.boundary_value_on_right=1 then rv.boundary_id+1 else rv.boundary_id end
,@partitionColumn=c.name
,@partitionFileGroupName=fg.name
-- compression can be 0: no compresson, 1: row level compression 2: page level compression
-- only apply to clustered index?
,@compression=case when i.index_id=1 then p.data_compression else 0 end
FROM sys.schemas s
INNER JOIN sys.tables t
ON t.schema_id=s.schema_id
-- check table storage type
INNER JOIN  sys.indexes i
ON (i.object_id = t.object_id
-- 0: heap
-- 1: clusterd
and i.index_id in (0,1))
INNER JOIN sys.partitions p
ON p.object_id = i.object_id
AND p.index_id = i.index_id
INNER JOIN  sys.index_columns  ic
ON (-- identify partioned column
ic.partition_ordinal > 0
and ic.index_id = i.index_id
and ic.object_id = t.object_id)
INNER JOIN  sys.columns c
ON c.object_id = ic.object_id and c.column_id = ic.column_id
INNER JOIN sys.system_internals_allocation_units au
ON p.partition_id = au.container_id
INNER JOIN sys.partition_schemes ps
ON ps.data_space_id = i.data_space_id
INNER JOIN sys.partition_functions pf
ON pf.function_id = ps.function_id
INNER JOIN sys.destination_data_spaces dds
ON dds.partition_scheme_id = ps.data_space_id AND dds.destination_id = p.partition_number
INNER JOIN sys.filegroups fg
ON dds.data_space_id = fg.data_space_id
INNER JOIN sys.partition_range_values rv
ON pf.function_id = rv.function_id AND p.partition_number = rv.boundary_id
WHERE -- only look for heap or clustered index
i.index_id IN (0, 1)
AND s.name=@schemaName
AND t.name=@tableName
-- need exact match
AND cast(rv.value as int)=@partitionValue

select @error=@@ERROR, @rowsCount=@@ROWCOUNT, @Now=getdate(), @LogHeader=convert(char(23), @Now, 126)
if (@rowsCount<=0)
begin
print @LogHeader+' table '+@tableName+' on db '+@databaseName+' with schema '+@schemaName
+' is not partitioned or @partitionValue='+cast(@partitionValue as varchar(16))
+' is not in exsiting partition range. will return.'
return 100
end else
begin
print @LogHeader+' table '+@tableName+' on db '+@databaseName+' with schema '
+@schemaName+' is partitioned: @partitionSchema='+@partitionSchema
+', @partitionFunction='+@partitionFunction
+', @partitionFunctionID='+cast(@partitionFunctionID as varchar(16))
+', @partitionColumn='+ @partitionColumn
+', @tableStorageType='+@tableStorageType
+', @partitionValue='+cast(@partitionValue as varchar(16))
+', @partitionNumber='+cast(@partitionNumber as varchar(16))
+', @partitionFileGroupName='+@partitionFileGroupName
+', @compression='+cast(@compression as varchar(16))
end

--
-- create clone table on same file group, drop it if already exists
-- we don't need to take care of FK and no clustered for our case
-- No clustered index is not needed
-- taking care of columns and primary key
-- Ironically you could drop a table without droping its primary key!! suprise suprise
-- Now we know the primary key name, we can generate the drop statement

declare @dropCloneTable varchar(8000),
        @createCloneTable varchar(8000),
        @primaryKeyName varchar(255),
        @dropPrimaryKey varchar(8000),
        @primayKeyNamePlaceHolder varchar(255),
        @hasPrimaryKey tinyint,
        @switchPartition varchar(1024)

select
-- A place holder for primary key which will be replace when the value is known
  @primayKeyNamePlaceHolder='$$PK_NAME$$'
 ,@cloneTableName=@tableName+'_'+cast(@partitionNumber as varchar(16))
 ,@dropCloneTable='IF EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'+''''+@schemaName+'.'+@cloneTableName+''''+') AND type in (N'+''''+'U'+''''+'))'+char(13)
 +'BEGIN'+char(13)+'DROP TABLE '+@schemaName+'.'+@cloneTableName+char(13) +'END'
 ,@dropPrimaryKey='IF EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID(N'+''''+@schemaName+'.'+@cloneTableName+''''+') AND name = N'+''''+@primayKeyNamePlaceHolder+''''+')'+char(13)
 +'BEGIN'+char(13)+'ALTER TABLE '+@schemaName+'.'+@cloneTableName+' DROP CONSTRAINT '+@primayKeyNamePlaceHolder+char(13)+'END'
 ,@switchPartition='ALTER TABLE '+@schemaName+'.'+@tableName +' SWITCH PARTITION '+cast(@partitionNumber as varchar(16))+' TO '+@schemaName+'.'+@cloneTableName

select
  @hasPrimaryKey=CASE WHEN tc.Constraint_Name IS NULL THEN 0 ELSE 1 END
 ,@primaryKeyName=CASE WHEN @hasPrimaryKey=1 THEN replace(replace(@CloneTableName, @schemaName, ''), '.', '')+'_'+tc.Constraint_Name ELSE '' END
 ,@createCloneTable= 'CREATE TABLE ' +@schemaName+'.'+@CloneTableName
  + '(' + o.list + ')'
  + ' ON ['+@partitionFileGroupName+']'
  + CASE WHEN @hasPrimaryKey=0 THEN '' ELSE char(13)
  +'ALTER TABLE ' +@schemaName+'.'+ @CloneTableName + ' ADD CONSTRAINT ' + @primaryKeyName + ' PRIMARY KEY '
  + CASE WHEN i.index_id IS NULL THEN 'NONCLUSTERED ' ELSE 'CLUSTERED ' END
  + ' (' + LEFT(j.List, Len(j.List)-1) + ')' END
 from sys.schemas s
  inner join sys.objects so
   on s.schema_id=so.schema_id
  cross apply
   (SELECT
' ['+column_name+'] '
-- deal with computed columns
+ case when cc.is_computed=1 then ' AS ('+cc.definition+')'+case when cc.is_persisted=1 then ' PERSISTED' else '' end + ', ' else
-- non computed columns case
data_type+
case data_type
when 'sql_variant' then ''
when 'text' then ''
when 'decimal' then '(' + cast(numeric_precision as varchar) + ', ' + cast(numeric_scale as varchar) + ')'
else coalesce('('+case when character_maximum_length = -1 then 'MAX' else cast(character_maximum_length as varchar) end +')','') end
+ ' '
+ case when exists (
 select id
  from syscolumns
  where object_name(id)='TestTruncatePartition'
  and name=column_name
  and columnproperty(id,name,'IsIdentity') = 1
)
 then 'IDENTITY(' + cast(ident_seed('TestTruncatePartition') as varchar) + ',' + cast(ident_incr('TestTruncatePartition') as varchar) + ')'
 else '' end
+ ' '
+ (case when c.IS_NULLABLE = 'No' then 'NOT ' else '' end ) + 'NULL '
+ case when c.COLUMN_DEFAULT IS NOT NULL THEN 'DEFAULT '+ c.COLUMN_DEFAULT ELSE '' END + ', '
end
from information_schema.columns c left join sys.computed_columns cc on
OBJECT_ID(c.TABLE_SCHEMA+'.'+c.TABLE_NAME)=cc.object_id and c.COLUMN_NAME=cc.name
where c.table_name = so.name AND c.TABLE_SCHEMA=@schemaName
 order by ordinal_position
 FOR XML PATH('')
  ) o (list)
 left join information_schema.table_constraints tc
  on tc.Table_name = so.Name
  AND tc.Constraint_Type = 'PRIMARY KEY'
  AND tc.TABLE_SCHEMA=@schemaName
 cross apply
  (select '[' + Column_Name + '], '
   FROM information_schema.key_column_usage kcu
   WHERE kcu.Constraint_Name = tc.Constraint_Name and kcu.TABLE_SCHEMA=@schemaName
   ORDER BY ORDINAL_POSITION
   FOR XML PATH('')
   ) j (list)
  -- find out if the primary key is clustered
  left join sys.indexes i
   on so.object_id=i.object_id
   -- index_id 1 means clustered
   -- constraint name is primary key name which is same as the index name
   AND i.index_id=1 AND tc.CONSTRAINT_NAME=i.name and tc.TABLE_SCHEMA=@schemaName
 where s.name=@SchemaName
and so.type = 'U'
and so.name NOT IN ('dtproperties')
and so.name=@TableName

select @error=@@ERROR,
       @rowsCount=@@ROWCOUNT,
       @Now=getdate(),
       @LogHeader=convert(char(23), @Now, 126)

print @LogHeader+' @hasPrimaryKey='+cast(@hasPrimaryKey as varchar(16))

if (@hasPrimaryKey=1)
 begin
  select @dropPrimaryKey=replace(@dropPrimaryKey, @primayKeyNamePlaceHolder, @primaryKeyName)
 end

-- 1: Row compression
-- 2: Page compression
if @compression=1
 begin
  set @compressionQuery='ALTER TABLE ' +@schemaName+'.'+@cloneTableName + ' REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = ROW)';
 end
else if @compression=2
 begin
  set @compressionQuery='ALTER TABLE ' +@schemaName+'.'+@cloneTableName + ' REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE)';
 end
--
-- taking care of clustered index
-- taking care of primary key is not enough since a primary key may not be clustered
-- so now we have to excludes clustered primary key index since they are already been taken care of
-- sample index create and drop statement
--
-- we may not need to drop the index? just in case
declare @indexColums varchar(256),
        @indexName varchar(256),
        @indexUnique varchar(8),
        @hasNonPrimaryKeyClusterdIndex tinyint,
        @createClusteredIndex varchar(8000),
        @dropClusteredIndex varchar(8000)

select @indexColums='(',
       @indexUnique='',
       @indexName='',
       @hasNonPrimaryKeyClusterdIndex=0,
       @dropClusteredIndex='',
       @createClusteredIndex=''

select
-- whenever we have result from here, we do have clusterd index
  @hasNonPrimaryKeyClusterdIndex=1
 ,@indexColums=@indexColums+ic.columnList
 ,@indexName=@cloneTableName+'_'+i.name
 ,@indexUnique=case when i.is_unique=0 then '' else 'UNIQUE ' end
 from sys.schemas s
  INNER JOIN sys.tables t
   ON t.schema_id=s.schema_id
  INNER JOIN sys.indexes i
   ON (i.object_id = t.object_id
   -- looking for clustered index ONLY
   and i.index_id=1
   -- ignore primary key, which is taken care of above already
   and i.is_primary_key=0)
  CROSS APPLY
   (
    select
      c.name+case when ic.is_descending_key=0 then ' ASC' else ' DESC' end+','
     from sys.index_columns ic
      INNER JOIN sys.columns c
       ON c.column_id=ic.column_id
      and c.object_id=ic.object_id
     where ic.index_id = i.index_id
       and ic.object_id = t.object_id
     order by ic.key_ordinal
    FOR XML PATH('')
    ) ic(columnList)
 where s.name=@schemaName
 and t.name=@tableName

select @error=@@ERROR,
       @rowsCount=@@ROWCOUNT,
       @Now=getdate(),
       @LogHeader=convert(char(23), @Now, 126),
       @hasNonPrimaryKeyClusterdIndex=isnull(@hasNonPrimaryKeyClusterdIndex, 0)

print @LogHeader+' @hasNonPrimaryKeyClusterdIndex='+cast(@hasNonPrimaryKeyClusterdIndex as varchar(16))

if (@hasNonPrimaryKeyClusterdIndex>=1)
 begin
  select @dropClusteredIndex='IF EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID(N'+''''+@schemaName+'.'+@cloneTableName+''''+') AND name = N'+''''+@indexName+''''+')'
  +char(13)+'BEGIN'+char(13)
  +'DROP INDEX '+@indexName+' ON '+@schemaName+'.'+@cloneTableName
  +char(13)+'END'
  ,@createClusteredIndex='CREATE '+@indexUnique+'CLUSTERED INDEX '+@indexName+' ON '+@schemaName+'.'+@cloneTableName+left(@indexColums, len(@indexColums)-1)+')'
 end

select @error=@@ERROR, @rowsCount=@@ROWCOUNT, @Now=getdate(), @LogHeader=convert(char(23), @Now, 126)

print @LogHeader+' (@dropPrimaryKey, @dropClusteredIndex, @dropCloneTable, @createCloneTable, @createClusteredIndex, @compressionQuery, @switchPartition, @dropPrimaryKey, @dropCloneTable)'

print @dropPrimaryKey+char(13)+@dropClusteredIndex+char(13)+@dropCloneTable+char(13)+@createCloneTable+char(13)+@createClusteredIndex+char(13)+@compressionQuery+char(13)+@switchPartition+char(13)+@dropPrimaryKey+char(13)+@dropCloneTable

declare @returnCode int

if (@hasPrimaryKey=1)
 begin
  EXEC(@dropPrimaryKey)
  select @returnCode=300, @error=@@ERROR, @rowsCount=@@ROWCOUNT, @Now=getdate(), @LogHeader=convert(char(23), @Now, 126), @errMsg=@LogHeader+' [1] failed to drop primary key for clone table. will return.'
  if (@error<>0) goto ERROR
 end

if (@hasNonPrimaryKeyClusterdIndex=1)
 begin
  EXEC(@dropClusteredIndex)
  select @returnCode=310, @error=@@ERROR, @rowsCount=@@ROWCOUNT, @Now=getdate(), @LogHeader=convert(char(23), @Now, 126), @errMsg=@LogHeader+' [1] failed to drop clustered index for clone table. will return.'
  if (@error<>0) goto ERROR
 end

EXEC(@dropCloneTable)

select @returnCode=320, @error=@@ERROR, @rowsCount=@@ROWCOUNT, @Now=getdate(), @LogHeader=convert(char(23), @Now, 126), @errMsg=@LogHeader+' [1] failed to drop clone table. will return.'

if (@error<>0) goto ERROR

EXEC(@createCloneTable)

select @returnCode=330, @error=@@ERROR, @rowsCount=@@ROWCOUNT, @Now=getdate(), @LogHeader=convert(char(23), @Now, 126), @errMsg=@LogHeader+' failed to create clone table. will return.'

if (@error<>0) goto ERROR
 if (@hasNonPrimaryKeyClusterdIndex=1)
  begin
   EXEC(@createClusteredIndex)

   select @returnCode=340, @error=@@ERROR, @rowsCount=@@ROWCOUNT, @Now=getdate(), @LogHeader=convert(char(23), @Now, 126), @errMsg=@LogHeader+' failed to create clustered index for clone table. will return.'

   if (@error<>0) goto ERROR
  end

if (@compression IN (1,2))
 begin
  EXEC(@compressionQuery)
  select @returnCode=350, @error=@@ERROR, @rowsCount=@@ROWCOUNT, @Now=getdate(), @LogHeader=convert(char(23), @Now, 126), @errMsg=@LogHeader+' failed to adjust clone table compressoin option. will return.'
  if (@error<>0) goto ERROR
 end

--
-- now do the partition switch to truncate data
EXEC(@switchPartition)

select @returnCode=360, @error=@@ERROR, @rowsCount=@@ROWCOUNT, @Now=getdate(), @LogHeader=convert(char(23), @Now, 126), @errMsg=@LogHeader+' failed to switch partition to clone table. will return.'

if (@error<>0) goto ERROR

--
-- now drop the coloum table again
if (@hasPrimaryKey=1)
 begin
  EXEC(@dropPrimaryKey)
  select @returnCode=370, @error=@@ERROR, @rowsCount=@@ROWCOUNT, @Now=getdate(), @LogHeader=convert(char(23), @Now, 126), @errMsg=@LogHeader+' [2] failed to drop primary key for clone table. will return.'
  if (@error<>0) goto ERROR
 end
--
-- may not need to do this:
-- also this may have some performance issue since dropping clustered index means a heap will have to be created??
--if (@hasNonPrimaryKeyClusterdIndex=1)
--begin
--EXEC(@dropClusteredIndex)
--select @returnCode=380, @error=@@ERROR, @rowsCount=@@ROWCOUNT, @Now=getdate(), @LogHeader=convert(char(23), @Now, 126), @errMsg=@LogHeader+' [2] failed to drop clustered index for clone table. will return.'
--if (@error<>0) goto ERROR
--end
EXEC(@dropCloneTable)

select @returnCode=390, @error=@@ERROR, @rowsCount=@@ROWCOUNT, @Now=getdate(), @LogHeader=convert(char(23), @Now, 126), @errMsg=@LogHeader+' [2] failed to drop clone table. will return.'

if (@error<>0) goto ERROR

RETURN 0

ERROR:
print @errMsg

return @returnCode

END

Posted by at No comments:

Monday 10 June 2013

usp_Util_InsertPartition: Dynamically add a new partition to a partioned table

--
-- basic assumption:
-- partition range is FOR LEFT
-- partiitn column is of data type in

-- exec [usp_Util_InsertPartition] 'PartitionTestNo', 100
-- exec [usp_Util_InsertPartition] 'PartitionTest', 2000
-- exec [usp_Util_InsertPartition] 'PartitionTest', 0
-- Basic assumption: (some minor changes are needed to extend the functionality of this sp)
-- 1) partition range is left
-- 2) partion parameter has integer type
-- when @usePreviousFileGroup=1, will use the file gruop on the left most partion value and @fileSize and @fileGrowth will be ignored
-- otherwise will append partion number as the file group name and add a new file to the new file group
CREATE PROCEDURE [usp_Util_InsertPartition]
@tableName varchar(255)
,@partitionValue int
,@schemaName varchar(255)='dbo'
,@usePreviousFileGroup tinyint=1
,@fileSize varchar(255)='50MB'
,@fileGrowth varchar(255)='10'
AS
AS
BEGIN
--
-- Is table partioned at all?
declare @error int, @rowsCount int, @errMsg varchar(1024)
declare @partitionSchema varchar(255), @partitionFunction varchar(255), @partitionColumn varchar(255), @tableStorageType varchar(16), @partitionFunctionID int
declare @partitionFileGroupName varchar(255), @databaseName varchar(255), @partitionNumber int, @cloneTableName varchar(255), @compression tinyint, @compressionQuery varchar(256)
declare @LogHeader varchar(23), @Now datetime
select @tableName=ltrim(rtrim(isnull(@tableName, ''))), @schemaName=ltrim(rtrim(isnull(@schemaName, ''))), @databaseName=db_name(), @schemaName=ltrim(rtrim(@schemaName)), @tableName=ltrim(rtrim(@tableName)), @compressionQuery=''
--
-- find the partition number for the partion value and other information for table partition
SELECT
 @tableStorageType=i.type_desc
 ,@partitionSchema=ps.name
 ,@partitionFunction=pf.name
 ,@partitionFunctionID=pf.function_id
 ,@partitionNumber=rv.boundary_id
 ,@partitionColumn=c.name
 ,@partitionFileGroupName=fg.name
 -- compression can be 0: no compresson, 1: row level compression 2: page level compression
 -- only apply to clustered index?
 ,@compression=case when i.index_id=1 then p.data_compression else 0 end
 FROM sys.schemas s
   INNER JOIN sys.tables t
   ON t.schema_id=s.schema_id
   -- check table storage type
   INNER JOIN  sys.indexes i
   ON (i.object_id = t.object_id
   -- 0: heap
   -- 1: clusterd
   and i.index_id in (0,1))
   INNER JOIN sys.partitions p
   ON p.object_id = i.object_id
   AND p.index_id = i.index_id
   INNER JOIN  sys.index_columns  ic
   ON (-- identify partioned column
   ic.partition_ordinal > 0
   and ic.index_id = i.index_id
   and ic.object_id = t.object_id)
   INNER JOIN  sys.columns c
   ON c.object_id = ic.object_id and c.column_id = ic.column_id
   INNER JOIN sys.system_internals_allocation_units au
   ON p.partition_id = au.container_id
   INNER JOIN sys.partition_schemes ps
   ON ps.data_space_id = i.data_space_id
   INNER JOIN sys.partition_functions pf
   ON pf.function_id = ps.function_id
   INNER JOIN sys.destination_data_spaces dds
   ON dds.partition_scheme_id = ps.data_space_id AND dds.destination_id = p.partition_number
   INNER JOIN sys.filegroups fg
   ON dds.data_space_id = fg.data_space_id
   INNER JOIN sys.partition_range_values rv
   ON pf.function_id = rv.function_id AND p.partition_number = rv.boundary_id 
 WHERE -- only look for heap or clustered index
   i.index_id IN (0, 1) 
   AND s.name=@schemaName
   AND t.name=@tableName
   -- need exact match, assuming left range type
   AND cast(rv.value as int)=@partitionValue
select @error=@@ERROR, @rowsCount=@@ROWCOUNT, @Now=getdate(), @LogHeader=convert(char(23), @Now, 126)
if (@rowsCount<=0)
begin
 print @LogHeader+' table '+@tableName+' on db '+@databaseName+' with schema '+@schemaName
   +' is not partitioned or @partitionValue='+cast(@partitionValue as varchar(16))
   +' is not in exsiting partition range. will return.'
 return 100
end else
begin
 print @LogHeader+' table '+@tableName+' on db '+@databaseName+' with schema '
   +@schemaName+' is partitioned: @partitionSchema='+@partitionSchema
   +', @partitionFunction='+@partitionFunction
   +', @partitionFunctionID='+cast(@partitionFunctionID as varchar(16))
   +', @partitionColumn='+ @partitionColumn
   +', @tableStorageType='+@tableStorageType
   +', @partitionValue='+cast(@partitionValue as varchar(16))
   +', @partitionNumber='+cast(@partitionNumber as varchar(16))
   +', @partitionFileGroupName='+@partitionFileGroupName
   +', @compression='+cast(@compression as varchar(16))
end
--
-- create clone table on same file group, drop it if already exists
-- sample checking and drop table and PK statement
--IF  EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[corDistrictPrd]') AND type in (N'U'))
--DROP TABLE [dbo].[corDistrictPrd]
--IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[corDistrictPrd]') AND name = N'PK_corDistrictPrd')
--ALTER TABLE [dbo].[corDistrictPrd] DROP CONSTRAINT [PK_corDistrictPrd]
-- No clustered index is not needed
-- taking care of columns and primary key
-- ironically you could drop a table without droping its primary key!! suprise suprise
-- Now we know the primary key name, we can generate the drop statement
declare @dropCloneTable varchar(8000), @createCloneTable varchar(8000), @primaryKeyName varchar(255), @dropPrimaryKey varchar(8000), @primayKeyNamePlaceHolder varchar(255), @hasPrimaryKey tinyint, @switchPartition varchar(1024)
select
 -- A place holder for primary key which will be replace when the value is known
 @primayKeyNamePlaceHolder='$$PK_NAME$$'
 ,@cloneTableName=@tableName+'_'+cast(@partitionNumber as varchar(16))
 ,@dropCloneTable='IF  EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'+''''+@schemaName+'.'+@cloneTableName+''''+') AND type in (N'+''''+'U'+''''+'))'+char(13)
  +'BEGIN'+char(13)+'DROP TABLE '+@schemaName+'.'+@cloneTableName+char(13)+'END'
 ,@dropPrimaryKey='IF  EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID(N'+''''+@schemaName+'.'+@cloneTableName+''''+') AND name = N'+''''+@primayKeyNamePlaceHolder+''''+')'+char(13)
  +'BEGIN'+char(13)+'ALTER TABLE '+@schemaName+'.'+@cloneTableName+' DROP CONSTRAINT '+@primayKeyNamePlaceHolder+char(13)+'END'
 ,@switchPartition='ALTER TABLE '+@schemaName+'.'+@tableName +' SWITCH PARTITION '+cast(@partitionNumber as varchar(16))+' TO '+@schemaName+'.'+@cloneTableName
select
  @hasPrimaryKey=CASE WHEN tc.Constraint_Name IS NULL THEN 0 ELSE 1 END
  ,@primaryKeyName=CASE WHEN @hasPrimaryKey=1 THEN replace(replace(@CloneTableName, @schemaName, ''), '.', '')+'_'+tc.Constraint_Name ELSE '' END
  ,@createCloneTable=
  'CREATE TABLE ' +@CloneTableName
  + '(' + o.list + ')'
  + ' ON '+@partitionFileGroupName
  + CASE WHEN @hasPrimaryKey=0 THEN '' ELSE char(13)
   +'ALTER TABLE ' + @CloneTableName + ' ADD CONSTRAINT ' +  @primaryKeyName + ' PRIMARY KEY '
   + CASE WHEN i.index_id IS NULL THEN 'NONCLUSTERED ' ELSE 'CLUSTERED ' END
   + ' (' + LEFT(j.List, Len(j.List)-1) + ')' END
 from    sys.schemas s inner join
   sys.objects so on
   s.schema_id=so.schema_id
   cross apply
    (SELECT
     '  ['+column_name+'] '
     + data_type
     + case data_type
       when 'sql_variant' then ''
       when 'text' then ''
       when 'decimal' then '(' + cast(numeric_precision as varchar) + ', ' + cast(numeric_scale as varchar) + ')'
       else coalesce('('+case when character_maximum_length = -1 then 'MAX' else cast(character_maximum_length as varchar) end +')','') end
     + ' '
     + case when exists (
          select id from syscolumns
             where object_name(id)=so.name
               and name=column_name
               and columnproperty(id,name,'IsIdentity') = 1
             )
       then 'IDENTITY(' + cast(ident_seed(so.name) as varchar) + ',' + cast(ident_incr(so.name) as varchar) + ')'
       else '' end
     + ' '
     + (case when IS_NULLABLE = 'No' then 'NOT ' else '' end ) + 'NULL '
     + case when information_schema.columns.COLUMN_DEFAULT IS NOT NULL THEN 'DEFAULT '+ information_schema.columns.COLUMN_DEFAULT ELSE '' END + ', '
    from information_schema.columns
    where table_name = so.name
    order by ordinal_position
    FOR XML PATH('')) o (list)
   left join information_schema.table_constraints tc
   on tc.Table_name               = so.Name
    AND tc.Constraint_Type  = 'PRIMARY KEY'
   cross apply
    (select '[' + Column_Name + '], '
     FROM       information_schema.key_column_usage kcu
     WHERE      kcu.Constraint_Name     = tc.Constraint_Name
     ORDER BY ORDINAL_POSITION
     FOR XML PATH('')) j (list)
   -- find out if the primary key is clustered
   left join sys.indexes i
   on so.object_id=i.object_id
    -- index_id 1 means clustered
    -- constraint name is primary key name which is same as the index name
    AND i.index_id=1 AND tc.CONSTRAINT_NAME=i.name
 where   s.name=@SchemaName
   and so.type = 'U'
   and so.name NOT IN ('dtproperties')
   and so.name=@TableName
select @error=@@ERROR, @rowsCount=@@ROWCOUNT, @Now=getdate(), @LogHeader=convert(char(23), @Now, 126)
print @LogHeader+' @hasPrimaryKey='+cast(@hasPrimaryKey as varchar(16))
if (@hasPrimaryKey=1)
begin
 select @dropPrimaryKey=replace(@dropPrimaryKey, @primayKeyNamePlaceHolder, @primaryKeyName)
end

-- 1: Row compression
-- 2: Page compression
if @compression=1
begin
 set @compressionQuery='ALTER TABLE ' +@schemaName+'.'+@cloneTableName + ' REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = ROW)';
end else if @compression=2
begin
 set @compressionQuery='ALTER TABLE ' +@schemaName+'.'+@cloneTableName + ' REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE)';
end
--
-- taking care of clusterd index
-- taking care of primary key is not enough since a primary key may not be clustered
-- so now we have to excludes clustered primary key index since they are already been taken care of
-- sample index create and drop statement
--IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[fctBranch]') AND name = N'IX_fctBranch')
--DROP INDEX [IX_fctBranch] ON [dbo].[fctBranch] WITH ( ONLINE = OFF )
-- CREATE [ UNIQUE | CLUSTERED | NONCLUSTERED ] INDEX [IX_fctBranch] ON [dbo].[fctBranch]
--(
-- [dimPMFirmid] ASC,
-- [dimPeriodid] ASC
--)
--
-- we may not need to drop the index? just in case
declare @indexColums varchar(256), @indexName varchar(256), @indexUnique varchar(8), @hasNonPrimaryKeyClusterdIndex tinyint, @createClusteredIndex varchar(8000), @dropClusteredIndex varchar(8000)
select @indexColums='(', @indexUnique='', @indexName='', @hasNonPrimaryKeyClusterdIndex=0, @dropClusteredIndex='', @createClusteredIndex=''
/*
select
 -- whenever we have result from here, we do hve clusterd index
 @hasNonPrimaryKeyClusterdIndex=1
 -- this will accumulate all columns
 ,@indexColums=@indexColums+c.name+case when ic.is_descending_key=0 then ' ASC' else ' DESC' end+','
 -- they will be overwritten since they have identical values for all columns
 ,@indexName=@cloneTableName+'_'+i.name
 ,@indexUnique=case when i.is_unique=0 then '' else 'UNIQUE ' end
 FROM sys.schemas s
   INNER JOIN sys.tables t
   ON t.schema_id=s.schema_id
   INNER JOIN  sys.indexes i
   ON (i.object_id = t.object_id
    -- looking for clustered index ONLY
    and i.index_id=1
    -- ignore primary key, which is taken care of above already
    and i.is_primary_key=0)
   INNER JOIN  sys.index_columns  ic
   ON (ic.index_id = i.index_id and ic.object_id = t.object_id)
   INNER JOIN sys.columns c
   ON c.column_id=ic.column_id and c.object_id=ic.object_id
 where s.name=@schemaName and t.name=@tableName
 order by ic.key_ordinal
*/
-- this is a better way to do the same thing:
select
 -- whenever we have result from here, we do hve clusterd index
 @hasNonPrimaryKeyClusterdIndex=1
 ,@indexColums=@indexColums+ic.columnList
 ,@indexName=@cloneTableName+'_'+i.name
 ,@indexUnique=case when i.is_unique=0 then '' else 'UNIQUE ' end
 from sys.schemas s
   INNER JOIN sys.tables t
   ON t.schema_id=s.schema_id
   INNER JOIN  sys.indexes i
   ON (i.object_id = t.object_id
    -- looking for clustered index ONLY
    and i.index_id=1
    -- ignore primary key, which is taken care of above already
    and i.is_primary_key=0)
   CROSS APPLY
    (
    select
     c.name+case when ic.is_descending_key=0 then ' ASC' else ' DESC' end+','
     from sys.index_columns  ic
       INNER JOIN sys.columns c
       ON c.column_id=ic.column_id and c.object_id=ic.object_id
     
     where ic.index_id = i.index_id and ic.object_id = t.object_id
     order by ic.key_ordinal
     FOR XML PATH('')
     ) ic(columnList)
 where s.name=@schemaName and t.name=@tableName
select @error=@@ERROR, @rowsCount=@@ROWCOUNT, @Now=getdate(), @LogHeader=convert(char(23), @Now, 126), @hasNonPrimaryKeyClusterdIndex=isnull(@hasNonPrimaryKeyClusterdIndex, 0)
print @LogHeader+' @hasNonPrimaryKeyClusterdIndex='+cast(@hasNonPrimaryKeyClusterdIndex as varchar(16))
if (@hasNonPrimaryKeyClusterdIndex>=1)
begin
select @dropClusteredIndex='IF  EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID(N'+''''+@schemaName+'.'+@cloneTableName+''''+') AND name = N'+''''+@indexName+''''+')'
    +char(13)+'BEGIN'+char(13)
    +'DROP INDEX '+@indexName+' ON '+@schemaName+'.'+@cloneTableName
    +char(13)+'END'
  ,@createClusteredIndex='CREATE '+@indexUnique+'CLUSTERED INDEX '+@indexName+' ON '+@schemaName+'.'+@cloneTableName+left(@indexColums, len(@indexColums)-1)+')'
end
select @error=@@ERROR, @rowsCount=@@ROWCOUNT, @Now=getdate(), @LogHeader=convert(char(23), @Now, 126)
print @LogHeader+' (@dropPrimaryKey, @dropClusteredIndex, @dropCloneTable, @createCloneTable, @createClusteredIndex, @compressionQuery, @switchPartition, @dropPrimaryKey, @dropCloneTable)'
print @dropPrimaryKey+char(13)+@dropClusteredIndex+char(13)+@dropCloneTable+char(13)+@createCloneTable+char(13)+@createClusteredIndex+char(13)+@compressionQuery+char(13)+@switchPartition+char(13)+@dropPrimaryKey+char(13)+@dropCloneTable
declare @returnCode int
-- Note: we can not have transaction here, unfortunately.
-- Msg 226, Level 16, State 6, Line 1
-- ALTER DATABASE statement not allowed within multi-statement transaction.
--begin transaction Txc_usp_Util_TruncatePartition
if (@hasPrimaryKey=1)
begin
EXEC(@dropPrimaryKey)
select @returnCode=300, @error=@@ERROR, @rowsCount=@@ROWCOUNT, @Now=getdate(), @LogHeader=convert(char(23), @Now, 126), @errMsg=@LogHeader+' [1] failed to drop primary key for clone table. will return.'
if (@error<>0) goto ERROR
end
if (@hasNonPrimaryKeyClusterdIndex=1)
begin
EXEC(@dropClusteredIndex)
select @returnCode=310, @error=@@ERROR, @rowsCount=@@ROWCOUNT, @Now=getdate(), @LogHeader=convert(char(23), @Now, 126), @errMsg=@LogHeader+' [1] failed to drop clustered index for clone table. will return.'
if (@error<>0) goto ERROR
end
EXEC(@dropCloneTable)
select @returnCode=320, @error=@@ERROR, @rowsCount=@@ROWCOUNT, @Now=getdate(), @LogHeader=convert(char(23), @Now, 126), @errMsg=@LogHeader+' [1] failed to drop clone table. will return.'
if (@error<>0) goto ERROR
EXEC(@createCloneTable)
select @returnCode=330, @error=@@ERROR, @rowsCount=@@ROWCOUNT, @Now=getdate(), @LogHeader=convert(char(23), @Now, 126), @errMsg=@LogHeader+' failed to create clone table. will return.'
if (@error<>0) goto ERROR
if (@hasNonPrimaryKeyClusterdIndex=1)
begin
EXEC(@createClusteredIndex)
select @returnCode=340, @error=@@ERROR, @rowsCount=@@ROWCOUNT, @Now=getdate(), @LogHeader=convert(char(23), @Now, 126), @errMsg=@LogHeader+' failed to create clustered index for clone table. will return.'
if (@error<>0) goto ERROR
end
if (@compression IN (1,2))
begin
EXEC(@compressionQuery)
select @returnCode=350, @error=@@ERROR, @rowsCount=@@ROWCOUNT, @Now=getdate(), @LogHeader=convert(char(23), @Now, 126), @errMsg=@LogHeader+' failed to adjust clone table compressoin option. will return.'
if (@error<>0) goto ERROR
end
--
-- now do the partition switch to truncate data
EXEC(@switchPartition)
select @returnCode=360, @error=@@ERROR, @rowsCount=@@ROWCOUNT, @Now=getdate(), @LogHeader=convert(char(23), @Now, 126), @errMsg=@LogHeader+' failed to switch partition to clone table. will return.'
if (@error<>0) goto ERROR
--
-- now drop the coloum table again
if (@hasPrimaryKey=1)
begin
EXEC(@dropPrimaryKey)
select @returnCode=370, @error=@@ERROR, @rowsCount=@@ROWCOUNT, @Now=getdate(), @LogHeader=convert(char(23), @Now, 126), @errMsg=@LogHeader+' [2] failed to drop primary key for clone table. will return.'
if (@error<>0) goto ERROR
end
--
-- may not need to do this:
-- also this may have some performance issue since dropping clustered index means a heap will have to be created??
--if (@hasNonPrimaryKeyClusterdIndex=1)
--begin
--EXEC(@dropClusteredIndex)
--select @returnCode=380, @error=@@ERROR, @rowsCount=@@ROWCOUNT, @Now=getdate(), @LogHeader=convert(char(23), @Now, 126), @errMsg=@LogHeader+' [2] failed to drop clustered index for clone table. will return.'
--if (@error<>0) goto ERROR
--end
EXEC(@dropCloneTable)
select @returnCode=390, @error=@@ERROR, @rowsCount=@@ROWCOUNT, @Now=getdate(), @LogHeader=convert(char(23), @Now, 126), @errMsg=@LogHeader+' [2] failed to drop clone table. will return.'
if (@error<>0) goto ERROR
--commit transaction Txc_usp_Util_TruncatePartition
RETURN 0
ERROR:
--rollback transaction
print @errMsg
return @returnCode
END



Posted by at No comments:

Looking inside Partition (how many rows are stroed in which file groups?)


-- reference:
http://davidpeterhansen.com/view-partitions-in-sql-server/

SELECT
 s.name as SchemaName
 ,t.name as TableName
 ,c.name as PartitionColumn
 ,CASE boundary_value_on_right WHEN 1 THEN 'RIGHT' ELSE 'LEFT' END as PartionRangeType
 ,ps.name as PartitionName
 ,pf.name as PartitionFunction
 ,p.partition_number AS PartitionNumber
 ,CASE boundary_value_on_right WHEN 1 THEN '<' ELSE '<=' END as PartionComparisionType
 ,rv.value as PartitionValue
 ,fg.name AS FileGroupName
 ,p.rows AS RowsInPartition
 ,au.total_pages AS PagesInPartition
 ,au.total_pages*8/1024.0 AS MBsInPartition
 ,CONVERT(VARCHAR(6), CONVERT(INT, SUBSTRING (au.first_page, 6, 1) +SUBSTRING (au.first_page, 5, 1)))
  +':'
  + CONVERT (VARCHAR(20), CONVERT (INT, SUBSTRING (au.first_page, 4, 1) +SUBSTRING (au.first_page, 3, 1) +SUBSTRING (au.first_page, 2, 1) +SUBSTRING (au.first_page, 1, 1)))
 AS PartitionFirstPage
 FROM sys.schemas s
   INNER JOIN sys.tables t
   ON t.schema_id=s.schema_id
   -- check table storage type
   INNER JOIN  sys.indexes i
   ON (i.object_id = t.object_id
   -- 0: heap
   -- 1: clusterd
   and i.index_id in (0,1))
   INNER JOIN sys.partitions p
   ON p.object_id = i.object_id
   AND p.index_id = i.index_id
   INNER JOIN  sys.index_columns  ic
   ON (-- identify partioned column
   ic.partition_ordinal > 0
   and ic.index_id = i.index_id
   and ic.object_id = t.object_id)
   INNER JOIN  sys.columns c
   ON c.object_id = ic.object_id and c.column_id = ic.column_id
   INNER JOIN sys.system_internals_allocation_units au
   ON p.partition_id = au.container_id
   INNER JOIN sys.partition_schemes ps
   ON ps.data_space_id = i.data_space_id
   INNER JOIN sys.partition_functions pf
   ON pf.function_id = ps.function_id
   INNER JOIN sys.destination_data_spaces dds
   ON dds.partition_scheme_id = ps.data_space_id AND dds.destination_id = p.partition_number
   INNER JOIN sys.filegroups fg
   ON dds.data_space_id = fg.data_space_id
   LEFT JOIN sys.partition_range_values rv
   ON pf.function_id = rv.function_id AND p.partition_number = rv.boundary_id 
 WHERE -- only look for heap or clustered index
   i.index_id IN (0, 1)
 ORDER BY 1,2,7






Posted by at No comments:

Thursday 6 June 2013

A better solution to drop/recreate FK

--
-- step 1:
-- drop all FKs
-- warning: before running scripts, make sure save the scripts from step 3!!!
  SELECT    'ALTER TABLE '
   + s.name + '.' + OBJECT_NAME(fk.parent_object_id)       
   + ' DROP CONSTRAINT ' + fk.NAME + ' ;' AS DropStatement
  FROM sys.foreign_keys fk
    INNER JOIN sys.tables t ON fk.parent_object_id = t.object_id
    INNER JOIN sys.schemas s ON fk.schema_id = s.schema_id

--
-- (optional)
-- step 2: truncte ALL tabls
 SELECT 'TRUNCATE TABLE '+s.name+'.'+t.name as 'TruncateStatement'
    FROM sys.tables t inner join sys.schemas s
   on t.schema_id=s.schema_id
   WHERE t.type='U'
  ORDER BY s.name, t.name

--
-- step 3, recreate FKs with original CHEK option
PRINT N'-- Re-CREATE FOREIGN KEY CONSTRAINTS with original check option --';
SET NOCOUNT ON;
SELECT '
 PRINT N''Creating '+ const.const_name +'...''
 GO
 ALTER TABLE ' + const.parent_obj
  -- you can remove this line if you don't want to keep original check option
  + ' WITH '+CASE WHEN ObjectProperty(const.constid,'CnstIsDisabled') =0 THEN 'CHECK' ELSE 'NOCHECK' END +
  + ' ADD CONSTRAINT ' + const.const_name + ' FOREIGN KEY (
    ' + const.parent_col_csv + '
    ) REFERENCES ' + const.ref_obj + '(' + const.ref_col_csv + ')
 GO' as StatementToReCreateFK
FROM (
    SELECT QUOTENAME(fk.NAME) AS [const_name]
        ,QUOTENAME(schParent.NAME) + '.' + QUOTENAME(OBJECT_name(fkc.parent_object_id)) AS [parent_obj]
        ,STUFF((
                SELECT ',' + QUOTENAME(COL_NAME(fcP.parent_object_id, fcp.parent_column_id))
                FROM sys.foreign_key_columns AS fcP
                WHERE fcp.constraint_object_id = fk.object_id
                FOR XML path('')
                ), 1, 1, '') AS [parent_col_csv]
        ,QUOTENAME(schRef.NAME) + '.' + QUOTENAME(OBJECT_NAME(fkc.referenced_object_id)) AS [ref_obj]
        ,STUFF((
                SELECT ',' + QUOTENAME(COL_NAME(fcR.referenced_object_id, fcR.referenced_column_id))
                FROM sys.foreign_key_columns AS fcR
                WHERE fcR.constraint_object_id = fk.object_id
                FOR XML path('')
                ), 1, 1, '') AS [ref_col_csv]
  ,sfk.constid
    FROM sys.foreign_key_columns AS fkc
    INNER JOIN sys.foreign_keys AS fk ON fk.object_id = fkc.constraint_object_id
    INNER JOIN sys.objects AS oParent ON oParent.object_id = fkc.parent_object_id
    INNER JOIN sys.schemas AS schParent ON schParent.schema_id = oParent.schema_id
    INNER JOIN sys.objects AS oRef ON oRef.object_id = fkc.referenced_object_id
    INNER JOIN sys.schemas AS schRef ON schRef.schema_id = oRef.schema_id
 -- add this to check FK CHECK option
 INNER JOIN sysobjects o on o.name=fk.name
 INNER JOIN sysforeignkeys sfk ON sfk.constid=o.id
    GROUP BY fkc.parent_object_id
        ,fkc.referenced_object_id
        ,fk.NAME
        ,fk.object_id
        ,schParent.NAME
        ,schRef.NAME
  ,sfk.constid
    ) AS const
ORDER BY const.const_name
Posted by at No comments:

Monday 3 June 2013

Going physical with table: File Group or Partition Info

--
-- All tables
select
 s.name as SchemaName
 ,t.name as TableName
 ,i.type_desc as TableStorageType
 ,case when f.name is null then 1 else 0 end as IsPartitioned
 ,f.name as FileGroupName
 ,ps.name as PartitionSchema
 ,pf.name as PartitionFunction
 ,c.name as PartionColumn
 -- on cluase for the table creation statement
 ,'ON '+case when f.name is not null then  f.name else ps.name+'('+c.name+')' end as OnClause
 from sys.schemas s inner join sys.tables t on
   t.schema_id=s.schema_id
   -- check table storage type
   inner join  sys.indexes i on  
   (i.object_id = t.object_id
   -- 0: heap
   -- 1: clusterd
   and i.index_id in (0,1))
   -- will have value for non-partioned table
   LEFT JOIN sys.filegroups f ON
   i.data_space_id = f.data_space_id
   -- will have value for partitioned table
   left join sys.partition_schemes ps on 
   i.data_space_id = ps.data_space_id
   left join sys.partition_functions pf ON ps.function_id = pf.function_id
   left join  sys.index_columns  ic on
   (-- identify partioned column
   ic.partition_ordinal > 0
   and ic.index_id = i.index_id
   and ic.object_id = t.object_id)
   left join  sys.columns c on
   (c.object_id = ic.object_id
   and c.column_id = ic.column_id)
 order by 1,2

For non-partitioned tables:
SELECT s.name as SchemaName
  ,o.[name] as TableName
  ,i.type_desc as TableStorageType
  ,f.[name] as FileGroupName
  FROM sys.indexes i INNER JOIN sys.filegroups f ON
    i.data_space_id = f.data_space_id
    INNER JOIN sys.all_objects o ON
    i.[object_id] = o.[object_id]
    INNER JOIN sys.schemas s ON
    s.schema_id=o.schema_id
  WHERE i.data_space_id = f.data_space_id
    AND o.type = 'U' -- User Created Tables
    AND i.index_id in (0,1)
  ORDER BY 1, 2

For partitioned tables:

select
 s.name as SchemaName
 ,t.name as TableName
 ,i.type_desc as TableStorageType
 ,ps.name as PartitionSchema
 ,pf.name as PartitionFunction
 ,c.name as PartionColumn
 from sys.schemas s inner join sys.tables t on
    t.schema_id=s.schema_id
   -- check table storage type
   inner join  sys.indexes i on  
    (i.object_id = t.object_id
    -- 0: heap
    -- 1: clusterd
    and i.index_id in (0,1))
   inner join sys.partition_schemes ps
    on  i.data_space_id = ps.data_space_id
   inner join sys.partition_functions pf ON ps.function_id = pf.function_id
   inner join  sys.index_columns  ic on
    (-- identify partioned column
    ic.partition_ordinal > 0
    and ic.index_id = i.index_id
    and ic.object_id = t.object_id)
   inner join  sys.columns c on
    (c.object_id = ic.object_id
    and c.column_id = ic.column_id)
 order by 1,2
Posted by at No comments:

Tuesday 28 May 2013

dm_os_wait_stats

-- http://www.sqlskills.com/blogs/paul/wait-statistics-or-please-tell-me-where-it-hurts/
-- sql server 2K
--DBCC SQLPERF (N'waitstats')
--go
-- reset wait stats
--DBCC SQLPERF (N'sys.dm_os_wait_stats', CLEAR);
go
WITH [Waits] AS
    (SELECT
        [wait_type],
        [wait_time_ms] / 1000.0 AS [WaitS],
        ([wait_time_ms] - [signal_wait_time_ms]) / 1000.0 AS [ResourceS],
        [signal_wait_time_ms] / 1000.0 AS [SignalS],
        [waiting_tasks_count] AS [WaitCount],
        100.0 * [wait_time_ms] / SUM ([wait_time_ms]) OVER() AS [Percentage],
        ROW_NUMBER() OVER(ORDER BY [wait_time_ms] DESC) AS [RowNum]
    FROM sys.dm_os_wait_stats
    WHERE [wait_type] NOT IN (
        N'CLR_SEMAPHORE',    N'LAZYWRITER_SLEEP',
        N'RESOURCE_QUEUE',   N'SQLTRACE_BUFFER_FLUSH',
        N'SLEEP_TASK',       N'SLEEP_SYSTEMTASK',
        N'WAITFOR',          N'HADR_FILESTREAM_IOMGR_IOCOMPLETION',
        N'CHECKPOINT_QUEUE', N'REQUEST_FOR_DEADLOCK_SEARCH',
        N'XE_TIMER_EVENT',   N'XE_DISPATCHER_JOIN',
        N'LOGMGR_QUEUE',     N'FT_IFTS_SCHEDULER_IDLE_WAIT',
        N'BROKER_TASK_STOP', N'CLR_MANUAL_EVENT',
        N'CLR_AUTO_EVENT',   N'DISPATCHER_QUEUE_SEMAPHORE',
        N'TRACEWRITE',       N'XE_DISPATCHER_WAIT',
        N'BROKER_TO_FLUSH',  N'BROKER_EVENTHANDLER',
        N'FT_IFTSHC_MUTEX',  N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
        N'DIRTY_PAGE_POLL',  N'SP_SERVER_DIAGNOSTICS_SLEEP')
  -- to avoid divide by zero erro right after wait stats cleanup
  -- very rare: no stats for user wait at all
  AND wait_time_ms>0
    )
SELECT
 [W1].RowNum,
    [W1].[wait_type] AS [WaitType],
    CAST ([W1].[WaitS] AS DECIMAL(14, 2)) AS [Wait_S],
    CAST ([W1].[ResourceS] AS DECIMAL(14, 2)) AS [Resource_S],
    CAST ([W1].[SignalS] AS DECIMAL(14, 2)) AS [Signal_S],
    [W1].[WaitCount] AS [WaitCount],
    CAST ([W1].[Percentage] AS DECIMAL(4, 2)) AS [Percentage],
    CAST (([W1].[WaitS] / [W1].[WaitCount]) AS DECIMAL (14, 4)) AS [AvgWait_S],
    CAST (([W1].[ResourceS] / [W1].[WaitCount]) AS DECIMAL (14, 4)) AS [AvgRes_S],
    CAST (([W1].[SignalS] / [W1].[WaitCount]) AS DECIMAL (14, 4)) AS [AvgSig_S]
FROM [Waits] AS [W1]
INNER JOIN [Waits] AS [W2]
    ON [W2].[RowNum] <= [W1].[RowNum]
GROUP BY [W1].[RowNum], [W1].[wait_type], [W1].[WaitS],
    [W1].[ResourceS], [W1].[SignalS], [W1].[WaitCount], [W1].[Percentage]
-- percentage threshold
HAVING SUM ([W2].[Percentage]) - [W1].[Percentage] < 95;
GO
Posted by at No comments:

Wednesday 22 May 2013

Check/Disable/Enable FK

SELECT
 (CASE WHEN OBJECTPROPERTY(CONSTID, 'CNSTISDISABLED') = 0 THEN 'ENABLED' ELSE 'DISABLED' END) AS STATUS
 ,OBJECT_NAME(CONSTID) AS CONSTRAINT_NAME
 ,OBJECT_NAME(FKEYID) AS TABLE_NAME
 ,COL_NAME(FKEYID, FKEY) AS COLUMN_NAME
 ,OBJECT_NAME(RKEYID) AS REFERENCED_TABLE_NAME
 ,COL_NAME(RKEYID, RKEY) AS REFERENCED_COLUMN_NAME
 FROM SYSFOREIGNKEYS
 ORDER BY TABLE_NAME
    , CONSTRAINT_NAME
    , REFERENCED_TABLE_NAME
    , KEYNO


http://www.jasinskionline.com/TechnicalWiki/Disable-Every-Foreign-Key.ashx?AspxAutoDetectCookieSupport=1

For SQL 2005 and above:
-- This SQL generates a set of SQL statements to disable every foreign
-- key in the database
select distinct
    sql = 'ALTER TABLE [' + onSchema.name + '].[' + onTable.name +
            '] NOCHECK CONSTRAINT [' + foreignKey.name + ']'
from sysforeignkeys fk inner join sysobjects foreignKey 
        on foreignKey.id = fk.constid
  inner join sys.objects onTable
        on fk.fkeyid = onTable.object_id
  inner join sys.schemas onSchema
        on onSchema.schema_id = onTable.schema_id
  inner join sysobjects againstTable 
        on fk.rkeyid = againstTable.id
where againstTable.TYPE = 'U'
  and onTable.TYPE = 'U'
  and ObjectProperty(fk.constid,'CnstIsDisabled') = 0
order by 1
-- This SQL generates a set of SQL statements to re-enable every foreign
-- key in the database
select distinct
    sql = 'ALTER TABLE [' + onSchema.name + '].[' + onTable.name +
            '] CHECK CONSTRAINT [' + foreignKey.name + ']'
from sysforeignkeys fk inner join sysobjects foreignKey 
        on foreignKey.id = fk.constid
  inner join sys.objects onTable
        on fk.fkeyid = onTable.object_id
  inner join sys.schemas onSchema
        on onSchema.schema_id = onTable.schema_id
  inner join sysobjects againstTable 
        on fk.rkeyid = againstTable.id
where againstTable.TYPE = 'U'
  and onTable.TYPE = 'U'
  and ObjectProperty(fk.constid,'CnstIsDisabled') = 1
order by 1
Posted by at No comments:

Thursday 9 May 2013

Quick way to remove all data from a database (remove FK, truncate and add FK back)

The following script can be used to generate script for these 3 steps which can be used to remove all data from a database quickly and recover FK inclusing check option.


--
-- the following is intresting but has some limitation
-- 1) can not handle the case when FK has multiple columns
-- 2) can not handle the case when FK refers to unique index

SET NOCOUNT ON
 DECLARE @FkDefination TABLE(
  Id INT PRIMARY KEY IDENTITY(1, 1),
  FKConstraintName VARCHAR(255),
  FKConstraintTableSchema VARCHAR(255),
  FKConstraintTableName VARCHAR(255),
  FKConstraintColumnName VARCHAR(255),
  PKConstraintName VARCHAR(255),
  PKConstraintTableSchema VARCHAR(255),
  PKConstraintTableName VARCHAR(255),
  PKConstraintColumnName VARCHAR(255),
  FkIsEnabled TINYINT   
 )
 --
 -- get FK definitions and property
 INSERT INTO @FkDefination(FKConstraintName, FKConstraintTableSchema, FKConstraintTableName, FKConstraintColumnName, FkIsEnabled)
  (
  SELECT
 kcu.CONSTRAINT_NAME
 ,kcu.TABLE_SCHEMA
 ,kcu.TABLE_NAME
 ,kcu.COLUMN_NAME
 ,1-ObjectProperty(fk.constid,'CnstIsDisabled') as FkIsEnabled
  FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
     INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
     ON kcu.CONSTRAINT_NAME = tc.CONSTRAINT_NAME
  INNER JOIN sysobjects o on o.name=tc.CONSTRAINT_NAME
  INNER JOIN sysforeignkeys fk
  ON fk.constid=o.id 
   WHERE tc.CONSTRAINT_TYPE = 'FOREIGN KEY'
  )
--
-- get PK and unique constrains name
 UPDATE @FkDefination
 SET PKConstraintName = UNIQUE_CONSTRAINT_NAME
     FROM @FkDefination tt
    INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc
    ON tt.FKConstraintName = rc.CONSTRAINT_NAME
--
-- get reference schema and table name
-- this seems to be working with unique constraint and PK, but not unique index?
 UPDATE @FkDefination
 SET PKConstraintTableSchema  = TABLE_SCHEMA,
   PKConstraintTableName  = TABLE_NAME
  FROM @FkDefination fkd INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
    ON fkd.PKConstraintName = tc.CONSTRAINT_NAME
--
-- get reference column name
 UPDATE @FkDefination
 SET PKConstraintColumnName = COLUMN_NAME
  FROM @FkDefination fkd
    INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
    ON fkd.PKConstraintName = kcu.CONSTRAINT_NAME
--select * from @FkDefination
 -- step 1: drop constraint:
 SELECT
        'ALTER TABLE [' + FKConstraintTableSchema + '].[' + FKConstraintTableName + ']  DROP CONSTRAINT ' + FKConstraintName AS DropFkStatement
     FROM @FkDefination
 -- step 2: truncate tables
 SELECT 'TRUNCATE TABLE '+s.name+'.'+t.name as 'TruncateStatement'
   FROM sys.tables t inner join sys.schemas s
  on t.schema_id=s.schema_id
  WHERE t.type='U'
  ORDER BY s.name, t.name
 -- step 3:
 -- add fk back
 SELECT
        'ALTER TABLE [' + FKConstraintTableSchema + '].[' + FKConstraintTableName + '] WITH '+CASE WHEN FkIsEnabled=1 THEN 'CHECK' ELSE 'NOCHECK' END +' ADD CONSTRAINT ' + FKConstraintName + ' FOREIGN KEY(' + FKConstraintColumnName + ') REFERENCES [' + PKConstraintTableSchema + '].[' + PKConstraintTableName + '](' + PKConstraintColumnName + ')' AS CreateFkStatement
     FROM @FkDefination
  WHERE PKConstraintTableName IS NOT NULL



reference:
http://stackoverflow.com/questions/11639868/temporarily-disable-all-foreign-key-constraints
Posted by at No comments:

Sunday 14 April 2013

Tempdb and space usage analysis

ref:
http://thesqldude.com/2012/05/15/monitoring-tempdb-space-usage-and-scripts-for-finding-queries-which-are-using-excessive-tempdb-space/

1) Which kind of objects consume more space in tempdb?
SELECT
SUM (user_object_reserved_page_count)*8 as user_obj_kb,
SUM (internal_object_reserved_page_count)*8 as internal_obj_kb,
SUM (version_store_reserved_page_count)*8  as version_store_kb,
SUM (unallocated_extent_page_count)*8 as freespace_kb,
SUM (mixed_extent_page_count)*8 as mixedextent_kb
FROM sys.dm_db_file_space_usage
 
 2) Which active scripts consume most space?
SELECT es.host_name , es.login_name , es.program_name,
st.dbid as QueryExecContextDBID, DB_NAME(st.dbid) as QueryExecContextDBNAME, st.objectid as ModuleObjectId,
SUBSTRING(st.text, er.statement_start_offset/2 + 1,(CASE WHEN er.statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(max),st.text)) * 2 ELSE er.statement_end_offset 
END - er.statement_start_offset)/2) as Query_Text,
tsu.session_id ,tsu.request_id, tsu.exec_context_id, 
(tsu.user_objects_alloc_page_count - tsu.user_objects_dealloc_page_count) as OutStanding_user_objects_page_counts,
(tsu.internal_objects_alloc_page_count - tsu.internal_objects_dealloc_page_count) as OutStanding_internal_objects_page_counts,
er.start_time, er.command, er.open_transaction_count, er.percent_complete, er.estimated_completion_time, er.cpu_time, er.total_elapsed_time, er.reads,er.writes, er.logical_reads, er.granted_query_memory
FROM sys.dm_db_task_space_usage tsu inner join sys.dm_exec_requests er 
 ON ( tsu.session_id = er.session_id and tsu.request_id = er.request_id) 
inner join sys.dm_exec_sessions es ON ( tsu.session_id = es.session_id ) 
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) st
WHERE (tsu.internal_objects_alloc_page_count+tsu.user_objects_alloc_page_count) > 0
ORDER BY (tsu.user_objects_alloc_page_count - tsu.user_objects_dealloc_page_count)+(tsu.internal_objects_alloc_page_count - tsu.internal_objects_dealloc_page_count) DESC
 
3) Longest transactions taking temp space
 SELECT top 5 a.session_id, a.transaction_id, a.transaction_sequence_num, a.elapsed_time_seconds, b.program_name, b.open_tran, b.status FROM sys.dm_tran_active_snapshot_database_transactions a join sys.sysprocesses b on a.session_id = b.spid ORDER BY elapsed_time_seconds DESC

4) Checking number of files:
Declare @tempdbfilecount as int;
select @tempdbfilecount = (select count(*) from sys.master_files where database_id=2 and type=0);
WITH Processor_CTE ([cpu_count], [hyperthread_ratio])
AS
(
      SELECT  cpu_count, hyperthread_ratio
      FROM sys.dm_os_sys_info sysinfo
)
select Processor_CTE.cpu_count as [# of Logical Processors], @tempdbfilecount as [Current_Tempdb_DataFileCount], 
(case 
      when (cpu_count<8 and @tempdbfilecount=cpu_count)  then 'No' 
      when (cpu_count<8 and @tempdbfilecount<>cpu_count and @tempdbfilecount<cpu_count) then 'Yes' 
      when (cpu_count<8 and @tempdbfilecount<>cpu_count and @tempdbfilecount>cpu_count) then 'No'
      when (cpu_count>=8 and @tempdbfilecount=cpu_count)  then 'No (Depends on continued Contention)' 
      when (cpu_count>=8 and @tempdbfilecount<>cpu_count and @tempdbfilecount<cpu_count) then 'Yes'
      when (cpu_count>=8 and @tempdbfilecount<>cpu_count and @tempdbfilecount>cpu_count) then 'No (Depends on continued Contention)'
end) AS [TempDB_DataFileCount_ChangeRequired]
from Processor_CTE;
 
5) Active session space consummation stats
SELECT
  sys.dm_exec_sessions.session_id AS [SESSION ID]
  ,DB_NAME(database_id) AS [DATABASE Name]
  ,HOST_NAME AS [System Name]
  ,program_name AS [Program Name]
  ,login_name AS [USER Name]
  ,status
  ,cpu_time AS [CPU TIME (in milisec)]
  ,total_scheduled_time AS [Total Scheduled TIME (in milisec)]
  ,total_elapsed_time AS    [Elapsed TIME (in milisec)]
  ,(memory_usage * 8)      AS [Memory USAGE (in KB)]
  ,(user_objects_alloc_page_count * 8) AS [SPACE Allocated FOR USER Objects (in KB)]
  ,(user_objects_dealloc_page_count * 8) AS [SPACE Deallocated FOR USER Objects (in KB)]
  ,(internal_objects_alloc_page_count * 8) AS [SPACE Allocated FOR Internal Objects (in KB)]
  ,(internal_objects_dealloc_page_count * 8) AS [SPACE Deallocated FOR Internal Objects (in KB)]
  ,CASE is_user_process
             WHEN 1      THEN 'user session'
             WHEN 0      THEN 'system session'
  END         AS [SESSION Type], row_count AS [ROW COUNT]
FROM 
  sys.dm_db_session_space_usage
INNER join
  sys.dm_exec_sessions
ON  sys.dm_db_session_space_usage.session_id = sys.dm_exec_sessions.session_id
Posted by at No comments:
Subscribe to: Posts (Atom)