August 21, 2002 at 10:15 am
I have several developers that will use the 'open all rows' option in Enterprise Manager instead of typing 'select top 10 * from table' in Query Analyzer. The problem is that several of our tables have 118,000,000 rows and I understand that this option will load the entire table into memory.
I have used this EM feature in the past if I have a small table that I need to change data on so I believe it must issue locks in case the data will be changed.
My question: Is this having a detrimental effect on server performance or on the end-user's ability to access data in that table?
Using SQL Server 7.0 SP3 Standard Edition and SQL Server 2000 SP2 Enterprise Edition. Both on Windows 2000 Advanced Server.
Thanks,
Michelle
Michelle
August 21, 2002 at 2:45 pm
Don't quote me, but I believe it does put some serious locks on the tables. We had soem developers here do that and we could watch the locks line up behind them. And, if you look at the way it is editing and accessing the data via profiler, it would make sense it is locking the records retrieved.
Brian Glass
Sr. Database Adminstrator
Bombardier Aerospace
August 22, 2002 at 4:19 am
There are locks added during the read process. It is the same as running SELECT * FROM tbl in any other application. I believe they are intent share locks or share locks as long as it is a select only but with that many rows they are causing a lot of network traffic for sre especially if they are looking for data at the end. I would personally tell them to only use QA and write queries to get what they need not all if possible.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply