Results 1 to 11 of 11
  1. #1
    Join Date
    Apr 2004
    Posts
    12

    Question Unanswered: help!!!emergency,SQL SERVER goes wrong

    Our SQL SERVER seems to be sick,all queries run very very slow since three days ago. SQL query analyzer can't select a efficient index to produce execute plan and the CPU is idle and lazy even if many proccess threads waiting for handle,it is very strange!! Otherwise,there are too many deadlock existing when the 2 third users use system at same time.
    I have use DBCC REINDEX to re-build all indeies.

    Total data used space :11.22G:
    reserved index_size data unused
    --------------------------------------------------
    11220056 2053432 8945432 221192

    data rows in some table up to 16009830,details of some table as follow:
    Table_name Rows reserved data index unused
    ------------------------------------------------------------------------
    Worksheet_Fabric 4629414 1073568 KB 782504 KB 290360 KB 704 KB
    Fab_Input_Detail 16009830 804512 KB 616896 KB 187248 KB 368 KB
    Product_In 4385485 774048 KB 525896 KB 247880 KB 272 KB
    Dw_note 9109810 741336 KB 736600 KB 72 KB 4664 KB
    Instructions_Detail 1906960 663432 KB 592800 KB 69288 KB 1344 KB
    Instructions_M_Detail 2328862 94128 KB 504824 KB 88656 KB 648 KB
    Product_Out 4323939 575256 KB 364880 KB 210208 KB 168 KB
    Worksheet_QC_Detail 3404309 561800 KB 507552 KB 54040 KB 208 KB

    SQL SERVER VERSION:
    Microsoft SQL Server 7.00 - 7.00.623 (Intel X86)
    Nov 27 1998 22:20:07
    Copyright (c) 1988-1998 Microsoft Corporation
    Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)

    result of sp_configure:
    name minimum maximum config_value run_value
    ----------------------------------- ----------- ----------- ---------
    affinity mask 0 2147483647 0 0
    allow updates 0 1 0 0
    cost threshold for parallelism 0 32767 5 5
    cursor threshold -1 2147483647 -1 -1
    default language 0 9999 0 0
    default sortorder id 0 255 197 197
    extended memory size (MB) 0 2147483647 0 0
    fill factor (%) 0 100 0 0
    index create memory (KB) 704 1600000 0 0
    language in cache 3 100 3 3
    language neutral full-text 0 1 0 0
    lightweight pooling 0 1 0 0
    locks 5000 2147483647 0 0
    max async IO 1 255 32 32
    max degree of parallelism 0 32 0 0
    max server memory (MB) 4 2147483647 2147483647 2147483647
    max text repl size (B) 0 2147483647 65536 65536
    max worker threads 10 1024 255 255
    media retention 0 365 0 0
    min memory per query (KB) 512 2147483647 1024 1024
    min server memory (MB) 0 2147483647 0 0
    nested triggers 0 1 1 1
    network packet size (B) 512 65535 4096 4096
    open objects 0 2147483647 0 0
    priority boost 0 1 0 0
    query governor cost limit 0 2147483647 0 0
    query wait (s) -1 2147483647 -1 -1
    recovery interval (min) 0 32767 0 0
    remote access 0 1 1 1
    remote login timeout (s) 0 2147483647 5 5
    remote proc trans 0 1 0 0
    remote query timeout (s) 0 2147483647 0 0
    resource timeout (s) 5 2147483647 10 10
    scan for startup procs 0 1 0 0
    set working set size 0 1 0 0
    show advanced options 0 1 1 1
    spin counter 1 2147483647 10000 10000
    time slice (ms) 50 1000 100 100
    two digit year cutoff 1753 9999 2049 2049
    Unicode comparison style 0 2147483647 196609 196609
    Unicode locale id 0 2147483647 1028 1028
    user connections 0 32767 0 0
    user options 0 4095 0 0
    Last edited by shine_law; 08-10-04 at 02:10.

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Were any changes made to the server 3 days ago? Software patches/installs? Also, does the database have auto-create statistics set to "on"?

    Somewhat shooting in the dark here.

    Do you happen to have a test instance of the database? If so, you can see if you can replicate the problem there, too.

  3. #3
    Join Date
    Apr 2004
    Posts
    12
    Anyway thank you for your response.
    I am sure no change made on server or application software,the system has run for two years. All databases have set Auto-create statistics,I even run sp_updatestats to update all statistics.
    Generally,we have 200 users logon SQL server,but now when amount of users(connections) reach to 130 the server become very slow,on other hand,I observed CPU is not too busy to handle those requests,only 5%.

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    So, in order to get this straight, when there are 100 or less users on the system, there are no problems whatsoever? And only when the number of users climbs over 130 (or around that number) do problems with the speed of the queries start coming up? Some time when there are over 130 users on the system, run sp_who2, and see if you have blocking problems. These will be characterized by having lots of numbers (spids) in the BlkBy column.

  5. #5
    Join Date
    Apr 2004
    Posts
    12
    Not blocking,not deadlock.Only when I restart the server too many users logon system simultaneously so the server collaps.
    I have re-install SQL SERVER on another sever,run the same applications and all things go well. I wonder if there is hadware defects with that data server or SQL SERVER 7.0 itself is so weak to manage system resources.

    Thank you very much and sorry for my poor english.

  6. #6
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    So, if many people access the SQL Server just after a reboot, the server crashes? Or is just slow? Just slow is not a problem, as SQL Server is just trying to rebuild its cache of most used data. If this is a crash, what messages are you getting in the SQL errorlog, or the NT Application Eventlog?

  7. #7
    Join Date
    Aug 2004
    Posts
    1
    Check your page file and logical disk activity.

  8. #8
    Join Date
    Apr 2004
    Posts
    12
    When I reboot the SQL SERVER,if users log on system one by one,it works well.If too many users acess the SQL Server at a same time,the disaster happens,all aplication system is dead,the server Processor\% processor time is down to 2%.This status remain a long enough time and I have to reboot SQL and the same matter reappears.I have checked log file it tell me nothing.
    Last edited by shine_law; 08-13-04 at 21:32.

  9. #9
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    This could be because of tw things. If the users are trying to get in immediately after the reboot, then the database could still be in recovery. While the database is in recovery, no one can access the database. Either that, or the disk system you are using is slow or not configured correctly. How do you have the files for the database (log and data) and tempdb laid out? What raid level on each of the devices that has a database file on it?

  10. #10
    Join Date
    Jul 2004
    Location
    Inverurie
    Posts
    628
    have you tried re-booting the server after office hours? this should eliminate the bulk log on scenario that you have been having. Im not sure if this would make any difference whatsoever, but it's a pure shot in the dark.

  11. #11
    Join Date
    Aug 2004
    Location
    Rayleigh, Essex
    Posts
    6

    Exclamation sa login

    We had a very strange thing happen like this, and we found a roge sa login on spid 7 or 8 showing NO DATABASE CONTENT

    This kept make our machine very slow and crashing.

    We rebuilt it in the end

    Just a thought

Posting Permissions

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