sp_updatestats and Update Statistics

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!!!

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

Love this skit!

Church at the Crossings Message

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

This is too neat!

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

65 Must-Know CSS Strategies and Tutorials

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

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'

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...

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...