Introduction When deploying SQL Server on Azure VMs, you can boost tempdb performance by storing it on ephemeral (temporary) storage. Microsoft recommends using the local SSD (often D: drive) for tempdb. However, if your SQL Server runs under a Managed…
declare@databaseName varchar(100) =’mydb’,@datapath varchar(2000) = ‘C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\’,@period varchar (50) = ‘QUARTER’, — DAY, WEEK, MONTH, QUARTER, YEAR@firstFilegroupDate date =’2024-01-01′,@lastFilegroupDate date =’2027-02-01′,@tempDate date,@yearpart varchar (4),@quarterpart varchar (1),@monthpart varchar (3),@daypart varchar(2),@filegroupNameInit varchar(50)=’PART’,@filegroupName varchar(100),@fileName varchar(100),@sql nvarchar(3000)=”,@debug bit =0,@execution bit=0,@fgNo int=0,@i int=0;BEGINIF…
A Distributed Availability Group (DAG) is an Availability Group that instead of nodes it consiss of two or more other Availability Groups. It is quite flexible and Availability Groups can be of different architecture, version even OS. For example it…
Find which table references a sequence qith the following statement: SELECT s.object_id AS sequence_object_id,<br>s.name AS sequence_name,<br>OBJECT_SCHEMA_NAME(o.parent_object_id) + '.'<br>+ OBJECT_NAME(o.parent_object_id) AS table_name,<br>r.*<br>FROM sys.sequences s<br>CROSS APPLY sys.dm_sql_referencing_entities(<br>OBJECT_SCHEMA_NAME(s.object_id) + '.' + s.name,<br>'OBJECT'<br>) r<br>JOIN sys.objects o<br>ON o.object_id = r.referencing_id
SELECT o.name as table_name, rv.value as partition_range,<br>fg.name as file_groupName, p.partition_number,max(p.rows) as number_of_rows,<br>sum(au.total_pages) *<em>8 [Total MB],sum(au.used_pages)</em>*8 [Used MB]<br>FROM sys.partitions p<br>INNER JOIN sys.indexes i ON p.object_id = i.object_id AND p.index_id = i.index_id<br>INNER JOIN sys.objects o ON p.object_id = o.object_id<br>INNER JOIN sys.system_internals_allocation_units au…
with cols as (select s.name,st.name table_name,ac.name column_name,t.name column_type,ac.max_length,ac.precision,ac.scale,casewhen t.name in (‘datetime’,’datetime2′,’date’) then ‘DATE’end oracle_typefrom sys.all_columns ac join sys.types t on t.system_type_id=ac.system_type_idjoin sys.tables st on st.object_id=ac.object_idjoin sys.schemas s on s.schema_id=st.schema_idwhere st.name!=’sysdiagrams’),grp as(select upper(table_name) table_name, col= upper(STUFF ((SELECT ‘,’ + column_name+’ ‘+isnull(oracle_type,”)…
select s.name,st.name table_name,ac.name column_name,t.name column_type,ac.max_length,ac.precision,ac.scale,casewhen t.name=’sysname’ then ‘nvarchar2(128)’when t.name=’nvarchar’ then ‘nvarchar2(‘+cast(ac.max_length as varchar)+’)’when t.name=’varchar’ then ‘varchar2(‘+cast(ac.max_length as varchar)+’)’when t.name=’char’ then ‘char(‘+cast(ac.max_length as varchar)+’)’when t.name=’int’ then ‘number(10)’when t.name=’bigint’ then ‘number(19)’when t.name in (‘float’,’real’) then ‘number’when t.name=’smallint’ then ‘number(5)’when t.name=’tinyint’ then ‘number(3)’when…
Use the following statements to find partitioned tables with non aligned indexes. Unaligned indexes forbid partition switch.
Automatic Partition SET ANSI_NULLS, QUOTED_IDENTIFIER ON; <br>GO <br>use <Database> <br>Declare <br>@nextRange date, <br>@sql nvarchar(3000), <br>@maxBoundary date, <br>@tableList varchar(1000) ='Table1,Table2,Table3', <br>@tableName varchar(100), <br>@partitionFunction varchar(100), <br>@partitionScheme varchar(100), <br>@PartitionedTables as cursor , <br>@debug bit =0, <br>@monthsPartitionFuture smallint =7,<br>@filegroup = 'Primary' ; <br>set @PartitionedTables…