TraceTune now shows a graphical view of logical reads for each SQL statement in a trace file. The width of the colored bar in the screen capture below is the percentage of logical reads for that statement. The absolute number of reads is shown to the right. [https://billg.sqlteam.…
I’ve been using the online version of ClearTrace [http://www.tracetune.com/] more and more lately. When I get to a new client it’s just much easier to upload a trace file rather than install ClearTrace. That means I’ve finally been adding more features to it. The…
I wrote a small utility to script some key SQL Server configuration information. I had two goals for this utility: 1. Assist with disaster recovery preparation 2. Identify configuration changes I’ve released the application as open source through CodePlex. You can download it from CodePlex at the Script SQL…
One of the challenges I struggle with is keeping a good "to do" list of things I need to do on the various SQL Servers I support. I have servers that I don't visit on a regular basis so my situation may be different than many…
We have a requirement to build a test environment where certain tables get reset from production every night. These are mainly lookup tables. I played around with all kinds of fancy solutions and finally settled on a series of MERGE statements. And being lazy I didn’t want to write…
I wanted an easier way to identify queries I’d already looked at so I could skip them. I’ve been entering comments for each query as I review it. These comments typically fall into three categories: a change I made, no easy fix available or something needs to be…
When I get to a new client I usually find myself using the same set of scripts for maintenance and troubleshooting. These are all drop in solutions for various maintenance issues. Reindexing. I use Michelle Ufford’s (SQLFool) re-indexing script [http://sqlfool.com/2010/04/index-defrag-script-v4-0/]. I like that it…
I’m trying to make TraceTune [http://www.tracetune.com/] (and eventually ClearTrace) work the way I do. My typical query tuning session goes like this: 1. Run a trace and upload to TraceTune/ClearTrace 2. Tune the slowest queries 3. Goto 1 I might do this two or three…
I updated TraceTune [http://www.tracetune.com/] over the weekend. I increased the trace file upload size to 20MB. We’ve processed over half a million rows of trace data so far and I’m confident this won’t kill the server. I added average CPU and average disk reads…
I recently spent some time troubleshooting odd query plans and came to the conclusion that we needed better statistics. We’ve been running sp_updatestats but apparently it wasn’t sampling enough of the table to get us what we needed. I have a pretty limited window at night where…