Video: A Better Way to Reindex

minion reindex-01Yestreday Sean taught “A Better Way to Reindex” for the PASS Performance Virtual Chapter (event link).

The recording is up, if you missed the live event, or if you want to re-watch it: https://attendee.gotowebinar.com/recording/4199143009901996032

And by the way, the demo that fails (there’s ALWAYS one demo that fails) is due to the fragmentation routine we use, fragmenting the data way more than usual. All we had to do was to increase the reorg threshold, and it would’ve been fine. So for the record, it’s the fault of the test harness, not of Minion Reindex.

Here’s the abstract:

A BETTER WAY TO REINDEX

Let’s play a guessing game: I guess that you don’t want to spend time on index maintenance, but you know your servers need it. You want something that’s straight plug and play, dependable, and preferably free. I also guess that you don’t want to manage multiple jobs for this one maintenance task, even when you have exceptions and special settings. Am I close on this? Come and take a look at Minion Reindex. It does all those things that you want for index maintenance, plus some really innovative stuff. Watch your reindex progress live. Configure settings and exceptions for individual databases or tables. Gather fragmentation stats separately, outside your maintenance window. There’s quite a lot more so come see how you’ll make your index maintenance a lot easier.

Of course, you should go and download Minion Reindex yourself, and see the documentation, and follow along with all the fun stuff in the session.

Dear Users [and IT Guys]…Sincerely, Your IT Guys

Dear Users [and fellow IT guys],

We, the tech people, understand that you just want technology to work so you can get your jobs done.  We really, really understand that.  When your technology doesn’t work, you turn to us so we’ll make it behave. We like making technology behave – we got into this field on purpose. But when you submit a request, throw us a bone.

All of this applies, too, to informal requests: emails, IMs, conversations, assignments to underlings, and so forth. No, “the database on Server2 is slow” is not a good enough communique for me to actually do something useful. “Slow” is subjective; any operator – whether technical, medical, mechanical, or otherwise – needs objective information to make any kind of positive change.

Let’s look at a few examples of how NOT to submit an IT request / how NOT to ask for improvements to a system.

Too Brief

“I have a question about SQL.”  

While we’re pleased that you’vetaken the initiativeto start a dialogue and educate yourself, let me make a few suggestions:  If it’s an actual question, just email, or ask. It doesn’t deserve a helpdesk ticket.  And, actually ask the question.  There’s nothing in “I have a question” that spurs me to respond quickly.

“I need help with my computer,” or, “The system is slow.” 

When I take my car to the mechanic, I wouldn’t expect to get good results if I just said, “I need help with my car”, or “It’s slow”.  I explain what’s been happening with my car, what symptoms I’ve seen, when it happens, how to make it happen again, and (if necessary) what I’d like done about it. With that starting information, he can look into it, and ask further questions.

“The DMP application doesn’t work.”

Or (a personal favorite): “The internet is broken.” 

Again, from this I have no idea whether your application or browser doesn’t start, gives an error, provides bad data, or if it’s even installed on your computer.

Too Wordy

Can you get me some information on the business licenses for that software? I’m doing a report for Bob, and I’ve been really busy all week, so sorry I didn’t write before now. Once I get this done then I can move on to a couple of other things I’m going to talk to you about (but don’t worry, I won’t keep  you too long, LOL!) before the conference next week. For that I’m probably going a little bit early, so I’ll be out on Tuesday…………………….[etc]

Like you, IT people are very busy. What we need is information, not a letter about your day (we’ll talk about your day later, when we all scrounge the board room meeting leftovers!) The ONLY pertinent part of this request was “some information on the business licenses”, but again, there are no specifics. What information? What licenses?

Not Enough Information, Period

“I’m getting this error on my screen, something about corrupt something or other.  It happened while I was on our accounting website, but I’m not sure if it’s something I did or what.”

Back to the car analogy:  you are now telling the machanic “My car did something weird yesterday, I was driving it on the freeway and it happened.”  Did it stall, slow down, make a noise, smoke, lose a tire, start speaking? Did you swerve or brake suddenly right before it happened? Has it been running with a low oil light for the last 6 months? 

You have to tell us more: What was the exact error? Send us a screenshot. Was the error generated from the website, or another application you have open? What steps did you take prior to receiving the error? Did the error prevent you from accomplishing something?

 “Please unlock the user name ‘BobS’, he forgot his password.” 

This might look solid, but this request is the equivalent of telling a locksmith “can you unlock my car? It’s the grey one in the parking lot.”  Is “BobS” the account he uses to log into his computer? Into a server (and if so, which one)? Into an application (which one)? A website (which one)? 

“Vendor needs SA access to [servername].”

Similarly, my daughter needs to borrow the keys to your new BMW.  I won’t tell you why, or what she’s up to…you just need to trust me and hand over the keys.  (Special note: IT guys don’t trust vendors on spec, and for very, very good reason.)  Tell us who they are, what they want to do, and why.

Short Lead Time/Inappropriate Prioritization

“I need you to put together a new TPS report, with additional columns Profit, Loss, Negligibility, and Email, grouped by Chapter leader, by 4pm today.”

“I can’t print to the East1 printer. PRIORITY 1!!!!

The tech people are in charge of making sure the business runs smoothly: computers, software, databases, security, backups, websites, questions, business intelligence…it’s all up to us.  Many times, the urgent 3 month project we’re on, or the server outage, or a meeting with vendors or upper management, takes precedence over the request you forgot to submit last week. I’m sorry, but sometimes we can’t drop what we’re doing for you.

As with all things, plan ahead as much as possible, ask nicely when you forget, and don’t fudge a “Priority 1″ request if you can’t (for example) print from your computer. Send the document to your cubemate and ask him to print it for you.

Request for Unsupported Tech

“I need you to jailbreak my iPhone.”

“Could you install the latest version of Photoshop?”

“I can’t watch The Guild from my laptop.”

We have been retained by a company that pays us to do work. Sure, we all slack off and have distractions through the day, but I can’t make it part of my job to support your extracurricular activities.  I like you, I really do, but if it’s not supported, not related to work, or illegal, don’t ask me to do it.

Guidelines

When you submit a helpdesk ticket, you increase the odds of getting your request fulfilled quickly and accurately if you do these things:

1. State the request or problem clearly. 

2. Include pertinent details: time, server/application/website, history, behavior…everything you’d tell your mechanic.

3. Attach a screenshot when appropriate.

4. Include proper contact information, assignment, categorization.

5. Plan ahead, and assign proper prioritization. Please understand that your last minute report is most likely NOT mission critical. An unresponsive sales website is.

5. Be sure, before you ask, that your request is related to work.

Sincerely,
Your IT Guys
http://www.MidnightDBA.com/Jen

Have you checked out Minion Reindex yet? It’s our free, easy, comprehensive index maintenance solution. You’ll love it, seriously.

This is a reprint and update from a 2010 post that still makes a lot of sense.

Create a clustered index and a primary key on existing tables

Snow_Flakes

Your data: unique little snowflakes

It’s the simple things in life that can trip people up. I find that creating primary keys and clustered indexes on existing tables is one that newish DBAs tend to trip up on, and so (like I do), I shall blog about it so there’s a nice, clear, explicit reference available.

Yes, Virginia, you most certainly CAN add a clustered index to a heap without explicitly dropping and recreating the table. Same thing for primary key constraints.

IMPORTANT: Creating a clustered index is a big operation, for large tables. This isn’t something you want to do without planning, on a production server. For more information, see http://sqlmag.com/blog/what-happens-if-i-drop-clustered-index

Requirements

Requirements for creating a clustered index on an existing table:

  • The table can’t have an existing clustered index.

That’s it. There’s no requirements for uniqueness or NULLability, or NULLs. Note, however, that after a column is part of a clustered index, you can’t alter the properties of that column (so, for example, you couldn’t then make the column NOT NULL).

Requirements for creating a primary key constraint on an existing table:

  • The table can’t have an existing PK constraint.
  • The column can’t have any NULL values.
  • The column must be defined as NOT NULL.

Demo time! Clustered Primary Key

So, let’s take a simple example, where we want a single-column, clustered primary key (on column [id]):

CREATE TABLE t1 ( id INT, val INT );
INSERT INTO t1 VALUES (1, 1), (2,2), (3,3);

The table meets 2 of the 3 requirements for a PK constraint, but [id] is a NULLable field. Let’s fix that:

ALTER TABLE t1 ALTER COLUMN id INT NOT NULL;

After that, adding a primary key constraint to an existing table is easy. While we could run a CREATE CLUSTERED INDEX statement first and then the PK, why don’t we just run a single statement and do both at once:

ALTER TABLE t1 ADD CONSTRAINT pk_t1
 PRIMARY KEY CLUSTERED (id);

That’s it!

Demo time! Nonclustered Primary Key

Not all primary keys are clustered. In this example, [id] is our primary key, and [val] is our clustered index:

CREATE TABLE t2 ( id INT, val INT );
INSERT INTO t2 VALUES (1, 1), (2,2), (3,3);

Again, we have to make sure the PK column is NOT NULLable:

ALTER TABLE t2 ALTER COLUMN id INT NOT NULL;

Now we can create the primary key constraint and the clustered index:

ALTER TABLE t2 ADD CONSTRAINT pk_t1
PRIMARY KEY NONCLUSTERED (id);
CREATE CLUSTERED INDEX ix1 ON t2 (val);

Ta-dah!

Reference

Remember kids, a clustered index is not the same thing as a primary key.

Clustered indexes sort and store the data rows in the table or view based on their key values. These are the columns included in the index definition. There can be only one clustered index per table, because the data rows themselves can be sorted in only one order.

The only time the data rows in a table are stored in sorted order is when the table contains a clustered index. When a table has a clustered index, the table is called a clustered table. If a table has no clustered index, its data rows are stored in an unordered structure called a heap.

-http://msdn.microsoft.com/en-us/library/ms190457.aspx

Whereas…

A table typically has a column or combination of columns that contain values that uniquely identify each row in the table. This column, or columns, is called the primary key (PK) of the table and enforces the entity integrity of the table. Because primary key constraints guarantee unique data, they are frequently defined on an identity column.

When you specify a primary key constraint for a table, the Database Engine enforces data uniqueness by automatically creating a unique index for the primary key columns. This index also permits fast access to data when the primary key is used in queries. If a primary key constraint is defined on more than one column, values may be duplicated within one column, but each combination of values from all the columns in the primary key constraint definition must be unique.

-http://msdn.microsoft.com/en-us/library/ms179610.aspx

Happy days,
Jen McCown
http://www.MidnightDBA.com/Jen

Have you checked out Minion Reindex yet? It’s our free, easy, comprehensive index maintenance solution. You’ll love it, seriously.

Real news, real tech.