AXForum  
Вернуться   AXForum > Microsoft Dynamics CRM > Dynamics CRM: Blogs
All
Забыли пароль?
Зарегистрироваться Правила Справка Пользователи Сообщения за день Поиск Все разделы прочитаны

 
 
Опции темы Поиск в этой теме Опции просмотра
Старый 26.02.2008, 12:29   #1  
Blog bot is offline
Blog bot
Участник
 
25,459 / 846 (79) +++++++
Регистрация: 28.10.2006
This is a follow up from an earlier blog "Finding Index usage". In that blog, I described a very simple way to list how indexes are being used. In this blog, the query is much extended so that it now shows your Navision keys, listed by either number of updates, or by their cost divided by their usage, and it shows when an index was last used for reading. The idea is to show a list of indexes that are being maintained, but never or rarely being used.The query uses SQL Server Dynamic Managament Views (DMW), which means it will only work for for SQL Server 2005 and later.Feel free to add comments to this blog about how useful (or not) this query is. And about any problems you may find, and suggestions to improve it. All comments will be welcome! To use it, copy the query below into SQL Server Management Studio. Remember to set the database to your Microsoft Dynamics NAV database (not Master or any other database). Then run it. Depending on the size of your database, it may take a few minutes to run it. First time you run it, I would recommend that you do it when the SQL Server is not otherwise busy, until you konw how long it takes: <span style="font-size:10pt;line-height:100%"><span style="color:#008000">-- use NavisionDB</span></span><span style="font-size:10pt;line-height:100%"><span style="color:#0000ff">IF</span></span><span style="font-size:10pt;line-height:100%"> </span><span style="font-size:10pt;line-height:100%"><span style="color:#ff00ff">OBJECT_ID</span></span><span style="font-size:10pt;line-height:100%"> </span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">(</span></span><span style="font-size:10pt;line-height:100%"><span style="color:#ff0000">'z_IUQ_Temp_Index_Keys'</span></span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">,</span></span><span style="font-size:10pt;line-height:100%"> </span><span style="font-size:10pt;line-height:100%"><span style="color:#ff0000">'U'</span></span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">)</span></span><span style="font-size:10pt;line-height:100%"> </span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">IS</span></span><span style="font-size:10pt;line-height:100%"> </span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">NOT</span></span><span style="font-size:10pt;line-height:100%"> </span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">NULL</span></span><span style="font-size:10pt;line-height:100%"></span><span style="font-size:10pt;line-height:100%"><span style="color:#0000ff">DROP</span></span><span style="font-size:10pt;line-height:100%"> </span><span style="font-size:10pt;line-height:100%"><span style="color:#0000ff">TABLE</span></span><span style="font-size:10pt;line-height:100%"> z_IUQ_Temp_Index_Keys</span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">;</span></span><span style="font-size:10pt;line-height:100%"><span style="color:#0000ff">IF</span></span><span style="font-size:10pt;line-height:100%"> </span><span style="font-size:10pt;line-height:100%"><span style="color:#ff00ff">OBJECT_ID</span></span><span style="font-size:10pt;line-height:100%"> </span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">(</span></span><span style="font-size:10pt;line-height:100%"><span style="color:#ff0000">'zIUQ_Temp_Index_Usage'</span></span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">,</span></span><span style="font-size:10pt;line-height:100%"> </span><span style="font-size:10pt;line-height:100%"><span style="color:#ff0000">'U'</span></span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">)</span></span><span style="font-size:10pt;line-height:100%"> </span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">IS</span></span><span style="font-size:10pt;line-height:100%"> </span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">NOT</span></span><span style="font-size:10pt;line-height:100%"> </span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">NULL</span></span><span style="font-size:10pt;line-height:100%"></span><span style="font-size:10pt;line-height:100%"><span style="color:#0000ff">DROP</span></span><span style="font-size:10pt;line-height:100%"> </span><span style="font-size:10pt;line-height:100%"><span style="color:#0000ff">TABLE</span></span><span style="font-size:10pt;line-height:100%"> zIUQ_Temp_Index_Usage</span><span style="font-size:10pt;line-height:100%"><span style="color:#008000">-- Generate list of indexes with key list</span></span><span style="font-size:10pt;line-height:100%"><span style="color:#0000ff">create</span></span><span style="font-size:10pt;line-height:100%"> </span><span style="font-size:10pt;line-height:100%"><span style="color:#0000ff">table</span></span><span style="font-size:10pt;line-height:100%"> z_IUQ_Temp_Index_Keys</span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">(</span></span><span style="font-size:10pt;line-height:100%">[F_Obj_ID] [int] </span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">NOT</span></span><span style="font-size:10pt;line-height:100%"> </span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">NULL,</span></span><span style="font-size:10pt;line-height:100%">[F_Obj_Name] [nvarchar] </span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">(</span></span><span style="font-size:10pt;line-height:100%">128</span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">)</span></span><span style="font-size:10pt;line-height:100%"> </span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">NULL,</span></span><span style="font-size:10pt;line-height:100%">[F_Ind_ID] [int] </span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">NOT</span></span><span style="font-size:10pt;line-height:100%"> </span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">NULL,</span></span><span style="font-size:10pt;line-height:100%">[Index_Column_ID] [int] </span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">NOT</span></span><span style="font-size:10pt;line-height:100%"> </span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">NULL,</span></span><span style="font-size:10pt;line-height:100%">[Index_Key] [nvarchar] </span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">(</span></span><span style="font-size:10pt;line-height:100%">128</span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">)</span></span><span style="font-size:10pt;line-height:100%"> </span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">NULL,</span></span><span style="font-size:10pt;line-height:100%">[Index_Key_List] [nvarchar] </span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">(</span></span><span style="font-size:10pt;line-height:100%"><span style="color:#ff00ff">MAX</span></span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">)</span></span><span style="font-size:10pt;line-height:100%"> </span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">NULL,</span></span><span style="font-size:10pt;line-height:100%"></span><span style="font-size:10pt;line-height:100%"><span style="color:#0000ff">CONSTRAINT</span></span><span style="font-size:10pt;line-height:100%"> [z_IUQ_TempPK] </span><span style="font-size:10pt;line-height:100%"><span style="color:#0000ff">PRIMARY</span></span><span style="font-size:10pt;line-height:100%"> </span><span style="font-size:10pt;line-height:100%"><span style="color:#0000ff">KEY</span></span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">(</span></span><span style="font-size:10pt;line-height:100%">[F_Obj_ID]</span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">,</span></span><span style="font-size:10pt;line-height:100%">[F_Ind_ID]</span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">,</span></span><span style="font-size:10pt;line-height:100%">[Index_Column_ID] </span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">))</span></span><span style="font-size:10pt;line-height:100%"><span style="color:#0000ff">Insert</span></span><span style="font-size:10pt;line-height:100%"> </span><span style="font-size:10pt;line-height:100%"><span style="color:#0000ff">into</span></span><span style="font-size:10pt;line-height:100%"> z_IUQ_Temp_Index_Keys</span><span style="font-size:10pt;line-height:100%"><span style="color:#0000ff">select</span></span><span style="font-size:10pt;line-height:100%"></span><span style="font-size:10pt;line-height:100%"><span style="color:#ff00ff">object_id</span></span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">,</span></span><span style="font-size:10pt;line-height:100%"></span><span style="font-size:10pt;line-height:100%"><span style="color:#ff00ff">object_name</span></span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">(</span></span><span style="font-size:10pt;line-height:100%"><span style="color:#ff00ff">object_id</span></span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">),</span></span><span style="font-size:10pt;line-height:100%">index_id</span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">,</span></span><span style="font-size:10pt;line-height:100%">Index_Column_ID</span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">,</span></span><span style="font-size:10pt;line-height:100%"></span><span style="font-size:10pt;line-height:100%"><span style="color:#ff00ff">index_col</span></span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">(</span></span><span style="font-size:10pt;line-height:100%"><span style="color:#ff00ff">object_name</span></span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">(</span></span><span style="font-size:10pt;line-height:100%"><span style="color:#ff00ff">object_id</span></span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">),</span></span><span style="font-size:10pt;line-height:100%">index_id</span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">,</span></span><span style="font-size:10pt;line-height:100%">Index_Column_ID</span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">),</span></span><span style="font-size:10pt;line-height:100%"></span><span style="font-size:10pt;line-height:100%"><span style="color:#ff0000">''</span></span><span style="font-size:10pt;line-height:100%"><span style="color:#0000ff">from</span></span><span style="font-size:10pt;line-height:100%"> </span><span style="font-size:10pt;line-height:100%"><span style="color:#008000">sys.index_columns</span></span><span style="font-size:10pt;line-height:100%"><span style="color:#008000">go-- populate key string</span></span><span style="font-size:10pt;line-height:100%"><span style="color:#0000ff">declare</span></span><span style="font-size:10pt;line-height:100%"> IndexCursor </span><span style="font-size:10pt;line-height:100%"><span style="color:#0000ff">cursor</span></span><span style="font-size:10pt;line-height:100%"> </span><span style="font-size:10pt;line-height:100%"><span style="color:#0000ff">FOR</span></span><span style="font-size:10pt;line-height:100%"></span><span style="font-size:10pt;line-height:100%"><span style="color:#0000ff">select</span></span><span style="font-size:10pt;line-height:100%"> F_Obj_ID</span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">,</span></span><span style="font-size:10pt;line-height:100%"> F_Ind_ID </span><span style="font-size:10pt;line-height:100%"><span style="color:#0000ff">from</span></span><span style="font-size:10pt;line-height:100%"> z_IUQ_Temp_Index_Keys</span><span style="font-size:10pt;line-height:100%"><span style="color:#0000ff">FOR</span></span><span style="font-size:10pt;line-height:100%"> </span><span style="font-size:10pt;line-height:100%"><span style="color:#0000ff">UPDATE</span></span><span style="font-size:10pt;line-height:100%"> </span><span style="font-size:10pt;line-height:100%"><span style="color:#0000ff">of</span></span><span style="font-size:10pt;line-height:100%"> Index_Key_List</span><span style="font-size:10pt;line-height:100%"><span style="color:#0000ff">declare</span></span><span style="font-size:10pt;line-height:100%"> @ObjID </span><span style="font-size:10pt;line-height:100%"><span style="color:#0000ff">intdeclare</span></span><span style="font-size:10pt;line-height:100%"> @IndID </span><span style="font-size:10pt;line-height:100%"><span style="color:#0000ff">intDECLARE</span></span><span style="font-size:10pt;line-height:100%"> @KeyString </span><span style="font-size:10pt;line-height:100%"><span style="color:#0000ff">VARCHAR</span></span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">(</span></span><span style="font-size:10pt;line-height:100%"><span style="color:#ff00ff">MAX</span></span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">)</span></span><span style="font-size:10pt;line-height:100%"><span style="color:#0000ff">set</span></span><span style="font-size:10pt;line-height:100%"> @KeyString </span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">=</span></span><span style="font-size:10pt;line-height:100%"> </span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">NULL</span></span><span style="font-size:10pt;line-height:100%"><span style="color:#0000ff">open</span></span><span style="font-size:10pt;line-height:100%"> IndexCursor</span><span style="font-size:10pt;line-height:100%"><span style="color:#0000ff">set</span></span><span style="font-size:10pt;line-height:100%"> </span><span style="font-size:10pt;line-height:100%"><span style="color:#0000ff">nocount</span></span><span style="font-size:10pt;line-height:100%"> </span><span style="font-size:10pt;line-height:100%"><span style="color:#0000ff">onfetch</span></span><span style="font-size:10pt;line-height:100%"> </span><span style="font-size:10pt;line-height:100%"><span style="color:#0000ff">next</span></span><span style="font-size:10pt;line-height:100%"> </span><span style="font-size:10pt;line-height:100%"><span style="color:#0000ff">from</span></span><span style="font-size:10pt;line-height:100%"> IndexCursor </span><span style="font-size:10pt;line-height:100%"><span style="color:#0000ff">into</span></span><span style="font-size:10pt;line-height:100%"> @ObjID</span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">,</span></span><span style="font-size:10pt;line-height:100%"> @IndID</span><span style="font-size:10pt;line-height:100%"><span style="color:#0000ff">while</span></span><span style="font-size:10pt;line-height:100%"> </span><span style="font-size:10pt;line-height:100%"><span style="color:#ff00ff">@@fetch_status</span></span><span style="font-size:10pt;line-height:100%"> </span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">=</span></span><span style="font-size:10pt;line-height:100%"> 0 </span><span style="font-size:10pt;line-height:100%"><span style="color:#0000ff">begin</span></span><span style="font-size:10pt;line-height:100%"></span><span style="font-size:10pt;line-height:100%"><span style="color:#0000ff">SET</span></span><span style="font-size:10pt;line-height:100%"> @KeyString </span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">=</span></span><span style="font-size:10pt;line-height:100%"> </span><span style="font-size:10pt;line-height:100%"><span style="color:#ff0000">''</span></span><span style="font-size:10pt;line-height:100%"></span><span style="font-size:10pt;line-height:100%"><span style="color:#0000ff">SELECT</span></span><span style="font-size:10pt;line-height:100%"> @KeyString </span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">=</span></span><span style="font-size:10pt;line-height:100%"> </span><span style="font-size:10pt;line-height:100%"><span style="color:#ff00ff">COALESCE</span></span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">(</span></span><span style="font-size:10pt;line-height:100%">@KeyString</span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">,</span></span><span style="font-size:10pt;line-height:100%"> </span><span style="font-size:10pt;line-height:100%"><span style="color:#ff0000">''</span></span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">)</span></span><span style="font-size:10pt;line-height:100%"> </span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">+</span></span><span style="font-size:10pt;line-height:100%"> Index_Key </span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">+</span></span><span style="font-size:10pt;line-height:100%"> </span><span style="font-size:10pt;line-height:100%"><span style="color:#ff0000">', '</span></span><span style="font-size:10pt;line-height:100%"></span><span style="font-size:10pt;line-height:100%"><span style="color:#0000ff">FROM</span></span><span style="font-size:10pt;line-height:100%"> z_IUQ_Temp_Index_Keys</span><span style="font-size:10pt;line-height:100%"><span style="color:#0000ff">where</span></span><span style="font-size:10pt;line-height:100%"> F_Obj_ID </span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">=</span></span><span style="font-size:10pt;line-height:100%"> @ObjID </span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">and</span></span><span style="font-size:10pt;line-height:100%"> F_Ind_ID </span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">=</span></span><span style="font-size:10pt;line-height:100%"> @IndID</span><span style="font-size:10pt;line-height:100%"><span style="color:#0000ff">ORDER</span></span><span style="font-size:10pt;line-height:100%"> </span><span style="font-size:10pt;line-height:100%"><span style="color:#0000ff">BY</span></span><span style="font-size:10pt;line-height:100%"> F_Ind_ID</span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">,</span></span><span style="font-size:10pt;line-height:100%"> Index_Column_ID</span><span style="font-size:10pt;line-height:100%"><span style="color:#0000ff">SET</span></span><span style="font-size:10pt;line-height:100%"> @KeyString </span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">=</span></span><span style="font-size:10pt;line-height:100%"> </span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">LEFT(</span></span><span style="font-size:10pt;line-height:100%">@KeyString</span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">,</span></span><span style="font-size:10pt;line-height:100%"><span style="color:#ff00ff">LEN</span></span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">(</span></span><span style="font-size:10pt;line-height:100%">@KeyString</span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">)</span></span><span style="font-size:10pt;line-height:100%"> </span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">-</span></span><span style="font-size:10pt;line-height:100%"> 2</span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">)</span></span><span style="font-size:10pt;line-height:100%"></span><span style="font-size:10pt;line-height:100%"><span style="color:#0000ff">update</span></span><span style="font-size:10pt;line-height:100%"> z_IUQ_Temp_Index_Keys</span><span style="font-size:10pt;line-height:100%"><span style="color:#0000ff">set</span></span><span style="font-size:10pt;line-height:100%"> Index_Key_List </span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">=</span></span><span style="font-size:10pt;line-height:100%"> @KeyString</span><span style="font-size:10pt;line-height:100%"><span style="color:#0000ff">where</span></span><span style="font-size:10pt;line-height:100%"> </span><span style="font-size:10pt;line-height:100%"><span style="color:#0000ff">current</span></span><span style="font-size:10pt;line-height:100%"> </span><span style="font-size:10pt;line-height:100%"><span style="color:#0000ff">of</span></span><span style="font-size:10pt;line-height:100%"> IndexCursor</span><span style="font-size:10pt;line-height:100%"><span style="color:#0000ff">fetch</span></span><span style="font-size:10pt;line-height:100%"> </span><span style="font-size:10pt;line-height:100%"><span style="color:#0000ff">next</span></span><span style="font-size:10pt;line-height:100%"> </span><span style="font-size:10pt;line-height:100%"><span style="color:#0000ff">from</span></span><span style="font-size:10pt;line-height:100%"> IndexCursor </span><span style="font-size:10pt;line-height:100%"><span style="color:#0000ff">into</span></span><span style="font-size:10pt;line-height:100%"> @ObjID</span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">,</span></span><span style="font-size:10pt;line-height:100%"> @IndID</span><span style="font-size:10pt;line-height:100%"><span style="color:#0000ff">end</span></span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">;</span></span><span style="font-size:10pt;line-height:100%"><span style="color:#0000ff">close</span></span><span style="font-size:10pt;line-height:100%"> IndexCursor</span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">;</span></span><span style="font-size:10pt;line-height:100%"><span style="color:#0000ff">deallocate</span></span><span style="font-size:10pt;line-height:100%"> IndexCursor</span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">;</span></span><span style="font-size:10pt;line-height:100%"><span style="color:#008000">-- Generate list of Index usage</span></span><span style="font-size:10pt;line-height:100%"><span style="color:#0000ff">create</span></span><span style="font-size:10pt;line-height:100%"> </span><span style="font-size:10pt;line-height:100%"><span style="color:#0000ff">table</span></span><span style="font-size:10pt;line-height:100%"> zIUQ_Temp_Index_Usage</span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">(</span></span><span style="font-size:10pt;line-height:100%">[F_Table_Name] [nvarchar]</span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">(</span></span><span style="font-size:10pt;line-height:100%">128</span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">)</span></span><span style="font-size:10pt;line-height:100%"> </span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">NOT</span></span><span style="font-size:10pt;line-height:100%"> </span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">NULL,</span></span><span style="font-size:10pt;line-height:100%">[F_Ind_ID] [int] </span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">NOT</span></span><span style="font-size:10pt;line-height:100%"> </span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">NULL,</span></span><span style="font-size:10pt;line-height:100%">[F_Index_Name] [nvarchar]</span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">(</span></span><span style="font-size:10pt;line-height:100%">128</span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">)</span></span><span style="font-size:10pt;line-height:100%"> </span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">NULL,</span></span><span style="font-size:10pt;line-height:100%">[No_Of_Updates] [int] </span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">NULL,</span></span><span style="font-size:10pt;line-height:100%">[User_Reads] [int] </span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">NULL,</span></span><span style="font-size:10pt;line-height:100%">[Last_Used_For_Reads] [datetime] </span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">NULL,</span></span><span style="font-size:10pt;line-height:100%">[Index_Type] [nvarchar]</span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">(</span></span><span style="font-size:10pt;line-height:100%">56</span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">)</span></span><span style="font-size:10pt;line-height:100%"> </span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">NOT</span></span><span style="font-size:10pt;line-height:100%"> </span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">NULL,</span></span><span style="font-size:10pt;line-height:100%">[last_user_seek] [datetime] </span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">NULL,</span></span><span style="font-size:10pt;line-height:100%">[last_user_scan] [datetime] </span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">NULL,</span></span><span style="font-size:10pt;line-height:100%">[last_user_lookup] [datetime] </span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">NULL,</span></span><span style="font-size:10pt;line-height:100%"> [Index_Keys] [nvarchar] </span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">(</span></span><span style="font-size:10pt;line-height:100%">255</span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">)</span></span><span style="font-size:10pt;line-height:100%"> </span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">NULL)</span></span><span style="font-size:10pt;line-height:100%"><span style="color:#0000ff">insert</span></span><span style="font-size:10pt;line-height:100%"> </span><span style="font-size:10pt;line-height:100%"><span style="color:#0000ff">into</span></span><span style="font-size:10pt;line-height:100%"> zIUQ_Temp_Index_Usage</span><span style="font-size:10pt;line-height:100%"><span style="color:#0000ff">select</span></span><span style="font-size:10pt;line-height:100%"> </span><span style="font-size:10pt;line-height:100%"><span style="color:#ff00ff">object_name</span></span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">(</span></span><span style="font-size:10pt;line-height:100%">US</span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">.</span></span><span style="font-size:10pt;line-height:100%"><span style="color:#ff00ff">object_id</span></span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">)</span></span><span style="font-size:10pt;line-height:100%"> Table_Name</span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">,</span></span><span style="font-size:10pt;line-height:100%">US</span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">.</span></span><span style="font-size:10pt;line-height:100%">index_id Index_ID</span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">,</span></span><span style="font-size:10pt;line-height:100%">SI</span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">.</span></span><span style="font-size:10pt;line-height:100%">name Index_Name</span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">,</span></span><span style="font-size:10pt;line-height:100%">US</span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">.</span></span><span style="font-size:10pt;line-height:100%">user_updates No_Of_Updates</span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">,</span></span><span style="font-size:10pt;line-height:100%">US</span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">.</span></span><span style="font-size:10pt;line-height:100%">user_seeks </span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">+</span></span><span style="font-size:10pt;line-height:100%"> US</span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">.</span></span><span style="font-size:10pt;line-height:100%">user_scans </span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">+</span></span><span style="font-size:10pt;line-height:100%"> US</span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">.</span></span><span style="font-size:10pt;line-height:100%">user_lookups User_Reads</span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">,</span></span><span style="font-size:10pt;line-height:100%"><span style="color:#0000ff">case</span></span><span style="font-size:10pt;line-height:100%"></span><span style="font-size:10pt;line-height:100%"><span style="color:#0000ff">when</span></span><span style="font-size:10pt;line-height:100%"> </span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">(</span></span><span style="font-size:10pt;line-height:100%"><span style="color:#ff00ff">ISNULL</span></span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">(</span></span><span style="font-size:10pt;line-height:100%">US</span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">.</span></span><span style="font-size:10pt;line-height:100%">last_user_seek</span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">,</span></span><span style="font-size:10pt;line-height:100%"><span style="color:#ff0000">'00:00:00.000'</span></span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">)</span></span><span style="font-size:10pt;line-height:100%"> </span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">>=</span></span><span style="font-size:10pt;line-height:100%"> </span><span style="font-size:10pt;line-height:100%"><span style="color:#ff00ff">ISNULL</span></span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">(</span></span><span style="font-size:10pt;line-height:100%">US</span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">.</span></span><span style="font-size:10pt;line-height:100%">last_user_scan</span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">,</span></span><span style="font-size:10pt;line-height:100%"><span style="color:#ff0000">'00:00:00.000'</span></span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">))</span></span><span style="font-size:10pt;line-height:100%"> </span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">and</span></span><span style="font-size:10pt;line-height:100%"> </span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">(</span></span><span style="font-size:10pt;line-height:100%"><span style="color:#ff00ff">ISNULL</span></span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">(</span></span><span style="font-size:10pt;line-height:100%">US</span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">.</span></span><span style="font-size:10pt;line-height:100%">last_user_seek</span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">,</span></span><span style="font-size:10pt;line-height:100%"><span style="color:#ff0000">'00:00:00.000'</span></span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">)</span></span><span style="font-size:10pt;line-height:100%"> </span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">>=</span></span><span style="font-size:10pt;line-height:100%"> </span><span style="font-size:10pt;line-height:100%"><span style="color:#ff00ff">ISNULL</span></span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">(</span></span><span style="font-size:10pt;line-height:100%">US</span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">.</span></span><span style="font-size:10pt;line-height:100%">last_user_lookup</span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">,</span></span><span style="font-size:10pt;line-height:100%"><span style="color:#ff0000">'00:00:00.000'</span></span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">))</span></span><span style="font-size:10pt;line-height:100%"> </span><span style="font-size:10pt;line-height:100%"><span style="color:#0000ff">then</span></span><span style="font-size:10pt;line-height:100%"> US</span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">.</span></span><span style="font-size:10pt;line-height:100%">last_user_seek</span><span style="font-size:10pt;line-height:100%"><span style="color:#0000ff">when</span></span><span style="font-size:10pt;line-height:100%"> </span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">(</span></span><span style="font-size:10pt;line-height:100%"><span style="color:#ff00ff">ISNULL</span></span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">(</span></span><span style="font-size:10pt;line-height:100%">US</span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">.</span></span><span style="font-size:10pt;line-height:100%">last_user_scan</span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">,</span></span><span style="font-size:10pt;line-height:100%"><span style="color:#ff0000">'00:00:00.000'</span></span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">)</span></span><span style="font-size:10pt;line-height:100%"> </span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">>=</span></span><span style="font-size:10pt;line-height:100%"> </span><span style="font-size:10pt;line-height:100%"><span style="color:#ff00ff">ISNULL</span></span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">(</span></span><span style="font-size:10pt;line-height:100%">US</span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">.</span></span><span style="font-size:10pt;line-height:100%">last_user_seek</span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">,</span></span><span style="font-size:10pt;line-height:100%"><span style="color:#ff0000">'00:00:00.000'</span></span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">))</span></span><span style="font-size:10pt;line-height:100%"> </span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">and</span></span><span style="font-size:10pt;line-height:100%"> </span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">(</span></span><span style="font-size:10pt;line-height:100%"><span style="color:#ff00ff">ISNULL</span></span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">(</span></span><span style="font-size:10pt;line-height:100%">US</span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">.</span></span><span style="font-size:10pt;line-height:100%">last_user_scan</span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">,</span></span><span style="font-size:10pt;line-height:100%"><span style="color:#ff0000">'00:00:00.000'</span></span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">)</span></span><span style="font-size:10pt;line-height:100%"> </span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">>=</span></span><span style="font-size:10pt;line-height:100%"> </span><span style="font-size:10pt;line-height:100%"><span style="color:#ff00ff">ISNULL</span></span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">(</span></span><span style="font-size:10pt;line-height:100%">US</span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">.</span></span><span style="font-size:10pt;line-height:100%">last_user_lookup</span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">,</span></span><span style="font-size:10pt;line-height:100%"><span style="color:#ff0000">'00:00:00.000'</span></span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">))</span></span><span style="font-size:10pt;line-height:100%"> </span><span style="font-size:10pt;line-height:100%"><span style="color:#0000ff">then</span></span><span style="font-size:10pt;line-height:100%"> US</span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">.</span></span><span style="font-size:10pt;line-height:100%">last_user_scan </span><span style="font-size:10pt;line-height:100%"><span style="color:#0000ff">else</span></span><span style="font-size:10pt;line-height:100%"> US</span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">.</span></span><span style="font-size:10pt;line-height:100%">last_user_lookup</span><span style="font-size:10pt;line-height:100%"><span style="color:#0000ff">end</span></span><span style="font-size:10pt;line-height:100%"> </span><span style="font-size:10pt;line-height:100%"><span style="color:#0000ff">as</span></span><span style="font-size:10pt;line-height:100%"> Last_Used_For_Reads</span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">,</span></span><span style="font-size:10pt;line-height:100%">SI</span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">.</span></span><span style="font-size:10pt;line-height:100%">type_desc Index_Type</span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">,</span></span><span style="font-size:10pt;line-height:100%">US</span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">.</span></span><span style="font-size:10pt;line-height:100%">last_user_seek</span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">,</span></span><span style="font-size:10pt;line-height:100%">US</span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">.</span></span><span style="font-size:10pt;line-height:100%">last_user_scan</span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">,</span></span><span style="font-size:10pt;line-height:100%">US</span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">.</span></span><span style="font-size:10pt;line-height:100%">last_user_lookup</span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">,</span></span><span style="font-size:10pt;line-height:100%"><span style="color:#ff0000">''</span></span><span style="font-size:10pt;line-height:100%"><span style="color:#0000ff">from</span></span><span style="font-size:10pt;line-height:100%"> </span><span style="font-size:10pt;line-height:100%"><span style="color:#008000">sys.dm_db_index_usage_stats</span></span><span style="font-size:10pt;line-height:100%"> US</span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">,</span></span><span style="font-size:10pt;line-height:100%"> </span><span style="font-size:10pt;line-height:100%"><span style="color:#008000">sys.indexes</span></span><span style="font-size:10pt;line-height:100%"> SI </span><span style="font-size:10pt;line-height:100%"><span style="color:#0000ff">where</span></span><span style="font-size:10pt;line-height:100%"> SI</span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">.</span></span><span style="font-size:10pt;line-height:100%"><span style="color:#ff00ff">object_id</span></span><span style="font-size:10pt;line-height:100%"> </span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">=</span></span><span style="font-size:10pt;line-height:100%"> US</span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">.</span></span><span style="font-size:10pt;line-height:100%"><span style="color:#ff00ff">object_id</span></span><span style="font-size:10pt;line-height:100%"> </span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">and</span></span><span style="font-size:10pt;line-height:100%"> SI</span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">.</span></span><span style="font-size:10pt;line-height:100%">index_id </span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">=</span></span><span style="font-size:10pt;line-height:100%"> US</span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">.</span></span><span style="font-size:10pt;line-height:100%">index_id</span><span style="font-size:10pt;line-height:100%"><span style="color:#0000ff">order</span></span><span style="font-size:10pt;line-height:100%"> </span><span style="font-size:10pt;line-height:100%"><span style="color:#0000ff">by</span></span><span style="font-size:10pt;line-height:100%"> No_Of_Updates </span><span style="font-size:10pt;line-height:100%"><span style="color:#0000ff">descgo</span></span><span style="font-size:10pt;line-height:100%"><span style="color:#008000">-- Select and join the two tables.</span></span><span style="font-size:10pt;line-height:100%"><span style="color:#0000ff">select</span></span><span style="font-size:10pt;line-height:100%"> TIU</span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">.</span></span><span style="font-size:10pt;line-height:100%">F_Table_Name Table_Name</span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">,</span></span><span style="font-size:10pt;line-height:100%"><span style="color:#008000">--TIU.F_Ind_ID Index_ID,--TIU.F_Index_Name Index_Name,</span></span><span style="font-size:10pt;line-height:100%">TIK</span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">.</span></span><span style="font-size:10pt;line-height:100%">Index_Key_List</span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">,</span></span><span style="font-size:10pt;line-height:100%">TIU</span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">.</span></span><span style="font-size:10pt;line-height:100%">No_Of_Updates</span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">,</span></span><span style="font-size:10pt;line-height:100%">TIU</span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">.</span></span><span style="font-size:10pt;line-height:100%">User_Reads</span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">,</span></span><span style="font-size:10pt;line-height:100%"><span style="color:#0000ff">case</span></span><span style="font-size:10pt;line-height:100%"> </span><span style="font-size:10pt;line-height:100%"><span style="color:#0000ff">when</span></span><span style="font-size:10pt;line-height:100%"> TIU</span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">.</span></span><span style="font-size:10pt;line-height:100%">User_Reads </span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">=</span></span><span style="font-size:10pt;line-height:100%"> 0 </span><span style="font-size:10pt;line-height:100%"><span style="color:#0000ff">then</span></span><span style="font-size:10pt;line-height:100%"> TIU</span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">.</span></span><span style="font-size:10pt;line-height:100%">No_Of_Updates</span><span style="font-size:10pt;line-height:100%"><span style="color:#0000ff">else</span></span><span style="font-size:10pt;line-height:100%"> TIU</span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">.</span></span><span style="font-size:10pt;line-height:100%">No_Of_Updates </span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">/</span></span><span style="font-size:10pt;line-height:100%"> TIU</span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">.</span></span><span style="font-size:10pt;line-height:100%">User_Reads</span><span style="font-size:10pt;line-height:100%"><span style="color:#0000ff">end</span></span><span style="font-size:10pt;line-height:100%"> </span><span style="font-size:10pt;line-height:100%"><span style="color:#0000ff">as</span></span><span style="font-size:10pt;line-height:100%"> Cost_Benefit</span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">,</span></span><span style="font-size:10pt;line-height:100%">TIU</span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">.</span></span><span style="font-size:10pt;line-height:100%">Last_Used_For_Reads</span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">,</span></span><span style="font-size:10pt;line-height:100%">TIU</span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">.</span></span><span style="font-size:10pt;line-height:100%">Index_Type</span><span style="font-size:10pt;line-height:100%"><span style="color:#0000ff">from</span></span><span style="font-size:10pt;line-height:100%"> zIUQ_Temp_Index_Usage TIU</span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">,</span></span><span style="font-size:10pt;line-height:100%"> z_IUQ_Temp_Index_Keys TIK </span><span style="font-size:10pt;line-height:100%"><span style="color:#0000ff">where</span></span><span style="font-size:10pt;line-height:100%"> TIK</span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">.</span></span><span style="font-size:10pt;line-height:100%">F_Obj_Name </span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">=</span></span><span style="font-size:10pt;line-height:100%"> TIU</span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">.</span></span><span style="font-size:10pt;line-height:100%">F_Table_Name </span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">and</span></span><span style="font-size:10pt;line-height:100%"> TIK</span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">.</span></span><span style="font-size:10pt;line-height:100%">F_Ind_ID </span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">=</span></span><span style="font-size:10pt;line-height:100%"> TIU</span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">.</span></span><span style="font-size:10pt;line-height:100%">F_Ind_ID </span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">and</span></span><span style="font-size:10pt;line-height:100%"> TIK</span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">.</span></span><span style="font-size:10pt;line-height:100%">Index_Column_ID </span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">=</span></span><span style="font-size:10pt;line-height:100%"> 1</span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">and</span></span><span style="font-size:10pt;line-height:100%"> TIU</span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">.</span></span><span style="font-size:10pt;line-height:100%">F_Table_Name </span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">not</span></span><span style="font-size:10pt;line-height:100%"> </span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">in</span></span><span style="font-size:10pt;line-height:100%"> </span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">(</span></span><span style="font-size:10pt;line-height:100%"><span style="color:#ff0000">'zIUQ_Temp_Index_Usage'</span></span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">,</span></span><span style="font-size:10pt;line-height:100%"><span style="color:#ff0000">'z_IUQ_Temp_Index_Keys'</span></span><span style="font-size:10pt;line-height:100%"><span style="color:#808080">)</span></span><span style="font-size:10pt;line-height:100%"><span style="color:#0000ff">order</span></span><span style="font-size:10pt;line-height:100%"> </span><span style="font-size:10pt;line-height:100%"><span style="color:#0000ff">by</span></span><span style="font-size:10pt;line-height:100%"> No_Of_Updates </span><span style="font-size:10pt;line-height:100%"><span style="color:#0000ff">desc</span></span><span style="font-size:10pt;line-height:100%"><span style="color:#008000">--order by Cost_Benefit desc </span></span> The query will show you one line for each index in the SQL Database. It shows you the table name, and a list of fields in the index. Note that any non-clustered index also contain the clustered index. For example on SQL Server, the key "Document No." in the "Cus. Ledger Entry table" is "Document No.","Entry No.". Also note that the indexes shown by SQL Server is not always shown in the same order as you have defined them in NAV.The column "No_Of_Updates" basically shows you the cost of this index, since every update requires a lock as well as a write to the database. The next column, "User_Reads", shows you how often this index has been used, either from the UI, or by C/AL code. Compare these two, and you have way to compare the cost against the benefits of each index, as shown in the column "Cost_Benefit", which is simply "No_Of_Updates" / "User_Reads". The column "Last_Used_For_Reads" shows you when an index was actually used for reading.The query sorts the indexes by "No_Of_Updates", with the most updated (most costly) index first. At the last line of the query you can change the sorting to "order by Cost_Benefit desc", and you are likely to see a different picture.Finally, the query shows you whether each index is clustered or non-clustered. The query will create two new tables called "z_IUQ_Temp_Index_Keys" and "zIUQ_Temp_Index_Usage". Although highly unlikely, if you already have tables with these names in your database, then the query will overwrite those without warnings. These tables collect index usage statistics, so if you need to run the query again, for example because you lost the results, or wat to run it with a different sorting, you don't have to run the whole query. Just run the last part of the query - from the section "<span style="font-size:10pt;line-height:100%"><span style="color:#008000">-- Select and join the two tables.</span></span>", and it will run much faster. Only after you change indexes, or want an updated view of index usage, you need to run the whole query again. The data shown by the query is reset every time SQL Server restarts. So if you have recently restarted SQL Server, then the query may not show you the most precise picture of how the indexes are being used over time. Also consider that some indexes may only ever be used for example at end of the month / end of fiscal year, etc. So just because the query shows that a certain index was not used since SQL Server was last restarted, then this index may still be required for specific jobs. Lars Lohndorf-LarsenEscalation EngineerThese postings are provided "AS IS" with no warranties and confer no rights. You assume all risk for your use.Читать дальше
__________________
Расскажите о новых и интересных блогах по Microsoft Dynamics, напишите личное сообщение администратору.
 

Опции темы Поиск в этой теме
Поиск в этой теме:

Расширенный поиск
Опции просмотра

Ваши права в разделе
Вы не можете создавать новые темы
Вы не можете отвечать в темах
Вы не можете прикреплять вложения
Вы не можете редактировать свои сообщения

BB коды Вкл.
Смайлы Вкл.
[IMG] код Вкл.
HTML код Выкл.
Быстрый переход

Рейтинг@Mail.ru
Часовой пояс GMT +3, время: 12:18.
Powered by vBulletin® v3.8.5. Перевод: zCarot
Контактная информация, Реклама.