InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 23 May 2013 02:30:11 GMT20http://www.sqlservercentral.com/Forums/Topic708345-360-1.aspx[quote][b]Mikey01 (5/1/2009)[/b][hr]I should point out the process [b]creates over 150 tables [/b]in total.... and is probably spread across a couple 100,000 lines of [b]SQL & 250 stored procs[/b][/quote]That seems a bit much even for a really complicated process. What does this process actually do?My recommendation would be to forget about indexing for now. A whole lot of people think that Temp Tables and While Loops are somehow better than a cursor. Neither is acceptable and the number of Temp Tables and procs you've identified strongly indicates such RBAR. Start by checking to find out if the stored procs are set based or if they're only handling one agonizing row at a time.Sat, 05 Mar 2011 10:52:38 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic708345-360-1.aspxJTS: RID Lookup is used to retrieve information from the table that is not part of the index. For example lets say you have a table with col1, col2, col3, col4. You have a non-clustered index on col1. You execute "Select * from Table1 Where Col1 = 123"; system will do a quick seek on col1 to get RID information for all the values in col1 where it is 123. Then using the RID information it will go to the heal to get the remaining information requested by the query, namely col2, col3, and col4. RID tells SQL Server where the row is physically located and for a singleton requests is fairly quick. However if you have scans or range retrieval of the information it can get expensive :).Answer you question, should you add Clustered Index? Since this is a 3rd party application most likely not a good idea as most 3rd party vendors will void warranty if people change their database or application. So please take that into consideration. However define large table for me? If the table has more then 1000 pages, maybe look at considering a cluster index. Alternative you might be able to convert one of your non-clusters indexes to clustered. Test, test, and test... before you implement anything :).Cheers!Sat, 05 Mar 2011 09:13:21 GMTMohit K. Guptahttp://www.sqlservercentral.com/Forums/Topic708345-360-1.aspx[quote][b]Mohit K. Gupta (5/1/2009)[/b][hr]If you are just using non-clustered indexes and heap; it will first cause a RID Lookup anytime you access anything in that table via a Index Seek. That is when an index seek operation completes it will have to go back to main heap to get the data. In addition when you are inserting, deleting, and updating records in the table the heap can become fragmented and there is no way to defrag it because of how heap are stored. So the access speed, updates, and deletes can take long time.[/quote]Hi there, I have also found a large table with no clustered indexes and four non-clustred indexes. I can see that one of these non-clustered indexes is being used in the query, but I don't understand what happens then with the RID Lookup - since there is no clustered index does SQL Server have to scan the whole heap? In which case, why bother with an index at all! Hope I'm making sense :-) I'm thinking of adding a clustered index to the table, but it's from a 3rd party app, so I need to be very careful (i.e. break the test system first!).Thu, 03 Mar 2011 07:59:20 GMTjts_2003http://www.sqlservercentral.com/Forums/Topic708345-360-1.aspxNo formal process.If I'm considering the indexes from missing indexes DMV, first I'll make sure there are no partial redundancies within the index recommendations. I'll also make sure that there are no existing indexes that I can widen.Once that's done, I'll get all the queries that run against that table (from the metadata if all access is via SP, or from profiler if they're not), run them, get their current performance characteristics. Create the new index, or alter an existing one, run the queries again and see if there's been an improvement.Thu, 07 May 2009 09:33:18 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic708345-360-1.aspx[quote]Agreed. I just see too many people suggesting to generate create index statements from the missing index DMVs and simply run them.[/quote]Gail,Do you have a general process you go through for adding indexes...is simply a matter of testing the indexes one at a time before implementing? Just curious.....ThanksThu, 07 May 2009 08:05:41 GMTBob Griffinhttp://www.sqlservercentral.com/Forums/Topic708345-360-1.aspxI realise I can use the DTA to analyse the Perm tables i've created I'm just wondering if i've wasted my time here potentially? i.e. could i have just used the DTA against teh Temp Tables...Thansk for all the input here guys aswellTue, 05 May 2009 07:07:52 GMTMikey01http://www.sqlservercentral.com/Forums/Topic708345-360-1.aspxHow does the DTA handle Temporary Tables.....I was of teh opinion that the DTA was incappable of giving suggestions on Temporary tables is that incorrect?Tue, 05 May 2009 07:00:25 GMTMikey01http://www.sqlservercentral.com/Forums/Topic708345-360-1.aspx[quote][b]Mohit (5/4/2009)[/b][hr]I understand your points, I think the Microsoft consultant was just suggesting I should seriously look at the fields in questions because there is no doubt for room for improvement (might not be as significant as DMV suggests). I have never created indexes out blank from there; but it gives a "starting" point.[/quote]Agreed. I just see too many people suggesting to generate create index statements from the missing index DMVs and simply run them.Mon, 04 May 2009 13:43:26 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic708345-360-1.aspxThanks Gail.I understand your points, I think the Microsoft consultant was just suggesting I should seriously look at the fields in questions because there is no doubt for room for improvement (might not be as significant as DMV suggests). I have never created indexes out blank from there; but it gives a "starting" point.But I never thought about the cluster/non-clustered part heh, thanks for pointing that out. Most databases I have tuned I got clustered indexes fixed on them (so I thought); so I never even consider if the recommended index by DMV for Clustered. Or that I should look at revising clustered index based of that information. I appreciate your view points :).Thanks a lot.Mohit.Mon, 04 May 2009 12:46:14 GMTMohit K. Guptahttp://www.sqlservercentral.com/Forums/Topic708345-360-1.aspx[quote][b]Mohit (5/1/2009)[/b][hr]I was talking to Microsoft Tier-3 Support guy about SQL Server; he suggested using the Dash Board there is a 90%+ improvement there will definetly be improvement. He said to take care for values below 80... but for above 90 you should consider creating the index.[/quote]Few things to note though.The logic that goes into an index being suggested in the missing index DMV is a subset of what DTA does.The recommendations are done individually for queries (as they are optimised) and not for an entire workloadMissing indexes will never suggest clustered vs nonclustered.If will never suggest modifying an existing index or dropping an existing one.It won't check for similar indexes in the DMV already or similar ones that already exist.Say you have an existing index on 2 columns - (a,b) however you have two queries (heavily used) that either filter on a, b and c or filter on a and b and select c. Missing indexes DMV may very well suggest 2 new indexes, one on (a,b,c) and one on a,b include (c). If you create both of those, you've got 2 redundant indexes.Mon, 04 May 2009 11:01:41 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic708345-360-1.aspxI've put together a very basic article for a beginner audience on setting up the performance dashboard that I created as I set it up in my environment. I've submitted to articles@sqlservercentral.com.....Look forward to your feedback....!Mon, 04 May 2009 10:13:13 GMTBob Griffinhttp://www.sqlservercentral.com/Forums/Topic708345-360-1.aspxInteresting debate here, anyone want to turn some testing into an article?Fri, 01 May 2009 15:48:51 GMTSteve Jones - SSC Editorhttp://www.sqlservercentral.com/Forums/Topic708345-360-1.aspxOne note about the DMV for Indexes ...I was talking to Microsoft Tier-3 Support guy about SQL Server; he suggested using the Dash Board there is a 90%+ improvement there will definetly be improvement. He said to take care for values below 80... but for above 90 you should consider creating the index.[code]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 migINNER JOIN sys.dm_db_missing_index_group_stats migsON migs.group_handle = mig.index_group_handleINNER JOIN sys.dm_db_missing_index_details midON mig.index_handle = mid.index_handleWHERE migs.avg_total_user_cost * ( migs.avg_user_impact / 100.0 ) * ( migs.user_seeks + migs.user_scans ) > 10ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * ( migs.user_seeks + migs.user_scans ) DESC[/code]I got that code from below link ... Thanks.Ref: http://blogs.msdn.com/bartd/archive/2007/07/19/are-you-using-sql-s-missing-index-dmvs.aspxFri, 01 May 2009 15:09:10 GMTMohit K. Guptahttp://www.sqlservercentral.com/Forums/Topic708345-360-1.aspxMy Perf testing is advancing along i have now removed all indexs and would you believe the DMV's are only suggesting a fraction of the indexes ......This process is limited to proccess a certain amount of units at a time so i know that if i go to the max allowed amount of through put and the DMV's are still saying these existing indexes won't be of use then i can be pretty confident to remove them...Of course the base line test will be the final confirmantion of this task but all in all its not been a bad days work :)Let the weekend begin I'm off home..Thansk for the help todayFri, 01 May 2009 11:32:25 GMTMikey01http://www.sqlservercentral.com/Forums/Topic708345-360-1.aspxNot surpirsed Mike, I find most people don't even know what a cluster index is. I remeber getting an answer from someone at one time "we are not running clusteres, so we can't use cluster index!" ... I was hmm okay!I walked away :-).But anyhow, that sounds like one hell of a process to deal with 150 temp tables and such. I would strongly recommend adding clustered indexes to the chart; but also I would recommend to control the number of non-clustered indexes. Because these table mostly likely be changed frequently you don't want to burden the system with having to update too many non-clustered indexes specially for batch imports like SELECT * INTO #Temp... Thanks.Mohit.Fri, 01 May 2009 11:02:11 GMTMohit K. Guptahttp://www.sqlservercentral.com/Forums/Topic708345-360-1.aspxFirstly these Perm tables are created where before the temp tables were created within the process purely so i could conduct my analysis i.e capture the stats..... I should point out the process creates over 150 tables in total.... and is probably spread across a couple 100,000 lines of SQL & 250 stored procsThat means every time before i run the process i must drop the tables...Thanks for the heads up in relation to the DMV stats being reset .. i've used these before so i'm aware of the stats being reset under certain scenarios...In terms of the performance improvement i'm looking for, "See if there is any improvement to be made from new or modifying indexes" was my brief :)I have since found out why only Non-Clustered indexes were created i spoke with some of the current Dev team they said thev'e never modified the Index's on the temp tables... then one Dev asked me what type of index is created by the CREATE INDEX statement and then the penny dropped ... the original developer used the CREATE INDEX statement which creates NON_Clustered indexes by default....Fri, 01 May 2009 09:47:24 GMTMikey01http://www.sqlservercentral.com/Forums/Topic708345-360-1.aspxWhere are the performance improvements needed? During selects, updates and/or the table load? Fri, 01 May 2009 08:26:22 GMTBob Griffinhttp://www.sqlservercentral.com/Forums/Topic708345-360-1.aspxOn a small table you will not notice much improvement; but general recommendation is you should have cluster indexes on every table. [quote]It was at this time i started looking at existing index's on tables created within this process and i found that none of the tables had Clustered index's but many had non-clustered index's, sometimes multiple Clustered Index's ....[/quote]A table can only have ONE clustered index it cannot have multiple clustered indexes because cluster indexes dictate how the rows are sorted on the physical level.If you are just using non-clustered indexes and heap; it will first cause a RID Lookup anytime you access anything in that table via a Index Seek. That is when an index seek operation completes it will have to go back to main heap to get the data. In addition when you are inserting, deleting, and updating records in the table the heap can become fragmented and there is no way to defrag it because of how heap are stored. So the access speed, updates, and deletes can take long time.These temporary tables you changed to permanent tables; do these all get destroyed and recreated every time?Also becareful about using the Index DMV they are only good as of last time you restarted your SQL Serer. Also they can lead you wrong if your statistics are out of date.Thanks.Mohit.Fri, 01 May 2009 08:03:38 GMTMohit K. Guptahttp://www.sqlservercentral.com/Forums/Topic708345-360-1.aspxI've been asked to analyse a process that uses alot of temporary tables to identify any potential index improvements. These tables are relatively small the largest maybe reaching 10000 records, but majority never get past 1000 records...I took all the stored procs and canabailised them so the Temp tables were now Permanent tables ......I then ran the proccess and started analysing the results from the Index DMV's.....It was at this time i started looking at existing index's on tables created within this process and i found that none of the tables had Clustered index's but many had non-clustered index's, sometimes multiple Clustered Index's ....Does anyone have an opinion on this i.e. when would this be an acceptable approach to implement?Also whats the underlying cost of using a non-clustered index when no clustered index exists, since non-clustered indexes are built on the clustered index, how does this relate to when we use a heap?I shoud point out the tables are loaded once then updated many times and queired many times before being dropped...Fri, 01 May 2009 05:22:40 GMTMikey01