I put together a presentation on Dynamic Management Views (DMVs) a few months back and one of queries I put together for that presentation was a version of sp_who2 that provided similar results but used DMVs instead. The query has most of the same columns as sp_who2 with some additional items that I found useful … Continue reading DMV Version of sp_who2
Thought I'd share a cool trick I picked up a while back with the OVER() clause. Typically you’d use this clause with the PARTITION BY or ORDER BY arguments with the ranking functions released with SQL Server 2005. The OVER() clause though can also be used to do aggregations with a SELECT statement without a … Continue reading The OVER() Clause
Updated content at - Really Search Cache For Execution Plans Sometimes when I am working with clients I need to take a look at execution plans for different stored procedures. Rather than digging around with SQL Profiler or executing the stored procedures with what I think might be the parameters I like to '”return to the … Continue reading Search Cache For Execution Plans
A good performance tuning trick in SQL Server 2008 from the SQL Programmability team and a response reminding everyone that it isn't a silver bullet from Joe Sack. While cool, this item doesn't count towards my blogging goal for the year. Too bad, still a cool thing to play with.
I'm generally a convert to the congragation that all tables should have clustered indexes. It makes sense, helps performance, and in most cases is the best starting point for a tables. If you aren't part of the community of the faithful on clustered indexes, please read Kimberly Trip's blog on clustered indexes. There are other … Continue reading Find The Heap Indexes
I've been looking into the AUTO_UPDATE_STATISTICS_ASYNC option for SQL Server lately and found a couple decent articles on there on it. AUTO_UPDATE_STATISTICS_ASYNC SQL Server Database Configuration Asynchronous Update Statistics Preventing the blocking of queries in OLTP environment from statastic updates seems like a good thing.
I've was reviewing wait stats the other day and kept finding this wait stat peaking out the other wait stats by a few hundred milliseconds. Oh my! Should I care or not... well long story short is that if all of the CLR assemblies are created in safe mode there is no problem. Or at … Continue reading CLR_AUTO_EVENT Wait Stat
Deadlocks are a not so wonderful unnaturally occurring event that all DBAs will eventually have the pleasure to take a look at. Since deadlocks are time senisitive it is important that at the time of the deadlock the correct mechanisms are in place to capture the detail of the deadlock in the SQL Server error … Continue reading Deadlock Resources
I was working through some performance issues on a "fun" table. A bit of half a billion rows and with some apparent I/O issues as the entire table is being read by some of the clients services to create an extract. In working through some of the performance bottlenecks, I found the following white paper … Continue reading Troubleshooting Performance Problems in SQL Server 2005
A quick link to a blog I read today on the default trace and determining what is in it. It's cool that we can get to all of this information in SS2005
Strate SQL 