How can I test this SQL Server performance Utility?
- by Martin Smith
As part of my MSc I need to do a three month project later this year.
I have decided to do something which will likely be useful for me in the workplace and spend the time getting to understand SQL Server internals.
The deliverable for this project will be a performance advisor looking at a variety of different rules. Some static such as finding redundant indexes, some more dynamic such as using XEvents to find outlying invocations of stored procedure execution times when certain parameters are passed.
I am struggling to come up with a good way of testing this though. I can obviously design a "bad" database and a synthetic workload that my tool will pick up issues on but I also need to demonstrate that it has real world utility.
Looking at the self tuning database literature it is common to use TPC benchmarks but I've had a look at the TPCC site and it looks very time consuming to implement and not that good a fit to my project's testing needs in any event (I would still be able to "rig" it by the decisions I made on indexing or physical architecture).
Plan A would be to find willing beta tester(s) but in the event that isn't possible I will need a fallback plan.
The best idea I have come up with so far is to use the various MS sample applications as examples of real world applications.
e.g.
http://msftdpprodsamples.codeplex.com/
http://www.asp.net/community/projects/
Does anyone have any better suggestions?
