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 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: References: <0e799bb4-d98b-422d-8d01-c2f0a0ac5810@googlegroups.com> <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 (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