find missing index or wanted MS SQL index from reading execution plan

Dear all,

any good article on how to find the best missing index of a MS SQL query from/by reading execution plan of that slowest query ?
LVL 1
marrowyungSenior Technical architecture (Data)Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

arnoldCommented:
looking at the query items under where if you are using joins the join on items should be indexed.
Indexes are/could be used to prevent duplicate entries.
Statistics are also .........

Your best bet is to use tuning advisor to capture a trace of queries and then it will suggest on indexes, and statistics that could improve the responsiveness  to your query/s
marrowyungSenior Technical architecture (Data)Author Commented:
Arnold,

"Your best bet is to use tuning advisor to capture a trace of queries and then it will suggest on indexes, and statistics that could improve the responsiveness  to your query/s "

but the tuning advisor always suggest the index I have already done.

so any step by step on how to:
1)create that kind of trace?
2) use advisor for that?

"looking at the query items under where if you are using joins the join on items should be indexed.
 Indexes are/could be used to prevent duplicate entries.
 Statistics are also ........."

you mean all column in the join should be indexed so that join will be faster ?

" Indexes are/could be used to prevent duplicate entries."

any link on how it works?

"Statistics are also .........""

you mean make sure index statistic are updated?
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

marrowyungSenior Technical architecture (Data)Author Commented:
have you try to use this script before to find out missing index and is it accurate ? I just tried to use this to verify my index:

SELECT 
statement AS [database.scheme.table],
column_id , column_name, column_usage, 
migs.user_seeks, migs.user_scans, 
migs.last_user_seek, migs.avg_total_user_cost,
migs.avg_user_impact
FROM sys.dm_db_missing_index_details AS mid
CROSS APPLY sys.dm_db_missing_index_columns (mid.index_handle)
INNER JOIN sys.dm_db_missing_index_groups AS mig 
ON mig.index_handle = mid.index_handle
INNER JOIN sys.dm_db_missing_index_group_stats  AS migs 
ON mig.index_group_handle=migs.group_handle
ORDER BY mig.index_group_handle, mig.index_handle, column_id
GO

Open in new window

arnoldCommented:
I have not used that.

If this is a custom application that you want to enhance, make sure to check with the vendor.  If they issue updates, your added indexes/statistics could prevent their schema changes if any.

To capture a trace of the SQL server, you would use SQL server Profiler.
You would specify the Database/s you want captured. and the duration of the capture. note the more and the longer the more space will be necessary. You can should limit the size of the trace file.

Once you have the trace, using the tunning advisor with the trace as the input to see what the recommendations are.

Often the indexes suggested are likely not the same as you have.
you may have an index on column1 unique the suggested index could be a combination of column1, column4, column5.  The only time I've seen an existing index included in the suggestion was when the index type is lets not clustered and the suggestion is for a clustered index.


Yes, anything used in a comparison should be indexed.
If column1 and column4 are always part of a comparison in either a where clause or on join, each should have its own index.  Combining the two in an index depending on the type could be an exclusive i.e. column1 and column4 can only appear once unique..

if you allow column1 and column4 of the same data to appear multiple times..........
marrowyungSenior Technical architecture (Data)Author Commented:
any good books suggestion on index tuning only so that I can highly focus on it ?

I have this one on my list: http://www.amazon.com/gp/product/1430237414?psc=1&redirect=true&ref_=ox_sc_sfl_title_4&smid=ATVPDKIKX0DER

any best buy you can suggest ? from which mostly like one books for everything from beginner/intermedia to expert ?

"If they issue updates, your added indexes/statistics could prevent their schema changes if any."

why can prevent their schema change by adding index ? or they simply say they don't support it anymore, right?

"Often the indexes suggested are likely not the same as you have.
 you may have an index on column1 unique the suggested index could be a combination of column1, column4, column5.  The only time I've seen an existing index included in the suggestion was when the index type is lets not clustered and the suggestion is for a clustered index."

so you are talking about your tried that many time and  the tuning advisor will not give you something already there, right? must be some change, right?

"you may have an index on column1 unique the suggested index could be a combination of column1, column4, column5.  "

I don't understand this one, please specify again.

"Combining the two in an index depending on the type could be an exclusive i.e. column1 and column4 can only appear once unique..
"

I don't understand that, any example ?

you mean index on (column1, column 4) must only exist once only  and not later on include (column 1, column 2, column4) so 1and 4 define more than once in other index on the same table ?
arnoldCommented:
Let's say you have frequently run query that includes where column1=value and column2=value2 and column3=value3

You could have individual column indexes for each, creating a single index that is based on the three columns combined .......

Exclusive/unique meaning the column1,column2 values can only exist once in the table I.e last name, first name can be such an example this way you trying to limit one record per individual (not most accurate as there are common names, some might include middle initial, dob)......


There are several resources online discussing the criteria, consideration, implications and impact of adding indexes.
I.e the trade off is higher storage and instead of improving read out pergormance, may impact/decrease the write performance.
Scott PletcherSenior DBACommented:
Yes, anything used in a comparison should be indexed.
If column1 and column4 are always part of a comparison in either a where clause or on join, each should have its own index.

That's just not true.  It's much more complex than that.  And, yet again, you cannot accurately make index determinations without looking at least at missing index stats and index usage stats, period.
arnoldCommented:
I did include the caveat that at times adding the index could have adverse impact.........
Scott PletcherSenior DBACommented:
But the index(es) shouldn't be created in the first place unless you expect it(them) to be used, and used frequently enough to justify the cost of creating it(them).  The only way to reasonably determine that ahead of time is index stats, period.  Just because something appears in a WHERE clause of JOIN does not mean SQL will use an index on that column.
marrowyungSenior Technical architecture (Data)Author Commented:
Arnold,

are you Arnold of http://sqlperformance.com/author/abertrand? 

I respect you all and I am reading all your article one by one!!

I am reading this too:
http://www.sqlskills.com/blogs/paul/indexes-from-every-angle-how-can-you-tell-if-an-index-is-being-used/

and I am reaching this :http://sqlperformance.com/2013/06/t-sql-queries/missing-index

The article is very good but after reading this I think about the sequence of the logic,  I have a big confuse that the script is going to return the use of indexes being use associate with the cache plan and the object the index built against with, however the objective of this articles is to show us DO NOT CREATE INDEX even suggested by execution plan! so without creating the index, how can I see that from your script and how can I hold the creation of these indexes AS I ALREADY CREATE IT.

agree?
marrowyungSenior Technical architecture (Data)Author Commented:
but I think the good thing to do to verify that is once an index has been added, the execution plan will be much simpler, agree?

then it is a good index to add !
arnoldCommented:
Marrowjung,

The author of the article you referenced is, Aaron Bertrand.

not sure the execution plan gets simplified by an addition of an index or that is the reason.  The addition of an index deals with improving the execution.


Scottpletcher  made interesting points, the stats point to data.  Using tuning advisor after using profiler to capture a sample of queries,
marrowyungSenior Technical architecture (Data)Author Commented:
Arnold,

"You could have individual column indexes for each, creating a single index that is based on the three columns combined ......."

this means I can't include all column in one index ? why ? I think it is better as it make index space take less!

is it because of this statement you said before?

" Combining the two in an index depending on the type could be an exclusive i.e. column1 and column4 can only appear once unique..

 if you allow column1 and column4 of the same data to appear multiple times.........."

can't understand this, any example ?

ScottPletcher,

"But the index(es) shouldn't be created in the first place unless you expect it(them) to be used, and used frequently enough to justify the cost of creating it(them). "

excellent, agree ! if more write than read! if very less record only, ignore the creation of index, agree?

but of course we will need to review the use of index from time to time to make sure that that index really has to being used !

"The only way to reasonably determine that ahead of time is index stats, period.  Just because something appears in a WHERE clause of JOIN does not mean SQL will use an index on that column. "

you mean index usage statistics ?
arnoldCommented:
The suggestion using the same index might be related to a type clustered versus non-clustered as you have it defined.

your table is
userid,first name, last name, middle initial, dob, etc.
userID is indexed as primary key
you often would include a non-unique index on first name, last name to allow searches based on those criteria.
you can add a unique index lastname_firstName_MI defined as last name, first name. middle initial means there can only one user with a specific last name, first name, middle initial.
So you get a call from John A Smith. The next day John B Smith calls. On Thursday, calls john A Smith, with a different DOB, but the unique index (lastname_firstname_MI) will reject the entry as a duplicate.
a unique index lastname, firstname, mi, DOB will be more likely to keep the data unique with a lower probability of overlap where all four columns must be identical.

an index of all columns will only be consulted when all columns are part of the criteria.  if they are not, the index is likely not consulted.  Indexes have to be written to with every insert/update/delete transaction.  Having a four column table with all possible column combination variations as indexes lets say non unique, would create huge overhead when any record in this table changes through addition, update or deletion,
As each index has to be updated.
marrowyungSenior Technical architecture (Data)Author Commented:
" Indexes have to be written to with every insert/update/delete transaction.  Having a four column table with all possible column combination variations as indexes lets say non unique, would create huge overhead when any record in this table changes through addition, update or deletion,
 As each index has to be updated. "

yeah, I knew, IO contention later on could be. too much update etc.

"ou can add a unique index lastname_firstName_MI defined as last name, first name. middle initial means there can only one user with a specific last name, first name, middle initial.
 So you get a call from John A Smith. The next day John B Smith calls. On Thursday, calls john A Smith, with a different DOB, but the unique index (lastname_firstname_MI) will reject the entry as a duplicate.
 a unique index lastname, firstname, mi, DOB will be more likely to keep the data unique with a lower probability of overlap where all four columns must be identical."

so unique is better than combine all index into one so save space? how many unique index we can define BTW ? unique index seems the best in non clustered index if we have the combine of field which unique identify a row or record for us, right?

so cover index / unique index is better? cover index has to cover the column in the select statement too, so include a lot of information and the size of index will be larger.

I read this one: https://www.mssqltips.com/sqlservertip/1449/avoid-index-redundancy-in-sql-server-tables/, this one said combine index is good.

" if you allow column1 and column4 of the same data to appear multiple times.........."

still can't understand this, any example ?
arnoldCommented:
Consider column1 is first name and column4 is last name.

The type of index to use is dictated by the business process/data scheme you need.
One should use validation on the entry side, but one should also setup the db to enforce to a point the business process,data integrity. Just in case there is an error in the coding for the ...........

Unique is there to prevent entry of duplicate records as the business process requires.
marrowyungSenior Technical architecture (Data)Author Commented:
"The type of index to use is dictated by the business process/data scheme you need.
 One should use validation on the entry side, but one should also setup the db to enforce to a point the business process,data integrity. Just in case there is an error in the coding for the ..........."

means there are no rule for that, so only cover index can be consider I think as it is type of non clustered index we can make use of !

"but one should also setup the db to enforce to a point the business process,data integrity."

you mean by store procedure to enforce process business process and to ensure data integrity ?
I think the application can already do this ? but we are talking about index here and I am not sure why this statement related.


"Yes, anything used in a comparison should be indexed.
 If column1 and column4 are always part of a comparison in either a where clause or on join, each should have its own index.  Combining the two in an index depending on the type could be an exclusive i.e. column1 and column4 can only appear once unique..

 if you allow column1 and column4 of the same data to appear multiple times.......... "

but is that mean index shouldn't be combine together as one ? like what this said: https://www.mssqltips.com/sqlservertip/1449/avoid-index-redundancy-in-sql-server-tables/  ?

"Unique is there to prevent entry of duplicate records as the business process requires. "

so the way you describe about unique index here is not about optimize the speed of query but make sure that no record can be duplicate? why don't make use of table constraint to make sure that ?
arnoldCommented:
Indexes, constraints, triggers are DB level enforcers to maintain data integrity and Business structure.

i.e. you have parent/child records you would use triggers/constraints to prevent the deletion of a paren while there are child records.
Alternatively, if you allow parent deletes, you would use a cascading trigger to delete the child records bedore the parent record is deleted.

We can discuss theories/abstract ......

What is the issue that you are actually having?
Scott Covered it also depends on when you are contemplating these. At time of design/atchitecure of the DB or after it has been in use?  Difference deals with when one designs/structures the DB the definition of what will be retrieved (reports) are one things, as times goes on, additional reports/criteria is added not initially .....
To improve performance for  those queries, it is possible additional index/statistics need to be added.

A unique index server two purposes:
1) like any other index it helps the execution plan. it will scan a subset of rows covered by the query versus all rows
2) the unique variant is to prevent duplicate records as defined by the index.
Scott PletcherSenior DBACommented:
When tuning indexes, the first, and absolutely critical thing to do, is to get the best clustered index on every table.  Therefore, unless and until you've used SQL stats and/or a representative query load to determine the best clustered index, doing anything else, including other index(es), is a basically a waste of time and resources.

Only after you create the best clustered index on a table, do you then go back and analyze the need for nonclustered index(es) on that table.
marrowyungSenior Technical architecture (Data)Author Commented:
ScottPletcher,

very good statement ! tks, because clustered index is touching the real data page in the left nodes so it is sure it is the first choice?

but clustered index can have only one and I read that if the where clause has a > or M sign, which means the query is going to return data in an order, then clustered index should be on that column, right? once it is defined, then we have no choice to add only non-clustered index.

just like what this links said : https://www.mssqltips.com/sqlservertip/1206/understanding-sql-server-indexing/ ?

Arnold,

"A unique index server two purposes:
 1) like any other index it helps the execution plan. it will scan a subset of rows covered by the query versus all rows
 2) the unique variant is to prevent duplicate records as defined by the index. "

yeah it does help anyway. tks.

"To improve performance for  those queries, it is possible additional index/statistics need to be added"

yeah, which can be add later on.
Scott PletcherSenior DBACommented:
And I read that if the where clause has a > or M sign, which means the query is going to return data in an order, then clustered index should be on that column, right?

Sigh.  No.  I give up.  No super-simple set of generic rules can ever properly guide you to the best index(es).  Such simplistic rules exist may exist, but they do not accurately work.  The only valid method is to use index stats.  If the table already exists, you can use stats from SQL itself.  If you're designing a new table, you can run a sample query load.
marrowyungSenior Technical architecture (Data)Author Commented:
please relax and I don't means any offend to your knowledge,, I don't think anyone can be very good on index like you.

I am checking out this books on index: http://www.amazon.com/gp/product/1430237414?psc=1&redirect=true&ref_=ox_sc_sfl_title_4&smid=ATVPDKIKX0DER

and I am not sure if that book is good and what else you can suggest me to read ? like focus more on index stats ? the index stats here means statistic, right?

"The only valid method is to use index stats.  "

this statement already a good hints, please share more link/resource on index stats and let me study myself!

nice to hear all coaching from you all.
marrowyungSenior Technical architecture (Data)Author Commented:
ScottPletcher,

I think you are talking about sys.dm_db_missing_index_group_stats.

so this kind of script is one tools to double check if the index has been used or not:

SELECT 
statement AS [database.scheme.table],
column_id , column_name, column_usage, 
migs.user_seeks, migs.user_scans, 
migs.last_user_seek, migs.avg_total_user_cost,
migs.avg_user_impact
FROM sys.dm_db_missing_index_details AS mid
CROSS APPLY sys.dm_db_missing_index_columns (mid.index_handle)
INNER JOIN sys.dm_db_missing_index_groups AS mig 
ON mig.index_handle = mid.index_handle
INNER JOIN sys.dm_db_missing_index_group_stats  AS migs 
ON mig.index_group_handle=migs.group_handle
ORDER BY mig.index_group_handle, mig.index_handle, column_id
GO

Open in new window

arnoldCommented:
Can you provide perspective from which point you are approaching your question
Are you tasked with the DB design of a new application, and having architect the DB, need to setup ........
Or do you have inherited/use an application that has a database as its backend and there are raised issues if it being slower, less responsive and you are trying to determine whether that can because of a missing not ... Index.
marrowyungSenior Technical architecture (Data)Author Commented:
"Can you provide perspective from which point you are approaching your question
 Are you tasked with the DB design of a new application, and having architect the DB, need to setup ........"

which regional of the world you are in? this is your working time already ?

my point of view now is I saw too much problem like this BEFORE and I try to prepare tools and knowledge on this so that I can have the RIGTH direction and tools to use when I see this kind of thing .

so I need help on what approach I need to take at the first place when I experience problem again and I also focus on how to read execution plan on a quick and accurate way ! the showplan_all can bring me read what index it use easily too instead of the graphical execution plan.

I read articles tells that the adding of index can also solve the  blocking/locking problem as it let's the process/queries blockings finish ASAP and then the blocking problem can be gone by adding index for the blocker !

Much better approach later on will be normalize the table further for that if locking and blocking keep coming but this will give more JOIN operation, which will use more CPU resource. e.g. 4-5 joins can already indicate de-normalization process has to start !

and finally I will learn some more parallel execution thing and make use of all CPU threading. which Is very powerful !

All kind of schema always design by developer by not by me and this kind of UML/data modelling will be highly controlled by business requirement, which I can't modify.

so BEFORE a big change CAN take place, focusing on index can be a good choice ! agree? As WHAT ELSE I CAN DO TO SPEED THAT UP AT THIS STAGE ?

then later on followed by schema redesign later. DEVELOPER here always not tell DBA what has been changed at all! then what can I do it speed that up first ? index !!

if you tell them how to write query, like no RBAR programming and use set-based, they might not follow and don't even know how to write no matter how many time you told them, then index still one of the good choice which I can work independently without asking developer.

but this can be done before I add an index as the change of coding can take a lot of time and once they change the code, then I start to review index and drop unused index / combine index, that's why I keep talking about the combine of index does help ! or ask you why combine of index doesn't work !

a lot of developer will also look at you and wait until you fix/add the index for them without REWRITE any code for them.

here DBA has to study everything.. they has to do, from time to time, a lot of SQL programming as web developer don't know how to do it but SQL developer. web programming is about HOW to do works but SQL developer focus on WHAT to do.

So DBA here actually is the DB architecture ! they have to design too !

So you can see I ask in a lot of areas.

my next areas will be on SQL replication slowness, so if you know that please help ! I will ask 2-3 weeks later. let me get more information on the existing replication problem first, I have a question on it already but closed.
marrowyungSenior Technical architecture (Data)Author Commented:
one thing, I heard that the order of index also does a lot of matter, please share some URL/links on this area with example !
 
is it really true ? on the order of join operation only?
arnoldCommented:
This forum IS Volunteer based.

So you are looking at the issue as a DBA; one who lacks control over anything, but from whom much is expected.
Adding just on the basis of the stats is a dangerous thing under these circumstances without consulting the db design and developers.  An added item could prevent a transition implementation in a subsequent release.

There are issues that an additional index, stats, etc, can not resolve.

Some issues are scaling related.
Understanding the existing structure, application functionally, workflow .
Under the scenario as you've laid out, you could use the stats to bring the matter to the two other groups for consideration.

The example, db/app design used a working table which was primarily accessed to retrieve active, suspended,pending records.
From the beginig the setup was extremely fast, but as time went buy, the response started to degrade in geometric progression.

The reason, when items were resolved, closed They remained in the working table. I do not believe there is anything you can add in the form of an index, etc. that could resolve this. The remedy, was to remove resolved/completed records from the working table.
But to make sure prior records are still available. New table to preserve the records, a process by dev to handle the movement of records between/among tables as well as looking for records in both when requested.
.....

I've not gone through the article you keep referencing.  For enhancing your base of information, Scott has covered a significant importance.
marrowyungSenior Technical architecture (Data)Author Commented:
"So you are looking at the issue as a DBA; one who lacks control over anything, but from whom much is expected."

no, they are not lack control over anything, in this country, they expected to control everything and they have sa account password from time to time.

they can/expected to pinpoint troublemaking query and tells why, they can kill that query by spid and make the DB back to normal ASAP. I usually focus on this.

" An added item could prevent a transition implementation in a subsequent release.
"
I don't understand why .

"The example, db/app design used a working table which was primarily accessed to retrieve active, suspended,pending records.
 From the beginig the setup was extremely fast, but as time went buy, the response started to degrade in geometric progression."

yeah, one of the example is it use cursor/while loop, which is still fast when no. of record is small!

"
 The reason, when items were resolved, closed They remained in the working table. I do not believe there is anything you can add in the form of an index, etc. that could resolve this. The remedy, was to remove resolved/completed records from the working table."

oh you mean when this happen the working table is too large and no index on this working table/ derived table so it is slow and usually we have the concept of live and no live table for this purpose, for compliance requirement, like SOX, we move all data older than 7 years to no live table as we don't usually need this.

there will be a archive schedule.

from time to time we need housekeeping process to refine the whole operation as 1-2 years later on we find the system is not what we expected and the whole thing is slow, so we redesign the processes.
arnoldCommented:
When a slow down is seen on performance of any application the blame is first goes to the hardware/os, Then up the ladder to the DBA, then the developer, ......so on and so forth


If you add an index to improve performance that is not accounted for by the DB architect, and developers, when the DB schema changes and they try to issue an alter table to adjust for the changes, your index could prevent it leading to a failure of the DB schema update and this can get worse and worse.  

Before one can alter table one has to drop indexes/stats/constraints on columns that are being altered, i.e. column1 used to have a length of 20 now needs to have a length of 64. If there is an index/statistics defined on this column the alter table tablename column1 will fail because of the index/statistical entry you added, it has to be dropped first and then reintroduced.

Maintain data in a live (actively accessed) db/table for 7 years is too long, creating archive/aging.
the issue with aging is the overhead for the transition.
the implementation of the transition is the ... dealing with offloading data that is not being actively accessed, while providing the ability to access it transparently .....

This discussion can go forever on dealing with the every day possibilities.
1-2 years is likely too long, your use of Scott's suggestions, and the stats/Aaron's Article provide a basis to point out were the issue might be..

I've previously used the profiler to capture data during various times and then used the tunning advisor to analyze and provide recommendations.

I'll check out the query you posted to see ..

Accuracy as compared to what? in terms of being correct in the estimation that the missing index will solve some of the performance issues?
interesting data,
marrowyungSenior Technical architecture (Data)Author Commented:
"When a slow down is seen on performance of any application the blame is first goes to the hardware/os, Then up the ladder to the DBA, then the developer, ......so on and so forth"

This is diff from company to company. but good to see that happen in this way in your country. the developer and DBA will work at the same time.

"If you add an index to improve performance that is not accounted for by the DB architect, and developers, when the DB schema changes and they try to issue an alter table to adjust for the changes, your index could prevent it leading to a failure of the DB schema update and this can get worse and worse.  "

this should have an error message on it..right? and both DBA and developer can come up together to discuss about that.

I think that's one of the good reason for DBA to suggest the index and developer can make decision on this and if an index create trouble for the schema change, then developer will know it specially when they create the index by THEMSELVES.

so should not be that trouble, right?

"Before one can alter table one has to drop indexes/stats/constraints on columns that are being altered, i.e. column1 used to have a length of 20 now needs to have a length of 64. If there is an index/statistics defined on this column the alter table tablename column1 will fail because of the index/statistical entry you added, it has to be dropped first and then reintroduced."

if developer create the index they will drop it, so it just a matter of who create/drop it.

"
 Maintain data in a live (actively accessed) db/table for 7 years is too long, creating archive/aging.
 the issue with aging is the overhead for the transition.
 the implementation of the transition is the ... dealing with offloading data that is not being actively accessed, while providing the ability to access it transparently ....."

developer's work or vendor application's works a the business logic define it and it also depends on compliance, like the DB backup. e.g. how long it keeps.

"1-2 years is likely too long,"

some company really do work like that. gods know why !

"I've previously used the profiler to capture data during various times and then used the tunning advisor to analyze and provide recommendations."

I will take this as the advice as I keep seeing expertists there talking about use SQl trace, any suggestion on what items should I select in SQL profiler to get all useful information for DTA to give good suggestion?

"Accuracy as compared to what? in terms of being correct in the estimation that the missing index will solve some of the performance issues?"

which topic I talked about accuracy ?
arnoldCommented:
An unaccounted for index that prevents the update to the database schema requires if it is not the first one ran a restore of the prior..
The db architect would need to be involved as well.(Scott without a doubt will point out that  the DB schema will be analyzed to build the ......)

As to accuracy, you asked at one point whether looking at missing index/stats was accurate.
Those stats are only there since the service started. An article in sqltipps I think suggests that you run /capture/record this information periodically if you want to have access over longer time.

Looking at how many users, last access, the last two columns  helps to provide info on whose basis decisions .....
arnoldCommented:
To your question on the profiler, you capture the workload on the DB of interest to you, the one with performance problems.

I.e. You capture 20 minutes worth or 500mb whichever is met first on db1 during peak usage.
What the profiler will have is e dry single request against this database.
The tuning advisor is then uses this workload to replay and analyze.......... The tuning advisor is done at off peak hours.
marrowyungSenior Technical architecture (Data)Author Commented:
"As to accuracy, you asked at one point whether looking at missing index/stats was accurate.
 Those stats are only there since the service started. An article in sqltipps I think suggests that you run /capture/record this information periodically if you want to have access over longer time."

sth like put it in an archive table so that we can analysis later on?

"An unaccounted for index that prevents the update to the database schema requires if it is not the first one ran a restore of the prior.."

sorry don't understand this .

"
 I.e. You capture 20 minutes worth or 500mb whichever is met first on db1 during peak usage.
 What the profiler will have is e dry single request against this database."

I knew but what I want to know is what option I have to "tick" to capture enough information for the advisor to give me enough advisor on index and anything else.

profiler option
arnoldCommented:
For the profiler, you would select the DB and all categories.

Test it out, on a very active database, the 500MB could be amassed quickly on another, the 20 minutes might not be enough..


If you add an index on column2.
Your DB Architect has a schema alteration on column2 increasing its size as well as adding columns.
The SQL script drops the indexes they created leaving yours in place. Depending on sequencing.
I.e, do they have different SQL scripts each making their own changes in a transaction....
So changes have already been made and commited.
Now this transaction runs and fails because column2 has an index.
The db structure was already altered meaning the upgrade failed, the new application can not work, because not all changes were made, the old application will generate errors because some tables/columns that it is not coded for are not included in inserts.....

No doubt the db architect/you and the developer will likely run an get the index son the db before doing changes, or use redgate tool to perform the DB comparison and generating schema update scripts. The dilemma is whether this will be done against the production server where you made the change, or the comparison will be on the test server presuming they are "identical"....,,.,,

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
marrowyungSenior Technical architecture (Data)Author Commented:
"For the profiler, you would select the DB and all categories.

 Test it out, on a very active database, the 500MB could be amassed quickly on another, the 20 minutes might not be enough.."

you mean all these including audit ?

profiler
"I.e, do they have different SQL scripts each making their own changes in a transaction....
 So changes have already been made and commited.
 Now this transaction runs and fails because column2 has an index.
 The db structure was already altered meaning the upgrade failed, the new application can not work, because not all changes were made, the old application will generate errors because some tables/columns that it is not coded for are not included in inserts.....
"

it seems that I don't add index for them but suggest, give them the script and let them decide that is much better, agree? they have to know what they have added. so they know what added has been added and I just said SQL optimizer are looking for this type of combination of column to be indexed, so here you go........

"The dilemma is whether this will be done against the production server where you made the change, or the comparison will be on the test server presuming they are "identical"....,,.,,

will be on test server first, then staging and production server of course.

"No doubt the db architect/you and the developer will likely run an get the index son the db before doing changes, or use redgate tool to perform the DB comparison and generating schema update scripts.

oh you use a lot of redgate tools ? SSDT is another good choice tool!
arnoldCommented:
Any tool used is .. Redgate just stuck in my head as a reference.

At this point you are trying to familiarize yourself with the profiler, getting .....
marrowyungSenior Technical architecture (Data)Author Commented:
yeah, tk.s I am looking for script as tools.
marrowyungSenior Technical architecture (Data)Author Commented:
so I tick all option including SQL audit for DTA to tune it for me ?
marrowyungSenior Technical architecture (Data)Author Commented:
"For the profiler, you would select the DB and all categories."

so just everything including audit ?
arnoldCommented:
Make note that profilers resource consuming, so test it the process to get familiar if you can pull the data from a system not performing services to minimize impact on production......


Then use the captured transactions inthe tuning advisor to see what it recommends......
marrowyungSenior Technical architecture (Data)Author Commented:
yes, I agree, in US now training for all day long, hard to touch EE.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.