We are using SQL server 7.0, our users use to connect to SQL server remotely using Visual Basic Application. When all the users are connected it keeps on increasing the memory utilization and at 1,836,848 KB memory utilization it stops increasing the memory i.e memory utilization become stangnet. The actual amount of physical Memory is around 5GB but SQL server dont increase it after the treshhold level and the performance of the server get affected badly. Any one please suggest me the possible solution.In Enterprise Manager,
- right click on the server name and choose properties.
- choose memory tab
You should be able to set your min/max memory settings there. Also look into worker threads (on the processor tab).
Something else to think about,
How big is the DB (Gigs) ? How big are the tables being referenced? Perhaps something could be re-designed to make things more efficient. Are there a lot of table scans where you could have indexes, etc..?
Also,
Open the performance moniter and check the sql counters..
More info at -> http://www.sql-server-performance.com/performance_monitor_tips.asp|||It looks like you're running Standard Edition of SQL2K, which will not recognize any more memory, period! You need to upgrade to Enterprise and then you'll have more options:
1. /3GB in boot.ini will allow SQL Server to use more than the default 2GB
2. /PAE in boot.ini and enabling AWE on SQL Server will give SQL service whatever amount you specify.
Showing posts with label memory. Show all posts
Showing posts with label memory. Show all posts
Friday, March 23, 2012
Monday, March 19, 2012
how to improve memory utilization?
We have a SQL 2005 Standard Server, single-processor license, in a
Win2k3 SP1 Standard Server box.
We recently increased its RAM to 4GB and added the /3GB switch to the
boot.ini file.
However, we still receive occasional alerts such as the one below:
Device: SQL
Service: Memory Utilization
State Transition: From Warning To Failed Time Of State Transition:
12/17/06 1:01 AM
Probe(s): 172.16.0.12
Scandetails:
Total Physical Memory (KB): 3145208
Used Physical Memory (KB): 3007896
Free Physical Memory (KB): 137312
Physical Memory Usage (%): 96
Total Virtual Memory (KB): 4190208
Used Virtual Memory (KB): 64500
Free Virtual Memory (KB): 4125708
Virtual Memory Usage (%): 2
Alert: 0
Alert Activated: 12/17/06 1:01 AM
Alert Sent: 12/17/06 1:11 AM
The timing of events like this is not a big deal, it does not affect
end users, but I thought after we increased the available RAM, such
events would go away. And 12/17/06 is a Sunday. There was a test backup
running, but this error has not always appeared during other similar
nightly backups.
So far as I know there are no scheduled activities on the SQL server at
that time, so I am wondering if there is something I should do in the
SQL Server configuration to make it use memory better.
The pagefile is 4GB by the way. I found the Slava Oks blog about SQL
memory but it was over my head.
Thank you, TomIf there is no other usage of the computer, I would recommend reducing the
page file to the minimum -or eliminating it altogether.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"tlyczko" <tlyczko@.gmail.com> wrote in message
news:1166452468.090918.44710@.l12g2000cwl.googlegroups.com...
> We have a SQL 2005 Standard Server, single-processor license, in a
> Win2k3 SP1 Standard Server box.
> We recently increased its RAM to 4GB and added the /3GB switch to the
> boot.ini file.
> However, we still receive occasional alerts such as the one below:
> Device: SQL
> Service: Memory Utilization
> State Transition: From Warning To Failed Time Of State Transition:
> 12/17/06 1:01 AM
> Probe(s): 172.16.0.12
> Scandetails:
> Total Physical Memory (KB): 3145208
> Used Physical Memory (KB): 3007896
> Free Physical Memory (KB): 137312
> Physical Memory Usage (%): 96
> Total Virtual Memory (KB): 4190208
> Used Virtual Memory (KB): 64500
> Free Virtual Memory (KB): 4125708
> Virtual Memory Usage (%): 2
> Alert: 0
> Alert Activated: 12/17/06 1:01 AM
> Alert Sent: 12/17/06 1:11 AM
> The timing of events like this is not a big deal, it does not affect
> end users, but I thought after we increased the available RAM, such
> events would go away. And 12/17/06 is a Sunday. There was a test backup
> running, but this error has not always appeared during other similar
> nightly backups.
> So far as I know there are no scheduled activities on the SQL server at
> that time, so I am wondering if there is something I should do in the
> SQL Server configuration to make it use memory better.
> The pagefile is 4GB by the way. I found the Slava Oks blog about SQL
> memory but it was over my head.
> Thank you, Tom
>|||Really' How come' Would you mind providing some explanation' (or a
link to an explanation)
Is it okay to shrink the pagefile to say 1GB or 512MB' instead of
eliminating it'
Our largest database is around 3GB (Great Plains) but it may someday
take on a SharePoint Services database as well, which could become
quite large, I know.
The box has one single Xeon 2.8 Ghz processor.
We do also have Abra Suite running on it (with FoxPro tables, this is
the server-side part of it, the client runs in Citrix).
(I know SQL does its own memory management etc.)
Thank you, Tom
Arnie Rowland wrote:[vbcol=seagreen]
> If there is no other usage of the computer, I would recommend reducing the
> page file to the minimum -or eliminating it altogether.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
> You can't help someone get up a hill without getting a little closer to th
e
> top yourself.
> - H. Norman Schwarzkopf
>
> "tlyczko" <tlyczko@.gmail.com> wrote in message
> news:1166452468.090918.44710@.l12g2000cwl.googlegroups.com...
Win2k3 SP1 Standard Server box.
We recently increased its RAM to 4GB and added the /3GB switch to the
boot.ini file.
However, we still receive occasional alerts such as the one below:
Device: SQL
Service: Memory Utilization
State Transition: From Warning To Failed Time Of State Transition:
12/17/06 1:01 AM
Probe(s): 172.16.0.12
Scandetails:
Total Physical Memory (KB): 3145208
Used Physical Memory (KB): 3007896
Free Physical Memory (KB): 137312
Physical Memory Usage (%): 96
Total Virtual Memory (KB): 4190208
Used Virtual Memory (KB): 64500
Free Virtual Memory (KB): 4125708
Virtual Memory Usage (%): 2
Alert: 0
Alert Activated: 12/17/06 1:01 AM
Alert Sent: 12/17/06 1:11 AM
The timing of events like this is not a big deal, it does not affect
end users, but I thought after we increased the available RAM, such
events would go away. And 12/17/06 is a Sunday. There was a test backup
running, but this error has not always appeared during other similar
nightly backups.
So far as I know there are no scheduled activities on the SQL server at
that time, so I am wondering if there is something I should do in the
SQL Server configuration to make it use memory better.
The pagefile is 4GB by the way. I found the Slava Oks blog about SQL
memory but it was over my head.
Thank you, TomIf there is no other usage of the computer, I would recommend reducing the
page file to the minimum -or eliminating it altogether.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"tlyczko" <tlyczko@.gmail.com> wrote in message
news:1166452468.090918.44710@.l12g2000cwl.googlegroups.com...
> We have a SQL 2005 Standard Server, single-processor license, in a
> Win2k3 SP1 Standard Server box.
> We recently increased its RAM to 4GB and added the /3GB switch to the
> boot.ini file.
> However, we still receive occasional alerts such as the one below:
> Device: SQL
> Service: Memory Utilization
> State Transition: From Warning To Failed Time Of State Transition:
> 12/17/06 1:01 AM
> Probe(s): 172.16.0.12
> Scandetails:
> Total Physical Memory (KB): 3145208
> Used Physical Memory (KB): 3007896
> Free Physical Memory (KB): 137312
> Physical Memory Usage (%): 96
> Total Virtual Memory (KB): 4190208
> Used Virtual Memory (KB): 64500
> Free Virtual Memory (KB): 4125708
> Virtual Memory Usage (%): 2
> Alert: 0
> Alert Activated: 12/17/06 1:01 AM
> Alert Sent: 12/17/06 1:11 AM
> The timing of events like this is not a big deal, it does not affect
> end users, but I thought after we increased the available RAM, such
> events would go away. And 12/17/06 is a Sunday. There was a test backup
> running, but this error has not always appeared during other similar
> nightly backups.
> So far as I know there are no scheduled activities on the SQL server at
> that time, so I am wondering if there is something I should do in the
> SQL Server configuration to make it use memory better.
> The pagefile is 4GB by the way. I found the Slava Oks blog about SQL
> memory but it was over my head.
> Thank you, Tom
>|||Really' How come' Would you mind providing some explanation' (or a
link to an explanation)
Is it okay to shrink the pagefile to say 1GB or 512MB' instead of
eliminating it'
Our largest database is around 3GB (Great Plains) but it may someday
take on a SharePoint Services database as well, which could become
quite large, I know.
The box has one single Xeon 2.8 Ghz processor.
We do also have Abra Suite running on it (with FoxPro tables, this is
the server-side part of it, the client runs in Citrix).
(I know SQL does its own memory management etc.)
Thank you, Tom
Arnie Rowland wrote:[vbcol=seagreen]
> If there is no other usage of the computer, I would recommend reducing the
> page file to the minimum -or eliminating it altogether.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
> You can't help someone get up a hill without getting a little closer to th
e
> top yourself.
> - H. Norman Schwarzkopf
>
> "tlyczko" <tlyczko@.gmail.com> wrote in message
> news:1166452468.090918.44710@.l12g2000cwl.googlegroups.com...
how to improve memory utilization?
We have a SQL 2005 Standard Server, single-processor license, in a
Win2k3 SP1 Standard Server box.
We recently increased its RAM to 4GB and added the /3GB switch to the
boot.ini file.
However, we still receive occasional alerts such as the one below:
Device: SQL
Service: Memory Utilization
State Transition: From Warning To Failed Time Of State Transition:
12/17/06 1:01 AM
Probe(s): 172.16.0.12
Scandetails:
Total Physical Memory (KB): 3145208
Used Physical Memory (KB): 3007896
Free Physical Memory (KB): 137312
Physical Memory Usage (%): 96
Total Virtual Memory (KB): 4190208
Used Virtual Memory (KB): 64500
Free Virtual Memory (KB): 4125708
Virtual Memory Usage (%): 2
Alert: 0
Alert Activated: 12/17/06 1:01 AM
Alert Sent: 12/17/06 1:11 AM
The timing of events like this is not a big deal, it does not affect
end users, but I thought after we increased the available RAM, such
events would go away. And 12/17/06 is a Sunday. There was a test backup
running, but this error has not always appeared during other similar
nightly backups.
So far as I know there are no scheduled activities on the SQL server at
that time, so I am wondering if there is something I should do in the
SQL Server configuration to make it use memory better.
The pagefile is 4GB by the way. I found the Slava Oks blog about SQL
memory but it was over my head. :(
Thank you, TomIf there is no other usage of the computer, I would recommend reducing the
page file to the minimum -or eliminating it altogether.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"tlyczko" <tlyczko@.gmail.com> wrote in message
news:1166452468.090918.44710@.l12g2000cwl.googlegroups.com...
> We have a SQL 2005 Standard Server, single-processor license, in a
> Win2k3 SP1 Standard Server box.
> We recently increased its RAM to 4GB and added the /3GB switch to the
> boot.ini file.
> However, we still receive occasional alerts such as the one below:
> Device: SQL
> Service: Memory Utilization
> State Transition: From Warning To Failed Time Of State Transition:
> 12/17/06 1:01 AM
> Probe(s): 172.16.0.12
> Scandetails:
> Total Physical Memory (KB): 3145208
> Used Physical Memory (KB): 3007896
> Free Physical Memory (KB): 137312
> Physical Memory Usage (%): 96
> Total Virtual Memory (KB): 4190208
> Used Virtual Memory (KB): 64500
> Free Virtual Memory (KB): 4125708
> Virtual Memory Usage (%): 2
> Alert: 0
> Alert Activated: 12/17/06 1:01 AM
> Alert Sent: 12/17/06 1:11 AM
> The timing of events like this is not a big deal, it does not affect
> end users, but I thought after we increased the available RAM, such
> events would go away. And 12/17/06 is a Sunday. There was a test backup
> running, but this error has not always appeared during other similar
> nightly backups.
> So far as I know there are no scheduled activities on the SQL server at
> that time, so I am wondering if there is something I should do in the
> SQL Server configuration to make it use memory better.
> The pagefile is 4GB by the way. I found the Slava Oks blog about SQL
> memory but it was over my head. :(
> Thank you, Tom
>|||Really' How come' Would you mind providing some explanation' (or a
link to an explanation)
Is it okay to shrink the pagefile to say 1GB or 512MB' instead of
eliminating it'
Our largest database is around 3GB (Great Plains) but it may someday
take on a SharePoint Services database as well, which could become
quite large, I know.
The box has one single Xeon 2.8 Ghz processor.
We do also have Abra Suite running on it (with FoxPro tables, this is
the server-side part of it, the client runs in Citrix).
(I know SQL does its own memory management etc.)
Thank you, Tom
Arnie Rowland wrote:
> If there is no other usage of the computer, I would recommend reducing the
> page file to the minimum -or eliminating it altogether.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
> You can't help someone get up a hill without getting a little closer to the
> top yourself.
> - H. Norman Schwarzkopf
>
> "tlyczko" <tlyczko@.gmail.com> wrote in message
> news:1166452468.090918.44710@.l12g2000cwl.googlegroups.com...
> > We have a SQL 2005 Standard Server, single-processor license, in a
> > Win2k3 SP1 Standard Server box.
> >
> > We recently increased its RAM to 4GB and added the /3GB switch to the
> > boot.ini file.
> >
> > However, we still receive occasional alerts such as the one below:
> >
> > Device: SQL
> > Service: Memory Utilization
> > State Transition: From Warning To Failed Time Of State Transition:
> > 12/17/06 1:01 AM
> > Probe(s): 172.16.0.12
> >
> > Scandetails:
> > Total Physical Memory (KB): 3145208
> > Used Physical Memory (KB): 3007896
> > Free Physical Memory (KB): 137312
> > Physical Memory Usage (%): 96
> > Total Virtual Memory (KB): 4190208
> > Used Virtual Memory (KB): 64500
> > Free Virtual Memory (KB): 4125708
> > Virtual Memory Usage (%): 2
> >
> > Alert: 0
> > Alert Activated: 12/17/06 1:01 AM
> > Alert Sent: 12/17/06 1:11 AM
> >
> > The timing of events like this is not a big deal, it does not affect
> > end users, but I thought after we increased the available RAM, such
> > events would go away. And 12/17/06 is a Sunday. There was a test backup
> > running, but this error has not always appeared during other similar
> > nightly backups.
> >
> > So far as I know there are no scheduled activities on the SQL server at
> > that time, so I am wondering if there is something I should do in the
> > SQL Server configuration to make it use memory better.
> >
> > The pagefile is 4GB by the way. I found the Slava Oks blog about SQL
> > memory but it was over my head. :(
> >
> > Thank you, Tom
> >
Win2k3 SP1 Standard Server box.
We recently increased its RAM to 4GB and added the /3GB switch to the
boot.ini file.
However, we still receive occasional alerts such as the one below:
Device: SQL
Service: Memory Utilization
State Transition: From Warning To Failed Time Of State Transition:
12/17/06 1:01 AM
Probe(s): 172.16.0.12
Scandetails:
Total Physical Memory (KB): 3145208
Used Physical Memory (KB): 3007896
Free Physical Memory (KB): 137312
Physical Memory Usage (%): 96
Total Virtual Memory (KB): 4190208
Used Virtual Memory (KB): 64500
Free Virtual Memory (KB): 4125708
Virtual Memory Usage (%): 2
Alert: 0
Alert Activated: 12/17/06 1:01 AM
Alert Sent: 12/17/06 1:11 AM
The timing of events like this is not a big deal, it does not affect
end users, but I thought after we increased the available RAM, such
events would go away. And 12/17/06 is a Sunday. There was a test backup
running, but this error has not always appeared during other similar
nightly backups.
So far as I know there are no scheduled activities on the SQL server at
that time, so I am wondering if there is something I should do in the
SQL Server configuration to make it use memory better.
The pagefile is 4GB by the way. I found the Slava Oks blog about SQL
memory but it was over my head. :(
Thank you, TomIf there is no other usage of the computer, I would recommend reducing the
page file to the minimum -or eliminating it altogether.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"tlyczko" <tlyczko@.gmail.com> wrote in message
news:1166452468.090918.44710@.l12g2000cwl.googlegroups.com...
> We have a SQL 2005 Standard Server, single-processor license, in a
> Win2k3 SP1 Standard Server box.
> We recently increased its RAM to 4GB and added the /3GB switch to the
> boot.ini file.
> However, we still receive occasional alerts such as the one below:
> Device: SQL
> Service: Memory Utilization
> State Transition: From Warning To Failed Time Of State Transition:
> 12/17/06 1:01 AM
> Probe(s): 172.16.0.12
> Scandetails:
> Total Physical Memory (KB): 3145208
> Used Physical Memory (KB): 3007896
> Free Physical Memory (KB): 137312
> Physical Memory Usage (%): 96
> Total Virtual Memory (KB): 4190208
> Used Virtual Memory (KB): 64500
> Free Virtual Memory (KB): 4125708
> Virtual Memory Usage (%): 2
> Alert: 0
> Alert Activated: 12/17/06 1:01 AM
> Alert Sent: 12/17/06 1:11 AM
> The timing of events like this is not a big deal, it does not affect
> end users, but I thought after we increased the available RAM, such
> events would go away. And 12/17/06 is a Sunday. There was a test backup
> running, but this error has not always appeared during other similar
> nightly backups.
> So far as I know there are no scheduled activities on the SQL server at
> that time, so I am wondering if there is something I should do in the
> SQL Server configuration to make it use memory better.
> The pagefile is 4GB by the way. I found the Slava Oks blog about SQL
> memory but it was over my head. :(
> Thank you, Tom
>|||Really' How come' Would you mind providing some explanation' (or a
link to an explanation)
Is it okay to shrink the pagefile to say 1GB or 512MB' instead of
eliminating it'
Our largest database is around 3GB (Great Plains) but it may someday
take on a SharePoint Services database as well, which could become
quite large, I know.
The box has one single Xeon 2.8 Ghz processor.
We do also have Abra Suite running on it (with FoxPro tables, this is
the server-side part of it, the client runs in Citrix).
(I know SQL does its own memory management etc.)
Thank you, Tom
Arnie Rowland wrote:
> If there is no other usage of the computer, I would recommend reducing the
> page file to the minimum -or eliminating it altogether.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
> You can't help someone get up a hill without getting a little closer to the
> top yourself.
> - H. Norman Schwarzkopf
>
> "tlyczko" <tlyczko@.gmail.com> wrote in message
> news:1166452468.090918.44710@.l12g2000cwl.googlegroups.com...
> > We have a SQL 2005 Standard Server, single-processor license, in a
> > Win2k3 SP1 Standard Server box.
> >
> > We recently increased its RAM to 4GB and added the /3GB switch to the
> > boot.ini file.
> >
> > However, we still receive occasional alerts such as the one below:
> >
> > Device: SQL
> > Service: Memory Utilization
> > State Transition: From Warning To Failed Time Of State Transition:
> > 12/17/06 1:01 AM
> > Probe(s): 172.16.0.12
> >
> > Scandetails:
> > Total Physical Memory (KB): 3145208
> > Used Physical Memory (KB): 3007896
> > Free Physical Memory (KB): 137312
> > Physical Memory Usage (%): 96
> > Total Virtual Memory (KB): 4190208
> > Used Virtual Memory (KB): 64500
> > Free Virtual Memory (KB): 4125708
> > Virtual Memory Usage (%): 2
> >
> > Alert: 0
> > Alert Activated: 12/17/06 1:01 AM
> > Alert Sent: 12/17/06 1:11 AM
> >
> > The timing of events like this is not a big deal, it does not affect
> > end users, but I thought after we increased the available RAM, such
> > events would go away. And 12/17/06 is a Sunday. There was a test backup
> > running, but this error has not always appeared during other similar
> > nightly backups.
> >
> > So far as I know there are no scheduled activities on the SQL server at
> > that time, so I am wondering if there is something I should do in the
> > SQL Server configuration to make it use memory better.
> >
> > The pagefile is 4GB by the way. I found the Slava Oks blog about SQL
> > memory but it was over my head. :(
> >
> > Thank you, Tom
> >
how to improve memory utilization?
We have a SQL 2005 Standard Server, single-processor license, in a
Win2k3 SP1 Standard Server box.
We recently increased its RAM to 4GB and added the /3GB switch to the
boot.ini file.
However, we still receive occasional alerts such as the one below:
Device: SQL
Service: Memory Utilization
State Transition: From Warning To Failed Time Of State Transition:
12/17/06 1:01 AM
Probe(s): 172.16.0.12
Scandetails:
Total Physical Memory (KB): 3145208
Used Physical Memory (KB): 3007896
Free Physical Memory (KB): 137312
Physical Memory Usage (%): 96
Total Virtual Memory (KB): 4190208
Used Virtual Memory (KB): 64500
Free Virtual Memory (KB): 4125708
Virtual Memory Usage (%): 2
Alert: 0
Alert Activated: 12/17/06 1:01 AM
Alert Sent: 12/17/06 1:11 AM
The timing of events like this is not a big deal, it does not affect
end users, but I thought after we increased the available RAM, such
events would go away. And 12/17/06 is a Sunday. There was a test backup
running, but this error has not always appeared during other similar
nightly backups.
So far as I know there are no scheduled activities on the SQL server at
that time, so I am wondering if there is something I should do in the
SQL Server configuration to make it use memory better.
The pagefile is 4GB by the way. I found the Slava Oks blog about SQL
memory but it was over my head.
Thank you, Tom
If there is no other usage of the computer, I would recommend reducing the
page file to the minimum -or eliminating it altogether.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"tlyczko" <tlyczko@.gmail.com> wrote in message
news:1166452468.090918.44710@.l12g2000cwl.googlegro ups.com...
> We have a SQL 2005 Standard Server, single-processor license, in a
> Win2k3 SP1 Standard Server box.
> We recently increased its RAM to 4GB and added the /3GB switch to the
> boot.ini file.
> However, we still receive occasional alerts such as the one below:
> Device: SQL
> Service: Memory Utilization
> State Transition: From Warning To Failed Time Of State Transition:
> 12/17/06 1:01 AM
> Probe(s): 172.16.0.12
> Scandetails:
> Total Physical Memory (KB): 3145208
> Used Physical Memory (KB): 3007896
> Free Physical Memory (KB): 137312
> Physical Memory Usage (%): 96
> Total Virtual Memory (KB): 4190208
> Used Virtual Memory (KB): 64500
> Free Virtual Memory (KB): 4125708
> Virtual Memory Usage (%): 2
> Alert: 0
> Alert Activated: 12/17/06 1:01 AM
> Alert Sent: 12/17/06 1:11 AM
> The timing of events like this is not a big deal, it does not affect
> end users, but I thought after we increased the available RAM, such
> events would go away. And 12/17/06 is a Sunday. There was a test backup
> running, but this error has not always appeared during other similar
> nightly backups.
> So far as I know there are no scheduled activities on the SQL server at
> that time, so I am wondering if there is something I should do in the
> SQL Server configuration to make it use memory better.
> The pagefile is 4GB by the way. I found the Slava Oks blog about SQL
> memory but it was over my head.
> Thank you, Tom
>
|||Really? How come? Would you mind providing some explanation? (or a
link to an explanation)
Is it okay to shrink the pagefile to say 1GB or 512MB? instead of
eliminating it?
Our largest database is around 3GB (Great Plains) but it may someday
take on a SharePoint Services database as well, which could become
quite large, I know.
The box has one single Xeon 2.8 Ghz processor.
We do also have Abra Suite running on it (with FoxPro tables, this is
the server-side part of it, the client runs in Citrix).
(I know SQL does its own memory management etc.)
Thank you, Tom
Arnie Rowland wrote:[vbcol=seagreen]
> If there is no other usage of the computer, I would recommend reducing the
> page file to the minimum -or eliminating it altogether.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
> You can't help someone get up a hill without getting a little closer to the
> top yourself.
> - H. Norman Schwarzkopf
>
> "tlyczko" <tlyczko@.gmail.com> wrote in message
> news:1166452468.090918.44710@.l12g2000cwl.googlegro ups.com...
Win2k3 SP1 Standard Server box.
We recently increased its RAM to 4GB and added the /3GB switch to the
boot.ini file.
However, we still receive occasional alerts such as the one below:
Device: SQL
Service: Memory Utilization
State Transition: From Warning To Failed Time Of State Transition:
12/17/06 1:01 AM
Probe(s): 172.16.0.12
Scandetails:
Total Physical Memory (KB): 3145208
Used Physical Memory (KB): 3007896
Free Physical Memory (KB): 137312
Physical Memory Usage (%): 96
Total Virtual Memory (KB): 4190208
Used Virtual Memory (KB): 64500
Free Virtual Memory (KB): 4125708
Virtual Memory Usage (%): 2
Alert: 0
Alert Activated: 12/17/06 1:01 AM
Alert Sent: 12/17/06 1:11 AM
The timing of events like this is not a big deal, it does not affect
end users, but I thought after we increased the available RAM, such
events would go away. And 12/17/06 is a Sunday. There was a test backup
running, but this error has not always appeared during other similar
nightly backups.
So far as I know there are no scheduled activities on the SQL server at
that time, so I am wondering if there is something I should do in the
SQL Server configuration to make it use memory better.
The pagefile is 4GB by the way. I found the Slava Oks blog about SQL
memory but it was over my head.
Thank you, Tom
If there is no other usage of the computer, I would recommend reducing the
page file to the minimum -or eliminating it altogether.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"tlyczko" <tlyczko@.gmail.com> wrote in message
news:1166452468.090918.44710@.l12g2000cwl.googlegro ups.com...
> We have a SQL 2005 Standard Server, single-processor license, in a
> Win2k3 SP1 Standard Server box.
> We recently increased its RAM to 4GB and added the /3GB switch to the
> boot.ini file.
> However, we still receive occasional alerts such as the one below:
> Device: SQL
> Service: Memory Utilization
> State Transition: From Warning To Failed Time Of State Transition:
> 12/17/06 1:01 AM
> Probe(s): 172.16.0.12
> Scandetails:
> Total Physical Memory (KB): 3145208
> Used Physical Memory (KB): 3007896
> Free Physical Memory (KB): 137312
> Physical Memory Usage (%): 96
> Total Virtual Memory (KB): 4190208
> Used Virtual Memory (KB): 64500
> Free Virtual Memory (KB): 4125708
> Virtual Memory Usage (%): 2
> Alert: 0
> Alert Activated: 12/17/06 1:01 AM
> Alert Sent: 12/17/06 1:11 AM
> The timing of events like this is not a big deal, it does not affect
> end users, but I thought after we increased the available RAM, such
> events would go away. And 12/17/06 is a Sunday. There was a test backup
> running, but this error has not always appeared during other similar
> nightly backups.
> So far as I know there are no scheduled activities on the SQL server at
> that time, so I am wondering if there is something I should do in the
> SQL Server configuration to make it use memory better.
> The pagefile is 4GB by the way. I found the Slava Oks blog about SQL
> memory but it was over my head.
> Thank you, Tom
>
|||Really? How come? Would you mind providing some explanation? (or a
link to an explanation)
Is it okay to shrink the pagefile to say 1GB or 512MB? instead of
eliminating it?
Our largest database is around 3GB (Great Plains) but it may someday
take on a SharePoint Services database as well, which could become
quite large, I know.
The box has one single Xeon 2.8 Ghz processor.
We do also have Abra Suite running on it (with FoxPro tables, this is
the server-side part of it, the client runs in Citrix).
(I know SQL does its own memory management etc.)
Thank you, Tom
Arnie Rowland wrote:[vbcol=seagreen]
> If there is no other usage of the computer, I would recommend reducing the
> page file to the minimum -or eliminating it altogether.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
> You can't help someone get up a hill without getting a little closer to the
> top yourself.
> - H. Norman Schwarzkopf
>
> "tlyczko" <tlyczko@.gmail.com> wrote in message
> news:1166452468.090918.44710@.l12g2000cwl.googlegro ups.com...
Friday, February 24, 2012
how to identify what consumes cpu in sql server
Hi all
I use 64 bit 2005 server with 8cpu and 8G of memory.
This server is accessed by large number of intensive or not so intensive programs.
I had eliminated all inefficient queries by means of sql profiler. What I see now is 30 procs or so runining in 1 second. They are all pretty simple and as I said use indexes. cpu column for most show 0, reads show 10 - 50 - pretty good.
But... my cpu utilization is 75% in avg. across of all 8 cpu's. I really can't find an answer for it.
If procs run so efficient, where does cpu go? Disk queue length is 0.04 or less - seems very good.
Task manager shows that all of it 75% attributed to sql server.
So which resources besides sql queries use so much cpu? Do I have to look at some other areas and which ones where cpu could be used besides sql queries themselves.
Thank you, Gene.
You could have other issues besides CPU that are causing CPU pressure, or it could be that your queries are very efficient, but they are being run so frequently as to cause CPU pressure. Try running these DMV queries to get a better handle on what is going on.
-- Check CPU Pressure
-- Total waits are wait_time_ms (high signal waits indicates CPU pressure)
SELECT signal_wait_time_ms=SUM(signal_wait_time_ms)
,'%signal (cpu) waits' = CAST(100.0 * SUM(signal_wait_time_ms) / SUM (wait_time_ms) AS NUMERIC(20,2))
,resource_wait_time_ms=SUM(wait_time_ms - signal_wait_time_ms)
,'%resource waits'= CAST(100.0 * SUM(wait_time_ms - signal_wait_time_ms) / SUM (wait_time_ms) AS NUMERIC(20,2))
FROM sys.dm_os_wait_stats
-- Check SQL Server Schedulers to see if they are waiting on CPU
SELECT scheduler_id, current_tasks_count, runnable_tasks_count
FROM sys.dm_os_schedulers
WHERE scheduler_id < 255
-- Get Top 50 executed SP's ordered by avg worker time
SELECT TOP 50 qt.text AS 'SP Name', qs.execution_count AS 'Execution Count', ISNULL(qs.total_elapsed_time/qs.execution_count, 0) AS 'AvgElapsedTime',
qs.total_worker_time/qs.execution_count AS 'AvgWorkerTime',
qs.total_worker_time AS 'TotalWorkerTime',
qs.max_logical_reads, qs.max_logical_writes, qs.creation_time,
DATEDIFF(Minute, qs.creation_time, GetDate()) AS Age,
ISNULL(qs.execution_count/DATEDIFF(Second, qs.creation_time, GetDate()), 0) AS 'Calls/Second'
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
--WHERE qt.dbid = 5 -- Filter by database
ORDER BY qs.total_worker_time/qs.execution_count DESC
-- Get Top 50 executed SP's ordered by calls/sec
SELECT TOP 50 qt.text AS 'SP Name', qs.execution_count AS 'Execution Count',
qs.total_worker_time/qs.execution_count AS 'AvgWorkerTime',
qs.total_worker_time AS 'TotalWorkerTime',
qs.total_elapsed_time/qs.execution_count AS 'AvgElapsedTime',
qs.max_logical_reads, qs.max_logical_writes, qs.creation_time,
DATEDIFF(Minute, qs.creation_time, GetDate()) AS 'Age in Cache',
--qs.execution_count/DATEDIFF(Minute, qs.creation_time, GetDate()) AS 'Calls/Minute',
qs.execution_count/DATEDIFF(Second, qs.creation_time, GetDate()) AS 'Calls/Second'
, qt.dbid
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
--WHERE qt.dbid = 5 -- Filter by database
ORDER BY qs.execution_count/DATEDIFF(Second, qs.creation_time, GetDate()) DESC
|||Or try the SQL 2005 Performance Dashboard, it's pretty powerful
http://www.sql-server-performance.com/bm_performance_dashboard_2005.asp
|||Hi Glen
It's very interesting material you sent. Thank you so much. i will need time on Monday to analyze it.
Great technical stuff! Gene.
How to identify Memory Leak caused by bad written stored procedures?
Does anyone has experience in monitoring Sql server memory leak? I am suspicious of some of the user stored procedures causing the memory leak. Can anyone who has such experience explaine how to find the offened stored procedures?
Thank you very much for any kind of suggestions and recommendations!
Unless you're talking about extended stored procedures, then stored procs themselves don't have memory leaks as such (like normal code), however, they may be doing things inefficiently, or you may have missing table indexes.
The best way to start is to use the SQL Profiler tool to monitor stored procedure execution and look at the duration field. If you capture results to a table, you can then select the slowest queries by ordering on the duration column descending. You should also group by the procedure name to look at the procedures executed most often.
The combination of the slowest queries, and those executed most often will give you the best place to start. From there it's a case of looking at the execution plan (in query analyser) of the procs and looking for ways to optimise the tsql or add/modify indexes.
Subscribe to:
Posts (Atom)