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

Asger Blond ab-mi at post3.tele.dk
Sat Dec 5 20:46:58 CST 2009


Sure you can:
USE tempdb
SELECT * FROM #MyTable
EXEC sp_help #MyTable
EXEC sp_helpindex #MyTable
-- and even:
ALTER TABLE #MyTable ADD MyNewColumn char(10)

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 03:24
Til: Discussion concerning MS SQL Server
Emne: Re: [dba-SQLServer] R: SPAM-LOW: Re: Do I need a cover index?

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