yes,

execute -- reset wiat stats

dbcc sqlperf('sys.dm_os_wait_stats',clear);

go

wait for 1 hr-2hrs

select wait_type,waiting_task_count,wait_time_ms,signal_wait_time_ms from sys.dm_os_wait_stats

and check the stats

That is understood I want the exact difference as well.

check the drive on which your ssis is installed and path of your temp and cache folders.

try to optimize the process use minimum sorting operations

SELECT dm_ws.wait_duration_ms,

dm_ws.wait_type,

dm_es.status,

dm_t.TEXT,

dm_qp.query_plan,

dm_ws.session_ID,

dm_es.cpu_time,

dm_es.memory_usage,

dm_es.logical_reads,

dm_es.total_elapsed_time,

dm_es.program_name,

DB_NAME(dm_r.database_id) DatabaseName,

-- Optional columns

dm_ws.blocking_session_id,

dm_r.wait_resource,

dm_es.login_name,

dm_r.command,

dm_r.last_wait_type

FROM sys.dm_os_waiting_tasks dm_ws

INNER JOIN sys.dm_exec_requests dm_r ON dm_ws.session_id = dm_r.session_id

INNER JOIN sys.dm_exec_sessions dm_es ON dm_es.session_id = dm_r.session_id

CROSS APPLY sys.dm_exec_sql_text (dm_r.sql_handle) dm_t

CROSS APPLY sys.dm_exec_query_plan (dm_r.plan_handle) dm_qp

WHERE dm_es.is_user_process...

Timeout occurred while waiting for latch: class 'SERVICE_BROKER_TRANSMISSION_WORKTABLE', id 0000000003472E00, type 4, Task 0x00000000043DC748 : 0, waittime 66900, flags 0x1a, owning task 0x000000000BEBC508. Continuing to wait.

I restarted mssqlserver . I did that cause the server was hanging and TempDB was consuming 1 TB disk space. The service was refreshed but showed the same...

Thanks all for your valuable inputs. As lowell pointed out yeah in past we did create #tmptable with million-billion rows. And one of the servers with (1TB- TempDB) had bad...

Thanks Jayant. I have restarted the service and would look into the settings.

Thanks Gila monster ..I used Create Database and it works fine ... thanks for your guidance 🙂

Hi Andy,

I tried the solution link provided by you its just great. Need to know if you have any solution for attaching multiple mdf files with the same name ?

G,

your query has 2 input tables but if you check CTE it is taking input from the same table.

On the first thought, I too thought of using a function...

Sorry Chris the thank was for you 😛

Thanks for the reply.

By chance, I came across a very good blog about the same which uses the following CTE :-

(http://weblogs.sqlteam.com/jeffs/archive/2008/02/21/Top-N-Percent-per-Group.aspx)

with AttributebyRegion as

(

select...

Thanks Paul for the knowledge, I would definitely keep these tips in mind.

Thanks Smith for your solution.

Do you guys think its possible in If...else as well ?

SQL_By_Chance (1/9/2012)

---

Hi Smith,

Apologies for the same :-

insert into sales (ID,Sales_Jan,Sales_Feb,Sales_Mar,Sales_Apr,Sales_May)

values

(1,100, 0,0,0 ,0 )

insert into sales (ID,Sales_Jan,Sales_Feb,Sales_Mar,Sales_Apr,Sales_May)

values

(2,120,0,200,300,0)

insert into sales (ID,Sales_Jan,Sales_Feb,Sales_Mar,Sales_Apr,Sales_May)

values

(3,0,100,0,300,0)

insert into sales (ID,Sales_Jan,Sales_Feb,Sales_Mar,Sales_Apr,Sales_May)

values

(4,100,200,100,100,100)

insert into sales (ID,Sales_Jan,Sales_Feb,Sales_Mar,Sales_Apr,Sales_May)

values(5,100,0,100,100,100)

--- Sales ID to populated...