What I found was a problem with, I believe, the lock memory in sql server after making these changes. A complex update query that before the changes would complete in five or six minutes was now not completing after an hour or two...I would have to go into QA and kill the process everytime. After running a trace and running sp_who2, it looked like tens of thousands of locks were being created when the query would run
Now here is my request
Obviously, I missed something in the configuration of the AWE memory in sql server. If anyone can walk me through it I would be most grateful
Thank you
MichaeThis is a multi-part message in MIME format.
--=_NextPart_000_06F0_01C3B362.895E6E60
Content-Type: text/plain;
charset="Utf-8"
Content-Transfer-Encoding: 7bit
You have 8 GGB on the server, yet configure SQL Server to take "a constant
3gb of RAM". Why? On our cluster, we gave 6.5 GB to SQL Server and the
rest to the OS. Use:
sp_configure 'awe', 6656
... and restart SQL Server.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"michael weiss" <mhweiss@.hotmail.com> wrote in message
news:2F59D0ED-086F-4BD2-8117-B05882D7F1D4@.microsoft.com...
I just went through configuring windows 2003 server to utilize the full 8gb
of RAM on the box for the purpose of enabling sql server 2000 to grab more
memory. After setting the /PAE and /3GB switches in the boot file, I set the
server options on the sql server to show advanced options, enabled awe
memory capability and set sql server to use a constant 3gb of RAM. I
received some error messages which I tracked down and, as a result of the
solutions I found online, disabled auto shrinking of the databases and also
disabled the minimum memory setting for queries.
What I found was a problem with, I believe, the lock memory in sql server
after making these changes. A complex update query that before the changes
would complete in five or six minutes was now not completing after an hour
or two...I would have to go into QA and kill the process everytime. After
running a trace and running sp_who2, it looked like tens of thousands of
locks were being created when the query would run.
Now here is my request -
Obviously, I missed something in the configuration of the AWE memory in sql
server. If anyone can walk me through it I would be most grateful.
Thank you,
Michael
--=_NextPart_000_06F0_01C3B362.895E6E60
Content-Type: text/html;
charset="Utf-8"
Content-Transfer-Encoding: quoted-printable
=EF=BB=BF<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
You have 8 GGB on the server, yet =configure SQL Server to take "a constant 3gb of RAM". Why? On our cluster, =we gave 6.5 GB to SQL Server and the rest to the OS. Use:
sp_configure 'awe', =6656
... and restart SQL =Server.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"michael weiss"
--=_NextPart_000_06F0_01C3B362.895E6E60--|||Thank you, Tom...I used the sp_configure and set the memory to use that way. Then I started to have serious problems with that update query that seemed to be holding locks and not releasing them. Therefore, I wondered if I were missing something or if anyone else has had a similar issue? Reverting back to the original configuration for memory usage (AWE disabled and removing the /PAE and /3GB switches from the boot ini file) fixed the query problem. I am running the latest service packs/updates on both sql server and win2k3 enterprise server.
Thanks,
Michael|||This is a multi-part message in MIME format.
--=_NextPart_000_0775_01C3B368.3E2D1B20
Content-Type: text/plain;
charset="Utf-8"
Content-Transfer-Encoding: 7bit
Sorry about that. Try:
sp_configure 'awe', 1
reconfigure with override
go
sp_configure 'max server', 6656
reconfigure with override
go
sp_configure 'min server', 0
reconfigure with override
go
Restart SQL Server. That said, it is possible that parallelism caused
degradation of this particular query. If the above doesn't work, try adding
OPTION (MAXDOP 1) at the end of the statement. This turns off parallelism.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"michael weiss" <mhweiss@.hotmail.com> wrote in message
news:317296B6-5AEE-451A-ABDA-471474D79703@.microsoft.com...
Thank you, Tom...I used the sp_configure and set the memory to use that way.
Then I started to have serious problems with that update query that seemed
to be holding locks and not releasing them. Therefore, I wondered if I were
missing something or if anyone else has had a similar issue? Reverting back
to the original configuration for memory usage (AWE disabled and removing
the /PAE and /3GB switches from the boot ini file) fixed the query problem.
I am running the latest service packs/updates on both sql server and win2k3
enterprise server.
Thanks,
Michael
--=_NextPart_000_0775_01C3B368.3E2D1B20
Content-Type: text/html;
charset="Utf-8"
Content-Transfer-Encoding: quoted-printable
=EF=BB=BF<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Sorry about that. =Try:
sp_configure 'awe', =1reconfigure with overridegosp_configure 'max server', 6656reconfigure with overridegosp_configure 'min server', 0reconfigure with overridego
Restart SQL Server. That =said, it is possible that parallelism caused degradation of this particular =query. If the above doesn't work, try adding OPTION (MAXDOP 1) at the end of =the statement. This turns off parallelism.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"michael weiss"
--=_NextPart_000_0775_01C3B368.3E2D1B20--|||Tom,
Could parallelism cause the creation of massive quantities of table locks?
Thanks,
Michael|||This is a multi-part message in MIME format.
--=_NextPart_000_0818_01C3B36D.BC615B50
Content-Type: text/plain;
charset="Utf-8"
Content-Transfer-Encoding: 7bit
It's not uncommon to have parallelized queries run slower than their
single-threaded counterparts. I'm surprised about "massive quantities of
table locks". A query would issue one table lock per table. It could issue
many, many page or row locks, though. Is it possible to specify a table
lock for this query or are others accessing the table at the time?
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"michael weiss" <anonymous@.discussions.microsoft.com> wrote in message
news:2C7FADE9-E72C-492C-915D-D58458944D89@.microsoft.com...
Tom,
Could parallelism cause the creation of massive quantities of table locks?
Thanks,
Michael
--=_NextPart_000_0818_01C3B36D.BC615B50
Content-Type: text/html;
charset="Utf-8"
Content-Transfer-Encoding: quoted-printable
=EF=BB=BF<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
It's not uncommon to have parallelized =queries run slower than their single-threaded counterparts. I'm surprised =about "massive quantities of table locks". A query would issue one table =lock per table. It could issue many, many page or row locks, =though. Is it possible to specify a table lock for this query or are others =accessing the table at the time?
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"michael weiss" wrote in message news:2C7=FADE9-E72C-492C-915D-D58458944D89@.microsoft.com...Tom,Could= parallelism cause the creation of massive quantities of table locks?Thanks,Michael
--=_NextPart_000_0818_01C3B36D.BC615B50--|||Sorry, Tom...I meant page locks, not table locks. My mistake. The query calls a correlated subquery many times depending on the outcome of various CASE statements. Therefore, the table which the subquery pulls data from is queried several times. I just never had any problems before I tried enabling AWE on the sql server
Thanks
Michael|||This is a multi-part message in MIME format.
--=_NextPart_000_089B_01C3B371.9D7C9610
Content-Type: text/plain;
charset="Utf-8"
Content-Transfer-Encoding: 7bit
OK, have you tried my revised script and re-started SQL Server? Also, it
could be that the query needs a re-write or you may need to tune your
indexing.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"michael weiss" <anonymous@.discussions.microsoft.com> wrote in message
news:05211AC9-8FF9-4980-AB85-D7262629F027@.microsoft.com...
Sorry, Tom...I meant page locks, not table locks. My mistake. The query
calls a correlated subquery many times depending on the outcome of various
CASE statements. Therefore, the table which the subquery pulls data from is
queried several times. I just never had any problems before I tried enabling
AWE on the sql server.
Thanks,
Michael
--=_NextPart_000_089B_01C3B371.9D7C9610
Content-Type: text/html;
charset="Utf-8"
Content-Transfer-Encoding: quoted-printable
=EF=BB=BF<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
OK, have you tried my revised script =and re-started SQL Server? Also, it could be that the query needs a =re-write or you may need to tune your indexing.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"michael weiss" wrote in message news:052=11AC9-8FF9-4980-AB85-D7262629F027@.microsoft.com...Sorry, Tom...I meant page locks, not table locks. My mistake. The query calls a =correlated subquery many times depending on the outcome of various CASE statements. Therefore, the table which the subquery pulls data from is =queried several times. I just never had any problems before I tried enabling AWE =on the sql server.Thanks,Michael
--=_NextPart_000_089B_01C3B371.9D7C9610--|||Thank you, Tom...I set the server as you suggested including configuring sql server to use 6.5GB of RAM, added the MAXDOP(1) option to the end of the query and it smoked right through 1.5 million records in just over 4 minutes.
Many thanks,
Michael|||P.S. on a related note...
take a look at dbcc sqlperf(waitstats). You should be able to find some
decent info on it if you google it..
Large waittimes with a waittype of cxpacket are often associated with cases
where parallelism is getting bogged down for one reason or another such that
serial plans (or at leat DOP less than number of CPU...) is a good idea...
looking at the waitstats is a nice way to quantitatively predict if lowering
DOP might help...
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"michael weiss" <mhweiss@.hotmail.com> wrote in message
news:1D2337B2-D79B-4B8F-975C-12138EBDCEEA@.microsoft.com...
> Thank you, Tom...I set the server as you suggested including configuring
sql server to use 6.5GB of RAM, added the MAXDOP(1) option to the end of the
query and it smoked right through 1.5 million records in just over 4
minutes.
> Many thanks,
> Michael|||This is a multi-part message in MIME format.
--=_NextPart_000_007A_01C3B3F9.C79FC0B0
Content-Type: text/plain;
charset="Utf-8"
Content-Transfer-Encoding: 7bit
Kewl! Glad to have made a difference. You may be able to tweak it more
with some coding and indexing changes.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"michael weiss" <mhweiss@.hotmail.com> wrote in message
news:1D2337B2-D79B-4B8F-975C-12138EBDCEEA@.microsoft.com...
Thank you, Tom...I set the server as you suggested including configuring sql
server to use 6.5GB of RAM, added the MAXDOP(1) option to the end of the
query and it smoked right through 1.5 million records in just over 4
minutes.
Many thanks,
Michael
--=_NextPart_000_007A_01C3B3F9.C79FC0B0
Content-Type: text/html;
charset="Utf-8"
Content-Transfer-Encoding: quoted-printable
=EF=BB=BF<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Kewl! Glad to have made a =difference. You may be able to tweak it more with some coding and indexing changes.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"michael weiss"
--=_NextPart_000_007A_01C3B3F9.C79FC0B0--
No comments:
Post a Comment