database server suddenly running slow

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.

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

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.

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


Deepak Kumar

--An eye for an eye and everyone shall be blind

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

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?

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

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

I dont have much idea but i think spooler is printing service

check this microsoft link too.
http://support.microsoft.com/default.aspx?scid=kb;en-us;839397

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

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

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.

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

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

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

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

Did you overlook my previous post about available diskspace?

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='' />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

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