06Apr 2010 by Grant Fritchey 2 Comments

Confio Ignite: First Impressions

SQL Server
One of the national sponsors for SQL Satuday is Confio Software. Just because they sponsor SQL Saturday, it's worth checking out their product, but they also host the speaker's dinner at these events. So, having eaten off their dime, I felt obligated to take a look. I'm glad I did. Confio Ignite is a monitoring software that keeps real-time and historical track of the performance of your SQL Server (and Oracle and DB2) database servers. It's focus is on wait states and queues, a very common method for troubleshooting performance. You can get a trial download from their web site to run for a couple of weeks yourself. Everyone's first impression of a software, after the web site of course, is when go to do the install. The install routine for Ignite was very…
Read More
18Mar 2010 by Grant Fritchey 5 Comments

Undocumented Virtual Column: %%lockres%

SQL Server, T-SQL
One of my development teams needed a mechanism for identifying the value of a key that was part of a lock (don't ask). I'd never tried doing that before. Obviously if you hit the DMV sys.dm_tran_locks you can see the hash of the key in the resource_description column. But how to pull the value back. After some research, I first found this excellent article by the late, great, Ken Henderson (I really wish he was still around). The article outlined, among other things, the use of an undocumented "virtual" column called %%lockres%%. Some more searching then uncovered this great article by James Rowland-Jones, AKA Claypole. He described how, in a very high volume system, he used %%lockres%% to identify the source of a deadlock as the internal mechanisms that SQL Server uses to manage locks, the hash…
Read More
15Mar 2010 by Grant Fritchey 1 Comment

SNESSUG March Meeting

Misc, SQL Server
Aaron Bertrand showed up to teach us tips and tricks for SQL Server Management Studio. We had to move our meeting night because of a conflict at our wonderful host, New England Tech. But we still had 12 people show up. For SNESSUG, that was a good turnout. I gave away some swag that I had received from Microsoft and some stuff that we had purchased. Bribary works (at least that's my theory, so feel free to bribe me, whenever). Aaron's presentation was great. He's just showing nothing but meat. There's no fluff. He's just showing a series of tips & tricks in SSMS and explains why you want to use them. First revelation, -nosplash has no effect whatsoever on load time. He called it a placebo. It just kept…
Read More
25Jan 2010 by Grant Fritchey 3 Comments

A Call to Arms

SQL Server, T-SQL
Phil Factor's most recent guest editorial over at SQL Server Central has, to a degree, pointed out that the emporer's spiffy new outfit... well, it's not exactly there. That's why he looks so nekkid. But seriously, the very idea of naming objects inside of the database with Hungarian-style notation really should end. Phil's right. There's absolutely no reason why you should name a unique index ixuTableName when UniqueTableName or TableNameUnique would do the job just as well and not be nearly as obscure. I confess to using this type of naming convention all the time, but I'm realizing that I don't need it and it's largely just habit. There are possible exceptions, for instance you want to group all lookup tables in your database so you name them lkTableName, but…
Read More
21Jan 2010 by Grant Fritchey 2 Comments

PowerShell Script for Creating Indexes

PowerShell, SQL Server, T-SQL
I needed to create an identical index on a bunch of tables within one of my projects (yes, I know this is problematic on multiple levels and I'm working on that too). Rather than sitting around typing this up, I decided to use PowerShell to do the work for me. I'm still very much learning how to do things in PowerShell so this took me almost as long as it would have to type them up, but now I know more than I did. Having gone through the pain of trying to find a good example on the web that did exactly what I wanted (they're out there, just hard to find), I decided I'd add this one in so the next person had at least one more source of…
Read More
13Jan 2010 by Grant Fritchey 6 Comments

Connections Sessions Evals

SQL Server, T-SQL
I've kind of been embarassed to post these despite the fact that I received them a couple of weeks ago. Overall, I'd say they're very good, and I'm quite proud of them, but one comment still has me upset. Anyway, here we go: DMV's For Performance Tuning (same session as PASS): 7 responses Q1. Speaker's knowledge of topic Your average score for this session: 4.0 Highest score (all SQL speakers for this question): 4.0 Mean average score (all SQL speakers for this question): 3.74 Lowest score (all SQL speakers for this question): 3.0 Q2. Speaker's presentation skills Your average score for this session: 3.86 Highest score (all SQL speakers for this question): 4.0 Mean average score (all SQL speakers for this question): 3.47 Lowest score (all SQL speakers for this…
Read More
12Jan 2010 by Grant Fritchey 4 Comments

TSQL Tuesday #2

SQL Server, T-SQL
I've wracked my brain for some bit of puzzle that I could present as part of TSQL Tuesday #2 and I finally came up with a bit of something. When you're looking at an execution plan for a query, you know that this represents SQL Servers best attempt at a good execution plan. But, it may not represent the best possible plan. Or, it could be the only possible plan. The puzzle is, how do you know what you're looking at? Is this a trivial plan, meaning it's the only possible execution method for the query? Is this plan fully optimized, or did the optimizer go through it's prescribed cycles and simply take the best plan it had generated up to that point? These questions can be answered directly from information…
Read More
30Dec 2009 by Grant Fritchey 1 Comment

Practice Your Restores

SQL Server
Steven Jones posted an excellent editorial today all about how your backups are only good if you know that you can restore from them. He couldn't be more correct. I posted the following thoughts in the comments, but I know not everyone reads the comments in articles & editorials. Although, if it's a good article, you should read the comments, especially on SQL Server Central. Frequently the discussion about the article can be as enlightening as the article itself. But I digress. Steve's point, pretty clearly stated but I'll repeat it, backups don't matter, restores do. I'm going to pile on to this point just a bit, because it can't be emphasized enough. Nothing is more important than verifying backups, except, verifying that you know how to run a restore. You're…
Read More
29Dec 2009 by Grant Fritchey 6 Comments

The Other Server

SQL Server, T-SQL
I had a fun support call I need to share. A developer called up to tell me that a particular dev instance was offline. He informed me that the server SQL08\DEV01 (the names have been changed to protect the innocent) server was completely inaccessible. I knew that multiple development teams would shortly be calling and that I'd better get on this issue most riki-tik. I quickly typed the connection string into Management Studio and watched in confusion as the server instance popped up on my screen. It was fine. I did a number of checks, looking for active connections, recent connections, errors in the log, indications of a recent reboot... Nothing. I called the developer back and told him that the server was fine. He called me again in two…
Read More
23Dec 2009 by Grant Fritchey 1 Comment

A Lack of Excitement

PASS, PowerShell, SQL Server, T-SQL, Tools
I usually use all the problems, crashes, and issues that I run into at work as grist for my mill, aka, material to blog about. But lately, we haven't been crashing & burning much <knock wood, turn three times, throw salt over my left shoulder, spit>. But it was suggested that may be I should mention why that is. The fact of the matter is that I've been spending a lot more time working on methods for monitoring our systems so that we avoid more of the stupid stuff, full disks, failed backups, long running agent jobs, etc.. I've blogged before about our use of Microsoft's Operations Manager for monitoring our servers and how we've built custom rules and monitors to keep an eye on things. I've also mentioned how…
Read More