May 1, 2009 at 5:22 am
I'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...
May 1, 2009 at 8:03 am
On a small table you will not notice much improvement; but general recommendation is you should have cluster indexes on every table.
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 ....
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.
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
May 1, 2009 at 8:26 am
Where are the performance improvements needed? During selects, updates and/or the table load?
May 1, 2009 at 9:47 am
Firstly 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 procs
That 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....
May 1, 2009 at 11:02 am
Not 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.
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
May 1, 2009 at 11:32 am
My 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 today
May 1, 2009 at 3:09 pm
One 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.
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 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.aspx
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
May 1, 2009 at 3:48 pm
Interesting debate here, anyone want to turn some testing into an article?
May 4, 2009 at 10:13 am
I'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....!
May 4, 2009 at 11:01 am
Mohit (5/1/2009)
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.
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 workload
Missing 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.
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 4, 2009 at 12:46 pm
Thanks 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.
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
May 4, 2009 at 1:43 pm
Mohit (5/4/2009)
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.
Agreed. I just see too many people suggesting to generate create index statements from the missing index DMVs and simply run them.
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 5, 2009 at 7:00 am
How 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?
May 5, 2009 at 7:07 am
I 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 aswell
May 7, 2009 at 8:05 am
Agreed. I just see too many people suggesting to generate create index statements from the missing index DMVs and simply run them.
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.....
Thanks
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply