Indexed Views.

Question

Post reply

Indexed Views.

nabeelmukhtar

Right there with Babe

Points: 749
More actions
May 4, 2009 at 5:18 am

#204009

Hi all
I want to know if its possible by looking at the execution plan of a query to come up with a design of an indexed view that will be used by the query optimizer while executing the query.
The execution plan does contain missing indexes information but as far as I know it does not have the recommendations of an indexed view that will benefit the execution of the query..
Is there any source for that information in SQL Server 2005 and 2008?
Regards
Nabeel Mukhtar

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Dugi SSCoach Points: 17998 More actions · Answer 1

The biggest source in the world is BOL, you can check these two links with much information about indexed views:

http://msdn.microsoft.com/en-us/library/dd171921.aspx

http://msdn.microsoft.com/en-us/library/ms191432.aspx

In SQL Server 2008 you have possibility to create filtered index also, read BOL for more info!

============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 2

There's nothing that will automatically suggest an indexed view. They really depend on what you're doing with the data. There isn't a set formula for their use, although there are specific requirements that they have to meet before you can create them.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

nabeelmukhtar Right there with Babe Points: 749 More actions · Answer 3

Thanks for your replies.

I am curious to know how Database engine tuning advisor recommends indexed views. What does it look for in the query? I must say I haven't played with it much but it would be nice if it provides a programmatic or T-SQL based interface much like Oracle's SQL Access Advisor. I can't utilize the GUI or the command line interface in my application.

Any idea how it recommends indexed views?

Thanks again.

Regards

Nabeel Mukhtar

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 4

To know what the DTA looks for inside it's code, you need to talk to someone at Microsoft. There's very little in the documentation (available here, just so you have it) that suggests the specifics that the DTA uses. It works off the workload you provide and the statistics of the database that you point that workload to. Based on those queries, internally it goes through mathematics, probably involving some of the same math used by the optimizer, to determine when & where different indexes, or indexed views, could help.

Why are you so focused on indexed views?

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

nabeelmukhtar Right there with Babe Points: 749 More actions · Answer 5

Thanks again for your help.

I was not actually focused on indexed views, I just want to provide the database engine tuning wizard functionality through my application. i.e. the application should be able to recommend indexes and indexed views based on the user provided workload (queries). I was able to find the missing indexes information from the query plan xml (its also available in various DMVs but there its not associated with a particular query so I had to fetch it from plan xml.). But the indexed view recommendation does not seem to be available anywhere.

Alternatively if there is a way to invoke the database engine tuning wizard programmatically (and remotely) e.g. through t-sql, it would be of great help.

Thanks again.

Nabeel Mukhtar

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 6

You can call it from the command line. If you had to do it through TSQL, you could use xp_cmdshell, but I wouldn't recommend it.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Dugi SSCoach Points: 17998 More actions · Answer 7

Grant Fritchey (5/6/2009)
You can call it from the command line. If you had to do it through TSQL, you could use xp_cmdshell, but I wouldn't recommend it.

Yep, my friend Grant can you explain little bit why you wouldn't recommend xp_cmdshell!? - just curious?

============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 8

Dugi (5/6/2009)
Grant Fritchey (5/6/2009)
You can call it from the command line. If you had to do it through TSQL, you could use xp_cmdshell, but I wouldn't recommend it.
Yep, my friend Grant can you explain little bit why you wouldn't recommend xp_cmdshell!? - just curious?

The main reason I don't like it is because it usually (but not always) requires elevated privileges to run stuff through the command line on the server. I usually try to give the least amount of privileges that I can to avoid unintended issues.

The other is, TSQL is a fine language for manipulating data, but getting into programmatic functions with it is more than a little bit problematic. If you wanted to use a CLR function for this, I could see that working much better since a .NET language has better error handling, etc., and is more geared towards this sort of thing.

In general, I just shy away from doing stuff in TSQL that doesn't involve DML or DDL.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning