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 > >