Showing posts with label experienced. Show all posts
Showing posts with label experienced. Show all posts

Monday, March 26, 2012

Performance degrade after using NOLOCK?

Hi all,
We experienced some deadlocks in our servers and to resolve those we used
nolock query hints in some of our select statements.
Afterwards we are experiencing "There is insufficient system memory to run
this query." errors for queries which previously ran well. These queries
also does not contain any nolock clause. The queries are relatively ones
which take a large time to execute but the problem is they ran well before.
No machine configuration change was done. SQL Server version is 2000 SP4.
The server administrators tell that in task manager that SQL Server did not
dislpay an unual hike in memory resources as well.
The sql statements are executed via a VFP com+ component via sqlexec.
A quick response in this regard will be appreciated as our application is
crashing due to this.
RJ
http://www.sql-server-performance.com/deadlocks.asp
"Roshan N.A. Jayalath" <roshannaj@.sltnet.lk> wrote in message
news:%23XVOMtkAHHA.3928@.TK2MSFTNGP03.phx.gbl...
> Hi all,
> We experienced some deadlocks in our servers and to resolve those we used
> nolock query hints in some of our select statements.
> Afterwards we are experiencing "There is insufficient system memory to run
> this query." errors for queries which previously ran well. These queries
> also does not contain any nolock clause. The queries are relatively ones
> which take a large time to execute but the problem is they ran well
> before.
> No machine configuration change was done. SQL Server version is 2000 SP4.
> The server administrators tell that in task manager that SQL Server did
> not
> dislpay an unual hike in memory resources as well.
> The sql statements are executed via a VFP com+ component via sqlexec.
> A quick response in this regard will be appreciated as our application is
> crashing due to this.
> RJ
>
>
|||Also these details were in the error log
2006-11-02 10:47:14.32 spid5154 WARNING: Failed to reserve contiguous
memory of Size= 65536.
2006-11-02 10:47:14.32 spid5154 Buffer Distribution: Stolen=21606 Free=352
Procedures=5972
Inram=0 Dirty=3177 Kept=0
I/O=2, Latched=1880, Other=173883
2006-11-02 10:47:14.32 spid5154 Buffer Counts: Commited=206872
Target=206872 Hashed=178942
InternalReservation=2396 ExternalReservation=388 Min Free=236 Visible=
206872
2006-11-02 10:47:14.32 spid5154 Procedure Cache: TotalProcs=2955
TotalPages=5972 InUsePages=2399
2006-11-02 10:47:14.32 spid5154 Dynamic Memory Manager: Stolen=27527 OS
Reserved=928
OS Committed=906
OS In Use=903
Query Plan=5739 Optimizer=0
General=6664
Utilities=280 Connection=15510
2006-11-02 10:47:14.32 spid5154 Global Memory Objects: Resource=6056
Locks=188
SQLCache=159 Replication=2
LockBytes=2 ServerGlobal=26
Xact=63
2006-11-02 10:47:14.32 spid5154 Query Memory Manager: Grants=2 Waiting=0
Maximum=91457 Available=91018
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:#3DXj2kAHHA.1780@.TK2MSFTNGP03.phx.gbl...[vbcol=seagreen]
> http://www.sql-server-performance.com/deadlocks.asp
>
> "Roshan N.A. Jayalath" <roshannaj@.sltnet.lk> wrote in message
> news:%23XVOMtkAHHA.3928@.TK2MSFTNGP03.phx.gbl...
used[vbcol=seagreen]
run[vbcol=seagreen]
SP4.[vbcol=seagreen]
is
>
|||is this relevant?
http://support.microsoft.com/kb/818095
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Roshan N.A. Jayalath" <roshannaj@.sltnet.lk> wrote in message
news:unzqKFmAHHA.4808@.TK2MSFTNGP03.phx.gbl...
> Also these details were in the error log
> 2006-11-02 10:47:14.32 spid5154 WARNING: Failed to reserve contiguous
> memory of Size= 65536.
> 2006-11-02 10:47:14.32 spid5154 Buffer Distribution: Stolen=21606
> Free=352
> Procedures=5972
> Inram=0 Dirty=3177 Kept=0
> I/O=2, Latched=1880, Other=173883
> 2006-11-02 10:47:14.32 spid5154 Buffer Counts: Commited=206872
> Target=206872 Hashed=178942
> InternalReservation=2396 ExternalReservation=388 Min Free=236 Visible=
> 206872
> 2006-11-02 10:47:14.32 spid5154 Procedure Cache: TotalProcs=2955
> TotalPages=5972 InUsePages=2399
> 2006-11-02 10:47:14.32 spid5154 Dynamic Memory Manager: Stolen=27527 OS
> Reserved=928
> OS Committed=906
> OS In Use=903
> Query Plan=5739 Optimizer=0
> General=6664
> Utilities=280 Connection=15510
> 2006-11-02 10:47:14.32 spid5154 Global Memory Objects: Resource=6056
> Locks=188
> SQLCache=159 Replication=2
> LockBytes=2 ServerGlobal=26
> Xact=63
> 2006-11-02 10:47:14.32 spid5154 Query Memory Manager: Grants=2 Waiting=0
> Maximum=91457 Available=91018
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:#3DXj2kAHHA.1780@.TK2MSFTNGP03.phx.gbl...
> used
> run
> SP4.
> is
>

Performance degrade after using NOLOCK?

Hi all,
We experienced some deadlocks in our servers and to resolve those we used
nolock query hints in some of our select statements.
Afterwards we are experiencing "There is insufficient system memory to run
this query." errors for queries which previously ran well. These queries
also does not contain any nolock clause. The queries are relatively ones
which take a large time to execute but the problem is they ran well before.
No machine configuration change was done. SQL Server version is 2000 SP4.
The server administrators tell that in task manager that SQL Server did not
dislpay an unual hike in memory resources as well.
The sql statements are executed via a VFP com+ component via sqlexec.
A quick response in this regard will be appreciated as our application is
crashing due to this.
RJhttp://www.sql-server-performance.com/deadlocks.asp
"Roshan N.A. Jayalath" <roshannaj@.sltnet.lk> wrote in message
news:%23XVOMtkAHHA.3928@.TK2MSFTNGP03.phx.gbl...
> Hi all,
> We experienced some deadlocks in our servers and to resolve those we used
> nolock query hints in some of our select statements.
> Afterwards we are experiencing "There is insufficient system memory to run
> this query." errors for queries which previously ran well. These queries
> also does not contain any nolock clause. The queries are relatively ones
> which take a large time to execute but the problem is they ran well
> before.
> No machine configuration change was done. SQL Server version is 2000 SP4.
> The server administrators tell that in task manager that SQL Server did
> not
> dislpay an unual hike in memory resources as well.
> The sql statements are executed via a VFP com+ component via sqlexec.
> A quick response in this regard will be appreciated as our application is
> crashing due to this.
> RJ
>
>|||Also these details were in the error log
2006-11-02 10:47:14.32 spid5154 WARNING: Failed to reserve contiguous
memory of Size= 65536.
2006-11-02 10:47:14.32 spid5154 Buffer Distribution: Stolen=21606 Free=352
Procedures=5972
Inram=0 Dirty=3177 Kept=0
I/O=2, Latched=1880, Other=173883
2006-11-02 10:47:14.32 spid5154 Buffer Counts: Commited=206872
Target=206872 Hashed=178942
InternalReservation=2396 ExternalReservation=388 Min Free=236 Visible=
206872
2006-11-02 10:47:14.32 spid5154 Procedure Cache: TotalProcs=2955
TotalPages=5972 InUsePages=2399
2006-11-02 10:47:14.32 spid5154 Dynamic Memory Manager: Stolen=27527 OS
Reserved=928
OS Committed=906
OS In Use=903
Query Plan=5739 Optimizer=0
General=6664
Utilities=280 Connection=15510
2006-11-02 10:47:14.32 spid5154 Global Memory Objects: Resource=6056
Locks=188
SQLCache=159 Replication=2
LockBytes=2 ServerGlobal=26
Xact=63
2006-11-02 10:47:14.32 spid5154 Query Memory Manager: Grants=2 Waiting=0
Maximum=91457 Available=91018
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:#3DXj2kAHHA.1780@.TK2MSFTNGP03.phx.gbl...
> http://www.sql-server-performance.com/deadlocks.asp
>
> "Roshan N.A. Jayalath" <roshannaj@.sltnet.lk> wrote in message
> news:%23XVOMtkAHHA.3928@.TK2MSFTNGP03.phx.gbl...
used[vbcol=seagreen]
run[vbcol=seagreen]
SP4.[vbcol=seagreen]
is[vbcol=seagreen]
>|||is this relevant?
http://support.microsoft.com/kb/818095
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Roshan N.A. Jayalath" <roshannaj@.sltnet.lk> wrote in message
news:unzqKFmAHHA.4808@.TK2MSFTNGP03.phx.gbl...
> Also these details were in the error log
> 2006-11-02 10:47:14.32 spid5154 WARNING: Failed to reserve contiguous
> memory of Size= 65536.
> 2006-11-02 10:47:14.32 spid5154 Buffer Distribution: Stolen=21606
> Free=352
> Procedures=5972
> Inram=0 Dirty=3177 Kept=0
> I/O=2, Latched=1880, Other=173883
> 2006-11-02 10:47:14.32 spid5154 Buffer Counts: Commited=206872
> Target=206872 Hashed=178942
> InternalReservation=2396 ExternalReservation=388 Min Free=236 Visible=
> 206872
> 2006-11-02 10:47:14.32 spid5154 Procedure Cache: TotalProcs=2955
> TotalPages=5972 InUsePages=2399
> 2006-11-02 10:47:14.32 spid5154 Dynamic Memory Manager: Stolen=27527 OS
> Reserved=928
> OS Committed=906
> OS In Use=903
> Query Plan=5739 Optimizer=0
> General=6664
> Utilities=280 Connection=15510
> 2006-11-02 10:47:14.32 spid5154 Global Memory Objects: Resource=6056
> Locks=188
> SQLCache=159 Replication=2
> LockBytes=2 ServerGlobal=26
> Xact=63
> 2006-11-02 10:47:14.32 spid5154 Query Memory Manager: Grants=2 Waiting=0
> Maximum=91457 Available=91018
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:#3DXj2kAHHA.1780@.TK2MSFTNGP03.phx.gbl...
> used
> run
> SP4.
> is
>

Performance degrade after using NOLOCK?

Hi all,
We experienced some deadlocks in our servers and to resolve those we used
nolock query hints in some of our select statements.
Afterwards we are experiencing "There is insufficient system memory to run
this query." errors for queries which previously ran well. These queries
also does not contain any nolock clause. The queries are relatively ones
which take a large time to execute but the problem is they ran well before.
No machine configuration change was done. SQL Server version is 2000 SP4.
The server administrators tell that in task manager that SQL Server did not
dislpay an unual hike in memory resources as well.
The sql statements are executed via a VFP com+ component via sqlexec.
A quick response in this regard will be appreciated as our application is
crashing due to this.
RJhttp://www.sql-server-performance.com/deadlocks.asp
"Roshan N.A. Jayalath" <roshannaj@.sltnet.lk> wrote in message
news:%23XVOMtkAHHA.3928@.TK2MSFTNGP03.phx.gbl...
> Hi all,
> We experienced some deadlocks in our servers and to resolve those we used
> nolock query hints in some of our select statements.
> Afterwards we are experiencing "There is insufficient system memory to run
> this query." errors for queries which previously ran well. These queries
> also does not contain any nolock clause. The queries are relatively ones
> which take a large time to execute but the problem is they ran well
> before.
> No machine configuration change was done. SQL Server version is 2000 SP4.
> The server administrators tell that in task manager that SQL Server did
> not
> dislpay an unual hike in memory resources as well.
> The sql statements are executed via a VFP com+ component via sqlexec.
> A quick response in this regard will be appreciated as our application is
> crashing due to this.
> RJ
>
>|||Also these details were in the error log
2006-11-02 10:47:14.32 spid5154 WARNING: Failed to reserve contiguous
memory of Size= 65536.
2006-11-02 10:47:14.32 spid5154 Buffer Distribution: Stolen=21606 Free=352
Procedures=5972
Inram=0 Dirty=3177 Kept=0
I/O=2, Latched=1880, Other=173883
2006-11-02 10:47:14.32 spid5154 Buffer Counts: Commited=206872
Target=206872 Hashed=178942
InternalReservation=2396 ExternalReservation=388 Min Free=236 Visible=206872
2006-11-02 10:47:14.32 spid5154 Procedure Cache: TotalProcs=2955
TotalPages=5972 InUsePages=2399
2006-11-02 10:47:14.32 spid5154 Dynamic Memory Manager: Stolen=27527 OS
Reserved=928
OS Committed=906
OS In Use=903
Query Plan=5739 Optimizer=0
General=6664
Utilities=280 Connection=15510
2006-11-02 10:47:14.32 spid5154 Global Memory Objects: Resource=6056
Locks=188
SQLCache=159 Replication=2
LockBytes=2 ServerGlobal=26
Xact=63
2006-11-02 10:47:14.32 spid5154 Query Memory Manager: Grants=2 Waiting=0
Maximum=91457 Available=91018
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:#3DXj2kAHHA.1780@.TK2MSFTNGP03.phx.gbl...
> http://www.sql-server-performance.com/deadlocks.asp
>
> "Roshan N.A. Jayalath" <roshannaj@.sltnet.lk> wrote in message
> news:%23XVOMtkAHHA.3928@.TK2MSFTNGP03.phx.gbl...
> > Hi all,
> >
> > We experienced some deadlocks in our servers and to resolve those we
used
> > nolock query hints in some of our select statements.
> >
> > Afterwards we are experiencing "There is insufficient system memory to
run
> > this query." errors for queries which previously ran well. These queries
> > also does not contain any nolock clause. The queries are relatively ones
> > which take a large time to execute but the problem is they ran well
> > before.
> >
> > No machine configuration change was done. SQL Server version is 2000
SP4.
> >
> > The server administrators tell that in task manager that SQL Server did
> > not
> > dislpay an unual hike in memory resources as well.
> >
> > The sql statements are executed via a VFP com+ component via sqlexec.
> >
> > A quick response in this regard will be appreciated as our application
is
> > crashing due to this.
> >
> > RJ
> >
> >
> >
>|||is this relevant?
http://support.microsoft.com/kb/818095
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Roshan N.A. Jayalath" <roshannaj@.sltnet.lk> wrote in message
news:unzqKFmAHHA.4808@.TK2MSFTNGP03.phx.gbl...
> Also these details were in the error log
> 2006-11-02 10:47:14.32 spid5154 WARNING: Failed to reserve contiguous
> memory of Size= 65536.
> 2006-11-02 10:47:14.32 spid5154 Buffer Distribution: Stolen=21606
> Free=352
> Procedures=5972
> Inram=0 Dirty=3177 Kept=0
> I/O=2, Latched=1880, Other=173883
> 2006-11-02 10:47:14.32 spid5154 Buffer Counts: Commited=206872
> Target=206872 Hashed=178942
> InternalReservation=2396 ExternalReservation=388 Min Free=236 Visible=> 206872
> 2006-11-02 10:47:14.32 spid5154 Procedure Cache: TotalProcs=2955
> TotalPages=5972 InUsePages=2399
> 2006-11-02 10:47:14.32 spid5154 Dynamic Memory Manager: Stolen=27527 OS
> Reserved=928
> OS Committed=906
> OS In Use=903
> Query Plan=5739 Optimizer=0
> General=6664
> Utilities=280 Connection=15510
> 2006-11-02 10:47:14.32 spid5154 Global Memory Objects: Resource=6056
> Locks=188
> SQLCache=159 Replication=2
> LockBytes=2 ServerGlobal=26
> Xact=63
> 2006-11-02 10:47:14.32 spid5154 Query Memory Manager: Grants=2 Waiting=0
> Maximum=91457 Available=91018
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:#3DXj2kAHHA.1780@.TK2MSFTNGP03.phx.gbl...
>> http://www.sql-server-performance.com/deadlocks.asp
>>
>> "Roshan N.A. Jayalath" <roshannaj@.sltnet.lk> wrote in message
>> news:%23XVOMtkAHHA.3928@.TK2MSFTNGP03.phx.gbl...
>> > Hi all,
>> >
>> > We experienced some deadlocks in our servers and to resolve those we
> used
>> > nolock query hints in some of our select statements.
>> >
>> > Afterwards we are experiencing "There is insufficient system memory to
> run
>> > this query." errors for queries which previously ran well. These
>> > queries
>> > also does not contain any nolock clause. The queries are relatively
>> > ones
>> > which take a large time to execute but the problem is they ran well
>> > before.
>> >
>> > No machine configuration change was done. SQL Server version is 2000
> SP4.
>> >
>> > The server administrators tell that in task manager that SQL Server did
>> > not
>> > dislpay an unual hike in memory resources as well.
>> >
>> > The sql statements are executed via a VFP com+ component via sqlexec.
>> >
>> > A quick response in this regard will be appreciated as our application
> is
>> > crashing due to this.
>> >
>> > RJ
>> >
>> >
>> >
>>
>sql

Monday, March 12, 2012

Performance and filegrowth

I'm new to SQL Server 2000 so I was hoping to get advice from some of you experienced SQL Administrators.

I have been reading about managing user database size and thought that I may be able to gain some performance from my server by changing my current server from automatically growing. i understand that the system must continually monitor the size of each file to determine is and when it needs to grow.

I wanted to change the setting on my server under the options for:

Space allocated for the DATA and Transaction Log files.
Change File Growth settings
Change Maximum File Size settings

My question is - Can this be done while users are connected or should it be done after hours?

Any other information that maybe helpful to me when working with the setting mentioned above would be appreciated. Again I'm an SQL newbie.Those values are dynamic, meaning that they can be implemented while there are active connections to the database. However, if you attempt to increase preallocated space it may impact current users under certain conditions. If performance is that import and there is high activity on the database that affects its growth (or rather requires periodic growth) I'd invest some time to investigate the rate of growth and preallocate enough room so that this background process does not divert server resources during business hours. But keep in mind that this approach will force you to commit more time administering this database, because along with the above you'll have to periodically monitor used space to confirm that the results of your investigation are correct and nothing unusual happened to affect the growth of the space used. You'll also have to perform scheduled increase in preallocated space manually, and preferrably after hours.|||Hello Arra2

I didt agree that you will have increment in performance disabling automatic grow.

In fact if dont realy knows how your database grows, keep it as built
or you will receive SQL error at front end telling that log is full.

If you realy want increase performance use Profile to find what commands are using more CPU (CPU > 2000) or reads (reads > 30000)
or duration (Duration > 4000). Analyse indexes e sql commands structures.

Good Luck

Robinson|||Disabling Auto Growth in high activity environment WILL positively affect the performance, - that's a fact. And I think Mr. Robinson is also confusing Auto Growth with Recovery Mode, which are not the same ;) All other suggestions are actually good, so go get'em, tiger ;)|||Thanks to all for the help.

Friday, March 9, 2012

Performance

Hello,

I wanted your opinion on a technique that we want to use and hear from
you if you know of or, have experienced performance or other issues in
such a scenario.

We have a business application that has two parts to it - 1. A part
that works on things such as managing users, roles, authorization,
application level settings etc. This part of the application will
require its own set of tables in the database.

Additionally, and more importantly we have a part that provides the
core business value. And this part will need its own set of tables etc
in the database.

All of this was thought to live within one ms sql database
(2000/2005/Express we need to support all three). Now the Business part
of the database is expected to have a lot of data. In some tables - we
may have as much as 1 to 4 Million records. Ofcourse the number of such
tables is very few. Probably between 1 and 3.

So thats about the application. Now, we need to allow the users to
actually be able to create several named versions of the business side
of the database and use them. An example may be - the user may start
with lets say "My Simulation First Cut" and work with it for a while.
He may then choose to create a new version such as "My Second Attempt"
etc. The number of versions may not be too many. May be 5 to 15 at any
time may be present.

We were initially thinking of acheiving this by having one catalogue
database that manages the user info and a table that keeps track of
versions, while each version may be a sql database in itself. The
application was thought to be able to provide the ability to create
versions and allow the user to select the context in which he would
like to work in.

This is a solution that we need to port to DB2 on iseries aswell, and
now we are thinking of actually having just one database having many
schemas within it. One schema would hold the user information and we
are thinking of dynamically creating versions by creating additional
schemas on the single database. This would be somethng that the
applicaton would manage.

Some additional features that we would need to provide would be the
ability to quickly copy one of the existing schema/version into a new
schema/version. The application mostly works on dynamic sql and so has
very few stored procedures and functions etc... Therfore the multiple
schemas/versions would amount to very little redundant db objects.

Overall, we seem to think the multiple schemas (being created
dynamically) route is much better than the multiple databases(being
created dynamically) route. Do you forsee any problems with this
approach ?

Thanks in advance, Regards,

Avinash(avinashraj@.gmail.com) writes:
> This is a solution that we need to port to DB2 on iseries aswell, and
> now we are thinking of actually having just one database having many
> schemas within it. One schema would hold the user information and we
> are thinking of dynamically creating versions by creating additional
> schemas on the single database. This would be somethng that the
> applicaton would manage.

Beware that in SQL 2000, schema is always tied to a user or a role. Since
you need to support SQL 2000, you could get some problems there, although
you probably can come around it by creating dummy roles and use these
as schemas.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx