Results 1 to 10 of 10
  1. #1
    Join Date
    Feb 2003
    Posts
    6

    Unhappy Unanswered: Slowdown in DB after data migration

    We are having a problem that last Friday we add 25% more data to our database so size is about 2.2 gigs. Last Friday end of business our fat client connecting to SQL was running about 2 to 3 seconds per screen over a wide area network. Starting Monday after the data was entered our times per screen load can be from 2 to 20 seconds YILES! Anyone have any idea's where we should be looking for this added time. Just so you know I am an Infrastructure manager I am just posting here because I am looking for help from anywhere.

    Thanks in advance!

  2. #2
    Join Date
    May 2002
    Posts
    299

    Re: Slowdown in DB after data migration

    You might want to run "sp_updatestats" to make sure everything is up-to-date.
    --
    -oj
    http://www.rac4sql.net

  3. #3
    Join Date
    Feb 2003
    Posts
    6
    Would a reboot of the box take care of this, or is this something that could possibly help even after a reboot. Only reason that I ask is because we are going to reboot at midnight?

    Thanks for the idea...

  4. #4
    Join Date
    May 2002
    Posts
    299
    Reboot the server will clear all server cache. It will nothing to your indexes/statistics.

    After a long dataload, one should *always* reindex.
    --
    -oj
    http://www.rac4sql.net

  5. #5
    Join Date
    Feb 2003
    Posts
    6
    sorry mis understood, we did reindex last night no real change in response time..

  6. #6
    Join Date
    May 2002
    Posts
    299
    Did you update statistics? Statistics are not the *same* as indexes. They represent data sampling which sqlserver uses to compute plans.
    --
    -oj
    http://www.rac4sql.net

  7. #7
    Join Date
    Feb 2003
    Posts
    6

    Unhappy

    Data was imported Sat, we re-index on Sunday evening, and the statistics were done Monday. Improved a little went from 20 to 35 seconds down to the 2 to 20 seconds. We were originally at 2 to 6 seconds at the longest in our fat client. When we had the time creeps before we just ran, dbcc dropcleanbuffers and dbcc freeproccache, but the aspirin no longer works on the problem and it has gotten much worse. Any and all help is appreciated!

  8. #8
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    one additional step is to force a recompile of all stored procedures. Look up sp_recompile in Books Online.
    Paul Young
    (Knowledge is power! Get some!)

  9. #9
    Join Date
    Feb 2003
    Posts
    6

    Unhappy

    Have also tried this and from what I am reading using freeproccache does that and much more what use to be one of our standard fixes for this. Thanks for the help!

  10. #10
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    DBCC FREEPROCCACHE does not force a recompile of stored procedures. Clearing the procedure cache will cause ad-hoc SQL statments to be re-compiled.
    Paul Young
    (Knowledge is power! Get some!)

Posting Permissions

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