From: Mark.Powell2@hp.com   
      
   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.carnegie@gmail.com) writes: > > I have data to work on spread through   
   many tables 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 start there, as home-built   
   partitioning tends to be >    
   painful. Only if you have really good reasons, like the need to > quickly age   
   out all data, you should do things like this. And you > should use partitioned   
   views in this case. Well, I know it's wrong, but, as I said, stuck with it.   
   This is for EIS and    
   is a ludicrous way to set things up, but I don't have authority to change it.   
   Up to now I haven't even got the server (2005, Standard) brought up 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 tables, is that the table >   
   definition itself is in one file [with its] PK constraints > and CHECK   
   constraints. However, foreign keys are    
   in a > separate 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 to 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 (!) take it into their head to create one. On the   
   other hand, my boss likes under pressure to use the tuning tool on a query and   
   then create all of    
   the indexes that it proposes, at once, which I /think/ is wrong? Apparently   
   the best rule is not to be us. By the way, are there any tips for Statistics?   
   I hope 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 for creating an index   
   instead (containing statistics?), although I also suppose that then it would   
   happen automatically. And    
   we have found that some ugly queries perform far better after executing   
   "UPDATE STATISTICS ... WITH FULLSCAN" on every table in one or more databases,   
   but, since /my/ duties tend 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 into a   
   table and - maybe? - before creating indexes on it. Would it improve the   
   indexes too, or just create a useless additional workload? I assume that   
   "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 could 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 for by the end - as logged in another table - and promote   
   tables to having it done earlier if the overnight processing itself seems   
   likely to benefit from that.   
      
   In discussions with MS tuning specialists who were hired to work on a MS   
   product one of my customers has installed the analyst confirmed that the index   
   tuning wizard over recommends the addition of indexes.  An recently in   
   discussion with another outside    
   product expert he stated the feature has a serious bug involving generating   
   indexes that include every column in the table, which I have seen on 2008R2   
   RTM.  I have not checked patched releases yet.  It will actually recommend   
   several such indexes for    
   the same table.  This 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 salt.   
      
   HTH -- Mark D Powell --   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)