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

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

Re: Elementary management of many indexes, best practice?

Show all headers | View raw

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.

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

Thread

Elementary management of many indexes, best practice? rja.carnegie@gmail.com - 2013-03-12 07:48 -0700
  Re: Elementary management of many indexes, best practice? Erland Sommarskog <esquel@sommarskog.se> - 2013-03-12 22:36 +0100
    Re: Elementary management of many indexes, best practice? rja.carnegie@gmail.com - 2013-03-15 19:23 -0700
      Re: Elementary management of many indexes, best practice? Erland Sommarskog <esquel@sommarskog.se> - 2013-03-16 12:32 +0100
        Re: Elementary management of many indexes, best practice? rja.carnegie@gmail.com - 2013-03-16 14:12 -0700
          Re: Elementary management of many indexes, best practice? Erland Sommarskog <esquel@sommarskog.se> - 2013-03-16 23:08 +0100
      Re: Elementary management of many indexes, best practice? Mark D Powell <Mark.Powell2@hp.com> - 2013-03-27 11:20 -0700

csiph-web