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.

Allow_Page_Locks for Reorgs

minion reindex-01This is a cross-post from MidnightSQL.com and Sean’s blog, DBARant.

There are many settings that get set one way or another in DBs and in tables. Allow_Page_Locks is one of them that you may not be able to do anything about because your vendor may require it and your situation may require it as well. Normally it’s set to true, but it does get set to false and when it does, it typically needs to stay that way.
The problem is that when you reorganize these indexes that have allow_page_locks = false, then the reorg will fail. But the problem is that if you change it, you may see increased blocking issues. So what is there to do?

Well, the answer is Minion Reindex. We allow you to define pre and post code at the table-level that you can use to switch this option on and then off again when the table is through. And better yet, we even give you the code to discover all of these issues in your database and fix them. In the Minion Reindex download folder you’ll find a Queries folder. This folder has a sql file that you can run and it will insert the table-level exceptions with the proper pre/post code. The precode sets allow_page_locks = true and the postcode sets allow_page_locks = false. This way you can still have the setting the way you need it, and perform your index maintenance too.

And of course, Minion Reindex is completely free so download it now and you won’t be sorry.

Real news, real tech.