Recently I sat down with some VB.NET developers who were using SQL Server as a backend for their application. They used SQL Server to create tables, but avoided everything else. And I mean everything. No views. No procs. And no indexes!
I’ve seen the “no views and procs” approach before (I want all my code in one place!), but I rarely see the “no index” approach. And every time I encounter it, it’s always the same: “I read somewhere that indexes add overhead to inserts, deletes, and updates.”
As the data grew, so did the size of the table scans, and so did the frequency of the timeouts. So they segmented the data by business logic across multiple tables. That worked for awhile. Eventually data generated from one business rule grew too large. Finally they abandoned the “no index” approach and added a few single column non-clustered indexes, which left them no better off than before. It wasn’t long before I got a call. We spent the day discussing how SQL Server works, building indexes, and reading execution plans. By the end of the day, SQL Server was much less intimidating.
With each release of SQL Server, the barriers to entry are lowered. However, once inside, if you don’t have someone there to show you around, it can be a bit overwhelming.
Recently I posted that hints should be used sparingly and only as a last resort. Too often I've seen hints used to mask bad code and poor database design. Having said that, there are, in certain circumstances, hints that should be used regularly. For example, Timothy A Wiseman makes an excellent case for using the NOEXPAND table hint with indexed views. Here are a three hints that I use regularly.
When accessing static data, I always use NOLOCK. Since I am confident that there are no inserts, updates, or deletes taking place, I don't have to worry about dirty reads. Using NOLOCK accomplishes two things. First, it removes the overhead of locking. Second, it removes the possibility of my query blocking another.
I also use its opposite: TABLOCKX. I use it whenever I'm updating large number of rows. It eliminates the overhead of lock escalation and ensures that there won't be any deadlocks.
Finally, after complaining about FAST (FASTFRISTROW prior to 2005) a few days ago, I must admit that I do use it. It's a great way to get a quick peek at the data when writing new new queries or just after running an ETL process.
Too often I see the word DISTINCT used to cover rushed, botched, or simply sloppy ETL. Today I scripted out a view since I couldn’t recall all six tables that it was joining together. I discovered a new entry in the column list: DISTINCT. There was even a comment admitting responsibility. He did it yesterday. Then I recalled yesterday’s email that announced the upload of new data. Just beneath his comment, I left one.
20081024-BHE: Nooooooooooo! Don't use distinct in a view, especially one with six joins!
The thing about DISTINCT is that it’s too easy. Just one word. Yes, it’s a quick way to clean up your ETL mess, but you leave another one in its place: an execution plan that’s in the tank, or at least now circling the drain. I wish we had the option of disabling DISTINCT.
EXEC sp_configure 'DISTINCT', '0';RECONFIGURE WITH OVERRIDE;
That way those who want to clean up their ELT mess in a view, must at least be forced to copy every column into a group by clause.
Oh, I almost forgot. The one who "fixed" the view using DISTINCT. He's like most of us. He was rushed. He had to get that data into production ASAP.
I recently attended the Indy and Tulsa TechFests. Here’s how the two compared.
Both drew about 500 per day (Indy was on a Saturday and Tulsa over was on a Thursday and Friday). Both had great swag. Indy had the better food (a huge Italian lunch buffet!) and Tulsa has the better facilities (there was plenty of room at OSU!).
For those primarily interested in SQL Server, Indy was the place to be. It offered two SQL-related tracks: one for DBAs and one for those interested in BI. Often I wanted to be in both sessions at once. The best presentation was Jimmy May’s on Disk Partition Alignment. It’s too much to summarize here, but you can read about it on his blog, or better yet, you can catch his presentation next month at PASS.
Tulsa had the basics of SQL Server covered, but the real appeal was the mind-numbing number of tracks (17!) and topics and speakers (4 cool keynote addresses). There was something for everyone. Since the SQL track was already full when I submitted my topics, I ended up presenting in the eMarketing and Business track. This turned out to be more fun than I had anticipated. It gave me a chance to meet and interact with a cross-section of attendees.
Finally, hats off to all the organizers! I had a chance to spend some time with Tulsa’s David Walker and Indy’s Dave Leininger and John Magnabosco. All three were incredible. If any of them were exhausted or frustrated, it didn’t show. What did show was their enthusiasm and passion for excellence. I will definitely be back next year.
I recently discussed query hints with Brad McGeHee. We talked at IndyTechFest where he presented on the subject. We both agreed that they should be used sparingly and only as a last resort.
Some of the worst code that I’ve had to troubleshoot was laced with hints. My favorite one is FASTFIRSTROW (and in 2005 FAST) which is usually used to prevent the application from timing out while waiting for data. After untangling even the worst code (the only green ink was retired code :-), I would usually find that all I needed to do was add (or change) an index. In some cases I rewrote one query into two or three. However, when tuning views, this isn’t possible.
A few months ago I was tuning a view that joined a large table (40M rows) to two smaller tables (1 or 2M rows). When certain values were used in the where clause of the query calling the view, the response time went from 1-2 seconds to 2-3 minutes. I tried everything I could think of. I built covering indexes. I rebuilt statistics. Nothing. After carefully comparing the execution plans, I finally isolated the problem. It was a table spool (lazy spool) coming off of a nested loop. Although its relative cost was only 4%, it practically hung the view. Since I had no way of preventing SQL Server from using a lazy spool, I had to prevent it from using a nested loop, so I used the hash join hint. Once I added that hint to the view, it became responsive again.
Yes, avoid using query hints whenever possible, but sometimes they can really be lifesaver.
