Hypothetical Indexes in SQL Server

Problem:


While attempting to rebuild all indexes on a table, I am getting the following error:

Cannot rebuild hypothetical index '_dta_index_table_name_10_992214785__K9' online.

What is this hypothetical index and I cannot see any such indexes in the object explorer against the specific table. How can I get over this error.


By: Anonymous


Solution:


You or someone who works on the database should have used Database Tuning Advisor (DTA) to analyze the performance aspect of the tables and indexes. DTA creates hypothetical Indexes when it checks queries & tables for columns to measure the performance gain that such indexes may offer and thus come up with a recommendation. Usually when the DTA completes its analysis, it deletes all the hypothetical indexes if it cleanly finishes but in some cases, these indexes may be left over.


For a query or a stored procedure execution, after an initial recompile is triggered, the optimizer uses some of the information from these hypothetical indexes, which is out of date, and hence incorrectly determines that a recompile is needed again. During the ensuing recompiles, the information from the hypothetical indexes is never refreshed, and so the optimizer remains in a recompile loop. Hence, dropping hypothetical indexes is a better choice.


Clustered hypothetical indexes can be dropped with DROP INDEX statement. Nonclustered hypothetical indexes can be dropped with DROP STATISTICS statement.

You may find out all hypothetical indexes using this query:


WITH [hypo] AS (

SELECT QUOTENAME(SCHEMA_NAME(o.[schema_id])) +'.'+ QUOTENAME(OBJECT_NAME(i.[object_id])) AS [Table] , QUOTENAME([i].[name]) AS [Index_or_Statistics], 1 AS [Type]
FROM sys.[indexes] AS [i]
JOIN sys.[objects] AS [o] ON i.[object_id] = o.[object_id]
WHERE 1=1
AND INDEXPROPERTY(i.[object_id], i.[name], 'IsHypothetical') = 1
AND OBJECTPROPERTY([o].[object_id], 'IsUserTable') = 1

UNION ALL

SELECT QUOTENAME(SCHEMA_NAME(o.[schema_id])) +'.'+ QUOTENAME(OBJECT_NAME(o.[object_id])) AS [Table], QUOTENAME([s].[name]) AS [Index_or_Statistics], 2 AS [Type]
FROM sys.[stats] AS [s]
JOIN sys.[objects] AS [o] ON [o].[object_id] = [s].[object_id]
WHERE [s].[user_created] = 0
AND [o].[name] LIKE '[_]dta[_]%'
AND OBJECTPROPERTY([o].[object_id], 'IsUserTable') = 1
)

SELECT [hypo].[Table],[hypo].[Index_or_Statistics], 

CASE [hypo].[Type]
WHEN 1 THEN 'DROP INDEX ' + [hypo].[Index_or_Statistics] + ' ON ' + [hypo].[Table] + ';'
WHEN 2 THEN 'DROP STATISTICS ' + [hypo].[Table] + '.' + [hypo].[Index_or_Statistics] + ';'
END AS [Drop Stmt]
FROM [hypo]

Hope this helps you.

SSRS - Custom Period Filters

Problem:

Just wondering if the reports published on SQL Server Reporting Services can be customized to have a predefined period filters like Last Week, Last Month, Last Quarter, etc.

By: Anonymous

Solution:

You have few options to work around this, which are given below:

1. Dynamic Parameter

Basically, your underlying query or stored procedure needs the values for the From and To paramteres to apply in the where clause of the query. You can do this by first creating a mutli valued Period parameter, with values like, Last Week, Last Month, etc specified as available values. The From and To parameters can then be set up in such a way that it fetches the default value from a query, which takes the value from the Period parameter and then return the corresponding From and To values respectively. In these, the Period Parameter shall be positioned above the From and To parameters. The query can be as simple as

SELECT 
CASE 
WHEN @Period = 'LW' THEN DATEADD(D,((DATEPART(dw, GETDATE())-1)*-1)-7,GETDATE())
WHEN @PEriod = 'LM' THEN DATEADD(M,-1,DATEADD(D,(DAY(GETDATE())-1)*-1, GETDATE()))
END AS FROM_DATE

for the default values of the From parameter. You may extend this idea and use it different ways to make a parameter dynamically derive a value based on a previous parameter. Note that the order of the parameter is important as the parameters are constructed sequentially and there is nothing like an onChange event that you can think of.

2. Let the SQL Query / Procedure do the work

The second alternative is to put in a logic similar to the above within the Report Query or Stored Procedure, so that the user just selects the period filter and the SQL Server interprets it appropriately and returns the appropriate results. The only difference with this option is that the From and To Filters need not be designed in the report and the user won't see it on screen.

3. Wrap the report within an Application

Just in case, you are rendering the reports within an Application, like a ASP.NET application or a Windows Forms Application, then you can have the complete control on the Filters in the presentation layer of your application. For instance in this case, you accept a Period Filter from the user in your front end application and then transform it to an appropriate From and To dates to the ReportViewer component and get the report rendered accordingly.

SSRS Report Builder 3.0 - How to avoid page breaks in Report Viewer

Problem:

The Report Viewer displays the tabular reports with pagination. This is inconvenient to users as they have to click on the page navigator to browse through the report. Is there a way to have the report viewer display all the rows in one view, so that users will find it convenient to browse through the report by just scrolloing up and down?

By: Muthu Saravanan

Solution:

Use the Interactive Size property of the report to overcome this. This property will not however show up in the Report Properties dialog. This will be available on the Properties Pane, which shows up ususally on the right side of the screen. If it is not showing up, check the options under the View menu. To have all records in single view without pagination, set the Height under Interactive Size to 0 cm.

This setting is effective for display in the report viewer only. Page breaks in print and other export formats will be based on the Page Size rather than the Interactive Size property.