Dec 11 2009
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.
Dec 2 2009
More fun with IPs, this time converting them to integers.
<cffunction name="IPtoInt" output="false" returntype="struct"
hint="Returns a struct with two nodes<br />
Start: The starting IP address for the range specified<br />
End: The last IP address in the range
Range: Number of IP Addresses in the range"
>
<cfargument name="IPAddress" type="string" required="true" hint="" />
<cfset var LOCAL = StructNew() />
<cfset LOCAL.retVal = StructNew() />
<cfset LOCAL.retVal.range = 0 />
<cfif ListLen(Arguments.IPAddress, ".") LT 4>
<cfset LOCAL.retVal.range = 255^(4-ListLen(Arguments.IPAddress, ".")) />
<cfloop from="1" to="#4-ListLen(Arguments.IPAddress, ".")#" index="LOCAL.i">
<cfset Arguments.IPAddress &= ".0" />
</cfloop>
</cfif>
<cfset LOCAL.Octets = ListToArray(Arguments.IPAddress, ".") />
<cfloop array="#LOCAL.Octets#" index="LOCAL.octet">
<cfif LOCAL.octet LT 0 OR LOCAL.octet GT 255>
<cfthrow
type="exception"
message="Invalid Octet"
detail="The octet '#LOCAL.octet#' is outside acceptable range. Octets must between 0 and 255." />
</cfif>
</cfloop>
<cfif LOCAL.Octets[1] LT 128>
<cfset LOCAL.base = LOCAL.Octets[1] * 16777216 />
<cfelse>
<cfset LOCAL.base = -(256 - LOCAL.Octets[1]) * 16777216 />
</cfif>
<cfset LOCAL.retVal.Start = LOCAL.base + (LOCAL.Octets[2] * 65536) + (LOCAL.Octets[3] * 256) + (LOCAL.Octets[4]) />
<cfset LOCAL.retVal.End = LOCAL.retVal.Start + LOCAL.retVal.Range />
<cfreturn LOCAL.retVal />
</cffunction>
Dec 2 2009
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:
<cffunction name="ConvertIP"
output="false"
returntype="any"
hint="Converts an IP to either Binary or dotted decimal notation
based on the format of the IPAddress argument">
<cfargument
name="IPAddress"
type="any"
required="true"
hint="The IP Address to be converted.
If the supplied value is binary, it will be converted to dotted decimal.
If the supplied value is dotted decimal, it will be conerted to binary." />
<cfset var LOCAL = StructNew() />
<cfset LOCAL.retVal = "" />
<cfif IsBinary(Arguments.IPAddress)>
<cftry>
<cfset LOCAL.hexIP = BinaryEncode(Arguments.IPAddress, "hex") />
<cfset LOCAL.retVal =
InputBaseN(Mid(LOCAL.hexIP, 1, 2), 16) & "."
& InputBaseN(Mid(LOCAL.hexIP, 3, 2), 16) & "."
& InputBaseN(Mid(LOCAL.hexIP, 5, 2), 16) & "."
& InputBaseN(Mid(LOCAL.hexIP, 7, 2), 16)
/ >
<cfcatch>
<cfthrow
type="exception"
message="Invalid Binary IP format"
detail="The binary data supplied could not be converted to an IP.
Please verify that the data is formatted correctly." />
</cfcatch>
</cftry>
<cfelse>
<cfif ListLen(Arguments.IPAddress, ".") NEQ 4>
<cfthrow type="exception"
message="Invalid IP Address Format"
detail="The IP address '#Arguments.IPAddress#' is not a valid dotted decimal IP address." />
</cfif>
<cfloop list="#Arguments.IPAddress#" delimiters="." index="LOCAL.octet">
<cfset LOCAL.RetVal &= Right(0 & FormatBaseN(LOCAL.octet, 16), 2) />
</cfloop>
<cfset LOCAL.retVal = BinaryDecode(LOCAL.retVal, "hex") />
</cfif>
<cfreturn LOCAL.retVal />
</cffunction>
10-8-2009
10-8-2009
10-5-2009
9-29-2009
9-23-2009