SQL Server Performance

Generally slow databases

Discussion in 'Performance Tuning for DBAs' started by Ecophobic, Oct 24, 2011.

  1. Ecophobic New Member

    Hello there!

    First of all, I've been scouring these forums for a while so I guess I owe you all a thank you for all the great information you have here. Lovely place!

    My problem is basically that I need help forming a schedueled task for maintenance of 4 databases containing a LOT of tables, I know this is prolly as vague as you can get but I'll ofc add information as its asked of me because I honestly dont know what info you need.

    From what I've gathered using some DBCC commands and running Profiler for a few days trying to figure out what tables were the ones causing the most performance-dips we had a few tables with under 15% scan density, (ran DBCC INDEXDEFRAG and it's dipped down to 75% now again), the harddrives are fragmented worse than I have ever seen (60% free HDD space and still all red when running a defrag). And Im fairly certain I'll have to look over what kind of indexes were using too.

    Do you guys have any tips to get me started? Since these databases are in use by clients I cant really run any Offline-operations except during the weekends and even then its a tight push.

    // Eco
  2. Luis Martin Moderator

    Welcome to the forums!.
    Some questions:
    1) What SQL and SP do you have?.
    2) What maintenance jobs do you run on weekends?.
    3) Do you have set on/off autocreate/autoupdate statistics in each database?
    4) After running profiler, did you find long queries and check execution plan?
  3. Ecophobic New Member

    1. SQL 2000 SP 4
    2. None really, a reboot of the computer is probably the biggest maintenance job we run.
    3. Autoupdate is on.
    4. Yes, and everything looked fine from what I could see. The only issue was the low performance from the database itself. Probably due to horrendously fragmented harddrives and indexes.

    Im thinking: DBCC Showcontig and indexdefrag on everything with more than a 30% fragmentation. Checkdisk and monthly defrags of the harddrives. And maybe some form of shrinkdb on the logfiles to clean up some memory since everything is at auto-grow atm.

    Does this sound sensible? I'm not very good at this : /

    // Eco
  4. Luis Martin Moderator

    Try to run Reindex on weekends.
    Also run Performance monitor to find out disk, memory, processors, locks counters. May you have some lack of resources.
  5. Ecophobic New Member

    We have plenty of resources on the machines. I can't really do anything about the hardware except maintenance.

    Is there any good way of benchmarking the results I'm getting? Should I do like 10 loops of selecting top 10000-results and then dropping cache before and after each search and use getdate to compute the time it takes for querys to run or is there a better, more well-used way of checking on progress?

    Do I need to recompile all the SPs after running Reindex to ensure good execution-plans?

    Problem with REINDEX instead of INDEXDEFRAG is that I want to use as few offline-operations as possible since these machines are in use pretty much always, and while the defrag doesnt aquire a lock on the table reindex does if I haven't missunderstood anything (please do correct me on these things, I know I might have missundestood a good bit of this). And while I realize I'll have to run a reindex from time to time, its not something I'd like to do weekly.

    Thank you for your fast input btw Luis!

    // Eco
  6. Luis Martin Moderator

    Reindex include update statistics. That's why I suggest to run Reindex at least once a week .
    Now, if you don't find any longer query using Profiler, I suppose you have unhappy users.
    You said: "The only issue was the low performance from the database itself" could you explain a little more?.
    Also:
    How many processor do you have and how many to parallelism?.
    How much memory and how is setting?
    How big are each database?
    Is SQL dedicated?
  7. Ecophobic New Member

    1 processor, Im not sure how to check MAXDOP, but its apparantly 5 min query plan threshhold to concider paralell execution (not sure if its even possible with only 1 processor.
    4gb memory. 1mb min query memory. Dynamic SQL memory with 0-2.2gb.
    The databases sizes vary a lot. The largest is about 8gb. The rest are 1,5gb-ish and theres about 10 dbs's but theres only issues with the large one, I'm focusing on the large 4.

    SQL has boosted Windows prio.

    EDIT:

    When I said the part about the only problem being the database performance itself, I meant there is no way for me to influence the hardware-environments in which the databases are being run. I have some leeway in setting memory for SQL etc, but I cant add CPUs/Memory/RAID-Disks to the hardware setup, which would make my life a whole lot easier.

    Sorry about the confusion.
  8. Luis Martin Moderator

    Well, 1 processor (means no parallelism) and 4gb is not a lot of resources for 10 databases.
    So, run Performance Monitor with:
    Memory: Available Kbytes.
    Memory: Pages/s
    Physical Disk: Average Disk Queue Length (for each physical disk).
    Processor: %Processor Time.
    SQL Server Buffer Manager: Buffer Cache Hit Ratio.
    SQL Server Buffer Manager: Page Life Expectancy.
    SQL Server Memory Manager: Target Server Memory
    SQL Server Memory Manager: Total Server Memory.
    System: Processor Queue Length.

    Run for a while and back with average counters.

    Also live dynamically memory from 0 to 4gb. If you server is x86, check if you have set /3gb in boot.ini
  9. Ecophobic New Member

    Can I trust the performance monitor results when its running on VMware? The server is virtual : /. We tried benchmarking the hardware since theyre using the same physical disk for two different Virtual machines running different servers (I know...) and it still got better I/O results than a Raided SSD while running both SQL-servers.

    That harddrive isn't even running any form of raid-setup from what I've gathered...

    Also, I would like to focus on just the SQL part if possible. I feel ungrateful for saying this but I'm not, appriciate the help but the plan is making a weekly maintenance job that can be distributed to a lot of customers (IMO we're in need of a maintenance job, not sure if I can get the resources to actually put these out there) or at least to have a job I can run manually with a schedualed task in weekends. Changing hardware setups or actually the settings of the SQL Server application itself is almost out of the question, because of frequent reinstalls etc which I cannot actually influence in any way.
  10. Luis Martin Moderator

    I'm sorry I have no experience with VM. Wait for others members opinions.
  11. MichaelB Member

    Just to answer your one question if you can trust performance monitor in a VMWare environment I can answer. It depends.. lol . Ok. Joking aside, the answer is no. The host could be nailed doing other tasks and ther perfmon will not reflect those activities. it is blind to the other clients or if the host is doing a backup etc. I hope that helps. I am not a VMWare expert nor do I administer them but I do have servers on them and we had issue that the disk was slow for some SSIS jobs and it just seemed to be hanging waiting.. and nothing going on. which SQL never stopped the process and it never ended. it just got lost...
  12. Luis Martin Moderator

  13. Ecophobic New Member

    Why oh why didnt we get a seperate RAID10 setup for the MDFs :(. Havent gotten an oppurunty to actually do a REINDEX but the INDEXDEFRAG helped a lot... for a day...

    Now the scan density is down to 15-20% AGAIN. I have average Page densities on 2% on some tables. What the...

    So what to do what to do... Should I just defrag the harddrive, do a checkdisk and start looking over eventually rebuilding all the indexes? Maybe look over the index types? Im really lost here, we need to get higher performance because the application using the databases are getting timeouts. Im desperate enough to start going through the SPs for more effective algorithms now, and that feels like it shouldnt be needed since the application is working fine on machines that arent ancient/fragmented to death.
  14. Ecophobic New Member

    Ran a benchmarking-query on the VM after rebuilding indexes. Results from Showcontig was GREAT etc. The query took 54 secs. I didnt DARE to reset the cache of the SQL due to the server being in use tho.

    I then ran the same benchmark querys against another, exactly alike db. The machine is about 20% slower hardware-wize. Same structure, really poor showcontig-results. Dreadfully fragmented and really bad Scan-density etc. Resetted ALL the cache on the SQL and the query took
    4 seconds...

    I'm thinking the Virtual-Machine is causing this. Can there be ANY other reason for these extremely strange results?

Share This Page