MMC Snap-In Doesn't allow Full-Text Index...

Yet if I pull up sql server under Computer Management it does and allows me
to configure it.
What's wrong here?
Kyle!
Are you registering the server with an administrative login which would give
you permissions?
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Kyle Jedrusiak" <kjedrusiak@.princetoninformation.com> wrote in message
news:O1qAC%23l1EHA.1404@.TK2MSFTNGP11.phx.gbl...
> Yet if I pull up sql server under Computer Management it does and allows
me
> to configure it.
> What's wrong here?
> Kyle!
>
|||The server registration is using the sa account and password.
Kyle!
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:%23r7x%234t1EHA.2716@.TK2MSFTNGP14.phx.gbl...
> Are you registering the server with an administrative login which would
> give
> you permissions?
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Kyle Jedrusiak" <kjedrusiak@.princetoninformation.com> wrote in message
> news:O1qAC%23l1EHA.1404@.TK2MSFTNGP11.phx.gbl...
> me
>
|||Also the sa is the dbo.
Kyle!
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:%23r7x%234t1EHA.2716@.TK2MSFTNGP14.phx.gbl...
> Are you registering the server with an administrative login which would
> give
> you permissions?
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Kyle Jedrusiak" <kjedrusiak@.princetoninformation.com> wrote in message
> news:O1qAC%23l1EHA.1404@.TK2MSFTNGP11.phx.gbl...
> me
>
sql

MMC Snap-In Doesn't allow Full-Text Index...

Yet if I pull up sql server under Computer Management it does and allows me
to configure it.
What's wrong here?
Kyle!Are you registering the server with an administrative login which would give
you permissions?
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Kyle Jedrusiak" <kjedrusiak@.princetoninformation.com> wrote in message
news:O1qAC%23l1EHA.1404@.TK2MSFTNGP11.phx.gbl...
> Yet if I pull up sql server under Computer Management it does and allows
me
> to configure it.
> What's wrong here?
> Kyle!
>|||The server registration is using the sa account and password.
Kyle!
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:%23r7x%234t1EHA.2716@.TK2MSFTNGP14.phx.gbl...
> Are you registering the server with an administrative login which would
> give
> you permissions?
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Kyle Jedrusiak" <kjedrusiak@.princetoninformation.com> wrote in message
> news:O1qAC%23l1EHA.1404@.TK2MSFTNGP11.phx.gbl...
>> Yet if I pull up sql server under Computer Management it does and allows
> me
>> to configure it.
>> What's wrong here?
>> Kyle!
>>
>|||Also the sa is the dbo.
Kyle!
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:%23r7x%234t1EHA.2716@.TK2MSFTNGP14.phx.gbl...
> Are you registering the server with an administrative login which would
> give
> you permissions?
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Kyle Jedrusiak" <kjedrusiak@.princetoninformation.com> wrote in message
> news:O1qAC%23l1EHA.1404@.TK2MSFTNGP11.phx.gbl...
>> Yet if I pull up sql server under Computer Management it does and allows
> me
>> to configure it.
>> What's wrong here?
>> Kyle!
>>
>

MMC Snap-In Doesn't allow Full-Text Index...

Yet if I pull up sql server under Computer Management it does and allows me
to configure it.
What's wrong here?
Kyle!Are you registering the server with an administrative login which would give
you permissions?
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Kyle Jedrusiak" <kjedrusiak@.princetoninformation.com> wrote in message
news:O1qAC%23l1EHA.1404@.TK2MSFTNGP11.phx.gbl...
> Yet if I pull up sql server under Computer Management it does and allows
me
> to configure it.
> What's wrong here?
> Kyle!
>|||The server registration is using the sa account and password.
Kyle!
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:%23r7x%234t1EHA.2716@.TK2MSFTNGP14.phx.gbl...
> Are you registering the server with an administrative login which would
> give
> you permissions?
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Kyle Jedrusiak" <kjedrusiak@.princetoninformation.com> wrote in message
> news:O1qAC%23l1EHA.1404@.TK2MSFTNGP11.phx.gbl...
> me
>|||Also the sa is the dbo.
Kyle!
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:%23r7x%234t1EHA.2716@.TK2MSFTNGP14.phx.gbl...
> Are you registering the server with an administrative login which would
> give
> you permissions?
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Kyle Jedrusiak" <kjedrusiak@.princetoninformation.com> wrote in message
> news:O1qAC%23l1EHA.1404@.TK2MSFTNGP11.phx.gbl...
> me
>

Missing index query help

When I was going through Kalens Query Tuning and Optimization book , she
provided the query below to find missing indices. Results are below.
select object_name(t1.object_id)
TblName,t2.user_seeks,t1.equality_columns,t1.inequality_columns,t1.included_columns
from
sys.dm_db_missing_index_details t1,sys.dm_db_missing_index_group_stats t2,
sys.dm_db_missing_index_groups t3
where database_id=db_id()
and t1.index_handle=t3.index_handle
and t2.group_handle=t3.index_group_handle
and object_name(object_id) = 'tableA'
order by 1 , 2 desc
Output :
TblName seeks Equality_cols Inequality_cols Included_cols
TableA 3609843 [Col1] NULL [Col2]
TableA 3434018 [Col2], [Col1] NULL NULL
TableA 703743 [Col1] [Col3] [Col2]
TableA 495032 [Col2], [Col1] [Col3] NULL
So how do I create these indices now ?
For the first entry, is it stating to create an index on col1 with Col2 as
included col ?
second entry, I guess it wants a covered index on Col2,Col1
For the 3rd and 4th entry I dont know what it wants us to create.
The 3rd entry has a column listed for each of the 3 column types namely
equality,inequality and included.
Thanks
Please help me figure this out.Hi Hassan,
See the BOL entry for sys.dm_db_missing_index_details:
"To convert the information returned by sys.dm_db_missing_index_details into
a CREATE INDEX statement, equality columns should be put before the
inequality columns, and together they should make the key of the index.
Included columns should be added to the CREATE INDEX statement using the
INCLUDE clause."
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Hassan" wrote:
> When I was going through Kalens Query Tuning and Optimization book , she
> provided the query below to find missing indices. Results are below.
> select object_name(t1.object_id)
> TblName,t2.user_seeks,t1.equality_columns,t1.inequality_columns,t1.included_columns
> from
> sys.dm_db_missing_index_details t1,sys.dm_db_missing_index_group_stats t2,
> sys.dm_db_missing_index_groups t3
> where database_id=db_id()
> and t1.index_handle=t3.index_handle
> and t2.group_handle=t3.index_group_handle
> and object_name(object_id) = 'tableA'
> order by 1 , 2 desc
> Output :
> TblName seeks Equality_cols Inequality_cols Included_cols
> TableA 3609843 [Col1] NULL [Col2]
> TableA 3434018 [Col2], [Col1] NULL NULL
> TableA 703743 [Col1] [Col3] [Col2]
> TableA 495032 [Col2], [Col1] [Col3] NULL
> So how do I create these indices now ?
> For the first entry, is it stating to create an index on col1 with Col2 as
> included col ?
> second entry, I guess it wants a covered index on Col2,Col1
> For the 3rd and 4th entry I dont know what it wants us to create.
> The 3rd entry has a column listed for each of the 3 column types namely
> equality,inequality and included.
> Thanks
> Please help me figure this out.
>|||By the way, you can also use the Database Engine Tuning Advisor for
recommendations for indexes on your database.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Ben Nevarez" wrote:
> Hi Hassan,
> See the BOL entry for sys.dm_db_missing_index_details:
> "To convert the information returned by sys.dm_db_missing_index_details into
> a CREATE INDEX statement, equality columns should be put before the
> inequality columns, and together they should make the key of the index.
> Included columns should be added to the CREATE INDEX statement using the
> INCLUDE clause."
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "Hassan" wrote:
> > When I was going through Kalens Query Tuning and Optimization book , she
> > provided the query below to find missing indices. Results are below.
> >
> > select object_name(t1.object_id)
> > TblName,t2.user_seeks,t1.equality_columns,t1.inequality_columns,t1.included_columns
> > from
> > sys.dm_db_missing_index_details t1,sys.dm_db_missing_index_group_stats t2,
> > sys.dm_db_missing_index_groups t3
> > where database_id=db_id()
> > and t1.index_handle=t3.index_handle
> > and t2.group_handle=t3.index_group_handle
> > and object_name(object_id) = 'tableA'
> > order by 1 , 2 desc
> >
> > Output :
> >
> > TblName seeks Equality_cols Inequality_cols Included_cols
> >
> > TableA 3609843 [Col1] NULL [Col2]
> > TableA 3434018 [Col2], [Col1] NULL NULL
> > TableA 703743 [Col1] [Col3] [Col2]
> > TableA 495032 [Col2], [Col1] [Col3] NULL
> >
> > So how do I create these indices now ?
> >
> > For the first entry, is it stating to create an index on col1 with Col2 as
> > included col ?
> > second entry, I guess it wants a covered index on Col2,Col1
> > For the 3rd and 4th entry I dont know what it wants us to create.
> >
> > The 3rd entry has a column listed for each of the 3 column types namely
> > equality,inequality and included.
> >
> > Thanks
> >
> > Please help me figure this out.
> >
> >|||Here is a query that can help do what you need. However, you need to test
the results. The column order may not be right so use your judgement.
SELECT sys.objects.name, (avg_total_user_cost * avg_user_impact) *
(user_seeks + user_scans) as Impact, 'CREATE INDEX YourName ON ' +
sys.objects.name + ' ( ' + mid.equality_columns + CASE WHEN
mid.inequality_columns IS NULL
THEN '' ELSE CASE WHEN mid.equality_columns IS NULL
THEN '' ELSE ',' END + mid.inequality_columns END + ' ) ' +
CASE WHEN mid.included_columns IS NULL
THEN '' ELSE 'INCLUDE (' + mid.included_columns + ')' END + ';'
AS CreateIndexStatement, mid.equality_columns, mid.inequality_columns,
mid.included_columns
FROM sys.dm_db_missing_index_group_stats AS migs
INNER JOIN sys.dm_db_missing_index_groups AS mig ON migs.group_handle =mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details AS mid ON mig.index_handle =mid.index_handle INNER JOIN sys.objects WITH (nolock) ON mid.object_id =sys.objects.object_id
WHERE (migs.group_handle IN
(SELECT TOP (5000) group_handle
FROM sys.dm_db_missing_index_group_stats
WITH (nolock)
ORDER BY (avg_total_user_cost * avg_user_impact)
* (user_seeks + user_scans) DESC)) and objectproperty(sys.objects.object_id,
'isusertable')=1 --and name = 'tblperson'
ORDER BY 2 DESC
Jason Massie
Web: http://statisticsio.com
RSS: http://statisticsio.com/Home/tabid/36/rssid/1/Default.aspx
"Hassan" <hassan@.test.com> wrote in message
news:e9KaGLMMIHA.748@.TK2MSFTNGP04.phx.gbl...
> When I was going through Kalens Query Tuning and Optimization book , she
> provided the query below to find missing indices. Results are below.
> select object_name(t1.object_id)
> TblName,t2.user_seeks,t1.equality_columns,t1.inequality_columns,t1.included_columns
> from
> sys.dm_db_missing_index_details t1,sys.dm_db_missing_index_group_stats t2,
> sys.dm_db_missing_index_groups t3
> where database_id=db_id()
> and t1.index_handle=t3.index_handle
> and t2.group_handle=t3.index_group_handle
> and object_name(object_id) = 'tableA'
> order by 1 , 2 desc
> Output :
> TblName seeks Equality_cols Inequality_cols Included_cols
> TableA 3609843 [Col1] NULL [Col2]
> TableA 3434018 [Col2], [Col1] NULL NULL
> TableA 703743 [Col1] [Col3] [Col2]
> TableA 495032 [Col2], [Col1] [Col3] NULL
> So how do I create these indices now ?
> For the first entry, is it stating to create an index on col1 with Col2 as
> included col ?
> second entry, I guess it wants a covered index on Col2,Col1
> For the 3rd and 4th entry I dont know what it wants us to create.
> The 3rd entry has a column listed for each of the 3 column types namely
> equality,inequality and included.
> Thanks
> Please help me figure this out.

Missing index query help

When I was going through Kalens Query Tuning and Optimization book , she
provided the query below to find missing indices. Results are below.
select object_name(t1.object_id)
TblName,t2.user_seeks,t1.equality_columns,t1.inequ ality_columns,t1.included_columns
from
sys.dm_db_missing_index_details t1,sys.dm_db_missing_index_group_stats t2,
sys.dm_db_missing_index_groups t3
where database_id=db_id()
and t1.index_handle=t3.index_handle
and t2.group_handle=t3.index_group_handle
and object_name(object_id) = 'tableA'
order by 1 , 2 desc
Output :
TblName seeks Equality_cols Inequality_cols Included_cols
TableA 3609843 [Col1] NULL [Col2]
TableA 3434018 [Col2], [Col1] NULL NULL
TableA 703743 [Col1] [Col3] [Col2]
TableA 495032 [Col2], [Col1] [Col3] NULL
So how do I create these indices now ?
For the first entry, is it stating to create an index on col1 with Col2 as
included col ?
second entry, I guess it wants a covered index on Col2,Col1
For the 3rd and 4th entry I dont know what it wants us to create.
The 3rd entry has a column listed for each of the 3 column types namely
equality,inequality and included.
Thanks
Please help me figure this out.
Hi Hassan,
See the BOL entry for sys.dm_db_missing_index_details:
"To convert the information returned by sys.dm_db_missing_index_details into
a CREATE INDEX statement, equality columns should be put before the
inequality columns, and together they should make the key of the index.
Included columns should be added to the CREATE INDEX statement using the
INCLUDE clause."
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Hassan" wrote:

> When I was going through Kalens Query Tuning and Optimization book , she
> provided the query below to find missing indices. Results are below.
> select object_name(t1.object_id)
> TblName,t2.user_seeks,t1.equality_columns,t1.inequ ality_columns,t1.included_columns
> from
> sys.dm_db_missing_index_details t1,sys.dm_db_missing_index_group_stats t2,
> sys.dm_db_missing_index_groups t3
> where database_id=db_id()
> and t1.index_handle=t3.index_handle
> and t2.group_handle=t3.index_group_handle
> and object_name(object_id) = 'tableA'
> order by 1 , 2 desc
> Output :
> TblName seeks Equality_cols Inequality_cols Included_cols
> TableA 3609843 [Col1] NULL [Col2]
> TableA 3434018 [Col2], [Col1] NULL NULL
> TableA 703743 [Col1] [Col3] [Col2]
> TableA 495032 [Col2], [Col1] [Col3] NULL
> So how do I create these indices now ?
> For the first entry, is it stating to create an index on col1 with Col2 as
> included col ?
> second entry, I guess it wants a covered index on Col2,Col1
> For the 3rd and 4th entry I dont know what it wants us to create.
> The 3rd entry has a column listed for each of the 3 column types namely
> equality,inequality and included.
> Thanks
> Please help me figure this out.
>
|||By the way, you can also use the Database Engine Tuning Advisor for
recommendations for indexes on your database.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Ben Nevarez" wrote:
[vbcol=seagreen]
> Hi Hassan,
> See the BOL entry for sys.dm_db_missing_index_details:
> "To convert the information returned by sys.dm_db_missing_index_details into
> a CREATE INDEX statement, equality columns should be put before the
> inequality columns, and together they should make the key of the index.
> Included columns should be added to the CREATE INDEX statement using the
> INCLUDE clause."
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "Hassan" wrote:
|||Here is a query that can help do what you need. However, you need to test
the results. The column order may not be right so use your judgement.
SELECT sys.objects.name, (avg_total_user_cost * avg_user_impact) *
(user_seeks + user_scans) as Impact, 'CREATE INDEX YourName ON ' +
sys.objects.name + ' ( ' + mid.equality_columns + CASE WHEN
mid.inequality_columns IS NULL
THEN '' ELSE CASE WHEN mid.equality_columns IS NULL
THEN '' ELSE ',' END + mid.inequality_columns END + ' ) ' +
CASE WHEN mid.included_columns IS NULL
THEN '' ELSE 'INCLUDE (' + mid.included_columns + ')' END + ';'
AS CreateIndexStatement, mid.equality_columns, mid.inequality_columns,
mid.included_columns
FROM sys.dm_db_missing_index_group_stats AS migs
INNER JOIN sys.dm_db_missing_index_groups AS mig ON migs.group_handle =
mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details AS mid ON mig.index_handle =
mid.index_handle INNER JOIN sys.objects WITH (nolock) ON mid.object_id =
sys.objects.object_id
WHERE (migs.group_handle IN
(SELECT TOP (5000) group_handle
FROM sys.dm_db_missing_index_group_stats
WITH (nolock)
ORDER BY (avg_total_user_cost * avg_user_impact)
* (user_seeks + user_scans) DESC)) and objectproperty(sys.objects.object_id,
'isusertable')=1 --and name = 'tblperson'
ORDER BY 2 DESC
Jason Massie
Web: http://statisticsio.com
RSS: http://statisticsio.com/Home/tabid/36/rssid/1/Default.aspx
"Hassan" <hassan@.test.com> wrote in message
news:e9KaGLMMIHA.748@.TK2MSFTNGP04.phx.gbl...
> When I was going through Kalens Query Tuning and Optimization book , she
> provided the query below to find missing indices. Results are below.
> select object_name(t1.object_id)
> TblName,t2.user_seeks,t1.equality_columns,t1.inequ ality_columns,t1.included_columns
> from
> sys.dm_db_missing_index_details t1,sys.dm_db_missing_index_group_stats t2,
> sys.dm_db_missing_index_groups t3
> where database_id=db_id()
> and t1.index_handle=t3.index_handle
> and t2.group_handle=t3.index_group_handle
> and object_name(object_id) = 'tableA'
> order by 1 , 2 desc
> Output :
> TblName seeks Equality_cols Inequality_cols Included_cols
> TableA 3609843 [Col1] NULL [Col2]
> TableA 3434018 [Col2], [Col1] NULL NULL
> TableA 703743 [Col1] [Col3] [Col2]
> TableA 495032 [Col2], [Col1] [Col3] NULL
> So how do I create these indices now ?
> For the first entry, is it stating to create an index on col1 with Col2 as
> included col ?
> second entry, I guess it wants a covered index on Col2,Col1
> For the 3rd and 4th entry I dont know what it wants us to create.
> The 3rd entry has a column listed for each of the 3 column types namely
> equality,inequality and included.
> Thanks
> Please help me figure this out.

Missing index query help

When I was going through Kalens Query Tuning and Optimization book , she
provided the query below to find missing indices. Results are below.
select object_name(t1.object_id)
TblName,t2.user_seeks,t1.equality_columns,t1.inequality_columns,t1.included_
columns
from
sys.dm_db_missing_index_details t1,sys.dm_db_missing_index_group_stats t2,
sys.dm_db_missing_index_groups t3
where database_id=db_id()
and t1.index_handle=t3.index_handle
and t2.group_handle=t3.index_group_handle
and object_name(object_id) = 'tableA'
order by 1 , 2 desc
Output :
TblName seeks Equality_cols Inequality_cols Included_cols
TableA 3609843 [Col1] NULL [Col2]
TableA 3434018 [Col2], [Col1] NULL NULL
TableA 703743 [Col1] [Col3] [Col2]
TableA 495032 [Col2], [Col1] [Col3] NULL
So how do I create these indices now ?
For the first entry, is it stating to create an index on col1 with Col2 as
included col ?
second entry, I guess it wants a covered index on Col2,Col1
For the 3rd and 4th entry I dont know what it wants us to create.
The 3rd entry has a column listed for each of the 3 column types namely
equality,inequality and included.
Thanks
Please help me figure this out.Hi Hassan,
See the BOL entry for sys.dm_db_missing_index_details:
"To convert the information returned by sys.dm_db_missing_index_details into
a CREATE INDEX statement, equality columns should be put before the
inequality columns, and together they should make the key of the index.
Included columns should be added to the CREATE INDEX statement using the
INCLUDE clause."
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Hassan" wrote:

> When I was going through Kalens Query Tuning and Optimization book , she
> provided the query below to find missing indices. Results are below.
> select object_name(t1.object_id)
> TblName,t2.user_seeks,t1.equality_columns,t1.inequality_columns,t1.include
d_columns
> from
> sys.dm_db_missing_index_details t1,sys.dm_db_missing_index_group_stats t2,
> sys.dm_db_missing_index_groups t3
> where database_id=db_id()
> and t1.index_handle=t3.index_handle
> and t2.group_handle=t3.index_group_handle
> and object_name(object_id) = 'tableA'
> order by 1 , 2 desc
> Output :
> TblName seeks Equality_cols Inequality_cols Included_cols
> TableA 3609843 [Col1] NULL [Col2]
> TableA 3434018 [Col2], [Col1] NULL NULL
> TableA 703743 [Col1] [Col3] [Col2]
> TableA 495032 [Col2], [Col1] [Col3] NULL
> So how do I create these indices now ?
> For the first entry, is it stating to create an index on col1 with Col2 as
> included col ?
> second entry, I guess it wants a covered index on Col2,Col1
> For the 3rd and 4th entry I dont know what it wants us to create.
> The 3rd entry has a column listed for each of the 3 column types namely
> equality,inequality and included.
> Thanks
> Please help me figure this out.
>|||By the way, you can also use the Database Engine Tuning Advisor for
recommendations for indexes on your database.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Ben Nevarez" wrote:
[vbcol=seagreen]
> Hi Hassan,
> See the BOL entry for sys.dm_db_missing_index_details:
> "To convert the information returned by sys.dm_db_missing_index_details in
to
> a CREATE INDEX statement, equality columns should be put before the
> inequality columns, and together they should make the key of the index.
> Included columns should be added to the CREATE INDEX statement using the
> INCLUDE clause."
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "Hassan" wrote:
>|||Here is a query that can help do what you need. However, you need to test
the results. The column order may not be right so use your judgement.
SELECT sys.objects.name, (avg_total_user_cost * avg_user_impact) *
(user_seeks + user_scans) as Impact, 'CREATE INDEX YourName ON ' +
sys.objects.name + ' ( ' + mid.equality_columns + CASE WHEN
mid.inequality_columns IS NULL
THEN '' ELSE CASE WHEN mid.equality_columns IS NULL
THEN '' ELSE ',' END + mid.inequality_columns END + ' ) ' +
CASE WHEN mid.included_columns IS NULL
THEN '' ELSE 'INCLUDE (' + mid.included_columns + ')' END + ';'
AS CreateIndexStatement, mid.equality_columns, mid.inequality_columns,
mid.included_columns
FROM sys.dm_db_missing_index_group_stats AS migs
INNER JOIN sys.dm_db_missing_index_groups AS mig ON migs.group_handle =
mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details AS mid ON mig.index_handle =
mid.index_handle INNER JOIN sys.objects WITH (nolock) ON mid.object_id =
sys.objects.object_id
WHERE (migs.group_handle IN
(SELECT TOP (5000) group_handle
FROM sys.dm_db_missing_index_group_stats
WITH (nolock)
ORDER BY (avg_total_user_cost * avg_user_impact)
* (user_seeks + user_scans) DESC)) and objectproperty(sys.objects.object_id,
'isusertable')=1 --and name = 'tblperson'
ORDER BY 2 DESC
Jason Massie
Web: http://statisticsio.com
RSS: http://statisticsio.com/Home/tabid/.../1/Default.aspx
"Hassan" <hassan@.test.com> wrote in message
news:e9KaGLMMIHA.748@.TK2MSFTNGP04.phx.gbl...
> When I was going through Kalens Query Tuning and Optimization book , she
> provided the query below to find missing indices. Results are below.
> select object_name(t1.object_id)
> TblName,t2.user_seeks,t1.equality_columns,t1.inequality_columns,t1.include
d_columns
> from
> sys.dm_db_missing_index_details t1,sys.dm_db_missing_index_group_stats t2,
> sys.dm_db_missing_index_groups t3
> where database_id=db_id()
> and t1.index_handle=t3.index_handle
> and t2.group_handle=t3.index_group_handle
> and object_name(object_id) = 'tableA'
> order by 1 , 2 desc
> Output :
> TblName seeks Equality_cols Inequality_cols Included_cols
> TableA 3609843 [Col1] NULL [Col2]
> TableA 3434018 [Col2], [Col1] NULL NULL
> TableA 703743 [Col1] [Col3] [Col2]
> TableA 495032 [Col2], [Col1] [Col3] NULL
> So how do I create these indices now ?
> For the first entry, is it stating to create an index on col1 with Col2 as
> included col ?
> second entry, I guess it wants a covered index on Col2,Col1
> For the 3rd and 4th entry I dont know what it wants us to create.
> The 3rd entry has a column listed for each of the 3 column types namely
> equality,inequality and included.
> Thanks
> Please help me figure this out.

missing index on FKY

I am analyzing a database schema I inherited. I am noticing some tables which have FKY to
another table,
but don't have an index on those FKY columns. Coming from another RDBMS background, I find
it strange
that SQLServer even allows this. What is the reason in keeping index-creation a separate and
optional
task from creating a FKY.
Second question: Shouldn't it make sense to create index on FKY columns so that the joins
with the
parent table is faster and can also avoid table locks on the child table.
Yes, creating indexes on foreign keys can be beneficial to query
performance. However, the reason it is not automatically done is that
indexes are not necessary for enforcement of the constraint -- and there are
times that a DBA may not want to create an index. So you're given an
option...
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"SQL Server DBA" <sqlsdba@.gmail.com> wrote in message
news:39oep6F60jstaU1@.individual.net...
> I am analyzing a database schema I inherited. I am noticing some tables
which have FKY to
> another table,
> but don't have an index on those FKY columns. Coming from another RDBMS
background, I find
> it strange
> that SQLServer even allows this. What is the reason in keeping
index-creation a separate and
> optional
> task from creating a FKY.
> Second question: Shouldn't it make sense to create index on FKY columns so
that the joins
> with the
> parent table is faster and can also avoid table locks on the child table.
>

missing index on FKY

I am analyzing a database schema I inherited. I am noticing some tables whic
h have FKY to
another table,
but don't have an index on those FKY columns. Coming from another RDBMS back
ground, I find
it strange
that SQLServer even allows this. What is the reason in keeping index-creatio
n a separate and
optional
task from creating a FKY.
Second question: Shouldn't it make sense to create index on FKY columns so t
hat the joins
with the
parent table is faster and can also avoid table locks on the child table.Yes, creating indexes on foreign keys can be beneficial to query
performance. However, the reason it is not automatically done is that
indexes are not necessary for enforcement of the constraint -- and there are
times that a DBA may not want to create an index. So you're given an
option...
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"SQL Server DBA" <sqlsdba@.gmail.com> wrote in message
news:39oep6F60jstaU1@.individual.net...
> I am analyzing a database schema I inherited. I am noticing some tables
which have FKY to
> another table,
> but don't have an index on those FKY columns. Coming from another RDBMS
background, I find
> it strange
> that SQLServer even allows this. What is the reason in keeping
index-creation a separate and
> optional
> task from creating a FKY.
> Second question: Shouldn't it make sense to create index on FKY columns so
that the joins
> with the
> parent table is faster and can also avoid table locks on the child table.
>

missing index on FKY

I am analyzing a database schema I inherited. I am noticing some tables which have FKY to
another table,
but don't have an index on those FKY columns. Coming from another RDBMS background, I find
it strange
that SQLServer even allows this. What is the reason in keeping index-creation a separate and
optional
task from creating a FKY.
Second question: Shouldn't it make sense to create index on FKY columns so that the joins
with the
parent table is faster and can also avoid table locks on the child table.Yes, creating indexes on foreign keys can be beneficial to query
performance. However, the reason it is not automatically done is that
indexes are not necessary for enforcement of the constraint -- and there are
times that a DBA may not want to create an index. So you're given an
option...
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"SQL Server DBA" <sqlsdba@.gmail.com> wrote in message
news:39oep6F60jstaU1@.individual.net...
> I am analyzing a database schema I inherited. I am noticing some tables
which have FKY to
> another table,
> but don't have an index on those FKY columns. Coming from another RDBMS
background, I find
> it strange
> that SQLServer even allows this. What is the reason in keeping
index-creation a separate and
> optional
> task from creating a FKY.
> Second question: Shouldn't it make sense to create index on FKY columns so
that the joins
> with the
> parent table is faster and can also avoid table locks on the child table.
>

Missing entry in sysfulltextcatalogs

Hi
I've just restored a database from a backup, usually I have to correct the
path of the fulltext index in master.sysfulltextcatalogs after doing this,
but now sysfulltextcatalogs is empty and has no entries. When I right click
the table with the fulltext index I can see that it has a fulltext index
defined.
Has anyone got an idea of what's going on, why is sysfulltextcatalogs
showing up as empty?
Kind Regards,
Allan Ebdrup
I am confused, you restored a database from a backup and you expect the
sysfulltextcatalog table to contain an entry for this catalog?
I take it that the original backup was done on this machine and is not from
a remote server, and that the database have tables which were full text
indexed before you restored the backup.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Allan Ebdrup" <comaeb@.ofir.com> wrote in message
news:ehtEprejFHA.3164@.TK2MSFTNGP15.phx.gbl...
> Hi
> I've just restored a database from a backup, usually I have to correct the
> path of the fulltext index in master.sysfulltextcatalogs after doing this,
> but now sysfulltextcatalogs is empty and has no entries. When I right
click
> the table with the fulltext index I can see that it has a fulltext index
> defined.
> Has anyone got an idea of what's going on, why is sysfulltextcatalogs
> showing up as empty?
> Kind Regards,
> Allan Ebdrup
>
|||BTW - don't you mean you have to correct the path in the database's
sysfulltextcatalogs as opposed to master's?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Allan Ebdrup" <comaeb@.ofir.com> wrote in message
news:ehtEprejFHA.3164@.TK2MSFTNGP15.phx.gbl...
> Hi
> I've just restored a database from a backup, usually I have to correct the
> path of the fulltext index in master.sysfulltextcatalogs after doing this,
> but now sysfulltextcatalogs is empty and has no entries. When I right
click
> the table with the fulltext index I can see that it has a fulltext index
> defined.
> Has anyone got an idea of what's going on, why is sysfulltextcatalogs
> showing up as empty?
> Kind Regards,
> Allan Ebdrup
>