X-Received: by 10.224.42.141 with SMTP id s13mr10644422qae.3.1368135925079; Thu, 09 May 2013 14:45:25 -0700 (PDT) X-Received: by 10.49.109.34 with SMTP id hp2mr1314143qeb.33.1368135925044; Thu, 09 May 2013 14:45:25 -0700 (PDT) Path: csiph.com!v102.xanadu-bbs.net!xanadu-bbs.net!news.glorb.com!l3no4152000qak.0!news-out.google.com!y6ni20871qax.0!nntp.google.com!m7no4208640qam.0!postnews.google.com!glegroupsg2000goo.googlegroups.com!not-for-mail Newsgroups: comp.databases.ms-sqlserver Date: Thu, 9 May 2013 14:45:24 -0700 (PDT) In-Reply-To: Complaints-To: groups-abuse@google.com Injection-Info: glegroupsg2000goo.googlegroups.com; posting-host=188.28.57.216; posting-account=dELd-gkAAABehNzDMBP4sfQElk2tFztP NNTP-Posting-Host: 188.28.57.216 References: <34bd78e8-b0d3-447c-a5de-88d35d6b6383@googlegroups.com> User-Agent: G2/1.0 MIME-Version: 1.0 Message-ID: <78faabb3-916b-472e-98f2-095fa7f6dd9e@googlegroups.com> Subject: Re: Some questions about relational indexes and statistics in SS2005 From: rja.carnegie@gmail.com Injection-Date: Thu, 09 May 2013 21:45:25 +0000 Content-Type: text/plain; charset=ISO-8859-1 Xref: csiph.com comp.databases.ms-sqlserver:1474 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.