Thank you Gail , in that case can I use a look up function for creating partition, my partition creation need a cluster index , hence can I use this...

To provide more context, on the HA setup we are looking we have two DC one we are planing to be HA and oter will be DR.

I have to present...

As this DW environemnt , we have created a BIG FAT datafile of 20 TB. The DB is only 40% full. Autogrowth is none now.

Thank you all for your valuable inputs. It was great learning.

I rebuild all the indexes of these tables. could still see fragementaion more then 20 - 25 %...

Please find the ddl

CREATE TABLE XXXX_XX(

[DM_CV_AGGR_FACT_POLICY_COVERAGE_ID] [int] IDENTITY(1,1) NOT NULL,

[POL_TYPE_ID] [int] NULL,

[GENDER] [varchar](1) NULL,

[DRIVER_AGE_RANGE_ID] [int] NULL,

[CARRIER_ID] [varchar](11) NULL,

[C_ST_ALPH] [nvarchar](10) NULL,

[LIMIT_ID] [int] NULL,

[BIZ_MONTHYEAR] [int] NOT NULL,

[BIZ_YEAR] [varchar](4) NULL,

[BIZ_MONTH] [varchar](2) NULL,

[TOT_POLICY_COUNT] [int]...

Lynn, what about the other cluster indexes which are simple int coloumn with identity values, why these all are frag tyo 90% ?

Thanks Lynn that was awsome catch. Will cluster index fragmentaion lead tonon cluster index frag also?

Hi Lynn,

Tables have PM / cluster index key on identity columns or the VARCHAR(1) . These VARCHAR(1) is nullable but I did search the tables there were no recs with...

Sure I will see if those comparison and scan are reduced.

Thank you all for the input , I am proud to be member of this forum.

We do truncate load to the staging table( source) , rebuild all in indexes...

Thanks Kevin.

Yes I agree I do see many transacations ( data loading runs for a long time with open_tran value euther 1 or 2)

I have asked the team to...

This is warehouse environment, we are pulling the subset and then will move this to warehouse table. I did capture this execution plan using dm_exec_sql_text and dm_exec_query_plan.

we have a...

Thanks Calvo, it was great help to me as the app team was waiting on this confirmation. Thank you.

Yes we have proposed it but they are not ready for mirroing or log shipping. As the database is very much static and small in size hence daily backup sync...

Thanks for helping to understand ho to debug the mdmp file. The KC 1000 output gives me the following result.

0:000> kc 1000

Call Site

kernel32!RaiseException

sqlservr!CDmpDump::Dump

sqlservr!SQLDumperLibraryInvoke

sqlservr!CImageHelper::DoMiniDump

sqlservr!stackTrace

sqlservr!stackTraceCallBack

sqlservr!ex_handle_except

kernel32!UnhandledExceptionFilter

ntdll!RtlUserThreadStart

ntdll!_C_specific_handler

ntdll!RtlpExecuteHandlerForException

ntdll!RtlDispatchException

ntdll!KiUserExceptionDispatcher

msxml6!Base::freeRentalObjects

msxml6!Base::StackExitNormal

msxml6!ModelInit::~ModelInit

msxml6!SAXReader::parse

MSO

0x0

0x0

0x0

ntdll!RtlAllocateHeap

any pointers? Not able to read or...