Category: SQL
We've had continual issues with our ColdFusion applications locking up on us. I've spent the last week cleaning out huge tables, creating indexes, and tuning queries, but could never seem to track down what was causing the issue. Today I caught it in the act... As the the applications locked up, I ran the following query in SSMS:
SELECT
st.text
, r.blocking_session_id
, r.session_id, r.status
, r.command
, r.cpu_time
, r.total_elapsed_time
FROM
sys.dm_exec_requests r
CROSS APPLY
sys.dm_exec_sql_text(sql_handle) AS st
This showed me what queries were getting blocked, and who was doing the blocking. It turns out that it was ColdFusion's Client Variable purging that was causing the blocking. This is the query that was running:
DELETE FROM
CDATA WHERE CDATA.cfid in
(SELECT CGLOBAL.cfid
FROM CGLOBAL
WHERE CGLOBAL.lvisit < {date} )
DELETE FROM CGLOBAL WHERE CGLOBAL.lvisit < {date}
Unfortunately, there are no lock hints anywhere on the query. With 7,500 records to delete in a table with more than 30,000,000 records, the table was getting locked for more than 2 minutes, a complete disaster for a production application dependent on client variables.
So I went to work creating a new query that will delete the client variables at a much more reasonable pace, with only as much locking as is necessary to keep things humming along smoothly. The new query looks like this:
SET NOCOUNT ON
DECLARE @cfid char(64), @rowsaffected int, @rowsdeleted int;
SET @rowsaffected = 0;
SET @rowsdeleted = 1;
WHILE @rowsdeleted > 0 AND @rowsaffected < 100
BEGIN
BEGIN TRANSACTION
SET @cfid = (
SELECT TOP 1 CGLOBAL.cfid
FROM CGLOBAL (NOLOCK)
WHERE CGLOBAL.lvisit < DATEADD(d, -30, GETDATE())
ORDER BY CGLOBAL.lvisit
)
DELETE FROM CDATA WITH (ROWLOCK) WHERE CDATA.cfid = @cfid
DELETE FROM CGLOBAL WITH (ROWLOCK) WHERE CGLOBAL.cfid = @cfid
SET @rowsdeleted = (SELECT @@ROWCOUNT);
SET @rowsaffected = @rowsaffected + 1;
COMMIT
END
PRINT CAST(@rowsaffected AS varchar) + ' client records deleted'
The query will loop through and delete one record at a time, until it hits the limit of rows affected specified. This is set up as a scheduled task in CF so that I know exactly when it will happen, and can watch for issues if there is a failure using existing global error catching.
More fun with IPs, this time converting them to integers.
I'm working on some SQL to search for data based on IPs and IP Ranges. Doing this with character data in the database is horrendous. When searching through millions of records using JOINs with LIKE comparisons, the performance is completely unacceptable. So I'm working on converting the IP addresses to Binary format to do some (hopefully) faster searching.
I have all of the SQL code to do this, which I'll post a little later with some performance benchmarks, but first some ColdFusion code to deal with display and conversion of IP addresses to and from binary. If you use the code, please let me know if it works out for you, and look for a further post on making use of this in SQL Server.
So, without further ado, here's the function: