November 17, 2007 at 12:59 am
Help!
I'm seeing a server that goes through periods where there is lots of blocking.
At those times (several times per night and at apparently random times throughout the day), this query returns more than 0 rows:
select * from master..sysprocesses where blocked > 0
In at least two-thirds of the cases, the query above shows blocking - one spid blocking another, which hangs our web servers.
The db server in question definitely appears overtaxed, but I am trying to find out what if anything I can do to stop the blocking.
We are running SQL 2000 SE on it, so it can't use more than 2 GB of RAM.
Given that limitation, what can I do to reduce the load, identify the culprit queries, etc.? I have reviewed the info from a procedure named aba_lockinfo, but it captures lots of data, and I'm not sure how to use the data to track down the problems.
The problem is starting to wear on me because I have to watch the server all night. What can I do? Also, how can I check whether there is a virus, a denial of service attack, etc. causing the problem or making it worse?
Thanks for any advice.
webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
November 17, 2007 at 2:26 am
Use DBCC INPUTBUFFER to see what query is running on the blocking process. Then go through the normal steps to troubleshoot that query - eliminating any row-by-row processing, ensuring the proper indexes are in place... there's loads of information on this site about all that.
John
November 17, 2007 at 10:23 am
Ummmm... what makes you think blocking is so unhealthy? It's part of the normal process of INSERT, UPDATE, and DELETE... there will ALWAYS be some blocking particullary if you have a lot of "night runs". Granted, a huge number of blocks indicates that code is running a bit ineffeciently and that some transactions may be particullarly and incorrectly long winded (and the code needs to be rewritten to fix that), but blocking is part of the normal function of SQL Server and all RDBMS's.
What you first need to concern yourself more with is... is any of that blocking producing deadlocks? Now, those are unhealthy... 😉 Once you've determined that there is no deadlocking or you've fixed those things that are, then you can start to worry about the extent of blocking.
--Jeff Moden
November 17, 2007 at 9:21 pm
Thanks, Jeff,
The issue with the blocking in this case is that it results from queries waiting so long that they are causing the web site to run very slowly or to hang. That is what I am getting complaints about, because the monitors that check those web servers are counting this as downtime - which it is if the users can't run the web pages. We are migrating to new hardware soon, which has a good chance of providing improvement, but if there are underlying issues with the queries or with something else I am not getting to the root of, then I fear the problem will resurface.
I have not seen many deadlocks (any really, maybe one per week) compared to long-running blocks which happen several times per day. The relative rarity of the deadlocks, from what you say, seems like relatively good news.
But I am trying to figure out if it is always the same queries causing the blocking, and what, if any, options are available to improve performance to shorten the blocking times. So far I have gotten some leads by finding costly queries with SQL Profiler - ones that have one or two runs with a long duration or many reads, or ones that have a low number of reads for each run but that are running many times per hour.
I realize that there is a lot of information on this topic, but so far it has overwhelmed me. I am looking for a method of investigation that will help narrow down what the issue is. Something that might enable me to focus on one aspect of performance at a time since I am new to performance tuning.
Night runs are one reasonable suspect, but they don't explain why we also get blocking at other times of the day. And I find that I end up killing processes too often - that's just a band-aid solution, and a risky one at that.
If you can suggest any methodical way of finding and resolving blocking that is taking too long, I would be very grateful.
Thanks again!
webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
November 17, 2007 at 9:56 pm
Take a look at the following KB article. Basically, MS has created a stored procedure you'll need to create on your SQL Server. Then, you'll need to execute it via the means described. Make sure you start execution PRIOR to the blocking issues. The reason for this is that at times a blocking issue can tie up resources so bad that you can't get logged in. It's not pretty when it happens.
Basically the stored procedure is going to return any blocking info, including queries responsible, etc., and if you have the results going to a log file, you should be able to read that log file to figure out what is causing your blocking issues. I've used it in the past to help diagnose blocking issues, and it can be pretty effective.
How to monitor blocking in SQL Server 2005 and in SQL Server 2000 (271509)
K. Brian Kelley
@kbriankelley
November 17, 2007 at 10:14 pm
Many thanks. I will consult that article.
webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
November 18, 2007 at 8:59 am
Heh... Understood, WebRunner. Ran into similar problems in our production environment except a bit worse... our blocking resulted in an average of 640 deadlocks PER DAY with occasional spikes to 4,000 per day!
Brian beat me to it...
I built a proc at work that checks for blocking and it runs once a minute... ALL the time. Seems like a lot, but the constant monitoring has saved our butts more than once... No need to figure out if blocking is occurring, it's always checking and capturing blocking info both before, during, and after. We dump the output to a table (has a trigger on it to delete it's own rows that are more than 72 hours old. It's similar to the proc that Brian sited which should certainly do what you need.
Not sure how the DBA's did it, but they figured out a way to email and page themselves if any major blocking is occuring so they know to check the block log/table I made for them.
Let us know if you need more help on this blocking problem... it's a tough problem to resolve both in actuality and politically... lots of developers took things real personal when one of their procs was sited for rewrite. AppDev managers took it hard to... they were required to provide the necessary manpower to fix the offending procs immediately (which, of course, is the only way to do this). Since many of the procs were batch jobs but still written with RBAR on steroids (developers were Oracle/Java developers, which can handle RBAR), I actually had to teach some of the developers how to "think set based" before they could even begin rewrites... and, yes, they took that personal, too.
But it was all worth it...
--Jeff Moden
November 18, 2007 at 10:02 am
If this is a production system you may want to look at adding
set transaction isolation level read uncommitted
tracking down locking/contention issues can take longer than your customers patience. Setting up your stored procedures to perform dirty reads from the DB to prevent outages while you are doing this may save you with the customer.
Just be careful in doing this (don't use for financial data...) because it is a dirty read.
Paul Ross
November 18, 2007 at 12:20 pm
We've got Idera's Diagnostic Manager and found it helpful, especially the "Worst Performing SQL & SP" function and the "Most Frequent SQL & SP" function. They helped us track down the SPs and SQL in our 3rd party application that were bogging down. We've been able to tune them and get rid of the bottlenecks. It's stores the history, so you can pick a point in time & see what was running. For example, look at the longest running queries from 2am - 4am last Saturday.
November 18, 2007 at 4:02 pm
Thanks for everyone's advice.
About how much does Idera's Diagnostic Manager cost?
webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
November 18, 2007 at 4:27 pm
webrunner (11/18/2007)
Thanks for everyone's advice.About how much does Idera's Diagnostic Manager cost?
webrunner
I don't remember. They have prices listed on their website, and they will usually discount those prices somewhat if you haggle. You can get a free trial for a couple of weeks or maybe a month.
November 20, 2007 at 11:01 am
Thanks again, Jeff,
We think we spotted one long-running query that was causing problems, so that is good since I think the developer was able to control it not to run (somehow a web page indexing spider was causing the query to run when hitting a hidden link). We are still seeing blocking problems, now the emphasis has switched to the database migration.
I have one question about that. When we move the database, we will need to rewrite any cross-database queries, won't we? I can't think of any elegant way around that. For example, if a query on the original database is:
SELECT id, name
FROM db1.dbo.table1 t1 INNER JOIN db2.dbo.table2 t2
ON t1.id = t2.id
Then we migrate db2 to a new server and set up a linked server, srv1, to db1. Now this query should break, right?
SELECT id, name
FROM db1.dbo.table1 t1 INNER JOIN db2.dbo.table2 t2
ON t1.id = t2.id
And this is the correct query now, is that right?
SELECT id, name
FROM srv1.db1.dbo.table1 t1 INNER JOIN db2.dbo.table2 t2
ON t1.id = t2.id
I'm just wondering if there is any better way to do this, so I figured it was worth asking.
Thanks for any help!
webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
November 20, 2007 at 12:12 pm
I'm not clear on what's moving where, but you're right about having to specify the server name with linked servers.
You might want to ask this in a new thread to get fresh eyes.
November 20, 2007 at 12:52 pm
OK, thanks,
Yes, though, that is what I meant. The queries on the new server fail with this error:
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'table1'
I was just wondering if there is a way to link a server to avoid needing to re-code the queries, something that I was unaware of.
Thanks again,
webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
November 20, 2007 at 5:33 pm
Unfortunately, no. When you go the linked server route because you've got to cross servers, you're going to have to use the four part naming convention.
EDIT: Actually, create a database on the new server with the same name as the database you're linking to on the old server. Create views which have the same name as the table objects on the old server. Define those views as having SELECT statements using the 4 part naming convention.
K. Brian Kelley
@kbriankelley
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply