Prev: Need Assistance With Defining Column Value in a View
Next: urgent date format problem help needed....
From: tshad on 26 Mar 2010 00:33
I tried to do the samples in:

http://blogs.msdn.com/queryoptteam/archive/2006/04/06/570176.aspx

And they worked fine.

index_handle database_id object_id equality_columns inequality_columns
included_columns statement

------------ ----------- ----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------13 2 85575343 [col1] NULL [col2], [col3] [tempdb].[dbo].[t]15 2 85575343 [col2] NULL NULL [tempdb].[dbo].[t](2 row(s) affected)index_group_handle index_handle------------------ ------------14 1316 15(2 row(s) affected)group_handle unique_compiles user_seeks user_scans last_user_seeklast_user_scan avg_total_user_cost avg_user_impact system_seeks system_scanslast_system_seek last_system_scan avg_total_system_cost avg_system_impact------------ -------------------- -------------------- -------------------- ----------------------- ----------------------- ---------------------- ---------------------- -------------------- -------------------- ----------------------- ----------------------- ---------------------- ----------------------14 2 5 0 2010-03-25 21:27:41.357 NULL 1.26916677071489 50.57 0 0 NULL NULL 0016 2 5 0 2010-03-25 21:27:41.357 NULL 1.26916677071489 46.49 0 0 NULL NULL 00(2 row(s) affected)I got the same results they got.But I also tried to run this query fromhttp://blogs.msdn.com/bartd/archive/2007/07/19/are-you-using-sql-s-missing-index-dmvs.aspx: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_columnsIS NOT NULL THEN ',' ELSE '' END + ISNULL (mid.inequality_columns, '') + ')' + ISNULL (' INCLUDE (' + mid.included_columns + ')', '') AScreate_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 migs ON migs.group_handle =mig.index_group_handleINNER JOIN sys.dm_db_missing_index_details mid ON 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) DESCI got no results.Why is that?Thanks,Tom

From: sloan on 26 Mar 2010 04:27
That's great.

Are you asking a question?

Did you change the subject line of your post? If you did, then most
newsreaders will "disassociate" it with the original post, aka, orhpan the
post and make it look like a totally new thread.
Aka, the rule of thumb is "keep the subject line the same".
.............


"tshad" <tfs(a)dslextreme.com> wrote in message
news:erkVF2JzKHA.6140(a)TK2MSFTNGP05.phx.gbl...
>I tried to do the samples in:
>
> http://blogs.msdn.com/queryoptteam/archive/2006/04/06/570176.aspx
>
> And they worked fine.
>
> index_handle database_id object_id equality_columns inequality_columns
> included_columns statement
>
> ------------ ----------- ----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------13 2 85575343 [col1] NULL [col2], [col3][tempdb].[dbo].[t]15 2 85575343 [col2] NULL NULL [tempdb].[dbo].[t](2 row(s)affected)index_group_handle index_handle------------------ ------------141316 15(2 row(s) affected)group_handle unique_compiles user_seeks user_scanslast_user_seeklast_user_scan avg_total_user_cost avg_user_impactsystem_seeks system_scanslast_system_seek last_system_scanavg_total_system_costavg_system_impact------------ -------------------- -------------------- -------------------- ----------------------- ----------------------- ---------------------- ---------------------- -------------------- -------------------- ----------------------- ----------------------- ---------------------- ----------------------14 2 5 0 2010-03-25 21:27:41.357 NULL 1.2691667707148950.57 0 0 NULL NULL 0016 2 5 0 2010-03-25 21:27:41.357 NULL 1.2691667707148946.49 0 0 NULL NULL 00(2 row(s) affected)I got the same results they got.ButI also tried to run this queryfromhttp://blogs.msdn.com/bartd/archive/2007/07/19/are-you-using-sql-s-missing-index-dmvs.aspx: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 NOTNULL AND mid.inequality_columnsIS NOT NULL THEN ',' ELSE '' END + ISNULL(mid.inequality_columns, '') + ')' + ISNULL (' INCLUDE (' +mid.included_columns + ')', '') AScreate_index_statement, migs.*,mid.database_id, mid.[object_id]FROM sys.dm_db_missing_index_groups migINNERJOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle=mig.index_group_handleINNER JOIN sys.dm_db_missing_index_details mid ONmig.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) DESCI got no results.Why isthat?Thanks,Tom>

From: tshad on 26 Mar 2010 12:20
I did ask a question but it looks like the text ran together. Not sure what
happened here. This was the first post.

I was trying some code in the first link and it seemed to work fine.

But when I took the code from the second post and got nothing - yet it did
seem to work at the office.

My question was - why was the Query bring back nothing?

The query was:
***************************************************************************************
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
*************************************************************************************

This should have brought back an index that the system thought I should be
using.

It does show it in example code from the 1st article.

Is there something I am missing to make this work?

Thanks,

Tom

"sloan" <sloan(a)ipass.net> wrote in message
news:%237rM64LzKHA.5288(a)TK2MSFTNGP05.phx.gbl...
> That's great.
>
> Are you asking a question?
>
> Did you change the subject line of your post? If you did, then most
> newsreaders will "disassociate" it with the original post, aka, orhpan the
> post and make it look like a totally new thread.
> Aka, the rule of thumb is "keep the subject line the same".
> ............
>
>
> "tshad" <tfs(a)dslextreme.com> wrote in message
> news:erkVF2JzKHA.6140(a)TK2MSFTNGP05.phx.gbl...
>>I tried to do the samples in:
>>
>> http://blogs.msdn.com/queryoptteam/archive/2006/04/06/570176.aspx
>>
>> And they worked fine.
>>
>> index_handle database_id object_id equality_columns inequality_columns
>> included_columns statement
>>
>> ------------ ----------- ----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>> ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>> ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>> ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------132 85575343 [col1] NULL [col2], [col3][tempdb].[dbo].[t]15 2 85575343 [col2]NULL NULL [tempdb].[dbo].[t](2 row(s)affected)index_group_handleindex_handle------------------ ------------141316 15(2 row(s)affected)group_handle unique_compiles user_seeksuser_scanslast_user_seeklast_user_scan avg_total_user_costavg_user_impactsystem_seeks system_scanslast_system_seeklast_system_scanavg_total_system_costavg_system_impact------------ -------------------- -------------------- -------------------- ----------------------- ----------------------- ---------------------- ---------------------- -------------------- -------------------- ----------------------- ----------------------- ---------------------- ----------------------14 2 5 0 2010-03-2521:27:41.357 NULL 1.2691667707148950.57 0 0 NULL NULL 0016 2 5 0 2010-03-2521:27:41.357 NULL 1.2691667707148946.49 0 0 NULL NULL 00(2 row(s) affected)Igot the same results they got.ButI also tried to run thisqueryfromhttp://blogs.msdn.com/bartd/archive/2007/07/19/are-you-using-sql-s-missing-index-dmvs.aspx:SELECT migs.avg_total_user_cost *(migs.avg_user_impact/ 100.0) *(migs.user_seeks + migs.user_scans) ASimprovement_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 ISNOTNULL AND mid.inequality_columnsIS NOT NULL THEN ',' ELSE '' END +ISNULL(mid.inequality_columns, '') + ')' + ISNULL (' INCLUDE ('+mid.included_columns + ')', '') AScreate_index_statement,migs.*,mid.database_id, mid.[object_id]FROM sys.dm_db_missing_index_groupsmigINNERJOIN sys.dm_db_missing_index_group_stats migs ONmigs.group_handle=mig.index_group_handleINNER JOINsys.dm_db_missing_index_details mid ONmig.index_handle=mid.index_handleWHERE migs.avg_total_user_cost *(migs.avg_user_impact /100.0) *(migs.user_seeks + migs.user_scans) >10ORDER BYmigs.avg_total_user_cost * migs.avg_user_impact *(migs.user_seeks+migs.user_scans) DESCI got no results.Why isthat?Thanks,Tom>>

From: Plamen Ratchev on 26 Mar 2010 12:40
The info the the DMVs is refresh on instance restart. Also, if you did not run any queries that access tables/columns
that could potentially have missing indexes there will be no data returned from the DVMs. Execute some queries that
simulate normal user workload and then rerun to check for missing indexes.

Also, notice the query has predicate to eliminate indexes which have low impact.

--
Plamen Ratchev
http://www.SQLStudio.com
From: tshad on 26 Mar 2010 13:36
I understand that that.

But when I did the 3 selects the author does I get the same results - which
shows an index is recommended. Directly after I run the other query which
should show me the same index using the same information. If I then run the
3 selects the author shows, it shows the same results.

Thanks,

Tom

"Plamen Ratchev" <Plamen(a)SQLStudio.com> wrote in message
news:u4qdncY0fNDhfjHWnZ2dnUVZ_j0wAAAA(a)speakeasy.net...
> The info the the DMVs is refresh on instance restart. Also, if you did not
> run any queries that access tables/columns that could potentially have
> missing indexes there will be no data returned from the DVMs. Execute some
> queries that simulate normal user workload and then rerun to check for
> missing indexes.
>
> Also, notice the query has predicate to eliminate indexes which have low
> impact.
>
> --
> Plamen Ratchev
> http://www.SQLStudio.com


 |  Next  |  Last
Pages: 1 2
Prev: Need Assistance With Defining Column Value in a View
Next: urgent date format problem help needed....