May 4, 2009 at 5:18 am
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
May 4, 2009 at 5:46 am
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!
May 4, 2009 at 12:32 pm
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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
May 4, 2009 at 10:52 pm
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
May 5, 2009 at 6:33 am
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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
May 6, 2009 at 12:19 am
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
May 6, 2009 at 5:40 am
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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
May 6, 2009 at 5:51 am
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?
May 6, 2009 at 7:06 am
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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply