Number2 Blog - myITforum

SQL Performance Tips - Avoid putting indexed columns inside functions in your WHERE clause by jnelson

THE PROBLEM When designing a query, it is incredibly important for performance reasons that you keep in mind what columns are indexed. The SQL engine will...

SMS Performance Tip - Reorganize the Add_Remove_Programs_DATA tables for speed by jnelson

DISCLAIMER! OK, BIG disclaimer here. This procedure is neither endorsed, nor supported by Microsoft and for small to middle size SMS deployments might...

SMS 2003 SP2+ - Tables named DWTbl%Del getting big or growing fast by jnelson

So, if you saw my article on tracking table size growth/shrinkage (or if you've been doing that yourself) you may have noticed that one or more of...

Track table growth (or shrinkage) with a SQL job, a table and a multistatement table-valued user defined function by jnelson

THE PROBLEM Sometimes your SMS infrastructure seems to be swimming right along and everything is going right and then something you didn't think too...

SCOM 2007 - OpsMgrHealth Service Stops unexpectedly with error %%2164195332 by jnelson

BACKGROUND This year we deployed SCOM 2007 to watch over our SMS infrastructure for server, DB, SMS, performance or other problems. It's been pretty useful for us so far (of course the SCOM console speed is a bit slow, but that's supposedly improved in SP1). Recently however, the "OpsMgr Health Service" has been stopping unexpectedly and not wanting to start back up without a reboot. SYMPTOMS In the SYSTEM event log we see the following error: Event Type: Error Event Source: Service Control Manager Event Category: None Event ID: 7023 Description: The OpsMgr Health Service service terminated with the following error: %%2164195332 ......

SMS/SCCM Query Performance Tips - Addressing objects with the schema name (.dbo) by jnelson

A commonly overlooked little performance tip in SQL is addressing your objects (like tables, views, functions, procedures, etc.) using the schema (aka...

Turning Rows into Columns using PIVOT by jnelson

I was looking at the ever-popular (but strangely absent this summer [:D]) Paul Thomsen's blog recently and I noticed this article entitled "Reporting on client version for multiple sites". His code does some cool stuff so I did what normal admins do and swiped his code for my own purposes. Then I did what any hard-core geek would do, I tried to think if there was any way I can improve on the code. After all, most really great products started out as good ideas that people have built upon over time. When I realized what he was doing, I thought I could come up with a way to get the desired results in a little different way and perhaps more efficiently. (I want to emphasise that Paul is one of the better engineers out there and my improving on his code is not meant to imply that he needs help with his SQL code, which he doesn't, it is intended to highlight that almost everything anyone writes, including everything I've ever written, can be improved upon and this gave me an opportunity to post about the SQL 2005 PIVOT statement and improve something at the same time). So in dissecting the code, I noticed that there were a number of subqueries that all went back to the same table, I saw a NOT IN, which from a SQL-performance standpoint is something I try to shy away from if I can do it another way, and I noticed that really he was manually pivoting on the sitecode (pivoting is going from rows to columns, unpivoting is going from columns to rows). At Wells Fargo, we're using SQL 2005 so I figured this was a perfect opportunity to use the newly introduced PIVOT command. (Note: Don't try this on SQL 2000 or earlier as there isn't a PIVOT statement) So here's the original code from Paul Thomsen (thanks Paul!) with our important site codes replacing his and I removed the case statement for the "v3 SP2 escrow" type stuff because it doesn't really mean anything more than the version number does to us: ...