While doing some research for my Half Day SQL PASS Session with Stacia Misner (blog | @staciamisner) I discovered that some of the queries which the SSRS engine runs against the ReportServer database are less than perfect total crap. My specific problem is that there are key lookups on the bulk of the queries.
Granted most of the queries results are pretty small, but these queries run every 10 seconds, and crap running every 10 seconds adds up to a whole lot of crap.
Below are some indexes you can create to resolve these problems. Keep in mind that adding this will probably make Microsoft not help you if you call, so be sure to delete the indexes if you call CSS for support.
These indexes will minimize the index scans and key lookups. I can’t remove all of them because of the way that the queries and the schema are designed and I’m not about to go around changing the schema of the tables or hacking the inline code of the SSRS UI. So while these indexes won’t fix every problem, some is better then none.
CREATE NONCLUSTERED INDEX [mrdenny_IX_Notifications3] ON [dbo].[Notifications] ( [NotificationEntered] ASC, ProcessStart, ProcessAfter ) include (NotificationID) GO CREATE INDEX mrdenny_PK_Catalog on dbo.Catalog (ItemID) include (Path, Type, PolicyId) GO CREATE INDEX mrdenny_IX_Event_TimeEntered on dbo.Event (ProcessStart, TimeEntered) include (EventID) GO CREATE INDEX mrdenny_IX_BatchID on dbo.Event (BatchID, TimeEntered) include (EventID, EventType, EventData) with (drop_existing=on) GO CREATE INDEX mrdenny_IX_UpgradeID on dbo.ServerUpgradeHistory (UpgradeID DESC) include (ServerVersion) GO
The SQL PASS summit is quickly approaching, and with the excitement of large conferences comes the worry of what to do during the week. How am I going to meet people, what am I going to do after hours or during the day when there is a session that I don’t want to attend?
This is a question that myself and two good friends Andy Warren (blog | @sqlandy) and Steve Jones (blog | @way0utwest) were kicking around last night (it’s amazing what you can do over email when everyone is in a different time zone) and we’ve come up with a solution that will hopefully work for everyone, and the best part is that using it is free. If you’d like to go somewhere for dinner, post it up on eventbrite.com as a free event for people to attend with a limited number of attendees (if your event is free, then Event Bright doesn’t charge). Then to get yourself a little free publicity post the info up on the passsummitevents.info website. Once the event is approved (which I’ll try and do as quickly as humanly possible) it’ll be up on the site and it’ll be blasted out to the world under the #sqlfun hash tag by the SQL_Fun twitter account. (At the moment the links aren’t working on mobile browsers, I’m working with the developer of the site to get that fixed before the summit.)
The Event Brite info is great because it gives you a way to easily limit the attendee list, because as Andy points out in his blog post (which is much more professionally written than this one) “It’s a lot easier to get a table for four than a restaurant for 150.”
There are lots of big events that happen at the PASS summit such as the Welcome Reception, GameWorks party, SQL Photo Walk, SQL Karaoke (most of these are posted up passummitevents.info already) and the various vendor parties (if you are lucky enough to get an invite to those). What we are talking about with this setup is more informal smaller get togethers. If you want to go down to Pikes Place Market for Breakfast on Tuesday (which I highly recommend you do at least once while you are in Seattle) this would be a great thing to get out. You’ll find some great people to talk to and have some company for breakfast.
Andy has started everyone off with Donuts & Coffee with SQLAndy/Tuesday Morning, so if you are interested go and check it out. And if not then think about throwing something up there and I’m pretty sure that you’ll find some people who are interested in joining you.
So go and put a dinner or a morning walk around town up on the site, and see who responds. Personally I would love it to see 100+ items up there before the summit starts next week.
Now if this is your first time at the PASS summit, or your first time in Seattle at all then be sure to check out the first timers video that I recorded a few weeks ago. In it I’ve got some ideas for things to do around town that don’t require driving, drinking, or thinking about SQL Server.
See you in Seattle,
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.