May 7, 2003 at 7:29 am
Hello,
We recently did clean install of SQL 2000 on our Production server and added all of our Databases in there. Applied SP3. Updated Stats and Reindexed everything. Things went fine for few days and now we have started to see SQL server blocks in our Database. We tried to find out what could be the reason of this. We figured out that one application was holding lots of DB resources and that was one of the reason. We have changed that and that's working fine now but now again we saw some blocking.
So my question is does anyone know what could cause blocks in Database? I see some of the processes blocked by 1 process and that might say it's waiting and wait type is 'MISCELLANEOUS'. I had never seen this before. This quesries are most of the time SELECT queries that have locks in it.
Any Help would be appreciated.
Thanks.
May 8, 2003 at 10:06 am
We saw this problem when someone was using another application to view the data which was reading large amounts of data, but feeding the results to the display a little at a time. This resulted in the table being locked. I know that Crystal Reports often reads in this fashion and Enterprise Manager did in version 6.5. We also had problems using Delphi BDE grid components.
Just $.02
Guarddata-
May 12, 2003 at 6:12 pm
If it is Crystal or MS-Access, you should see it in the Application or ProgramName column of one of: EnterpriseMgr, sp_who2, sysprocesses.
Or you can also run Profiler to catch this and more information.
May 14, 2003 at 12:59 pm
You can run profiler or check in current activity to catch the application which causes the blocking. The long query would take lots of resources. The query with many joins or subquery, select distinct, order by, ......will hold resources and caused blocking.
Robert
May 19, 2003 at 10:00 pm
I'd also check the memory usage for the SQL instance. Compare it to what was being used before SP3 was applied. I have seen SQL Server start escalating to table locks when the amount of available memory is insufficient for the current activity.
In particular, this occurred to us one time when someone fired up a memory-hog application on the server, robbing SQL Server of it's normally available amount of memory.
I have subsequently changed from allowing SQL Server to (entirely) dynamically allocate it's memory. I know specify an appropriate min server memory and max server memory. The min server memory is appropriate for the amount of activity anticipated for that server.
David R Buckingham, MCDBA,MCSA,MCP
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply