Results 1 to 12 of 12
  1. #1
    Join Date
    Nov 2002
    Posts
    23

    Unanswered: Suggestions for speeding up SQL 7

    OK, I'm in the process of defrag and am begining to think this is not my only issue in my EXTREMELY slow database(s).

    I've never had an issue with speed before. My main knowledge of speeding things up was making sure everything was set to grow by mb instead of percent.

    is there anything else I should be checking?

  2. #2
    Join Date
    Oct 2002
    Posts
    369

    Lightbulb Re: Suggestions for speeding up SQL 7

    Originally posted by criki
    RE: OK, I'm in the process of defrag and am begining to think this is not my only issue in my EXTREMELY slow database(s).

    I've never had an issue with speed before. My main knowledge of speeding things up was making sure everything was set to grow by mb instead of percent.

    is there anything else I should be checking?


    Q1 I'm in the process of defrag and am begining to think this is not my only issue in my EXTREMELY slow database(s), is there anything else I should be checking?

    A1 Yes. There are many places to look. (First of all, what exactly is slow? - everything or only certain queries / stored procedures)
    i If eveything, some places to start might include looking at unnecessary services on the sql server, very high cpu utilization, high IO use, bandwidth saturation - high collision rates, concurrency issues such as blocking, overall index maintenance and design, etc.
    ii If only certain stored procedures are slow, check the slow ones statement by statement, e.g. (are columns in where clauses indexed, do they use inefficient cursors, etc.), etc.

  3. #3
    Join Date
    Feb 2002
    Posts
    2,232
    What exactly is slow ?

  4. #4
    Join Date
    Nov 2002
    Posts
    23
    Well, we have 3 apps (none that I have the code to) and in 1 app a few tables (there are no sp's or views), app 2 is slow when people in app 1 are running queries, app3 is fine.

    Each app has completely seperate databases.
    Unfortunately, there's nothing I can do about their coding, but I do have full control over the db's.

  5. #5
    Join Date
    Oct 2002
    Posts
    369
    RE:
    Well, we have 3 apps (none that I have the code to) and in 1 app a few tables (there are no sp's or views), app 2 is slow when people in app 1 are running queries, app3 is fine.

    Each app has completely seperate databases.
    Unfortunately, there's nothing I can do about their coding, but I do have full control over the db's.
    Q1 [App 2 is slow when people in app 1 are running queries, while app3 is fine]
    A1 From that, you may want to start by looking at what exactly app 1 (DB stored procedures etc.) is doing that 'boggs down' app2. In any case it appears you need to gather more detailed information; for example, determine if app1 is mainly CPU or IO intensive or both, identify any particularly resource intensive app1 stored procedures, etc., . You may, for example, find that manipulating (better maintaining, adding, dropping, etc.) app1 DB indexes might be a helpful strategy.

  6. #6
    Join Date
    Nov 2002
    Posts
    23
    The main problem here is I don't have access to the code of any of these 3 apps. There aren't any stored procs or views for me to tune in the DB's.
    So, if there is a problem with the queries in the app resulting in longer times there's nothing I can really do on the code side.

    Database wise, it's all mine to rearrange. Minus table structure :-( that would affect code, of course.
    So, I stuck to rearranging indexes and?? any other suggestions?

  7. #7
    Join Date
    Oct 2002
    Posts
    369
    Originally posted by criki
    The main problem here is I don't have access to the code of any of these 3 apps. There aren't any stored procs or views for me to tune in the DB's. So, if there is a problem with the queries in the app resulting in longer times there's nothing I can really do on the code side.
    Database wise, it's all mine to rearrange. Minus table structure :-( that would affect code, of course. So, I stuck to rearranging indexes and?? any other suggestions?
    Q1 {So, I stuck to rearranging indexes and?? any other suggestions]?
    A1 The apps must use SQL which you may capture.
    i There are no SPs in any DBs? That is an interesting design strategy, for what are these, commercial applications?
    ii Learn about Sql Profiler, if nothing else run the index tuning wizard against several longer traces taken against several periods of heavy activity. Beyond that, note which tables are being hit, capture some of most problematic Sql, put that into query analyzer, and fine tune your indexes based on that as necessary).

  8. #8
    Join Date
    Nov 2002
    Posts
    23
    Yes, they're all commercial apps (uggh!!) I know! No trig, sp's, nothing!

    Good! Then I'm on the right track, I just opened the profiler up for the first time. I'll play around there and see if I find anything useful.

    Thank you all for all your help!!

  9. #9
    Join Date
    Oct 2002
    Posts
    369
    Originally posted by criki
    Yes, they're all commercial apps (uggh!!) I know! No trig, sp's, nothing!
    Good! Then I'm on the right track, I just opened the profiler up for the first time. I'll play around there and see if I find anything useful.
    Thank you all for all your help!!
    RE: Thank you all for all your help!!
    S1 You are welcome.

    RE: I just opened the profiler up for the first time.
    S2 You might want to make a mental note:

    Using profiler adds to the server load as well (it can be a powerful tool that is well worth using but it can be a drag on performance). You might want to warn your users in advance of the days / times you will be recording extended traces (to use later with the index tuning wizard or analyze, etc.). That should help to avoid addressing unnecessary user complaints; you might also explain that the goal is to result in better performance (for your inconvenienced users) in the future.

  10. #10
    Join Date
    Feb 2002
    Posts
    2,232
    Don't forget about your "Current Activity" under Management. This will give you processes/locks that are currently active. Another benefit is that you can actually see the query that is in place and possibly track down a handful of queries that are creating the problem. Also, this lacks the resource intensive behavior that comes with profiler.

  11. #11
    Join Date
    Oct 2002
    Posts
    369

    Lightbulb

    Originally posted by rnealejr
    RE: Don't forget about your "Current Activity" under Management. This will give you processes/locks that are currently active. Another benefit is that you can actually see the query that is in place and possibly track down a handful of queries that are creating the problem. Also, this lacks the resource intensive behavior that comes with profiler.
    S1 True, "Current Activity" is especially handy for quickly seeing current blocks and locks and at least a portion of the query that is in place.

    S2 Unfortunatly, "Current Activity" won't show more than the first 255 characters or so of the current sql statement. Often that is 'good enough' but if your db has no stored procedures and your applications are sending much longer sql select statements and what not it probably won't be nearly as helpful.

    S3 Using "Current Activity" is most effective if one regularly refreshes the entire "Current Activity" view. Don't expect or rely on "Current Activity" to refresh itself on its own.

    S4 Use Enterprise Manager, and hence "Current Activity" from a workstation (rather than on the Sql Server console); generally EM is noticibly less resource intensive when run from a workstation.

  12. #12
    Join Date
    Feb 2002
    Posts
    2,232
    If you look at current activity it actually provides a date/time stamp next to it which should be a clue that it doesn't automatically refresh. But, dba brings up a good point that throughout em, especially in version 7, the need to refresh is critical. Also, you can use the performance monitor - find the "SQLServer:" under the performance objects header.

    The following is a good slide show on monitoring tools for sql server including profiler and performance monitor.

    show

    Good luck.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •