X-Received: by 10.224.110.68 with SMTP id m4mr12145941qap.2.1364408423388; Wed, 27 Mar 2013 11:20:23 -0700 (PDT) X-Received: by 10.49.12.16 with SMTP id u16mr1522838qeb.31.1364408423345; Wed, 27 Mar 2013 11:20:23 -0700 (PDT) Path: csiph.com!v102.xanadu-bbs.net!xanadu-bbs.net!news.glorb.com!t2no22395129qal.0!news-out.google.com!k8ni11100qas.0!nntp.google.com!ca1no12936023qab.0!postnews.google.com!glegroupsg2000goo.googlegroups.com!not-for-mail Newsgroups: comp.databases.ms-sqlserver Date: Wed, 27 Mar 2013 11:20:23 -0700 (PDT) In-Reply-To: Complaints-To: groups-abuse@google.com Injection-Info: glegroupsg2000goo.googlegroups.com; posting-host=15.227.185.75; posting-account=qJFqbQkAAACYQSLN0-cvP6ydkRfuOu6u NNTP-Posting-Host: 15.227.185.75 References: <0e799bb4-d98b-422d-8d01-c2f0a0ac5810@googlegroups.com> User-Agent: G2/1.0 MIME-Version: 1.0 Message-ID: <1828832e-e5c7-40c8-b73f-cfd4eb427bb0@googlegroups.com> Subject: Re: Elementary management of many indexes, best practice? From: Mark D Powell Injection-Date: Wed, 27 Mar 2013 18:20:23 +0000 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Xref: csiph.com comp.databases.ms-sqlserver:1419 On Friday, March 15, 2013 10:23:09 PM UTC-4, rja.ca...@gmail.com wrote: > On Tuesday, 12 March 2013 21:36:36 UTC, Erland Sommarskog wrote: > (rja.c= arnegie@gmail.com) writes: > > I have data to work on spread through many t= ables in many databases, > > with names such as [BO-Stud18].[dbo].[Calendar= _2012], where all > > "Calendar" tables have the same structure, or more or= less so. > > Sometimes these tables are dropped and re-created. This isn't= an ideal > > design, but I'm kind of stuck with it. > > I would probably s= tart there, as home-built partitioning tends to be > painful. Only if you h= ave really good reasons, like the need to > quickly age out all data, you s= hould do things like this. And you > should use partitioned views in this c= ase. Well, I know it's wrong, but, as I said, stuck with it. This is for EI= S and is a ludicrous way to set things up, but I don't have authority to ch= ange it. Up to now I haven't even got the server (2005, Standard) brought u= p to the latest service pack - but we had a particularly bad Friday, so, if= I get the chance, what version should I ask for? > My strong preference is= to keep all database code under version > control. The [way] we organise t= ables, is that the table > definition itself is in one file [with its] PK c= onstraints > and CHECK constraints. However, foreign keys are in a > separa= te file. The indexes are also in a separate file. > > I don't like putting = index definitions in stored procedures > like you are planning, because all= the T-SQL trees will hide > the index forest for you. What I've got now is= worse, though; any program that runs slow is liable to have been changed t= o create table indexes on the spot. Having /one/ procedure that creates all= table indexes - being called separately for each table - will keep things = more coherent, and maybe even create a presumption that a table will /have/= indexes, which doesn't cross some of our developers' minds when they (!) t= ake it into their head to create one. On the other hand, my boss likes unde= r pressure to use the tuning tool on a query and then create all of the ind= exes that it proposes, at once, which I /think/ is wrong? Apparently the be= st rule is not to be us. By the way, are there any tips for Statistics? I h= ope I've got this straight: they reflect the distribution of data values in= the table, but are not necessarily current. I suspect that some or all of = the statistics that spontaneously appear on tables ought to be considered f= or creating an index instead (containing statistics?), although I also supp= ose that then it would happen automatically. And we have found that some ug= ly queries perform far better after executing "UPDATE STATISTICS ... WITH F= ULLSCAN" on every table in one or more databases, but, since /my/ duties te= nd to involve table that get emptied or dropped and recreated every night, = for the EIS, we'd need to do that ideally just after loading all the data i= nto a table and - maybe? - before creating indexes on it. Would it improve = the indexes too, or just create a useless additional workload? I assume tha= t "FULLSCAN" makes statistics better. And larger. I'm considering creating = a design, similar to one that I've got that just hits all tables, that coul= d allow tables to be pre-chosen to get this done every night or every week = or once or never, and do it either by calling it on one table during murky = overnight processing, or on any nominated tables that it hasn't been done f= or by the end - as logged in another table - and promote tables to having i= t done earlier if the overnight processing itself seems likely to benefit f= rom that. In discussions with MS tuning specialists who were hired to work on a MS pr= oduct one of my customers has installed the analyst confirmed that the inde= x tuning wizard over recommends the addition of indexes. An recently in di= scussion with another outside product expert he stated the feature has a se= rious bug involving generating indexes that include every column in the tab= le, which I have seen on 2008R2 RTM. I have not checked patched releases y= et. It will actually recommend several such indexes for the same table. T= his will pretty much kill insert performance and consumes a lot of space. I have always taken the tuning wizard's recommendations with a grain of sal= t. HTH -- Mark D Powell --