July 2010 - Posts

An interesting annecdote

A while ago I blogged about using the dmvs to identify lookups and scans on tables  http://sqlblogcasts.com/blogs/grumpyolddba/archive/2010/05/19/analysing-indexes-reducing-scans.aspx

Well continuing on from this I had discovered that two of the top scanning tables don't actually contain any data. After some investigations within the application database I discovered that one of these tables was designed to hold system lock out times; e.g. you populate the table with when the system will be unavailable and the application warns users of an impending outage. So far so good. Next a collaegue discovered that the data in this table was cached on the application tier so the level of access should be very low, we're actually looking at about 75,000 scans per day over the last 50 days, however what happens is that you cannot create an empty cache object so the cache keeps trying to populate, somewhere around once a second.

No worries then, we'll add old data, say a shutdown for last year, this will populate the cache object and the calls will be reduced. I raised a change request to test this and my colleague decided to test it on his own system first but despite his best efforts he couldn't produce a shutdown or warning message. After some debugging and searching through the application source code we discovered that the there was indeed a function calling the stored procedure and it was popualting the datareader, sadly the function was coded to return nothing, so here we had a piece of functionality which I was assured did do as it said on the tin, ( system shutdown ), but in reality was either never implemented or was disabled , we didn't examine every change to check but  at least 2006 seemed the last time it might have worked if it every did.

Agreed there is not going to be any measurable change in application performance but there has to be some satisfaction in the outcome. The dmvs are a very powerful addition to SQL Server and can be used in many different ways to diagnose "things", yes they came in with SQL 2005 but the application in question only made the leap from 2000 to 2008 this year so this type of analysis was unavailable to me.


I think I'm fitting the OLD in GrumpyOldDBA

I try to keep up with a posts on a number of blogs both SQL Server and Windows and it's interesting to see the same subjects being raised time after time in, especially, the SQL Blogs. Now I don't see this as bad, other than it's duplication, but what I realise is that there are constantly new users to SQL Server who don't have the benefit ( ? ) of having worked through several versions of SQL Server, from 6.0 for me.

This means I guess the same questions are endlessly asked resulting in generally the same answers, but not always! So does this mean that documentation and training courses don't really cover what I see in many cases as quite basic fundamentals? Maybe training is out of fashion and all you could ever want to know is available via your favourite search engine.

I rarely use BOL as I find it irksome to navigate and usually doesn't clearly cover or answer what I'm looking for. Just to be grumpy here I also find it extremely irritating when blogs or articles on web sites merely reproduce the content of BOL, usually the T-SQL with different wording, I usually find that any examples for a feature never actually use it like I want to - partitioning back in sql 2000 was a fine example where it seemed every writer assumed you'd only ever want to partition by date, usually year or month.

I used to answer questions on forums, back in SQL 2000 I was using quite large boxes with lots of memory ( for then ) and I remember seeming to constantly be answering the same questions about awe and the 3gb switch and how and what they did - and fighting against the incorrect answers who got it wrong every time.  The trouble with forums is that just like blogs others can pick up your posts/code and present it as their own - I recently found a word for word blog post for a well known MVP posted on another site -  it was somewhat obscure and a couple of years old.

However it does illustrate the problems of presenting information - my plan for www.grumpyolddba.co.uk was for me to be able to put all my working documents in one place in an orderly fashion, however it takes time to convert large word docs which contain lots of screen shots and code, it took me some time to find a ultility which allowed me to put correctly formatted T-SQL into this blog for example.

Ah well back to grindstone! I have some posts concerning some interesting aspects of tuning to come soon once I've changed all the client data names and such to obscure their data.

Posted by GrumpyOldDBA with 1 comment(s)
Filed under: