Performance Tuning Temp DB Slow INSERT from VIEW

I am running A View that INSERTS into #Temp Table - On Only Certain Days the INSERT Speed into #tempDB is so slow . Can any one point in Right direction. 

Attached snapshot that shows after one minute so many few records are inserted - and it dosent happen every day somedays its very fast. 

Thanks

May 16th, 2015 2:49am

1) how many TempDB Data Files?:- use multiple data files is to increase the I/O throughput to tempdb 

2) A default installation of any SQL Server edition will create a tempdb database with an 8MB data file and a 1MB transaction log file. For a lot of SQL Server installations these file sizes wont be enough, but they are configured to autogrow by 10% as needed.


http://www.brentozar.com/sql/tempdb-performance-and-configuration/

https://www.simple-talk.com/sql/database-administration/optimizing-tempdb-configuration-with-sql-server-2012-extended-events/


Free Windows Admin Tool Kit Click here and download it now
May 16th, 2015 3:00am

I am running A View that INSERTS into #Temp Table

A view can't insert anything anywhere...

In any case, with the miniscule information you have given, it is very difficult to say anything with certainty. But the most likely explanation is that you get different query plans at different occasions. Recall that the optimizer works with statistics that is sampled from the data and from this it makes an estimate of which is the best plan. This is anything but a deterministic process. Even less if the query has "problems" or there are not suitable indexes available.

There could be other reasons as well, as blocking, resource contention etc.

As a start you could post the query, so that we know what you are talking about. For the view, we need to see the view definition - as well as definition of any nested views. Most likely, we will ask for more information, but we start there.

May 16th, 2015 4:49am

What does the below query return (during the INSERT operation)

select   session_id, wait_duration_ms,   resource_description 
      from    sys.dm_os_waiting_tasks
      where   wait_type like 'PAGE%LATCH_%' and
              resource_description like '2:%'

Free Windows Admin Tool Kit Click here and download it now
May 17th, 2015 3:04am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics