Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Looking for guidelines on using the Tuning Advisor Expand / Collapse
Author
Message
Posted Tuesday, August 7, 2012 1:23 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: 2 days ago @ 11:53 AM
Points: 730, Visits: 5,333
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.)
Post #1341500
Posted Tuesday, August 7, 2012 1:56 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 1:04 PM
Points: 13,086, Visits: 12,553
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 Moden's 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)
Post #1341525
Posted Tuesday, August 7, 2012 2:00 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: 2 days ago @ 11:53 AM
Points: 730, Visits: 5,333
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
Post #1341530
Posted Tuesday, August 7, 2012 6:32 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:38 PM
Points: 35,371, Visits: 31,912
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1341635
Posted Tuesday, August 7, 2012 6:39 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 10:53 PM
Points: 3,421, Visits: 5,359
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!
Post #1341636
Posted Wednesday, August 8, 2012 7:26 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: 2 days ago @ 11:53 AM
Points: 730, Visits: 5,333
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
Post #1341858
Posted Wednesday, August 8, 2012 8:03 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 6:11 AM
Points: 127, Visits: 1,751
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
Post #1341886
Posted Thursday, August 9, 2012 10:04 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, November 14, 2012 12:59 AM
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.
Post #1342801
Posted Thursday, August 9, 2012 11:24 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 5:24 AM
Points: 128, Visits: 490
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



Post #1342871
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse