Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

SQL causes mem usage to jump to 100% 1

Status
Not open for further replies.
perfectchaoss

perfectchaoss

Programmer
Nov 6, 2002
44
US
Hi,
When ever i run a query on SQL the memory usage quickly jumps to 100%. Yesterday I was running the same exact same queries with no problems. There are only 2 things I can think of that i changed. I rebuild an index on one of my tables and I downloaded a patch from microsofts "Automatic Updates" that supposedly fixed a security issue. I just scanned the disk for viruses and it did not find any. Before I uninstall the automatic update I wanted to see if anyone else is having this problem or can provide any insite into what is going on.

Thanks in advance,
JF
 
Sort by date Sort by votes
Are you referring to the memory usage on a client PC or on the SQL Server? How do run the query - Query Analyzer (QA), Enterprise Manager (EM), or another client? If using QA, is the result returned to a grid or text window? How many rows are returned by the query? If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:
 
Upvote 0 Downvote
It is the memory usage on the SQL Server. I have ran Queries through EM and other client PC's with the same result. Smaller queries around 500,000 rows complete but cause memory usage jump up and does not come down until i restart the server or end the "sqlservr.exe" process through the process manager. The larger queries 2 million+ rows will cause memory usage to jump up to 100% and do not finish.
 
Upvote 0 Downvote
here is the update i downloaded: Windows 2000 Hotfix (SP4) Q815021
 
Upvote 0 Downvote
SQL caches the datapages. This is normal. SQL server holds onto the datapages in memory in case you run the query again. It will hold the datapages into memory until another query comes along, and it need to reallocate that memory for the new query. It's a performance enhancement to SQL so that the DB doesn't have to scan the disk for the data.

-Bad Dos
 
Upvote 0 Downvote
SL Server is performing as it should. It is desirable to have SQL Server use as much memory as possible for performance. If your queries are returning 500K or more rows to a client, performance is bound to be poor. How are you running the queries? If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:
 
Upvote 0 Downvote
Wow really, I guess the reason it is causeing a problem is it takes up so much resources on the CPU i could not open a file on the D: Drive. Is this normal? It gave me a message that my server did not have enought memory to perform the operation. I never had this problem in the past.
 
Upvote 0 Downvote
How much memory is on the server? Do other services or applications run on the server in addition to SQL Server? What is the CPU time measure when these queries run? How much disk space is available for TempDB? How big is TempDB? If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:
 
Upvote 0 Downvote
I run them through excel and access. The one in particular that caused a problem was when I was creating a OLAP cube in excel for a pivot table.
 
Upvote 0 Downvote
Those types of queries are resource intensive. They require a lot of memory and CPU time. You haven't provided any additional information about the available memory, etc. I can only guess that the server is underpowered for your applications. If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:
 
Upvote 0 Downvote
Memory on server:2 gigs
Apps: excel, acess, antivirus software,:
There are no additional Services, besides some minor file storage.
tempdb: disk space availible: 7.72
tempdb: Size: 8.85
Im not quite sure of the CPU time measure but i'll find out. the 500k query took only a few seconds to run through EM on the server.
 
Upvote 0 Downvote
Sorry both of those figures are in MB
tempdb disk space: 7.72MB
Size: 8.85 MB
 
Upvote 0 Downvote
Do you mean there is only 7.72 MB of disk space available on the disk drive that hold tempdb? or is that the unused space in tempdb? How much unused space is available on the drive?

If you are running Excel and Access on the computer, they will get some of the memory and CPU time. It is usually not a good idea to run applications on the SQL Server. If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:
 
Upvote 0 Downvote
The Disc drive has over 200 gigs of free storage.

The Space available: 7.72MB and Size: 8.85MB were taken from right clicking the temp DB.

the temp db data file and trans log are both set to grow unrestricted.
 
Upvote 0 Downvote
tlBroadbent,
Thanks for all of your help. I realize that SQL has caches the query while it is running. But isnt it supposed to free up the memory after it is finished? Or when you try to run a new query. With two or Three queries it jumps from 200,000k/1,961,012k to 1,940,948k/1,961,012k and just stays there. Before yesterday it never got much higher than 500,000k/1,961,012k no matter what size query I ran.

Thanks again,
JF
 
Upvote 0 Downvote
One other thing you may want to consider doing is to change the maximum amount of ram that is available to SQL. Back it off about 256 meg or 512 meg. SQL will utilize as much free ram as there is and not leave any for the OS. Eventually on large queries you will experience slow returns because the OS on the server is trying to do other things as well. Reserve this memory and see how things go.

andy
 
Upvote 0 Downvote
Can anyone tell me how to change the max amount of ram availible to SQL. I do it through the OS right? Or is it setting in SQL.
 
Upvote 0 Downvote
The setting is in SQL Server. Before making any changes to SQL Server memory options, I recommend reading about the options in SQL BOL. See the topic, "Server Memory Options."

From Enterprise Manager, open the server properties and click on the memory tab. Make changes.

From Query Analyzer, OSQL or another tool that can ececute SQL Statements run sp_configure to set the min and max memory options.

--set advanced options on
exec sp_configure 'show advanced options',1
reconfigure with override
go

--Set max memory to 1500 MB
exec sp_configure 'max server memory',1500
reconfigure with override
go

--set advanced options off
exec sp_configure 'show advanced options',1
reconfigure with override
If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

MathiasB
  • Locked
  • Question
Parameterised sql server query
Replies
8
Views
485
Chriss Miller
C
V
  • Locked
  • Question
xp_cmdshell vs psexec - this SQL server forgets credential to an W10 share
Replies
0
Views
809
Volvere
V
Cpreston
  • Locked
  • Question
Seconds to minutes 2
Replies
5
Views
2K
Chris Miller
Chris Miller
gbaughma
  • Locked
  • Question
SQL Performance
Replies
11
Views
1K
pjw001
pjw001
Steve Yu
  • Locked
  • Question
How secure is MS SQL on the Internet vs behind firewall inside a LAN 1
Replies
3
Views
613
Chris Miller
Chris Miller

Part and Inventory Search

Sponsor

Back
Top