The hypothetical indexes created by the Index Tuning Wizard start with a name of "hind_%" and should not exist after the tuning has finished; they should all be removed. You can run the following script from the SQL Server Query Analyzer to remove any such indexes that may exist. You must log in by using an account that has either sysadmin or db_owner permissions, or is the owner of the object on which these statistics were created.
For example
DECLARE @strSQL nvarchar(1024)
DECLARE @objid int
DECLARE @indid tinyint
DECLARE ITW_Stats CURSOR FOR SELECT id, indid FROM sysindexes WHERE name LIKE 'hind_%' ORDER BY name
OPEN ITW_Stats
FETCH NEXT FROM ITW_Stats INTO @objid, @indid
WHILE (@@FETCH_STATUS -1)
BEGIN
SELECT @strSQL = (SELECT case when INDEXPROPERTY(i.id, i.name, 'IsStatistics') = 1 then 'drop statistics [' else 'drop index [' end + OBJECT_NAME(i.id) + '].[' + i.name + ']'
FROM sysindexes i join sysobjects o on i.id = o.id
WHERE i.id = @objid and i.indid = @indid AND
(INDEXPROPERTY(i.id, i.name, 'IsHypothetical') = 1 OR
(INDEXPROPERTY(i.id, i.name, 'IsStatistics') = 1 AND
INDEXPROPERTY(i.id, i.name, 'IsAutoStatistics') = 0)))
EXEC(@strSQL)
FETCH NEXT FROM ITW_Stats INTO @objid, @indid
END
CLOSE ITW_Stats
DEALLOCATE ITW_Stats
Above script is provided by Microsoft for more detail you can visit following link.
http://support.microsoft.com/kb/q293177
You've got lots of questions in here:
Q: (The lack of foreign keys) confuses me a lot! It is a good practice (not mandatory) to have Fk's in the DWH for a variety of reasons (data integrity, relations visible for semantic layer, ....)
A: Correct, it's normally a good practice to have foreign keys in a data warehouse. However, clustered columnstore indexes don't support that yet.
Q: So MS advocates Clustered Column store indexes for DWH scenarios, However it can not handle FK relationships?!
A: Microsoft gives you tools. It's up to you how you use those tools.
If your biggest challenge is a lack of data integrity in your data warehouse, then the tool you want is conventional tables with foreign keys.
If your biggest challenge is query performance, and you're willing to check your own data integrity as part of the loading process, then the tool you want is clustered columnstore indexes.
Q: However SQL 2014 than adds no real new value for DWH??
A: Thankfully, clustered columnstore wasn't the only new feature in SQL Server 2014. For example, check out the new cardinality estimator.
Q: Why am I so angry and bitter about the way my favorite feature was implemented?
A: You caught me - you didn't really ask that question - but I'll answer it anyway. Welcome to the world of third party software where not everything is built according to your exact specifications. If you feel passionately about a change you'd like to see in a Microsoft product, check out Connect.Microsoft.com. It's their feedback process where you can submit a change, other people can vote it up, and then the product team reads it and tells you why they won't implement it. Sometimes. Most of the time they just mark it as "won't fix, works on my machine" but hey, sometimes you do get some answers.
Best Answer
I like to follow the Performance Tuning by Waits and Queues methodology introduced by SQLCAT team member Tom Davidson.
You can download and read his seminal white paper from here.
SQLCAT is Microsoft's SQL Customer Advisory Team - the folks who deal with the most demanding, largest SQL Server customers and thus have unique insights into some of the biggest installations and how to make them fly.
Basically, SQL Server collects a lot of statistical data on why and what it waits for. Collecting and interpreting that information can lead you to perf tune in the right spot and fixing the right issues, instead of just bumbling around and not knowing where to start and what to do.
Read that whitepaper! It's really very enlightening!
And of course - you'll need to establish a baseline first (e.g. have a set of queries - if ever possible a reasonable and realistic "workload" from your system in production - and measure how they perform) and then you need to tweak something (based on the insights from collecting and interpreting the wait stats) and then you need to measure again to see if that tweak help anything at all - or not.