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