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

Re: Elementary management of many indexes, best practice?

Path	csiph.com!usenet.pasdenom.info!aioe.org!eternal-september.org!feeder.eternal-september.org!mx05.eternal-september.org!.POSTED!not-for-mail
From	Erland Sommarskog <esquel@sommarskog.se>
Newsgroups	comp.databases.ms-sqlserver
Subject	Re: Elementary management of many indexes, best practice?
Date	Sat, 16 Mar 2013 23:08:33 +0100
Organization	Erland Sommarskog
Lines	44
Message-ID	<XnsA185EB6AF3F7FYazorman@127.0.0.1> (permalink)
References	<0e799bb4-d98b-422d-8d01-c2f0a0ac5810@googlegroups.com> <XnsA181E6006E85FYazorman@127.0.0.1> <fdcfe50c-6c97-4836-b7c3-bf00c09767a8@googlegroups.com> <XnsA1857FA5E27D7Yazorman@127.0.0.1> <12f0a067-0608-44d9-bfd7-5b0ea592f5ae@googlegroups.com>
Mime-Version	1.0
Content-Type	text/plain; charset=windows-1252
Content-Transfer-Encoding	8bit
Injection-Info	mx05.eternal-september.org; posting-host="78e2ae7e6764121e217a035f19bdb315"; logging-data="3193"; mail-complaints-to="abuse@eternal-september.org"; posting-account="U2FsdGVkX18yj+SsRtAZejYdACg95hLj"
User-Agent	Xnews/2006.08.24 Mime-proxy/2.1.c.0 (Win32)
Cancel-Lock	sha1:226j6S5nSoCq9a11GvNu/UsYBTU=
Xref	csiph.com comp.databases.ms-sqlserver:1416

Show key headers only | View raw

 (rja.carnegie@gmail.com) writes:
> 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.

There are certainly a couple of CU after SP4. Exactly how many I don't
know, nor whether how critical they are.
 
> 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.

Looks like this query needs tuning.
 
> 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.

Not really. You a trace during the day, and then you feed that to DTA.
Since this is reports for bosses, maybe you should run it for a week
or so, as there might be a daily pattern which is strong enough.
 
> 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?

Exactly! You feed it a workload, and if you lie and say that this 
single query is your workload, it will comply.




-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

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