I got a question recently about a panel discussion on Database Development Disasters at SQL in the City Streamed. I had framed a question as, “how fast should development go without load or performance testing?”
It’s often useful to be able to run a bunch of stored procedures in the background over a period of time against a test instance.
I am a fangirl of Adam Machanic’s free sp_whoisactive stored procedure.
One of the great things about sp_whoisactive is that it’s very quick to generate a table to collect the data you need, then run the procedure in a loop with a wait statement to regularly collect the data you want over an interval.
With every new year I think a little bit about time and dates. This posts looks a little more at that in TSQL.
In A Previous Installment… our heroine (that’s me) rediscovered CTEs, specifically in the recursive style. That was in my post “Filling in Data Potholes with Recursive CTEs.”
Yesterday was day 1 of SQL PASS 2009. I am attending a variety of sessions on execution plans this year, and along the way I heard three very different opinions yesterday on managing the procedure cache in presentations.
Sometimes tempdb is filling up. Sometimes I just want to monitor the amount of tempdb and check out execution plans of heavy tempdb users while watching performance on a server.
I often need to find a query plan in the cache for a process that has run long overnight. Typically I’ll be able to figure out from our logging some of the tables involved in the query. Sometimes I will have most of the executing text but won’t know exactly what dates or reference points were included.
As an add on to my last post, here is what I currently do use to track index usage. This shows usage, columns in the index, and index size on disk. The size can be quite useful to know when evaluating how much an index is worth– typically if this index is large then you’re paying a fair amount on the inserts.
It can be pretty difficult to manage statistics in data warehouses, or even OLTP databases that have very large tables.
I am going to post my monstrously big index query.
Why? Because it’s AWESOME. No really, it actually is awesome. At least, if you like that sort of thing. I use some variant of this almost daily, and I tweak it fairly regularly to suit the needs of whatever I’m working on. So it’s a work in progress, but I find it constantly valuable.