Left Outer Join

sp_updatestats and Update Statistics

2012-03-03T13:28:00.003-05:00

Before I get started on this one, I wanted to give thanks to RK Kuppãla for pointing me to the great article and code written by Michelle Ufford (aka SQLFool) - this has been a HUGE help!

I am blogging about this so that I can keep my thoughts in one spot and due to the potential length of the post. This needs to be introduced with the back story of how I got to this point. Our Student Information System is produced by a vendor. That vendor has certain specifications that we are required to follow - if they are not followed, the first thing their support folks point to is that we are not following protocol (although most of the time it is not the issue) when we have problems. The one that we get hit with a lot is the Maintenance Plans for rebuilding indexes and updating statistics not running to completion. Note that the maintenance plan rebuilt every index in the entire database and the update statistics run a full scan on every index in the database.

We determined that this was happening because the system is available 24/7 and a lot of the users were accessing data through the application that were in deadlock with the maintenance plan which wound up being the loser in the deadlock. Obviously the maintenance plan has no control over deadlock priority and neither do we when it comes to the application code.

There are also several processes in place that have to be scheduled each night to check student attendance and modify the all day information based on how many periods a student missed. Because of the amount of workload that these processes take, we had to switch things around as to when different plans run. The attendance schedule runs two different processes and takes about 4 hours in total. The maintenance plans were taking about 8 hours.

I decided to rebuild how the maintenance plans run by dividing them into groups according to what was needed for the attendance schedule and completing those first, then running the other tables according to order of how often they are "touched" during the day. I took out all temporary tables, any manual backups and lookup tables that rarely get changed. The only problem with this method is that it is no longer dynamic and would need to be reviewed if the vendor added tables when they do upgrades. This took a good hour off of what we had before, and it allowed me to start the attendance processes earlier since their tables were no longer being used by the plan.

The problem we are faced with now is that we are still 11 hours with these processes we have no good time to schedule others. I contacted the vendor when I looked over the previously mentioned script to see if they would approve of it - they did! I have been testing all week a modified version of the script and have gotten the index portion down to less than an hour (obviously variable based on percent of fragmentation). The problem is that the update statistics with the fullscan takes anywhere between 3 and 4 hours. Yes, that has me down to 4 to 5 hours which is a total reduction of 3, but my hopes were to get it down to less than 2 hours so that we can truly optimize our time.

Here (finally) is where the question comes in - doing research on sp_updatestats vs. update statistics has shown that most run the stored procedure nightly and then the other once a week or once a month. Because the vendor requires the fullscan on every index. what am I losing with running the stored procedure if it truly sees that an update is not required? Because this runs so quickly (usually less than 10 minutes), would it be a bad idea to run this a few times a day just to make sure everything is accurate?

I have also seen that when you rebuild an index, it fully updates the statistics anyway - if I have to stick with the update statistics, could I not just skip all of the ones that were rebuilt? Would it hurt and/or help to run sp_updatestats after the individual update statistics has run, or would that process actually cause issues with the previously run update because of the method it uses to scan?

Thanks in advance for any help!!!

2011-08-16T12:10:00.001-05:00

First Day of School - 3rd Grade and Pre-K!

2011-08-08T09:53:00.000-05:00

Love this skit!

2011-08-05T09:33:00.001-05:00

Church at the Crossings Message

2011-07-24T15:15:00.001-05:00

Hello. Have you heard about Church at the Crossings?

Why no, I have not. Where is it located?

It is in the same complex as the old WalMart building. We meet at 10:30 AM every Sunday.

Really? That seems earlier than other churches in the area.

You are correct. Because we meet earlier, we are also out earlier and you can beat the lunch crowds.

That is awesome. How is the music?

We have very upbeat music with electric guitars and drums - it is more of a band atmosphere.

Excellent - but will my kids enjoy that type of service.

Your children are always welcome to stay with you in the service. However, we have a superb Crossings Kids team for babies and toddlers, preschoolers and school age children through twelve years of age. This experience is geared towards their age group so that they can experience worship and learning on their level.

Wow - this sounds like a place perfect for me. I will see you there next Sunday! Woo Hoo!

Olympics Mobile TV

2010-02-13T14:28:00.001-05:00

This is too neat!

Exclusive Winter Olympics news & widgets at NBC Olympics.com!

65 Must-Know CSS Strategies and Tutorials

2009-08-19T14:53:00.003-05:00

Adding details and advanced CSS techniques to projects when unasked can increase the value of yourself as a designer, so learning new things is practical as both a personal and business move. I encourage everyone to try out at least a few of these techniques you are unaware of. Even if it’s hard to imagine a practical use for them right now, it can be incredibly useful in future projects, or you may come up with a revolutionary idea based on these strategies.

Either way — try them out. Put them into practice, and brainstorm ways to include them in your current design process. It can do any designer or developer a lot of good. Constantly learning new techniques is the key to becoming a successful designer or developer. READ MORE

Twelve Tips for Techies Trying to Resist Recessions

2009-08-19T14:45:00.004-05:00

To me things seem very bad right now, but on some level what’s going on isn’t quite as bad for the tech sector as things were during the dot.com crash. Now this isn’t to say that folks aren’t hurting, but that last crash seemed very specific to the tech sector while this crash seems to be hitting everyone everywhere. My gut feeling is that the worst may not be over, so with this in mind here are my tips (both small and large) from what I’ve learned:
READ MORE

Why So Serious? Obama/Joker Poster Draws 'Outrage'

2009-08-11T14:34:00.004-05:00

Read the full story here
If there's one thing about liberals, it's that they're predictably predictable. In their eyes, it is more than acceptable to mock Sarah Palin and her family, using descriptions that range from rude to crude. When President Bush was in office, he was the focus of daily ridicule from left-wing journalists and bloggers.

However, no one better make fun of Barack Obama or the thought police will express "outrage" and brand you a racist. That's exactly what is happening now as a poster of Obama in "Joker" makeup is popping up on walls and city streets. My question to the lefties: Why so serious?

Frustrations...

2009-06-18T07:49:00.002-05:00

I absolutely hate those stupid Kroger commercials they have. People talking about how much fresher their fruit is than everybody else's? How do they know? Most folks I know have one store they like going to. So unless you have some independent "mystery" shopper going out or an investigative reporter going and checking out fruit at every store and comparing them, I would say that these comments are crap.

I heard a commercial this morning talking about the meats. Now I understand that meat quality can differ from store to store, but there is a baseline that has to be met and that should be well over anyone's expectations. This one woman was talking about how Kroger's meat specifically was tender and juicy. OK, unless this lady eats meat raw, isn't that subject to the way it is cooked? You're going to tell me you can't go buy meat anywhere else and after you cook it to the same specifications it is not tender and juicy?

Hey - Guess what folks, the name of the store is Kroger - K-R-O-G-E-R - go look at the front of the building. There is no "S" on the end. It is not on their grocery bags, their carts or their sales flyers. And I am not talking about people that use it in the possessive sense like "Kroger's meat is magically better and grants my wishes when I rub it like a lamp" or in the plural sense like "Of all the Krogers in the metro Atlanta area, mine is the best." I am specifically talking about folks that think the name of the store is Krogers - like "My local Krogers store carries Unicorn steaks."

OK, rant over...

2009-06-15T07:46:00.001-05:00

Really looking forward to the new Dream Theater Release!

Amazing Video

2009-04-26T12:14:00.001-05:00

Found this via Leo Laporte - this is really cool!

Noteboek from Evelien Lohbeck on Vimeo.

Qik on the Blackberry Storm

2009-04-19T13:20:00.003-05:00

I have fooled around with this app for the last few days and it has the potential to be really good. It is not true live streaming on the Blackberry Storm. It may say it is live, but it doesn't actually start uploading until after you stop the recording on the device (which is not truly live).

The version I had was 0.1.4 and I played around with some of the settings - it only gave me a choice of CDMA or to do nothing in the upload options. I decided to check and see if there was a newer version. I downloaded again and it was 0.1.8 - this gave me the 3G option finally. However, the speed of upload did not improve at all - still extremely slow in my opinion. Also, I had a couple of fewer choices in the menu and the application would not close at all. I have to do a battery pull each time I open the app even if I don't record a video.

Without a doubt it could be the OS I am running - it is a Hybrid 4.7.0.114 - still the app would shut down properly before the upgrade, so that has me baffled. Finally, if you are lacking in device memory, it is nearly impossible to run this app because it brings the entire phone to a screeching halt if you try to navigate with it open (thus the constant battery pull).

I wrote about this on the Crackberry.com Comments section for the announcement of the release of a Storm version.

I Love This Skit...

2009-04-14T21:17:00.001-05:00

I have probably watched this 20 times, but I think it is one of the funniest things I have ever seen...

Crazy Monday

2009-04-13T08:52:00.003-05:00

So far this morning it has taken almost 2 hours for my e-mail to get settled, although I still don't think it is working correctly. Normally, Mondays are pretty wild anyway, but when you add a week of Spring Break on top of major Thunderstorms, all you-know-what breaks loose. Now that my e-mail seems to be straightened out, it is extremely slow. We have about 10 schools that have either lost power completely or have no network connectivity nor phones. Plus, if all of this stuff wasn't enough, people are having trouble logging into Active Directory based authentication applications not realizing that it is all related (and therefore my phone is ringing off the hook). Not to mention that the lights keep flickering making the battery backups click and beep each time it happens. Perhaps I will be able to get something done by lunch time today?!?!

Funny Video...

2009-04-05T15:07:00.000-05:00

Had to share...

Dishwasher Blues...

2009-03-30T21:17:00.002-05:00

It has been crazy the last couple of days with our dishwasher. There was a pool of water at the bottom of the unit right below the heating element as if the pump wasn't draining all of he water out. After everything I had read online, we had determined that the pump in question was at least $150.00. My thought was that it would be easier to go ahead and purchase a brand new dishwasher. This one is nearly 10 years old, and it has served us well.

So I started taking the thing apart - flipped the breaker, popped the kick plate, unscrewed the brackets and slid it out as far as I could. I unscrewed the power box, removed the wire nuts and the ground screw. I loosened the drain tube and got ready to remove the water source. I went through every tool I have in my storage containers - not the first adjustable wrench in the entire house...

I got really frustrated and got what dishes were in the sink ready to wash by hand. At this point I figured out that the drain was clogged. I read somewhere that this could be an issue, even if the tube from the dishwasher to the drain isn't clogged. I took a plunger to the sink and cleared out the mess. I put everything back together and tested the unit - it drained just like it was supposed to this time! So I have put everything back and we are good.to.go. Hopefully this resolved the issue and we won't have to spend ANY money for a while on a major appliance.

Letter shock for grieving parents...

2009-03-26T08:45:00.003-05:00

Letter shock for grieving parents - News - Manchester Evening News

The parents of a British teen, who died suddenly two months ago, received a disturbing letter from the girl's high school saying she needs to improve her attendance or can't go to the prom, the Manchester Evening News reported.

Posted using ShareThis

FOR and NET FILE

2009-03-25T14:35:00.005-05:00

I was presented with the task of figuring out how to kick users off of a server who did not properly log off at the end of their work day. These folks would unknowingly hang on to a file that pretty well kinked up the entire schedule. The solution to this seems easy enough, right? Problem is, most of the scripts that are out there are an all-or-nothing solution. Most of our users utilize a terminal server to get to the app that we want to disable when our nightly processes are to begin. Those users that are directly connecting through a mapped drive would not be included in a NET SESSION /DELETE command. Also, that could potentially knock off someone that may be connected to that server for another purpose that didn't include the application in question.

In my search for other possibilities in the NET suite of commands, I found NET FILE. This lists all of the open files on the machine the command is executed on with the ID, File path and name, user id and 1 or 0 for locked file indicator. Since I didn't want to knock out the main user id that connects up the middle-ware, I had to find a way to tell it to kick out all users except for one and only those that have locked files. This is where I found that the FOR command was helpful. I was able to get it to pull the three fields I needed into variable, use IF condition to test for the values I wanted and, if it found them, it passed the ID into the NET FILE ID /CLOSE command.

We tested it last night as a batch file in a scheduled task and it worked great! I am excited that I have discovered this (although it has been around forever) and the potential that it has to make a lot of tasks run more smoothly or even be completely automated!

Georgians help needed...

2009-03-24T14:45:00.003-05:00

To find a spell checker...

Schönes Jubiläum!

2009-03-24T11:39:00.002-05:00

8 years ago today my wife and I began our life together as one. It has not been the smoothest of roads, but I wouldn't give anything for it! Just like anyone else, we've had our ups and downs, but we got throught it... together. There have been many mileposts along the way - home ownership, vehicle ownership, many churches, many jobs, but I would say the best thing that has happened to us has been our boys. Our two little men are growing up fast. I can't thank my wife enough for giving me such magnificent gifts - her hand in marriage, her trust and belief in me and those two little miracles that sleep in the next room every night. I hope to one day be able to show her how much the three of them mean to me, but I don't know if words will ever be able to express the depth, height, width and length of its measure. All I can offer is I Love You!!!

I'm Baaaaaaaack...

2009-03-23T13:41:00.002-05:00

Don't know why I have not posted in so long. I promise I will try to do better.

We had a rough night with the boys last night. With the addition of the bunk beds I think the baby is out of his comfort zone. Needless to say, big brother has a hard time sleeping with full on crying going on in the bed below. So the little one came and slept with us last night. He moves constantly - I don't know that any of us slept much at all. It will be interesting to see what happens this evening...

2008-11-20T10:23:00.001-05:00

At home sick - caught what the boys had...

2008-11-13T13:34:00.001-05:00

Trying out ping.fm for the first time

Post Turtle

2008-10-16T08:02:00.003-05:00

While suturing a cut on the hand of a Texas Rancher whose hand had been caught in a gate while working cattle, the doctor struck up a conversation with the old man.

Eventually, the topic got around to Obama and his bid for the presidency.

The old rancher said, Well, ya know, Obama is a 'post turtle'.
Not being familiar with the term, the doctor asked, 'What is a post turtle'?

The old rancher said, When you're driving down a country road and you come across a fence post with a turtle balanced on top, that's a 'post turtle'.

The old rancher saw a puzzled look on the doctor's face, so he continued to explain.

'You know he didn't get up there by himself, he sure as heck doesn't belong up there, he doesn't know what to do while he is up there and you just wonder what kind of an idiot put him there in the first place.'