SQL Server Performance

CPU Showing 100% when running Reports

Discussion in 'Performance Tuning for DBAs' started by shreesoft00, Mar 7, 2003.

  1. shreesoft00 New Member

    Hi
    i am running sql2000 on win nt 4.0
    i have database which is 9.5 gb in size which is geographically located in defferent location and user compalined me when they run reports it takes hours then i found that the when the report querry is fired the db server cpu is 100% untill it completes transaction log files was 8.5 gb and data file 1.25 gb i shrinked the log files as we dont need log backups . now db size is 2 gb and i ran dbcc comands checkdb and reindexed all the tables but still
    the problem is there we cant question about the query as the queries runs from an world famous software

    can anyone help it out ?
    shreesoft00, Mar 7, 2003
    #2

  2. satya Moderator

    Whatever it may be the queries originated from world famous software, still you need to fine tune them or use views (to reduce overhead on live tables) instead to get better performance.

    Run PROFILER and see what kind of activity its having during this operation.

    Satya SKJ
    satya, Mar 7, 2003
    #2

  3. shreesoft00 New Member

    hi satya
    thanx
    but i cant actually modify the queries if some prblem persist
    because the statements are in built in the application
    and it is running fine in other locations
    in this server in process monitor it shows sqlserver using very high memory and cpu100%



    shreesoft00, Mar 7, 2003
    #2

  4. bradmcgehee New Member

    I have run into the same problem myself, where queries from third party software are poorly written and I can't change them. In this case, your best bet is to the following:

    -- Optimize hardware
    -- Optimize indexes
    -- Rebuild indexes periodically

    If you have not done so, get a Profiler Trace of typical activity and run the Index Wizard against the trace to identify obvious indexes. If this helps, but is not enough, then you must identify the longest running queries (using Profiler) and then examine them carefully (viewing their execution plan) to see if creating new indexes would be helpful. This can be a slow and tedious project, but you don't have a lot of options.

    By the way, what part of Switzerland are you from? I will be in Switzerland doing the Tour Du Mount Blanc in September of this year.

    ------------------
    Brad M. McGehee
    Webmaster
    SQL-Server-Performance.Com
    bradmcgehee, Mar 7, 2003
    #2

  5. dtipton New Member

    "because the statements are in built in the application
    and it is running fine in other locations"

    Do you mean an identical report is run from a different location and the performance is better?
    dtipton, Mar 7, 2003
    #2

  6. satya Moderator

    Tailormade application queries can only be fine tuned by using PROFILER and INDEX TUNING WIZARD, as it proved 100% favour for me.

    Check whether any other processes are running on SQL server at the same time, it will make lot of difference even though if its runs for a minute.

    Satya SKJ
    satya, Mar 8, 2003
    #2

  7. shreesoft00 New Member

    "because the statements are in built in the application
    and it is running fine in other locations"

    I mean we have the same setup in out another office and the same query is running fine there
    I have already done the profiler activity and reindexed the query it has really affected the performance positively

    but still its slow
    and the server runs only sqlserver and there is no other services running on that server
    i feel is there any hardware issue ?

    Thaanx brad

    shreesoft00, Mar 9, 2003
    #2

  8. satya Moderator

    Can't say deliberately its an hardware issue. BTW what is the difference between these 2 SQL engines in terms of SQL setup and H/w resources?

    Satya SKJ
    satya, Mar 10, 2003
    #2

  9. bradmcgehee New Member

    The only way to tell if hardware is an issue is to do a Performance Monitor log and look for potential bottlenecks. Also, are there any other applications running on the slow server as compared to the faster server?

    ------------------
    Brad M. McGehee
    Webmaster
    SQL-Server-Performance.Com
    bradmcgehee, Mar 10, 2003
    #2

  10. satya Moderator

    Add up to Brad's comments - just sneak thru event viewer (all logs) for any kind of information (informational/warning).

    Satya SKJ
    satya, Mar 11, 2003
    #2

  11. shreesoft00 New Member

    Strangely the server in shich it runs faster have a small web server also on that
    and still its faster and hardware configuration is concerned its the same exactly the same
    the different is faster server is on Win2k and the slower one is in WinNT4

    is it any issue running WinNt4 with sql2000?

    thaanx
    shreesoft00, Mar 11, 2003
    #2

  12. satya Moderator

    2 of my Production servers runs perfectly (in terms of performance) on SQL 2K with Win NT4 SP6.

    Satya SKJ
    satya, Mar 11, 2003
    #2

  13. satya Moderator

    quote:Originally posted by satya

    2 of my Production servers runs perfectly (in terms of performance) on SQL 2K with Win NT4 SP6.

    Further I would suggest running PERFMON as referred.

    Satya SKJ


    Satya SKJ
    satya, Mar 11, 2003
    #2

  14. bradmcgehee New Member

    I'd still take a close look at the hardware, using Performance Monitor as your guide to what may be going on internally in these servers. Also, be sure you double verify that the databases are the same with recently updated statistics and rebuilt indexes.

    ------------------
    Brad M. McGehee
    Webmaster
    SQL-Server-Performance.Com
    bradmcgehee, Mar 11, 2003
    #2

  15. bytehd New Member

    Umm, a wire trace might show the exact SQL sent to the engine.

    My personal FAQ:
    Primary/Foreign keys for RI
    Indexes (sarg, join, covering) for performance
    Constraints for domain integrity
    bytehd, Mar 17, 2003
    #2

Share This Page