WhyI dont Have any result for Missing index script

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Administration

WhyI dont Have any result for Missing index script

Author

Topic

m.esteghamat
Starting Member

47 Posts

Posted - 2014-12-22 : 02:09:10

Hi
I Have a Missing index script. (I bring it on continue)
and the other hand, We have n Servers that there are 2 servers only for search.
on this type servers (search servers) we have 1 database and 1 main table.
and all queries that run on these servers are 2 dynamic query (exec ...).
Problem is this :
The Result of missing index query on Search type servers , is null!!!
Please help me.

--------------------------------------
SELECT mig.index_group_handle,mid.index_handle,migs.avg_total_user_cost AS AvgTotalUserCostThatCouldbeReduced,

migs.avg_user_impact AS AvgPercentageBenefit,'CREATE INDEX missing_index_' + CONVERT (varchar, mig.index_group_handle)

+ '_' + CONVERT (varchar, mid.index_handle)+ ' 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

Into MyMissedIndex2

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

order by migs.avg_user_impact Desc
---------------------------------------------

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-12-22 : 12:47:00

Are the dynamic queries using sp_executesql or just EXEC (@SQL)? I would use sp_executesql if dynamic queries are a must.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

m.esteghamat
Starting Member

47 Posts

Posted - 2014-12-27 : 07:43:09

using sp_executesql :
Such this :
CREATE PROCEDURE [dbo].[SP_Search]
(
@parameters;
)
AS
BEGIN
SET @SQLString = ' SELECT ... '
PRINT @SQLString
EXECUTE sp_executesql @SQLString, @parameters,..
END
GO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources