Prev: Getting error on INSERT.. SELECT using table valued function
Next: sql query
From: John on 25 Jul 2008 22:39
Hi

Is there an option or script that will automatically/easily index all/most
columns of a table or better still all tables in a database?

Thanks

Regards


From: Eric Isaacs on 25 Jul 2008 23:12
You don't want to index all or most columns in a table, otherwise the
inserts and updates will take a long time. There's a balance between
the number of indexes and the performance of the selects/inserts/
updates/deletes. The reads, sorts and finds and joins take longer
without proper index design and the inserts and updates take longer
with too many indexes.

SQL Server does have tools that help you identify places where it
thinks indexes would be useful.

See this link:
http://blogs.msdn.com/bartd/archive/2007/07/19/are-you-using-sql-s-missing-index-dmvs.aspx

Here's the query it suggests:
SELECT
migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) *
(migs.user_seeks + migs.user_scans) AS improvement_measure,
'CREATE INDEX [missing_index_' + CONVERT (varchar,
mig.index_group_handle) + '_' + CONVERT (varchar, mid.index_handle) +
'_' + LEFT(PARSENAME(mid.statement, 1), 32) + ']' + ' ON ' +
mid.statement + ' (' + ISNULL(mid.equality_columns, '') + CASE WHEN
mid.equality_columns IS NOT NULL

AND mid.inequality_columns IS NOT NULL THEN ','

ELSE ''

END + ISNULL(mid.inequality_columns, '') + ')' + ISNULL(' INCLUDE (' +
mid.included_columns + ')', '') AS create_index_statement,
migs.*,
mid.database_id,
mid.[object_id]
FROM
sys.dm_db_missing_index_groups mig
INNER JOIN sys.dm_db_missing_index_group_stats migs ON
migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle
= mid.index_handle
WHERE
migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) *
(migs.user_seeks + migs.user_scans) > 10
ORDER BY
migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks
+ migs.user_scans) DESC


I hope that helps,

-Eric Isaacs
From: John on 25 Jul 2008 23:29
Hi Eirc

Thanks for that. My point is that if a tool can cerate indexes on all fields
then I can delete the ones that are not needed.

Thanks again.

Regards

"Eric Isaacs" <eisaacs(a)gmail.com> wrote in message
news:4e23bc4e-ee8f-4dd7-b517-491e6752b0c6(a)w39g2000prb.googlegroups.com...
> You don't want to index all or most columns in a table, otherwise the
> inserts and updates will take a long time. There's a balance between
> the number of indexes and the performance of the selects/inserts/
> updates/deletes. The reads, sorts and finds and joins take longer
> without proper index design and the inserts and updates take longer
> with too many indexes.
>
> SQL Server does have tools that help you identify places where it
> thinks indexes would be useful.
>
> See this link:
> http://blogs.msdn.com/bartd/archive/2007/07/19/are-you-using-sql-s-missing-index-dmvs.aspx
>
> Here's the query it suggests:
> SELECT
> migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) *
> (migs.user_seeks + migs.user_scans) AS improvement_measure,
> 'CREATE INDEX [missing_index_' + CONVERT (varchar,
> mig.index_group_handle) + '_' + CONVERT (varchar, mid.index_handle) +
> '_' + LEFT(PARSENAME(mid.statement, 1), 32) + ']' + ' ON ' +
> mid.statement + ' (' + ISNULL(mid.equality_columns, '') + CASE WHEN
> mid.equality_columns IS NOT NULL
>
> AND mid.inequality_columns IS NOT NULL THEN ','
>
> ELSE ''
>
> END + ISNULL(mid.inequality_columns, '') + ')' + ISNULL(' INCLUDE (' +
> mid.included_columns + ')', '') AS create_index_statement,
> migs.*,
> mid.database_id,
> mid.[object_id]
> FROM
> sys.dm_db_missing_index_groups mig
> INNER JOIN sys.dm_db_missing_index_group_stats migs ON
> migs.group_handle = mig.index_group_handle
> INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle
> = mid.index_handle
> WHERE
> migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) *
> (migs.user_seeks + migs.user_scans) > 10
> ORDER BY
> migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks
> + migs.user_scans) DESC
>
>
> I hope that helps,
>
> -Eric Isaacs


From: Stuart Ainsworth on 25 Jul 2008 23:37
On Jul 25, 11:29 pm, "John" <i...(a)nospam.infovis.co.uk> wrote:
> Hi Eirc
>
> Thanks for that. My point is that if a tool can cerate indexes on all fields
> then I can delete the ones that are not needed.
>
> Thanks again.
>
> Regards
>

I think Eric's point is that it's far easier to identify where indexes
are needed than it is to identify ones that are not needed. Far better
to be conservative with your index creation than it is to overuse
them.

Stu
From: Plamen Ratchev on 26 Jul 2008 02:17
You can use SQL Profiler (or server side trace) to save a trace of good load
of user activity and then use it with Database Engine Tuning Advisor. It
will analyze the queries and suggest what indexes are needed, as well as
generate all scripts. It is still best to analyze in detail queries but this
will give you a starting point if you database does not have any indexes.

HTH,

Plamen Ratchev
http://www.SQLStudio.com

 |  Next  |  Last
Pages: 1 2
Prev: Getting error on INSERT.. SELECT using table valued function
Next: sql query