August 26, 2008 at 5:47 am
I've recently inherited DBA responsibility for a database in an application best known for its less than stellar performance. We are making some progress but trying not to add too many variables to each change.
The database uses the party-plan design, which is not necessarily a good or bad choice, but it is evident that there was not a lot of index (clustered or otherwise) planning in either the design or implementation, nor serious execution plan testing of the stored procedures.
I suppose it can be considered an OLTP implementation, although it may be a bit of a crossover because a lot of the input is then used immediately in decision making for the next step.
My question is in regard to the Missing Indexes report. I am looking at other things like duration and cpu usage to identify problematic stored procedures, but I am looking at the Missing Indexes results pretty closely. I am a bit leery of simply implementing all the suggestions, if only because of the scale of change.
What has your experience with the Missing Indexes report been? Do you use the suggestions to implement new indexes pretty much as is or tailor the results? Or do you ignore the reports and reverse engineer some proper indexing in some other fashion?
------------
Buy the ticket, take the ride. -- Hunter S. Thompson
August 26, 2008 at 5:57 am
Take its suggestions as just that. Suggestions. It's less accurate even than the Database Tuning Advisor.
Missing indexes is populated by the query optimiser as it's optimising queries. It will note that there would have been a better plan for a particular query, had a certain index existed then that index details gets put into missing indexes.
It doesn't take into account clustered/nonclustered. It doesn't consider that there may be an existing index almost adequate.
I will use the missing indexes DMVs as a place to start when considering indexes for a table, but I will always check to see if the indexes really are useful and if they aren't going to hurt other queries
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
August 26, 2008 at 6:03 am
By 'existing index almost adequate', I am assuming you mean one that may have most but not all the elements in the suggestion and could thus be simply revised rather than adding another index. Am I correct?
I've always using the tuning advisors with a grain of salt. Part of my problem here is I don't know what I don't know, and the original implementors are long gone. So what is a good way to predict the impact of a index change on other portions of the application short of running every affected stored procedure before and after comparing the performance statistics?
------------
Buy the ticket, take the ride. -- Hunter S. Thompson
August 26, 2008 at 6:14 am
G Bryant McClellan (8/26/2008)
By 'existing index almost adequate', I am assuming you mean one that may have most but not all the elements in the suggestion and could thus be simply revised rather than adding another index. Am I correct?
Yup
So what is a good way to predict the impact of a index change on other portions of the application short of running every affected stored procedure before and after comparing the performance statistics?
There isn't really one.
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
August 26, 2008 at 5:56 pm
Also, keep in mind that the missing index dynamic management view data is reset to zeros when SQL restarts. This means the data may not take into account those week/month/quarter reports you may run if you have restarted since the last run.
August 27, 2008 at 5:17 am
That part I was aware of. It also skews the index usage counts when a new index is added because you don't necessarily see the difference. You can see the new index usage growing but unless you are taking snapshots regularly you cannot gauge the impact on other indexes on the table.
------------
Buy the ticket, take the ride. -- Hunter S. Thompson
August 27, 2008 at 10:28 am
G Bryant McClellan (8/27/2008)
That part I was aware of. It also skews the index usage counts when a new index is added because you don't necessarily see the difference. You can see the new index usage growing but unless you are taking snapshots regularly you cannot gauge the impact on other indexes on the table.
The DMV methodology for tracking missing indexes, unused indexes etc., is a great tool and fills a major gap in performance monitoring. However, as others already mentioned in this thread, there are caveats.
One caveat is that indexes used in places other than in the WHERE clause of queries are not included in the DMVs, so, for example, indexes needed on JOINed columns are not taken into account. Also, as someone already mentioned, the data is flushed when the instance is restarted. It is also flushed when there is memory pressure. So, best bet is to record the data somewhere at set intervals and review the results time and again.
The DMVs are not an all-in-one solution, but it is a great first step in identifying missing/unused indexes (and lots of other stuff).
__________________________________________________________________________________
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]
August 27, 2008 at 10:37 am
Marios Philippopoulos (8/27/2008)
One caveat is that indexes used in places other than in the WHERE clause of queries are not included in the DMVs, so, for example, indexes needed on JOINed columns are not taken into account.
Do you have a reference for that?
Afaik, any index that the optimiser thinks would be useful when evaluating the query will go into the missing indexes DMV and will appear in the execution plan for that query.
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
August 27, 2008 at 10:44 am
Rather than relying on a shotgun approach like the missing index DMV, I tend to hit the low performance queries and tune them one at a time. That can mean indexes, it can mean code rewrites, it can mean constraints on tables, it can mean dozens of other things. I do whatever I need to get one query working well, then move on to the next one.
By prioritizing the queries based on how bad they are, how easily they look like they can be fixed, and how often they are used, I can get a lot of improvement very rapidly.
It also means that it's usually pretty easy to control the scope of a change, and to keep an undo script for it handy. That lets me know exactly what was improved (or broken) by a particular change.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 27, 2008 at 10:58 am
GilaMonster (8/27/2008)
Marios Philippopoulos (8/27/2008)
One caveat is that indexes used in places other than in the WHERE clause of queries are not included in the DMVs, so, for example, indexes needed on JOINed columns are not taken into account.
Do you have a reference for that?
Afaik, any index that the optimiser thinks would be useful when evaluating the query will go into the missing indexes DMV and will appear in the execution plan for that query.
I read it in an article on SQL Server magazine, April 2007, by Kalen Delaney: "use Missing-Index Groups for Query Tuning". I quote:
"At present, the missing index information is generated only for queries that meet these conditions:
- The query must have a predicate (ie. a WHERE clause) referencing a column with a potential missing index
- The optimizer must not consider the plan trivial"
The DMVs involved here are sys.dm_db_missing_index_details, sys.dm_db_missing_index_groups, sys.dm_db_missing_index_group_stats.
__________________________________________________________________________________
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]
August 27, 2008 at 11:03 am
Marios Philippopoulos (8/27/2008)
"At present, the missing index information is generated only for queries that meet these conditions:
- The query must have a predicate (ie. a WHERE clause) referencing a column with a potential missing index
- The optimizer must not consider the plan trivial"
If anyone would like to test this, I'd be very interested in the result. It seems pretty limiting as a feature.
__________________________________________________________________________________
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]
August 27, 2008 at 11:38 am
Marios Philippopoulos (8/27/2008)
I read it in an article on SQL Server magazine, April 2007, by Kalen Delaney: "use Missing-Index Groups for Query Tuning". I quote:"At present, the missing index information is generated only for queries that meet these conditions:
- The query must have a predicate (ie. a WHERE clause) referencing a column with a potential missing index
- The optimizer must not consider the plan trivial"
Interesting, but I'm going to disagree with her on the first point.
The following test was done on SQL Server 2005 Developer edition, SP2
CREATE TABLE TestingMissingIndexes (
ID INT,
SomeString CHAR(6),
RandomDate DATETIME
)
GO
;WITH DataPopulate (RowNo, Strng, ADate) AS (
SELECT 1 AS RowNo, CAST('ABC' AS CHAR(6)) as Strng,
DATEADD(dd, FLOOR(RAND()*75454),'1753/01/01')
UNION ALL
SELECT rowNo+1, CAST(char(65+FLOOR(RAND(rowNo*56412)*4)) + char(65+FLOOR(RAND(rowNo*8145)*4)) + char(65+FLOOR(RAND(rowNo*9852)*4)) AS CHAR(6)) as Strng,
DATEADD(dd, FLOOR(RAND(RowNo*96322)*85454),'1753/01/01')
FROM DataPopulate WHERE RowNo<20000
)
INSERT INTO TestingMissingIndexes
SELECT * FROM DataPopulate
OPTION (MAXRECURSION 20000)
GO
CREATE CLUSTERED INDEX idx_TestingMissingIndexes_ID on TestingMissingIndexes (ID)
GO
CREATE TABLE ReferencingTable (
SomeString Char(6) NOT NULL PRIMARY KEY,
AnotherString Char(6)
)
Insert into ReferencingTable (SomeString, AnotherString)
SELECT distinct SomeString, LTRIM(Reverse(SomeString)) FROM TestingMissingIndexes
-- Restart SQL Instance here. --
-- There is no index on the table TestingMissingIndexes to support the join.
SELECT mi.ID, mi.SomeString, ref.AnotherString FROM
TestingMissingIndexes mi INNER JOIN ReferencingTable ref ON mi.SomeString = ref.SomeString
WHERE ref.AnotherString = 'ABB'
-- Query the missing indexes DMV
select object_name(object_id) as TableName, equality_columns, included_columns from sys.dm_db_missing_index_details
Note that there is no where clause predicate referencing the TestingMissingIndexes table, only a join. On my server, the query against the missing indexes DMV returns the following:
TableName equality_columns included_columns
TestingMissingIndexes [SomeString] [ID]
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
August 27, 2008 at 11:42 am
Thanks Gail. I was just about to start testing this too because I believe I have seen it pick up missing indexes without having anything in the where clause as well. Saved me time... : )
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
August 27, 2008 at 11:54 am
GilaMonster (8/27/2008)
The following test was done on SQL Server 2005 Developer edition, SP2
...
Thanks for doing the test, I got the same results running it on my machine, version 9.0.3054 Dev Ed.
Well, that decides it then, she may have meant something else, and, in any case, it serves to remind me to never take anything at face value, even from the experts. 🙂
So join columns are taken into account. That restores some of my faith to the DMVs.
__________________________________________________________________________________
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]
August 27, 2008 at 2:17 pm
The problem is probably the common misuse of "ie", with "eg" being what was intended. After all, "FROM" is a predicate, just like "WHERE", which would make the statement make sense, if "eg" were used instead of "ie".
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply