Page 1 of 2 12 LastLast
Results 1 to 15 of 26
  1. #1
    Join Date
    Aug 2006
    Posts
    28

    Unanswered: Sudden DB problems/instability

    I work for a small analytical laboratory. I'm mostly the net admin guy,
    but I do the db stuff (poorly) and other things.

    Last night right before closing my users started experiencing bigtime
    slowdown. This morning it was magically better for a short duration.
    As the day went on the problems got worse.

    We use MS Access as a 'front end' to talk to the SQL server. My users
    would go into fields and toggle on (or off) something they wanted.
    Sometimes the system acted as in the toggle never happend (cant
    generate a report without the right options toggled).

    I'm not very DB savvy, so I checked all the other things I was having a
    problem with that day to see if it was direct correlation.

    A few people have suggested I defrag the hdd as well as the SQL db. I
    have maintenance plans set up to back up the db and transaction logs
    daily (and weekly). However the maintenance plan that had "reorganize
    data and index pages" checked has been disabled for 2 months, since it
    seems it never finishes. It would run, and then one of my other plans
    would run (several hours later on the weekend).. and I'd come in on
    monday and find 3 maintenance plans all bound up.

    Usually I had to stop the SQL server and re-start it as the 'stop'
    option for maintenance plans wasn't being responsive.

    At any rate. I'm looking for suggestions. The DB is (after a shrink)
    7GB. I don't think is excceptionally large, so I'm not sure why I'm
    experiencing these problems.

    I found some scripts for DBCC SHOWCONTIG and DBCC INDEXDEFRAG. But I'm
    not sure how to execute them in a scheduled fashion? Is this just done
    in the maintenance jobs?

    My users need to use this system (reliably) asap tomorrow. I'm going in
    2 hours before the office opens in hopes of saving the day and anything
    I could arm myself with to make this process easier would be completely
    awesome.

    So with that in mind. I'll stop rambling.

    Ah yes, some information regarding the system:

    Windows SBS Server 2k3
    SQL Server 2000 w/ SP3 (or maybe 4?) (came with SBS 2k3)

    Hardware:
    AMD X2 4800+
    2GB ECC RAM
    160GB x 2 Sata Raid config.
    1x Gigabit Network Card

    (Not connected to the outside 'world' at all).

    Thank you for your consideration,
    Ryan

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Seven gigabytes isn't trivial, but it isn't anything to get excited over either. It is certainly a very managable size.

    The symptoms that you describe puzzle me. I've never seen maintenance plans "hang" under these kind of circumstances, and I've never seen scheduled jobs "collide" either.

    Without a lot more information, I'm just guessing, but I suspect that you might have a corrupt database. If that's the case, I'd suggest:

    1) Make a database backup to disk
    2) Use the DTS Export Wizard to copy your current database to a new database
    3) Test, test, and retest the new database. If any test fails, stop here.
    4) Use ALTER DATABASE MODIFY NAME to rename the old database out of harm's way.
    5) Use the same command to rename the new database to replace the old database.

    -PatP

  3. #3
    Join Date
    Dec 2002
    Posts
    1,245
    Lots of things to look at; I completely empathize with your situation.

    If/when things start to slow down, try running sp_who2 or sp_lock from a query anayzer window. If there is locking/blocking going on, then you should be able to detect which SPID (process) is causing the blocking.

    From there, try to determine what statement is being executed (google fn_get_sql). You may get lucky and find a statement that can be tuned (copy/paste the statement into QA and run the index tuning wizard).

    Be careful about the defrag/reorg options in the default maint plan wizards. You are probably better off looking up DBCC INDEXDEFRAG in SQL BOL. Use DBCC SHOWCONTIG WITH TABLERESULTS to identify candidate indexes to defrag (depending on who you talk to, look for indexes where the scan density is less than 80%...or 70% or 90%). Focus on indexes with more than just a few pages of data (one or two columns over in the list). Reason being, you could have a scan density of 66%, but if there are only three pages of data, it's not making that much difference. If there are 3,000 pages for that index, then that's another matter entirely.

    7GB is not too large, but it's large enough that poorly designed indexes could certainly affect performance.

    Look for large DELETE operations (deleting thousands or hundreds of thousands of records in a single go). A DELETE will lock a table and cause contention.

    If I understand your drive configuration, it is not ideal. A pair of SATA drives raided together? It may not be something you can do anything about, but I would have preferred 5 SCSI drives; one RAID-1 with the log files and three drives RAID-5 for data files. If you can't change it, you will have to deal with it, but it is something to keep in mind for the future.

    Finally, some odds and ends...
    1. Network utilization, what is it? Are clients pulling down large data sets and tying up network IO (look for the wait resource column in sp_who2).
    2. Virus? I know you said you are not connected to the outside world, but it's something to check...
    3. Other processes/jobs/stuff? Open task manager and see what's chomping your CPU. Add columns for IO and memory to see what else may be running too.

    I hope this helps some...

    Regards,

    hmscott

    Edit to add hint: Copy and paste the results of DBCC SHOWCONTIG WITH TABLERESULTS into an excel spreadsheet. Use the AutoFilter option (after adding column headings), to sort/filter the data according to criteria you want (Scan Density < 80% and Pages > 10, for example). Then add a formula at the end to concatenate a string to create a DBCC DEFRAGINDEX (database, table, index name) using the appropriate columns from the spreadsheet (and adding the database name as a constant). It's a quick and easy way to defrag all the clustered indexes on tables in the db. If you don't see an index name, it probably means that there is no clustered index for that table. That might be something to fix (CREATE CLUSTERED INDEX IX_[tablename]_[columnname] on [tablename] ([columnname]). You will want to do some additional research to determine which columns to index.
    Last edited by hmscott; 08-11-06 at 01:07.
    Have you hugged your backup today?

  4. #4
    Join Date
    Dec 2002
    Posts
    1,245
    Quote Originally Posted by Pat Phelan
    The symptoms that you describe puzzle me. I've never seen maintenance plans "hang" under these kind of circumstances, and I've never seen scheduled jobs "collide" either.
    I ran into this issue in my current environment. I was never able to pinpoint the precise reason fo the hangs, but I believe it is related to the maint plan wizard using DBCC DBREINDEX (which I believe drops and recreates the index). I could never find a "quiet" time where a DBREINDEX could run without processes attempting to update the data. I gave up and began using DBCC INDEXDEFRAG instead about two years ago.

    Regards,

    hmscott
    Have you hugged your backup today?

  5. #5
    Join Date
    Aug 2006
    Posts
    28
    Thanks for the advice folks. Into the office I go.

    A few tidbits:

    CPU utilization is always low.. under 10%. I watched this yesterday.

    Network utilization is always low. There's only about 30 users on this, and some aren't very active at all.

    I'm going to try and showcontig/defragindex this morning, hopefully. I've got users coming in -- 2 hours from now.

    When I've been in the SQL manager looking at processes and spid's I've seen waittypes of NETWORKIO once or twice, and another one I can't recall once.

    But when using those process viewers, I don't understand why there's no way to refresh that (no f5, no right click -> refresh?) The only way for me to see the changing processes is to right click the main "branch" (I forget the name now, It's 6:30 and I'm not an early bird) and tell that to refresh, then go back and look at the processes.

    I'll report back in a bit...

  6. #6
    Join Date
    Aug 2006
    Posts
    28
    Alright, all my big tables are in the 90% or greater scan density. I did have tables with a few dozen pages in the 50% area. But as you guys said, since it's not a big table that's likely not the cause.

    I'm running a script I found on MSDN that does scancontig, takes anything with greater than 30% frag and runs the DEFRAGINDEX on it.

    I'm not quite sure where to go from here.. or how long this will take. So far the messages window is empty.

  7. #7
    Join Date
    Aug 2006
    Posts
    28
    Ok I keep getting a pid (52) that gets a waittype of PAGEIOLATCH_EX?.. it goes away and goes to a DBCC if I refresh it now and then. I can see my hdd light churning so it must be doing something...

    I also have an spid (52) blocked by (52)....... it comes and goes.

  8. #8
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Well, you have confirmed (if unknowingly) that you have SP4 installed. The (spid) blocked by (same spid) was introduced as a diagnostic tool in SP4, and is not really a problem. The Occasional DBCC is probably the indexdefrag, or showcontig running.

    When the slowdown occurs next time, jump on the SQL Server with Query Analyzer. Just run "sp_who2", and see how long it takes to get back to you. Normally, this system stored procedure comes back pretty quickly, so if you are having major problems on the DB server, it should be agonizingly slow. Another thing to check would be how much memory SQL Server has in its Working Set (Perfmon Processes->sqlsrvr.exe counter) Normally, this should be as high as physical memory will allow. If it is under 4 or 500 MB, you may just need more memory. Lastly, check the disk queue counters. You may need to turn these on with diskperf. It could be that you are now running into disk queueing.

    Lots of things you could look for, so I will leave my suggestions at that for now.

  9. #9
    Join Date
    Aug 2006
    Posts
    28
    ok i ran INDEXDEFRAG on my biggest table with 500k pages. It had a logical scan frag of 38%. I had a user (singular) try and change what we were having problems with last night as so far so good.

    ***EDIT

    Now that a bunch of users are on, we're seeing the same symptoms.

    I'm completely stumped.
    Last edited by Mindflux; 08-11-06 at 11:13.

  10. #10
    Join Date
    Dec 2002
    Posts
    1,245
    Quote Originally Posted by Mindflux

    Now that a bunch of users are on, we're seeing the same symptoms.

    I'm completely stumped.
    ou mentioned CPU and Network usage. What's disk utilization? Paging? You have 2 GB RAM. Can you bump it to 4 GB?

    Regards,

    hmscott
    Have you hugged your backup today?

  11. #11
    Join Date
    Aug 2006
    Posts
    28
    I thought SQL Server that came with SBS2k3 was limited to 2GB anyway?

    I'm still trying to concatenate the rest of the tables (about 130) with high frag.

    I can only get concatenate to do one set of colums at a time? (A20, C20).. not a range A20:A160 ?
    Last edited by Mindflux; 08-11-06 at 11:52.

  12. #12
    Join Date
    Aug 2006
    Posts
    28
    Quote Originally Posted by MCrowley
    . Another thing to check would be how much memory SQL Server has in its Working Set (Perfmon Processes->sqlsrvr.exe counter) Normally, this should be as high as physical memory will allow. If it is under 4 or 500 MB, you may just need more memory. Lastly, check the disk queue counters. You may need to turn these on with diskperf. It could be that you are now running into disk queueing.

    Lots of things you could look for, so I will leave my suggestions at that for now.

    Alright, well it hasn't been slow today, but I'm still having problems where users update fields and they dont actually GET updated.

    sqlsrvr in task manager is using 1.5GB of memory (1.41GB actually). There's 2gb in the system so I imagine it's about right there.

  13. #13
    Join Date
    Aug 2006
    Posts
    28
    Quote Originally Posted by hmscott
    ou mentioned CPU and Network usage. What's disk utilization? Paging? You have 2 GB RAM. Can you bump it to 4 GB?

    Regards,

    hmscott

    paging in perfmon seems to never move
    however avg disk queue length right now is 1.45, but I've seen it a hair above 4.00 also.

    From reading up disk queue length shouldn't exceed spindles +2 (so in my case 4)..

    not sure how relevent this is to my tables not updating.. more so to db speed?

  14. #14
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Quote Originally Posted by Mindflux
    not sure how relevent this is to my tables not updating.. more so to db speed?
    Yep. Database speed. Looks like you are relatively OK there, as well. Could be better, but it is not a huge performance problem.

    Now for the "lost updates". Either these are tossing errors, or they are getting rolled back. Is this a repeatable thing? If so, you can check the user's log (if it exists) or sit with them to see what message boxes they click through. If neither of these are the case, you can use Profler to try to catch what is going on. The Events of interest to you will be
    Errors And Warnings->Exceptions
    and
    Errors And Warnings->OLEDB Errors
    I don't see an event for Rollbacks, so you may have to go digging in the code for that keyword.

  15. #15
    Join Date
    Dec 2002
    Posts
    1,245
    Quote Originally Posted by Mindflux
    I thought SQL Server that came with SBS2k3 was limited to 2GB anyway?

    I'm still trying to concatenate the rest of the tables (about 130) with high frag.

    I can only get concatenate to do one set of colums at a time? (A20, C20).. not a range A20:A160 ?
    I could be wrong, but I thought that the version of SQL 2000 that shipped with SBS2k3 was SQL 2000 Standard, in which case the upper memor limit would be 4 GB. Were it me, I would put in 4 GB RAM, put a /3GB switch in the boot.ini file and let SQL dynamically manage memory to an upper limit of 3 GB. I would also set my paging file to a fixed size.

    I'm a bit puzzled b your last statement regarding defragmentation and concatenation. I am assuming it is related to my hint on how to build a series of DBCC INDEXDEFRAG statements using an Excel spreadsheet. You can create as many statements as you like using a formula (enter the formula, then copy and paste it into the appropriate cells). Am I misunderstanding your statement somehow?

    Regards,

    hmscott
    Have you hugged your backup today?

Posting Permissions

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