More PASS Activities

October 30, 2009 at 12:48 pm (PASS) (, )

As if I needed more.

I’ll be sitting at the blogger table and blogging live (as live as I get) during the keynotes. It should be fun. As I said on Twitter, this means I get to pick on the cool kids. Life is good.

Thursday I’ll be doing a book signing with Apress between 12:30PM-1:00PM across from the Summit bookstore. I don’t have a clue which book we’re talking about since they’ll be supplying it. I hope it’s the Performance Tuning one, but it might be the Introduction one. Either way, that’s another place to track me down & say hello.

In addition to my keynote blogging, I’ll put up a summary for each day of the summit just like last year. It’s looking to be a great summit.

Permalink 3 Comments

I Started a Fight

October 28, 2009 at 2:14 pm (nHibernate) (, , )

I had no idea, but evidently I started a bit of a donny-brook. Cool!

Permalink 2 Comments

Snags With Profiler GUI

October 28, 2009 at 7:59 am (SQL Server 2005, SQL Server 2008, SQLServerPedia Syndication, Tools, TSQL) (, , , , )

Running the Profiler GUI against a production server is not something you should do. I’ve outlined my research into exactly why in the past. But I hit another little issue with the Profiler GUI as part of work I’m doing on a Microsoft PSS call (more on that in another post). We have a procedure on one of our systems that is erroring out, but only on a particular server and only when called from the application, not when it’s called, on the same server, from SQL Server Management Studio.

I needed to capture some trace events and do it quickly, so I decided to use the GUI, just this once. I put filters on it so that I would only collect certain data, and the database I was collecting from was not in active use, so I knew the only commands I’d be capturing were my own. I just captured SQL:BatchCompleted and SQL:BatchStarting. I needed to run the query one time to validate the behavior. Under these circumstances, with full knowledge of what the GUI can do to a production system, I figured I was ok.

I kicked off the trace through the GUI and ran my proc. Here’s what I saw:


This is not evidence of the problems outlined above. You know how TextData normally shows the query that is being run? In this case the query being run involves a rather large XML parameter being passed in as part of the query. This is so large that the Profiler GUI skipped over it and didn’t bother clogging  the memory of the system or my network with capturing it. Don’t panic though. If I run the exact same trace as a server side trace, I get the full text of the call in the TextData field. And yes, if you’re wondering, I’m pretty positive this is by design (although the only reference I can see in BOL is to events greater than 1gb, which we are not into here).

On more reason to avoid the Profiler GUI as a data collection mechanism.

Permalink 7 Comments

SQL Server Standard, Volume 6, Issue #1

October 27, 2009 at 5:11 pm (PASS, sql server standard, SQLServerPedia Syndication) (, , , , )

It’s alive! It’s alive!

That’s enough from Colin Clive.

It’ll be out for the Summit. SQL Server Standard lives again! Although, not quite in the same shape as it used to be. But hey, stitching stuff together out of dead tissue is messy work.

I want to thank our first author who had to suffer through quite a few growing pains and help us blaze a trail through the woods, Thomas LaRock. I want to thank my boss at PASS for all the support especially the time I started whining, Andy Warren. And there’s this other guy, who has helped just a ton in this effort in every way, and lead the technical edit team, Brad McGehee. We have a photo credit to Pat Write for the front. Craig Ellis has done a yeoman’s labor putting together all the layout & art stuff, especially when you consider he had me making artistic suggestions (gave my wife a laugh anyway). The technical editors on the first article are K. Brian Kelley, Jose Santiago Oyervides and Tim Mitchell. Finally I have to thank Kathy Blomstrom for all her hard work editing and laying out the final.

To all you guys, thanks for working so hard through this process. I have bad news though. We have to get another one together in just a couple of weeks.

I’m probably stealing a little bit of Andy’s thunder by announcing it here. I don’t think he’ll be too mad at me.

