Missing Indexes

Think of a telephone directory

It shows everyone in the area, sorted by Surname and Firstname

If it was sorted by phone number, it would take an awfully long time to find Zachary Young

So that’s why it is sorted the way it is

A French phone directory is different

It’s sorted by Village, then Surname and Firstname

In database language, those are two different clustered indexes.  Clustered just means that the list of information is sorted that way

But suppose you were Detective Chief Inspector Tom Barnaby, every week hunting down the killer of half the population of the village of Midsomer.  You found a clue - a phone number scrawled in the victim’s blood at the murder scene, and wanted to find out whose number it was.  Yeah, obviously the murderer is going to turn out to be Zachary Young, but you don’t know that.  

Your database query might look something like this:

select

       firstname,

       lastname,

       address1,

       Town

from 

       Directory

where 

       PhoneNumber = '0123456789'

It’s going to take a long time to run that – because the directory table is missing an index on PhoneNumber

When you run the query, it has to search all the way through the list of names until eventually it finds a match

SQL Server keeps a record of all the queries it runs, and you can get it to count up all the times it scans through a table because the index it needs is missing.  

So – you REALLY need that missing index.  You will find the murderer eventually, but the index makes it a lot easier

Create the index, and everything works a whole lot more easily

Want something a bit more technical?  Here's how to find the missing indexes

The Joys of Admin

I'm admin on a group on LinkedIn called French Connections – 65,621 members at the time of writing.  The other day I got a join request to review.  The profile was a little bit thin, and only 15 connections, so I sent a message… 

But read for yourself.  

Monday Jack Whittaker sent the following message at 12:47 PM

You are both members of French Connections on LinkedIn

Hi Charlie!   Thanks for applying to join the French Connections group! I have a slight problem - we like members to have some sort of connection to Francophonie (it helps to keep spammers at bay), and unfortunately you don't seem to have one. Probably because you haven't added it to your profile yet, or possibly I just missed it!   Perhaps you live in France? Drive a Peugeot? Love French cuisine?   Please reply and tell me why you would like to join, and if it’s appropriate, add it to your profile. Do please let me know within a few days or I’ll assume you aren’t interested. I look forward to hearing from you   Regards Jack Whittaker Moderator

• Today Charlie O sent the following messages at 3:30 PM 

You are totally wrong in your assumptions.   first of all i am Belgian and Flemish which speaks french and 12 other languages   secondly i lived in nice france for 5 years even got married to my first wife in Nice   But your reaction as a moderator says it alll   Forget about me and be less biased next time. little bonafide Ape.   I know more about france and the culture it stand for then you ever will. You  missed a golden opportunity and cant even write french I bet.   SO, dear wanker: au revoir, Le Petit Pois Un grand salut du LION Flament, which would gobble up little minded Brits or Yankess for breakfast. You must have nothing but 'faux' wannabees trying to understand what I did a long time ago in your group.  I was willing to help you learn about what you claim interest you. Learn to do proper research and get an education  upgrade in social communication on this type of platform you badly need dear FOOL.  Carpe Diem,  I rest my case Dear Moderator you are now challenged and I will make sure the public at large knows how biased you are ....

And you do not dictate what goes on or comes off my profile dear fool!

 <Charlie O sends me a link to his brother’s Linkedin profile for no obvious reason – to spare his brother's blushes, I’ve removed it from this transcript>

my brother would slpa you for saying what you said to me, shall I prosecute you in a court room?

Dumb fool

Moderate that, Mr Le Professuer de math qui est devenue un venduer de petit pois!

• Jack Whittaker sent the following message at 3:34 PM

Jack Whittaker 3:34 PM

Hi Charlie None of that stuff appears in your profile Any one of those things would have got you welcomed to the group without question - but I'm not psychic So - let's assume you are having a bad day - would you still like to join or have I offended you too much?

• Charlie O sent the following messages at 3:35 PM

People like me are protecting what you can find out about me for good reasons as is proven yet again!!!

Lets assume you dont know google exsist A simple charlie o would have answered all your dumb questions

learn to do your job as a moderator you dumb ape

now leave me be and stop wasting my time with your spam pre created paragraphs

No one wil tell me what to put on a profile Unlike you I dont need this linkedin app or site to achiev what I achieved in lifeI was trying to help your group You dumb fool which means I am the good kind of apple you kicked in the balls, bravo fool! ILL GLADLY TAKE SOME TIME NOW TO DOCUMENT THIS ON SOCIAL MEDIA AND MAKE YOUR ATITUDE GO VIRAL

Lets see how you deal with that as a moderator and my internet foot print is huge and I know how to SEO the right way ...

Skills which will garantee your group gets seen as it needs to be seen a bunch of wankers from the UK trying to make french clients or contacts!!!

No smart come backs I see you are a coward as well?

Last but no0t least you should never have been made a moderator ... you haven't got the skills for the role!

lets see where this ape works  and if i have him as client if so your company will be booted off the books

sql dba admin what a joke

Do you even speak french ape?  <enough to know how to spell "vendeur">

Brexit YES voter I bet ....

grow up fool!

• Jack Whittaker sent the following message at 3:49 PM

You seem to be slightly upset Charlie All because I asked a man who lives in Sheffield if he has any connection to francophonie I'm curious - why do you feel that I insulted you?

Sadly, Charlie O never replied

I blocked him from the group, of course – well, obviously

But I took his advice and googled his name.  I found his Facebook account – and it’s chock full of adverts for all sorts of crap.  Remember this line in my initial message to him?  “We like members to have some sort of connection to Francophonie (it helps to keep spammers at bay)”.  Turns out he IS a spammer!

Activity Group not synchronizing

There was an activity group failover in the night.  Some sort of glitch – glitch happens.  The Primary flicked over to the Secondary and the Secondary became the Primary, no problem.
The Old Primary became the Secondary
BUT…

Databases on the New Secondary are marked as Not Synchronizing

The error log says: A connection timeout has occurred on a previously established connection to availability replica with id [5FA3CBDB-5504-43E2-8697-2C713110307B].  Either a networking or a firewall issue exists or the availability replica has transitioned to the resolving role.

Well, the replica has certainly transitioned.  Other instances on the nodes were unaffected

A bit of googling finds this article.
Microsoft recommends restarting service on secondary replica.
The problem occurs because of an internal race condition.  What on earth does that mean?  Ah - it's a bit like a deadlock.  

And another article.
Resolve Secondary Replica Database Not Synchronizing You cannot ONLINE a secondary replica availability group database Since a secondary replica availability group database is really in an ongoing restoring state, it cannot be set Online like the primary replica. Remember it is already in the Online role, according to the DMVs.

Solution Restart the SQL Server instance hosting the secondary replica. This will initiate recovery on all databases including availability group databases, and so long as SQL Server can access all the database files, the availability group database affected should recover successfully and resume synchronization with the primary replica. 
OK, that all seems pretty clear.

Next question - can we do it now?  In the working day?  
Well, if we don't, we have lost our Availability group resilience - that's not a Good Thing

If you look at the picture above, we have an estimated recovery time of 0 seconds.  I've added a few extra columns to my Dashboard - there's a list of them here.  Now that seems too good to be true.  It's been down for a few hours, are you seriously expecting me to believe that there is no data waiting to go across to the Secondary?  Pshaw! I say to you.
One of my colleagues thinks that Dave has mentioned this issue before – but he isn’t sure.  Dave is on holiday - because of course he is.  Whenever you need someone who knows stuff, Sod's Law mandates that they are not around.  

So we are a bit scared of restarting the services on the secondary – is it going to impact on the primary as it catches up with the backlog of stuff to be transferred? 

We decide to wait until everyone goes home for the night. 

I stop the agent service.  

I stop the SQL Server service

I start the SQL Server service.  

I start the Agent service

I go and have a look at the Dashboard

The two small databases have already sorted themselves out.  

The two big ones estimate 20 minutes and 10 minutes to recover, but it quickly becomes obvious that this is an over-estimate.  The actual time taken to recover was about 3 mins for the larger, 1 min for the smaller.  There doesn't appear to have been any impact on the Primary, but it fixed itself before I could check.  

When Dave returns from his hols he confirms that restarting the services on the secondary will fix this; it takes seconds he says.    He reports that it sometimes happens when he manually fails over e.g. to apply patches.  Out of hours, of course. 

So with hindsight, I would feel much more confident about restarting the service on the Secondary node, and do it in the working day rather than run the risk of losing data.  

Shrinking that Database

Rule 1 - don't shrink that database!

It's a bad thing to do.  Brent Ozar says so.   And I believe him,.  Really, it's a bad idea

Imagine the stock control database.
You buy widgets, you sell widgets
100 widgets into stock
80 widgets out
No need to free up the disk space, because the day after, you are going to add another hundred widgets.
And SQL Server doesn't free up the space.  It just sits there waiting for more widgets

There's a but coming.

But suppose you need to free up space?  In my situation, I purged the database of two million old records into an archive DB.  Now I have an archive DB taking up 150 GB.  The original DB contains only 10,000 records (the last 15 days) but it still takes up 150GB, with 140GB free.  There's no room on the disk, and the Infrastructure guys aren't keen on giving me any more because it is an old physical box and it would mean them actually doing some work for a change.  

No problem, I can do this

I set up a maintenance plan:

Doing it as a maintenance plan lets me specify a time out for each step, which saves me the task of coding it. 

Step 1 - Shrink it in manageable chunks

Rather than try and shift all that 140GB empty space in one go, I want to shift a bit at a time, rather like a batch  Anil Kumar sets out a way to do it, and I unashamedly stole his code

How big a chunk?  Doesn't really matter - 512MB seemed like a good figure.  The point is that it shrinks.  then shrinks, then shrinks again.  If it fails for some reason e.g. hits the time out, then you have done at least a few of the chunks.  

Step 2 - Release Space

Just in case it hasn't gone already, belt and braces

USE DatabaseName

GO

DBCC SHRINKFILE (N'FileName' , 0, TRUNCATEONLY)

GO

Step 3 - Smart Rebuild

All that shrinking is going to screw up your fragmentation levels something 'orrible.  

Bad, really bad. for performance.  

If Brent Ozar catches you, he will not be a happy bunny

So we need to Rebuild or Reorganize the indexes.  

Here's how

All clear?

Right then.  So I fire it off, it runs, step 1 times out as expected, the other two steps run, all well and good.  

Except that the database hasn't shrunk.  Not one MB less.  

I spent a lot of time with my friend Google, and I see lots of people are having similar issues, hence this blog, which I hope will help others.  

I set up a bare minimum job to shrink the database a little bit:

USE DatabaseName

GO
DBCC SHRINKFILE (N'FileName' , 180176)
GO

I set it running, and went home.  Came in next day, and it had succeeded - Two hours 45 minutes, and it shaved 6.5GB off the database!  (That IS a little bit, when you have a 190GB database).  And that is a much longer time than I allowed for in my timeout period.  I reduced the target and ran it again - again it succeeded, this time removing 10GB in 03:37:40

I'm not sure why shrink in the manageable chunks code didn't work; it's on my list to troubleshoot that.  But in the meantime I will be able to live with a rough and ready shrink that works.  

Understanding Unique Constraints

I'm looking at a database from a vendor, who shall be Nameless.

It contains a table called tblConfig.  OK, I know some people hate to see tables with the prefix tbl, but I can see the point to it.  That isn't my issue

I'm reviewing indexes, with a view to getting rid of duplicates.  There really is no point in having two indexes doing the same thing - SQL Server will only use one of them, but it has to keep both updated.

And that's where I found it:

There is an index on that table called UniqueConstraint.  

There is another index, also called UniqueConstraint.  

One is unique, the other isn't, which is how the developer got away with it.  

The index UniqueConstraint which is unique indexes different fields to its namesake index UniqueConstraint  which is not unique.  

There is also an index called UniqueConstraintReporting.  This duplicates the index UniqueConstraint which is not unique.  UniqueConstraintReporting is unique.

I've dropped the UniqueConstraint index which is not unique, leaving me with the unique indexes UniqueConstraint and UniqueConstraintReporting

I hope that is all clear to everyone?

In Search of a Smarter Maintenance Plan

I wrote a blog post about Implementing Maintenance Plans a while back.  In it I suggested that if you have time to rebuild the indexes once a week, you should go ahead and do it. 

But what if you DON'T have time?

Indexes

I suggested rebuilding the indexes once a week.  And the bog standard maintenance plan rebuilds every index, whether it needs it or not.  But if things are heavily fragmented, or there are a lot of indexes with millions of rows each, that might take an awfully long time.  I would expect heavily fragmented indexes to adversely affect performance, so set up a Smart Rebuild / Reorganise job to run as part of the weekly maintenance plan.  

Instead of the usual Rebuild Index task, grab the Execute T-SQL Statement and add it to the maintenance plan.  You can restrict the run time with the Execution Time Out parameter - specifying 3600 seconds means that after an hour the job fails, so that it does not over-run the maintenance window.  Note that if it hits that time out, the job will fail, so I have put this step last.  It will also send an error message email if you have it set up to do so.  

Here's the script - I swiped it from Artemakis Artemiou - poli efcharisto!  

The job identifies all the indexes with fragmentation levels >15%

It starts with the worst and works down
Above 30% it rebuilds them
Between 15% and 30% it re-organises them

If the database is badly fragmented, you might want to run this daily, at least until they are under control.  


Statistics

I also set up a job to update statistics.  Statistics can get out of date, and queries may be less efficient because of this. 

Updating statistics is a bit of a funny one.  If you rebuild the indexes, you get a nice new set of statistics.  But if we are not rebuilding the indexes, you have to rely on Microsoft to do it as it thinks fit, according to its algorithm.  Sod's Law says that it won't update the ones you want it to update.  The SQL execution plan can ignore a perfectly good index because the statistics are not up to date, and may take far longer to run a query because of this.

As before, I added a T-SQL task to the Daily tasks,  last in the line.  

I've given it a timer limit of 5400 seconds (an hour and a half, to save you getting your calculator out)

And it runs a stored procedure which I found here...

Thanks to Bill Graziano!

Note that Bill's script sets a time limit too, so I don't really need to set an execution timeout.

There's an alternative to the above - the well regarded and reputable scripts from Ola Hallengren.
A lot of people swear by them.

The Madness Continues

I have a script which identifies MDF files which do not appear in my list of databases.

The purpose of this is to find databases, usually on development boxes, which have been detached and forgotten, but are taking up space on the disk.  A detached database is of course invisible, unless you go poking around looking for it.

What I usually do is:
i)  attach them again
ii) take a final backup just in case they actually are needed
     (not very likely, but sod's law says if you don't do this, they will suddenly become critical)
iii)  drop the thing

But today, I failed at the first hurdle:

Create file encountered operating system error 32

The process cannot access the file because it is being used by another process

SQL Server error 5123 tells me it's a problem attaching the database

Huh?  It's a detached database file, isn't it?  

What could possibly be using the detached file?

Some utter maniac renamed the .ldf file as .mdf

name	fileid	filename	filegroup	size	maxsize	growth	usage
GenDraft	1	F:\User Databases\GenDraft_20180925.mdf	PRIMARY	40960 KB	Unlimited	8192 KB	data only
GenDraft_log	2	G:\Logs\GenDraft_20180925.mdf	NULL	12352 KB	2147483648 KB	4096 KB	log only