SQL Server Performance Forum – Threads Archive

Automaic query optimizing by analysing the indexes

Hi, It is my understanding that SQL server does automatic query oprimizing periodially by analysing the usage of indexes. my questing is if i have run a script altering the current table indexes after this Scheduled job which is automated by the SQL server itself will it overwite the idexes I have created?? The reason for me to run a seperate script is that I have notice few indexes being dupilcated as well as introducing few new indexes including clustered intexes. Could anyone please tell me what this scheduled job actually does?? Thanks!

---

If you run the index scripts, the stats will be updated automatically. If you have ‘Auto update stats’ turned on, for any further inserts/updates/deletes, the stats are automatically updated too. ***********************
Dinakar Nethi
SQL Server MVP
***********************

---

<blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by umimetha</i><br /><br />Hi,<br /><br />The reason for me to run a seperate script is that I have notice few indexes being dupilcated as well as introducing few new indexes including clustered intexes.<br /><br />Thanks!<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br />Strange! I think you are confusing with index and statitic. The scheduled maintenance job should not override the indices you created explicitly in a table. You can check this yourself by going to the individual table and finding the index you created. [<img src=’/community/emoticons/emotion-1.gif’ alt=’‘ />]<br /><br /><pre id="code"><font face="courier" size="2" id="code"><br />Thanks,<br /><br />Name<br />——— <br />Dilli Grg <br /><br />(1 row(s) affected)<br /></font id="code"></pre id="code">

---

May be umimetha refferring defragementing the tables using ALTER INDEX command which is new sql 2005… Check the following article…
Statistics Used by the Query Optimizer in Microsoft SQL Server 2005
http://www.microsoft.com/technet/prodtechnol/sql/2005/qrystats.mspx
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.

---

In terms of index optimisation, SQL Server 2005 DOES do some kind of automatic index optimisation.<br /><br />My current understanding of this is that:-<br /><br />1. Query Optimizer builds execution plan<br />2. As plan is being built, optimizer is capable of figuring out which indexes would help this query.<br />3. This was always the case, but now in SQL Server 2005 this information is recorded and made accessible through a DMV.<br /><br />It doesn’t help with ALL missing indexes – Kalen Delaney wrote an article on this in SQLMag – it will only help find missing indexes if:<br /><br />1. The query has a WHERE clause<br />2. The query does not have a trivial plan – i.e. not a select from tableA where colB = X (trivial = only one way to optimise this query)<br /><br />Anyway, I’ve been messing about with the dynamic management view and I thought I’d share this nice bit of code – it re-uses some stuff from MSDN to get the basic missing index info out and then does some other stuff to construct the SQL code required to create the missing indexes.<br /><br />I’d advise AGAINST creating ALL indexes listed by this. If you’re going to use this, you should examine the user_seeks, avg_total_user_cost and avg_user_impact columns – don’t just create all of the indexes as it will massively slow down your inserts/updates.<br /><br />select <br />’create index ixAuto_’ + left(convert(char(36), newid()), <img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> + ‘ on ‘ <br />+ object_name(d.object_id) + ‘ (‘ + isnull(d.equality_columns, ”) + isnull( case when d.equality_columns is null then ” else ‘, ‘ end + inequality_columns, ”)<br />+ ‘) ‘ + isnull(‘include (‘ + d.included_columns + ‘)’, ”) SQLToCreateIndexes<br />, *<br />from <br />sys.dm_db_missing_index_details d<br />inner join sys.dm_db_missing_index_groups g<br />on d.index_handle = g.index_handle<br />inner join sys.dm_db_missing_index_group_stats s<br />on g.index_group_handle = s.group_handle<br />–where avg_total_user_cost &gt; 0.1<br />order by user_seeks desc<br /><br /><br />Add flight search to your own site for free<br />www.skyscanner.net

---

I don’t understand how come a scheduled job to optimize will overwrite or create duplicate indexes? Cna you explain a bit more on this part. http://blogs.msdn.com/queryoptteam/archive/2006/06/01/613516.aspx fyi on automatic optimization.

Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

---

]]>