I just became in charge of a server running SQL Server 2000 Enterprise on a
Dell server with 8 Xeon CPUs running 3 Ghz and 32 GB RAM. It is running on
Windows 2003 Enterprise. Memory utilization is fairly steady with 17.8 GB RAM
of the 32 GB RAM in use. The database on the server is 120GB. Approximately
60GB is actual data, 49GB of indexes and the rest is empty space. For
reference, this database is for our ClarifyCRM application.
I am starting to do performance monitoring and I am seeing a high amount of
system paging - 1,000-2000 or more pages/sec.
Even though RAM utilization is low with plenty to spare, I see that the
paging file on the server is only 2GB in size (40.8 MB of it is actually in
use). Other than increasing the size of the paging file (which I will be
doing soon) while is extremely small, are there any other recommendations to
decrease the amount of system paging that is occurring?What are the memory settings set to, especially the MAX Memory one? It
sounds odd that only 17.8 out of 32GB is being used. I assume this is 32 bit
so with AWE the memory should not be dynamic and I would expect it to be
much higher. What other apps are running on this server?
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"KnightFall1" <KnightFall1@.discussions.microsoft.com> wrote in message
news:0B03B003-FB37-4586-A379-0F14DF5A8586@.microsoft.com...
>I just became in charge of a server running SQL Server 2000 Enterprise on a
> Dell server with 8 Xeon CPUs running 3 Ghz and 32 GB RAM. It is running on
> Windows 2003 Enterprise. Memory utilization is fairly steady with 17.8 GB
> RAM
> of the 32 GB RAM in use. The database on the server is 120GB.
> Approximately
> 60GB is actual data, 49GB of indexes and the rest is empty space. For
> reference, this database is for our ClarifyCRM application.
> I am starting to do performance monitoring and I am seeing a high amount
> of
> system paging - 1,000-2000 or more pages/sec.
> Even though RAM utilization is low with plenty to spare, I see that the
> paging file on the server is only 2GB in size (40.8 MB of it is actually
> in
> use). Other than increasing the size of the paging file (which I will be
> doing soon) while is extremely small, are there any other recommendations
> to
> decrease the amount of system paging that is occurring?|||I don't recall the hotfix, but - SP4 rtm they broke AWE, you only see half
of the actual memory you can get at.
Tony.
--
Tony Rogerson, SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson
[Ramblings from the field from a SQL consultant]
http://sqlserverfaq.com
[UK SQL User Community]
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OvGjlPQDIHA.5328@.TK2MSFTNGP05.phx.gbl...
> What are the memory settings set to, especially the MAX Memory one? It
> sounds odd that only 17.8 out of 32GB is being used. I assume this is 32
> bit so with AWE the memory should not be dynamic and I would expect it to
> be much higher. What other apps are running on this server?
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "KnightFall1" <KnightFall1@.discussions.microsoft.com> wrote in message
> news:0B03B003-FB37-4586-A379-0F14DF5A8586@.microsoft.com...
>>I just became in charge of a server running SQL Server 2000 Enterprise on
>>a
>> Dell server with 8 Xeon CPUs running 3 Ghz and 32 GB RAM. It is running
>> on
>> Windows 2003 Enterprise. Memory utilization is fairly steady with 17.8 GB
>> RAM
>> of the 32 GB RAM in use. The database on the server is 120GB.
>> Approximately
>> 60GB is actual data, 49GB of indexes and the rest is empty space. For
>> reference, this database is for our ClarifyCRM application.
>> I am starting to do performance monitoring and I am seeing a high amount
>> of
>> system paging - 1,000-2000 or more pages/sec.
>> Even though RAM utilization is low with plenty to spare, I see that the
>> paging file on the server is only 2GB in size (40.8 MB of it is actually
>> in
>> use). Other than increasing the size of the paging file (which I will be
>> doing soon) while is extremely small, are there any other recommendations
>> to
>> decrease the amount of system paging that is occurring?
>|||You may not actually have a problem.
Microsoft has an excellent (and short) article on paging and monitorint it,
I suggect you spend a few minutes reading it.
http://support.microsoft.com/kb/555223
Good luck,
Jay
"KnightFall1" <KnightFall1@.discussions.microsoft.com> wrote in message
news:0B03B003-FB37-4586-A379-0F14DF5A8586@.microsoft.com...
>I just became in charge of a server running SQL Server 2000 Enterprise on a
> Dell server with 8 Xeon CPUs running 3 Ghz and 32 GB RAM. It is running on
> Windows 2003 Enterprise. Memory utilization is fairly steady with 17.8 GB
> RAM
> of the 32 GB RAM in use. The database on the server is 120GB.
> Approximately
> 60GB is actual data, 49GB of indexes and the rest is empty space. For
> reference, this database is for our ClarifyCRM application.
> I am starting to do performance monitoring and I am seeing a high amount
> of
> system paging - 1,000-2000 or more pages/sec.
> Even though RAM utilization is low with plenty to spare, I see that the
> paging file on the server is only 2GB in size (40.8 MB of it is actually
> in
> use). Other than increasing the size of the paging file (which I will be
> doing soon) while is extremely small, are there any other recommendations
> to
> decrease the amount of system paging that is occurring?|||Yes Tony probably hit the nail on the head here. You are using SQL2000 so
you most likely have SP4 without the Hotfix and it will only use approx half
the memory.
http://support.microsoft.com/kb/899761
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Tony Rogerson" <tonyrogerson@.torver.net> wrote in message
news:64222C7B-A187-4BE8-AB4D-AA309F75377A@.microsoft.com...
>I don't recall the hotfix, but - SP4 rtm they broke AWE, you only see half
>of the actual memory you can get at.
> Tony.
> --
> Tony Rogerson, SQL Server MVP
> http://sqlblogcasts.com/blogs/tonyrogerson
> [Ramblings from the field from a SQL consultant]
> http://sqlserverfaq.com
> [UK SQL User Community]
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OvGjlPQDIHA.5328@.TK2MSFTNGP05.phx.gbl...
>> What are the memory settings set to, especially the MAX Memory one? It
>> sounds odd that only 17.8 out of 32GB is being used. I assume this is 32
>> bit so with AWE the memory should not be dynamic and I would expect it to
>> be much higher. What other apps are running on this server?
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "KnightFall1" <KnightFall1@.discussions.microsoft.com> wrote in message
>> news:0B03B003-FB37-4586-A379-0F14DF5A8586@.microsoft.com...
>>I just became in charge of a server running SQL Server 2000 Enterprise on
>>a
>> Dell server with 8 Xeon CPUs running 3 Ghz and 32 GB RAM. It is running
>> on
>> Windows 2003 Enterprise. Memory utilization is fairly steady with 17.8
>> GB RAM
>> of the 32 GB RAM in use. The database on the server is 120GB.
>> Approximately
>> 60GB is actual data, 49GB of indexes and the rest is empty space. For
>> reference, this database is for our ClarifyCRM application.
>> I am starting to do performance monitoring and I am seeing a high amount
>> of
>> system paging - 1,000-2000 or more pages/sec.
>> Even though RAM utilization is low with plenty to spare, I see that the
>> paging file on the server is only 2GB in size (40.8 MB of it is actually
>> in
>> use). Other than increasing the size of the paging file (which I will be
>> doing soon) while is extremely small, are there any other
>> recommendations to
>> decrease the amount of system paging that is occurring?
>|||Thats the one.
Thanks Andrew - I was being lazy, well, time constrained :(
--
Tony Rogerson, SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson
[Ramblings from the field from a SQL consultant]
http://sqlserverfaq.com
[UK SQL User Community]
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OhLBUbbDIHA.5976@.TK2MSFTNGP02.phx.gbl...
> Yes Tony probably hit the nail on the head here. You are using SQL2000 so
> you most likely have SP4 without the Hotfix and it will only use approx
> half the memory.
> http://support.microsoft.com/kb/899761
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Tony Rogerson" <tonyrogerson@.torver.net> wrote in message
> news:64222C7B-A187-4BE8-AB4D-AA309F75377A@.microsoft.com...
>>I don't recall the hotfix, but - SP4 rtm they broke AWE, you only see half
>>of the actual memory you can get at.
>> Tony.
>> --
>> Tony Rogerson, SQL Server MVP
>> http://sqlblogcasts.com/blogs/tonyrogerson
>> [Ramblings from the field from a SQL consultant]
>> http://sqlserverfaq.com
>> [UK SQL User Community]
>>
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:OvGjlPQDIHA.5328@.TK2MSFTNGP05.phx.gbl...
>> What are the memory settings set to, especially the MAX Memory one? It
>> sounds odd that only 17.8 out of 32GB is being used. I assume this is 32
>> bit so with AWE the memory should not be dynamic and I would expect it
>> to be much higher. What other apps are running on this server?
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "KnightFall1" <KnightFall1@.discussions.microsoft.com> wrote in message
>> news:0B03B003-FB37-4586-A379-0F14DF5A8586@.microsoft.com...
>>I just became in charge of a server running SQL Server 2000 Enterprise
>>on a
>> Dell server with 8 Xeon CPUs running 3 Ghz and 32 GB RAM. It is running
>> on
>> Windows 2003 Enterprise. Memory utilization is fairly steady with 17.8
>> GB RAM
>> of the 32 GB RAM in use. The database on the server is 120GB.
>> Approximately
>> 60GB is actual data, 49GB of indexes and the rest is empty space. For
>> reference, this database is for our ClarifyCRM application.
>> I am starting to do performance monitoring and I am seeing a high
>> amount of
>> system paging - 1,000-2000 or more pages/sec.
>> Even though RAM utilization is low with plenty to spare, I see that the
>> paging file on the server is only 2GB in size (40.8 MB of it is
>> actually in
>> use). Other than increasing the size of the paging file (which I will
>> be
>> doing soon) while is extremely small, are there any other
>> recommendations to
>> decrease the amount of system paging that is occurring?
>>
>
Showing posts with label paging. Show all posts
Showing posts with label paging. Show all posts
Wednesday, March 28, 2012
Friday, March 9, 2012
performance on query analyzer and .NET
Dear Sir,
I used dynamic SQL on stored procedure to do the paging in my site.
I often hit a connection with that dynamic SQl when I call it from .NET code.
However if it run it under query analyzer, everything works fine.
Who can explain this to me and what should I do in this case?
regards,
Guoqi Zheng
http://www.ureader.com
Hi
How do you call it? Have you created a stored procedure which accepts
parameters?
"guoqi zheng" <no@.sorry.com> wrote in message
news:4cc7b9b7665549a4b34db8869b21a9f0@.ureader.com. ..
> Dear Sir,
> I used dynamic SQL on stored procedure to do the paging in my site.
> I often hit a connection with that dynamic SQl when I call it from .NET
> code.
> However if it run it under query analyzer, everything works fine.
> Who can explain this to me and what should I do in this case?
> regards,
> Guoqi Zheng
> http://www.ureader.com
|||yes... I have a stored procedure which accept pagenr, pagesize as
parameter..
The correct sentence is that I hit a connection time out problem when I call
it from .NET code..
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:#7ubM76NGHA.1132@.TK2MSFTNGP10.phx.gbl...
> Hi
> How do you call it? Have you created a stored procedure which accepts
> parameters?
>
> "guoqi zheng" <no@.sorry.com> wrote in message
> news:4cc7b9b7665549a4b34db8869b21a9f0@.ureader.com. ..
>
|||It is probably because you've got a 'bad' execution pln. Try recompile the
SP and see what is going on
"Guoqi Zheng" <no@.sorry.com> wrote in message
news:dtho1p$i4i$1@.newspe.com...
> yes... I have a stored procedure which accept pagenr, pagesize as
> parameter..
> The correct sentence is that I hit a connection time out problem when I
> call
> it from .NET code..
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:#7ubM76NGHA.1132@.TK2MSFTNGP10.phx.gbl...
>
|||I actually do not know how can I compile my SQL code, not to mention
recompile.
Do you know how? I thought when I create a SP, it is compile automatically?
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:#W72oL7NGHA.3460@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> It is probably because you've got a 'bad' execution pln. Try recompile the
> SP and see what is going on
>
>
> "Guoqi Zheng" <no@.sorry.com> wrote in message
> news:dtho1p$i4i$1@.newspe.com...
..NET
>
|||There is sp_recompile stored procedure, please refer to the BOL
"Guoqi Zheng" <no@.sorry.com> wrote in message
news:dths3a$jq6$1@.newspe.com...
>I actually do not know how can I compile my SQL code, not to mention
> recompile.
> Do you know how? I thought when I create a SP, it is compile
> automatically?
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:#W72oL7NGHA.3460@.TK2MSFTNGP15.phx.gbl...
> .NET
>
I used dynamic SQL on stored procedure to do the paging in my site.
I often hit a connection with that dynamic SQl when I call it from .NET code.
However if it run it under query analyzer, everything works fine.
Who can explain this to me and what should I do in this case?
regards,
Guoqi Zheng
http://www.ureader.com
Hi
How do you call it? Have you created a stored procedure which accepts
parameters?
"guoqi zheng" <no@.sorry.com> wrote in message
news:4cc7b9b7665549a4b34db8869b21a9f0@.ureader.com. ..
> Dear Sir,
> I used dynamic SQL on stored procedure to do the paging in my site.
> I often hit a connection with that dynamic SQl when I call it from .NET
> code.
> However if it run it under query analyzer, everything works fine.
> Who can explain this to me and what should I do in this case?
> regards,
> Guoqi Zheng
> http://www.ureader.com
|||yes... I have a stored procedure which accept pagenr, pagesize as
parameter..
The correct sentence is that I hit a connection time out problem when I call
it from .NET code..
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:#7ubM76NGHA.1132@.TK2MSFTNGP10.phx.gbl...
> Hi
> How do you call it? Have you created a stored procedure which accepts
> parameters?
>
> "guoqi zheng" <no@.sorry.com> wrote in message
> news:4cc7b9b7665549a4b34db8869b21a9f0@.ureader.com. ..
>
|||It is probably because you've got a 'bad' execution pln. Try recompile the
SP and see what is going on
"Guoqi Zheng" <no@.sorry.com> wrote in message
news:dtho1p$i4i$1@.newspe.com...
> yes... I have a stored procedure which accept pagenr, pagesize as
> parameter..
> The correct sentence is that I hit a connection time out problem when I
> call
> it from .NET code..
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:#7ubM76NGHA.1132@.TK2MSFTNGP10.phx.gbl...
>
|||I actually do not know how can I compile my SQL code, not to mention
recompile.
Do you know how? I thought when I create a SP, it is compile automatically?
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:#W72oL7NGHA.3460@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> It is probably because you've got a 'bad' execution pln. Try recompile the
> SP and see what is going on
>
>
> "Guoqi Zheng" <no@.sorry.com> wrote in message
> news:dtho1p$i4i$1@.newspe.com...
..NET
>
|||There is sp_recompile stored procedure, please refer to the BOL
"Guoqi Zheng" <no@.sorry.com> wrote in message
news:dths3a$jq6$1@.newspe.com...
>I actually do not know how can I compile my SQL code, not to mention
> recompile.
> Do you know how? I thought when I create a SP, it is compile
> automatically?
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:#W72oL7NGHA.3460@.TK2MSFTNGP15.phx.gbl...
> .NET
>
performance on query analyzer and .NET
Dear Sir,
I used dynamic SQL on stored procedure to do the paging in my site.
I often hit a connection with that dynamic SQl when I call it from .NET code.
However if it run it under query analyzer, everything works fine.
Who can explain this to me and what should I do in this case?
regards,
Guoqi Zheng
http://www.ureader.comHi
How do you call it? Have you created a stored procedure which accepts
parameters?
"guoqi zheng" <no@.sorry.com> wrote in message
news:4cc7b9b7665549a4b34db8869b21a9f0@.ureader.com...
> Dear Sir,
> I used dynamic SQL on stored procedure to do the paging in my site.
> I often hit a connection with that dynamic SQl when I call it from .NET
> code.
> However if it run it under query analyzer, everything works fine.
> Who can explain this to me and what should I do in this case?
> regards,
> Guoqi Zheng
> http://www.ureader.com|||yes... I have a stored procedure which accept pagenr, pagesize as
parameter..
The correct sentence is that I hit a connection time out problem when I call
it from .NET code..
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:#7ubM76NGHA.1132@.TK2MSFTNGP10.phx.gbl...
> Hi
> How do you call it? Have you created a stored procedure which accepts
> parameters?
>
> "guoqi zheng" <no@.sorry.com> wrote in message
> news:4cc7b9b7665549a4b34db8869b21a9f0@.ureader.com...
> > Dear Sir,
> >
> > I used dynamic SQL on stored procedure to do the paging in my site.
> >
> > I often hit a connection with that dynamic SQl when I call it from .NET
> > code.
> > However if it run it under query analyzer, everything works fine.
> >
> > Who can explain this to me and what should I do in this case?
> >
> > regards,
> >
> > Guoqi Zheng
> > http://www.ureader.com
>|||It is probably because you've got a 'bad' execution pln. Try recompile the
SP and see what is going on
"Guoqi Zheng" <no@.sorry.com> wrote in message
news:dtho1p$i4i$1@.newspe.com...
> yes... I have a stored procedure which accept pagenr, pagesize as
> parameter..
> The correct sentence is that I hit a connection time out problem when I
> call
> it from .NET code..
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:#7ubM76NGHA.1132@.TK2MSFTNGP10.phx.gbl...
>> Hi
>> How do you call it? Have you created a stored procedure which accepts
>> parameters?
>>
>> "guoqi zheng" <no@.sorry.com> wrote in message
>> news:4cc7b9b7665549a4b34db8869b21a9f0@.ureader.com...
>> > Dear Sir,
>> >
>> > I used dynamic SQL on stored procedure to do the paging in my site.
>> >
>> > I often hit a connection with that dynamic SQl when I call it from .NET
>> > code.
>> > However if it run it under query analyzer, everything works fine.
>> >
>> > Who can explain this to me and what should I do in this case?
>> >
>> > regards,
>> >
>> > Guoqi Zheng
>> > http://www.ureader.com
>>
>|||I actually do not know how can I compile my SQL code, not to mention
recompile.
Do you know how? I thought when I create a SP, it is compile automatically?
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:#W72oL7NGHA.3460@.TK2MSFTNGP15.phx.gbl...
> It is probably because you've got a 'bad' execution pln. Try recompile the
> SP and see what is going on
>
>
> "Guoqi Zheng" <no@.sorry.com> wrote in message
> news:dtho1p$i4i$1@.newspe.com...
> > yes... I have a stored procedure which accept pagenr, pagesize as
> > parameter..
> >
> > The correct sentence is that I hit a connection time out problem when I
> > call
> > it from .NET code..
> >
> >
> >
> > "Uri Dimant" <urid@.iscar.co.il> wrote in message
> > news:#7ubM76NGHA.1132@.TK2MSFTNGP10.phx.gbl...
> >> Hi
> >> How do you call it? Have you created a stored procedure which accepts
> >> parameters?
> >>
> >>
> >> "guoqi zheng" <no@.sorry.com> wrote in message
> >> news:4cc7b9b7665549a4b34db8869b21a9f0@.ureader.com...
> >> > Dear Sir,
> >> >
> >> > I used dynamic SQL on stored procedure to do the paging in my site.
> >> >
> >> > I often hit a connection with that dynamic SQl when I call it from
.NET
> >> > code.
> >> > However if it run it under query analyzer, everything works fine.
> >> >
> >> > Who can explain this to me and what should I do in this case?
> >> >
> >> > regards,
> >> >
> >> > Guoqi Zheng
> >> > http://www.ureader.com
> >>
> >>
> >
> >
>|||There is sp_recompile stored procedure, please refer to the BOL
"Guoqi Zheng" <no@.sorry.com> wrote in message
news:dths3a$jq6$1@.newspe.com...
>I actually do not know how can I compile my SQL code, not to mention
> recompile.
> Do you know how? I thought when I create a SP, it is compile
> automatically?
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:#W72oL7NGHA.3460@.TK2MSFTNGP15.phx.gbl...
>> It is probably because you've got a 'bad' execution pln. Try recompile
>> the
>> SP and see what is going on
>>
>>
>> "Guoqi Zheng" <no@.sorry.com> wrote in message
>> news:dtho1p$i4i$1@.newspe.com...
>> > yes... I have a stored procedure which accept pagenr, pagesize as
>> > parameter..
>> >
>> > The correct sentence is that I hit a connection time out problem when I
>> > call
>> > it from .NET code..
>> >
>> >
>> >
>> > "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> > news:#7ubM76NGHA.1132@.TK2MSFTNGP10.phx.gbl...
>> >> Hi
>> >> How do you call it? Have you created a stored procedure which accepts
>> >> parameters?
>> >>
>> >>
>> >> "guoqi zheng" <no@.sorry.com> wrote in message
>> >> news:4cc7b9b7665549a4b34db8869b21a9f0@.ureader.com...
>> >> > Dear Sir,
>> >> >
>> >> > I used dynamic SQL on stored procedure to do the paging in my site.
>> >> >
>> >> > I often hit a connection with that dynamic SQl when I call it from
> .NET
>> >> > code.
>> >> > However if it run it under query analyzer, everything works fine.
>> >> >
>> >> > Who can explain this to me and what should I do in this case?
>> >> >
>> >> > regards,
>> >> >
>> >> > Guoqi Zheng
>> >> > http://www.ureader.com
>> >>
>> >>
>> >
>> >
>>
>
I used dynamic SQL on stored procedure to do the paging in my site.
I often hit a connection with that dynamic SQl when I call it from .NET code.
However if it run it under query analyzer, everything works fine.
Who can explain this to me and what should I do in this case?
regards,
Guoqi Zheng
http://www.ureader.comHi
How do you call it? Have you created a stored procedure which accepts
parameters?
"guoqi zheng" <no@.sorry.com> wrote in message
news:4cc7b9b7665549a4b34db8869b21a9f0@.ureader.com...
> Dear Sir,
> I used dynamic SQL on stored procedure to do the paging in my site.
> I often hit a connection with that dynamic SQl when I call it from .NET
> code.
> However if it run it under query analyzer, everything works fine.
> Who can explain this to me and what should I do in this case?
> regards,
> Guoqi Zheng
> http://www.ureader.com|||yes... I have a stored procedure which accept pagenr, pagesize as
parameter..
The correct sentence is that I hit a connection time out problem when I call
it from .NET code..
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:#7ubM76NGHA.1132@.TK2MSFTNGP10.phx.gbl...
> Hi
> How do you call it? Have you created a stored procedure which accepts
> parameters?
>
> "guoqi zheng" <no@.sorry.com> wrote in message
> news:4cc7b9b7665549a4b34db8869b21a9f0@.ureader.com...
> > Dear Sir,
> >
> > I used dynamic SQL on stored procedure to do the paging in my site.
> >
> > I often hit a connection with that dynamic SQl when I call it from .NET
> > code.
> > However if it run it under query analyzer, everything works fine.
> >
> > Who can explain this to me and what should I do in this case?
> >
> > regards,
> >
> > Guoqi Zheng
> > http://www.ureader.com
>|||It is probably because you've got a 'bad' execution pln. Try recompile the
SP and see what is going on
"Guoqi Zheng" <no@.sorry.com> wrote in message
news:dtho1p$i4i$1@.newspe.com...
> yes... I have a stored procedure which accept pagenr, pagesize as
> parameter..
> The correct sentence is that I hit a connection time out problem when I
> call
> it from .NET code..
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:#7ubM76NGHA.1132@.TK2MSFTNGP10.phx.gbl...
>> Hi
>> How do you call it? Have you created a stored procedure which accepts
>> parameters?
>>
>> "guoqi zheng" <no@.sorry.com> wrote in message
>> news:4cc7b9b7665549a4b34db8869b21a9f0@.ureader.com...
>> > Dear Sir,
>> >
>> > I used dynamic SQL on stored procedure to do the paging in my site.
>> >
>> > I often hit a connection with that dynamic SQl when I call it from .NET
>> > code.
>> > However if it run it under query analyzer, everything works fine.
>> >
>> > Who can explain this to me and what should I do in this case?
>> >
>> > regards,
>> >
>> > Guoqi Zheng
>> > http://www.ureader.com
>>
>|||I actually do not know how can I compile my SQL code, not to mention
recompile.
Do you know how? I thought when I create a SP, it is compile automatically?
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:#W72oL7NGHA.3460@.TK2MSFTNGP15.phx.gbl...
> It is probably because you've got a 'bad' execution pln. Try recompile the
> SP and see what is going on
>
>
> "Guoqi Zheng" <no@.sorry.com> wrote in message
> news:dtho1p$i4i$1@.newspe.com...
> > yes... I have a stored procedure which accept pagenr, pagesize as
> > parameter..
> >
> > The correct sentence is that I hit a connection time out problem when I
> > call
> > it from .NET code..
> >
> >
> >
> > "Uri Dimant" <urid@.iscar.co.il> wrote in message
> > news:#7ubM76NGHA.1132@.TK2MSFTNGP10.phx.gbl...
> >> Hi
> >> How do you call it? Have you created a stored procedure which accepts
> >> parameters?
> >>
> >>
> >> "guoqi zheng" <no@.sorry.com> wrote in message
> >> news:4cc7b9b7665549a4b34db8869b21a9f0@.ureader.com...
> >> > Dear Sir,
> >> >
> >> > I used dynamic SQL on stored procedure to do the paging in my site.
> >> >
> >> > I often hit a connection with that dynamic SQl when I call it from
.NET
> >> > code.
> >> > However if it run it under query analyzer, everything works fine.
> >> >
> >> > Who can explain this to me and what should I do in this case?
> >> >
> >> > regards,
> >> >
> >> > Guoqi Zheng
> >> > http://www.ureader.com
> >>
> >>
> >
> >
>|||There is sp_recompile stored procedure, please refer to the BOL
"Guoqi Zheng" <no@.sorry.com> wrote in message
news:dths3a$jq6$1@.newspe.com...
>I actually do not know how can I compile my SQL code, not to mention
> recompile.
> Do you know how? I thought when I create a SP, it is compile
> automatically?
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:#W72oL7NGHA.3460@.TK2MSFTNGP15.phx.gbl...
>> It is probably because you've got a 'bad' execution pln. Try recompile
>> the
>> SP and see what is going on
>>
>>
>> "Guoqi Zheng" <no@.sorry.com> wrote in message
>> news:dtho1p$i4i$1@.newspe.com...
>> > yes... I have a stored procedure which accept pagenr, pagesize as
>> > parameter..
>> >
>> > The correct sentence is that I hit a connection time out problem when I
>> > call
>> > it from .NET code..
>> >
>> >
>> >
>> > "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> > news:#7ubM76NGHA.1132@.TK2MSFTNGP10.phx.gbl...
>> >> Hi
>> >> How do you call it? Have you created a stored procedure which accepts
>> >> parameters?
>> >>
>> >>
>> >> "guoqi zheng" <no@.sorry.com> wrote in message
>> >> news:4cc7b9b7665549a4b34db8869b21a9f0@.ureader.com...
>> >> > Dear Sir,
>> >> >
>> >> > I used dynamic SQL on stored procedure to do the paging in my site.
>> >> >
>> >> > I often hit a connection with that dynamic SQl when I call it from
> .NET
>> >> > code.
>> >> > However if it run it under query analyzer, everything works fine.
>> >> >
>> >> > Who can explain this to me and what should I do in this case?
>> >> >
>> >> > regards,
>> >> >
>> >> > Guoqi Zheng
>> >> > http://www.ureader.com
>> >>
>> >>
>> >
>> >
>>
>
performance on query analyzer and .NET
Dear Sir,
I used dynamic SQL on stored procedure to do the paging in my site.
I often hit a connection with that dynamic SQl when I call it from .NET code
.
However if it run it under query analyzer, everything works fine.
Who can explain this to me and what should I do in this case?
regards,
Guoqi Zheng
http://www.ureader.comHi
How do you call it? Have you created a stored procedure which accepts
parameters?
"guoqi zheng" <no@.sorry.com> wrote in message
news:4cc7b9b7665549a4b34db8869b21a9f0@.ur
eader.com...
> Dear Sir,
> I used dynamic SQL on stored procedure to do the paging in my site.
> I often hit a connection with that dynamic SQl when I call it from .NET
> code.
> However if it run it under query analyzer, everything works fine.
> Who can explain this to me and what should I do in this case?
> regards,
> Guoqi Zheng
> http://www.ureader.com|||yes... I have a stored procedure which accept pagenr, pagesize as
parameter..
The correct sentence is that I hit a connection time out problem when I call
it from .NET code..
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:#7ubM76NGHA.1132@.TK2MSFTNGP10.phx.gbl...
> Hi
> How do you call it? Have you created a stored procedure which accepts
> parameters?
>
> "guoqi zheng" <no@.sorry.com> wrote in message
> news:4cc7b9b7665549a4b34db8869b21a9f0@.ur
eader.com...
>|||It is probably because you've got a 'bad' execution pln. Try recompile the
SP and see what is going on
"Guoqi Zheng" <no@.sorry.com> wrote in message
news:dtho1p$i4i$1@.newspe.com...
> yes... I have a stored procedure which accept pagenr, pagesize as
> parameter..
> The correct sentence is that I hit a connection time out problem when I
> call
> it from .NET code..
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:#7ubM76NGHA.1132@.TK2MSFTNGP10.phx.gbl...
>|||I actually do not know how can I compile my SQL code, not to mention
recompile.
Do you know how? I thought when I create a SP, it is compile automatically?
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:#W72oL7NGHA.3460@.TK2MSFTNGP15.phx.gbl...
> It is probably because you've got a 'bad' execution pln. Try recompile the
> SP and see what is going on
>
>
> "Guoqi Zheng" <no@.sorry.com> wrote in message
> news:dtho1p$i4i$1@.newspe.com...
.NET[vbcol=seagreen]
>|||There is sp_recompile stored procedure, please refer to the BOL
"Guoqi Zheng" <no@.sorry.com> wrote in message
news:dths3a$jq6$1@.newspe.com...
>I actually do not know how can I compile my SQL code, not to mention
> recompile.
> Do you know how? I thought when I create a SP, it is compile
> automatically?
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:#W72oL7NGHA.3460@.TK2MSFTNGP15.phx.gbl...
> .NET
>
I used dynamic SQL on stored procedure to do the paging in my site.
I often hit a connection with that dynamic SQl when I call it from .NET code
.
However if it run it under query analyzer, everything works fine.
Who can explain this to me and what should I do in this case?
regards,
Guoqi Zheng
http://www.ureader.comHi
How do you call it? Have you created a stored procedure which accepts
parameters?
"guoqi zheng" <no@.sorry.com> wrote in message
news:4cc7b9b7665549a4b34db8869b21a9f0@.ur
eader.com...
> Dear Sir,
> I used dynamic SQL on stored procedure to do the paging in my site.
> I often hit a connection with that dynamic SQl when I call it from .NET
> code.
> However if it run it under query analyzer, everything works fine.
> Who can explain this to me and what should I do in this case?
> regards,
> Guoqi Zheng
> http://www.ureader.com|||yes... I have a stored procedure which accept pagenr, pagesize as
parameter..
The correct sentence is that I hit a connection time out problem when I call
it from .NET code..
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:#7ubM76NGHA.1132@.TK2MSFTNGP10.phx.gbl...
> Hi
> How do you call it? Have you created a stored procedure which accepts
> parameters?
>
> "guoqi zheng" <no@.sorry.com> wrote in message
> news:4cc7b9b7665549a4b34db8869b21a9f0@.ur
eader.com...
>|||It is probably because you've got a 'bad' execution pln. Try recompile the
SP and see what is going on
"Guoqi Zheng" <no@.sorry.com> wrote in message
news:dtho1p$i4i$1@.newspe.com...
> yes... I have a stored procedure which accept pagenr, pagesize as
> parameter..
> The correct sentence is that I hit a connection time out problem when I
> call
> it from .NET code..
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:#7ubM76NGHA.1132@.TK2MSFTNGP10.phx.gbl...
>|||I actually do not know how can I compile my SQL code, not to mention
recompile.
Do you know how? I thought when I create a SP, it is compile automatically?
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:#W72oL7NGHA.3460@.TK2MSFTNGP15.phx.gbl...
> It is probably because you've got a 'bad' execution pln. Try recompile the
> SP and see what is going on
>
>
> "Guoqi Zheng" <no@.sorry.com> wrote in message
> news:dtho1p$i4i$1@.newspe.com...
.NET[vbcol=seagreen]
>|||There is sp_recompile stored procedure, please refer to the BOL
"Guoqi Zheng" <no@.sorry.com> wrote in message
news:dths3a$jq6$1@.newspe.com...
>I actually do not know how can I compile my SQL code, not to mention
> recompile.
> Do you know how? I thought when I create a SP, it is compile
> automatically?
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:#W72oL7NGHA.3460@.TK2MSFTNGP15.phx.gbl...
> .NET
>
Subscribe to:
Comments (Atom)