Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2010
    Posts
    8

    Unanswered: Very slow - no idea why

    Hi all;

    This is something weird I ran across recently.

    We had a report (Windward, not SSRS) that was pretty basic, 9 pages long, and it was taking over 1 minute to run. I ran it under a profiler and 92% of the time is in the ADO.NET connector (i.e. SQL Server was slow to respond). And then about 1 time out of 20, it would be the expected 2 seconds. Something weird was going on…

    To cut to the chase, by changing the selects, I got it down to 2 seconds. I did this by crafting better selects. But the thing is, the amount of data was so small, it shouldn’t have made a difference.

    I have all the details here.

    My guess is that SQL Server is optimized for standard ways of pulling down selected data from large databases. And optimization is trade-offs where you build up caches, structures, etc. to then be able to handle 100,000 queries against a terabyte of data. And for our case, that was just a ton of unnecessary overhead. Or maybe we just hit some very weird combination of calls that it handles poorly.

    thanks - dave

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,842
    Provided Answers: 6
    These are not enough details to diagnose your performance issue. I would want to see the before and after SQL, the table DDL including indexes, and have some idea about how much hardware was involved.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Mar 2015
    Posts
    61
    Provided Answers: 4
    As ThrasyMachus stated really there isn't enough information to make a proper diagnosis. There was one glue that sounded familiar. The run time is not consistent "And then about 1 time out of 20, it would be the expected 2 seconds. " Usually when one gets inconsistent results it because background processes are taking up processor time. So here are some ideas as to where to look.
    Indexes can speed up any query but at a cost of disk space. Some of my index's ended up bigger than the database. Check your not runing up against a full hard drive. I had one situation where I wasn't out of space but I was so close the a log was having a hard time finding room to grow. Log file growth, the bigger your log file the harder your server has to work swapping out stuff. I was on a shared server once that ran alright on Tuesdays but was slow the rest of the week. Turns out they backed up the database and shrunk the logs every Monday night. I was able to deal with the problem by using chunk updates adding commits to my updates keeping the log form getting away form me. Afterwards the logs never grew over 5 gig and the runs started being more consistent.
    How your system is allocating free space, by default SQL Server will auto grow by percentage, This is not good because running out of space on a large database means it will try to allocate a larger free space. Which means it has to stop and find that space. Here is a good article. Database Growth and auto growth settings

    Hope this helps
    Last edited by wjburke2; 08-23-17 at 17:21.

Posting Permissions

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