SQL Server Performance

database server suddenly running slow

Discussion in 'General DBA Questions' started by aaronsandy, Apr 20, 2005.

  1. aaronsandy New Member

    Hi!
    One of my database server suddenly running slow.When I clicked on the tree view of any instances on this server, its taking time to show the structure.
    The network is fine I guess as another db server in the same network is working fine.More over it has more dbs than the problematic server.Though all the dbs on this server are working fine yet something is wrong with my db server.I cant see any err logged in event viewer or sql error log,but I am sure there is some problem.Till yesterday it was fine.Now it is too slow.Pls suggest what to do.The system has enough free memory.No dbs were added in past 1 week.
  2. deepakontheweb New Member

    Create a performance monitor trace with below counters set interval time to 15 sec and run for 20 mins during peak working hours from another sever. Save output in tabdelemeted text file and analyze..it will give you clear picture of problem area..

    MemoryAvailable MBytes"/>
    MemoryPages/sec"/>
    Paging File()% Usage"/>

    -- for both data and log files of SQL database add below three counters
    PhysicalDisk()Avg. Disk Queue Length"/>
    PhysicalDisk()Disk Reads/sec"/>
    PhysicalDisk()Disk Writes/sec"/>

    Processor(_Total)% Processor Time"/>
    SystemProcessor Queue Length"/>
    SQLServer:Buffer ManagerBuffer cache hit ratio"/>
    SQLServer:Buffer ManagerProcedure cache pages"/>
    SQLServer:General StatisticsUser Connections"/>
    SQLServer:Locks(_Total)Number of Deadlocks/sec"/>

    -- if your server got more then 1 CPU, then add below counter
    SystemContext Switches/sec"/>

    Deepak Kumar

    --An eye for an eye and everyone shall be blind
  3. aaronsandy New Member

    When I connect to the server by QA and click on the drop down box to view the dbs on the server, the QA gotta hanged.The same is the case when I use EM to view the db list or any other tree view.
  4. deepakontheweb New Member

    What is the level of service packs installed on server for SQL?


    Deepak Kumar

    --An eye for an eye and everyone shall be blind
  5. ranjitjain New Member

    i think u need to edit and save the sql server registration properties again and check the client network utility.
  6. aaronsandy New Member

    I set up the performance monitor for the above counters but it failed to capture.When I saw the event viewer I got the following error .
    The Collect Procedure for the "Spooler" service in DLL "C:WINDOWSsystem32winspool.drv" generated an exception or returned an invalid status. Performance data returned by counter DLL will be not be returned in Perf Data Block. The exception or status code returned is the first DWORD in the attached data.

    What to do now?
  7. aaronsandy New Member

    I have sp3 installed.I dont think its a service pack issue because till yesterday everything was fine.
  8. aaronsandy New Member

    Client network utility on this server has the TCP/IP and named pipes enabled.Nothing here is changed .
  9. ranjitjain New Member

    I dont have much idea but i think spooler is printing service
  10. ranjitjain New Member

  11. aaronsandy New Member

    Now the performance counter logs is working fine ,lets see after 20 min what the output it gives.
  12. aaronsandy New Member

    Even when I right click on the properties to see the properties window ..the time comsuming is more than 15 min
  13. satya Moderator

    This could be a problem because of mismatch of few .DLL files on the client tools, uninstall the client tools and reinstall to take new settings with good performance.

    As a test you can try to access the server from other client's machine.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  14. Adriaan New Member

    You see this mostly when the server runs out of diskspace. If your databases have the Full recovery model, without the appropriate backup strategy, your log files may be getting into GB terrirory.
  15. deepakontheweb New Member

    you can have a look on system events and SQL error logs if there is any clue hidden there.

    (Because, After an action there is reaction.. Nothing happens by its own)

    Deepak Kumar

    --An eye for an eye and everyone shall be blind
  16. aaronsandy New Member

    Here is the details of performance logs.What I observed is that the processor time is more approx 99% which is more than usual.Now what to do.


    MemoryAvailable MBytesMemoryPages/secPaging File(_Total)% UsagePhysicalDisk(_Total)Avg. Disk Queue LengthPhysicalDisk(_Total)Disk Reads/secPhysicalDisk(_Total)Disk Writes/secProcessor(_Total)% Processor TimeSQLServer:Buffer ManagerBuffer cache hit ratioSQLServer:Buffer ManagerProcedure cache pagesSQLServer:General StatisticsUser ConnectionsSQLServer:Locks(_Total)Number of Deadlocks/secSystemContext Switches/secSystemProcessor Queue Length
    56 7.790120443 99.732441471171184 10
    5607.7901204430.1965895629.7629884973.87844748599.8956158799.882005911801860821.02721071
    5607.789103190.42824058315.449446382.33073544699.8959417399.8745294911801780773.33802092
    5607.7877468530.26162747115.116422761.46502775799.8959417399.8495109111521820814.35565621
    5707.7870686850.4893216.59872192.8664459599.9479166799.8416468711561860770.40734526
    5707.7870686850.09201082212.585916291.46502729310099.8405103712031860803.10132539
    5707.7870686850.32935333315.531692052.73304452410099.7690531211661710678.52829794
    5707.7870686850.4246620.399007630.86662450710099.5973154410771630739.2973683
    5607.7870686850.204529.5992599693.93303012699.8437599.8811645910761790823.40318851
    5607.7867296010.0846266676.1328597363.26641442599.7916666799.8620689710741770683.14724543
    5607.7867296010.26827333353.862543033.39973972199.8958333399.313786659561810859.60085692
    5607.7860514320.20645333349.862801951.19990699999.9479166799.466382078421750852.80056872
    5607.7860514320.2396814.465565382.39981729899.9479166799.849624068141850663.41615962
    5607.7853732640.1536333339.4658077540.6666061810099.822852087891880589.54650559
    5607.785034180.26279333315.732326511.99987201310099.87293527721770704.02161114
    5407.755194770.073024.0663526363.59972200599.9479166799.86301377501790791.20556457
    5507.755194770.0686333335.1996013222.93310843899.9479166799.862164027771740602.08716856
    5507.755194770.7089634.064049251.06658471299.5312599.571078437691790558.02378932
    5507.755194770.1237333339.1326296222.13316896399.9479166799.85528227641790558.3569763
    5507.755194770.0822333336.2661841450.99992300210099.848254937811820578.48878736
    5507.755194770.35606666713.065273622.06644633710099.842519697931830748.98680795
    5507.755194770.232369.7327783320.99994297910099.845081338201730845.68510913
    5507.755194770.41292666723.66506045.26630921799.5312599.877074377321780579.96063525
    5507.755194770.45104666728.397759981.73319661899.6354166799.752321987421780669.68050882
    5407.755194770.4293626.664654523.33308181599.6354166799.452554747341830598.95480223
    5607.755194770.19160666721.73166322.46647711199.7916666799.883040948251910620.41898587
    5607.755194770.1016333338.5993330082.66645984899.7916666799.889746428811790560.15655257
    5607.755194770.1141666677.4659851461.1998904799.8958333399.869791678901750554.08275484
    5607.755194770.274349.8660519542.79982555599.2708333399.863387989211830497.76898616
    5607.755194770.41360666720.131781071.79986122199.5833333399.864130438981790619.41890626
    5607.7548556860.4515422.864893165.53290417699.2708333399.582985828851830633.55085897
    592.9331097857.7548556860.18927333332.93082352.93310978599.8958333399.625468169311840664.74933596
    5807.7548556860.75431333346.196404524.99961087998.6458333399.426229519761870731.60972535
    5807.7534993490.17931333329.197778483.33307973599.7916666799.663526249561810757.14239273
    5907.7534993490.2334416.465032045.53278404799.8958333399.883109299841720666.33384744
    5907.7524820960.34608666714.732500295.26636888298.5416666799.858356949801780601.3659969
    5807.7494303391.018834.197398736.59949800197.9166666799.579242649871870815.13799562
    581.3332306317.7494303390.3885819.798474872.93310738799.5833333399.845679019351860780.60653435
    5807.7494303391.47242666729.464395425.73289141699.5833333399.583828789641810776.20683314
    5807.7494303390.31390666716.398730593.93302888299.687599.700419419931850804.3377375
    5807.7494303390.21993333313.598938634.93294832699.7916666799.836199849361900775.40614771
    5807.7494303390.116146.1994330673.93297366699.687599.879807699551930705.93544273
    580.3333129547.7494303392.618436.864412696.1329583598.9062595.439189199931850687.02466039
    5907.7494303390.78969333334.463788821.59986640599.4791666799.417475739761840654.01205435
    5907.7490912541.33227333358.862478073.93305346199.8958333399.305993698531860621.02247534
    5807.748752170.4248227.264575151.5998772799.8437599.870466328891830611.01979413
    5807.7484130860.2570629.931004682.53313625399.8437599.884192249581790649.28281867
    5807.7484130860.8668632.464155880.79993813399.7916666799.889258039811790716.21127472
    5807.7484130860.1545666679.9989841583.26633482599.8437599.7866287310321710821.449878511
    5807.7484130860.0942533338.1995694961.86656866610099.848942610291750704.4963458
    5807.7484130860.1883666679.7325313413.46638102610099.8736576110131770808.93334135
    5807.7484130860.23931333312.199119890.66661857399.687599.8824911910071690778.67715530
    5807.7484130860.41212666722.864924432.79978666599.5312599.560922068011700857.80130497
    5807.7484130860.71779333335.530588440.79993820199.4270833399.884593198101770839.73512672
    5807.7484130860.36432666736.397032142.33314308699.7916666799.559794578211870901.19318222
    5807.7484130860.27461333329.463797160.79992209510099.510403928691840701.53167722
    5807.7484130860.64308666725.731984383.39982177199.5312599.881866518601880598.9019375
    5807.7484130860.11000666715.132163740.86659968599.9479166799.858356948981900622.88518934
    5807.7484130860.48568666721.931637732.53313748899.7395833399.614643558521970682.1472614
    5807.7484130860.210158.528867111.39989317799.9479166799.663299668821870728.07777522
    5807.7484130860.69217333398.658572972.99975390899.6354166798.450536358641930839.39780462
    583.9997036087.7480740021.0711293.326417532.06651353199.37599.389499398831960946.79650594
    5807.7480740020.512273.925697522.59973147399.6354166799.107142868681910967.16676765
    571.4665935997.7457004120.44715333381.129291361.73324698199.0104166799.883855984916901036.7483488
    570.06666157.7185736760.54448666717.798620413.46639798399.7395833399.8617831441710659.94884675
    572.3998174087.7172173390.37283333322.331634221.73320146299.5312599.875930522191740626.2856825
    5707.7158610030.49085333320.531746084.26633684899.1145833399.848828422651770691.61324
    5707.7158610030.4154636.663460051.59986007599.7916666799.377722463261840686.13998973
    570.6666216277.7158610030.31435333327.331486693.33310813399.7395833399.823165343771840684.88705915
    5707.7158610030.7674220.930984732.26641235999.5312599.867197884611880611.79801865
    5707.7158610030.9410850.197895913.19986587599.2708333399.212598435131930691.37102064
    5707.7158610031.066376.79413232.9331092298.2812599.342498514831890860.66757122
    5607.7158610030.5318637.663721446.06619230398.4895833399.832355414831870739.14220068
    5607.7158610030.57489333348.396337983.19975788398.9062599.394987044941870727.61161036
    5607.7145046660.19531333318.998526912.79978291310099.859550565511840708.94503058
    570.1333230267.7056884770.1250217.398654911.79986085399.9479166799.865681675981980719.07774136
    570.0666597067.7050103080.073526.3993317583.6662838299.8958333399.892473126311710742.92242134
    5707.704332140.070665.4663965151.66658430310099.881936256541710738.09685624
    572.7331228277.6995849610.27660666710.999152843.79970734699.2708333399.681933846851620785.67282065
    5707.6928032770.52374666717.465332763.99969452498.2291666799.84012796731590884.0658134
    5607.6928032770.10033333311.932404284.26633449199.5833333399.876695446601620807.40380234
    560.5999536787.6928032770.0457666673.5330605474.79962942299.7395833399.850523176721600850.40100752
    560.066661497.6928032770.144368.26602470811.6657606898.8020833399.531981286991690772.00671099
    560.7999074057.6928032770.21070666716.664737613.59958332499.8958333399.873096457531640845.90208129
    5607.6921251090.20176666746.198263774.73315545199.4791666799.304589718071640993.2293397
    550.8665998417.6782226560.059610.199213512.79978410199.687599.891008178551720761.14130646
    550.9332481167.6260036890.0614933336.7327185494.3329376899.8958333399.833887048871680852.52215373
    550.8666121647.6232910160.0281133332.6664989663.79976102799.9479166799.855491338901760694.68964324
    550.8666000127.6056586370.1594733339.1326308966.19952316399.7916666799.840764339301680740.07641032
    550.8666000967.5805664060.105427.8660624062.0665079299.8437599.835390959881640592.02118842
    550.8665755647.5412326390.38283333315.864998794.3328778299.687599.6674979210601810622.93451127
    550.0666633557.5222439240.0639466676.6663355092.59987084999.7395833399.8338870411351780594.90378091
    550.2666462457.5202094180.3806612.1324041362.4618828190.6770833399.8438719811961900697.61323761
    550.2666459867.5188530820.0611666678.1993640693.86636679799.3229166799.8883305412451900650.34955985
    540.5999532857.5147840710.0396466675.1995951342.53313609199.7916666799.8957247112671870881.13139087
    540.1999847767.5147840710.0580533332.6664636769.66593082599.8958333399.8648648612811860613.75327664
    5407.5127495660.2477666678.7993293143.93303355799.3229166799.854014613061850580.35576528
    5407.5059678820.0558333335.2661725911.39986866399.8437599.8828353813871860747.596526711
    5407.5052897140.189269.5994099842.73316534399.8958333399.7026022314151900617.36205469
    5507.4998643660.0159733334.7329701551.19990792710099.8736576114631870815.47075934
    5507.4971516930.0343933334.8662914072.53313799310099.8166819414741950633.41782136
    5507.4971516930.033765.0662756681.13324587399.9479166799.8871331815241970656.549329614
    5507.4917263450.036783.7997081882.19983105699.9479166799.8417721515391870742.87628155
    5507.4917263450.0075266671.7998606031.19990706910099.8613998615711870726.87703762
    5507.4917263450.0556666674.2662699212.86640010310099.8882681616161990741.39771968
    550.199987847.4883355030.65949333316.065689842.06654101799.8958333399.8732572916221880690.89132576
    5507.4805365670.2674266675.7995543153.06643101710099.8535871216651800681.61428593
    5507.479180230.4664416.665381251.333230599.7395833399.8430141317481910789.53910236
    5507.4785020620.0201466673.0664296482.73312207810099.8529411818361890809.47076563
    5507.4785020620.009982.1331678681.66653739799.9479166799.8685075618841790804.53759378
    5507.4771457250.0570533333.1997535222.26649207899.9479166799.8191681719331810807.67111824
    5507.4757893880.0234266672.9996561161.19986244610099.8781973219631820759.44626957
    5507.475111220.0280333332.7998909332.53323465499.9479166799.8878923819731680656.64108797
    5507.4744330510.0032066670.4666304431.19990685399.8958333399.8773758420221620758.207807910
    5507.4269612630.63813333318.331930424.19967860698.8541666799.6273291919901620804.00513772
    5507.4208577470.267289.7325855982.19983099199.5833333399.8242530821181550691.346885211
    5507.4201795790.022122.9331070382.73312246710099.8773006121521670692.14659946
    5707.4201795790.1730066673.8663688441.53321523199.9479166799.8456790121701580638.55081315
    5707.4201795790.0075733330.7332640242.3997731710099.8527245921831680642.60592688
    5707.4198404950.013440.9332763042.26652816799.8958333399.8542274122411720690.22448944
    5707.4198404950.0046666670.3999692413.73304624810099.8541210822641620608.88650774
    5707.4198404950.0022733330.1999848731.59987898199.8958333399.8888888922971680689.54784097
    5707.4198404950.0153133332.799782694.26633552899.7395833399.8417721523041660644.08334173
    5707.4195014110.003221.1332461851.59987696710099.8870056523291570839.0021467
    5707.4130588110.0038333330.6666157232.66646289110099.8324958123431620578.95575534
    5707.4130588110.00440.7332578241.5331754510099.8663101623991630608.47067443
    5707.4130588110.0063466670.5333055572.33321181399.8958333399.8670212824181640662.83214448
    5707.4130588110.0020666670.3999691291.46655347299.8437599.8461538524451560614.08593563
    5707.4110243060.006581.5998649762.66644162799.8958333399.8663994724671550613.28157435
    5707.4106852210.009543.0664538511.26657876599.8437599.8141263925091530703.15120059
    5707.4100070530.005940.6666139192.7331170799.8958333399.8683344325351430864.19828517
    5707.4022081160.0135466670.9999250122.53314336499.9479166799.8518518525711490702.61397516
    5707.3981391060.0137733331.2665496582.53309931510099.8642226725871380806.05886637
    5807.3981391060.0017666670.0666625161.26658779510099.87187726061280784.01784526
    5707.3981391060.0056133330.133323112.26649286410099.8823529426121330714.278564
    5807.3981391060.003960.1999845061.13324553199.8958333399.8916576426441140779.40627977
    5707.3981391060.00251333302.59980082399.8958333399.817351626571020802.87182332
    5707.3981391060.0140866673.3997353142.79978202399.8437599.8642226726781020801.53759632
    5707.3981391060.08760666725.931353493.33307885510099.4619523426371130671.54872762
    5807.3961046010.0022866670.1333193180.99989488610099.8750780826681040714.2582476
  17. aaronsandy New Member

    I m sorry for the trouble that will be caused by above post.I have submitted the log file details completly.So moderator pls delete the above if it seems to be unnecessary
  18. aaronsandy New Member

    Some thing is there which is taking the maximum processor time.
    Can I determine which db (or objects) is taking maximum time by PM.
  19. Adriaan New Member

    Did you overlook my previous post about available diskspace?
  20. deepakontheweb New Member

    Check Processes running on server from TaskManager, there may be any new/old Exe other then SQL running on it to raise CPU utilization all in sudden.<br /><br />Run below trace to find out, which database is working most.. <br />SQLServer<img src='/community/emoticons/emotion-2.gif' alt=':D' />atabases(db_name)Transactions/sec <br /><br />Another, check master..sysprocesses table for CPU and other running process on SQL Server<br /><br />Deepak Kumar<br /><br />--An eye for an eye and everyone shall be blind
  21. deepakontheweb New Member

    I checked performance counter's data.. its avg is like: -

    Disk queue length 0.281985768
    Disk Read 17.40145323
    Disk Writes 3.272217778
    Processor 99.65077937
    Buffer cache hit ratio 99.73449125
    Processor queue length5.007194245


    there is almost normal activity on disk.. needless to say its not only SQL which is using all CPU. findout what all is running on server.



    Deepak Kumar

    --An eye for an eye and everyone shall be blind
  22. satya Moderator

    WIth regard to this error refer to this KBAhttp://support.microsoft.com/Default.aspx?kbid=839397 to get the fix if the OS falls under as per specifications.

    quote:Originally posted by aaronsandy

    I set up the performance monitor for the above counters but it failed to capture.When I saw the event viewer I got the following error .
    The Collect Procedure for the "Spooler" service in DLL "C:WINDOWSsystem32winspool.drv" generated an exception or returned an invalid status. Performance data returned by counter DLL will be not be returned in Perf Data Block. The exception or status code returned is the first DWORD in the attached data.

    What to do now?


    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

Share This Page