April 19, 2008 at 6:57 am
[font="Arial"]We were facing the problem of SQL Blocking in SQL 2000.Not only this but also our users will have to wait a long for some calculation or retireiving the data from database.So we have upgraded our server & done all the required configuration recommended by Microsoft. Now we have deployed IBM DS3200 storage box with x3550 server with 14 SAS 3 GB/S HDD.Now the data retrieval from db for the last 4-5 years have become very much efficient & fast even with multi user load.But the problematic area is still observed that ceratin process is there which blocks other process.We have observed that when one user keep some calculation process(Uses SELECT & UPDATE statement) & simultaneously other users do retrieval process(Uses SELECT statement) then as soon as the calculation process completes other users retrival process also dispalys its result.It means that retrieval process hardly kept one second to complete but due to that first process it also have to wait. When we see in querry analyser by sp_who2 it shows that second process is blocked by first process. How do we resolve this issue after upgrading the server also as blocking has also very important to maintain database intigrity.
So DBA point of view what action should be taken ?[/font]
April 19, 2008 at 9:31 am
- I think you've posted in the wrong forum (sql2005 backup)
- what kind of isolation level are your applications using.
avoid repeateble read as much as you can.
sp_blocker_80 shows the isolation level. http://support.microsoft.com/default.aspx?scid=kb;en-us;271509
(use read-committed for your update processes, maybe even read uncommitted by your select only apps)
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
April 20, 2008 at 12:05 am
Are you statistics up-to-date (WITH FULLSCAN)?
Are your indexes defragmented?
Are your queries performing full table scans, where an index would have helped?
These are some of the things you need to be looking at.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
April 21, 2008 at 12:46 am
Are the queries written optimally?
Do you have appropriate indexes?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 21, 2008 at 4:13 am
In my experience blocking is almost always due to poor application code rather than anything you can do much about on SQL Server - that said if you do not have access to the code to optimise/correct it there are some things you may be able to do. Profiler is your friend here - get some traces to see what SQL is causing the blocks and then look at the tables that are affected to see if optimising indexes etc could help
James Horsley
Workflow Consulting Limited
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply