jwcolby
jwcolby at colbyconsulting.com
Sat Dec 5 20:23:59 CST 2009
Asger, Working with the tempdb is a little unsettling. With a normal table I was able to see what fields I had created, if my index create worked, how many records were in the table, even view the data in the table. With the tempdb I can see that the table exists, but can't "do anything" with it. John W. Colby www.ColbyConsulting.com Asger Blond wrote: > John, > Yes, moving the tempdb off (as you did) is very good, and enlarging the size > of its data and log files is certainly a point too: relying on auto growth > for the tempdb is as bad as it is for a normal user db. > BTW: SQL Server 2005 makes much more use of the tempdb database for its own > operations than previous versions did - and SQL Server 2008 does so even > more. So that's another good reason to replace and resize this database. > Asger > > -----Oprindelig meddelelse----- > Fra: dba-sqlserver-bounces at databaseadvisors.com > [mailto:dba-sqlserver-bounces at databaseadvisors.com] På vegne af jwcolby > Sendt: 6. december 2009 02:46 > Til: Discussion concerning MS SQL Server > Emne: Re: [dba-SQLServer] R: SPAM-LOW: Re: Do I need a cover index? > > 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 >> >> > _______________________________________________ > 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 > >