Looking for guidelines on using the Tuning Advisor

Question

Looking for guidelines on using the Tuning Advisor

jasona.work

SSC Guru

Points: 50082
More actions
August 7, 2012 at 1:23 pm

#260444

So I'm looking to assist the developers at my employer, who have been working on an SQL based application. The problem is, they don't know indexing (and I'm still enough of a newb to know I don't know everything I should know) and haven't put any indexes in other than a clustered index on a automatically incremental "KeyID" column (data type Int.)
Now, I've seen a few things that might help (such as a covering index on some fields that store names and acct numbers, a full text index on some description fields that get searched,) but I'd like to be able to more specifically target improvements. My thoughts thus led naturally to thinking of setting up a SQL Profiler run to generate a workload, then feeding this into the DTA.
Now, I just want to confirm a couple things on this, before I go making the suggestion:
1. Optimally, the Profiler run will be on a production system, and run for several hours.
2. The Profiler process will have some impact on the performance during this time (thankfully these aren't OLTP with thousands of transactions a second.)
3. The best option to then use DTA would be a backed up copy of the production DB, on a separate system, to "consume" the workload information, without further impacting the production system.
4. Then I / we should review the DTA suggestions before implementing anything.
Sound like a good plan? Or at least the outline of a plan?
Thanks,
Jason
PS. If you're wondering why the ostensible DBA isn't the one completely responsible for the DB, it's a combination of office politics, institutional inertia (the devs were also the ones responsible for the Foxpro-based system and its tables), and a lack of understanding of what a DBA is intended to do (which to me seems to be almost equal parts developer and administrator.)

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

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

Sean Lange SSC Guru Points: 286573 More actions · Answer 1

In my experience the DTA is pretty suspect about the suggestions it makes. Definitely use the suggestions with a grain of salt.

It sounds to me like you need to do some reading to better understand indexes. Both you and the dev team would benefit greatly from reading the stairways on indexes.

http://www.sqlservercentral.com/stairway/72399/[/url]

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

jasona.work SSC Guru Points: 50082 More actions · Answer 2

Thanks. I figure the DTA would at least be a start, until we get a better handle on the situation.

I've done some digging on indexes, and reading here, although not that particular Stairway (although I did e-mail it to the head developer yesterday!) so I know that sometimes even if you have an index on the column(s) you're querying, the SQL engine may find it's more "cost effective" to just scan the table.

Once more, thank you.

Jason

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 3

Step 1 should be to watch this movie at least twice.

http://technet.microsoft.com/en-us/sqlserver/gg508878.aspx

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Dwain Camps SSC Guru Points: 86908 More actions · Answer 4

For what it's worth, here's my opinion on Tuning Advisor.

If you put sugar on dog food it may taste better but do you really want to eat it?

The meaning of course is that adding a covering index to a poor performing query may make it run faster, but you're better advised to get your query running at optimal performance without using an index first. Then if it still needs to be faster, see what Tuning Advisor recommends.

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

jasona.work SSC Guru Points: 50082 More actions · Answer 5

dwain.c (8/7/2012)
For what it's worth, here's my opinion on Tuning Advisor.
If you put sugar on dog food it may taste better but do you really want to eat it?
The meaning of course is that adding a covering index to a poor performing query may make it run faster, but you're better advised to get your query running at optimal performance without using an index first. Then if it still needs to be faster, see what Tuning Advisor recommends.

Heh, I like the way you phrased that...

From further talking with one of the devs, it seems the primary place that people are complaining about the performance is in various search boxes in the application. Internally, the application uses the KeyID field (which has the clustered index on it) so that goes fine. It's when a user tries to search for someone in the data that the performance issue comes up. To me, it sounds like it does this because the application can't search by the KeyID as it won't know the KeyID until it finds the entry in the data, so SQL has to do a table scan to find it. So I do think there isn't much to be done to improve the query(ies) that are running into this.

I'm trying to go into this with a "light touch" philosophy, make as small and as few changes as possible, for the greatest gain.

Thanks

Jason

Feeg SSCrazy Points: 2766 More actions · Answer 6

well then you got a starting point ...

Run a SQL trace (not for hours just for those specific problimatic queries) and get the Dev's to execute all or some the bad running queries (they identified) ...

The SQL trace will record these and you can select the execution plan in the SQL trace for these queries as well. Can help you find out what and how sql is processing the queries and help you decide where the minimal change can make a beg difference ...

MCITP: Database Administrator 2005
MCTS SQL Server 2008
MCP SQL 2012/2014
MCSA SQL Server 2012/2014
MCSE Data Management and Analytics

Inbal_db SSC Rookie Points: 46 More actions · Answer 7

If you're going to capture a trace of your workload, you might want to try DBSophic's Qure Optimizer. It can recommend indexes, SQL rewrite and schema changes - depending on your database and workload, of course.

However, you should be aware that Qure Optimizer needs to run against a copy of the production database, so it requires more setup than just running DTA against your database.

Mark Eckeard Hall of Fame Points: 3462 More actions · Answer 8

I wouldn't automatically rule out the tsql that searches the database. Unless you run a serious risk of losing your job, I'd review the sproc to see if that could be improved as well.

Mark