Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-sqlserver > #1474

Re: Some questions about relational indexes and statistics in SS2005

Show all headers | View raw

On Tuesday, 7 May 2013 20:26:41 UTC+1, Erland Sommarskog  wrote:
> (rja.carnegie@gmail.com) writes:
> 
> > 2. Will SQL Server ever automatically create a separate statistics group 
> >    on columns that are already the key of a clustered or nonclustered 
> >    index?  And, if so, should I consider improving the index so that 
> >    it provides a satisfactory statistics group, and, how can I do that?
> 
> Not sure that I understand the question, but SQL Server always creates
> statistics on the index columns. I have never heard "statistics group"
> before, but maybe you mean multi-column statistics.

Thanks for your helpful advice.  I'd better clear up that point first:
if I execute "CREATE STATISTICS Erland ON Accounts(Balance)", what is
Erland?  That's to say, Erland is a what?  ;-)  I went back to the
SQL Server 2005 Books Online and found one or more pages that say
that Erland is "a statistics group (collection)" - usually with
all of those words.  But going by the SQL Server 2012 manual, 
I should say "Erland are statistics".  In my own language, a name
usually belongs to a singular thing, something that there's one of.
But apparently SQL Server differs in this!  :-)

I'm feeling more confident now that if I create an Erland on a
column, SQL Server won't create another auto-stat Erland on the 
same column - which is liable to happen while a user is waiting
for their query to run.  I don't know if it's important to 
avoid that, but I'm more confident that my Erlands need to be
up-to-date, and for some reason that isn't always the case, 
since performing "UPDATE STATISTICS" on every table improves
the queries.  I /assume/ that auto-stats only happen when 
a query wants to read from a table, so, ...hmm, I suppose
that a table update statement reads /and/ writes.  And I was
looking today at a program that does that several times.
Okay.  I probably should have paid more attention to that.
Our database designs, you see, are baroque.  I think that's
the word?  Or a word that sounds like it?  ;-)

Well, my grand scheme includes (1) writing a stored procedure
that defines, say, ten or twenty indexes and statistics on a 
table, but allows them to be created one by one, and, 
(2) using the same interface to perform "index rebuild"
and "update statistics" on the target - that part isn't 
done yet.  And this is where the table may be called
Student_2011, Student_2012, Student_2013, and may be in
database UserStuff01, UserStuff02, UserStuff03, and so on:
the interface stored procedure addresses that complexity.

I also want to avoid having the server do some of this meta-work
more than once.  Except that it deletes a lot of the data every
day and loads it in from where it came from again.  So, make that
"more than necessary".

So, I guess I'm going to need some or all of those features in my
tool, which may mean, writing the parts that I haven't written yet.

Alternatively, I may finally convince my boss that a lot of things
will run acceptably fast if they install a bigger, better server,
and the way that they pay for it is to let me go - as they say.

And then it won't be my problem any more.

Back to comp.databases.ms-sqlserver | Previous | Next — Previous in thread | Next in thread | Find similar

Thread

Some questions about relational indexes and statistics in SS2005 rja.carnegie@gmail.com - 2013-05-07 04:39 -0700
  Re: Some questions about relational indexes and statistics in SS2005 Erland Sommarskog <esquel@sommarskog.se> - 2013-05-07 21:26 +0200
    Re: Some questions about relational indexes and statistics in SS2005 rja.carnegie@gmail.com - 2013-05-09 14:45 -0700
      Re: Some questions about relational indexes and statistics in SS2005 Erland Sommarskog <esquel@sommarskog.se> - 2013-05-10 11:05 +0200
        Re: Some questions about relational indexes and statistics in SS2005 rja.carnegie@gmail.com - 2013-05-12 10:01 -0700
          Re: Some questions about relational indexes and statistics in SS2005 Erland Sommarskog <esquel@sommarskog.se> - 2013-05-12 19:22 +0200

csiph-web