SQL Server Performance Forum – Threads Archive
Indexes in a stored proc???
1,How can we check existing indexes on the tables involved in a stored proc?<br />2,If there are none, then how can we create one?<br /><br />thanks<img src=’/community/emoticons/emotion-1.gif’ alt=’
‘ />
<blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by i.netdeveloper</i><br /><br />1,How can we check existing indexes on the tables involved in a stored proc?<br />2,If there are none, then how can we create one?<br /><br />thanks<img src=’/community/emoticons/emotion-1.gif’ alt=’
‘ /><br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br /><br />Look BOL (SQL Server Books Online) for create index information.<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">
BTW, you have pretty similar index question here too. http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=19628 Thanks, Name
———
Dilli Grg (1 row(s) affected)
<blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by i.netdeveloper</i><br /><br />1,How can we check existing indexes on the tables involved in a stored proc?<br />2,If there are none, then how can we create one?<br /><br />thanks<img src=’/community/emoticons/emotion-1.gif’ alt=’
‘ /><br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br />1>Check the execution plan to see the indexes used by sql statements. If you find table scan then it’s not using any indexes.<br />Also you can run sp_help on table objects used in procedure to find the indexes created on the table.<br />2>In 2005 you can take suggestions on creating Indexes from Database Engine Tuning advisor tool else in Sql 2000, use Index Tuning Wizard SQL server tool<br />
Anyways, stored procs are not the right places to create indexes. Roji. P. Thomas
SQL Server MVP
http://toponewithties.blogspot.com
If it is related to SQL Server 2005 sys.dm_db_index_usage_stats Dynamic management view can help u.
Read more about Index Related Dynamic Management Views and Functions in BOL Madhu
]]>