'Microsoft.AnalysisServices.DsvTableBinding' to type 'Microsoft.AnalysisServices.QueryBinding'

One of the things this blog intends to do is help speed up the troubleshooting process by documenting strange errors like this.

Here's an interesting error message that came up today which has surprisingly little web coverage.

"Unable to cast object of type 'Microsoft.AnalysisServices.DsvTableBinding' to type 'Microsoft.AnalysisServices.QueryBinding'.

It is caused by trying to apply a querystring to an existing partition that is not query-driven in OMA. In this case, an SSIS 2005 vb.net app that builds partitions (by the hundreds for me) into a cube. Like many things, its one of those spend a while then slap your forehead problems that makes a ton of sense. Obviously, I can't set the partition query of a partition that is table-bound.

I do plan to post my ssis app once I am done with it. But until then, here's the blog post that inspired it.

"No question is so difficult to answer as that to which the answer is obvious." -George Bernard Shaw

handy feedback for SSIS 2005 Script Components

As you know, getting feedback from SSIS is spotty at times. Debugging is also very limited in SSIS Script Components (where's immed!?). This came in handy today.

Dts.Events.FireProgress("informational string", 0, 0, 0, Nothing, False)

Lots of different ways to get info out of the "progress" window. Lots of feedback possibilities in the Dts.Events collection.

And don't forget that in SSIS 2008, you can write C# as well as VB.net.

"Feedback is the breakfast of champions." -Ken Blanchard

Fancy Missing Indexes

UPDATED 5/30/2019, the latest version of this script is in the Toolbox here: https://github.com/SparkhoundSQL/sql-server-toolbox/blob/master/missing%20indexes.sql

UPDATED 5/28/2013, see notes below.

You've seen fancy ketchup. Here's fancy missing indexes.

This script is database specific. It does not use the server-wide DMV's alone, it joins to INFORMATION_SCHEMA.TABLES system tables to generate index create scripts. This is a very fast way to enter an environment, and take a peek at the indexing situation, something I do regularly as a DBA consultant.

SELECT 
 mid.statement
, create_index_statement = 'CREATE NONCLUSTERED INDEX IDX_NC_' + replace(t.name, ' ' ,'')
 + replace(replace(replace(ISNULL(replace(mid.equality_columns, ' ' ,''),'') , '],[' ,'_'),'[','_'),']','') 
 + replace(replace(replace(ISNULL(replace(mid.inequality_columns, ' ' ,''),''), '],[' ,'_'),'[','_'),']','') 
 + ' ON ' + 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 + ')', '')  COLLATE SQL_Latin1_General_CP1_CI_AS
, unique_compiles, migs.user_seeks, migs.user_scans, last_user_seek, migs.avg_total_user_cost
, avg_user_impact, mid.equality_columns,  mid.inequality_columns, mid.included_columns
, quartile
--select *
FROM sys.dm_db_missing_index_groups mig
INNER JOIN --get only the top 20% of suggestions based on cost.
(select *, quartile = NTILE(5) OVER (ORDER BY avg_total_user_cost asc) from sys.dm_db_missing_index_group_stats) migs 
ON migs.group_handle = mig.index_group_handle
--and migs.quartile = 1
INNER JOIN sys.dm_db_missing_index_details mid 
ON mig.index_handle = mid.index_handle
inner join sys.tables t 
on t.object_id = mid.object_id
inner join sys.schemas s
on s.schema_id = t.schema_id

WHERE 1=1
and mid.database_id = db_id() -- current database only
--and  (datediff(week, last_user_seek, getdate())) < 6
--AND  migs.unique_compiles > 10
--and  migs.quartile >= 3
--and  migs.user_seeks > 10
--and  migs.avg_user_impact > 75
--and  t.name like '%salesorder%'

--order by avg_user_impact * avg_total_user_cost desc 
order by create_index_statement

Pardon blogspot's inane raw code posting ability.

• The improv calc up top in the alternate ORDER BY is simply to evaluate the relative use of a potential index against others that show up in the report. Its not terribly scientific but weighs usage and value.
• The WHERE clause items can be used depending on the situation. If your server has been operating for a while and you'd like to see what indexing needs recent changes have been generating, use the date filter. Have a lot of ad hocs? Filter on unique_compiles > 1. The third WHERE clause is the same as the improv value above, helps me filter out things that are too small to care about. Lastly, I filter out anything that won't have at least a 50% impact. Oftentimes, I'll set this last value much higher.
• Joins to INFORMATION_SCHEMA.TABLES system tables to generate a proper index create statement. It follows a naming convention I prefer, IDX_NC_ for nonclustered indexes, followed by the table name and the key fields. For covering indexes, I'll remove the key fields from the name and just put "covering". Your naming convention for indexes is probably different.
• Before creating any indexes from the missing index DMV's, do a little research on them and keep in mind that they reset with the server. It maybe useful to retain these over time, spanning multiple resets.
• Oftentimes, you'll see many of the rows in this result overlap. The INCLUDES might be in a different order, or you'll see one suggested index that has all but one other field that another suggestion has. Intelligently combine these together, keeping in mind that order DOES matter in the key fields.
• Lastly, if you've examined existing indexing on the table, use the provided creation script in the column create_index_statement .
• This script, aside from a few tweaks here and there, is hardly an original creation of this blog. As part of your research on missing index DMV's, don't miss: http://blogs.msdn.com/bartd/archive/2007/07/19/are-you-using-sql-s-missing-index-dmvs.aspx

Other linkage:
https://learn.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms179325(v=sql.105)?redirectedfrom=MSDN
https://www.mssqltips.com/sqlservertip/1634/find-sql-server-missing-indexes-with-dmvs/

Updated 5/28/2013 to reflect some changes, including getting rid of the INFORMATION_SCHEMA tables in favor of system tables, update the formatting,