To re-cap from previous posts, Expert Performance Indexing for SQL Server is a book I co-wrote with Grant Fritchey (Blog | @gfritchey) last summer, which is a valuable resource to anyone building indexes on SQL Server.  As part of finishing the book, Apress sent me a number of copies of the book to share with people.  I figured the best way to share these out is to give them away to people that comment on this blog.

So here’s a topic for leaving a comment this week…

The Question

For part seven, the question will focus on chapter nine, which covers index maintenance.  The topic for discussion in the comments this time is…

Do you think that defragmenting indexes is worthwhile?  Why or why not?  What evidence do you have to support your position?

If you have a story or opinion to share, leave a comment below. After about a week (once I return from vacation), I’ll select one of the comments and send that author a copy of the book.

Expert Performance Indexing for SQL Server is a book I co-wrote with Grant Fritchey (Blog | @gfritchey) last summer, which can be a valuable resource to anyone building indexes on SQL Server.  As part of finishing the book, Apress sent me a number of copies of the book to share with people.  I figured the best way to share these out is to give them away to people that comment on this blog.

So here’s a topic for leaving a comment this week…

The Question

For week six, the question will focus on chapters eight, which covers index myths and best practices.  The topic for discussion in the comments this time is…

What indexing myths have you had to deal with?  Have you had trouble with those who think databases don’t need indexes?  Have you dealt with those that believe key column order doesn’t matter?  Is there one you’ve dealt with that isn’t often talked about?  Or how about an indexing best practice that you use?

If you have a story to share in this area, leave it in a comment below. After a week, I’ll select one of the comments and send that author a copy of the book.

A bit late, again, on selecting the winner for this post, but after looking over the comments, the winner for the book this week is Peter Schott. Peter worked with the “order doesn’t matter” myth. This is probably one of my favorite myths, mainly because it logically doesn’t make sense that order would matter. Order does indeed matter for key columns and the selectivity or frequency of filtering on those columns makes a huge difference. This myth is dealt with directly in chapter 8.  Learn more about indexing myths and best practices in Expert Performance Indexing for SQL Server.

The other comments this week were from:

• Aaron dealt with a problem of heaps and compression and how their maintenace appeared to interrupt the desired state
• Erik dealt with primary key columns needing to be added added as key columns to non-clustered indexes
• John had a client that had a ‘no downtime’ requirement and bad practices around index and statistic maintenance.

Expert Performance Indexing for SQL Server is a book I co-wrote with Grant Fritchey (Blog | @gfritchey) last summer, which I think can be a valuable resource to anyone building indexes on SQL Server.  As part of finishing the book, Apress sent me a number of copies of the book to share with people.  I figured the best way to share these out is to give them away to people that comment on this blog.

So here’s a topic for leaving a comment this week…

The Question

For week five, the question will focus on chapters six and seven, which cover full-text indexing and indexes on memory-optimized tables, respectively.  The topic for discussion in the comments this time is…

What challenges have you solve with full-text indexes?  Or what have you learned about indexes on memory optimized tables?

If you have a story to share in this area, leave it in a comment below. After a week, I’ll select one of the comments and send that author a copy of the book.

July 25 Update

A bit late on selecting the winner for this post, but after looking over the comments, the winner for the book this week is Margaret Norkett. She upgraded from SQL Server 2005 to 2012 with the “old style” full-text indexes and ran into issues with the migration creating large numbers of ndf files. Just for the pain of dealing with that many ndf files, she deserves the book.
Learn more about full-text indexes in chapter six of Expert Performance Indexing for SQL Server.

The other comments this week were from:

• Robert built a resume search capabilities for HR using full-text indexes.
• Peter used full-text indexing back in the day prior to the recent improvement. While it was combersome at that time and required some workarounds, today he’d jump right in with it.

But, in past three weeks’ posts (1, 2, 3), I talked about how I’ve got a few copies of Expert Performance Indexing for SQL Server to give away.  This is a book that I co-wrote with Grant Fritchey (Blog | @gfritchey) last summer, which I think can be a valuable resource to anyone building indexes on SQL Server.  To give away these books, I’m asking a set of questions each week and sending out a book to someone based on comments left on this post.

The Question

For week four, the questions will focus on chapters four through five, which cover XML and spatial indexes, respectively.  The topic for discussion in the comments this time is…

How have XML and spatial indexes helped in your databases?  Have you had a use for these yet?  Are you using any XML or spatial data without using any indexes?  Any brilliant stories when XML or spatial indexes have saved the day?

If you have a story to share in this area, leave it in a comment below. At the end of a week, I’ll select one of the comments and send that author a copy of the book.

July 1 Update

The winner for the book this week is Jason Horner. He shared a story of improving performance on tables with spatial columns with dramatic effect.  I’ve worked with Horner in the past and have heard him talk about spatial indexes in a number of situations.  I’ve always been impressed with the effectiveness of these types of indexes and Horner’s skill in applying the spatial indexes.  Learn more about spatial indexes in chapter five of Expert Performance Indexing for SQL Server.

The Question

For week three, the questions will focus on chapter three, which is Index Metadata and Statistics.  The topic for discussion in the comments this time is…

What indexing dynamic management view do you use the most, and how does it help you?  How have index dynamic management views helped you in your indexing and performance tuning quests?

If you have a story to share in this area, leave it in a comment below. At the end of a week, I’ll select one of the comments and send that author a copy of the book.

June, 24 Update

The winner for the book this week is SQLDBAPro. I had to go with him because he mentioned my favorite index DMV, sys.dm_db_index_operational_stats.  This DMV is chalk full of low level details on how indexes are used.  I encourage you to check it out.  Chapter three of Expert Performance Indexing for SQL Server digs into this DMV quite a bit with examples on how the details are generated.

The other comments this week were from:

• Bob talked about using sys.dm_db_index_usage_stats and sys.dm_db_index_physical_stats
• Kathi mentioned indexing being an iterative process and reading the last edition of the book (Yes!)
• Shaishav uses sys.dm_db_missing_index_details for finding new indexes
• Josh pointed out using sys.dm_db_index_physical_stats for index fragmentation

Another free indexing books, more awesome!

In last week’s post, I talked about how I’ve got a number of copies of Expert Performance Indexing for SQL Server; that I co-wrote with Grant Fritchey (Blog | @gfritchey). Since these books don’t help anyone sitting on my desk, I’m giving them away over the next few weeks.

The Question

For week two, the topic is moving up a chapter to index storage fundamentals. The topic for discussion in the comments this time is…

What aspect of understanding the physical structure of indexes has proven useful in solving performance issues? Do you have a story of fragmentation? Forwarded records? Large object allocations? Ever used DBCC or page allocations to directly solve a performance issue?

If you have a story to share in this area, leave it in a comment below. At the end of a week, I’ll select one of the comments and send that author a copy of the book.

June, 17 Update

The winner for the book this week is Todd Kleinhans. He talked about using DBCC PAGE to recover missing data from a database. Something that takes a lot of patience and time, but works when you need the data.

The other comments was by SQLDBAPro, who talked about how he’s used the structure of the non-clustered index to better understand how to build indexes.

Sapiens takes the reader on an interesting journey through three revolutions of homo sapiens; the cognitive, agricultural, and scientific. Through these revolutions, Harari discusses how these revolutions changed humans and our society.  He looks at what our life was like in the past, an how pre-agricultural revolution homo sapiens may have lived happier lives, which I didn’t agree with.  And he moves forward through the revolutions to look at the future and the impact of knowledge from the scientific revolution and artificial intelligence.

There are a few things I found interesting in Sapiens. First, how he described our rules of society, money, and basic interpretation of the world as being based on an agreed upon fiction. Not that the don’t exist but they aren’t material in the same way that the physical world exists. Most of the world we experience is based on concepts built on abstract ideas that we all trust and agree to go along with.

[bctt tweet=”Money is the most universal and most efficient system of mutual trust ever devised. -Yuval Harari” username=”stratesql”]

The other bit that was pretty was the discussions around religion. In it, he discussed the traditional concepts of religion but also included other belief systems, such as liberalism, capitalism, and environmentalism. While these may not seem like religions in the traditional sense, the parallels he draws between natural religions and these philosophies is pretty compelling.

[bctt tweet=”A good rule of thumb is ‘Biology enables, Culture forbids. -Yuval Harari” username=”stratesql”]

Sapiens is a great book that will leave you thinking about where humankind is going, I definitely recommend reading. It provided a great opportunity to think about the world we live in and the fictions that we subscribe to. Take a chance and give it a read, I think it’ll change your perspectives.

Before I tell you how, lets start with what we are talking about here. If you weren’t aware, last summer I worked with Grant Fritchey (Blog | @gfritchey) to update Expert Performance Indexing for SQL Server. The new edition jumps from 10 to 15 chapters and contains a bunch of new stuff.  This includes material that didn’t have a chance to include in the first edition, some chapter reorganization, and updates to include information for SQL Server 2014.

So, how to give away these books?

I figure the best way to do this is to talk about indexes.  For the next 13 Fridays, I’m going to propose an indexing questions related to different chapters of the book.  The week following each question, I’ll select one of the comments at random and send out a book.  Basically, tell a good story, make me laugh, share something unique and you got a chance to get the book.

For week one, let’s start with some fundamentals of indexes and bullet 1 from chapter 1 – why build indexes?  What interesting adventures have you had in the past where you built an index and dramatically improved performance?  Leave your thoughts in a comment below and I’ll select the first person to receive a book a week from now.

June, 10 Update

The winner for the book this week is Cujo DeSoque. I loved how he identified a poor index and then disabled it when he wasn’t allowed to drop it. Solving both requirements… in spirit.

Here’s a summary of the other comments:

• Tim talked about how improving performance with an index led to him becoming a DBA.
• Todd talked about crazy DBAs who had no indexes.
• Ken included a story where indexes were avoided due to their percieved imapct on triggers.
• SQLPRODDBA performance was improved from 30 minutes to 2 seconds with an index.
• Lohith shared a situation where a lack of indexes led to CPU utilization issues.

Thanks all for sharing your stories. Be sure to check out the next set of indexing questions and a chance for the second book.

Since this change, I’ve spent a lot of time evaluating what I want from my career. What should I continue to do versus what I had to do to build a consulting brand. As a consultant, it is easy to see the value and necessity in blogging and presenting. The wider your technical base of knowledge and better your reputation , the more success you can expect.  Blogging and presenting offer a great opportunity for both of those.

While this doesn’t change as a full-time employee, there is an additional consideration that comes up. What’s the value of deeper industry versus technical knowledge for your career going forward. The catch here being that when in financial services, there is a limited amount in which I can discuss industry knowledge and technology related to my projects.  Adding to this, the job change was related to spending more time with the family, and time for blogging and presenting presents a trade-off in time.

As a result, I’ve taken some time to figure out if I should continue writing on this blog. And by writing, I mean a return to some real technical content, versus the rubber stamped Monday roll-ups. While going through this, I thought about whether I write for myself or for others. While there is merit in both, I think I’ve been successful in the past when I’ve focused on writing for myself.

This goes back to when I started writing and why I should continue. I started writing create a stake in the ground regarding thoughts, proofs, and ideas regarding technical topics and techniques. Building out fully formed ideas and validating my understanding on how different technology pieces operated. It’s easy to play around with technology, but its better to polish an idea to the point in which it can be considered done. Of course, the byproduct of writing for myself is the ability to share that content here.

I have a backlog of things both SQL Server and Azure that I’ve been playing with the last few months. It’s definitely time to start polishing and publishing those ideas.

Thanks for reading.

What makes this interesting is when I am using UNION to join the results. How do you place a final resultset from a UNION, EXCEPT, or INTERSECT into a temporary table using SELECT INTO? Where does the INTO portion of the query go?

This is actually a pretty simple thing to do. The INTO for the SELECT INTO goes into the first query of the set. An example of UNIONing the results from sys.dm_exec_query_stats and sys.dm_exec_query_stats into a temporary table is provided in listing 1.

--Listing 1. Query to UNION results into temporary table.

SELECT plan_handle, execution_count, total_elapsed_time
INTO #stats
FROM sys.dm_exec_query_stats
UNION ALL
SELECT plan_handle, execution_count, total_elapsed_time
FROM sys.dm_exec_query_stats

Creating temporary tables in this fashion is easy and simple. I mention how to do this because, I forgot for longer than I want to admit that this can be accomplished without a sub-query.