 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
SQL Server Forum / General / Data Warehousing / October 2004SORT_IN_TEMPDB not use tempdb???
Hi, I've an index to create around a table with 3millions of rows. my tempdb database is on a different set of disks, so I use the SORT_IN_TEMPDB when I recreate my index, but I don't see any activity around the tempdb database and the time to create the index is the same! any idea? thanks, Jerome. Are you sure that tempdb is actually on a different set of physical disks or just a Logical drive? What is the actual command you used? This is only applicable on creating an index not rebuilding one.  SignatureAndrew J. Kelly SQL MVP > Hi, > [quoted text clipped - 7 lines] > thanks, > Jerome. its a command like this: create index [IndexName] on Table (col1, col2 ...) with fill_factor=100, SORT_IN_TEMPDB on primary Does SQL Server test if the tempdb database is on the same hard drive as the target index file? > Are you sure that tempdb is actually on a different set of physical disks > or just a Logical drive? What is the actual command you used? This is [quoted text clipped - 11 lines] >> thanks, >> Jerome. No it does not know the difference between a physical drive array and a logical drive per say. As Derrick mentioned how are you determining that tempdb is not being used? How much memory do you have? Maybe you have enough free memory that the physical drive amy not matter as much. SignatureAndrew J. Kelly SQL MVP > its a command like this: > create index [IndexName] on Table (col1, col2 ...) with fill_factor=100, [quoted text clipped - 18 lines] >>> thanks, >>> Jerome. How are you monitoring the activity in tempdb? > Hi, > [quoted text clipped - 7 lines] > thanks, > Jerome. the tempdb database appear to never growth and I don't see any connection to the database. the size of tempdb is only 50mb which is less then the size of my index. but like andrew says, maybe all is in memory. can I validate this? > How are you monitoring the activity in tempdb? > [quoted text clipped - 10 lines] > > thanks, > > Jerome. How big is the column you are indexing? You only have 3 Million rows and that's over 15 bytes per row. SignatureAndrew J. Kelly SQL MVP > the tempdb database appear to never growth and I don't see any connection > to [quoted text clipped - 17 lines] >> > thanks, >> > Jerome. How large is the table? Is it possible that the sort fits in memory? You can use the windows perfmon counters to monitor the IO activity in tempdb. Signature-- Wei Xiao [MSFT] SQL Server Storage Engine Development http://weblogs.asp.net/weix This posting is provided "AS IS" with no warranties, and confers no rights. <J?? <willgart@BBBhotmailAAA.com>> wrote in message news:%23ClIQmKtEHA.376@TK2MSFTNGP09.phx.gbl... > Hi, > [quoted text clipped - 7 lines] > thanks, > Jerome. I think its in memory the table is smaller than the memory on the system... > How large is the table? Is it possible that the sort fits in memory? > [quoted text clipped - 12 lines] > > thanks, > > Jerome. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.