Do I need an Index?

[sheila11]

Hi All,

My Stored Proc is returning results in about 20 milliseconds. Should I be placing an Index on the table that this SP is querying?

What is the duration that I can use as a benchmark, beyond which I should start adding Indexes to tables?

TIA,
Sheila

 

[markros]

Try running this query

SELECT object_name(s.object_id) AS TableName, COL_NAME(s.object_id, sc.column_id) AS ColumnName,
		* 
	FROM sys.stats s
		JOIN sys.stats_columns sc ON s.object_id = sc.object_id AND s.stats_id = sc.stats_id
	WHERE object_name(s.object_id) NOT LIKE 'sys%' 
	ORDER BY 1,2

to see what indexes may be suggested by optimizer.

 

[sheila11]

Thanks, Markros.

It gives me a list of over three hundred columns. Some of them are Primary Key ID columns.

I am surprised, because most stored procs are returning in less than 6 milliseconds. If the DB needed so many indexes, wdn't the Stored Procs have shown much poorer performance?

Sheila

 

[mrdenny]

The only way to know if a query needs an index added is to look at the execution plan. You could be querying a set of very large tables and have a query take 20 minutes when indexed.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)

My Blog