July, 2010 | Michael J. Swart

July 29, 2010

Tricks with User Settable Perfmon Counters

Filed under: SQLServerPedia Syndication,Technical Articles — Tags: "sql server", perfmon — Michael J. Swart @ 3:52 pm

So there’s this underrated SQL Server feature called User Settable Counters and they help SQL Server developers create our own custom perfmon counters.

You use them by using syntax like the following:

EXEC sp_user_counter1 @newvalue

This updates a counter than can be tracked using windows performance monitor etc…

Trick 1

Unfortunately this is not a counter that can be incremented. You have to know the new value to set. It would be great if we had stored procedures like sp_increment_counterX so that we could simply increment the existing value of the counter by any given value. Well ask and you shall receive!

USE master
GO
 
CREATE PROCEDURE sp_increment_counter1
	@value INT
AS
 
	DECLARE @newValue INT
	SELECT @newValue = @value + cntr_value
	FROM sys.dm_os_performance_counters
	WHERE instance_name = 'User counter 1'
	and [object_name] like '%User Settable%'
	and counter_name = 'Query'
 
	EXEC sp_user_counter1 @newValue
GO

I like this stored procedure because it’s very light weight:

The ~~cpu~~/IO/memory is negligible. Update Feb 8, 2011: Woah! On a performance test, this sproc is seen to take a lot of cpu!
It doesn’t take or hold any significant locks.

Trick 2

If you have a lot of spare time, you can the commands sp_user_counter combined with WAITFOR to make some pretty pictures of your favorite skyline!

Like the Toronto Skyline

Based on this this photo from BriYYZ (at Flickr):

Toronto skyline

-- Comments (7)

July 22, 2010

Worst error message ever…

Filed under: SQLServerPedia Syndication,Technical Articles — Michael J. Swart @ 1:14 pm

Many of you are familiar with the following error message:

.Net SqlClient Data Provider: Msg 233, Level 20, State 0, Line 0
A transport-level error has occurred when sending the request to the
server. (provider: Shared Memory Provider, error: 0 – No process is on
the other end of the pipe.)

The reason it’s a sucky error message is that it doesn’t do a good job of explaining what happened and it doesn’t suggest any actions to take.

Here’s what happened: You used to have an open connection to a server but you don’t any more. It was closed or killed from the server side. Maybe the server rebooted (or failed-over to another cluster node). You’re talking into one tin can, but nobody’s on the tin can on the other end of the string.

Annoying, but not a problem… open a new connection and try again. If you see this error message from inside SQL Server Management Studio, just hit F5 again (or Alt-X or Ctrl-E depending on preference).

Aaron Bertrand opened this connect issue which indicates that in a future version, Management Studio will retry automatically.

-- Comments (3)

July 12, 2010

Learning Objectives vs. SQL Server

Filed under: SQLServerPedia Syndication,Technical Articles — Tags: "sql server", #TSQL2sDay, learning, learning objectives — Michael J. Swart @ 9:00 pm

Takeaway: Choose learning activities that increase understanding and not just knowledge.

So It’s T-SQL Tuesday time again. This month it’s hosted by Robert Davis and the topic is Learning. It’s a topic of particular interest to me. I work in a field that writes software for education and now I’m writing about education on software. Here are the ways I learn (BTW, The methods are ordered from shallowest to deepest in terms of understanding)

Knowledge: (Ability to Remember)

Attending Conference Sessions
Watching webinars
Reading (BOL/Books/Blogs/Magazines)

Comprehension: (Ability to Understand)

All of the above… plus:

Getting help from StackOverflow and #sqlhelp
Conference Sessions (Q&A time). I mentioned conference sessions above, but here, I’m calling out the Q&A time.
User Group Discussions

Application: (Ability to Use)

All of the above… plus:

Troubleshooting: The best knowledge and understanding comes from troubleshooting, especially in crisis situations. This hard-fought knowledge doesn’t get forgotten.
Exploring a new feature installed on a machine.
Talking to consultants or mentors: Don’t underestimate this. I often felt like the SQL Server clinic (drop-in consulting provided by Microsoft’s CSS team) at the pass summit were underused.
Hands-on Labs.
Receiving code reviews.

Analysis: (Ability to Evaluate/Create)

With enough experience, you reach Nirvana*. You’ll know you’ve arrived if you can:

Answer questions from StackOverflow and #sqlhelp
Conduct code reviews
Give Talks/Sessions/Workshops on a topic

* Nirvana comes from Seattle. Coincidence?

But why should you care?

It’s useful to know which methods are most effective. Some lessons we could take from above is that it’s more effective to learn by doing than to attend training. Which kind of makes sense. Being thrown in the deep-end is often more effective swimming instruction than to read about swimming.

And as SQL Server professionals we’ve already got a leg up on academia! Our motivations for learning are often more immediate and pressing than “gotta study for the final”.

It’s my hope that by recognizing what kind of learning objectives you could achieve with each approach, you can make better choices about learning like:

Starting in support is an awesome way to shorten the learning curve.
The Q&A part of sessions stick in my head more than any other (especially if I asked the question).
Have management studio open (or BIDS or whatever) when reading blog articles. Believe me when reading Brad Shulz‘s blog, his examples sink in a lot better this way.
When way out of your depth. Consider hiring a consultant: It’s fixed cost, you get your problem solved, and you can learn something too.

-- Comments (1)