February 3, 2012 at 6:41 am
Hi , I hope im posting this on the right forum.
I have been experiencing major performance issues on my 2008 SQL server and seeing inconsistent performance between multi line TVF functions and the same code in side them being executed (Yes i know of the evils of TVF's and lack of statistics...etc)
I have been monitoring the locks put in place when the function executes compared to the code inside the function and everything looks exactly the same except for these additional locks on the TempDb :
DB[BULKOP_BACKUP_LOG]
DB[BULKOP_BACKUP_DB]
DB[ENCRYPTION_SCAN]
AU[BULK_OPERATION_PAGE]
HBT[BULK_OPERATION]
HBT[BULK_OPERATION]
I dont know if this is the cause for performance degredation , but i have also seen the function run as quickly as the inside code and the locks that the process created at that time did not include these additional locks on the temp db.
In both cases i cleared the buffer with DBCC dropcleanbuffers before i executed.
Am i barking up the wrong tree ?
Has any one else experienced these issuses?
Any help in the right direction would be really appreciated.
February 3, 2012 at 7:12 am
The table value function is building a table. That table gets built in tempdb. If you're just running the query, it may also do tempdb work, but it doesn't have the added overhead of the TVF. I'm fairly certain that's what you're seeing.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 3, 2012 at 7:17 am
The strange thing is that i have executed the function and seen markedly quicker results , but the additional locks on tempdb were not there.
Not like they were when the execution slowed down.
February 3, 2012 at 7:25 am
Hmmm... Contention for resources? Other processes running at the same time? Ummm... Not sure. But if you're getting pain from a multi-statement TVF, the best solution I know is to toss the stinking thing. They're not always bad or inherently evil, but they're generally dangerous and problematic.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 6, 2012 at 1:49 am
Thanks , pity TVF's are useful for filtering results
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply