A few questions on statistics

A few questions on statistics

Post by Doug Hanhar » Thu, 31 May 2001 06:33:35



I know that SQLSvr uses statistics, which can be either manually or automatically
created.  I've read much of the BOL regarding this, and it has answered many
questions, but I still don't quite have a picture of how they are used.  I've
heard quite a bit over the years about them, but don't know how much is rumor.

Is the following true?

1.  Statistics for indexed columns are contained within the index's row in
sysindexes.

2.  Statistics for non-indexed columns can somehow be used by the query optimizer
to optimize a query (even without an index).  If this is true, how are they
used?  I can sort of visualize how they would be used if an index existed, but
I'm at a loss on how they're used without one.

3.  Hypothetical indexes are only created during use of Index Tuning Wizard, and
dropped afterward (unless changes are immediately applied).

4.  Statistics are auto-generated whenever a where clause uses a column that has
no index or statistics.

5.  If you see an auto-generated statistic has been created, it is a good
candidate for replacement by an index.

6.  Since SQL Server auto-generates/updates statistics, the only time they would
be out of date is if you've set the "auto-statistics" options off.

7.  If an index exists, it must have associated statistics.

Thanks, all.  

 
 
 

1. A few questions (rules, many vs few dbs, wierd kernal errors)

Hello all,

I have a few questions someone my be able to shed some light on.

1) I need a rule to check for valid ether addresses.  has anyone written one?
   The gist is six 1 or 2 character hex numbers separated by :'s.  It's not too
   difficult to create one that only allows 6 2 character number but to allow
   1 or two character numbers is the harder part.   Some valid addresses may be
   0:0:0:0:0:0
   a:5f:3c:ff:ff:ff
   11:11:11:11:11:11

   anyone .. anyone...

2) Is it better to have many small databases or a few larger ones?  That is say
you have a number of apps that each require there own set of tables.  Is it
better to create separate databases for each app or create a few databases and
put apps together is some logical way?  Most important why do you fell the way
you feel.

3) I had a table with around 500 records.  It was working fine for a long time.
Then all of the sudden any updates or inserts into the table caused kernel
errors in the log file and the process was closed by the server.  I truncated
the table successfully and the same thing happened.  When I did a dbcc on the
database or the table nothing wrong was reported.  Finally I dropped and
recreated the table and all was fine.  Any ideas what was wrong and if the same
thing happens again any way to fix it without dropping the table?  Following is
an excerpt from the log file of one such insert.

00: 94/01/12 10:37:13.15 kernel: current process (0x5e0010) infected with Signal
11
00: 94/01/12 10:37:13.47 kernel: pc: 0x16da1c srcal8_7 (0x3a,0x1b8000,0x4b4680,0
x100,0x2,0x10f04c8)
00: 94/01/12 10:37:13.47 kernel: ************************************
00: 94/01/12 10:37:13.47 kernel: SQL causing error : insert into IICFtest..Equip
ment select * from IICF..Equipment

00: 94/01/12 10:37:13.51 kernel: curdb = 27 pstat = 0x10100 lasterror = 0
00: 94/01/12 10:37:13.51 kernel: preverror = 0 curcmd = 195 program = isql

00: 94/01/12 10:37:13.57 kernel: pc: 0x7210 terminate_process (0x0,0xffffffff,0x
 82cc08,0x0,0x3e)
00: 94/01/12 10:37:13.60 kernel: pc: 0x150e8c kisignal (0xb,0x3,0x4b3608,0x2,0x2
)
00: 94/01/12 10:37:13.63 kernel: pc: 0x18a4dc _sigtramp (0xac1020,0x2,0x3,0x10f1
654,0x0)
00: 94/01/12 10:37:13.67 kernel: pc: 0x7289c run (0x3a,0x1b8000,0x4b4680,0x100,0
x2)
00: 94/01/12 10:37:13.68 kernel: pc: 0xe1610 exec_eop (0x10f1314,0x10f14dc,0x0,0
x1ad8,0x0)
00: 94/01/12 10:37:13.69 kernel: pc: 0xe0ab4 execute (0x10f12e4,0x10f1314,0xc3,0
x10f0448,0x1)
00: 94/01/12 10:37:13.70 kernel: pc: 0x10f7d0 s_execute (0x10f1314,0x400000,0x10
f12e4,0x10f1314,0x10f12e4)
00: 94/01/12 10:37:13.71 kernel: pc: 0x7f7bc sequencer (0x0,0x71c804,0x10f02dc,0
x0,0x4adb18)
00: 94/01/12 10:37:13.72 kernel: pc: 0xc94b0 conn_hdlr (0x1ad8,0x80000000,0x4000
00,0x1,0x1)
00: 94/01/12 10:37:13.75 kernel: pc: 0x153db8 _coldstart (0x4,0xc78e8,0x0,0x0,0x
0)
00: 94/01/12 10:37:13.75 kernel: end of stack trace, spid 6, kpid 6160400, suid
535

Thanks for any and all help.
--
Scott

2. ISQL - retrieval of records

3. few few index cursors error

4. Question on imbedded single quotes in Record Find stat

5. Image columns - a few questions

6. Using a defined DataType as a DataSource of a DataReport

7. a few questions

8. Distributed databases

9. Few Minor Questions I have about SQL SERVER

10. A few questions ...

11. starting sql, have a few simple questions

12. A few replication questions

13. starting sql, have a few simple questions