[dba-SQLServer] R: SPAM-LOW: Re: Do I need a cover index?

jwcolby jwcolby at colbyconsulting.com
Sat Dec 5 19:46:14 CST 2009


Asger,

A month or so back I did move the TempDB off to it's own "spindle", a two drive raid 0 array, 600 
gigs, nothing on it except the temp db.

Do you think it would be worthwhile to make this large, like 50 gigs or 100 gigs or even more? 
Again I have plenty of room, the array is dedicated to the temp drive.  If "expanding" is a high 
cost operation then perhaps just making it huge would be a good thing?

The operations I am discussing import and export tens of gigs of data.

Thanks for telling me about this.  I have been thinking about doing something like this, but was 
considering creating databases on-the-fly to do this stuff.  Using the tempdb sure looks much easier!

John W. Colby
www.ColbyConsulting.com


Asger Blond wrote:
> Sorry John, I only read the first two lines of your posting right through to
> ;) - thinking the rest was from another response not related to my comment.
> Now I happen to notice that your reply was far longer...
> As I see you create temporary tables in a normal user db populating and then
> dropping them, which causes the normal user db and its log to grow.
> Did you consider creating these tables in the special tempdb database
> instead? You create a table in the tempdb database using a # in front of the
> tablename, e.g. #MyTable. This kind of table will automatically be deleted
> when the connection creating it is closed and it will not cause any growth
> of our normal user db. Since the tempdb database is using the "simple
> recovery model" it will also automatically truncate its own log file
> whenever an operation is finished.
> To make efficient use of the tempdb database you might want to place its
> data file and log file on separate fast drives and enlarge the files (by
> default both files are stored in the Data-directory of your SQL Server
> installation and have a size of 8MB and 1MB respectively). You change the
> default placement and size of the tempdb this way:
> 
> ALTER DATABASE tempdb MODIFY FILE 
> (name='tempdev', filename='X:\TempDBData\tempdb.mdf')
> GO
> ALTER DATABASE tempdb MODIFY FILE 
> (name='templog', filename='Y:\TempDBLog\templog.ldf')
> GO
> ALTER DATABASE tempdb MODIFY FILE(name = 'tempdev', size = 2000 MB )
> GO
> ALTER DATABASE tempdb MODIFY FILE(name = 'templog', size = 500 MB )
> GO
> After this you have to restart SQL Server to make the changes effective.
> 
> HTH
> Asger
> -----Oprindelig meddelelse-----
> Fra: dba-sqlserver-bounces at databaseadvisors.com
> [mailto:dba-sqlserver-bounces at databaseadvisors.com] På vegne af Asger Blond
> Sendt: 4. december 2009 03:44
> Til: 'Discussion concerning MS SQL Server'
> Emne: Re: [dba-SQLServer] SPAM-LOW: Re: Do I need a cover index?
> 
> For once you asked a simple question. The answer is: big enough.
> Asger
> 
> -----Oprindelig meddelelse-----
> Fra: dba-sqlserver-bounces at databaseadvisors.com
> [mailto:dba-sqlserver-bounces at databaseadvisors.com] På vegne af jwcolby
> Sendt: 4. december 2009 02:57
> Til: Discussion concerning MS SQL Server
> Emne: Re: [dba-SQLServer] SPAM-LOW: Re: Do I need a cover index?
> 
> Thanks Asger.  I will take #5 to heart though I will have to figure out how
> to figure out what is a 
> "good size".
> 
> ;)
> 
> 
> As I mentioned, at the moment I don't really have a lot of "new databases".
> I do occasionally get 
> new lists, but it is just occasionally.  Where I do much more is in monthly
> maintenance.  Every 
> month I update the database, which creates new temp (currently) tables
> inside of the existing 
> database.  As an example I create a sorted output table (on zip5/zip4) and
> copy every record into 
> that table.  I then take 2 million records at a whack and place them in a
> temp table, created on the 
> fly.  BCP out that "chunk".  Drop the table, create it all over again, copy
> the next 2 million 
> records in.  BCP out.  Rinse repeat until all 50 million records are BCPd
> out.
> 
> As you might imagine that process causes enormous file growth, particularly
> in my log file but also 
> in my main database file.
> 
> These database are largish, at least coming from Access where the db file is
> never more than a few 
> hundred million bytes.  I might start with a 30 gig file and end up with a
> 100 gig file after 
> processing.  The more I think about it the more I think I want to go with a
> temp db to perform this 
> export, as well as a matching import.  Again though, how big is big enough.
> I do an 11 million 
> record file, then a 21 million record file, then a 50 million record file
> etc.  Just building an 
> empty 50 gig file takes a long time for SQL Server.
> 
> I don't have any answers on how to figure this out.  As I get my C# control
> program happening I will 
> be logging the start / stop time for each step (stored procedure) of this
> process.  I could also log 
> start and stop file sizes for the database files.
> 
> Thus I could intentionally select different strategies and view my log files
> to see the times 
> required for the strategy.
> 
> As of now I don't have that in place, though I am actively working on it.
> 
> The other thing I want to do is get my C# controller able to detach and
> attach these databases.  If 
> I can detach them programmatically then I can have defrag running at night.
> I own Perfectdisk 
> server defrag which does a very good job at consolidating free space but the
> database files have to 
> be detached.
> 
> BTW I moved my log files out to a separate "spindle", that being a raid
> array based on its own set 
> of disks.  I am looking at the log files which I have been using a lot
> lately and while they are 
> large, the actual number of fragments seems (to me) to be pretty reasonable.
> 
> 50 gig - 4 Fragments
> 25 gig - 2 Fragments
> 30 gig - 6 Fragments
> 
> Most of them are in the 2-6 fragment range.  ONE is 16 fragments and it is
> "small" at 1.5 gig.
> 
> That is log file data.  And understand that I often shrink the log files
> which usually shrinks them 
> back down to 1 meg, and the disk was defragged awhile back to get all of the
> free space together. 
> But these log files were all created (expanded) since that defrag.
> 
> I think it is useful to remember that my databases are not transactional
> databases, and activity is 
> concentrated in a single database as I perform this maintenance, then I move
> on to the next 
> database.  Thus it is not like there are 20 highly varied databases all
> mixing it up on a minute by 
> minute basis.
> 
> 
> John W. Colby
> www.ColbyConsulting.com
> 
> 
> Asger Blond wrote:
>> Some points to consider:
>> 1. A clustered index on the PK will be useful for the join operation.
>> 2. A non clustered index on the filtering field will be useful for the
>> search operation.
>> 3. In your scenario the best choice IMO is a clustered index on the PK and
> a
>> non clustered index on the filtering field. As mentioned in my previous
>> posting this index will automatically include the values of the PK in the
>> bottom of its index tree (the "leaf level"). Perhaps your searches don't
>> specify the PK value (I guess not, since you use surrogate keys, which of
>> cause are not candidates for searches). But nice to know anyhow - it
>> certainly should prevent you from creating a composite index on the PK
> plus
>> your filtering field: this kind of index would be an enormously waste of
>> space and performance.
>> 4. If you don't have a clustered index, then your table is a "heap". This
>> means that the records can go anywhere in the physical file. A heap can be
>> very efficient for inserts, because SQL Server then don't have to bother
>> where to drop the records, it just scatters the records wherever room is
>> available. But it is very-very inefficient for searches and joins, because
>> then SQL Server has to consult a special internal table named IAM to find
>> where it happened to drop your damned records. That's why it's normally
> bad.
>> But you could imagine situations where this would be very well, for
> example
>> an auditing table populated by a trigger.
>> 5. If you want to make your inserts create huge amounts of disk
>> fragmentation then just go ahead using a small-sizes db relying on auto
> grow
>> of your db. If you don't want that then follow Mark's advice and size your
>> db initially to the anticipated size.
>> Asger
> 
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
> 
> 
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
> 
> 
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
> 
> 



More information about the dba-SQLServer mailing list