May 12, 2010 at 3:04 am
Do not ever run DTA against a production database. If you use it at all, you should run it against a copy of the databsae on a dev or test server. Make sure that the workload you submit to it is a comprehensive one. This is a classic case of 'garbage in, garbage out'. If the workload is not comprehensive, the index recommendations will be worse than usual.
Do note our comments on testing DTA's recommendations. It's not always right.
It looks like you're an absolute beginner at performance tuning. Is getting a consultant in not an option? It's likely to be one of the better ways to proceed if you don't know where to start.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 12, 2010 at 3:39 am
Hello GilaMonster,
actually i was assigned to get more information about Index and and also given a table and asked me to create index to that table.
is it enough if i know upto Index,cluster index and non cluster index?
could you please suggest me correct material to get more information about index and step to follow to do performance turning.
thanx
May 12, 2010 at 5:30 am
Satya,
I'd strongly recommend you pick up a couple of books and read, a lot. If you've never done this before, it's very simple to make lots of big mistakes. Be careful with production systems.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
May 12, 2010 at 5:40 am
satya.sakamuri (5/12/2010)
actually i was assigned to get more information about Index and and also given a table and asked me to create index to that table.
Did you try google?
Assigned? Homework assignment or work assignment?
Start with Books Online. Lots of good info there. This series may be of use - http://qa.sqlservercentral.com/articles/Indexing/68439/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 12, 2010 at 6:32 am
its a homework assignment,thanx for the BOL link.
May 12, 2010 at 6:39 am
Then you should note that we don't answer homework questions. We'll give you hints, but not answers. Spoon-fed answers teach you nothing. Have you tries asking your teacher/lecturer/tutor? for assistance?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 12, 2010 at 7:08 am
satya.sakamuri (5/12/2010)
its a homework assignment,thanx for the BOL link.
This is one of the most amazing threads I have read on SQL Server Central forums in a while. A fresher dabbling with database tuning concepts - all experts coming up with advice(s) only to be finally, somewhat ironically told it is a homework assignment?
Satya I wonder why you didn't mention it right at the beginning? Have you taken the time to search Clustered Index/Non Clustered Index/ Database Tuning right here on this forum itself?
Saurabh Dwivedy
___________________________________________________________
My Blog: http://tinyurl.com/dwivedys
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537
May 12, 2010 at 7:17 am
hi guys..
its not my college assignment..
wat iam trying to say is this work was assigned to me by my project lead,he asked to get a clear idiea on index and all the related stuff.
i never been thorugh this concept so i am trying to find more information through this forms.
thanx
May 12, 2010 at 7:28 am
Grant Fritchey (5/11/2010)
Whether the DBA knows it or not, that data can get changed by circumstances. It's best that you know that fact in order to gauge best whether or not the index usage is an accurate reflection of your system. Also, know which indexes are getting used doesn't help that much in determining which ones to create.
Actually, there are a handful of DMVs with the prefix dm_db_missing_index_* which keep track of table scans and other statistics for suggesting missing indexes.
I got following DMV query from a blog post by Bart Duncan. It will even generate the create index statements.
http://blogs.msdn.com/bartd/archive/2007/07/19/are-you-using-sql-s-missing-index-dmvs.aspx">
http://blogs.msdn.com/bartd/archive/2007/07/19/are-you-using-sql-s-missing-index-dmvs.aspx
SELECT
mid.statement, 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
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
May 12, 2010 at 7:33 am
eric_russell 13013 (5/12/2010)
Grant Fritchey (5/11/2010)
Whether the DBA knows it or not, that data can get changed by circumstances. It's best that you know that fact in order to gauge best whether or not the index usage is an accurate reflection of your system. Also, know which indexes are getting used doesn't help that much in determining which ones to create.
Actually, there are a handful of DMVs with the prefix dm_db_missing_index_* which keep track of table scans and other statistics for suggesting missing indexes.
Yup, but the one that you mentioned earlier doesn't, and that (index_usage) is the one Grant was referring to.
Missing index DMVs - less comprehensive than DTA, less trustworthy, very likely to produce near-duplicates, redundant indexes and massive, huge indexes. Use with caution. Do not trust without doublechecking.
I got following DMV query from a blog post by Bart Duncan. It will even generate the create index statements.
Do not, please, ever do that and run the run the generated script as-is. Or, if you do, let me know so that I can send you a proposal for a performance tuning contract to remove and fix the indexes on that DB.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 12, 2010 at 7:36 am
satya.sakamuri (5/12/2010)
...
if i run the above script inmy sql query with selecting perticular database ....will the data in tables effect?becaz those are production live tables in that database.
could you plese clear me this doubt before i run the DTA script.
The script isn't related to the DTA (Database Tuning Advisor), but queries the DMV (database management views) directly. My experience has been that it's not resource intensive, taking no more than a few seconds, because it references usage statistics retained in a special memory cache.
A single full table scan resulting from a missing index would potentially use more resources, so the sooner you identify missing indexes, the better.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
May 12, 2010 at 7:43 am
eric_russell 13013 (5/12/2010)
Grant Fritchey (5/11/2010)
Whether the DBA knows it or not, that data can get changed by circumstances. It's best that you know that fact in order to gauge best whether or not the index usage is an accurate reflection of your system. Also, know which indexes are getting used doesn't help that much in determining which ones to create.
Actually, there are a handful of DMVs with the prefix dm_db_missing_index_* which keep track of table scans and other statistics for suggesting missing indexes.
I got following DMV query from a blog post by Bart Duncan. It will even generate the create index statements.
http://blogs.msdn.com/bartd/archive/2007/07/19/are-you-using-sql-s-missing-index-dmvs.aspx">
http://blogs.msdn.com/bartd/archive/2007/07/19/are-you-using-sql-s-missing-index-dmvs.aspx
SELECT
mid.statement, 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
Gail addresses several of the shortcomings of relying on these as a tuning mechanism. I'll add another. Which query is that index being suggested for? Is it a query that was called once this year and never will be again or is it a query that is called thousands of times a minute? One of these two you're really going to want to spend time working on the index for and the other you aren't.
There is a way to get this information, but it's not from the missing indexes DMV's.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
May 12, 2010 at 7:48 am
Grant Fritchey (5/12/2010)
There is a way to get this information, but it's not from the missing indexes DMV's.
However getting it from the plan cache is not reliable, as the plan could have been recompiled, aged out or invalidated at various times resulting in the stats in the plan cache being for different lengths of time for different queries. And that's not even considering queries whose plans never cache at all...
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 12, 2010 at 7:49 am
GilaMonster (5/12/2010)
Do not, please, ever do that and run the run the generated script as-is. Or, if you do, let me know so that I can send you a proposal for a performance tuning contract to remove and fix the indexes on that DB.
Indeed, I would not reccomend that anyone implement all the suggestions wholesale or use the actual create index statements as is. Really, what's useful about the output of this query is that it aggregates the each reccomendation into a more readble format, rather than dumping out one row per column, which is hard to digest. The DMV query is an alternative to the DTA in a situation where a performance problem has been reported in the production environment, and you want a quick and lightweight query to suggest what might be going on.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
May 12, 2010 at 7:50 am
GilaMonster (5/12/2010)
Grant Fritchey (5/12/2010)
There is a way to get this information, but it's not from the missing indexes DMV's.However getting it from the plan cache is not reliable, as the plan could have been recompiled, aged out or invalidated at various times resulting in the stats in the plan cache being for different lengths of time for different queries. And that's not even considering queries whose plans never cache at all...
Yep. But it's still the only way to try to match the missing index suggestion to a query.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 15 posts - 16 through 30 (of 33 total)
You must be logged in to reply to this topic. Login to reply