SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Looking for guidelines on using the Tuning Advisor


Looking for guidelines on using the Tuning Advisor

Author
Message
jasona.work
jasona.work
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16308 Visits: 13160
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.)
Sean Lange
Sean Lange
SSC Guru
SSC Guru (101K reputation)SSC Guru (101K reputation)SSC Guru (101K reputation)SSC Guru (101K reputation)SSC Guru (101K reputation)SSC Guru (101K reputation)SSC Guru (101K reputation)SSC Guru (101K reputation)

Group: General Forum Members
Points: 101590 Visits: 18199
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/

_______________________________________________________________

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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
jasona.work
jasona.work
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16308 Visits: 13160
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
Jeff Moden
SSC Guru
SSC Guru (341K reputation)SSC Guru (341K reputation)SSC Guru (341K reputation)SSC Guru (341K reputation)SSC Guru (341K reputation)SSC Guru (341K reputation)SSC Guru (341K reputation)SSC Guru (341K reputation)

Group: General Forum Members
Points: 341035 Visits: 42658
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
dwain.c
dwain.c
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28555 Visits: 6431
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!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
jasona.work
jasona.work
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16308 Visits: 13160
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
Feeg
SSC Eights!
SSC Eights! (830 reputation)SSC Eights! (830 reputation)SSC Eights! (830 reputation)SSC Eights! (830 reputation)SSC Eights! (830 reputation)SSC Eights! (830 reputation)SSC Eights! (830 reputation)SSC Eights! (830 reputation)

Group: General Forum Members
Points: 830 Visits: 2013
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 DBA 2008
Inbal_db
Inbal_db
SSC Rookie
SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)

Group: General Forum Members
Points: 36 Visits: 58
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
Mark Eckeard
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1143 Visits: 505
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



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search