Most requests we can find a way to solve easily, sql server 2005 finally began to give us great insight into what was happenning with DMV's, 2008 expanded on this....  But every so often those requests come along that I still can't solve.

Application team is requesting to know the execution count of a particular stored procedure by hour, from 3 days ago !

I have no solution.

It may be possible to "tap" the default trace and get a count of the procedure and roll it into a table, which may help going forward, but not for the past.  The volume of data generated could be quite large, as this particular server is performing 5,000 tps on average.

It was explained to me that they are trying to figure out this count as it represents a session timeout from the login page, and that specific counts from the iis log on the hits to the logon page do not accurately reflect those that had a session timeout vs. those that logged on again, hence the desire to know the counts by hour of the stored procedure used to login.

No help from the DBA on that one, any suggestions are welcomed...

Never enough beer, never enough time.

Now is the time to start thinking about brewing for spring and summer.

While not traditional spring or summer beers, i had to have some Animal Stout and Dust Bunny Ale.

Easy to make and quick to brew, should be ready in 4-6 weeks.

http://www.lifeasbob.com/HomeBrew/DustBunny.aspx 

http://www.lifeasbob.com/HomeBrew/AnimalStout.aspx 

Really titled, what else can go wrong...

Recently working a nice light wheat beer for spring.   Nice...until my mash efficiency was off by a too high temperature, 170 degrees, work it down by adding in cooler water, but over-all effect was noticeable.  I increase the fermentables by working in some honey and brown sugar, thinking I'd recovered from the problem, i move on to cooling the wort.

I don't turn my back for a minute, and the 32 quart pot (8 gallons for the mathmatically challenged), now has about 6.5 gallons of wort, the wort chiller sprung a leak (hence the working title of this brew).  Not wanting un-sterilized water in the wort or increased volume of water in an already light beer, I return the wort for another boil.  Removing 1.5 gallons of water takes quite a bit of time and produce a large volume of steam.....

Steam that is vented out of the kitchen via the Microwave.....hmmm....kid needs hotdog, ever see a Microwave catch fire ?  Now my "Sprung a leak Wheat" has ruined a $250 microwave.  Finally I have the beer at an acceptable volume, though it is still weak.

Tasting the flat beer at bottling time reveals a definetly light taste, my first 3.2 beer!

On the bright side, the wife finally relents on the budget and purchases the outdoor beer making equipment ($300), as the cost of replacing the microwave on regular basis is not cost-effective !

Oh yeah, i'll update on the finished product, i'm hoping with carbonation the beer will be tasty.

Trouble shooting performance issues is one of the real differences between an operational DBA (what we used to call Jr. DBA's) and a Sr. DBA.   

Even with all the new tools, there is always that support call....It's been working fine for the past 5 months, and today the application is performing poorly, and of course it's a database issue.  At this point i'm usually biting my lip and silently protesting, but often they are right, so it's time to dig into it.

What's the error ?  Answer:  We don't know

What's the issue ? Answer:  We don't know, but it's slow.

These issues are particularly troublesome.  Poorly written error handlers in the application, code developed by off-site vendors that support personnel don't understand, we've all seen the issues...AND now it's your job to fix it!

We have so many more tools now with SQL Server 2005 and 2008, that it becomes important to know which tool to use when, so the problem can be solved quickly.  I even have a PDF document, "Troubleshooting SQL Server 2005/2008 Query Performance/Scalability Issues" that we used our plotter printer on 3x4 paper just to help.

I start in with profiler, as I want to try and see if there are any client side errors, since it seems that the applicaiton is not performing any error handling or trapping any errors.  Sure enough we find an error on violation of a unique index, PUNT, back to the application team for more analysis; but why after 5 months would this just now come up (of course there have been no changes.) 

We finally find an issue with a stored procedure that seems to be hanging or timing out and then progressing to the next step in the application causing the unique index error, since we aren't trapping for errors on the client side and profiler can't find client side time-outs, it was particulary bothersome to find.  Even the procedure seems ok when executed through management studio and returns in sub-seconds, but something happens when the application runs it (still haven't figured this out).

Finally determine there is a degree of parallelism issue (DOP), which we found through analyzing the wait types.  The wait types was not on our first list of items to check.  Changing the DOP from 0 to 2, immediatly resolved the issue and dropped the CPU from 40% to 15%.  Utlimately some indexing would help the query, but we can't build indexes on the fly as the load is so heavy that even a table lock for 10-15 seconds would generate 100's of support calls; DOP can be done on the fly with little effect.

Analysis of the query plans shows that as the data grew the DOP changed the query plan and began to cause issues with a wait type of CXPACKET. 

This is why we still need real DBA's.