The error which we are facing these days is related to the abnormal
performance of SQL server, and sometimes also got the error â'SQL Run-time
error 2147217911(80040e09)â' sometimes on the client ends.
As per our detailed observation it is observed that our SQL server database
response sometimes get dead slow and that is why all the related application
and website responses gets slow as well.
This delay of responses is not always but rarely and the unusual things
noticed are that user connection on the SQL server and on our web server
increases dynamically, which really seems to be faking not actual. And when
these user connections drops all the SQL related activities are back to
normal. Just to add more we are using windows 2003 server enterprise edition
of 64bit and SQL server 2000 enterprise edition of 64bit as well.â'Memory utilisation is also increased i.e. used more than normal.
"JS" wrote:
> The error which we are facing these days is related to the abnormal
> performance of SQL server, and sometimes also got the error â'SQL Run-time
> error 2147217911(80040e09)â' sometimes on the client ends.
> As per our detailed observation it is observed that our SQL server database
> response sometimes get dead slow and that is why all the related application
> and website responses gets slow as well.
> This delay of responses is not always but rarely and the unusual things
> noticed are that user connection on the SQL server and on our web server
> increases dynamically, which really seems to be faking not actual. And when
> these user connections drops all the SQL related activities are back to
> normal. Just to add more we are using windows 2003 server enterprise edition
> of 64bit and SQL server 2000 enterprise edition of 64bit as well.â'
>
>
>sql
Showing posts with label performance. Show all posts
Showing posts with label performance. Show all posts
Tuesday, March 27, 2012
Thursday, March 22, 2012
A Very Disturbing Finding about SQL Server Performance
After some testing, we have found that, when a client
computer is connected to an MS SQL server, and they are
listening to a music cd, in their own computer, the
performance of the SQL server degrades badly, until the
client stops listening to the CD. We have used
performance monitor to check this and are incredulous at
the findings. I have gone out on the web and, after some
exhaustive searching, have come up with nothing written
on the subject. Has anyone else experienced this
behavior? Has anyone else tested this? Does anyone have
an idea as to why this may be happening and how we can
insulate the performance of the SQL server from the
casual activities of the clients?
Thanks in advance for your replies.
John MangioneI listen to CDs as well as many of our developers while
connected to SQL server. We've never experienced what you
mention here.
>--Original Message--
>After some testing, we have found that, when a client
>computer is connected to an MS SQL server, and they are
>listening to a music cd, in their own computer, the
>performance of the SQL server degrades badly, until the
>client stops listening to the CD. We have used
>performance monitor to check this and are incredulous at
>the findings. I have gone out on the web and, after some
>exhaustive searching, have come up with nothing written
>on the subject. Has anyone else experienced this
>behavior? Has anyone else tested this? Does anyone have
>an idea as to why this may be happening and how we can
>insulate the performance of the SQL server from the
>casual activities of the clients?
>Thanks in advance for your replies.
>John Mangione
>.
>|||When the application is designed, there are many ways of
implementing database access.
If its done through cursors or VB Recordsets, this could
well be the case.
A (I won't say better) perhaps more efficient way is to
use SET commands.
E.g.
Issue the command to UPDATE directly to the server, rather
than locking the cursor.
Microsoft do recognise this as being more efficient.
Good Luck.
>--Original Message--
>If listening to the CD slows down the client machine
while it talks to the
>SQL server (SELECTs, INSERTs, UPDATEs, etc) then maybe
the application and
>the SQL code locks too many resources so other clients
have to wait and that
>might slow down the server.
>"John Mangione" <jmangione@.adelphia.com> wrote in message
>news:015601c36298$8032f4c0$a601280a@.phx.gbl...
>> After some testing, we have found that, when a client
>> computer is connected to an MS SQL server, and they are
>> listening to a music cd, in their own computer, the
>> performance of the SQL server degrades badly, until the
>> client stops listening to the CD. We have used
>> performance monitor to check this and are incredulous at
>> the findings. I have gone out on the web and, after some
>> exhaustive searching, have come up with nothing written
>> on the subject. Has anyone else experienced this
>> behavior? Has anyone else tested this? Does anyone have
>> an idea as to why this may be happening and how we can
>> insulate the performance of the SQL server from the
>> casual activities of the clients?
>> Thanks in advance for your replies.
>> John Mangione
>
>.
>|||Are you using Windows Media Player to listen to the CD's
IF so do you have one of the 3D visualizations running in the back ground
Why, these 3D graphics can eat up between 30-50% of your CPU.
I do not have any idea about the spec's of the machine they are running on
but if it is a desktop with SQL running on it, it can slow them down
significantly
Hope this helps|||This problem may very well expose design flaws in your application.
Regardless of what a user does on his/her machine SQL server must never be
slown down. You do not open a cursor at the client side and keep it open for
a log time, actually you are better off if you never use client side
cursors.
"John Mangione" <jmangione@.adelphia.net> wrote in message
news:016401c36361$a74aa390$a501280a@.phx.gbl...
> Well, I found out some additional information that has
> lowered my anxiety about the issue: the only people that
> are experiencing the degradation of service are those who
> are listening to the music, not any other users of that
> SQL server or the application on it.
> The indicator that threw us off is that the user's
> instance of the process running at the SQL server was
> spiking every time the music was playing on their
> machine. We are still at a loss to explain that, although
> Beverly's response might be closest to what's going on;
> that is, perhaps the use of recordset and cursor locks
> are creating interdepedencies on the two machines that
> could be avoided by using SET commands.
> Thank you all for your replies.
>
> >--Original Message--
> >After some testing, we have found that, when a client
> >computer is connected to an MS SQL server, and they are
> >listening to a music cd, in their own computer, the
> >performance of the SQL server degrades badly, until the
> >client stops listening to the CD. We have used
> >performance monitor to check this and are incredulous at
> >the findings. I have gone out on the web and, after some
> >exhaustive searching, have come up with nothing written
> >on the subject. Has anyone else experienced this
> >behavior? Has anyone else tested this? Does anyone have
> >an idea as to why this may be happening and how we can
> >insulate the performance of the SQL server from the
> >casual activities of the clients?
> >
> >Thanks in advance for your replies.
> >John Mangione
> >.
> >
computer is connected to an MS SQL server, and they are
listening to a music cd, in their own computer, the
performance of the SQL server degrades badly, until the
client stops listening to the CD. We have used
performance monitor to check this and are incredulous at
the findings. I have gone out on the web and, after some
exhaustive searching, have come up with nothing written
on the subject. Has anyone else experienced this
behavior? Has anyone else tested this? Does anyone have
an idea as to why this may be happening and how we can
insulate the performance of the SQL server from the
casual activities of the clients?
Thanks in advance for your replies.
John MangioneI listen to CDs as well as many of our developers while
connected to SQL server. We've never experienced what you
mention here.
>--Original Message--
>After some testing, we have found that, when a client
>computer is connected to an MS SQL server, and they are
>listening to a music cd, in their own computer, the
>performance of the SQL server degrades badly, until the
>client stops listening to the CD. We have used
>performance monitor to check this and are incredulous at
>the findings. I have gone out on the web and, after some
>exhaustive searching, have come up with nothing written
>on the subject. Has anyone else experienced this
>behavior? Has anyone else tested this? Does anyone have
>an idea as to why this may be happening and how we can
>insulate the performance of the SQL server from the
>casual activities of the clients?
>Thanks in advance for your replies.
>John Mangione
>.
>|||When the application is designed, there are many ways of
implementing database access.
If its done through cursors or VB Recordsets, this could
well be the case.
A (I won't say better) perhaps more efficient way is to
use SET commands.
E.g.
Issue the command to UPDATE directly to the server, rather
than locking the cursor.
Microsoft do recognise this as being more efficient.
Good Luck.
>--Original Message--
>If listening to the CD slows down the client machine
while it talks to the
>SQL server (SELECTs, INSERTs, UPDATEs, etc) then maybe
the application and
>the SQL code locks too many resources so other clients
have to wait and that
>might slow down the server.
>"John Mangione" <jmangione@.adelphia.com> wrote in message
>news:015601c36298$8032f4c0$a601280a@.phx.gbl...
>> After some testing, we have found that, when a client
>> computer is connected to an MS SQL server, and they are
>> listening to a music cd, in their own computer, the
>> performance of the SQL server degrades badly, until the
>> client stops listening to the CD. We have used
>> performance monitor to check this and are incredulous at
>> the findings. I have gone out on the web and, after some
>> exhaustive searching, have come up with nothing written
>> on the subject. Has anyone else experienced this
>> behavior? Has anyone else tested this? Does anyone have
>> an idea as to why this may be happening and how we can
>> insulate the performance of the SQL server from the
>> casual activities of the clients?
>> Thanks in advance for your replies.
>> John Mangione
>
>.
>|||Are you using Windows Media Player to listen to the CD's
IF so do you have one of the 3D visualizations running in the back ground
Why, these 3D graphics can eat up between 30-50% of your CPU.
I do not have any idea about the spec's of the machine they are running on
but if it is a desktop with SQL running on it, it can slow them down
significantly
Hope this helps|||This problem may very well expose design flaws in your application.
Regardless of what a user does on his/her machine SQL server must never be
slown down. You do not open a cursor at the client side and keep it open for
a log time, actually you are better off if you never use client side
cursors.
"John Mangione" <jmangione@.adelphia.net> wrote in message
news:016401c36361$a74aa390$a501280a@.phx.gbl...
> Well, I found out some additional information that has
> lowered my anxiety about the issue: the only people that
> are experiencing the degradation of service are those who
> are listening to the music, not any other users of that
> SQL server or the application on it.
> The indicator that threw us off is that the user's
> instance of the process running at the SQL server was
> spiking every time the music was playing on their
> machine. We are still at a loss to explain that, although
> Beverly's response might be closest to what's going on;
> that is, perhaps the use of recordset and cursor locks
> are creating interdepedencies on the two machines that
> could be avoided by using SET commands.
> Thank you all for your replies.
>
> >--Original Message--
> >After some testing, we have found that, when a client
> >computer is connected to an MS SQL server, and they are
> >listening to a music cd, in their own computer, the
> >performance of the SQL server degrades badly, until the
> >client stops listening to the CD. We have used
> >performance monitor to check this and are incredulous at
> >the findings. I have gone out on the web and, after some
> >exhaustive searching, have come up with nothing written
> >on the subject. Has anyone else experienced this
> >behavior? Has anyone else tested this? Does anyone have
> >an idea as to why this may be happening and how we can
> >insulate the performance of the SQL server from the
> >casual activities of the clients?
> >
> >Thanks in advance for your replies.
> >John Mangione
> >.
> >
Thursday, March 8, 2012
A significant part of sql server process memory has been paged out.
A significant part of sql server process memory has been paged out.
This may result in a performance degradation. Duration: 0 seconds.
Working set (KB): 349620, committed (KB): 686376, memory utilization:
50%.
Has anyone been able to solve this problem on SQL Server Std Edition ?
I know the OS is trimming the Memory but apparently I have been
unable to find a way for this to stop.
"lock pages in memory" is disregarded in Std Edition.
Any thoughts ?
Add RAM to the box (or if there is more than 686 MB, increasing the amount
of money allocated to SQL Server)?
Lighten the workload?
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
<isyourfriend@.gmail.com> wrote in message
news:1183390681.575465.86070@.57g2000hsv.googlegrou ps.com...
>A significant part of sql server process memory has been paged out.
> This may result in a performance degradation. Duration: 0 seconds.
> Working set (KB): 349620, committed (KB): 686376, memory utilization:
> 50%.
> Has anyone been able to solve this problem on SQL Server Std Edition ?
> I know the OS is trimming the Memory but apparently I have been
> unable to find a way for this to stop.
> "lock pages in memory" is disregarded in Std Edition.
> Any thoughts ?
>
|||You need to know what is causing the memory shortage.
If it is the OS that really needs the memory then you probably want to
place a limit on SQL Server so that the max memory it uses leaves
enough for the OS. Starving the OS for memory guarantees poor
performance.
If there is some other application causing the memory shortage you
probably want to see if that application can be controlled.
Roy Harvey
Beacon Falls, CT
On Mon, 02 Jul 2007 15:38:01 -0000, isyourfriend@.gmail.com wrote:
>A significant part of sql server process memory has been paged out.
>This may result in a performance degradation. Duration: 0 seconds.
>Working set (KB): 349620, committed (KB): 686376, memory utilization:
>50%.
>Has anyone been able to solve this problem on SQL Server Std Edition ?
>I know the OS is trimming the Memory but apparently I have been
>unable to find a way for this to stop.
>"lock pages in memory" is disregarded in Std Edition.
>Any thoughts ?
|||Are other processes running on the server? This is suboptimal.
How much RAM total? If < max allowed, add ram.
Make sure your drivers and OS and sql server are fully patched. There are
numerous known bugs in the wild that will flush ram.
TheSQLGuru
President
Indicium Resources, Inc.
<isyourfriend@.gmail.com> wrote in message
news:1183390681.575465.86070@.57g2000hsv.googlegrou ps.com...
>A significant part of sql server process memory has been paged out.
> This may result in a performance degradation. Duration: 0 seconds.
> Working set (KB): 349620, committed (KB): 686376, memory utilization:
> 50%.
> Has anyone been able to solve this problem on SQL Server Std Edition ?
> I know the OS is trimming the Memory but apparently I have been
> unable to find a way for this to stop.
> "lock pages in memory" is disregarded in Std Edition.
> Any thoughts ?
>
|||On Jul 2, 11:57 pm, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:[vbcol=seagreen]
> Are other processes running on theserver? This is suboptimal.
> How much RAM total? If < max allowed, add ram.
> Make sure your drivers and OS andsqlserverare fully patched. There are
> numerous known bugs in the wild that will flush ram.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> <isyourfri...@.gmail.com> wrote in message
> news:1183390681.575465.86070@.57g2000hsv.googlegrou ps.com...
>
The Box has 12GB of RAM.
SQL Server max memory is set to 8GB.
Windows is patched to SP2.
I am copying though "very large" backup files (around 70~100GB ) from
and to the box.
Is there *anything* I can do?
|||On Jul 5, 9:19 am, isyourfri...@.gmail.com wrote:
> On Jul 2, 11:57 pm, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:
>
>
>
>
>
>
> The Box has 12GB of RAM.
> SQL Server max memory is set to 8GB.
> Windows is patched to SP2.
> I am copying though "very large" backup files (around 70~100GB ) from
> and to the box.
> Is there *anything* I can do?
I have to correct the data:
The Box has 16GB of RAM.
SQL Server max memory is 10GB
The rest is the same.
|||1) You did not answer my question about other services/apps running on this
box.
2) What version and is it 32/64 bit of sql server are you running?
3) Consider setting minimum sql server memor to see if this prevents the
paging. Also, have you been able to tie the paging to a specific event on
the server?
TheSQLGuru
President
Indicium Resources, Inc.
<isyourfriend@.gmail.com> wrote in message
news:1183642164.966037.25340@.q75g2000hsh.googlegro ups.com...
> On Jul 5, 9:19 am, isyourfri...@.gmail.com wrote:
> I have to correct the data:
> The Box has 16GB of RAM.
> SQL Server max memory is 10GB
> The rest is the same.
>
|||On Jul 5, 11:15 am, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:[vbcol=seagreen]
> 1) You did not answer my question about other services/apps running on this
> box.
> 2) What version and is it 32/64 bit of sql server are you running?
> 3) Consider setting minimum sql server memor to see if this prevents the
> paging. Also, have you been able to tie the paging to a specific event on
> the server?
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> <isyourfri...@.gmail.com> wrote in message
> news:1183642164.966037.25340@.q75g2000hsh.googlegro ups.com...
>
>
>
>
>
>
>
>
1) there are other scripts copying *very large* backup files around.
2) this is x64 SQL Server 2005 Standard Edition built 3161
3) It *does* happens when copying those large files
Now, is there a way to prevent this Huge Memory Flush ?
-Noel
This may result in a performance degradation. Duration: 0 seconds.
Working set (KB): 349620, committed (KB): 686376, memory utilization:
50%.
Has anyone been able to solve this problem on SQL Server Std Edition ?
I know the OS is trimming the Memory but apparently I have been
unable to find a way for this to stop.
"lock pages in memory" is disregarded in Std Edition.
Any thoughts ?
Add RAM to the box (or if there is more than 686 MB, increasing the amount
of money allocated to SQL Server)?
Lighten the workload?
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
<isyourfriend@.gmail.com> wrote in message
news:1183390681.575465.86070@.57g2000hsv.googlegrou ps.com...
>A significant part of sql server process memory has been paged out.
> This may result in a performance degradation. Duration: 0 seconds.
> Working set (KB): 349620, committed (KB): 686376, memory utilization:
> 50%.
> Has anyone been able to solve this problem on SQL Server Std Edition ?
> I know the OS is trimming the Memory but apparently I have been
> unable to find a way for this to stop.
> "lock pages in memory" is disregarded in Std Edition.
> Any thoughts ?
>
|||You need to know what is causing the memory shortage.
If it is the OS that really needs the memory then you probably want to
place a limit on SQL Server so that the max memory it uses leaves
enough for the OS. Starving the OS for memory guarantees poor
performance.
If there is some other application causing the memory shortage you
probably want to see if that application can be controlled.
Roy Harvey
Beacon Falls, CT
On Mon, 02 Jul 2007 15:38:01 -0000, isyourfriend@.gmail.com wrote:
>A significant part of sql server process memory has been paged out.
>This may result in a performance degradation. Duration: 0 seconds.
>Working set (KB): 349620, committed (KB): 686376, memory utilization:
>50%.
>Has anyone been able to solve this problem on SQL Server Std Edition ?
>I know the OS is trimming the Memory but apparently I have been
>unable to find a way for this to stop.
>"lock pages in memory" is disregarded in Std Edition.
>Any thoughts ?
|||Are other processes running on the server? This is suboptimal.
How much RAM total? If < max allowed, add ram.
Make sure your drivers and OS and sql server are fully patched. There are
numerous known bugs in the wild that will flush ram.
TheSQLGuru
President
Indicium Resources, Inc.
<isyourfriend@.gmail.com> wrote in message
news:1183390681.575465.86070@.57g2000hsv.googlegrou ps.com...
>A significant part of sql server process memory has been paged out.
> This may result in a performance degradation. Duration: 0 seconds.
> Working set (KB): 349620, committed (KB): 686376, memory utilization:
> 50%.
> Has anyone been able to solve this problem on SQL Server Std Edition ?
> I know the OS is trimming the Memory but apparently I have been
> unable to find a way for this to stop.
> "lock pages in memory" is disregarded in Std Edition.
> Any thoughts ?
>
|||On Jul 2, 11:57 pm, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:[vbcol=seagreen]
> Are other processes running on theserver? This is suboptimal.
> How much RAM total? If < max allowed, add ram.
> Make sure your drivers and OS andsqlserverare fully patched. There are
> numerous known bugs in the wild that will flush ram.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> <isyourfri...@.gmail.com> wrote in message
> news:1183390681.575465.86070@.57g2000hsv.googlegrou ps.com...
>
The Box has 12GB of RAM.
SQL Server max memory is set to 8GB.
Windows is patched to SP2.
I am copying though "very large" backup files (around 70~100GB ) from
and to the box.
Is there *anything* I can do?
|||On Jul 5, 9:19 am, isyourfri...@.gmail.com wrote:
> On Jul 2, 11:57 pm, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:
>
>
>
>
>
>
> The Box has 12GB of RAM.
> SQL Server max memory is set to 8GB.
> Windows is patched to SP2.
> I am copying though "very large" backup files (around 70~100GB ) from
> and to the box.
> Is there *anything* I can do?
I have to correct the data:
The Box has 16GB of RAM.
SQL Server max memory is 10GB
The rest is the same.
|||1) You did not answer my question about other services/apps running on this
box.
2) What version and is it 32/64 bit of sql server are you running?
3) Consider setting minimum sql server memor to see if this prevents the
paging. Also, have you been able to tie the paging to a specific event on
the server?
TheSQLGuru
President
Indicium Resources, Inc.
<isyourfriend@.gmail.com> wrote in message
news:1183642164.966037.25340@.q75g2000hsh.googlegro ups.com...
> On Jul 5, 9:19 am, isyourfri...@.gmail.com wrote:
> I have to correct the data:
> The Box has 16GB of RAM.
> SQL Server max memory is 10GB
> The rest is the same.
>
|||On Jul 5, 11:15 am, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:[vbcol=seagreen]
> 1) You did not answer my question about other services/apps running on this
> box.
> 2) What version and is it 32/64 bit of sql server are you running?
> 3) Consider setting minimum sql server memor to see if this prevents the
> paging. Also, have you been able to tie the paging to a specific event on
> the server?
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> <isyourfri...@.gmail.com> wrote in message
> news:1183642164.966037.25340@.q75g2000hsh.googlegro ups.com...
>
>
>
>
>
>
>
>
1) there are other scripts copying *very large* backup files around.
2) this is x64 SQL Server 2005 Standard Edition built 3161
3) It *does* happens when copying those large files
Now, is there a way to prevent this Huge Memory Flush ?
-Noel
A significant part of sql server process memory has been paged out.
A significant part of sql server process memory has been paged out.
This may result in a performance degradation. Duration: 0 seconds.
Working set (KB): 349620, committed (KB): 686376, memory utilization:
50%.
Has anyone been able to solve this problem on SQL Server Std Edition ?
I know the OS is trimming the Memory but apparently I have been
unable to find a way for this to stop.
"lock pages in memory" is disregarded in Std Edition.
Any thoughts ?Add RAM to the box (or if there is more than 686 MB, increasing the amount
of money allocated to SQL Server)?
Lighten the workload?
--
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
<isyourfriend@.gmail.com> wrote in message
news:1183390681.575465.86070@.57g2000hsv.googlegroups.com...
>A significant part of sql server process memory has been paged out.
> This may result in a performance degradation. Duration: 0 seconds.
> Working set (KB): 349620, committed (KB): 686376, memory utilization:
> 50%.
> Has anyone been able to solve this problem on SQL Server Std Edition ?
> I know the OS is trimming the Memory but apparently I have been
> unable to find a way for this to stop.
> "lock pages in memory" is disregarded in Std Edition.
> Any thoughts ?
>|||You need to know what is causing the memory shortage.
If it is the OS that really needs the memory then you probably want to
place a limit on SQL Server so that the max memory it uses leaves
enough for the OS. Starving the OS for memory guarantees poor
performance.
If there is some other application causing the memory shortage you
probably want to see if that application can be controlled.
Roy Harvey
Beacon Falls, CT
On Mon, 02 Jul 2007 15:38:01 -0000, isyourfriend@.gmail.com wrote:
>A significant part of sql server process memory has been paged out.
>This may result in a performance degradation. Duration: 0 seconds.
>Working set (KB): 349620, committed (KB): 686376, memory utilization:
>50%.
>Has anyone been able to solve this problem on SQL Server Std Edition ?
>I know the OS is trimming the Memory but apparently I have been
>unable to find a way for this to stop.
>"lock pages in memory" is disregarded in Std Edition.
>Any thoughts ?|||Are other processes running on the server? This is suboptimal.
How much RAM total? If < max allowed, add ram.
Make sure your drivers and OS and sql server are fully patched. There are
numerous known bugs in the wild that will flush ram.
--
TheSQLGuru
President
Indicium Resources, Inc.
<isyourfriend@.gmail.com> wrote in message
news:1183390681.575465.86070@.57g2000hsv.googlegroups.com...
>A significant part of sql server process memory has been paged out.
> This may result in a performance degradation. Duration: 0 seconds.
> Working set (KB): 349620, committed (KB): 686376, memory utilization:
> 50%.
> Has anyone been able to solve this problem on SQL Server Std Edition ?
> I know the OS is trimming the Memory but apparently I have been
> unable to find a way for this to stop.
> "lock pages in memory" is disregarded in Std Edition.
> Any thoughts ?
>|||On Jul 2, 11:57 pm, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:
> Are other processes running on theserver? This is suboptimal.
> How much RAM total? If < max allowed, add ram.
> Make sure your drivers and OS andsqlserverare fully patched. There are
> numerous known bugs in the wild that will flush ram.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> <isyourfri...@.gmail.com> wrote in message
> news:1183390681.575465.86070@.57g2000hsv.googlegroups.com...
> >Asignificantpartofsqlserverprocessmemoryhas been paged out.
> > This may result in a performance degradation. Duration: 0 seconds.
> > Working set (KB): 349620, committed (KB): 686376,memoryutilization:
> > 50%.
> > Has anyone been able to solve this problem onSQLServerStd Edition ?
> > I know the OS is trimming theMemorybut apparently I have been
> > unable to find a way for this to stop.
> > "lock pages inmemory" is disregarded in Std Edition.
> > Any thoughts ?
The Box has 12GB of RAM.
SQL Server max memory is set to 8GB.
Windows is patched to SP2.
I am copying though "very large" backup files (around 70~100GB ) from
and to the box.
Is there *anything* I can do?|||On Jul 5, 9:19 am, isyourfri...@.gmail.com wrote:
> On Jul 2, 11:57 pm, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:
>
> > Are other processes running on theserver? This is suboptimal.
> > How much RAM total? If < max allowed, add ram.
> > Make sure your drivers and OS andsqlserverare fully patched. There are
> > numerous known bugs in the wild that will flush ram.
> > --
> > TheSQLGuru
> > President
> > Indicium Resources, Inc.
> > <isyourfri...@.gmail.com> wrote in message
> >news:1183390681.575465.86070@.57g2000hsv.googlegroups.com...
> > >Asignificantpartofsqlserverprocessmemoryhas been paged out.
> > > This may result in a performance degradation. Duration: 0 seconds.
> > > Working set (KB): 349620, committed (KB): 686376,memoryutilization:
> > > 50%.
> > > Has anyone been able to solve this problem onSQLServerStd Edition ?
> > > I know the OS is trimming theMemorybut apparently I have been
> > > unable to find a way for this to stop.
> > > "lock pages inmemory" is disregarded in Std Edition.
> > > Any thoughts ?
> The Box has 12GB of RAM.
> SQL Server max memory is set to 8GB.
> Windows is patched to SP2.
> I am copying though "very large" backup files (around 70~100GB ) from
> and to the box.
> Is there *anything* I can do?
I have to correct the data:
The Box has 16GB of RAM.
SQL Server max memory is 10GB
The rest is the same.|||1) You did not answer my question about other services/apps running on this
box.
2) What version and is it 32/64 bit of sql server are you running?
3) Consider setting minimum sql server memor to see if this prevents the
paging. Also, have you been able to tie the paging to a specific event on
the server?
--
TheSQLGuru
President
Indicium Resources, Inc.
<isyourfriend@.gmail.com> wrote in message
news:1183642164.966037.25340@.q75g2000hsh.googlegroups.com...
> On Jul 5, 9:19 am, isyourfri...@.gmail.com wrote:
>> On Jul 2, 11:57 pm, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:
>>
>> > Are other processes running on theserver? This is suboptimal.
>> > How much RAM total? If < max allowed, add ram.
>> > Make sure your drivers and OS andsqlserverare fully patched. There are
>> > numerous known bugs in the wild that will flush ram.
>> > --
>> > TheSQLGuru
>> > President
>> > Indicium Resources, Inc.
>> > <isyourfri...@.gmail.com> wrote in message
>> >news:1183390681.575465.86070@.57g2000hsv.googlegroups.com...
>> > >Asignificantpartofsqlserverprocessmemoryhas been paged out.
>> > > This may result in a performance degradation. Duration: 0 seconds.
>> > > Working set (KB): 349620, committed (KB): 686376,memoryutilization:
>> > > 50%.
>> > > Has anyone been able to solve this problem onSQLServerStd Edition ?
>> > > I know the OS is trimming theMemorybut apparently I have been
>> > > unable to find a way for this to stop.
>> > > "lock pages inmemory" is disregarded in Std Edition.
>> > > Any thoughts ?
>> The Box has 12GB of RAM.
>> SQL Server max memory is set to 8GB.
>> Windows is patched to SP2.
>> I am copying though "very large" backup files (around 70~100GB ) from
>> and to the box.
>> Is there *anything* I can do?
> I have to correct the data:
> The Box has 16GB of RAM.
> SQL Server max memory is 10GB
> The rest is the same.
>|||On Jul 5, 11:15 am, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:
> 1) You did not answer my question about other services/apps running on this
> box.
> 2) What version and is it 32/64 bit of sql server are you running?
> 3) Consider setting minimum sql server memor to see if this prevents the
> paging. Also, have you been able to tie the paging to a specific event on
> the server?
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> <isyourfri...@.gmail.com> wrote in message
> news:1183642164.966037.25340@.q75g2000hsh.googlegroups.com...
> > On Jul 5, 9:19 am, isyourfri...@.gmail.com wrote:
> >> On Jul 2, 11:57 pm, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:
> >> > Are other processes running on theserver? This is suboptimal.
> >> > How much RAM total? If < max allowed, add ram.
> >> > Make sure your drivers and OS andsqlserverare fully patched. There are
> >> > numerous known bugs in the wild that will flush ram.
> >> > --
> >> > TheSQLGuru
> >> > President
> >> > Indicium Resources, Inc.
> >> > <isyourfri...@.gmail.com> wrote in message
> >> >news:1183390681.575465.86070@.57g2000hsv.googlegroups.com...
> >> > >Asignificantpartofsqlserverprocessmemoryhas been paged out.
> >> > > This may result in a performance degradation. Duration: 0 seconds.
> >> > > Working set (KB): 349620, committed (KB): 686376,memoryutilization:
> >> > > 50%.
> >> > > Has anyone been able to solve this problem onSQLServerStd Edition ?
> >> > > I know the OS is trimming theMemorybut apparently I have been
> >> > > unable to find a way for this to stop.
> >> > > "lock pages inmemory" is disregarded in Std Edition.
> >> > > Any thoughts ?
> >> The Box has 12GB of RAM.
> >> SQL Server max memory is set to 8GB.
> >> Windows is patched to SP2.
> >> I am copying though "very large" backup files (around 70~100GB ) from
> >> and to the box.
> >> Is there *anything* I can do?
> > I have to correct the data:
> > The Box has 16GB of RAM.
> > SQL Server max memory is 10GB
> > The rest is the same.
1) there are other scripts copying *very large* backup files around.
2) this is x64 SQL Server 2005 Standard Edition built 3161
3) It *does* happens when copying those large files
Now, is there a way to prevent this Huge Memory Flush ?
-Noel
This may result in a performance degradation. Duration: 0 seconds.
Working set (KB): 349620, committed (KB): 686376, memory utilization:
50%.
Has anyone been able to solve this problem on SQL Server Std Edition ?
I know the OS is trimming the Memory but apparently I have been
unable to find a way for this to stop.
"lock pages in memory" is disregarded in Std Edition.
Any thoughts ?Add RAM to the box (or if there is more than 686 MB, increasing the amount
of money allocated to SQL Server)?
Lighten the workload?
--
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
<isyourfriend@.gmail.com> wrote in message
news:1183390681.575465.86070@.57g2000hsv.googlegroups.com...
>A significant part of sql server process memory has been paged out.
> This may result in a performance degradation. Duration: 0 seconds.
> Working set (KB): 349620, committed (KB): 686376, memory utilization:
> 50%.
> Has anyone been able to solve this problem on SQL Server Std Edition ?
> I know the OS is trimming the Memory but apparently I have been
> unable to find a way for this to stop.
> "lock pages in memory" is disregarded in Std Edition.
> Any thoughts ?
>|||You need to know what is causing the memory shortage.
If it is the OS that really needs the memory then you probably want to
place a limit on SQL Server so that the max memory it uses leaves
enough for the OS. Starving the OS for memory guarantees poor
performance.
If there is some other application causing the memory shortage you
probably want to see if that application can be controlled.
Roy Harvey
Beacon Falls, CT
On Mon, 02 Jul 2007 15:38:01 -0000, isyourfriend@.gmail.com wrote:
>A significant part of sql server process memory has been paged out.
>This may result in a performance degradation. Duration: 0 seconds.
>Working set (KB): 349620, committed (KB): 686376, memory utilization:
>50%.
>Has anyone been able to solve this problem on SQL Server Std Edition ?
>I know the OS is trimming the Memory but apparently I have been
>unable to find a way for this to stop.
>"lock pages in memory" is disregarded in Std Edition.
>Any thoughts ?|||Are other processes running on the server? This is suboptimal.
How much RAM total? If < max allowed, add ram.
Make sure your drivers and OS and sql server are fully patched. There are
numerous known bugs in the wild that will flush ram.
--
TheSQLGuru
President
Indicium Resources, Inc.
<isyourfriend@.gmail.com> wrote in message
news:1183390681.575465.86070@.57g2000hsv.googlegroups.com...
>A significant part of sql server process memory has been paged out.
> This may result in a performance degradation. Duration: 0 seconds.
> Working set (KB): 349620, committed (KB): 686376, memory utilization:
> 50%.
> Has anyone been able to solve this problem on SQL Server Std Edition ?
> I know the OS is trimming the Memory but apparently I have been
> unable to find a way for this to stop.
> "lock pages in memory" is disregarded in Std Edition.
> Any thoughts ?
>|||On Jul 2, 11:57 pm, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:
> Are other processes running on theserver? This is suboptimal.
> How much RAM total? If < max allowed, add ram.
> Make sure your drivers and OS andsqlserverare fully patched. There are
> numerous known bugs in the wild that will flush ram.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> <isyourfri...@.gmail.com> wrote in message
> news:1183390681.575465.86070@.57g2000hsv.googlegroups.com...
> >Asignificantpartofsqlserverprocessmemoryhas been paged out.
> > This may result in a performance degradation. Duration: 0 seconds.
> > Working set (KB): 349620, committed (KB): 686376,memoryutilization:
> > 50%.
> > Has anyone been able to solve this problem onSQLServerStd Edition ?
> > I know the OS is trimming theMemorybut apparently I have been
> > unable to find a way for this to stop.
> > "lock pages inmemory" is disregarded in Std Edition.
> > Any thoughts ?
The Box has 12GB of RAM.
SQL Server max memory is set to 8GB.
Windows is patched to SP2.
I am copying though "very large" backup files (around 70~100GB ) from
and to the box.
Is there *anything* I can do?|||On Jul 5, 9:19 am, isyourfri...@.gmail.com wrote:
> On Jul 2, 11:57 pm, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:
>
> > Are other processes running on theserver? This is suboptimal.
> > How much RAM total? If < max allowed, add ram.
> > Make sure your drivers and OS andsqlserverare fully patched. There are
> > numerous known bugs in the wild that will flush ram.
> > --
> > TheSQLGuru
> > President
> > Indicium Resources, Inc.
> > <isyourfri...@.gmail.com> wrote in message
> >news:1183390681.575465.86070@.57g2000hsv.googlegroups.com...
> > >Asignificantpartofsqlserverprocessmemoryhas been paged out.
> > > This may result in a performance degradation. Duration: 0 seconds.
> > > Working set (KB): 349620, committed (KB): 686376,memoryutilization:
> > > 50%.
> > > Has anyone been able to solve this problem onSQLServerStd Edition ?
> > > I know the OS is trimming theMemorybut apparently I have been
> > > unable to find a way for this to stop.
> > > "lock pages inmemory" is disregarded in Std Edition.
> > > Any thoughts ?
> The Box has 12GB of RAM.
> SQL Server max memory is set to 8GB.
> Windows is patched to SP2.
> I am copying though "very large" backup files (around 70~100GB ) from
> and to the box.
> Is there *anything* I can do?
I have to correct the data:
The Box has 16GB of RAM.
SQL Server max memory is 10GB
The rest is the same.|||1) You did not answer my question about other services/apps running on this
box.
2) What version and is it 32/64 bit of sql server are you running?
3) Consider setting minimum sql server memor to see if this prevents the
paging. Also, have you been able to tie the paging to a specific event on
the server?
--
TheSQLGuru
President
Indicium Resources, Inc.
<isyourfriend@.gmail.com> wrote in message
news:1183642164.966037.25340@.q75g2000hsh.googlegroups.com...
> On Jul 5, 9:19 am, isyourfri...@.gmail.com wrote:
>> On Jul 2, 11:57 pm, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:
>>
>> > Are other processes running on theserver? This is suboptimal.
>> > How much RAM total? If < max allowed, add ram.
>> > Make sure your drivers and OS andsqlserverare fully patched. There are
>> > numerous known bugs in the wild that will flush ram.
>> > --
>> > TheSQLGuru
>> > President
>> > Indicium Resources, Inc.
>> > <isyourfri...@.gmail.com> wrote in message
>> >news:1183390681.575465.86070@.57g2000hsv.googlegroups.com...
>> > >Asignificantpartofsqlserverprocessmemoryhas been paged out.
>> > > This may result in a performance degradation. Duration: 0 seconds.
>> > > Working set (KB): 349620, committed (KB): 686376,memoryutilization:
>> > > 50%.
>> > > Has anyone been able to solve this problem onSQLServerStd Edition ?
>> > > I know the OS is trimming theMemorybut apparently I have been
>> > > unable to find a way for this to stop.
>> > > "lock pages inmemory" is disregarded in Std Edition.
>> > > Any thoughts ?
>> The Box has 12GB of RAM.
>> SQL Server max memory is set to 8GB.
>> Windows is patched to SP2.
>> I am copying though "very large" backup files (around 70~100GB ) from
>> and to the box.
>> Is there *anything* I can do?
> I have to correct the data:
> The Box has 16GB of RAM.
> SQL Server max memory is 10GB
> The rest is the same.
>|||On Jul 5, 11:15 am, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:
> 1) You did not answer my question about other services/apps running on this
> box.
> 2) What version and is it 32/64 bit of sql server are you running?
> 3) Consider setting minimum sql server memor to see if this prevents the
> paging. Also, have you been able to tie the paging to a specific event on
> the server?
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> <isyourfri...@.gmail.com> wrote in message
> news:1183642164.966037.25340@.q75g2000hsh.googlegroups.com...
> > On Jul 5, 9:19 am, isyourfri...@.gmail.com wrote:
> >> On Jul 2, 11:57 pm, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:
> >> > Are other processes running on theserver? This is suboptimal.
> >> > How much RAM total? If < max allowed, add ram.
> >> > Make sure your drivers and OS andsqlserverare fully patched. There are
> >> > numerous known bugs in the wild that will flush ram.
> >> > --
> >> > TheSQLGuru
> >> > President
> >> > Indicium Resources, Inc.
> >> > <isyourfri...@.gmail.com> wrote in message
> >> >news:1183390681.575465.86070@.57g2000hsv.googlegroups.com...
> >> > >Asignificantpartofsqlserverprocessmemoryhas been paged out.
> >> > > This may result in a performance degradation. Duration: 0 seconds.
> >> > > Working set (KB): 349620, committed (KB): 686376,memoryutilization:
> >> > > 50%.
> >> > > Has anyone been able to solve this problem onSQLServerStd Edition ?
> >> > > I know the OS is trimming theMemorybut apparently I have been
> >> > > unable to find a way for this to stop.
> >> > > "lock pages inmemory" is disregarded in Std Edition.
> >> > > Any thoughts ?
> >> The Box has 12GB of RAM.
> >> SQL Server max memory is set to 8GB.
> >> Windows is patched to SP2.
> >> I am copying though "very large" backup files (around 70~100GB ) from
> >> and to the box.
> >> Is there *anything* I can do?
> > I have to correct the data:
> > The Box has 16GB of RAM.
> > SQL Server max memory is 10GB
> > The rest is the same.
1) there are other scripts copying *very large* backup files around.
2) this is x64 SQL Server 2005 Standard Edition built 3161
3) It *does* happens when copying those large files
Now, is there a way to prevent this Huge Memory Flush ?
-Noel
A significant part of sql server process memory has been paged out.
A significant part of sql server process memory has been paged out.
This may result in a performance degradation. Duration: 0 seconds.
Working set (KB): 349620, committed (KB): 686376, memory utilization:
50%.
Has anyone been able to solve this problem on SQL Server Std Edition ?
I know the OS is trimming the Memory but apparently I have been
unable to find a way for this to stop.
"lock pages in memory" is disregarded in Std Edition.
Any thoughts ?Add RAM to the box (or if there is more than 686 MB, increasing the amount
of money allocated to SQL Server)?
Lighten the workload?
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
<isyourfriend@.gmail.com> wrote in message
news:1183390681.575465.86070@.57g2000hsv.googlegroups.com...
>A significant part of sql server process memory has been paged out.
> This may result in a performance degradation. Duration: 0 seconds.
> Working set (KB): 349620, committed (KB): 686376, memory utilization:
> 50%.
> Has anyone been able to solve this problem on SQL Server Std Edition ?
> I know the OS is trimming the Memory but apparently I have been
> unable to find a way for this to stop.
> "lock pages in memory" is disregarded in Std Edition.
> Any thoughts ?
>|||You need to know what is causing the memory shortage.
If it is the OS that really needs the memory then you probably want to
place a limit on SQL Server so that the max memory it uses leaves
enough for the OS. Starving the OS for memory guarantees poor
performance.
If there is some other application causing the memory shortage you
probably want to see if that application can be controlled.
Roy Harvey
Beacon Falls, CT
On Mon, 02 Jul 2007 15:38:01 -0000, isyourfriend@.gmail.com wrote:
>A significant part of sql server process memory has been paged out.
>This may result in a performance degradation. Duration: 0 seconds.
>Working set (KB): 349620, committed (KB): 686376, memory utilization:
>50%.
>Has anyone been able to solve this problem on SQL Server Std Edition ?
>I know the OS is trimming the Memory but apparently I have been
>unable to find a way for this to stop.
>"lock pages in memory" is disregarded in Std Edition.
>Any thoughts ?|||Are other processes running on the server? This is suboptimal.
How much RAM total? If < max allowed, add ram.
Make sure your drivers and OS and sql server are fully patched. There are
numerous known bugs in the wild that will flush ram.
TheSQLGuru
President
Indicium Resources, Inc.
<isyourfriend@.gmail.com> wrote in message
news:1183390681.575465.86070@.57g2000hsv.googlegroups.com...
>A significant part of sql server process memory has been paged out.
> This may result in a performance degradation. Duration: 0 seconds.
> Working set (KB): 349620, committed (KB): 686376, memory utilization:
> 50%.
> Has anyone been able to solve this problem on SQL Server Std Edition ?
> I know the OS is trimming the Memory but apparently I have been
> unable to find a way for this to stop.
> "lock pages in memory" is disregarded in Std Edition.
> Any thoughts ?
>|||On Jul 2, 11:57 pm, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:[vbcol=seagreen]
> Are other processes running on theserver? This is suboptimal.
> How much RAM total? If < max allowed, add ram.
> Make sure your drivers and OS andsqlserverare fully patched. There are
> numerous known bugs in the wild that will flush ram.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> <isyourfri...@.gmail.com> wrote in message
> news:1183390681.575465.86070@.57g2000hsv.googlegroups.com...
>
>
>
The Box has 12GB of RAM.
SQL Server max memory is set to 8GB.
Windows is patched to SP2.
I am copying though "very large" backup files (around 70~100GB ) from
and to the box.
Is there *anything* I can do?|||On Jul 5, 9:19 am, isyourfri...@.gmail.com wrote:
> On Jul 2, 11:57 pm, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:
>
>
>
>
>
>
>
>
>
>
> The Box has 12GB of RAM.
> SQL Server max memory is set to 8GB.
> Windows is patched to SP2.
> I am copying though "very large" backup files (around 70~100GB ) from
> and to the box.
> Is there *anything* I can do?
I have to correct the data:
The Box has 16GB of RAM.
SQL Server max memory is 10GB
The rest is the same.|||1) You did not answer my question about other services/apps running on this
box.
2) What version and is it 32/64 bit of sql server are you running?
3) Consider setting minimum sql server memor to see if this prevents the
paging. Also, have you been able to tie the paging to a specific event on
the server?
TheSQLGuru
President
Indicium Resources, Inc.
<isyourfriend@.gmail.com> wrote in message
news:1183642164.966037.25340@.q75g2000hsh.googlegroups.com...
> On Jul 5, 9:19 am, isyourfri...@.gmail.com wrote:
> I have to correct the data:
> The Box has 16GB of RAM.
> SQL Server max memory is 10GB
> The rest is the same.
>|||On Jul 5, 11:15 am, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:[vbcol=seagreen]
> 1) You did not answer my question about other services/apps running on thi
s
> box.
> 2) What version and is it 32/64 bit of sql server are you running?
> 3) Consider setting minimum sql server memor to see if this prevents the
> paging. Also, have you been able to tie the paging to a specific event on
> the server?
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> <isyourfri...@.gmail.com> wrote in message
> news:1183642164.966037.25340@.q75g2000hsh.googlegroups.com...
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
1) there are other scripts copying *very large* backup files around.
2) this is x64 SQL Server 2005 Standard Edition built 3161
3) It *does* happens when copying those large files
Now, is there a way to prevent this Huge Memory Flush ?
-Noel
This may result in a performance degradation. Duration: 0 seconds.
Working set (KB): 349620, committed (KB): 686376, memory utilization:
50%.
Has anyone been able to solve this problem on SQL Server Std Edition ?
I know the OS is trimming the Memory but apparently I have been
unable to find a way for this to stop.
"lock pages in memory" is disregarded in Std Edition.
Any thoughts ?Add RAM to the box (or if there is more than 686 MB, increasing the amount
of money allocated to SQL Server)?
Lighten the workload?
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
<isyourfriend@.gmail.com> wrote in message
news:1183390681.575465.86070@.57g2000hsv.googlegroups.com...
>A significant part of sql server process memory has been paged out.
> This may result in a performance degradation. Duration: 0 seconds.
> Working set (KB): 349620, committed (KB): 686376, memory utilization:
> 50%.
> Has anyone been able to solve this problem on SQL Server Std Edition ?
> I know the OS is trimming the Memory but apparently I have been
> unable to find a way for this to stop.
> "lock pages in memory" is disregarded in Std Edition.
> Any thoughts ?
>|||You need to know what is causing the memory shortage.
If it is the OS that really needs the memory then you probably want to
place a limit on SQL Server so that the max memory it uses leaves
enough for the OS. Starving the OS for memory guarantees poor
performance.
If there is some other application causing the memory shortage you
probably want to see if that application can be controlled.
Roy Harvey
Beacon Falls, CT
On Mon, 02 Jul 2007 15:38:01 -0000, isyourfriend@.gmail.com wrote:
>A significant part of sql server process memory has been paged out.
>This may result in a performance degradation. Duration: 0 seconds.
>Working set (KB): 349620, committed (KB): 686376, memory utilization:
>50%.
>Has anyone been able to solve this problem on SQL Server Std Edition ?
>I know the OS is trimming the Memory but apparently I have been
>unable to find a way for this to stop.
>"lock pages in memory" is disregarded in Std Edition.
>Any thoughts ?|||Are other processes running on the server? This is suboptimal.
How much RAM total? If < max allowed, add ram.
Make sure your drivers and OS and sql server are fully patched. There are
numerous known bugs in the wild that will flush ram.
TheSQLGuru
President
Indicium Resources, Inc.
<isyourfriend@.gmail.com> wrote in message
news:1183390681.575465.86070@.57g2000hsv.googlegroups.com...
>A significant part of sql server process memory has been paged out.
> This may result in a performance degradation. Duration: 0 seconds.
> Working set (KB): 349620, committed (KB): 686376, memory utilization:
> 50%.
> Has anyone been able to solve this problem on SQL Server Std Edition ?
> I know the OS is trimming the Memory but apparently I have been
> unable to find a way for this to stop.
> "lock pages in memory" is disregarded in Std Edition.
> Any thoughts ?
>|||On Jul 2, 11:57 pm, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:[vbcol=seagreen]
> Are other processes running on theserver? This is suboptimal.
> How much RAM total? If < max allowed, add ram.
> Make sure your drivers and OS andsqlserverare fully patched. There are
> numerous known bugs in the wild that will flush ram.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> <isyourfri...@.gmail.com> wrote in message
> news:1183390681.575465.86070@.57g2000hsv.googlegroups.com...
>
>
>
The Box has 12GB of RAM.
SQL Server max memory is set to 8GB.
Windows is patched to SP2.
I am copying though "very large" backup files (around 70~100GB ) from
and to the box.
Is there *anything* I can do?|||On Jul 5, 9:19 am, isyourfri...@.gmail.com wrote:
> On Jul 2, 11:57 pm, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:
>
>
>
>
>
>
>
>
>
>
> The Box has 12GB of RAM.
> SQL Server max memory is set to 8GB.
> Windows is patched to SP2.
> I am copying though "very large" backup files (around 70~100GB ) from
> and to the box.
> Is there *anything* I can do?
I have to correct the data:
The Box has 16GB of RAM.
SQL Server max memory is 10GB
The rest is the same.|||1) You did not answer my question about other services/apps running on this
box.
2) What version and is it 32/64 bit of sql server are you running?
3) Consider setting minimum sql server memor to see if this prevents the
paging. Also, have you been able to tie the paging to a specific event on
the server?
TheSQLGuru
President
Indicium Resources, Inc.
<isyourfriend@.gmail.com> wrote in message
news:1183642164.966037.25340@.q75g2000hsh.googlegroups.com...
> On Jul 5, 9:19 am, isyourfri...@.gmail.com wrote:
> I have to correct the data:
> The Box has 16GB of RAM.
> SQL Server max memory is 10GB
> The rest is the same.
>|||On Jul 5, 11:15 am, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:[vbcol=seagreen]
> 1) You did not answer my question about other services/apps running on thi
s
> box.
> 2) What version and is it 32/64 bit of sql server are you running?
> 3) Consider setting minimum sql server memor to see if this prevents the
> paging. Also, have you been able to tie the paging to a specific event on
> the server?
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> <isyourfri...@.gmail.com> wrote in message
> news:1183642164.966037.25340@.q75g2000hsh.googlegroups.com...
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
1) there are other scripts copying *very large* backup files around.
2) this is x64 SQL Server 2005 Standard Edition built 3161
3) It *does* happens when copying those large files
Now, is there a way to prevent this Huge Memory Flush ?
-Noel
Friday, February 24, 2012
A QUERY THAT RUN ON DB2 THAT HAVE MORE PERFORMANCE THAN SQL SERVER 2000
The execution time for this query on DB2 v8.0 DBMS one second but I execute it on SQL SERVER 2000 is around 55 second
so how i can incease the performance for SQL server
SELECT ACC_KEY1,ACC_STATUS_LAST FROM PSSIG.CLNT_ACCOUNTS INNER JOIN PSSIG.CLNT_CUSTOMERS ON
PSSIG.CLNT_ACCOUNTS.CSTMR_OID = PSSIG.CLNT_CUSTOMERS.CSTMR_OID
WHERE (PSSIG.CLNT_CUSTOMERS.CSTMR_START_DT >= '1900-1-1 12:00:00') AND
(PSSIG.CLNT_CUSTOMERS.CSTMR_END_DT <= '2106-12-31 12:00:00') AND
(PSSIG.CLNT_ACCOUNTS.ACC_KEY1 >= '0000000000000') AND
(PSSIG.CLNT_ACCOUNTS.ACC_KEY1 <= '9999999999999') AND
(PSSIG.CLNT_ACCOUNTS.ACC_STATUS_LAST = 5 ) AND
ACC_KEY1 > '0' ORDER BY ACC_KEY1
Note 1: value 5 exist in most of rows about ( 999999/1000000 ) from the table rows count
Note 2: the number of rows in each table around 15000000
Note 3: I used the same index structure for both DB2 and SQL server 2000
Note 4: I used some other feature in DB2 that increase the performance but I did not
found the alternative for it in SQL server 2000 :
a- cardinality varies at run time feature
b- include column in index instead of use compound index for
( ACC_KEY1 ,ACC_STATUS_LAST ) columns
Note 5 : Enable reverse scan for index
Um, why are you using strings to store the ACC_KEY1? Numeric fields are much faster.
I would suggest that you drop all your indexes that relate to that query. Then run the Database Engine Tuning Advisor (or whatever its called in SQL 2000) to determine what the right indexes are. Unless you know SQL Server intimately, it can generate better indexes than you can by hand.
Jonathan
|||
thank you for you advice , i use the tuuning wizard but it did not improve the performance
- and acc_key1 could contain a letter so it must be a string
|||You use the same indexes, but what does those indexes look like?
What is the volume to be returned? Is the expected output close to a million rows? (all the '5's)
How do you measure the time? Do you look at the server for the time it takes to resolve the query, or do you measure at the 'end-point'? (ie if you select... and wait until a million rows has been drawn on the screen, or similar)
/Kenneth
Thursday, February 16, 2012
A performance question: "SELECT ID as subID FROM myTable AS myTable1"
For some reasons I need to access the same field of the same table twice in a query, and each give out a diferrent value
Like this:
"SELECT myTable.id, myTable1.id as subID FROM myTable INNER JOIN ... INNER JOIN myTable as myTable1 ..."
The question is, when I write it as myTable as myTable1 will it affect the query performance if myTable is a large table? will it create another so big copy of myTable? or I should create a view like "CREATE VIEW myTable1 AS SELECT id FROM myTable" to reduce the side of myTable1?
Thank you.
Saturday, February 11, 2012
a humdinger of a performance problem
Hi all,
Generally i'm helping to answer questions here, but i have strange problem
that I haven't seen before, or at least such a difference in performance of
a query.
Scenario:
I have my laptop (terrible spec) that has WinXP SP2, SQL2000 DEV EDITION +
SP4.
I have a datawarehouse that currently contains tiny amounts of data, like
3000 rows in 1 table.
All 3 tables in use are 'perfectly' indexed and I have a simple view sitting
on top of them.
If i run a select query against the DB it returns me 3000 rows in under a
second... Now.. here's the killer:
I have EXACTLY the same DB installed on the DWH server which boasts 4GB RAM,
4 3.08Ghz Processers and Win2003 and SQL 2000 STANDARD Edition.
The server is again 'perfectly' tuned with the exact same database ready for
rocking and rolling!
Now - the same query runs in 3 minutes!!!!!!!
If a perform execution plans and traces to my hearts content they ALWAYS
return the same output - but simply a difference in the time taken to return
the data.
I haven't come across a difference like this in 5 years of DBA'ing and am
grateful for any pointers.
A stupid question, would Dev version of SQL perform better indexing of views
(even though no indexed views are in play) than Standard edition which
obviously doesnt support Ind.Views!!'
Baffled! and TIA
ImmyImmy
Check out all SQL Server's settings on both machines
Add "noexpand" hint to the indexed view like
select sum(col) from IndexView with (noexpand)
"Immy" <therealasianbabe@.hotmail.com> wrote in message
news:OWPs5alfGHA.2208@.TK2MSFTNGP05.phx.gbl...
> Hi all,
> Generally i'm helping to answer questions here, but i have strange problem
> that I haven't seen before, or at least such a difference in performance
> of a query.
> Scenario:
> I have my laptop (terrible spec) that has WinXP SP2, SQL2000 DEV EDITION +
> SP4.
> I have a datawarehouse that currently contains tiny amounts of data, like
> 3000 rows in 1 table.
> All 3 tables in use are 'perfectly' indexed and I have a simple view
> sitting on top of them.
> If i run a select query against the DB it returns me 3000 rows in under a
> second... Now.. here's the killer:
> I have EXACTLY the same DB installed on the DWH server which boasts 4GB
> RAM, 4 3.08Ghz Processers and Win2003 and SQL 2000 STANDARD Edition.
> The server is again 'perfectly' tuned with the exact same database ready
> for rocking and rolling!
> Now - the same query runs in 3 minutes!!!!!!!
> If a perform execution plans and traces to my hearts content they ALWAYS
> return the same output - but simply a difference in the time taken to
> return the data.
> I haven't come across a difference like this in 5 years of DBA'ing and am
> grateful for any pointers.
> A stupid question, would Dev version of SQL perform better indexing of
> views (even though no indexed views are in play) than Standard edition
> which obviously doesnt support Ind.Views!!'
> Baffled! and TIA
> Immy
>|||Why don't you run a perfmon on the server and keep a check on the buffer
cache, I/O, processesor and memory when the query is run and see which is
being used more? that info will help in address the issue faster.
"Immy" wrote:
> Hi all,
> Generally i'm helping to answer questions here, but i have strange problem
> that I haven't seen before, or at least such a difference in performance o
f
> a query.
> Scenario:
> I have my laptop (terrible spec) that has WinXP SP2, SQL2000 DEV EDITION +
> SP4.
> I have a datawarehouse that currently contains tiny amounts of data, like
> 3000 rows in 1 table.
> All 3 tables in use are 'perfectly' indexed and I have a simple view sitti
ng
> on top of them.
> If i run a select query against the DB it returns me 3000 rows in under a
> second... Now.. here's the killer:
> I have EXACTLY the same DB installed on the DWH server which boasts 4GB RA
M,
> 4 3.08Ghz Processers and Win2003 and SQL 2000 STANDARD Edition.
> The server is again 'perfectly' tuned with the exact same database ready f
or
> rocking and rolling!
> Now - the same query runs in 3 minutes!!!!!!!
> If a perform execution plans and traces to my hearts content they ALWAYS
> return the same output - but simply a difference in the time taken to retu
rn
> the data.
> I haven't come across a difference like this in 5 years of DBA'ing and am
> grateful for any pointers.
> A stupid question, would Dev version of SQL perform better indexing of vie
ws
> (even though no indexed views are in play) than Standard edition which
> obviously doesnt support Ind.Views!!'
> Baffled! and TIA
> Immy
>
>|||Hi Uri,
I mentioned that both my machines are setup exactly the same way. I
installed them both personally.
I don't have indexed views setup as my live server uses Standard Edition of
SQL Server.
Immy
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OsqQcelfGHA.4864@.TK2MSFTNGP05.phx.gbl...
> Immy
> Check out all SQL Server's settings on both machines
> Add "noexpand" hint to the indexed view like
> select sum(col) from IndexView with (noexpand)
>
> "Immy" <therealasianbabe@.hotmail.com> wrote in message
> news:OWPs5alfGHA.2208@.TK2MSFTNGP05.phx.gbl...
>|||> If a perform execution plans and traces to my hearts content they ALWAYS
> return the same output - but simply a difference in the time taken to
> return the data.
I would expect different execution plans to explain such a big difference.
Take a close look at each plan node to ensure each is the same in both
plans.
Is there blocking or other processes running on the DWH server that might
explain the difference?
> A stupid question, would Dev version of SQL perform better indexing of
> views (even though no indexed views are in play) than Standard edition
> which obviously doesnt support Ind.Views!!'
Developer Edition has the same features as Enterprise Edition.
Consequently, DE will automatically consider view indexes. However, you
would see different execution plans if indexed views was the reason for the
difference in performance. DE/EE also has a merry-go-round scan feature
that can improve concurrent scan performance but that shouldn't be an issue
here.
Hope this helps.
Dan Guzman
SQL Server MVP
"Immy" <therealasianbabe@.hotmail.com> wrote in message
news:OWPs5alfGHA.2208@.TK2MSFTNGP05.phx.gbl...
> Hi all,
> Generally i'm helping to answer questions here, but i have strange problem
> that I haven't seen before, or at least such a difference in performance
> of a query.
> Scenario:
> I have my laptop (terrible spec) that has WinXP SP2, SQL2000 DEV EDITION +
> SP4.
> I have a datawarehouse that currently contains tiny amounts of data, like
> 3000 rows in 1 table.
> All 3 tables in use are 'perfectly' indexed and I have a simple view
> sitting on top of them.
> If i run a select query against the DB it returns me 3000 rows in under a
> second... Now.. here's the killer:
> I have EXACTLY the same DB installed on the DWH server which boasts 4GB
> RAM, 4 3.08Ghz Processers and Win2003 and SQL 2000 STANDARD Edition.
> The server is again 'perfectly' tuned with the exact same database ready
> for rocking and rolling!
> Now - the same query runs in 3 minutes!!!!!!!
> If a perform execution plans and traces to my hearts content they ALWAYS
> return the same output - but simply a difference in the time taken to
> return the data.
> I haven't come across a difference like this in 5 years of DBA'ing and am
> grateful for any pointers.
> A stupid question, would Dev version of SQL perform better indexing of
> views (even though no indexed views are in play) than Standard edition
> which obviously doesnt support Ind.Views!!'
> Baffled! and TIA
> Immy
>|||Immy
> A stupid question, would Dev version of SQL perform better indexing of
> views (even though no indexed views are in play) than Standard edition
> which obviously doesnt support Ind.Views!!'
Sorry I was referencing to the above statement somehow
Run DBCC FREEPROCCACHE on both machines and see what is going on
"Immy" <therealasianbabe@.hotmail.com> wrote in message
news:%23c130jlfGHA.3996@.TK2MSFTNGP04.phx.gbl...
> Hi Uri,
> I mentioned that both my machines are setup exactly the same way. I
> installed them both personally.
> I don't have indexed views setup as my live server uses Standard Edition
> of SQL Server.
> Immy
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:OsqQcelfGHA.4864@.TK2MSFTNGP05.phx.gbl...
>|||thanks for the responses guys and i've either already tried most of your
suggestions or tried the rest offered but getting nowehere very fast.
I'm currently on Japanese time zone rolling out a live system so there comes
a time where i need to re-group and take a look after some sleep!
I'm beginning to think my conclusion as well as Dans comments re: auto
indexing is the key here as i've swapped db's around from server to server
and always see the issue on the Standard version of sql.
I'll have to just try alternate methods of providing the data to the users;
however, feel free to post more suggestions that i can try in a few hours
time.
thanks guys
Immy
"Immy" <therealasianbabe@.hotmail.com> wrote in message
news:OWPs5alfGHA.2208@.TK2MSFTNGP05.phx.gbl...
> Hi all,
> Generally i'm helping to answer questions here, but i have strange problem
> that I haven't seen before, or at least such a difference in performance
> of a query.
> Scenario:
> I have my laptop (terrible spec) that has WinXP SP2, SQL2000 DEV EDITION +
> SP4.
> I have a datawarehouse that currently contains tiny amounts of data, like
> 3000 rows in 1 table.
> All 3 tables in use are 'perfectly' indexed and I have a simple view
> sitting on top of them.
> If i run a select query against the DB it returns me 3000 rows in under a
> second... Now.. here's the killer:
> I have EXACTLY the same DB installed on the DWH server which boasts 4GB
> RAM, 4 3.08Ghz Processers and Win2003 and SQL 2000 STANDARD Edition.
> The server is again 'perfectly' tuned with the exact same database ready
> for rocking and rolling!
> Now - the same query runs in 3 minutes!!!!!!!
> If a perform execution plans and traces to my hearts content they ALWAYS
> return the same output - but simply a difference in the time taken to
> return the data.
> I haven't come across a difference like this in 5 years of DBA'ing and am
> grateful for any pointers.
> A stupid question, would Dev version of SQL perform better indexing of
> views (even though no indexed views are in play) than Standard edition
> which obviously doesnt support Ind.Views!!'
> Baffled! and TIA
> Immy
>|||How long has the database been set up on the production server?
Do any other queries take this long to run?
Is it possible that you have a bad processor / memory?
Or, maybe you have a corrupt index.
Lets figure out if one table is causing you the trouble. Take each table in
your query, and create a seperate query, joining the table to itself several
times, on the same columns used for the join in the original query. Run
each of these and see if one of them is slow. I would expect that one of
these would be very slow and the other two would be quite fast. Drop the
indexes on the one that is slow and recreate them.
"Immy" <therealasianbabe@.hotmail.com> wrote in message
news:%23Y8B0ylfGHA.4304@.TK2MSFTNGP05.phx.gbl...
> thanks for the responses guys and i've either already tried most of your
> suggestions or tried the rest offered but getting nowehere very fast.
> I'm currently on Japanese time zone rolling out a live system so there
comes
> a time where i need to re-group and take a look after some sleep!
> I'm beginning to think my conclusion as well as Dans comments re: auto
> indexing is the key here as i've swapped db's around from server to server
> and always see the issue on the Standard version of sql.
> I'll have to just try alternate methods of providing the data to the
users;
> however, feel free to post more suggestions that i can try in a few hours
> time.
> thanks guys
> Immy
> "Immy" <therealasianbabe@.hotmail.com> wrote in message
> news:OWPs5alfGHA.2208@.TK2MSFTNGP05.phx.gbl...
problem
+
like
a
am
>|||Jim
the DB is new as of yesterday as is the installation and the query was
running fine during the first few hours of yesterday.
I have also broken down the queries and joined onto 1 table it's fine.
If i add 1 of any other 2 tables to the query it slows down dramatically and
i had also recreated the indexes a few times throughout the day.
Could be right about bad processor as 1 of the processors always peaks for
the duration of the run.
Once again, thanks for your responses. Good to know we're all singing from
the same hymn sheet and i'm sure i'll get to the end of it soon.
Immy
"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:%23n4XommfGHA.2068@.TK2MSFTNGP02.phx.gbl...
> How long has the database been set up on the production server?
> Do any other queries take this long to run?
> Is it possible that you have a bad processor / memory?
> Or, maybe you have a corrupt index.
> Lets figure out if one table is causing you the trouble. Take each table
> in
> your query, and create a seperate query, joining the table to itself
> several
> times, on the same columns used for the join in the original query. Run
> each of these and see if one of them is slow. I would expect that one of
> these would be very slow and the other two would be quite fast. Drop the
> indexes on the one that is slow and recreate them.
>
> "Immy" <therealasianbabe@.hotmail.com> wrote in message
> news:%23Y8B0ylfGHA.4304@.TK2MSFTNGP05.phx.gbl...
> comes
> users;
> problem
> +
> like
> a
> am
>|||How is the memory and IO (from perfmon) during the run? This really has my
curiosity peaked now. With only 3000 rows, I would expect very fast
response on anything better than a 386. With identical execution plans, I
am even more puzzled.
Just to confirm, all 3 tables have the same number of rows on both
databases?
Could you post the SQL query and the DDL, just to satisfy our curiosity?
"Immy" <therealasianbabe@.hotmail.com> wrote in message
news:uzTPfDnfGHA.2068@.TK2MSFTNGP02.phx.gbl...
> Jim
> the DB is new as of yesterday as is the installation and the query was
> running fine during the first few hours of yesterday.
> I have also broken down the queries and joined onto 1 table it's fine.
> If i add 1 of any other 2 tables to the query it slows down dramatically
and
> i had also recreated the indexes a few times throughout the day.
> Could be right about bad processor as 1 of the processors always peaks for
> the duration of the run.
> Once again, thanks for your responses. Good to know we're all singing from
> the same hymn sheet and i'm sure i'll get to the end of it soon.
> Immy
> "Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
> news:%23n4XommfGHA.2068@.TK2MSFTNGP02.phx.gbl...
table
of
the
your
hours
under
4GB
and
of
edition
>
Generally i'm helping to answer questions here, but i have strange problem
that I haven't seen before, or at least such a difference in performance of
a query.
Scenario:
I have my laptop (terrible spec) that has WinXP SP2, SQL2000 DEV EDITION +
SP4.
I have a datawarehouse that currently contains tiny amounts of data, like
3000 rows in 1 table.
All 3 tables in use are 'perfectly' indexed and I have a simple view sitting
on top of them.
If i run a select query against the DB it returns me 3000 rows in under a
second... Now.. here's the killer:
I have EXACTLY the same DB installed on the DWH server which boasts 4GB RAM,
4 3.08Ghz Processers and Win2003 and SQL 2000 STANDARD Edition.
The server is again 'perfectly' tuned with the exact same database ready for
rocking and rolling!
Now - the same query runs in 3 minutes!!!!!!!
If a perform execution plans and traces to my hearts content they ALWAYS
return the same output - but simply a difference in the time taken to return
the data.
I haven't come across a difference like this in 5 years of DBA'ing and am
grateful for any pointers.
A stupid question, would Dev version of SQL perform better indexing of views
(even though no indexed views are in play) than Standard edition which
obviously doesnt support Ind.Views!!'
Baffled! and TIA
ImmyImmy
Check out all SQL Server's settings on both machines
Add "noexpand" hint to the indexed view like
select sum(col) from IndexView with (noexpand)
"Immy" <therealasianbabe@.hotmail.com> wrote in message
news:OWPs5alfGHA.2208@.TK2MSFTNGP05.phx.gbl...
> Hi all,
> Generally i'm helping to answer questions here, but i have strange problem
> that I haven't seen before, or at least such a difference in performance
> of a query.
> Scenario:
> I have my laptop (terrible spec) that has WinXP SP2, SQL2000 DEV EDITION +
> SP4.
> I have a datawarehouse that currently contains tiny amounts of data, like
> 3000 rows in 1 table.
> All 3 tables in use are 'perfectly' indexed and I have a simple view
> sitting on top of them.
> If i run a select query against the DB it returns me 3000 rows in under a
> second... Now.. here's the killer:
> I have EXACTLY the same DB installed on the DWH server which boasts 4GB
> RAM, 4 3.08Ghz Processers and Win2003 and SQL 2000 STANDARD Edition.
> The server is again 'perfectly' tuned with the exact same database ready
> for rocking and rolling!
> Now - the same query runs in 3 minutes!!!!!!!
> If a perform execution plans and traces to my hearts content they ALWAYS
> return the same output - but simply a difference in the time taken to
> return the data.
> I haven't come across a difference like this in 5 years of DBA'ing and am
> grateful for any pointers.
> A stupid question, would Dev version of SQL perform better indexing of
> views (even though no indexed views are in play) than Standard edition
> which obviously doesnt support Ind.Views!!'
> Baffled! and TIA
> Immy
>|||Why don't you run a perfmon on the server and keep a check on the buffer
cache, I/O, processesor and memory when the query is run and see which is
being used more? that info will help in address the issue faster.
"Immy" wrote:
> Hi all,
> Generally i'm helping to answer questions here, but i have strange problem
> that I haven't seen before, or at least such a difference in performance o
f
> a query.
> Scenario:
> I have my laptop (terrible spec) that has WinXP SP2, SQL2000 DEV EDITION +
> SP4.
> I have a datawarehouse that currently contains tiny amounts of data, like
> 3000 rows in 1 table.
> All 3 tables in use are 'perfectly' indexed and I have a simple view sitti
ng
> on top of them.
> If i run a select query against the DB it returns me 3000 rows in under a
> second... Now.. here's the killer:
> I have EXACTLY the same DB installed on the DWH server which boasts 4GB RA
M,
> 4 3.08Ghz Processers and Win2003 and SQL 2000 STANDARD Edition.
> The server is again 'perfectly' tuned with the exact same database ready f
or
> rocking and rolling!
> Now - the same query runs in 3 minutes!!!!!!!
> If a perform execution plans and traces to my hearts content they ALWAYS
> return the same output - but simply a difference in the time taken to retu
rn
> the data.
> I haven't come across a difference like this in 5 years of DBA'ing and am
> grateful for any pointers.
> A stupid question, would Dev version of SQL perform better indexing of vie
ws
> (even though no indexed views are in play) than Standard edition which
> obviously doesnt support Ind.Views!!'
> Baffled! and TIA
> Immy
>
>|||Hi Uri,
I mentioned that both my machines are setup exactly the same way. I
installed them both personally.
I don't have indexed views setup as my live server uses Standard Edition of
SQL Server.
Immy
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OsqQcelfGHA.4864@.TK2MSFTNGP05.phx.gbl...
> Immy
> Check out all SQL Server's settings on both machines
> Add "noexpand" hint to the indexed view like
> select sum(col) from IndexView with (noexpand)
>
> "Immy" <therealasianbabe@.hotmail.com> wrote in message
> news:OWPs5alfGHA.2208@.TK2MSFTNGP05.phx.gbl...
>|||> If a perform execution plans and traces to my hearts content they ALWAYS
> return the same output - but simply a difference in the time taken to
> return the data.
I would expect different execution plans to explain such a big difference.
Take a close look at each plan node to ensure each is the same in both
plans.
Is there blocking or other processes running on the DWH server that might
explain the difference?
> A stupid question, would Dev version of SQL perform better indexing of
> views (even though no indexed views are in play) than Standard edition
> which obviously doesnt support Ind.Views!!'
Developer Edition has the same features as Enterprise Edition.
Consequently, DE will automatically consider view indexes. However, you
would see different execution plans if indexed views was the reason for the
difference in performance. DE/EE also has a merry-go-round scan feature
that can improve concurrent scan performance but that shouldn't be an issue
here.
Hope this helps.
Dan Guzman
SQL Server MVP
"Immy" <therealasianbabe@.hotmail.com> wrote in message
news:OWPs5alfGHA.2208@.TK2MSFTNGP05.phx.gbl...
> Hi all,
> Generally i'm helping to answer questions here, but i have strange problem
> that I haven't seen before, or at least such a difference in performance
> of a query.
> Scenario:
> I have my laptop (terrible spec) that has WinXP SP2, SQL2000 DEV EDITION +
> SP4.
> I have a datawarehouse that currently contains tiny amounts of data, like
> 3000 rows in 1 table.
> All 3 tables in use are 'perfectly' indexed and I have a simple view
> sitting on top of them.
> If i run a select query against the DB it returns me 3000 rows in under a
> second... Now.. here's the killer:
> I have EXACTLY the same DB installed on the DWH server which boasts 4GB
> RAM, 4 3.08Ghz Processers and Win2003 and SQL 2000 STANDARD Edition.
> The server is again 'perfectly' tuned with the exact same database ready
> for rocking and rolling!
> Now - the same query runs in 3 minutes!!!!!!!
> If a perform execution plans and traces to my hearts content they ALWAYS
> return the same output - but simply a difference in the time taken to
> return the data.
> I haven't come across a difference like this in 5 years of DBA'ing and am
> grateful for any pointers.
> A stupid question, would Dev version of SQL perform better indexing of
> views (even though no indexed views are in play) than Standard edition
> which obviously doesnt support Ind.Views!!'
> Baffled! and TIA
> Immy
>|||Immy
> A stupid question, would Dev version of SQL perform better indexing of
> views (even though no indexed views are in play) than Standard edition
> which obviously doesnt support Ind.Views!!'
Sorry I was referencing to the above statement somehow
Run DBCC FREEPROCCACHE on both machines and see what is going on
"Immy" <therealasianbabe@.hotmail.com> wrote in message
news:%23c130jlfGHA.3996@.TK2MSFTNGP04.phx.gbl...
> Hi Uri,
> I mentioned that both my machines are setup exactly the same way. I
> installed them both personally.
> I don't have indexed views setup as my live server uses Standard Edition
> of SQL Server.
> Immy
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:OsqQcelfGHA.4864@.TK2MSFTNGP05.phx.gbl...
>|||thanks for the responses guys and i've either already tried most of your
suggestions or tried the rest offered but getting nowehere very fast.
I'm currently on Japanese time zone rolling out a live system so there comes
a time where i need to re-group and take a look after some sleep!
I'm beginning to think my conclusion as well as Dans comments re: auto
indexing is the key here as i've swapped db's around from server to server
and always see the issue on the Standard version of sql.
I'll have to just try alternate methods of providing the data to the users;
however, feel free to post more suggestions that i can try in a few hours
time.
thanks guys
Immy
"Immy" <therealasianbabe@.hotmail.com> wrote in message
news:OWPs5alfGHA.2208@.TK2MSFTNGP05.phx.gbl...
> Hi all,
> Generally i'm helping to answer questions here, but i have strange problem
> that I haven't seen before, or at least such a difference in performance
> of a query.
> Scenario:
> I have my laptop (terrible spec) that has WinXP SP2, SQL2000 DEV EDITION +
> SP4.
> I have a datawarehouse that currently contains tiny amounts of data, like
> 3000 rows in 1 table.
> All 3 tables in use are 'perfectly' indexed and I have a simple view
> sitting on top of them.
> If i run a select query against the DB it returns me 3000 rows in under a
> second... Now.. here's the killer:
> I have EXACTLY the same DB installed on the DWH server which boasts 4GB
> RAM, 4 3.08Ghz Processers and Win2003 and SQL 2000 STANDARD Edition.
> The server is again 'perfectly' tuned with the exact same database ready
> for rocking and rolling!
> Now - the same query runs in 3 minutes!!!!!!!
> If a perform execution plans and traces to my hearts content they ALWAYS
> return the same output - but simply a difference in the time taken to
> return the data.
> I haven't come across a difference like this in 5 years of DBA'ing and am
> grateful for any pointers.
> A stupid question, would Dev version of SQL perform better indexing of
> views (even though no indexed views are in play) than Standard edition
> which obviously doesnt support Ind.Views!!'
> Baffled! and TIA
> Immy
>|||How long has the database been set up on the production server?
Do any other queries take this long to run?
Is it possible that you have a bad processor / memory?
Or, maybe you have a corrupt index.
Lets figure out if one table is causing you the trouble. Take each table in
your query, and create a seperate query, joining the table to itself several
times, on the same columns used for the join in the original query. Run
each of these and see if one of them is slow. I would expect that one of
these would be very slow and the other two would be quite fast. Drop the
indexes on the one that is slow and recreate them.
"Immy" <therealasianbabe@.hotmail.com> wrote in message
news:%23Y8B0ylfGHA.4304@.TK2MSFTNGP05.phx.gbl...
> thanks for the responses guys and i've either already tried most of your
> suggestions or tried the rest offered but getting nowehere very fast.
> I'm currently on Japanese time zone rolling out a live system so there
comes
> a time where i need to re-group and take a look after some sleep!
> I'm beginning to think my conclusion as well as Dans comments re: auto
> indexing is the key here as i've swapped db's around from server to server
> and always see the issue on the Standard version of sql.
> I'll have to just try alternate methods of providing the data to the
users;
> however, feel free to post more suggestions that i can try in a few hours
> time.
> thanks guys
> Immy
> "Immy" <therealasianbabe@.hotmail.com> wrote in message
> news:OWPs5alfGHA.2208@.TK2MSFTNGP05.phx.gbl...
problem
+
like
a
am
>|||Jim
the DB is new as of yesterday as is the installation and the query was
running fine during the first few hours of yesterday.
I have also broken down the queries and joined onto 1 table it's fine.
If i add 1 of any other 2 tables to the query it slows down dramatically and
i had also recreated the indexes a few times throughout the day.
Could be right about bad processor as 1 of the processors always peaks for
the duration of the run.
Once again, thanks for your responses. Good to know we're all singing from
the same hymn sheet and i'm sure i'll get to the end of it soon.
Immy
"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:%23n4XommfGHA.2068@.TK2MSFTNGP02.phx.gbl...
> How long has the database been set up on the production server?
> Do any other queries take this long to run?
> Is it possible that you have a bad processor / memory?
> Or, maybe you have a corrupt index.
> Lets figure out if one table is causing you the trouble. Take each table
> in
> your query, and create a seperate query, joining the table to itself
> several
> times, on the same columns used for the join in the original query. Run
> each of these and see if one of them is slow. I would expect that one of
> these would be very slow and the other two would be quite fast. Drop the
> indexes on the one that is slow and recreate them.
>
> "Immy" <therealasianbabe@.hotmail.com> wrote in message
> news:%23Y8B0ylfGHA.4304@.TK2MSFTNGP05.phx.gbl...
> comes
> users;
> problem
> +
> like
> a
> am
>|||How is the memory and IO (from perfmon) during the run? This really has my
curiosity peaked now. With only 3000 rows, I would expect very fast
response on anything better than a 386. With identical execution plans, I
am even more puzzled.
Just to confirm, all 3 tables have the same number of rows on both
databases?
Could you post the SQL query and the DDL, just to satisfy our curiosity?
"Immy" <therealasianbabe@.hotmail.com> wrote in message
news:uzTPfDnfGHA.2068@.TK2MSFTNGP02.phx.gbl...
> Jim
> the DB is new as of yesterday as is the installation and the query was
> running fine during the first few hours of yesterday.
> I have also broken down the queries and joined onto 1 table it's fine.
> If i add 1 of any other 2 tables to the query it slows down dramatically
and
> i had also recreated the indexes a few times throughout the day.
> Could be right about bad processor as 1 of the processors always peaks for
> the duration of the run.
> Once again, thanks for your responses. Good to know we're all singing from
> the same hymn sheet and i'm sure i'll get to the end of it soon.
> Immy
> "Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
> news:%23n4XommfGHA.2068@.TK2MSFTNGP02.phx.gbl...
table
of
the
your
hours
under
4GB
and
of
edition
>
Thursday, February 9, 2012
A fix for "SelectMethod= cursor"?
I haven't seen this mentioned anywhere, is this covered under the
"Update Performance" and/or "Updateable recordsets" headings? Having
to include that in the connection string greatly reduced our
performance in previous attempts to use SQL Server from our Java
application. If so I will start down the path of trying to convince
the powers that be of the need to switch from Oracle to SQL Server.
The main reason I got shot down before was performance of the free
drivers, and cost of the not-free drivers with good performance.
Thanks,
Ed
EdGmail wrote:
> I haven't seen this mentioned anywhere, is this covered under the
> "Update Performance" and/or "Updateable recordsets" headings? Having
> to include that in the connection string greatly reduced our
> performance in previous attempts to use SQL Server from our Java
> application. If so I will start down the path of trying to convince
> the powers that be of the need to switch from Oracle to SQL Server.
> The main reason I got shot down before was performance of the free
> drivers, and cost of the not-free drivers with good performance.
> Thanks,
> Ed
>
There is no way around the selectMethod=cursor setting if you want
transactional behavior, for example. Also, even with selectMethod=direct
the old MS driver will cache pretty much everything to memory.
Your choices are the new MS driver for SQL Server 2005 (a copy of the
JNetDirect driver, which is currently in beta and still works --
although undocumented -- with older versions of SQL Server) or jTDS (you
would probably be surprised that the performance and stability of jTDS
is at least comparable to that of the commercial drivers).
Disclaimer: I am a jTDS developer.
Alin,
The jTDS Project.
|||I remember now why the "SelectMethod=Cursor" was causing a problem. It
wasn't so much performance as it was causing exceptions when we tried
to do our initial system load with Ant and Torque. I never researched
it thoroughly, but apparently Torque (or ant) spawns threads which
attempt to clone the connection, which the original MS driver choked
on. We tried the JNetDirect driver and that worked OK w/o
"SelectMethod", but we (ok, managment) just didn't want to cough up the
dough for it. Is that particular problem addressed by jTDS or the new
MS driver?
Thanks,
Ed
|||EdGmail wrote:
> Is that particular problem addressed by jTDS or the new MS driver?
Actually that's only a problem of the DataDirect driver and its
derivatives, including the old MS driver. It's not a limitation of SQL
Server, so the other drivers don't have this issue.
Alin,
The jTDS Project.
"Update Performance" and/or "Updateable recordsets" headings? Having
to include that in the connection string greatly reduced our
performance in previous attempts to use SQL Server from our Java
application. If so I will start down the path of trying to convince
the powers that be of the need to switch from Oracle to SQL Server.
The main reason I got shot down before was performance of the free
drivers, and cost of the not-free drivers with good performance.
Thanks,
Ed
EdGmail wrote:
> I haven't seen this mentioned anywhere, is this covered under the
> "Update Performance" and/or "Updateable recordsets" headings? Having
> to include that in the connection string greatly reduced our
> performance in previous attempts to use SQL Server from our Java
> application. If so I will start down the path of trying to convince
> the powers that be of the need to switch from Oracle to SQL Server.
> The main reason I got shot down before was performance of the free
> drivers, and cost of the not-free drivers with good performance.
> Thanks,
> Ed
>
There is no way around the selectMethod=cursor setting if you want
transactional behavior, for example. Also, even with selectMethod=direct
the old MS driver will cache pretty much everything to memory.
Your choices are the new MS driver for SQL Server 2005 (a copy of the
JNetDirect driver, which is currently in beta and still works --
although undocumented -- with older versions of SQL Server) or jTDS (you
would probably be surprised that the performance and stability of jTDS
is at least comparable to that of the commercial drivers).
Disclaimer: I am a jTDS developer.
Alin,
The jTDS Project.
|||I remember now why the "SelectMethod=Cursor" was causing a problem. It
wasn't so much performance as it was causing exceptions when we tried
to do our initial system load with Ant and Torque. I never researched
it thoroughly, but apparently Torque (or ant) spawns threads which
attempt to clone the connection, which the original MS driver choked
on. We tried the JNetDirect driver and that worked OK w/o
"SelectMethod", but we (ok, managment) just didn't want to cough up the
dough for it. Is that particular problem addressed by jTDS or the new
MS driver?
Thanks,
Ed
|||EdGmail wrote:
> Is that particular problem addressed by jTDS or the new MS driver?
Actually that's only a problem of the DataDirect driver and its
derivatives, including the old MS driver. It's not a limitation of SQL
Server, so the other drivers don't have this issue.
Alin,
The jTDS Project.
Subscribe to:
Posts (Atom)