| Author |
Topic |
|
sqlfresher2k7
Aged Yak Warrior
623 Posts |
 Posted - 2011-10-27 : 21:09:55
|
| I have run the sp_who2 on the server the CPU resluts shows below..Command CPUTIME---------- ---------LAZY WRITER 42624965LAZY WRITER 3461227Please suggest to correct this. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-10-28 : 05:41:09
|
| what do you mean by correcting this? can you elaborate?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
sqlfresher2k7
Aged Yak Warrior
623 Posts |
Posted - 2011-10-28 : 09:46:04
|
| I am very new to DBA activities..I am not sure about why lazy writer is showing up cputime more.Do i need to explicit do any thing to improve the performance.server performance of system is slow.. |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-10-28 : 11:16:25
|
| Do you have a known performance problem involving the lazy writer? If not, leave it aloneThat's the cumulative CPU since SQL last started. If SQL's been up a while, that's probably not even that high.--Gail ShawSQL Server MVP |
|
|
|
sqlfresher2k7
Aged Yak Warrior
623 Posts |
Posted - 2011-10-28 : 12:27:55
|
| There is some performance going on for bulk insert and delete.The server service was restarted a week back.. |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-10-28 : 13:12:14
|
| Then that CPU usage is what has been used since then. So 11 hours over a course of 7 days.--Gail ShawSQL Server MVP |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
sqlfresher2k7
Aged Yak Warrior
623 Posts |
Posted - 2011-10-28 : 14:26:35
|
| The cumulative CPU usage number should be fine of 11 hours over a course of 7 days.. |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-10-28 : 15:32:25
|
| It is a little higher than I would probably like, which may indicate potential memory bottlenecks, but a lot more investigation will be needed to decide that.--Gail ShawSQL Server MVP |
|
|
|
sqlfresher2k7
Aged Yak Warrior
623 Posts |
Posted - 2011-10-28 : 17:11:47
|
| Please let me know where should i start to investigate for potential memory bottlenecks.Can you provide me details. |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2011-10-28 : 17:28:07
|
| First of all.1) Are you actually sure you have a problem?2) If you do what are the symtoms? What is slow and is causing you concern?3) Don't mess with stuff you don't understand on production. Do you have any kind of testing environment that you can play with until you get familiar with the db admin?Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
sqlfresher2k7
Aged Yak Warrior
623 Posts |
Posted - 2011-10-28 : 21:21:57
|
| Thanks Charlie.1) There is a performance issue.2)There is a delete which is causing problem and sometimes system is very slow and not able to connect.3)I don't have any test environment to verify.I dont want to mess with production but i have investigate. |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-10-29 : 06:58:35
|
| If there is a delete causing problems, then focus your effort on the delete. Looking at everything and trying to tweak without reason is something I call 'shotgun tuning', it's about as effective as using a shotgun to kill a mosquito from 20m away. You NEED a test/dev environment to fiddle and test. It can be your local machine with a cut down version of prod, but you have to have something to test stuff out on.--Gail ShawSQL Server MVP |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2011-10-29 : 07:04:49
|
| OK - have you sniffed (or do you just know) the details of the delete that is causing the problem? If it's a really expensive query then the solution may be as simple as rewriting the delete to be more efficient. Or if the delete is deleting a large amount of data then maybe batching up the delete will fix your problem too. On the other hand -- if you are as unsure as you are about how to find the problem, how sure are you that you have good indexes in place?Another solution may be as simple as just tuning some indexes.Basically, more info please and focus on the actual problem you are experiencing.Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
sqlfresher2k7
Aged Yak Warrior
623 Posts |
Posted - 2011-10-29 : 09:46:44
|
| Thanks I figured out the cause,delete statement which is causing problem and rewritten the code which helped performance improved drastically.Again thanks for your help |
|
|
|
|
Performance