[dba-SQLServer] Query Analyzer hogs machine

Michael Maddison michael at ddisolutions.com.au
Sat Nov 25 23:16:21 CST 2006


Hi John,

SQL will as you have discovered use and hold as much memory as the OS
will allow.
This is by design.  
You can tinker with this but there are limitations with diff combos of
SQL and OS.

It's not actually QA that hogs memory, it uses bugger all IIRC, but SQL,
another reason
it really should be run on its own server, WITH NO OTHER APPS COMPETING.


Rather then stopping the whole service when
you have a runaway process try killing the process itself.

In QA
--This example shows how to terminate SPID 53.
KILL 53

Or Kill the process in EM if it hasn't locked up.  I've never seen QA
lock up BTW.

This will return the used memory as and when SQL needs it.  SQL uses RAM
to cache
data++ so it can perform queries more efficiently.  In your case with
the size of your 
datasets you probably don't have enough RAM to make much difference...
lol
In a way SQL treats RAM similarly to how Access treats CPU, it will take
100% but 
give back when required.

You don't say where you are running the query when you get the timeout?
I'll assume
its either in Access or EM?  By default QA has no timeout set, use that
always IMO.

I'm a bit confused as to what you expect to happen when you run large
queries concurrently. 
SQL has a lot of tools that can help to show where your bottlenecks are,
unfortunately there
is not likely to be a simple 1 answer fits all response as to how your
server hardware 
+ and SQL should be configured to work together.
http://www.sql-server-performance.com/articles_performance.asp

start reading ;-)))

cheers


Michael Maddison

DDI Solutions Pty Ltd
michael at ddisolutions.com.au
Bus: 0260400620
Mob: 0412620497
www.ddisolutions.com.au 
-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of JWColby
Sent: Saturday, 25 November 2006 2:19 AM
To: dba-sqlserver at databaseadvisors.com
Subject: Re: [dba-SQLServer] Query Analyzer hogs machine

Well, the wall appears to be physical memory.  I went into task manager
to look at memory used by process and sql server is sitting at 1.7 gb.
This is with Query analyzer and EM closed / unloaded.  The only way to
get it back is to stop sql server, as soon as I do that the memory is
returned to Windows.  

To do this processing I created a view of the two tables joined on the
PKID and pulling just the PKID out.  When I try to open the view, the
memory usage climbs until it hits 1.7gb, then a 10-15 seconds later I
get an ODBC SQL Server Driver error - Timeout Expired.

Pretty fascinating to me.  I would have thought that it would continue
to process, grabbing chunks of page file but that doesn't appear to be
the case.

John W. Colby
Colby Consulting
www.ColbyConsulting.com

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of JWColby
Sent: Friday, November 24, 2006 9:59 AM
To: dba-sqlserver at databaseadvisors.com
Subject: [dba-SQLServer] Query Analyzer hogs machine

Here's a fascinating one.  In query Analyzer I built a simple query
joining two tables with a PKID on the PKID, pulling one of the PKIDs
out.  
 
It was taking awhile to run so I switched away to run a process over in
Access.  This Access process displays a progress meter and normally
processes about 100K records per second.  
 
With the query running in Query Analyzer , Access was only processing
about 10K records per second!  The CPU Utilization was almost nil!!!  
 
When I aborted Query Analyzer, processor utilization shot up to 50% and
my Access process went to full speed.
 
Has anyone seen anything like this?  I must be hitting some kind of
limit somewhere but it isn't CPU Cycles.
 
John W. Colby
Colby Consulting
www.ColbyConsulting.com
 
_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com

_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com




More information about the dba-SQLServer mailing list