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.

Website Scalability Test - JMeter Listeners

Question:


I am on a task to perform a scalability test of a website. I am new to JMeter and I want to know how to capture and analyze the test results using JMeter. More specifically I need to capture the response time of the pages that I would be testing.

By: Saran Sathyan


Answer:

You need to add a listener and configure to write the test results into a Log file. You have the option of using a csv or xml file format. You can do this visually, by right clicking on your test project shown in the left explorer bar and then add-> Listner->... JMeter provides many listeners and you may just choose either Simple Data Writer or View Results in a Table. In the resulting window, you will find options to specify the output file location and configure the data elements that you want to capture. JMeter captures two time fields, one being the elapsed time and the other being latency. While the latency indicates the time to the first response, the elapsed time indicates the total time to load the page completely. With this listener, all your test results will be logged, and you may use simple tools like excel or import into MS Access or such other database as you may be comfortable and then get the necessary summary data by grouping on appropriate fields.


Generally to assess the scalability of your website, you also need to monitor and collect stats from the server(s) on which your website is running at the same time your tests are run. You may have to engage your system administrators to help you in collecting the performance statistics on server resource utilization like, CPU, Memory, Network bandwidth, Disk IO, etc. As you simulate more load from JMeter, the utilization of the resources on the server will go up. The ideal approach is to plan to have multiple tests starting with 50 or 100 users and ramp up the load until you the resource utilization on the server hits the maximum (say 90%). This way you may also benchmark the site performance on the given server hardware and network bandwidth.

Your report should also report the server utilization details in addition to the page response time and the bytes received so as to make it useful for the report users to make further decisions. Also be aware that if you perform the load tests from one location, your test results might be biased as you may hit the bandwidth limitation on your end. Hope this helps.

For more details on JMeter listeners, check out the documentation available online at http://jmeter.apache.org/usermanual/listeners.html

JMeter for Scalability Testing

Question:

In on of your own blog post, you have indicated the use of JMeter for Stress Testing a Game Application. I have a web application to be tested for its scalability. Do you recommend using JMeter for simulating load? You may also share some thoughts on its ease of setting up and usage and generally about Scalability testing. You may also recommend using any other tools together with JMeter.

Question By: Natarajan Ganesh

Response:

Scalability testing is about testing how the application scales in or out under different load conditions, and yes we need tools to simulate the load. It is about specifically testing the deployment model of the application as to how it makes use of additional computing resources when the load increases. Needless to mention, it involves testing both scale up and scale down. The actual test cases for scalability testing depend on the application design and architecture as to how it is expected to scale.

For example, in case of a cluster of application servers used, the test case should be to verify that the load on all the servers in the cluster are evenly distributed or as designed and that no server in the cluster is idles out when one or more other servers are peaking beyond a threshold. Another related test case could be to examine the application behavior when one of the server in the cluster is pulled out.

Thus scalability testing involves in addition to simulating required load or stress on the application, examining whether the scale up or down happens as designed or architected.

Yes, JMeter is a good tool for simulating the necessary load or stress on the application. Setting up JMeter and using it is easy and intuitive. The following simple steps would help you to get JMeter up and running the tests on a Windows box:

1. Make sure that you have JRE or JDK installed the bin folder is included in the system path.
2. Navigate to the bin folder of the JMeter installation folder and run the JMeter.bat. Just in case if it errors out, view or edit the JMeter.bat file to see if there is any other settings that need to be modified.
3. Right Click on the Test Plan in the vertical explorer pane on the left and add a Thread Group (Add -> Threads -> Thread Group).
4. Right Click on the newly added thread group and add a Sampler -> HTTP Request. In the resulting window make sure that you fill out appropriate vaules. Repeat this step to add as many HTTP Requests to be sampled.
5. Use an appropriate Listener to gather the data. This can be done again by adding a Listener to the Thread Group.

You could observe from the Add menu that you have options to add configuration elements, pre and post processors, assertions and logic controllers. Depending on your test scenario, use a combination of these components an build your test plan accordingly. Consult the JMeter documentation to to know more about effectively using JMeter.

While JMeter is just a tool simulate the stress or load, you need tools to gather server perormance data and as well as the application performance. While the native performance monitoring tools on Windows could b very useful, for Linux, you can try out NewRelic, a hosted remote monitoring tool. As I have described in one of my blog post, the application should have been designed to generate necessary data for further review and examination to spot probable or potential bottlenecks that need to be further tweaked.

Good luck with your testing.