Category: SQL

SQL Blocking and Client Variable Purge

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.
10 comments | Posted by Daniel Short on Dec 11, 2009 at 1:50 PM | Categories: ColdFusion - SQL -

Convert IPs to Integers

More fun with IPs, this time converting them to integers.
0 comments | Posted by Daniel Short on Dec 2, 2009 at 1:07 PM | Categories: ColdFusion - SQL -

Convert IPs to Binary

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:
2 comments | Posted by Daniel Short on Dec 2, 2009 at 8:09 AM | Categories: ColdFusion - SQL -