 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
SQL Server Forum / General / Other SQL Server Topics / August 2005Memory usage\Performance problem
I am experiencing the following problem; I boot my Windows 2000 sp#4 machine. I check sqlservr.exe's memory usage in Task manager. It is ca 20 mb, everything is OK. Then I run this query; select postoffice, count(*) as counter from tblTable where postoffice is not null group by postoffice order by counter DESC There's no index or constraint on field "postoffice" and tblTable contains ca 916.000 rows. I thought this query would still execute in only a few seconds, but it takes minutes. And the worst thing is that sqlserver.exe's memory usage grows to about 300-400 mb when the query runs. What am I doing wrong?? Tnx in advance! /Magnus > I am experiencing the following problem; > [quoted text clipped - 12 lines] > sqlserver.exe's memory usage grows to about 300-400 mb when the query > runs. What am I doing wrong?? So what is the average row size of this table? Say that is 300 bytes, then that is 300 MB of data to read. That is not very likely to be done instantly. SQL Server's memory consumption will increase, as it will read the entire table into cache, and the table will stay in the cache as long as no other data competes about the space. This means that if you resubmit the query, the response time will be significantly shorter. SQL Server is designed to grab as much memory it can, as the more data in can have in cache, the better the response times. If there are other applications competing for memory on the machine, SQL Server will yield, but in this case it may be better to configure how much memory you want SQL Server to use. Note also that framgmenation could cause extra delay. Use DBCC SHOWCONTIG to see what shape the table is in. To defragment it, you would have to create a clustered index on the table, and then drop that index. The query itself would benefit enormously by a non-clustered index on postoffice.  SignatureErland Sommarskog, SQL Server MVP, esquel@sommarskog.se Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp >> I am experiencing the following problem; >> [quoted text clipped - 34 lines] > The query itself would benefit enormously by a non-clustered index on > postoffice. Thanks for a well-written answer! I guess my expectations on selecting based on a non-indexed column were somewhat high. When I added an index, my query executes in seconds. One index solved this problem, but my table contains 30-40 similar columns, and I don't think indexing every column is a good idea. Isn't there any other ways of speeding up selects? Well, I guess not... /Magnus > I guess my expectations on selecting based on a non-indexed column were > somewhat high. When I added an index, my query executes in seconds. One > index solved this problem, but my table contains 30-40 similar columns, > and I don't think indexing every column is a good idea. > > Isn't there any other ways of speeding up selects? Well, I guess not... Well, once data is in cache it will be faster. Or at least less slow. But if you need to do this on every column, it sounds to me like one of those things Analysis Services is good for. SignatureErland Sommarskog, SQL Server MVP, esquel@sommarskog.se Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp > I guess my expectations on selecting based on a non-indexed column > were somewhat high. When I added an index, my query executes in > seconds. > One index solved this problem, but my table contains 30-40 similar > columns, and I don't think indexing every column is a good idea. Two things come to mind: 1. rethink your table design. If these columns are so similar you might be better off with a second table which is joined. But that of course depends on your data - just an idea. 2. Create indexes (possibly composite indexes) for most used queries. You might even get away with a single (or few) composite index if your queries only use a leading portion of this index's fields. > Isn't there any other ways of speeding up selects? Well, I guess > not... Well, there are tons of other options, like having data files on several disks, putting tx log on a separate disk, adding memory, .... It all depends. :-) Kind regards robert Thank you Robert, these all are things that I'll keep in mind. One more thing though; why is the sqlservr.exe process consuming 395 mb RAM when the entire server is idle? My "cron job" is inserting a few 1000 rows of data at night, but now it is late morning here in Finland, and the entire machine is more or less idle. On my other servers, the RAM consumption on idle is about 20-30 mb only. Anyone got any ideas? /Magnus >> I guess my expectations on selecting based on a non-indexed column >> were somewhat high. When I added an index, my query executes in [quoted text clipped - 22 lines] > > robert > One more thing though; why is the sqlservr.exe process consuming 395 mb > RAM when the entire server is idle? I believe that was in my first reply. SQL Server is designed to get as much memory as it can, and only yield if an another application needs it. This is because it keeps data in cache so that future requests for the same data can be answered without reading from disk. Thus, this is perfectly normal behaviour. SignatureErland Sommarskog, SQL Server MVP, esquel@sommarskog.se Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp >> One more thing though; why is the sqlservr.exe process consuming 395 >> mb RAM when the entire server is idle? [quoted text clipped - 7 lines] > > Thus, this is perfectly normal behaviour. Adding to that max memory consumption is easily configurable so if 400MB is too much for you then simply turn that down. Kind regards robert |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2010 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.