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

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

Re: Elementary management of many indexes, best practice?

Show all headers | View raw

On Saturday, 16 March 2013 11:32:53 UTC, Erland Sommarskog  wrote:
> (rja.carnegie@gmail.com) writes:
> > 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, 
> 
> What is EIS?

I meant "Enterprise Information System", which I suppose is an
out-of-date term: reports for bosses.  /Interactive/ reports.

> > 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?
> 
> Service Pack 4 is the last service pack for SQL 2005.

OK... so there aren't any hotfixes or cumulative updates that 
you'd install as standard?  I know we're not /supposed/ to do
that.

<http://www.sqldbaguru.com/cu-builds> is a catalogue of these,
and <http://support.microsoft.com/kb/2494120> is particularly
interesting because it fixes a security hole - which I think
I remember, from looking at documentation previously, is only in 
the server admin or client tools, not in the server itself.
However, it bumps you from Service Pack 4 (9.00.5000) 
to 9.00.5057.

It was six months ago, when I was told that installing the 
service pack wasn't going to be scheduled.

A problem that we have currently, that may be associated with a 
particular query, seems to lead to tempdb suddenly inflating to
over 250 gigabytes - the size of the separate partition that 
someone with unusual forethought placed it on.  We think this
is a completely unreasonable effect.

> > 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?
> 
> The purpose of the Database Tuning Advisor is that you collect a 
> workload for a longer period, say a day, and from this add or drop
> indexes. If you feed it a single query it will tune that workload, 
> and be oblvious to other effects it may have.

The trouble that I see with that is that our EIS team only has 
one SQL server - no, don't leave...  The tuning process is liable to 
block normal use of the server (uh, right?), particularly if you're
tuning for a query where performance stinks.

My impression has been that tuning recommendations should be treated
as alternatives to choose from, rather than a prescribed remedy;
I seem to be told to create several indexes and statistics that 
are quite similar.  But perhaps that's a result of asking the tool
about just one query - it calculates anything that will help that?

Thank you for the advice on statistics.  If the white paper descibes
them adequately then I suspect it will be difficult to read, but I
should apply time to it.

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