April 19, 2008 at 5:24 am
Hi,
Is there any index tunning tools for MS SQL 2005 better than its own one?
Thanks,
April 19, 2008 at 7:38 pm
I doubt.
[font="Verdana"]--www.sqlvillage.com[/size][/font]
April 19, 2008 at 9:17 pm
What problems are you experiencing that are making you look elsewhere?
April 19, 2008 at 10:58 pm
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]
April 20, 2008 at 10:43 pm
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.
April 20, 2008 at 10:44 pm
forgot to say that the link is really useful Marios, thanks a lot
Any more comment or link is appreciated
April 21, 2008 at 8:30 am
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
April 21, 2008 at 8:38 am
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
For DTA refer this article Database Tuning Advisor - DTA
Regards..Vidhya Sagar
SQL-Articles
April 21, 2008 at 8:52 am
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]
April 21, 2008 at 9:13 am
Now if we just had something like this for the Compact Edition on the mobiles. :sigh:
ATBCharles Kincaid
April 21, 2008 at 9:46 am
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]
April 21, 2008 at 10:37 pm
Thanks saggar!:)
April 21, 2008 at 10:39 pm
Hey Charles your comments helped me much :w00t: Thank you
April 22, 2008 at 8:39 am
peace2007 (4/21/2008)
Hey Charles your comments helped me much :w00t: Thank you
You are very welcome.
ATBCharles Kincaid
November 20, 2009 at 8:57 pm
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy