Index Tunning Tools

Question

Post reply

Index Tunning Tools

peace2007

SSCrazy

Points: 2154
More actions
April 19, 2008 at 5:24 am

#128625

Hi,
Is there any index tunning tools for MS SQL 2005 better than its own one?
Thanks,

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

Mohan Kumar SSCertifiable Points: 5365 More actions · Answer 1

I doubt.

[font="Verdana"]--www.sqlvillage.com[/size][/font]

Ian Yates SSCoach Points: 19738 More actions · Answer 2

What problems are you experiencing that are making you look elsewhere?

Marios Philippopoulos SSC Guru Points: 57190 More actions · Answer 3

If you are referring to the Database-Engine Tuning Advisor (DTA), then yes, that tool has a long way to go to become dependable for index analysis.

However, SQL 2005 ships with a rich library of system views and functions (dynamic management views/functions) that provide unprecedented detail into the inner workings of the database engine.

See this link - one of my favorites - for use of these tools in index analysis:

http://msdn.microsoft.com/msdnmag/issues/08/01/SqlDmvs/default.aspx

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

peace2007 SSCrazy Points: 2154 More actions · Answer 4

Thanks all for the replies

We're having a number of applications with large DB and a number of indexes in each, which are not tuned for years and now causes systems to run slowly.

As I'm new in field of DB administration, I'm looking for a way (better saying, a tutorial) to remove current performance problems and to automate index tunning process for future.

peace2007 SSCrazy Points: 2154 More actions · Answer 5

forgot to say that the link is really useful Marios, thanks a lot

Any more comment or link is appreciated

Charles Kincaid SSChampion Points: 13593 More actions · Answer 6

It's better than the old days in SQL 2000. You had to run the profiler for 24 hours to gather a "workload" and then run that through the index tuning wizard to come up with the recommendations. It was often faster to just set a team down and pull up every SQL statement and review by hand. Often we could spot where the performance problems occurred and run specific queries through Query Analyzer and do a Tarzan Analysis. (Table Scan, bad. Index Lookup, good.)

Thanks, Marios, for the great link.

ATBCharles Kincaid

Vidhya Sagar SSCrazy Eights Points: 8216 More actions · Answer 7

You can make use of the below DMV's to find out which index is in use whether seek or user scan etc.

Sys.dm_db_index_physical_stats

Sys.dm_db_index_usage_stats

For DTA refer this article Database Tuning Advisor - DTA

Regards..Vidhya Sagar
SQL-Articles

Marios Philippopoulos SSC Guru Points: 57190 More actions · Answer 8

Glad it was helpful guys! 🙂

Here is a query to gather useful missing indexes:

--Get missing indexes
SELECT
index_advantage
,user_seeks
,last_user_seek
,avg_total_user_cost
,avg_user_impact
, equality_columns
,included_columns
,[statement]
FROM
(SELECT
user_seeks * avg_total_user_cost * (avg_user_impact * 0.01) AS index_advantage
,migs.* FROM sys.dm_db_missing_index_group_stats migs
) AS migs_adv
inner join
sys.dm_db_missing_index_groups AS mig
ON
migs_adv.group_handle = mig.index_group_handle
inner join
sys.dm_db_missing_index_details AS mid
ON
mig.index_handle = mid.index_handle
WHERE
migs_adv.index_advantage > 10000
ORDER BY
migs_adv.index_advantage desc

And here is one to gather unused indexes (that need to be dropped):

SELECT
OBJECT_NAME( s.[object_id] ) AS TableName
,i.name AS IndexName
,s.user_updates
FROM
sys.dm_db_index_usage_stats s
INNER JOIN
sys.indexes i
ON
s.object_id = i.object_id
AND i.index_id = s.index_id
WHERE
db_name(s.database_id) = db_name(db_id())
AND objectproperty(i.object_id, 'IsIndexable') = 1
AND LEFT(i.name, 3) NOT IN ('PK_', 'UC_')
AND s.user_updates > 0 AND s.user_seeks = 0
AND s.user_scans = 0 AND s.user_lookups = 0
ORDER BY
OBJECT_NAME( s.[object_id] )

Make sure your instance has been running for a sufficient amount of time (at least a few days) before basing any decisions on the results of DMV queries.

happy indexing! 🙂

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

Charles Kincaid SSChampion Points: 13593 More actions · Answer 9

Now if we just had something like this for the Compact Edition on the mobiles. :sigh:

ATBCharles Kincaid

Marios Philippopoulos SSC Guru Points: 57190 More actions · Answer 10

Perhaps we can request that from Microsoft to include in their next release.

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]