SQL Server Performance Forum – Threads Archive

Self optimising application

Im currently working on a huge project which will be deployed as a win32 gui app, and a sql database. Its a single user application and so the database will run under MSDE on a single machine. The database allows extremely flexbile analytical reports to be run, and each user may want to use the system in an entirely different way. What I want to provide, is a basic set of indices which will be helpful for some thigns which all users will do. But then I want the application to ‘learn’ how the user drills into the database and attempt to over time, be able to suggest indices which would be useful to the way the user queries the db. Im aware I can use index tuning wizard for specific queries, but the variations are too great to provide an all encompassing set of indices for everyone. Has anyone done anything similar to this ? Would be nice to hear any advice if you have. Im hoping to query the execution plan after each user query and analyse this in my application to derive indices which ight be useful. Its a bit of a headbuster, admittedly <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />

I had taken help from this site by referring to this article http://www.sql-server-performance.com/sql_server_performance_audit7.asp] which I refer always to my developers. Moreover, to help identify which tables in your database may need additional or improved indexes, use the PROFILER (hope you’re on track about this topic). Also referring those frequently used queries in QA by using Query Execution Plan and fine tune accordingly with the help fo ITA. HTH Satya SKJ

If i was to actually sit down and tune the database indices manually, I could optimise it for a given user using these techniques. However, my problem is the application needs to tune itself, ie after being deployed it needs to learn how the user is using the system and figure out what indices it should apply programatically. There is to be no user intervention, especially as the people using it are not the least bit tech savvy – so profiler and indx tuning wizard is not a possibility once its deployed.

In that case to get optimum performance you need to revamp the application (if possible) to use accordingly as per index recommendations by any tool. Satya SKJ

Can you recommend such a tool ?
My thoughts were to attempt to write this process myself by having the application study execution plans, and maintain its own usage statistics, after each and every query. Then once a week or so use these to derive an optimum set of indices.

Depends on which tools or code used to build that application. I believe this going to take too much of time incase if you require to monitor regularly. As I referred revamping the application means, is to modifying most used queries everytime and running few tests on QA and get the required results. And with regard to tool means, SQL server supplied ones QA and ITA.
Apologies if I mislead you. Satya SKJ

I have never seen an application that self-tunes its own indexes, nor have I considered the possibility. I presume it can be done (I don’t know of any technical reasons why not). If you are going to take on this project, you might want to consider writing in generically so that it can be used by other applications. In fact, if you write such as app, you should consider marketing it, as I imagine you would find a large market for it.
——————
Brad M. McGehee
Webmaster
SQL-Server-Performance.Com

Hi Brad, thanks for the advice. I actually have no choice but to make this generic, as not all users will take all aspects of the database. But youre right, if I were to develop it as a library which could be just plugged into other projects easily I imagine it could be a very nice tool.
No doubt I will have lots of other questions as I develop the system, ie how certain aspects of the execution plan suggest that some indices could be improved.

]]>