April 14, 2009 at 8:48 am
Hi,
I've been doing some bench marking on one of my servers and I've detected some tables which are generating a large number of full scans / sec, these legacy tables have no indexes, hence the full scans.
However interestingly I also have a large number of transactions / sec on my tempdb, I was wondering if these two could be related, my perfmon graphs show a likely corralation between the two, so I'm inclined to think they are.
However I was just wondering on the exact mechanism that SQL will use to resolve these, will it utilise tempdb for this operation? I wouldnt of expected it to, however they are small tables (based on row counts), so could it be using tempdb as a more effecient method of reading this data?
Any comments would be welcome.
Regards,
Jackal
April 14, 2009 at 9:01 am
Jackal (4/14/2009)
However I was just wondering on the exact mechanism that SQL will use to resolve these, will it utilise tempdb for this operation?
No, it's just a table scan.
I wouldnt of expected it to, however they are small tables (based on row counts), so could it be using tempdb as a more effecient method of reading this data?
No. TempDB is used for temp tables and other temporary objects. It's not part of a table scan. It may be that the queries that cause those scans have one of more of the following in them, which do use TempDB:
Temp Tables
Table variables
Hashs (hash joins or hash aggregates)
Sorts
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
April 14, 2009 at 9:04 am
Thanks Gail,
I did think it was a little strange for the tempdb transaction/sec to be directly related to the fullscans/sec.
Although as you point out the procedures may be causing the relationship (via the sorts etc).
I'll get looking into the them, thank you very much for your help.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply