Saturday, February 25, 2012
Missing INFORMATION_SCHEMA.ROUTINES
For some reason this view is missing from my MSSQL:
INFORMATION_SCHEMA.ROUTINES
Does anyone have any idea why?... Is it a service pack maybe?
Please let me know. Thank you!I do not think that this view was added as a service pack addition. Pleasew
verify that you are logging in with applicable permissions. This view is
created by default.
"Madestro" wrote:
> Hi all,
> For some reason this view is missing from my MSSQL:
> INFORMATION_SCHEMA.ROUTINES
> Does anyone have any idea why?... Is it a service pack maybe?
> Please let me know. Thank you!
>|||it's not available in SQLServer 7. Is that the reason?
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Database change management for SQL Server
"DBADave" wrote:
> I do not think that this view was added as a service pack addition. Pleasew
> verify that you are logging in with applicable permissions. This view is
> created by default.
> "Madestro" wrote:
> > Hi all,
> >
> > For some reason this view is missing from my MSSQL:
> >
> > INFORMATION_SCHEMA.ROUTINES
> >
> > Does anyone have any idea why?... Is it a service pack maybe?
> >
> > Please let me know. Thank you!
> >|||Thank you for replying Dave, I appreciate your help.
I am logging in as "sa", but I still don't see the ROUTINES view.
I have taken a snapshot of the available views through Enterprise Manager.
If you would like to see it, please let me know an address I can send the
snapshot to.
Any other ideas?
Thanks again!
"DBADave" wrote:
> I do not think that this view was added as a service pack addition. Pleasew
> verify that you are logging in with applicable permissions. This view is
> created by default.
> "Madestro" wrote:
> > Hi all,
> >
> > For some reason this view is missing from my MSSQL:
> >
> > INFORMATION_SCHEMA.ROUTINES
> >
> > Does anyone have any idea why?... Is it a service pack maybe?
> >
> > Please let me know. Thank you!
> >|||Madestro,
When you use the object browser to look for these views, you
will only find them in the database [master], but you can access
them from any database as
select * from INFORMATION_SCHEMA.ROUTINES
Please let us know if you receive an error when you issue this query.
Steve Kass
Drew University
Madestro wrote:
>Thank you for replying Dave, I appreciate your help.
>I am logging in as "sa", but I still don't see the ROUTINES view.
>I have taken a snapshot of the available views through Enterprise Manager.
>If you would like to see it, please let me know an address I can send the
>snapshot to.
>Any other ideas?
>Thanks again!
>"DBADave" wrote:
>
>>I do not think that this view was added as a service pack addition. Pleasew
>>verify that you are logging in with applicable permissions. This view is
>>created by default.
>>"Madestro" wrote:
>>
>>Hi all,
>>For some reason this view is missing from my MSSQL:
>>INFORMATION_SCHEMA.ROUTINES
>>Does anyone have any idea why?... Is it a service pack maybe?
>>Please let me know. Thank you!
>>|||Where are you looking? The view only exists in the master database physically, but it acts just as
it exists in any database. What happens if you execute below:
USE pubs
SELECT * FROM INFORMATION_SCHEMA.ROUTINES
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Madestro" <me_no_like_spam_juanDOTromero@.bowneDOTcom> wrote in message
news:A8F2AA45-D5C9-45A0-85A4-B75B50E4D9E6@.microsoft.com...
> Thank you for replying Dave, I appreciate your help.
> I am logging in as "sa", but I still don't see the ROUTINES view.
> I have taken a snapshot of the available views through Enterprise Manager.
> If you would like to see it, please let me know an address I can send the
> snapshot to.
> Any other ideas?
> Thanks again!
> "DBADave" wrote:
>> I do not think that this view was added as a service pack addition. Pleasew
>> verify that you are logging in with applicable permissions. This view is
>> created by default.
>> "Madestro" wrote:
>> > Hi all,
>> >
>> > For some reason this view is missing from my MSSQL:
>> >
>> > INFORMATION_SCHEMA.ROUTINES
>> >
>> > Does anyone have any idea why?... Is it a service pack maybe?
>> >
>> > Please let me know. Thank you!
>> >|||Thank you all for your replies.
You are going to laugh at this but.... it turns out this machine is running
MSSQL 7.0...
I just didn't realize it since for some reason it has the 2000 Enterprise
Manager and Query Analizer tools!!!... ?
Thanks again!
"mark baekdal" wrote:
> it's not available in SQLServer 7. Is that the reason?
>
> regards,
> Mark Baekdal
> http://www.dbghost.com
> http://www.innovartis.co.uk
> +44 (0)208 241 1762
> Database change management for SQL Server
>
>
> "DBADave" wrote:
> > I do not think that this view was added as a service pack addition. Pleasew
> > verify that you are logging in with applicable permissions. This view is
> > created by default.
> >
> > "Madestro" wrote:
> >
> > > Hi all,
> > >
> > > For some reason this view is missing from my MSSQL:
> > >
> > > INFORMATION_SCHEMA.ROUTINES
> > >
> > > Does anyone have any idea why?... Is it a service pack maybe?
> > >
> > > Please let me know. Thank you!
> > >
Missing Information with Cross Tab Expert
Is there a way I can programatically add a new row one car has Full Glass, but the other doesnt, so row 1 will print "Comprehensive" and have the Car 1 value, and row 2 will print "Comprehensive - Full Glass" and have the Car 2 value?
Thanks!!
MichaelNot quite sure why you are a using a crosstab, but here goes:
Set up the crosstab by customer and, the next level lower, by car. Thus, if I had four cars, I would have four rows, sorted based on the type of coverage as you describe. If I had but two cars, both with the same level f coverage, only the two would print.
Missing Information from Database
I have a serious problem with either SQL Server or SQL Reporting Services. I have 7 tables in a database that are relational. I have created serveral reports against this database. Last week when I pulled one of the reports, I was missing information on one of the cells. The reason why I know this is because my boss has us verify the report against the database. That took about an hour to complete. When we found that the report was not correct I repulled the report and the cell that was empty, was now populated. What gives? This is the same report I have pulled for the last month and had no problems until now. Is there a time issue involved between the last update to the database to the time I pull the report? Or maybe there is something wrong with the query?
SELECT Lease.[Lease #], MineralContacts.[Mineral Owner First Name], MineralContacts.[Mineral Owner Last Name], Tract.County, Tract.[Tract Number],
Tract.[Tract Description], Tract.Section, Tract.Block, Tract.Survey, Tract.Abstract, Lease.[Lease Date], Tract.[Title Check Though], Tract.[2ndTitleReview],
Tract.[2nd R/S Complete], Tract.[Title Agent], Tract.[Tract Gross Acres], SubTract.[Tract Net Acres], Draft.[Draft Status], Draft.[Draft Due Date],
Draft.[Draft Amount], Draft.[Draft #], Draft.[ANB Invoice #], Draft.[Lse File Sent to CP], Draft.[Money Wired from CP to EA],
Draft.[STA Approved Draft for pmt], Draft.[STA Recommend to Return Daft], Draft.[KE Advised ANB to Pay Draft], Draft.[KE Advised ANB to Return Draft],
Draft.[Paid Draft Recd], SubTract.Comments, SubTract.Hide
FROM Tract INNER JOIN
SubTract ON Tract.TractID = SubTract.TractID INNER JOIN
Mineral ON SubTract.SubTractID = Mineral.SubTractID INNER JOIN
Lease ON Mineral.MineralID = Lease.MineralID INNER JOIN
Draft ON Lease.LeaseID = Draft.LeaseID INNER JOIN
MineralContacts ON Mineral.MineralID = MineralContacts.MineralID
WHERE (Draft.[Draft Status] IS NOT NULL) AND (Draft.[Draft Due Date] IS NOT NULL)
Anyone have an answer?
Missing Information from Database
I have a serious problem with either SQL Server or SQL Reporting Services. I have 7 tables in a database that are relational. I have created serveral reports against this database. Last week when I pulled one of the reports, I was missing information on one of the cells. The reason why I know this is because my boss has us verify the report against the database. That took about an hour to complete. When we found that the report was not correct I repulled the report and the cell that was empty, was now populated. What gives? This is the same report I have pulled for the last month and had no problems until now. Is there a time issue involved between the last update to the database to the time I pull the report? Or maybe there is something wrong with the query?
SELECT Lease.[Lease #], MineralContacts.[Mineral Owner First Name], MineralContacts.[Mineral Owner Last Name], Tract.County, Tract.[Tract Number],
Tract.[Tract Description], Tract.Section, Tract.Block, Tract.Survey, Tract.Abstract, Lease.[Lease Date], Tract.[Title Check Though], Tract.[2ndTitleReview],
Tract.[2nd R/S Complete], Tract.[Title Agent], Tract.[Tract Gross Acres], SubTract.[Tract Net Acres], Draft.[Draft Status], Draft.[Draft Due Date],
Draft.[Draft Amount], Draft.[Draft #], Draft.[ANB Invoice #], Draft.[Lse File Sent to CP], Draft.[Money Wired from CP to EA],
Draft.[STA Approved Draft for pmt], Draft.[STA Recommend to Return Daft], Draft.[KE Advised ANB to Pay Draft], Draft.[KE Advised ANB to Return Draft],
Draft.[Paid Draft Recd], SubTract.Comments, SubTract.Hide
FROM Tract INNER JOIN
SubTract ON Tract.TractID = SubTract.TractID INNER JOIN
Mineral ON SubTract.SubTractID = Mineral.SubTractID INNER JOIN
Lease ON Mineral.MineralID = Lease.MineralID INNER JOIN
Draft ON Lease.LeaseID = Draft.LeaseID INNER JOIN
MineralContacts ON Mineral.MineralID = MineralContacts.MineralID
WHERE (Draft.[Draft Status] IS NOT NULL) AND (Draft.[Draft Due Date] IS NOT NULL)
Anyone have an answer?
Missing index query help
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
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
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.