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.
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 |
 |
|
|
|
|