you can run DBCC FREEPROCCACHE it removes all elements from the procedure cache.

To verify it clears procedure cache run DBCC PROCCACHE

Prakash

Now after running for 36 hours that tasks finished. The concern was wait time was shown as 98767000 miliseconds for some threads while for other threads are showing 51000 miliseconds....

It's non clustered indexes that if create on different disk gets benifitted(conditional: if the data required to read or write (disk I/O) is not enough for the disk.)

Clustered index should...

Service pack 2 is released.....

I guess to start with you should use profiler to capture events and then analyze the events where it's taking long......

Cheers,

Prakash

Thanks to all,

I checked with all this stuff this being very helpful.

Prakash

Some thoughts:

Can't you use distributed Partitioned vievs I guess if fits to ur needs, this is best.

Regarding tables if you are reading lot of data then it's good to place...

If you want to create your own procedures for a particular requirement instead of the functionality given.

Better use sp_helptext on the procedure in master to get the procedure...

It's a datawarehouse of bank...

Basically when a single procedure is run it takes some two to three hours while if we run it parallaly with some other processes it get...

Compile this procedure this will give you enough information about locks.......

Cheers,

Prakash

drop proc ph_blocked_details

go

create proc ph_blocked_details as

DECLARE @BLOCKED_SPID INT,

@Blocking_SPID INT,

@HOSTNAME_BlockedProccess varchar(50),

@PROGRAM_NAME_BlockedProccess varchar(50),

@HOSTNAME_BlockingProccess varchar(50),

@PROGRAM_NAME_BlockingProccess varchar(50),

@dbNameOfBlocked varchar(4000),

@dbNameOfBlocking varchar(4000),

@statusOfBlocked varchar(4000),

@statusOfBlocking ...

Check performance monitor for disk queue length ,total processor and buffer cache

If disk queue length is more than 2 per disk in array then Disk I/O is culprit

If total processor...