Can anyone pls explain me??

Question

Post reply

Can anyone pls explain me??

kerobin jesu

SSC-Addicted

Points: 494

March 29, 2007 at 4:04 am

#173031

Hi all,

I've a stored procedure, while executin with set statistics io on i'm getting...

Table '#ProducedHour___________________________________________0000000012B7'. Scan count 1, logical reads 424, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#6DF65D3E'. Scan count 1, logical reads 21617, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Attendance'. Scan count 1, logical reads 469, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Can u explain me what is worktable and #6DF65D3E which has more reads... is it possible to get rid of that or to reduce the reads..??

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 1

#6Df65D3E is probably a statistic added to one of the tables because an index is needed for the query you're running and that index isn't there. The worktables are usually generated for order by's & group by's as part of processing that data. It could also be a distinct. Again it's caused when an index that would be needed by the process is missing. You need to look at the actual plan to see where these objects are being created and it will show you the table & column that needs the index.

----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

Steve Jones - SSC Editor SSC Guru Points: 728164 More actions · Answer 2

Grant's explanation makes sense. It looks like you're using a temp table as well, so that will get created in tempdb along with any other intermediate tables that SQL Server needs.

Can you post the query that generated this?

Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com

kerobin jesu SSC-Addicted Points: 494 More actions · Answer 3

Here is the query...

UPDATE #ProducedHour  SET                               
 ProducedHour = #ProducedHour.Units                        
FROM                       
 attendance a                               
WHERE                      
  #ProducedHour.courseMember_id  =  a.CourseMember_id  AND a.startWeekCode =  #ProducedHour.startWeekCode                                 
 AND datediff(d,StartDate,dateadd(d ,0 , dbo.getdatefromweekCode(#ProducedHour.startWeekCode)))=0                       
 AND a.StatusCodemon IN (SELECT value FROM dbo.splittext('P,A',','))

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 4

From this, you should try adding an index to the temp table, [#ProducedHour]. The first one I'd try, and you will need to test anything you do, would be to add a clustered index on [courseMember_Id] & [startWeekCode]. Other options might work better. Lots of tests are the key.

One thing I'd look at for sure is whether or not you need the temp table at all. From this code fragment, it looks like you're modifying the data in the temp table. Usually this is done when people aren't sure how to do a single select statement so instead they load the data into temp tables and then modify them several times before selecting from them as the output from a procedure. As you're seeing, this can be very ineficient.

----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 5

#6DF65D3E is the type of object name given to a table variable. The most likely origin of that is the function dbo.splittext, which looks like a table-valued function.

It could also be the other function (getdatefromweekCode). Can't see from the update whether or not it has a table variable. will need the code of it for that.

You'll have to take a look at the code in the functions to see if the table variable can be optimised. Table variables can't have indexes (other than the pk) and can't have statistics, so they're often hard to optimise.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass