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 |
|
|
|
|
|
|
WhyI dont Have any result for Missing index script