Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
BigSam
Starting Member
30 Posts |
 Posted - 2005-03-01 : 09:31:14
|
| We've an intranet application that has taken the usual route of application: changing requiremnets. Consequently the database & indexes have changed to keep pace with the application. Now I want to determine what indexes are being used (or not) and how often. How do I do this without putting a hurt on my production system? I'd rather not use SQL Profiler, but will if it's my only/best choice. If I do use it, what are the minimum trace Events & Columns to capture?Part of my question stems from a poorly performing query. After investigating the problem I saw that it was not using a covering index, but rather the primary index. When I changed the query to use the correct index via the index hint, it ran well (sub-second versus over 2 minutes). I'd prefer to instruct the developer to not use the hint in a production system, but for the time being I'm stuck, until I get a better handle on the total index usage issue. Since the developer doesn't want to use stored procedures, I've asked him to include the Update Statistics in the appropriate queries, in hopes that the queries will have a better shot of using th ecorrect index.Any help will be appreciated.BigSam |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-03-01 : 20:27:51
|
quote:
Since the developer doesn't want to use stored procedures
If you're the DBA, you have every right to INSIST that they use them. There's more than performance at stake...security, code deployment, and maintenance are all affected. I can tell you from personal experience, you will DEEPLY regret having to support dynamic SQL embedded in application code instead of stored procedures. And unless you're contracting, it's entirely likely your developer will be gone at some point, and you or someone else will be stuck fixing their code.quote:
I've asked him to include the Update Statistics in the appropriate queries
This is a bad idea, as it will add overhead to the queries. Updating stats is a maintenance task, NOT programming. Frankly, if you would suggest this to the developer then suggesting stored procedures should be a lot easier and more sensible. The same applies to using index hints, they're not something a developer should concern themselves with. |
 |
|
|
BigSam
Starting Member
30 Posts |
Posted - 2005-03-02 : 09:09:48
|
quote:
Originally posted by robvolk
quote:
Since the developer doesn't want to use stored procedures
If you're the DBA, you have every right to INSIST that they use them. There's more than performance at stake...security, code deployment, and maintenance are all affected. I can tell you from personal experience, you will DEEPLY regret having to support dynamic SQL embedded in application code instead of stored procedures. And unless you're contracting, it's entirely likely your developer will be gone at some point, and you or someone else will be stuck fixing their code.quote:
I've asked him to include the Update Statistics in the appropriate queries
This is a bad idea, as it will add overhead to the queries. Updating stats is a maintenance task, NOT programming. Frankly, if you would suggest this to the developer then suggesting stored procedures should be a lot easier and more sensible. The same applies to using index hints, they're not something a developer should concern themselves with.
Thanks for the sermon. I agree that they should be using stored procedures. I've preached the same litnay without any success. Unfortunately, my boss thinks this developer can do anything & everything - including a pathetic database design, which is the root cause of this problem. I inherited this crappy database & it's associated application, and I've sent reams of emails explaining all of the problems that need fixing. I've learned not to fight city hall regarding anything this developer does. If anyone leaves anytime soon - it'll be me.I know the Update Statistics adds overhead, afterall 'Ain't No Free'. I've never recommended this nor the hints solution to anyone, because of the overhead & potential for more problems. These were the only things I could come up with to address the problem until I get a handle on a better fix; absent a better database design.Now back to my problem. What is the best way to determine index usage? I may even need to know this for another database someday.Thanks,BigSam |
|
|
|
BigSam
Starting Member
30 Posts |
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2005-03-02 : 19:19:19
|
| >> If anyone leaves anytime soon - it'll be mePost the link to this thread to the developers....Anyway, the sql-perf link was interesting.Best of Luck!rockmoose |
|
|
|
|
|
|
|
|
Index Usage