SSWUGtv
With Stephen Wynkoop
How do you maintain security through encryption, WITHOUT killing your server? Patrick Townsend is on today’s show with some priceless tips.
Watch the Show
Hypothetical Indexes
I was reading a newsletter from a SQL Site and came across the topic of Hypothetical Indexes. That sure got my attention. I didn’t know anything like that was available. The article was restricted to Microsoft SQL Server, and used un-documented TSQL commands. I’m still highly impressed.
What is the value of a Hypothetical Index? The value of any index is the ability to optimize the performance of your SQL statements. The larger a table grows, the more impact good indexes may have on server performance. How do you know what a good index will be?
- Make a good estimated guess by tracking the kinds of queries being performed.
- Perhaps you could review the query plans from those executed queries, and look for places where an index might provide better performance.
- Build indexes on the tables and see how they work.
When your table size is large, it takes time to build indexes that you are not even sure how they will impact the performance of your query. Sometimes you have to restore a backup of your production database somewhere else in order to be able to create the indexes. Sometimes it just takes a long time to build the index. There are lots of issues involved.
The Microsoft tuning Advisor uses un-documented techniques allowing the creation of a hypothetical index (one that won’t be used by other processes). Statistics are created allowing a new query plan to be estimated without actually building the complete index. Frankly, that is really cool.
The writer provides enough information for you to use these undocumented techniques yourself. The value I see in his methodology is that you can focus on a specific area of your database, rather than covering the entire database, as you would have to do with the Tuning Advisor. You can also use his method to work on a specific known query rather than having to record activity as input to the Advisor. Rather, you have chosen the specific query or queries you have identified.
Do you think the use of hypothetical indexes will help you tune your databases? Share your thoughts by writing btaylor@sswug.org.
Cheers,
Ben
$$SWYNK$$
Featured Article(s)
Tips for using SQL Server 2012 backup and restore
In this article, you can find some helpful tips to perform backup and restore actions in SQL Server 2012.
Featured White Paper(s)
Encryption & Key Management for Microsoft SQL Server 2008/2012
Simplify encryption and key management on your SQL Server. Data thieves are targeting SMB companies because of their inadequa… (read more)