I hope the community appreciates all the hard work these people put into this. Each of them did a great job. If you see them at the PASS Summit, be sure to say thanks.

Permalink 2 Comments

PASS Summit One Week Away

October 23, 2009 at 9:15 am (PASS, SQLServerPedia Syndication)

It’s just a week and a couple of days before I’ll be hopping a plane for Seattle and the 2009 PASS Summit. I’m as excited about this one as I was my first summit, maybe more so. I’m going to get to see a bunch of friends that I only see once a year and hopefully meet in person people I’ve only interacted with online. A very large part of the PASS Summit is networking. I’d like to meet people who actually read my fumbling attempts at understanding how SQL Server works and how to best work with it. If you’d like to have a chat, there are a lot places to track me down:

  • Monday, most of the day I’ll be going to various PASS meetings, so if you’re a volunteer, look me up.
  • Monday, I’ll be at the networking training too. That should be useful.
  • Monday night, the reception, of course
  • Monday night, the SSC party, again, of course
  • Tuesday I’ll be doing two presentations, one in the morning and one in the afternoon (but don’t talk to me after the morning one, I have to get to my next assignment)
  • Tuesday I’ll be hosting a table (although I’ll get there a little late because of my presentation) at the MVP Birds of a Feather lunch.
  • Tuesday night I’ll be at a party or parties
  • Wednesday is kilt day at the summit. I’ll be one of the guys wearing a skirt.
  • Wednesday night I’m pretty sure I’ll be at the Solid Quality Mentors party, but I may make others
  • Thursday, I’m not sure, but I’ll bet something is going on. Maybe I’ll be able to make a seesion or two.
  • Thursday night, Friends of Red Gate and I wouldn’t miss it.
  • Friday, I’ll be around, attending some sessions
  • Friday night, another party that I wouldn’t miss.

Some of the parties are by invitation only, so track down someone you know from those organizations to get in (not me, I don’t have weight with anyone).

I’m also a square in the Twitter Bingo game, so there’s incentive to track me down and thump me until I give up my password.

I’m jazzed. The Summit is coming. See you there.

Permalink Leave a Comment


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,
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,
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

SQL Server Standard Update

October 15, 2009 at 7:43 pm (sql server standard) (, )

The first article should be out within a week or so (knock wood).

I hav a winner for the contest to help us pick the artwork for the cover. Leo Pasta. Congrats. Get in touch with me at: grantedd -at- so that I can send you your prize.

Permalink Leave a Comment

SNESSUG October Meeting

October 14, 2009 at 9:23 pm (PASS) (, )

The Southern New England SQL Server Users Group’s October meeting was a bit sparsely attended with 7 attendees. The sponsor for the night was ApexSQL. The presenation was by AJ Dharmapuri who spoke on using DMV’s.

Barbara Sampson, SNESSUG Treasurer, did a demo of ApexSQL’s SQLEdit. It’s a pretty powerful TSQL coding and scripting tool. There’s a lot of functionality that worked in a very snappy way during the demo and looked great. I may need to spend some time with their products to see if we can put them to work. As part of the sponsorship, we gave away a license for the Apex SQL Univeral Studio. Quite a prize.

I was very excited to see AJ’s presentation for a few reasons. First, because he presented on DMV’s, based on an article he wrote that will soon be published in SQL Server Central. Next, because AJ is presenting for the very first time (and it’s a shame he didn’t get a bigger audience). Finally, because assuming he likes it and works with it, then SNESSUG has another speaker (it’s hard to get people to speak in Rhode Island).

He was great! He broke down the discussion by identifying common situations, such as, what is running on the server right now. Based on the situation, AJ showed which DMV’s or DMF’s would help you answer the question or address the situation best. His knowledge was good, presentation skills were solid, he explained things very well and definitely said “uh” a lot less than I usually do. It was a useful bit of knowledge, well presented and well received. Congrats AJ, well done.

Permalink 1 Comment

Next page »