Buy vs. Build

May 28, 2010 at 8:52 am (PowerShell, SCOM) (, , )

It’s the classic question faced by everyone in Information Services. I know how to do this and I could build software to do it, but I’m a lazy b_____d so I’d rather just pick up a piece of software that does it for me. I love working for large companies because I can usually get them to purchase stuff so that I can loll around stuffing my face all day instead of doing actual work. Unfortunately, not everyone can afford to pick up Microsoft’s Operations Manager or Idera’s Diagnostic Manager. But you still need to monitor your servers. With buy eliminated, that leaves build.

Which, is where this excellent blog post by Laerte Junior comes in. He lays out how to build a wrapper around calls to get Performan Counter information using PowerShell. It’s a pretty slick and worth a read. Becuase the thing is, when you need to build your monitoring products, you want to use a language that you know. Since everyone is learning Powershell (right?) this provides a good foundation for beginning your monitoring project.

Permalink 4 Comments

Slick New Software

May 24, 2010 at 12:52 pm (SQL Server 2005, SQL Server 2008) (, , , )

I’m lazy. And frankly, I’m not ashamed to admit it. When a software comes along that can do the work for me, even work I can do just fine on my own, I’m interested. When that software is inexpensive, even better. When it’s free…

I just got word that Confio is putting together a free version of Ignite, their performance monitoring software. It’s basically going to be the current view of performance and not have all the historical tracking and nifty trend reports. Yeah, it’s a tease, but it’s a tease on a product that focuses on monitoring wait states, something you should be doing. Just remember, reference above, I’m lazy.

The thing is, they need some people to help them beta test. Here’s the word I received:

If you would like to participate in the beta test, you can download and get the full copy at

As a measure of our appreciation, we will send you a $5 Starbucks card once you install and provide feedback at our IgniteFree forum

IgniteFree will be the only free response time based performance tool.  It uses the same agentless, low load monitoring technique as Ignite 8.  The difference is that IgniteFree is focused on real-time monitoring, showing only the last hour of response time, server resources, query and session statistics.   Longer history and advanced features are not included in the freeware.  

Do some beta testing on a slick piece of software, help make it better, and get a cup of coffee (I’m assuming that’s all that $5 at Starbucks will get you). What’s not to like?

Permalink 2 Comments

Recompiles and Constant Learning

May 11, 2010 at 3:31 pm (SQLServerPedia Syndication, TSQL) (, , )

When faced with a procedure that looks like this:

CREATE PROCEDURE dbo.TestProc (@TestValue INT)
IF @TestValue = 1
FROM Sales.SalesOrderHeader AS soh
JOIN Sales.SalesOrderDetail AS sod
ON soh.SalesOrderID = sod.SalesOrderID
WHERE soh.SalesOrderID = @TestValue
FROM Production.Product AS p
JOIN Production.ProductDocument AS pd
ON p.ProductID = pd.ProductID
WHERE p.ProductID = @TestValue

I used to suggest creating a wrapper procedure in order to avoid the recompiles that occur when the different paths through the IF statement are taken by the optimizer. I mentioned that recently on a post over at SQL Server Central. Gail Shaw (blog | twitter) asked me why I thought there would be a recompile. She said that the optimizer took the query as a whole and created plans for it. I never seem to learn my lesson, so I suggested that she might be wrong about that. Gail being who she is, immediately went and made up a quick little test with simple queries. Sure enough, no recompiles. Ah, but I figured she was benefiting from trivial plans or something, so I created the procedure above to test the theory out. Each query, while relatively simple, goes through a full optimization process, so no trivial plans involved…

Short answer, Gail’s right and I was wrong. I don’t know where I got the idea that this type of query caused recompiles. I have now tested it on 2000, 2005 and 2008, no recompiles anywhere. I also cleared the cache, ran the procedure once, and then checked the cache using this query:

DBCC freeproccache ;

EXEC dbo.TestProc

@TestValue = 1 ;

SELECT deqp.query_plan

FROM sys.dm_exec_query_stats AS deqs

CROSS APPLY sys.dm_exec_query_plan(deqs.plan_handle) AS deqp

This is what I saw:


I swear, I’d never seen a plan like this before. I guess partly because I tend to only look at the actual execution plan rather than the estimated plan. It’s pretty clear that the optimizer just walked through and determined that there was more than one query involved and built a plan for them, including the conditional IF statement. If I’d just bothered to look at the estimated plan one time, I could have avoided my error of understanding.

To all those I’ve suggested wrapper procs in order to avoid recompiles… oops, sorry.

Permalink 10 Comments