Monday, 16 December 2013

SQL Server Database Engine Tuning Advisor

I noticed a problem whilst doing some routine tuning. I had a query that I wanted to improve as the profiler told me it was doing a lot of work. I ran this through the Database Engine Tuning Advisor (DETA) but I guess I must have started it up wrong.

I was in another copy of the database when I was looking at this query (the two databases were identical in both structure and data). I then fired up DETA, which had selected "DatabaseB", I de-selected this and selected "DatabaseA" and ran the analysis.

The result came back, no improvement, no recommendations. I was surprised by this as I was expecting some index suggestions, I opened the view in design mode and examined the indices on the tables myself and saw what IMHO was quite a lack of at least 2 indices that I would have though would speed things up.

Curiously, I logged the Management Studio onto DatabaseA and ran it again, 2 indices suggested and 99% improvement estimated.

So it would seem that you should choose your database before firing up DETA or it would (I suspect) still run the query in the original database it was launched from but only examine tables from the selected database.