Hi
I'm running SQL server 2005 (64 bit) on windows 2003 server (64 bit) in a
clustered environment (both machines in the cluster are identical) and the
machines have 12GB or RAM each and 8 dual core CPU's (3.Ghz) (DL580 G4's) -
presenting 16 cpus to the OS . We moved this instance from an 8 way xeon
cluster with 4GB memory, which pottered along quite happily at 50-70% CPU.
However the new cluster - which would have expected to manage the workload
much better epsecially in terms of CPU is constantly at about 80-90% cpu on
all processors, and frequntly in the high 90's - I've seen a couple of KB
articles relating to problems like this - however none seems to quite fit out
situation.
Anyone got any ideas ?I suspect that the lower CPU usage was due to slower disks and less memory.
In that case, the CPU has to wait for data. If it's not in cache, then it
has to go to disk. During this time, the CPU waits, and CPU usage goes to
0.
In your newer system, you have more cache, which reduces the need to go to
disk as often. Because the data is already available, then the CPU can be
put to work right away to service the query.
Has query performance suffered or are you just concerned that you see higher
CPU usage?
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Rabbers" <Rabbers@.discussions.microsoft.com> wrote in message
news:ACEBF5A1-099B-4C13-A0CF-9677E48C2F4C@.microsoft.com...
Hi
I'm running SQL server 2005 (64 bit) on windows 2003 server (64 bit) in a
clustered environment (both machines in the cluster are identical) and the
machines have 12GB or RAM each and 8 dual core CPU's (3.Ghz) (DL580 G4's) -
presenting 16 cpus to the OS . We moved this instance from an 8 way xeon
cluster with 4GB memory, which pottered along quite happily at 50-70% CPU.
However the new cluster - which would have expected to manage the workload
much better epsecially in terms of CPU is constantly at about 80-90% cpu on
all processors, and frequntly in the high 90's - I've seen a couple of KB
articles relating to problems like this - however none seems to quite fit
out
situation.
Anyone got any ideas ?|||Rabbers
Can you tell us what did you check so far? Long running queries? Blocking
/Loking?
select top 50
sum(qs.total_worker_time) as total_cpu_time,
sum(qs.execution_count) as total_execution_count,
count(*) as number_of_statements,
qs.plan_handle
from
sys.dm_exec_query_stats qs
group by qs.plan_handle
order by sum(qs.total_worker_time) desc
"Rabbers" <Rabbers@.discussions.microsoft.com> wrote in message
news:ACEBF5A1-099B-4C13-A0CF-9677E48C2F4C@.microsoft.com...
> Hi
> I'm running SQL server 2005 (64 bit) on windows 2003 server (64 bit) in a
> clustered environment (both machines in the cluster are identical) and the
> machines have 12GB or RAM each and 8 dual core CPU's (3.Ghz) (DL580
> G4's) -
> presenting 16 cpus to the OS . We moved this instance from an 8 way xeon
> cluster with 4GB memory, which pottered along quite happily at 50-70% CPU.
> However the new cluster - which would have expected to manage the workload
> much better epsecially in terms of CPU is constantly at about 80-90% cpu
> on
> all processors, and frequntly in the high 90's - I've seen a couple of KB
> articles relating to problems like this - however none seems to quite fit
> out
> situation.
> Anyone got any ideas ?
>|||I agree with Tom in that you most likely had other bottlenecks that
prevented your CPU's from being constantly higher in the last system. High
CPU usage usually means you are getting a lot of work done and is not
necessarily a bad thing. But it can also indicate you have poorly optimized
queries and are getting a lot of compiles or recompiles.
--
Andrew J. Kelly SQL MVP
"Rabbers" <Rabbers@.discussions.microsoft.com> wrote in message
news:ACEBF5A1-099B-4C13-A0CF-9677E48C2F4C@.microsoft.com...
> Hi
> I'm running SQL server 2005 (64 bit) on windows 2003 server (64 bit) in a
> clustered environment (both machines in the cluster are identical) and the
> machines have 12GB or RAM each and 8 dual core CPU's (3.Ghz) (DL580
> G4's) -
> presenting 16 cpus to the OS . We moved this instance from an 8 way xeon
> cluster with 4GB memory, which pottered along quite happily at 50-70% CPU.
> However the new cluster - which would have expected to manage the workload
> much better epsecially in terms of CPU is constantly at about 80-90% cpu
> on
> all processors, and frequntly in the high 90's - I've seen a couple of KB
> articles relating to problems like this - however none seems to quite fit
> out
> situation.
> Anyone got any ideas ?
>|||Interesting - I had not thought of it in those terms, but my team are also
telling me that query performance has suffered. Incidently the disk sub
system is the same as on the old sub system it is an EMC CX300 SAN dual path
in active/active configuration, There does not appear to be any IO waiting
going on so I'm not sure this is oir was an issue. Workload is unchanged.
No blocking or locking going on. Although some big numbers from Uri's query.
14131421676 9670 32 0x0500050053D8DE7840039DC5000000000000000000000000
8347158363 30999 9 0x050005009014281C40C3ADB5000000000000000000000000
2376266762 1270 7 0x05000B00A39BCF6240C3AEE7000000000000000000000000
2202179405 4480 32 0x050005001AB4EA7740C3EE07010000000000000000000000
1997588436 2379 8 0x05000B00F82EF35F4003F5A9020000000000000000000000
1597112198 30677 3 0x05000500A3463C3F40A307AC000000000000000000000000
1456992451 19956 3 0x05000500541E7401406392BC000000000000000000000000
984281776 43805 13 0x05000500052FD03740A3A1C6000000000000000000000000
962083727 112113 18 0x05000700942C7D3E406339EA000000000000000000000000
843149798 577 7 0x05000B003153E7604023B6E9000000000000000000000000
752809408 5511 1 0x0500050088E47D5740631FC1000000000000000000000000
730983354 594 7 0x050005005AC2F3344083D8D7000000000000000000000000
538219354 3533651 5 0x050005009C23030F400304CF000000000000000000000000
405080101 463791 2 0x050005002ADB1A0D40233ADC000000000000000000000000
404464827 8912 3 0x05000500DAA5E13A406343BC000000000000000000000000
354858971 12888 9 0x050005006B2FB9524083CBDB000000000000000000000000
354788856 20840 2 0x0500070022E4943C40C3B2AF000000000000000000000000
311644988 223882 2 0x05000500D5F2636C40234B8A000000000000000000000000
278456532 1001 2 0x05000500354FCD53406346E1000000000000000000000000
270333901 837513 1 0x05000500286B5F2B4003AFAB000000000000000000000000
219689259 548657 3 0x050007008240FE2340C3F5DE000000000000000000000000
209456887 313 36 0x05000500C520C77A40639DBF010000000000000000000000
203146055 70 6 0x05000B006A77DB614003F289010000000000000000000000
187539837 8882 3 0x05000B0002B2A34140A38900010000000000000000000000
183248652 158 1 0x060005006B51F52940439A48020000000000000000000000
151190159 4904 12 0x050005003EA8575C40437BDD000000000000000000000000
148130918 2873 1 0x05000B0029EA182840A3DFE1020000000000000000000000
129011430 591 10 0x05000500FBC78E0540A3AB0A020000000000000000000000
122411767 9145 38 0x05000500A9B1977E4023BCD7000000000000000000000000
103452720 1947 4 0x05000500FC0D240E40A34CD7000000000000000000000000
99285280 433203 1 0x05000500162A4D6D404339BE000000000000000000000000
97455900 77 1 0x060005003E8B77394063E000030000000000000000000000
96981407 90 1 0x0600050061E19A1B40A3C087000000000000000000000000
94693286 9 9 0x05000600309B09704003E311030000000000000000000000
89323421 74 1 0x060005009BAE130440A3B7D8020000000000000000000000
75662449 1117974 2 0x05000500D547F70F40837AD0000000000000000000000000
74451891 84 4 0x06000B00CFFD2D3840C3FBF9000000000000000000000000
72410847 80 4 0x06000B0047B9FB1940A3FB6F010000000000000000000000
72244371 58 1 0x0600050005018F074083E895020000000000000000000000
70044701 5036752 1 0x05000B00B0B71B6A40A374E7000000000000000000000000
68605351 42 7 0x060005003488F60C4003CEE5020000000000000000000000
65495930 158 1 0x06000500FD821E3240233246020000000000000000000000
65255754 7922 8 0x05000500F9E6D05040C3D2D9000000000000000000000000
62199510 107 1 0x06000500A41D2C1E40E34E1A020000000000000000000000
60560005 34 2 0x06000B00D37CFA2240E3FAA7000000000000000000000000
56735759 127554 9 0x05000500FB72FB614043C1AB000000000000000000000000
54106604 110763 6 0x05000500735F631740E3A1C6000000000000000000000000
53255770 6 6 0x06000500C5224C2640833421020000000000000000000000
50681958 202 8 0x05000500B7F7095F40A337BA000000000000000000000000
50605253 1222 9 0x05000B00C3949C6940C30FC2000000000000000000000000
"Tom Moreau" wrote:
> I suspect that the lower CPU usage was due to slower disks and less memory.
> In that case, the CPU has to wait for data. If it's not in cache, then it
> has to go to disk. During this time, the CPU waits, and CPU usage goes to
> 0.
> In your newer system, you have more cache, which reduces the need to go to
> disk as often. Because the data is already available, then the CPU can be
> put to work right away to service the query.
> Has query performance suffered or are you just concerned that you see higher
> CPU usage?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Rabbers" <Rabbers@.discussions.microsoft.com> wrote in message
> news:ACEBF5A1-099B-4C13-A0CF-9677E48C2F4C@.microsoft.com...
> Hi
> I'm running SQL server 2005 (64 bit) on windows 2003 server (64 bit) in a
> clustered environment (both machines in the cluster are identical) and the
> machines have 12GB or RAM each and 8 dual core CPU's (3.Ghz) (DL580 G4's) -
> presenting 16 cpus to the OS . We moved this instance from an 8 way xeon
> cluster with 4GB memory, which pottered along quite happily at 50-70% CPU.
> However the new cluster - which would have expected to manage the workload
> much better epsecially in terms of CPU is constantly at about 80-90% cpu on
> all processors, and frequntly in the high 90's - I've seen a couple of KB
> articles relating to problems like this - however none seems to quite fit
> out
> situation.
> Anyone got any ideas ?
>
>|||You may want to start profiling for long-running queries and/or those
queries that are using a lot of CPU.
BTW what service pack are you running?
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Rabbers" <Rabbers@.discussions.microsoft.com> wrote in message
news:105535AF-20A9-41A4-A320-AD35C4CF28CA@.microsoft.com...
Interesting - I had not thought of it in those terms, but my team are also
telling me that query performance has suffered. Incidently the disk sub
system is the same as on the old sub system it is an EMC CX300 SAN dual path
in active/active configuration, There does not appear to be any IO waiting
going on so I'm not sure this is oir was an issue. Workload is unchanged.
No blocking or locking going on. Although some big numbers from Uri's query.
14131421676 9670 32 0x0500050053D8DE7840039DC5000000000000000000000000
8347158363 30999 9 0x050005009014281C40C3ADB5000000000000000000000000
2376266762 1270 7 0x05000B00A39BCF6240C3AEE7000000000000000000000000
2202179405 4480 32 0x050005001AB4EA7740C3EE07010000000000000000000000
1997588436 2379 8 0x05000B00F82EF35F4003F5A9020000000000000000000000
1597112198 30677 3 0x05000500A3463C3F40A307AC000000000000000000000000
1456992451 19956 3 0x05000500541E7401406392BC000000000000000000000000
984281776 43805 13 0x05000500052FD03740A3A1C6000000000000000000000000
962083727 112113 18 0x05000700942C7D3E406339EA000000000000000000000000
843149798 577 7 0x05000B003153E7604023B6E9000000000000000000000000
752809408 5511 1 0x0500050088E47D5740631FC1000000000000000000000000
730983354 594 7 0x050005005AC2F3344083D8D7000000000000000000000000
538219354 3533651 5 0x050005009C23030F400304CF000000000000000000000000
405080101 463791 2 0x050005002ADB1A0D40233ADC000000000000000000000000
404464827 8912 3 0x05000500DAA5E13A406343BC000000000000000000000000
354858971 12888 9 0x050005006B2FB9524083CBDB000000000000000000000000
354788856 20840 2 0x0500070022E4943C40C3B2AF000000000000000000000000
311644988 223882 2 0x05000500D5F2636C40234B8A000000000000000000000000
278456532 1001 2 0x05000500354FCD53406346E1000000000000000000000000
270333901 837513 1 0x05000500286B5F2B4003AFAB000000000000000000000000
219689259 548657 3 0x050007008240FE2340C3F5DE000000000000000000000000
209456887 313 36 0x05000500C520C77A40639DBF010000000000000000000000
203146055 70 6 0x05000B006A77DB614003F289010000000000000000000000
187539837 8882 3 0x05000B0002B2A34140A38900010000000000000000000000
183248652 158 1 0x060005006B51F52940439A48020000000000000000000000
151190159 4904 12 0x050005003EA8575C40437BDD000000000000000000000000
148130918 2873 1 0x05000B0029EA182840A3DFE1020000000000000000000000
129011430 591 10 0x05000500FBC78E0540A3AB0A020000000000000000000000
122411767 9145 38 0x05000500A9B1977E4023BCD7000000000000000000000000
103452720 1947 4 0x05000500FC0D240E40A34CD7000000000000000000000000
99285280 433203 1 0x05000500162A4D6D404339BE000000000000000000000000
97455900 77 1 0x060005003E8B77394063E000030000000000000000000000
96981407 90 1 0x0600050061E19A1B40A3C087000000000000000000000000
94693286 9 9 0x05000600309B09704003E311030000000000000000000000
89323421 74 1 0x060005009BAE130440A3B7D8020000000000000000000000
75662449 1117974 2 0x05000500D547F70F40837AD0000000000000000000000000
74451891 84 4 0x06000B00CFFD2D3840C3FBF9000000000000000000000000
72410847 80 4 0x06000B0047B9FB1940A3FB6F010000000000000000000000
72244371 58 1 0x0600050005018F074083E895020000000000000000000000
70044701 5036752 1 0x05000B00B0B71B6A40A374E7000000000000000000000000
68605351 42 7 0x060005003488F60C4003CEE5020000000000000000000000
65495930 158 1 0x06000500FD821E3240233246020000000000000000000000
65255754 7922 8 0x05000500F9E6D05040C3D2D9000000000000000000000000
62199510 107 1 0x06000500A41D2C1E40E34E1A020000000000000000000000
60560005 34 2 0x06000B00D37CFA2240E3FAA7000000000000000000000000
56735759 127554 9 0x05000500FB72FB614043C1AB000000000000000000000000
54106604 110763 6 0x05000500735F631740E3A1C6000000000000000000000000
53255770 6 6 0x06000500C5224C2640833421020000000000000000000000
50681958 202 8 0x05000500B7F7095F40A337BA000000000000000000000000
50605253 1222 9 0x05000B00C3949C6940C30FC2000000000000000000000000
"Tom Moreau" wrote:
> I suspect that the lower CPU usage was due to slower disks and less
> memory.
> In that case, the CPU has to wait for data. If it's not in cache, then it
> has to go to disk. During this time, the CPU waits, and CPU usage goes to
> 0.
> In your newer system, you have more cache, which reduces the need to go to
> disk as often. Because the data is already available, then the CPU can be
> put to work right away to service the query.
> Has query performance suffered or are you just concerned that you see
> higher
> CPU usage?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Rabbers" <Rabbers@.discussions.microsoft.com> wrote in message
> news:ACEBF5A1-099B-4C13-A0CF-9677E48C2F4C@.microsoft.com...
> Hi
> I'm running SQL server 2005 (64 bit) on windows 2003 server (64 bit) in a
> clustered environment (both machines in the cluster are identical) and the
> machines have 12GB or RAM each and 8 dual core CPU's (3.Ghz) (DL580
> G4's) -
> presenting 16 cpus to the OS . We moved this instance from an 8 way xeon
> cluster with 4GB memory, which pottered along quite happily at 50-70% CPU.
> However the new cluster - which would have expected to manage the workload
> much better epsecially in terms of CPU is constantly at about 80-90% cpu
> on
> all processors, and frequntly in the high 90's - I've seen a couple of KB
> articles relating to problems like this - however none seems to quite fit
> out
> situation.
> Anyone got any ideas ?
>
>|||Have already looked at the query side of things - what surpirises me is that
CPU usage was much lower on the former server with the same workload..
Version is 9.00.3050.00 which is SP2
"Tom Moreau" wrote:
> You may want to start profiling for long-running queries and/or those
> queries that are using a lot of CPU.
> BTW what service pack are you running?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Rabbers" <Rabbers@.discussions.microsoft.com> wrote in message
> news:105535AF-20A9-41A4-A320-AD35C4CF28CA@.microsoft.com...
> Interesting - I had not thought of it in those terms, but my team are also
> telling me that query performance has suffered. Incidently the disk sub
> system is the same as on the old sub system it is an EMC CX300 SAN dual path
> in active/active configuration, There does not appear to be any IO waiting
> going on so I'm not sure this is oir was an issue. Workload is unchanged.
> No blocking or locking going on. Although some big numbers from Uri's query.
> 14131421676 9670 32 0x0500050053D8DE7840039DC5000000000000000000000000
> 8347158363 30999 9 0x050005009014281C40C3ADB5000000000000000000000000
> 2376266762 1270 7 0x05000B00A39BCF6240C3AEE7000000000000000000000000
> 2202179405 4480 32 0x050005001AB4EA7740C3EE07010000000000000000000000
> 1997588436 2379 8 0x05000B00F82EF35F4003F5A9020000000000000000000000
> 1597112198 30677 3 0x05000500A3463C3F40A307AC000000000000000000000000
> 1456992451 19956 3 0x05000500541E7401406392BC000000000000000000000000
> 984281776 43805 13 0x05000500052FD03740A3A1C6000000000000000000000000
> 962083727 112113 18 0x05000700942C7D3E406339EA000000000000000000000000
> 843149798 577 7 0x05000B003153E7604023B6E9000000000000000000000000
> 752809408 5511 1 0x0500050088E47D5740631FC1000000000000000000000000
> 730983354 594 7 0x050005005AC2F3344083D8D7000000000000000000000000
> 538219354 3533651 5 0x050005009C23030F400304CF000000000000000000000000
> 405080101 463791 2 0x050005002ADB1A0D40233ADC000000000000000000000000
> 404464827 8912 3 0x05000500DAA5E13A406343BC000000000000000000000000
> 354858971 12888 9 0x050005006B2FB9524083CBDB000000000000000000000000
> 354788856 20840 2 0x0500070022E4943C40C3B2AF000000000000000000000000
> 311644988 223882 2 0x05000500D5F2636C40234B8A000000000000000000000000
> 278456532 1001 2 0x05000500354FCD53406346E1000000000000000000000000
> 270333901 837513 1 0x05000500286B5F2B4003AFAB000000000000000000000000
> 219689259 548657 3 0x050007008240FE2340C3F5DE000000000000000000000000
> 209456887 313 36 0x05000500C520C77A40639DBF010000000000000000000000
> 203146055 70 6 0x05000B006A77DB614003F289010000000000000000000000
> 187539837 8882 3 0x05000B0002B2A34140A38900010000000000000000000000
> 183248652 158 1 0x060005006B51F52940439A48020000000000000000000000
> 151190159 4904 12 0x050005003EA8575C40437BDD000000000000000000000000
> 148130918 2873 1 0x05000B0029EA182840A3DFE1020000000000000000000000
> 129011430 591 10 0x05000500FBC78E0540A3AB0A020000000000000000000000
> 122411767 9145 38 0x05000500A9B1977E4023BCD7000000000000000000000000
> 103452720 1947 4 0x05000500FC0D240E40A34CD7000000000000000000000000
> 99285280 433203 1 0x05000500162A4D6D404339BE000000000000000000000000
> 97455900 77 1 0x060005003E8B77394063E000030000000000000000000000
> 96981407 90 1 0x0600050061E19A1B40A3C087000000000000000000000000
> 94693286 9 9 0x05000600309B09704003E311030000000000000000000000
> 89323421 74 1 0x060005009BAE130440A3B7D8020000000000000000000000
> 75662449 1117974 2 0x05000500D547F70F40837AD0000000000000000000000000
> 74451891 84 4 0x06000B00CFFD2D3840C3FBF9000000000000000000000000
> 72410847 80 4 0x06000B0047B9FB1940A3FB6F010000000000000000000000
> 72244371 58 1 0x0600050005018F074083E895020000000000000000000000
> 70044701 5036752 1 0x05000B00B0B71B6A40A374E7000000000000000000000000
> 68605351 42 7 0x060005003488F60C4003CEE5020000000000000000000000
> 65495930 158 1 0x06000500FD821E3240233246020000000000000000000000
> 65255754 7922 8 0x05000500F9E6D05040C3D2D9000000000000000000000000
> 62199510 107 1 0x06000500A41D2C1E40E34E1A020000000000000000000000
> 60560005 34 2 0x06000B00D37CFA2240E3FAA7000000000000000000000000
> 56735759 127554 9 0x05000500FB72FB614043C1AB000000000000000000000000
> 54106604 110763 6 0x05000500735F631740E3A1C6000000000000000000000000
> 53255770 6 6 0x06000500C5224C2640833421020000000000000000000000
> 50681958 202 8 0x05000500B7F7095F40A337BA000000000000000000000000
> 50605253 1222 9 0x05000B00C3949C6940C30FC2000000000000000000000000
> "Tom Moreau" wrote:
> > I suspect that the lower CPU usage was due to slower disks and less
> > memory.
> > In that case, the CPU has to wait for data. If it's not in cache, then it
> > has to go to disk. During this time, the CPU waits, and CPU usage goes to
> > 0.
> >
> > In your newer system, you have more cache, which reduces the need to go to
> > disk as often. Because the data is already available, then the CPU can be
> > put to work right away to service the query.
> >
> > Has query performance suffered or are you just concerned that you see
> > higher
> > CPU usage?
> >
> > --
> > Tom
> >
> > ----
> > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> > SQL Server MVP
> > Toronto, ON Canada
> > https://mvp.support.microsoft.com/profile/Tom.Moreau
> >
> >
> > "Rabbers" <Rabbers@.discussions.microsoft.com> wrote in message
> > news:ACEBF5A1-099B-4C13-A0CF-9677E48C2F4C@.microsoft.com...
> > Hi
> >
> > I'm running SQL server 2005 (64 bit) on windows 2003 server (64 bit) in a
> > clustered environment (both machines in the cluster are identical) and the
> > machines have 12GB or RAM each and 8 dual core CPU's (3.Ghz) (DL580
> > G4's) -
> > presenting 16 cpus to the OS . We moved this instance from an 8 way xeon
> > cluster with 4GB memory, which pottered along quite happily at 50-70% CPU.
> >
> > However the new cluster - which would have expected to manage the workload
> > much better epsecially in terms of CPU is constantly at about 80-90% cpu
> > on
> > all processors, and frequntly in the high 90's - I've seen a couple of KB
> > articles relating to problems like this - however none seems to quite fit
> > out
> > situation.
> >
> > Anyone got any ideas ?
> >
> >
> >
>|||OK - I'll have a look at the query side of things...
"Andrew J. Kelly" wrote:
> I agree with Tom in that you most likely had other bottlenecks that
> prevented your CPU's from being constantly higher in the last system. High
> CPU usage usually means you are getting a lot of work done and is not
> necessarily a bad thing. But it can also indicate you have poorly optimized
> queries and are getting a lot of compiles or recompiles.
> --
> Andrew J. Kelly SQL MVP
> "Rabbers" <Rabbers@.discussions.microsoft.com> wrote in message
> news:ACEBF5A1-099B-4C13-A0CF-9677E48C2F4C@.microsoft.com...
> > Hi
> >
> > I'm running SQL server 2005 (64 bit) on windows 2003 server (64 bit) in a
> > clustered environment (both machines in the cluster are identical) and the
> > machines have 12GB or RAM each and 8 dual core CPU's (3.Ghz) (DL580
> > G4's) -
> > presenting 16 cpus to the OS . We moved this instance from an 8 way xeon
> > cluster with 4GB memory, which pottered along quite happily at 50-70% CPU.
> >
> > However the new cluster - which would have expected to manage the workload
> > much better epsecially in terms of CPU is constantly at about 80-90% cpu
> > on
> > all processors, and frequntly in the high 90's - I've seen a couple of KB
> > articles relating to problems like this - however none seems to quite fit
> > out
> > situation.
> >
> > Anyone got any ideas ?
> >
> >
>
>
Showing posts with label dual. Show all posts
Showing posts with label dual. Show all posts
Monday, March 19, 2012
Monday, March 12, 2012
high CPU time and sql 2000 getting slow performance
Hi,
I admin a server, dual 3,6 xeon with 4GB ram.
Windows 2000 server + SQL 2000 with SP3a + DNS + IIS
The server have around 800 dynamic sites.
SQL 2000 have 1Gb memory for it.
the problem is:
the CPU time after a SQL restart of service keeps getting higher and higher,
i say in a 24h its aroung 24 also memory starts to grow and stays at 1Gb,
wich is the reserved memory for SQL.
Is it normal the CPU time keeping getting higher? or should refresh? like
dllhost.exe in the webserver.
Is it normal the memory stays at 1Gb or also should refresh?
The problem is with some hours/days sites start to open slow and when i
restart the SQL service, sites start to open in few seconds, but after some
day/days start to be slow again.
Its a very busy server with some portals, a few, others sites dynamic also
but with few hits. But almost 100% of sites uses, except the portals, read
for one of the portals database.
Im not the programmer of the sites, and also i dont make the SQL statements
need for each site, or design the databases.
Tkx in advance,
PVThe first red flag is to have a SQL Server sharing resources with another
application in the same server. It is not the optimal planning. Having said
that, it gets worse if the application is IIS and it is a very busy server
due to the busy portals.
SQL Server behaves somehow like an only child. It wants all CPU and all
possible memory. If you tell SQL Server that it could use from 0 to 1GB, it
will eventually use 1GB and it will keep it just in case.
According with what you describe, you have two demanding applications that
are competeng for the hardware resources on the same machine. This could get
worse if the IIS load increases.
I would look into the shared load between IIS and the SQL Server, meaning
that if the SQL Server is only 25% of the load, DNS users 15%, and IIS takes
the 60% remaining, it may be wise to start planning about getting another
machine to move either the SQL Server or get a bigger IIS Server to handle a
n
increasing load.
Let me know if this helps..
"PV" wrote:
> Hi,
> I admin a server, dual 3,6 xeon with 4GB ram.
> Windows 2000 server + SQL 2000 with SP3a + DNS + IIS
> The server have around 800 dynamic sites.
> SQL 2000 have 1Gb memory for it.
>
> the problem is:
> the CPU time after a SQL restart of service keeps getting higher and highe
r,
> i say in a 24h its aroung 24 also memory starts to grow and stays at 1Gb,
> wich is the reserved memory for SQL.
> Is it normal the CPU time keeping getting higher? or should refresh? like
> dllhost.exe in the webserver.
> Is it normal the memory stays at 1Gb or also should refresh?
> The problem is with some hours/days sites start to open slow and when i
> restart the SQL service, sites start to open in few seconds, but after som
e
> day/days start to be slow again.
> Its a very busy server with some portals, a few, others sites dynamic also
> but with few hits. But almost 100% of sites uses, except the portals, read
> for one of the portals database.
> Im not the programmer of the sites, and also i dont make the SQL statement
s
> need for each site, or design the databases.
> Tkx in advance,
> PV|||Hi,
Tkx for the directions needed to be made.
SQL needs a dedicated server for it.
Its SQL 2000 that "decreases server performance" after some hours/days. And,
as you say, starts the "competition" of resources between SQL and IIS mostly
.
dispite all that sites, IIS doesnt take much amount of processor, except the
portals, one specially, but not all the time like SQL.
Since its SQL that decreases the performance i wanted to know what i asked,
if high cpu time is normal? and use of memory. the answer is positve i
presume.
Is there a way to "recycle" SQL uses of resources? like there is one tool to
IIS 5.0, but not need by the time being at this server since IIS is ok.
Or its prudent to "force" restart once a day/2days to the SQL? i think this
isnt the right way to resolve things.
PS:
we had one server with 1Gb memory with a xeon 2.0 with 500-600 dynamic sites
and only one BD in SQL(512Mb reserved and CPU utilization restrain to 50%
only), a forum and DNS. And the server worked EXCELLENT with no problems for
months and months without no problem always online and keeping increasing
amount of sites (10) per month. the server never reached the 1Gb use of
memory.
but no PORTALS... neither SQL to the portals.
Tkx again for the answer,
PV
"Edgardo Valdez, MCSD, MCDBA" wrote:
[vbcol=seagreen]
> The first red flag is to have a SQL Server sharing resources with another
> application in the same server. It is not the optimal planning. Having sai
d
> that, it gets worse if the application is IIS and it is a very busy server
> due to the busy portals.
> SQL Server behaves somehow like an only child. It wants all CPU and all
> possible memory. If you tell SQL Server that it could use from 0 to 1GB, i
t
> will eventually use 1GB and it will keep it just in case.
> According with what you describe, you have two demanding applications that
> are competeng for the hardware resources on the same machine. This could g
et
> worse if the IIS load increases.
> I would look into the shared load between IIS and the SQL Server, meaning
> that if the SQL Server is only 25% of the load, DNS users 15%, and IIS tak
es
> the 60% remaining, it may be wise to start planning about getting another
> machine to move either the SQL Server or get a bigger IIS Server to handle
an
> increasing load.
> Let me know if this helps..
> "PV" wrote:
>|||Using 1 GB of memory isn't unusual...SQL uses as much memory as it can
get ahold of. It's also not unusual to have high CPU times.
Shut down all unecessary services (including IIS, FTP, etc.), enable
the /3GB switch and monitor your I/O & CPU usage. If you still run
into problems, run Profiler to determine what queries, SPs or other
operations are utilitizing your CPU.
Above all, run SQL Server on its own server. Don't mix in IIS
On Tue, 4 Apr 2006 06:42:05 -0700, PV <PV@.discussions.microsoft.com>
wrote:
>Hi,
>I admin a server, dual 3,6 xeon with 4GB ram.
>Windows 2000 server + SQL 2000 with SP3a + DNS + IIS
>The server have around 800 dynamic sites.
>SQL 2000 have 1Gb memory for it.
>
>the problem is:
>the CPU time after a SQL restart of service keeps getting higher and higher
,
>i say in a 24h its aroung 24 also memory starts to grow and stays at 1Gb,
>wich is the reserved memory for SQL.
>Is it normal the CPU time keeping getting higher? or should refresh? like
>dllhost.exe in the webserver.
>Is it normal the memory stays at 1Gb or also should refresh?
>The problem is with some hours/days sites start to open slow and when i
>restart the SQL service, sites start to open in few seconds, but after some
>day/days start to be slow again.
>Its a very busy server with some portals, a few, others sites dynamic also
>but with few hits. But almost 100% of sites uses, except the portals, read
>for one of the portals database.
>Im not the programmer of the sites, and also i dont make the SQL statements
>need for each site, or design the databases.
>Tkx in advance,
>PV
I admin a server, dual 3,6 xeon with 4GB ram.
Windows 2000 server + SQL 2000 with SP3a + DNS + IIS
The server have around 800 dynamic sites.
SQL 2000 have 1Gb memory for it.
the problem is:
the CPU time after a SQL restart of service keeps getting higher and higher,
i say in a 24h its aroung 24 also memory starts to grow and stays at 1Gb,
wich is the reserved memory for SQL.
Is it normal the CPU time keeping getting higher? or should refresh? like
dllhost.exe in the webserver.
Is it normal the memory stays at 1Gb or also should refresh?
The problem is with some hours/days sites start to open slow and when i
restart the SQL service, sites start to open in few seconds, but after some
day/days start to be slow again.
Its a very busy server with some portals, a few, others sites dynamic also
but with few hits. But almost 100% of sites uses, except the portals, read
for one of the portals database.
Im not the programmer of the sites, and also i dont make the SQL statements
need for each site, or design the databases.
Tkx in advance,
PVThe first red flag is to have a SQL Server sharing resources with another
application in the same server. It is not the optimal planning. Having said
that, it gets worse if the application is IIS and it is a very busy server
due to the busy portals.
SQL Server behaves somehow like an only child. It wants all CPU and all
possible memory. If you tell SQL Server that it could use from 0 to 1GB, it
will eventually use 1GB and it will keep it just in case.
According with what you describe, you have two demanding applications that
are competeng for the hardware resources on the same machine. This could get
worse if the IIS load increases.
I would look into the shared load between IIS and the SQL Server, meaning
that if the SQL Server is only 25% of the load, DNS users 15%, and IIS takes
the 60% remaining, it may be wise to start planning about getting another
machine to move either the SQL Server or get a bigger IIS Server to handle a
n
increasing load.
Let me know if this helps..
"PV" wrote:
> Hi,
> I admin a server, dual 3,6 xeon with 4GB ram.
> Windows 2000 server + SQL 2000 with SP3a + DNS + IIS
> The server have around 800 dynamic sites.
> SQL 2000 have 1Gb memory for it.
>
> the problem is:
> the CPU time after a SQL restart of service keeps getting higher and highe
r,
> i say in a 24h its aroung 24 also memory starts to grow and stays at 1Gb,
> wich is the reserved memory for SQL.
> Is it normal the CPU time keeping getting higher? or should refresh? like
> dllhost.exe in the webserver.
> Is it normal the memory stays at 1Gb or also should refresh?
> The problem is with some hours/days sites start to open slow and when i
> restart the SQL service, sites start to open in few seconds, but after som
e
> day/days start to be slow again.
> Its a very busy server with some portals, a few, others sites dynamic also
> but with few hits. But almost 100% of sites uses, except the portals, read
> for one of the portals database.
> Im not the programmer of the sites, and also i dont make the SQL statement
s
> need for each site, or design the databases.
> Tkx in advance,
> PV|||Hi,
Tkx for the directions needed to be made.
SQL needs a dedicated server for it.
Its SQL 2000 that "decreases server performance" after some hours/days. And,
as you say, starts the "competition" of resources between SQL and IIS mostly
.
dispite all that sites, IIS doesnt take much amount of processor, except the
portals, one specially, but not all the time like SQL.
Since its SQL that decreases the performance i wanted to know what i asked,
if high cpu time is normal? and use of memory. the answer is positve i
presume.
Is there a way to "recycle" SQL uses of resources? like there is one tool to
IIS 5.0, but not need by the time being at this server since IIS is ok.
Or its prudent to "force" restart once a day/2days to the SQL? i think this
isnt the right way to resolve things.
PS:
we had one server with 1Gb memory with a xeon 2.0 with 500-600 dynamic sites
and only one BD in SQL(512Mb reserved and CPU utilization restrain to 50%
only), a forum and DNS. And the server worked EXCELLENT with no problems for
months and months without no problem always online and keeping increasing
amount of sites (10) per month. the server never reached the 1Gb use of
memory.
but no PORTALS... neither SQL to the portals.
Tkx again for the answer,
PV
"Edgardo Valdez, MCSD, MCDBA" wrote:
[vbcol=seagreen]
> The first red flag is to have a SQL Server sharing resources with another
> application in the same server. It is not the optimal planning. Having sai
d
> that, it gets worse if the application is IIS and it is a very busy server
> due to the busy portals.
> SQL Server behaves somehow like an only child. It wants all CPU and all
> possible memory. If you tell SQL Server that it could use from 0 to 1GB, i
t
> will eventually use 1GB and it will keep it just in case.
> According with what you describe, you have two demanding applications that
> are competeng for the hardware resources on the same machine. This could g
et
> worse if the IIS load increases.
> I would look into the shared load between IIS and the SQL Server, meaning
> that if the SQL Server is only 25% of the load, DNS users 15%, and IIS tak
es
> the 60% remaining, it may be wise to start planning about getting another
> machine to move either the SQL Server or get a bigger IIS Server to handle
an
> increasing load.
> Let me know if this helps..
> "PV" wrote:
>|||Using 1 GB of memory isn't unusual...SQL uses as much memory as it can
get ahold of. It's also not unusual to have high CPU times.
Shut down all unecessary services (including IIS, FTP, etc.), enable
the /3GB switch and monitor your I/O & CPU usage. If you still run
into problems, run Profiler to determine what queries, SPs or other
operations are utilitizing your CPU.
Above all, run SQL Server on its own server. Don't mix in IIS
On Tue, 4 Apr 2006 06:42:05 -0700, PV <PV@.discussions.microsoft.com>
wrote:
>Hi,
>I admin a server, dual 3,6 xeon with 4GB ram.
>Windows 2000 server + SQL 2000 with SP3a + DNS + IIS
>The server have around 800 dynamic sites.
>SQL 2000 have 1Gb memory for it.
>
>the problem is:
>the CPU time after a SQL restart of service keeps getting higher and higher
,
>i say in a 24h its aroung 24 also memory starts to grow and stays at 1Gb,
>wich is the reserved memory for SQL.
>Is it normal the CPU time keeping getting higher? or should refresh? like
>dllhost.exe in the webserver.
>Is it normal the memory stays at 1Gb or also should refresh?
>The problem is with some hours/days sites start to open slow and when i
>restart the SQL service, sites start to open in few seconds, but after some
>day/days start to be slow again.
>Its a very busy server with some portals, a few, others sites dynamic also
>but with few hits. But almost 100% of sites uses, except the portals, read
>for one of the portals database.
>Im not the programmer of the sites, and also i dont make the SQL statements
>need for each site, or design the databases.
>Tkx in advance,
>PV
high CPU time and sql 2000 getting slow performance
Hi,
I admin a server, dual 3,6 xeon with 4GB ram.
Windows 2000 server + SQL 2000 with SP3a + DNS + IIS
The server have around 800 dynamic sites.
SQL 2000 have 1Gb memory for it.
the problem is:
the CPU time after a SQL restart of service keeps getting higher and higher,
i say in a 24h its aroung 24 also memory starts to grow and stays at 1Gb,
wich is the reserved memory for SQL.
Is it normal the CPU time keeping getting higher? or should refresh? like
dllhost.exe in the webserver.
Is it normal the memory stays at 1Gb or also should refresh?
The problem is with some hours/days sites start to open slow and when i
restart the SQL service, sites start to open in few seconds, but after some
day/days start to be slow again.
Its a very busy server with some portals, a few, others sites dynamic also
but with few hits. But almost 100% of sites uses, except the portals, read
for one of the portals database.
Im not the programmer of the sites, and also i dont make the SQL statements
need for each site, or design the databases.
Tkx in advance,
PVIt sounds to me that the server is overloaded.
Normally if it is a live environment, you have a dedicated server just
for MSSQL.
Normally it is not advisable to change the default memory setting. For
optimum performance, SQL Server should be allowed to take as much as
RAM as it wants for its own use without having to compete for RAM with
other applications.
You can turn on some performance monitors to see how busy your system
is and justify to the owner of the machine it is time to upgrade the
hardware.
Here are some monitors you can put on (for memory only):
Memory Object: Pages/Sec
Memory Object: Available Bytes
SQLServer: Memory Manager: Total Server Memory
SQLServer: Memory Manager: Target Server Memory
Alternatively, you can turn on SQL Profiler if you are more interested
what MSSQL doing all day longs. It has CPU, I/O and memory eventlog
you can trace.
Mel|||The first red flag is to have a SQL Server sharing resources with another
application in the same server. It is not the optimal planning. Having said
that, it gets worse if the application is IIS and it is a very busy server
due to the busy portals.
SQL Server behaves somehow like an only child. It wants all CPU and all
possible memory. If you tell SQL Server that it could use from 0 to 1GB, it
will eventually use 1GB and it will keep it just in case.
According with what you describe, you have two demanding applications that
are competeng for the hardware resources on the same machine. This could get
worse if the IIS load increases.
I would look into the shared load between IIS and the SQL Server, meaning
that if the SQL Server is only 25% of the load, DNS users 15%, and IIS takes
the 60% remaining, it may be wise to start planning about getting another
machine to move either the SQL Server or get a bigger IIS Server to handle an
increasing load.
Let me know if this helps..
"PV" wrote:
> Hi,
> I admin a server, dual 3,6 xeon with 4GB ram.
> Windows 2000 server + SQL 2000 with SP3a + DNS + IIS
> The server have around 800 dynamic sites.
> SQL 2000 have 1Gb memory for it.
>
> the problem is:
> the CPU time after a SQL restart of service keeps getting higher and higher,
> i say in a 24h its aroung 24 also memory starts to grow and stays at 1Gb,
> wich is the reserved memory for SQL.
> Is it normal the CPU time keeping getting higher? or should refresh? like
> dllhost.exe in the webserver.
> Is it normal the memory stays at 1Gb or also should refresh?
> The problem is with some hours/days sites start to open slow and when i
> restart the SQL service, sites start to open in few seconds, but after some
> day/days start to be slow again.
> Its a very busy server with some portals, a few, others sites dynamic also
> but with few hits. But almost 100% of sites uses, except the portals, read
> for one of the portals database.
> Im not the programmer of the sites, and also i dont make the SQL statements
> need for each site, or design the databases.
> Tkx in advance,
> PV|||Hi,
Tkx for the directions needed to be made.
SQL needs a dedicated server for it.
Its SQL 2000 that "decreases server performance" after some hours/days. And,
as you say, starts the "competition" of resources between SQL and IIS mostly.
dispite all that sites, IIS doesnt take much amount of processor, except the
portals, one specially, but not all the time like SQL.
Since its SQL that decreases the performance i wanted to know what i asked,
if high cpu time is normal? and use of memory. the answer is positve i
presume.
Is there a way to "recycle" SQL uses of resources? like there is one tool to
IIS 5.0, but not need by the time being at this server since IIS is ok.
Or its prudent to "force" restart once a day/2days to the SQL? i think this
isnt the right way to resolve things.
PS:
we had one server with 1Gb memory with a xeon 2.0 with 500-600 dynamic sites
and only one BD in SQL(512Mb reserved and CPU utilization restrain to 50%
only), a forum and DNS. And the server worked EXCELLENT with no problems for
months and months without no problem always online and keeping increasing
amount of sites (10) per month. the server never reached the 1Gb use of
memory.
but no PORTALS... neither SQL to the portals.
Tkx again for the answer,
PV
"Edgardo Valdez, MCSD, MCDBA" wrote:
> The first red flag is to have a SQL Server sharing resources with another
> application in the same server. It is not the optimal planning. Having said
> that, it gets worse if the application is IIS and it is a very busy server
> due to the busy portals.
> SQL Server behaves somehow like an only child. It wants all CPU and all
> possible memory. If you tell SQL Server that it could use from 0 to 1GB, it
> will eventually use 1GB and it will keep it just in case.
> According with what you describe, you have two demanding applications that
> are competeng for the hardware resources on the same machine. This could get
> worse if the IIS load increases.
> I would look into the shared load between IIS and the SQL Server, meaning
> that if the SQL Server is only 25% of the load, DNS users 15%, and IIS takes
> the 60% remaining, it may be wise to start planning about getting another
> machine to move either the SQL Server or get a bigger IIS Server to handle an
> increasing load.
> Let me know if this helps..
> "PV" wrote:
> > Hi,
> >
> > I admin a server, dual 3,6 xeon with 4GB ram.
> >
> > Windows 2000 server + SQL 2000 with SP3a + DNS + IIS
> >
> > The server have around 800 dynamic sites.
> >
> > SQL 2000 have 1Gb memory for it.
> >
> >
> > the problem is:
> > the CPU time after a SQL restart of service keeps getting higher and higher,
> > i say in a 24h its aroung 24 also memory starts to grow and stays at 1Gb,
> > wich is the reserved memory for SQL.
> >
> > Is it normal the CPU time keeping getting higher? or should refresh? like
> > dllhost.exe in the webserver.
> >
> > Is it normal the memory stays at 1Gb or also should refresh?
> >
> > The problem is with some hours/days sites start to open slow and when i
> > restart the SQL service, sites start to open in few seconds, but after some
> > day/days start to be slow again.
> >
> > Its a very busy server with some portals, a few, others sites dynamic also
> > but with few hits. But almost 100% of sites uses, except the portals, read
> > for one of the portals database.
> >
> > Im not the programmer of the sites, and also i dont make the SQL statements
> > need for each site, or design the databases.
> >
> > Tkx in advance,
> > PV|||You will need to use SQL Profiler to capture the CPU and memory usage.
As the monitor will also cause some system resources, so you normally
turn it on for a period of time that you think it is a typical business
workload.
After you have the trace, you can either just open in Profiler or even
better import into as a trace table (built-in feature of Profiler -
Save as trace table). From the trace you can then see what processes
have highest CPU/memory usage, using select sql.
As you quoted another server running just fine, it could well be some
poorly written sql scripts/stored procedures are running on the server.
With the trace, you can then identify the root of the problem (if
any). E.g. a stored procedure that keep re-compile for no good reason,
session no closed down properly.
Mel|||Using 1 GB of memory isn't unusual...SQL uses as much memory as it can
get ahold of. It's also not unusual to have high CPU times.
Shut down all unecessary services (including IIS, FTP, etc.), enable
the /3GB switch and monitor your I/O & CPU usage. If you still run
into problems, run Profiler to determine what queries, SPs or other
operations are utilitizing your CPU.
Above all, run SQL Server on its own server. Don't mix in IIS
On Tue, 4 Apr 2006 06:42:05 -0700, PV <PV@.discussions.microsoft.com>
wrote:
>Hi,
>I admin a server, dual 3,6 xeon with 4GB ram.
>Windows 2000 server + SQL 2000 with SP3a + DNS + IIS
>The server have around 800 dynamic sites.
>SQL 2000 have 1Gb memory for it.
>
>the problem is:
>the CPU time after a SQL restart of service keeps getting higher and higher,
>i say in a 24h its aroung 24 also memory starts to grow and stays at 1Gb,
>wich is the reserved memory for SQL.
>Is it normal the CPU time keeping getting higher? or should refresh? like
>dllhost.exe in the webserver.
>Is it normal the memory stays at 1Gb or also should refresh?
>The problem is with some hours/days sites start to open slow and when i
>restart the SQL service, sites start to open in few seconds, but after some
>day/days start to be slow again.
>Its a very busy server with some portals, a few, others sites dynamic also
>but with few hits. But almost 100% of sites uses, except the portals, read
>for one of the portals database.
>Im not the programmer of the sites, and also i dont make the SQL statements
>need for each site, or design the databases.
>Tkx in advance,
>PV
I admin a server, dual 3,6 xeon with 4GB ram.
Windows 2000 server + SQL 2000 with SP3a + DNS + IIS
The server have around 800 dynamic sites.
SQL 2000 have 1Gb memory for it.
the problem is:
the CPU time after a SQL restart of service keeps getting higher and higher,
i say in a 24h its aroung 24 also memory starts to grow and stays at 1Gb,
wich is the reserved memory for SQL.
Is it normal the CPU time keeping getting higher? or should refresh? like
dllhost.exe in the webserver.
Is it normal the memory stays at 1Gb or also should refresh?
The problem is with some hours/days sites start to open slow and when i
restart the SQL service, sites start to open in few seconds, but after some
day/days start to be slow again.
Its a very busy server with some portals, a few, others sites dynamic also
but with few hits. But almost 100% of sites uses, except the portals, read
for one of the portals database.
Im not the programmer of the sites, and also i dont make the SQL statements
need for each site, or design the databases.
Tkx in advance,
PVIt sounds to me that the server is overloaded.
Normally if it is a live environment, you have a dedicated server just
for MSSQL.
Normally it is not advisable to change the default memory setting. For
optimum performance, SQL Server should be allowed to take as much as
RAM as it wants for its own use without having to compete for RAM with
other applications.
You can turn on some performance monitors to see how busy your system
is and justify to the owner of the machine it is time to upgrade the
hardware.
Here are some monitors you can put on (for memory only):
Memory Object: Pages/Sec
Memory Object: Available Bytes
SQLServer: Memory Manager: Total Server Memory
SQLServer: Memory Manager: Target Server Memory
Alternatively, you can turn on SQL Profiler if you are more interested
what MSSQL doing all day longs. It has CPU, I/O and memory eventlog
you can trace.
Mel|||The first red flag is to have a SQL Server sharing resources with another
application in the same server. It is not the optimal planning. Having said
that, it gets worse if the application is IIS and it is a very busy server
due to the busy portals.
SQL Server behaves somehow like an only child. It wants all CPU and all
possible memory. If you tell SQL Server that it could use from 0 to 1GB, it
will eventually use 1GB and it will keep it just in case.
According with what you describe, you have two demanding applications that
are competeng for the hardware resources on the same machine. This could get
worse if the IIS load increases.
I would look into the shared load between IIS and the SQL Server, meaning
that if the SQL Server is only 25% of the load, DNS users 15%, and IIS takes
the 60% remaining, it may be wise to start planning about getting another
machine to move either the SQL Server or get a bigger IIS Server to handle an
increasing load.
Let me know if this helps..
"PV" wrote:
> Hi,
> I admin a server, dual 3,6 xeon with 4GB ram.
> Windows 2000 server + SQL 2000 with SP3a + DNS + IIS
> The server have around 800 dynamic sites.
> SQL 2000 have 1Gb memory for it.
>
> the problem is:
> the CPU time after a SQL restart of service keeps getting higher and higher,
> i say in a 24h its aroung 24 also memory starts to grow and stays at 1Gb,
> wich is the reserved memory for SQL.
> Is it normal the CPU time keeping getting higher? or should refresh? like
> dllhost.exe in the webserver.
> Is it normal the memory stays at 1Gb or also should refresh?
> The problem is with some hours/days sites start to open slow and when i
> restart the SQL service, sites start to open in few seconds, but after some
> day/days start to be slow again.
> Its a very busy server with some portals, a few, others sites dynamic also
> but with few hits. But almost 100% of sites uses, except the portals, read
> for one of the portals database.
> Im not the programmer of the sites, and also i dont make the SQL statements
> need for each site, or design the databases.
> Tkx in advance,
> PV|||Hi,
Tkx for the directions needed to be made.
SQL needs a dedicated server for it.
Its SQL 2000 that "decreases server performance" after some hours/days. And,
as you say, starts the "competition" of resources between SQL and IIS mostly.
dispite all that sites, IIS doesnt take much amount of processor, except the
portals, one specially, but not all the time like SQL.
Since its SQL that decreases the performance i wanted to know what i asked,
if high cpu time is normal? and use of memory. the answer is positve i
presume.
Is there a way to "recycle" SQL uses of resources? like there is one tool to
IIS 5.0, but not need by the time being at this server since IIS is ok.
Or its prudent to "force" restart once a day/2days to the SQL? i think this
isnt the right way to resolve things.
PS:
we had one server with 1Gb memory with a xeon 2.0 with 500-600 dynamic sites
and only one BD in SQL(512Mb reserved and CPU utilization restrain to 50%
only), a forum and DNS. And the server worked EXCELLENT with no problems for
months and months without no problem always online and keeping increasing
amount of sites (10) per month. the server never reached the 1Gb use of
memory.
but no PORTALS... neither SQL to the portals.
Tkx again for the answer,
PV
"Edgardo Valdez, MCSD, MCDBA" wrote:
> The first red flag is to have a SQL Server sharing resources with another
> application in the same server. It is not the optimal planning. Having said
> that, it gets worse if the application is IIS and it is a very busy server
> due to the busy portals.
> SQL Server behaves somehow like an only child. It wants all CPU and all
> possible memory. If you tell SQL Server that it could use from 0 to 1GB, it
> will eventually use 1GB and it will keep it just in case.
> According with what you describe, you have two demanding applications that
> are competeng for the hardware resources on the same machine. This could get
> worse if the IIS load increases.
> I would look into the shared load between IIS and the SQL Server, meaning
> that if the SQL Server is only 25% of the load, DNS users 15%, and IIS takes
> the 60% remaining, it may be wise to start planning about getting another
> machine to move either the SQL Server or get a bigger IIS Server to handle an
> increasing load.
> Let me know if this helps..
> "PV" wrote:
> > Hi,
> >
> > I admin a server, dual 3,6 xeon with 4GB ram.
> >
> > Windows 2000 server + SQL 2000 with SP3a + DNS + IIS
> >
> > The server have around 800 dynamic sites.
> >
> > SQL 2000 have 1Gb memory for it.
> >
> >
> > the problem is:
> > the CPU time after a SQL restart of service keeps getting higher and higher,
> > i say in a 24h its aroung 24 also memory starts to grow and stays at 1Gb,
> > wich is the reserved memory for SQL.
> >
> > Is it normal the CPU time keeping getting higher? or should refresh? like
> > dllhost.exe in the webserver.
> >
> > Is it normal the memory stays at 1Gb or also should refresh?
> >
> > The problem is with some hours/days sites start to open slow and when i
> > restart the SQL service, sites start to open in few seconds, but after some
> > day/days start to be slow again.
> >
> > Its a very busy server with some portals, a few, others sites dynamic also
> > but with few hits. But almost 100% of sites uses, except the portals, read
> > for one of the portals database.
> >
> > Im not the programmer of the sites, and also i dont make the SQL statements
> > need for each site, or design the databases.
> >
> > Tkx in advance,
> > PV|||You will need to use SQL Profiler to capture the CPU and memory usage.
As the monitor will also cause some system resources, so you normally
turn it on for a period of time that you think it is a typical business
workload.
After you have the trace, you can either just open in Profiler or even
better import into as a trace table (built-in feature of Profiler -
Save as trace table). From the trace you can then see what processes
have highest CPU/memory usage, using select sql.
As you quoted another server running just fine, it could well be some
poorly written sql scripts/stored procedures are running on the server.
With the trace, you can then identify the root of the problem (if
any). E.g. a stored procedure that keep re-compile for no good reason,
session no closed down properly.
Mel|||Using 1 GB of memory isn't unusual...SQL uses as much memory as it can
get ahold of. It's also not unusual to have high CPU times.
Shut down all unecessary services (including IIS, FTP, etc.), enable
the /3GB switch and monitor your I/O & CPU usage. If you still run
into problems, run Profiler to determine what queries, SPs or other
operations are utilitizing your CPU.
Above all, run SQL Server on its own server. Don't mix in IIS
On Tue, 4 Apr 2006 06:42:05 -0700, PV <PV@.discussions.microsoft.com>
wrote:
>Hi,
>I admin a server, dual 3,6 xeon with 4GB ram.
>Windows 2000 server + SQL 2000 with SP3a + DNS + IIS
>The server have around 800 dynamic sites.
>SQL 2000 have 1Gb memory for it.
>
>the problem is:
>the CPU time after a SQL restart of service keeps getting higher and higher,
>i say in a 24h its aroung 24 also memory starts to grow and stays at 1Gb,
>wich is the reserved memory for SQL.
>Is it normal the CPU time keeping getting higher? or should refresh? like
>dllhost.exe in the webserver.
>Is it normal the memory stays at 1Gb or also should refresh?
>The problem is with some hours/days sites start to open slow and when i
>restart the SQL service, sites start to open in few seconds, but after some
>day/days start to be slow again.
>Its a very busy server with some portals, a few, others sites dynamic also
>but with few hits. But almost 100% of sites uses, except the portals, read
>for one of the portals database.
>Im not the programmer of the sites, and also i dont make the SQL statements
>need for each site, or design the databases.
>Tkx in advance,
>PV
high CPU time and sql 2000 getting slow performance
Hi,
I admin a server, dual 3,6 xeon with 4GB ram.
Windows 2000 server + SQL 2000 with SP3a + DNS + IIS
The server have around 800 dynamic sites.
SQL 2000 have 1Gb memory for it.
the problem is:
the CPU time after a SQL restart of service keeps getting higher and higher,
i say in a 24h its aroung 24 also memory starts to grow and stays at 1Gb,
wich is the reserved memory for SQL.
Is it normal the CPU time keeping getting higher? or should refresh? like
dllhost.exe in the webserver.
Is it normal the memory stays at 1Gb or also should refresh?
The problem is with some hours/days sites start to open slow and when i
restart the SQL service, sites start to open in few seconds, but after some
day/days start to be slow again.
Its a very busy server with some portals, a few, others sites dynamic also
but with few hits. But almost 100% of sites uses, except the portals, read
for one of the portals database.
Im not the programmer of the sites, and also i dont make the SQL statements
need for each site, or design the databases.
Tkx in advance,
PV
The first red flag is to have a SQL Server sharing resources with another
application in the same server. It is not the optimal planning. Having said
that, it gets worse if the application is IIS and it is a very busy server
due to the busy portals.
SQL Server behaves somehow like an only child. It wants all CPU and all
possible memory. If you tell SQL Server that it could use from 0 to 1GB, it
will eventually use 1GB and it will keep it just in case.
According with what you describe, you have two demanding applications that
are competeng for the hardware resources on the same machine. This could get
worse if the IIS load increases.
I would look into the shared load between IIS and the SQL Server, meaning
that if the SQL Server is only 25% of the load, DNS users 15%, and IIS takes
the 60% remaining, it may be wise to start planning about getting another
machine to move either the SQL Server or get a bigger IIS Server to handle an
increasing load.
Let me know if this helps..
"PV" wrote:
> Hi,
> I admin a server, dual 3,6 xeon with 4GB ram.
> Windows 2000 server + SQL 2000 with SP3a + DNS + IIS
> The server have around 800 dynamic sites.
> SQL 2000 have 1Gb memory for it.
>
> the problem is:
> the CPU time after a SQL restart of service keeps getting higher and higher,
> i say in a 24h its aroung 24 also memory starts to grow and stays at 1Gb,
> wich is the reserved memory for SQL.
> Is it normal the CPU time keeping getting higher? or should refresh? like
> dllhost.exe in the webserver.
> Is it normal the memory stays at 1Gb or also should refresh?
> The problem is with some hours/days sites start to open slow and when i
> restart the SQL service, sites start to open in few seconds, but after some
> day/days start to be slow again.
> Its a very busy server with some portals, a few, others sites dynamic also
> but with few hits. But almost 100% of sites uses, except the portals, read
> for one of the portals database.
> Im not the programmer of the sites, and also i dont make the SQL statements
> need for each site, or design the databases.
> Tkx in advance,
> PV
|||Hi,
Tkx for the directions needed to be made.
SQL needs a dedicated server for it.
Its SQL 2000 that "decreases server performance" after some hours/days. And,
as you say, starts the "competition" of resources between SQL and IIS mostly.
dispite all that sites, IIS doesnt take much amount of processor, except the
portals, one specially, but not all the time like SQL.
Since its SQL that decreases the performance i wanted to know what i asked,
if high cpu time is normal? and use of memory. the answer is positve i
presume.
Is there a way to "recycle" SQL uses of resources? like there is one tool to
IIS 5.0, but not need by the time being at this server since IIS is ok.
Or its prudent to "force" restart once a day/2days to the SQL? i think this
isnt the right way to resolve things.
PS:
we had one server with 1Gb memory with a xeon 2.0 with 500-600 dynamic sites
and only one BD in SQL(512Mb reserved and CPU utilization restrain to 50%
only), a forum and DNS. And the server worked EXCELLENT with no problems for
months and months without no problem always online and keeping increasing
amount of sites (10) per month. the server never reached the 1Gb use of
memory.
but no PORTALS... neither SQL to the portals.
Tkx again for the answer,
PV
"Edgardo Valdez, MCSD, MCDBA" wrote:
[vbcol=seagreen]
> The first red flag is to have a SQL Server sharing resources with another
> application in the same server. It is not the optimal planning. Having said
> that, it gets worse if the application is IIS and it is a very busy server
> due to the busy portals.
> SQL Server behaves somehow like an only child. It wants all CPU and all
> possible memory. If you tell SQL Server that it could use from 0 to 1GB, it
> will eventually use 1GB and it will keep it just in case.
> According with what you describe, you have two demanding applications that
> are competeng for the hardware resources on the same machine. This could get
> worse if the IIS load increases.
> I would look into the shared load between IIS and the SQL Server, meaning
> that if the SQL Server is only 25% of the load, DNS users 15%, and IIS takes
> the 60% remaining, it may be wise to start planning about getting another
> machine to move either the SQL Server or get a bigger IIS Server to handle an
> increasing load.
> Let me know if this helps..
> "PV" wrote:
I admin a server, dual 3,6 xeon with 4GB ram.
Windows 2000 server + SQL 2000 with SP3a + DNS + IIS
The server have around 800 dynamic sites.
SQL 2000 have 1Gb memory for it.
the problem is:
the CPU time after a SQL restart of service keeps getting higher and higher,
i say in a 24h its aroung 24 also memory starts to grow and stays at 1Gb,
wich is the reserved memory for SQL.
Is it normal the CPU time keeping getting higher? or should refresh? like
dllhost.exe in the webserver.
Is it normal the memory stays at 1Gb or also should refresh?
The problem is with some hours/days sites start to open slow and when i
restart the SQL service, sites start to open in few seconds, but after some
day/days start to be slow again.
Its a very busy server with some portals, a few, others sites dynamic also
but with few hits. But almost 100% of sites uses, except the portals, read
for one of the portals database.
Im not the programmer of the sites, and also i dont make the SQL statements
need for each site, or design the databases.
Tkx in advance,
PV
The first red flag is to have a SQL Server sharing resources with another
application in the same server. It is not the optimal planning. Having said
that, it gets worse if the application is IIS and it is a very busy server
due to the busy portals.
SQL Server behaves somehow like an only child. It wants all CPU and all
possible memory. If you tell SQL Server that it could use from 0 to 1GB, it
will eventually use 1GB and it will keep it just in case.
According with what you describe, you have two demanding applications that
are competeng for the hardware resources on the same machine. This could get
worse if the IIS load increases.
I would look into the shared load between IIS and the SQL Server, meaning
that if the SQL Server is only 25% of the load, DNS users 15%, and IIS takes
the 60% remaining, it may be wise to start planning about getting another
machine to move either the SQL Server or get a bigger IIS Server to handle an
increasing load.
Let me know if this helps..
"PV" wrote:
> Hi,
> I admin a server, dual 3,6 xeon with 4GB ram.
> Windows 2000 server + SQL 2000 with SP3a + DNS + IIS
> The server have around 800 dynamic sites.
> SQL 2000 have 1Gb memory for it.
>
> the problem is:
> the CPU time after a SQL restart of service keeps getting higher and higher,
> i say in a 24h its aroung 24 also memory starts to grow and stays at 1Gb,
> wich is the reserved memory for SQL.
> Is it normal the CPU time keeping getting higher? or should refresh? like
> dllhost.exe in the webserver.
> Is it normal the memory stays at 1Gb or also should refresh?
> The problem is with some hours/days sites start to open slow and when i
> restart the SQL service, sites start to open in few seconds, but after some
> day/days start to be slow again.
> Its a very busy server with some portals, a few, others sites dynamic also
> but with few hits. But almost 100% of sites uses, except the portals, read
> for one of the portals database.
> Im not the programmer of the sites, and also i dont make the SQL statements
> need for each site, or design the databases.
> Tkx in advance,
> PV
|||Hi,
Tkx for the directions needed to be made.
SQL needs a dedicated server for it.
Its SQL 2000 that "decreases server performance" after some hours/days. And,
as you say, starts the "competition" of resources between SQL and IIS mostly.
dispite all that sites, IIS doesnt take much amount of processor, except the
portals, one specially, but not all the time like SQL.
Since its SQL that decreases the performance i wanted to know what i asked,
if high cpu time is normal? and use of memory. the answer is positve i
presume.
Is there a way to "recycle" SQL uses of resources? like there is one tool to
IIS 5.0, but not need by the time being at this server since IIS is ok.
Or its prudent to "force" restart once a day/2days to the SQL? i think this
isnt the right way to resolve things.
PS:
we had one server with 1Gb memory with a xeon 2.0 with 500-600 dynamic sites
and only one BD in SQL(512Mb reserved and CPU utilization restrain to 50%
only), a forum and DNS. And the server worked EXCELLENT with no problems for
months and months without no problem always online and keeping increasing
amount of sites (10) per month. the server never reached the 1Gb use of
memory.
but no PORTALS... neither SQL to the portals.
Tkx again for the answer,
PV
"Edgardo Valdez, MCSD, MCDBA" wrote:
[vbcol=seagreen]
> The first red flag is to have a SQL Server sharing resources with another
> application in the same server. It is not the optimal planning. Having said
> that, it gets worse if the application is IIS and it is a very busy server
> due to the busy portals.
> SQL Server behaves somehow like an only child. It wants all CPU and all
> possible memory. If you tell SQL Server that it could use from 0 to 1GB, it
> will eventually use 1GB and it will keep it just in case.
> According with what you describe, you have two demanding applications that
> are competeng for the hardware resources on the same machine. This could get
> worse if the IIS load increases.
> I would look into the shared load between IIS and the SQL Server, meaning
> that if the SQL Server is only 25% of the load, DNS users 15%, and IIS takes
> the 60% remaining, it may be wise to start planning about getting another
> machine to move either the SQL Server or get a bigger IIS Server to handle an
> increasing load.
> Let me know if this helps..
> "PV" wrote:
Subscribe to:
Posts (Atom)