November 14, 2011 at 9:14 am
We have a job which runs that determines which indexes require rebuilding (based on size and % fragmentation). The job uses dynamic T-SQL commands to rebuild all indexes on any table which has a fragmented index (there is a reason for this that is unimportant to the issue). When the rebuild command is executed, it is called without specifying any value for the SORT_IN_TEMPBDB option so, it should default to OFF, according to BOL. Yet, when the command runs, the tempdb grows in size during processing.
Can anyone help me to understand why? What I am trying to determine is why is the tempdb growing during an operation which should not be using it for any purpose.
Extra info...
Each of our tables has a the primary key index as the clustered and the primary key is a pseudo key of INT with an IDENTITY(1,1). Additional indexes are then built for any foreign key and commonly searched fields.
We have several tables which are 50M+ records or larger with our largest table being 260M+ records.
System info: SQL Server 2008, standard, 64 bit, version 10.50.2500.0, 32GB RAM, 4 quad core processors. User DB files stored on NetApp SAN with seperate LUN's (RAID 10)for data and log files. Tempdb stored on internal disk to server which only tempdb uses.
Job executes during low throughput times for the db which we use for our nightly maintenance window. There is also a NetApp snapshot with verification that executes during this time frame.
Scott B Dragoo
Enterprise Architect
Vitality Group
http://www.thevitalitygroup.com
November 14, 2011 at 9:24 am
Doing your rebuilds online?
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
November 14, 2011 at 9:28 am
No. We are using Standard Edition which does not support online rebuilds of indexes.
Scott B Dragoo
Enterprise Architect
Vitality Group
http://www.thevitalitygroup.com
November 14, 2011 at 10:02 am
There is also a NetApp snapshot with verification that executes during this time frame.
Verification as in mounting of the snapshots & DBCC CheckDB run against them?
This would cause TempDB use I believe.
November 14, 2011 at 10:11 am
Right. I expect that it would. But that process runs every night and the index rebuild process only runs twice per week. Only at the times when the index rebuild process runs does the tempdb grow to really large size where we get low disk space warnings and have had the tempdb fill it's hard drive.
Scott B Dragoo
Enterprise Architect
Vitality Group
http://www.thevitalitygroup.com
November 14, 2011 at 10:27 am
Ah. Well, not that then!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply