Characters

October 21, 2009 at 2:42 pm (SCOM, SQL Server 2005, SQL Server 2008, SQLServerPedia Syndication, TSQL) (, , , , )

No, I’m not talking about a Dickens novel. I’m talking about the number of characters in a string. I had a painful time recently because of the word “characters.” 

If you take a look at the dynamic management view sys.dm_exec_sql_text you can get the queries that have been run on your system that are still in the cache. It’s a great utility. Better still, you can get specific statements from the code that are actively running through sys.dm_exec_requests or ones that have run through sys.dm_exec_query_stats. To do this is very simple. Each of these DMV’s has a pair of columns, statement_start_offset and statement_end_offset. These columns, and I’m quoting directly from books online measure the “number of character” offset from the beginning of the SQL string and from the end of the SQL string. Using these values you can retrieve an individual statement out of a stored procedure that has multiple statements.

But… Here’s where things get tricky. Try this on your machine:

SELECT SUBSTRING(dest.text, (der.statement_start_offset ) + 1,
(der.statement_end_offset - der.statement_start_offset) + 1)
,LEN(dest.text) AS CharLength,
der.statement_start_offset,
der.statement_end_offset
FROM sys.dm_exec_query_stats AS der
CROSS APPLY sys.dm_exec_sql_text(der.sql_handle) AS dest
WHERE der.statement_end_offset > -1

You might get an error or you might get a bunch of really odd looking statements in the first column, starting part way into TSQL and cutting off after they’re done or before they’re over. It’ll look odd. But what’s the deal? The SUBSTRING function should work. Logically it’s configured correctly. Here’s the problem.

The [text] column in sys.dm_exec_sql_text is of the datatype NVARCHAR(MAX). Unicode. If you look at the length of the text, it’ll tell you exactly how many characters you see in the string that called to your server. But, the statement_start_offset and statement_end_offset are measuring something different. They’re not measuring characters, they’re measuring unicode characters. Try this query instead:

SELECT SUBSTRING(dest.text, (der.statement_start_offset / 2) + 1,
(der.statement_end_offset - der.statement_start_offset) / 2+ 1),
LEN(dest.text) AS CharLength,
DATALENGTH(dest.text) AS DLength,
DATALENGTH(dest.text) / 2 AS HalfDLength,
der.statement_start_offset,
der.statement_end_offset
FROM sys.dm_exec_query_stats AS der
CROSS APPLY sys.dm_exec_sql_text(der.sql_handle) AS dest
WHERE der.statement_end_offset > -1

You can see that the character length is, whatever it’s supposed to be, but the DATALENGTH is twice that much. Unicode, as we all know, includes a byte to identify the character set. That’s included in the character count in statement_start_offset and statement_end_offset.  You need to take that into account when dealing with these “characters.”

Permalink 1 Comment

Free Training RIGHT NOW

October 21, 2009 at 8:03 am (SQL Server 2005, SQL Server 2008, TSQL)

Quest Connect 2009 is occurring even as I type this. Get on over there if you’re interested in some free training. I recorded a session on understanding execution plans. But even better, there are live sessions with some great people. Stop reading this, click the link, get yourself some free training.

Permalink Leave a Comment

Birds Of a Feather Lunch

October 21, 2009 at 6:30 am (PASS) (, , , )

The PASS Summit agenda is shaping up and it’s already looking to be much busier than last year. The latest is the Birds of a Feather lunch. It’s lunch with an MVP. At least 50 different MVP’s will be hosting a table each. At each table a topic of discussion will be hosted by the MVP present. It should be a lot fun. It’ll be a great way to meet people and share war stories, tips, approaches, what ever. The list of topics and the MVP’s leading are available here at Mike Walsh’s blog.

I’ll be hosting a table on Team Development. I crack jokes about beating up developers, but really I see them as partners and teammates. We’re all working towards a common goal, delivering the product, whatever it is, in a timely and efficient manner. If you’re interested in talking about ways to improve teamwork or sharing horror stories about failed teams, please stop by and chat for a bit.

Permalink Leave a Comment