I recently learned about a new trace flag which has been introduced in SQL Server 2008 R2 SP1 (and SQL Server “Denali”). This trace flag, number 2371, changes the way that the SQL Server figures out when auto-stats should kick in.
Up until now auto-stats was fixed to kick in every time that the column (or table in the older versions) had 20% + 500 rows change. With the new flag turned on auto stats now stats static at the old value up until the row has 25,000 rows in it. At this point the percentage of rows that need to change before auto-stats kicks in gets smaller and smaller as the table grows. As you can see from the included graph as the number of rows in the table gets higher the percentage of rows that needs to changes gets very small with tables in the 100M row range needing only 0.31% of the rows to change (310,000 rows). Because of this the odds of a large table having auto stats kick in is much greater than before, in the range of 20 to 60 times greater according to the SAP on SQL Server blog (the same place I stole the graph from). As Microsoft points out in their blog post by updating stats this much more frequently new stats could be generated in just a couple of days instead of waiting for weeks or months for new stats to be generated.
Like everything in SQL Server, nothing is free. Keep in mind that when auto-stats kicks in for a column or table all the execution plans for that table or column will be invalidated and new plans will be generated. If auto-stats were to kick in, in the middle of the day this could cause performance problems while the new plans are being generated which would be seen as slow query run times and increased CPU load on the SQL Server as well as increased IO load when you aren’t expecting it as update stats goes through reading the data in the table.
This trace flag was specifically designed for the “stupid large” databases in the world that are running OLTP workloads such as when running SAP on SQL Server which can generate some insanely large OLTP tables.
Microsoft currently doesn’t have an recommendations on when to turn on this trace flag, as finding companies with “stupid large” databases to test stuff on is tricky at best.
If you have one of these “stupidly large” databases and you find yourself having to manually update stats (probably via a scheduled job unless you really hate yourself) and you have SQL Server 2008 R2 SP1 installed then you might want to check out this trace flag and see if it helps you out. Personally I’ve got a system I’d love to try this out on, and I’ll be getting that system upgraded to SP1 shortly so I can give it a try.
I wouldn’t expect this trace flag to be back ported down to SQL Server 2008 or SQL Server 2005.
This year at the SQL PASS summit I’m once again taking a lunch table for the Birds of a Feather lunch on Friday. And even better than last year I don’t have a session that I’m presenting right before lunch so I’ll actually be there on time (last year I was like 30 minutes late because of Q&A in my session).
The topic of the Birds of a Feather lunch is storage (officially it’s “Storage with mrdenny”). If you’d like to chat about storage and SQL Server come on by and have some lunch (you’ve got to eat anyway right) and lets talk over some storage problems that you may be having in your shop, or just answer some questions that you have.
No I’m not talking about something to take notes on during the sessions (you’ll want that as well, either paper or digital). But you’ll want to bring some business cards. Even if you aren’t a consultant, business cards are a great way to give someone your contact info so that you can get in touch later to talk about what ever it is that needs following up on.
You may have a technical question that you want to give someone more info about, or a business idea, or maybe you ran into someone who works in the same area of the world as you and you want to get together for lunch and chat somewhere slightly less crazy than the summit. Being able to hand out cards is a much better idea than having everyone write your info down on their hand like back in high school. One spilled drink and all that contact info would be lost.
Now personally I’ve gone a step further and made it even easier to get my contact info from my card into your phone or computer. When you look at the back of my cards you’ll see a Microsoft Tag that will automatically put my contact info into your phone when you use the Microsoft Tag viewer to take a picture of it. These tags are great, and free because personally I hate entering a few dozen new contacts into Outlook (I’ve got an app on my phone call CamCard that will do most of the work these days). And the logo that the Microsoft Tag uses is all colorful adding a little extra splash of color. If you don’t want to use a Microsoft tag, you can also use a QR Code to do the same kind of thing.
Now it’s probably to late to order cards in time for the PASS summit without paying a fortune for shipping, but if you don’t have any you can hit up Office Max or the Fed Ex stores (the ones that used to be Kinko’s) as they both have same day printing options that look OK (the paper stock they use isn’t the best) and won’t kill you on price. If you are really desperate you can grab the print at home kits. I’ve tried these and wasn’t all that thrilled with the results, but I was printing on a ink-jet printer. If you have access to a nice color laser printer at the office you might be better results.
If you run into someone that you’d like to get in touch with later, give them your card and get one back from them. We pay for these things for a reason, lets use them to make some new friends.
Well this weekend was a very stressful weekend for about 764 of the Microsoft MVPs world wide, including myself. You see this weekend was October 1st which is a Microsoft MVP award day. This means that these 764 Microsoft MVPs woke up this morning and frantically checked there email looking for a notice from Microsoft saying that they had been awarded the Microsoft MVP award again. There were 143 MVPs who received the award for the first time.
Apparently someone at Microsoft enjoys my writing and speaking as well as getting yelled at by me. Because when I got up on the 1st I found a nice email in my mailbox from Microsoft telling me that I had been awarded the Microsoft MVP award again.
Congrats to all the other October MVP award winners both new and re-awarded MVPs.
May thanks to my wife Kris for letting me spend all on my free time working on various stuff for the community. And thanks to everyone in the community to buying books, coming to my webcasts and to come see me speak at the various events. The SQL Server MVP award wouldn’t exist without the great community that we have here in the SQL Server community.
For those of you attending the SQL PASS summit, if you are interested in picking up a copy of the second edition of the MVP Deep Dives book you’ll have a chance to not only pick it up at the summit, but also get it signed by a large portion of the books authors.
There will be two signing sessions, the first on Wednesday from 1pm until 1:30pm between the expo and dining halls (4th floor). The second session will be Friday morning from 7:15am until 8:am in room 211 on the second floor (by the escalators). I make no claims to how awake, sober or polite the MVPs will be at 7:15am on Friday morning.
If you are wondering when you’ll have time to pick up the book before the Wednesday book signing session, the onsite book store will be open all day or Wednesday from the time the exhibit hall opens (if not earlier) and they are opening on Tuesday evening during the welcome reception so you can pick up a copy then.
If you can’t make it to the signing, or the author you were looking for isn’t there for some reason because they are still drunk then look for people wearing the “MVP Deep Dives Author” flag from their badges. If you haven’t been to the submit before, you’ll see what I mean pretty quickly.
See you at the summit.
So this is the all important SQL Karaoke wrist band post. In order to get your SQL Karaoke wrist band you must head over the NEC booth on Wednesday during the exhibit hall hours and talk to the fine folks that will be working there. If they decide that you are worthy they will grace you with an all powerful wristband which will get you some free drinks at Bush Garden that night. Now keep in mind that there are a limited number of wristbands available.
Now myself and our awesome NEC rep will be arriving at 9:30 at Bush Garden (possibly even together). Anything you drink before 9:30pm IS ON YOUR TAB. Once the tab is open its first come, first served. Don’t be greedy, we have a budget so don’t be drinking all of it. We want the budget to last so that everyone can enjoy our sponsors amazing generosity, so I’ll say it again, don’t be a putz.
When the drinks run out, they run out. If you get there late and the budget is gone, then sorry. Crying to me will get some big old puppy dog eyes with my most polite and heartfelt “so sorry”. Once the budget runs out I’ll grab the mic and let you know that you’ll need to open your own tab in a way that is truly “me”.
If when you get to the NEC booth the folks there don’t know what you are talking let me know on Twitter or email and I’ll get my guy down there to straighten them out. There shouldn’t be any problems, we’ve got their marketing department running the show there.
Don’t forget to tip the nice lady that brings you the drinks and the KJ who lets us abuse him for the night.
If you haven’t had a chance to get signed up for SQL Server Days in Belgium (November 14th and 15th) now is the time. The early bird discount ends Friday meaning that when you remember to sign up on Monday the price goes from €79 to €99.
Now if you aren’t interested in saving €20 then by all means wait until Monday, but if it was up to me I’d want to save that €20 for someone else (good beer comes to mind), so go and sign up now. I’ll wait.
Not sure why you want to go to SQL Server Days?
The answer to that is simple. There is a lot of top name speaker talent flying in, and they want to meet you at the event. You’ve got some of the biggest speakers in the community speaking at this event. Chris Webb (blog) is going to be speaking on SQL Server Analysis Services and the new DAX queries. Jennifer Stirrup (blog | @jenstirrup) will be talking about the iPad and PowerPivot and how to get BI solutions into the hands of mobile users. Kevin Kline (blog | twitter) will be talking about thoubleshooting SQL Server and much, much more.
Now stop reading and go sign up already and save yourself the €20. I’ll see you there on the 14th and 15th.
Recently I was working with a client, and the client wanted to send me a batch of SQL Server execution plans (about 300 of them) in a single file. Normally I would just connect to the SQL Server and view the queries from there, but the client hadn’t gotten me VPN access to their network yet, so that wasn’t an option. I didn’t really want to copy and paste the plans from the text file that they sent me one at a time, so I threw a little app together and figured that I should post it online in case any one else runs into this problem. Just give it the big text file and point it towards an empty folder then click go.
RedGate’s SQL In The City is coming to the amazing Skirball Center in Los Angeles just a couple of weeks after the PASS summit. This is a great change to see some great presentations giving by some of the top speakers in the SQL Server community (who all happen to be great friends of mine).
The speakers (including myself) are:
- Steve Jones, SQL Server MVP
- Brad McGehee, SQL Server MVP
- Grant Fritchey, SQL Server MVP
- Denny Cherry, SQL Server MVP
- Kalen Delaney SQL Server MVP
- Aaron Nelson SQL Server MVP
- Ike Ellis SQL Server MVP
All this for the amazing low price of zero, zip, nada, nothing. If you’ll be in town, or you can be in town this is a great opportunity to get some absolutely amazing SQL Server training. Now is the time to register for the event and reserve yourself a seat before they are all gone.
See you at the Skirball center, and at the party after.
As you may or may not know, the SQL Server MVPs put out a book last year called the SQL Server MVP Deep Dives in which the proceeds of the book went to charity. The MVPs have done a second volume of the book with once again the proceeds of the book going to charity. This time around we as a group chose Operation Smile as the charity. Now this isn’t an update to the first book, but a totally different book done under the same premise.
A large number of the MVPs submitted topic ideas and those who’s ideas were accepted (they didn’t want 10 chapters on PowerShell for example) were asked to write a chapter on that topic. My chapter was on Storage (shocking I know) and got put in as Chapter 5. You can see the chapter list over on Manning’s site (they are the publisher). Currently you can’t pre-order from Manning, but you can from Amazon. If you wait until you can get the book from Manning directly the charity will get a few extra bucks, but if you don’t want to wait we all totally understand.
Rumor has it that there will be another SQL Server Deep Dives book signing at the PASS summit this year. If you are interested in getting your book signed by a large number of the authors this is your chance. There aren’t that many times when this many of the authors are in the same room at the same time. There hasn’t been any announcement about where or when it will be, but you should be able to pick up a copy of the book at the PASS summit book store so you don’t have to lug a copy from home then back home again. I know that when the MVPs did the last MVP Deep Dives book the book store did sell out and they had to do a rush overnight over to get more copies for everyone.
I’ll see you at the PASS summit, and hopefully you’ll be carrying around a copy of the new SQL Server MVP Deep Dives Volume 2.