Thursday, December 01, 2011
I’m presenting in Omaha on Writing Faster SQL at 6PM on December 7th. You can find meeting details on the Omaha SQL Server User Group page. The meeting location requires an RSVP so building security has a list of attendees.
The presentation is a series of suggestions on improving performance. It ranges from simple things like comparing indexed columns to scalar values up to tips for reducing query compiles and asynchronous processing patterns. Nearly all of these come from specific issues I’ve encountered working on poorly performing SQL Servers.
Saturday, October 08, 2011
At the Summit you’ll see PASS announce the total attendance and the “total registrations”. The total registrations is the sum of the conference attendees and the pre-conference registrations. A single person can be counted three times (conference plus two pre-cons) in the total registration count.
When I was doing marketing for the Summit this drove me nuts. I couldn’t figure out why anyone would use total registrations. However, when I tried to stop reporting this number I got lots of pushback. Apparently this is how conferences compare themselves to each other. Vendors, sponsors and Microsoft all wanted to know our total registration number. I was even asked why we weren’t doing more “things” that people could register for so that our number would be even larger. This drove me nuts.
I understand that many of you are very detail oriented. I just want to make sure you understand what numbers you’re seeing when we include them in the keynote at the Summit.
Tuesday, October 04, 2011
The last two years we’ve put the Board in front of the members and taken questions. We’re going to do that again this year. It will be in Room 307/308 from 12:15 to 1:30 on Friday.
Yes, this time overlaps with the Birds of a Feather Lunch and the start of afternoon sessions – but only partially. You can attend the Q&A and still get to parts of both of those. There just isn’t a great time to do this. Every time overlaps with something. We can’t do it after the last session on Friday. We can’t fit it between the last session and the evening events on Wednesday or Thursday. We had some discussion around breakfast time but I didn’t think that was realistic. This is the least bad time we could come up with.
Last year we had 60-70 people attend. These are the items that were specific things that I could work on:
- The first question was whether to increase transparency around individual votes of Board members. We approved this at the Board meeting the following day. The only caveat was that if the Board is given confidential information as a basis for their vote then we may not be able to disclose individual votes. Putting a Director in a position where they can’t publicly defend the reason for their vote is a difficult situation. Thanks Kendal!
- Can we have a Board member discretionary fund? As background, I took a couple of people to lunch so we could have a quiet place to talk. I bought lunch but wasn’t able to expense it back to PASS. We just don’t have a budget item for things like this. I think we should. I would guess the entire Board would like it also. It was in an earlier version of the budget but came out as part of a cost-cutting move to balance the budget. I’d like to see it added back in but we’ll have to see.
- I know there were a comments about the elections. At this point we had created the Election Review Committee. I’ve already written at length about this process.
- Where does IT work go? PASS started to publish our internal management reports starting in December 2010. You can find them on our Governance page. These aren’t filtered at all and include a variety of information about IT projects. The most recent update had roughly a page of updates related to IT. Lots of the work was related to Summit and the Orator tool that we use to manage speaker submissions.
- There were numerous requests that Tina Turner not be repeated. Done. I don’t think we’ll do anything quite like that again.
- We had a request for a payment plan for Summit. We looked into this briefly but didn’t take any action. We didn’t think the effort was worth the small number of people that would use it. If you disagree, submit this on our Summit Feedback site and get some votes.
- There were lots of suggestions around the first-timers events – especially from first timers. You can find all our current activities related to first-timers at the First Timers page on the Summit web site. Plus links to 34 (!) blog posts on suggestions for first-timers. And a big THANK YOU to Confio and Red Gate for sponsoring this.
I hope you get the chance to attend. These events are very helpful to me as a Board member. I like being able to look around the room as comments are being made and see the audience reaction. It helps me gauge the interest in an idea.
I’d also like to direct you to the Summit Feedback site. You can submit and vote on ideas to make the Summit a better experience. As of right now we have the suggestions from last year still up. We may reset these prior to the Summit though.
Tuesday, September 27, 2011
Our budget situation is a little different this year than in years past. We were late getting an initial budget approved. There are a number of different reasons this occurred. We had different competing priorities and the budget got pushed down the list. And that’s completely my fault for not making the budget a higher priority and getting it completed on time.
That left us with initial budget approval in early August rather than prior to June 30th. Even after that there were a number of small adjustments that needed to be made. And one large glaring mistake that needed to be fixed. We had a typo in the budget that made it through twelve versions of review. In my defense I can only say that the cell was red so of course it had to be negative! And that’s one more mistake I can add to my long and growing list of Mistakes I’ll Never Make Again.
Last week we passed a revised budget (version 17) with this corrected. This is the version we’re cleaning up and posting to the web site this week or next.
Monday, August 15, 2011
I did another SQL Server migration over the weekend that dealt with linked servers. I’ve seen all kinds of odd naming schemes and there are a few I like and a few I suggest you avoid.
Don’t name your linked server for its IP address. At some point whatever is on the other end of that IP address will move. You’ll probably need to point your linked server to a new IP address but not change the name of the linked server. And then you’ve completely lost any context around this. Bonus points if a new SQL Server eventually ends up at the old IP address further adding confusion when you’re trying to troubleshoot.
Don’t name your linked server based on its instance name. This one is less obvious. It sounds nice to have a linked server named [VSRV1\SQLTRAN01]. You know what it is and it’s easy to use. It’s less nice when you’ve got 200 stored procedures that all reference this linked server but the database they reference has moved to a new instance. Now when you query this you’re actually querying a different instance.
(Please note: I’m not saying it’s a good idea to have 200 stored procedures that all reference a linked server. I’m just saying it’s not all that uncommon.)
Consider naming your linked server something that you can easily search on. See my note above. You can also get around this by always enclosing the name in brackets. That is harder to enforce unless you use some odd characters in it.
Consider naming your linked server based on the function. For example, I’ve had some luck having a linked server named [DW] that points to our data warehouse server. That server can change names or physically move and all I need to do is update the linked server to point to the new destination. The descriptive name of the linked server is still accurate. No code needs to change and people still know what it is just by looking at it.
Consider naming your linked server for the database. I’m still thinking through this one. It may mean you have multiple linked servers that point to the same instance. I’ve found that database names rarely change. It also makes it easier to move individual databases to new servers.
Consider pointing your linked servers to DNS entries and not IP addresses. I’ve done this for reporting databases and had some success. Especially for read-only snapshots that can get created on the main database or on the mirror.
What issues have you had with linked server names? What has worked for you? Where are the holes in my approach?
Friday, July 15, 2011
There are a two big items in flight that I want to discuss.
Elections
We recently published the ERC recommendations and new election procedures. Unfortunately getting from the recommendations to the new plan took longer than I’d hoped. And it was mostly me doing the taking longer part. Plus calling what I wrote “procedures” is really a stretch. It’s really more like a rough description of what the procedures should be. Add that to the fact that the Summit is nearly a month earlier this year and we have a scheduling problem. We can try to rush and get it done or go slow and make sure we get all the details right.
We’re still working through the critical dates but it looks like we won’t have the election completed prior to the Summit. Our bylaws require us to complete the election prior to the end of the calendar year – not prior to the Summit. Years ago we used to finish the election and announce the results at the Summit. Recently we’ve tried to complete the elections prior to the Summit. This year it looks like we won’t be done until after the Summit.
Budget
And if you thought the elections were behind you should see our budget! Our new fiscal year started July 1st and we don’t have an approved budget. Fortunately that’s not the end of the world. We can continue to pay vendors and work based on last year’s budget. We recently emailed version 10 to the Board for review. I believe we’ll either have or be voting on a final budget by the end of the month. After it gets approved we’ll get it cleaned up and published.
Bill Graziano
Executive Vice-President – Finance
PASS
Sunday, July 03, 2011
SQL Server 2008 started reporting the statement duration in microseconds in scripted traces. That’s one millionth of a second. Previously they were reported in milliseconds. Profiler automatically corrects for this and always displays in milliseconds regardless of the version of SQL Server that generated the trace. However SMO reported them in milliseconds or microseconds depending on what version of SQL Server generated the trace. Worse, SMO has no way to tell you what version of SQL Server generated a trace. Or what unit of measure was used to return the value.
That left me reading traces with no way to figure out the unit of measure for duration.
After a fair bit of research I’ve finally figured it out and TraceTune now displays all durations in milliseconds. In case you’re curious, it appears that traces from SQL Server 2008 and higher all start with the characters “FFFE”.
Sunday, June 26, 2011
TraceTune now shows a graphical view of logical reads for each SQL statement in a trace file. The width of the colored bar in the screen capture below is the percentage of logical reads for that statement. The absolute number of reads is shown to the right.
Any statement that has a user entered comment is shown in bold. If you hover over the statement it will show the most recent comment for that statement.
Thursday, June 23, 2011
I’ve been using the online version of ClearTrace more and more lately. When I get to a new client it’s just much easier to upload a trace file rather than install ClearTrace.
That means I’ve finally been adding more features to it. The two latest features are around ease of use.
You can now upload a ZIP file that contains a trace file. Trace files are already somewhat compressed. Putting it in a ZIP file further compresses it by a factor of 8X or 9X in my testing.
That means you can start with a 100MB trace and end up with a 10Mb-12MB ZIP file to upload. I’m consistently able to get over 150,000 events in a 100MB ZIP file. That gives me a pretty good look at a system.
The second part of this is that files are now processed asynchronously. After you upload a file you’ll be taken to a processing page that updates every few seconds with the number of rows processed. It generally takes under a minute to process a 100MB trace file but I *hated* staring at a blank screen.
Give TraceTune a try. It’s getting easier to use every day.
Wednesday, June 22, 2011
Last year after the election, the PASS Board created an Election Review Committee. This group was charged with reviewing our election procedures and making suggestions to improve the process. You can read about the formation of the group and review some of the intermediate work on the site – especially in the forums.
I was one of the members of the group along with Joe Webb (Chair), Lori Edwards, Brian Kelley, Wendy Pastrick, Andy Warren and Allen White. This group worked from October to April on our election process. Along the way we:
- Interviewed interested parties including former NomCom members, Board candidates and anyone else that came forward.
- Held a session at the Summit to allow interested parties to discuss the issues
- Had numerous conference calls and worked through the various topics
I can’t thank these people enough for the work they did. They invested a tremendous number of hours thinking, talking and writing about our elections. I’m proud to say I was a member of this group and thoroughly enjoyed working with everyone (even if I did finally get tired of all the calls.)
The ERC delivered their recommendations to the PASS Board prior to our May Board meeting. We reviewed those and made a few modifications. I took their recommendations and rewrote them as procedures while incorporating those changes. Their original recommendations as well as our final document are posted at the ERC documents page. Please take a second and read them BEFORE we start the elections. If you have any questions please post them in the forums on the ERC site.
(My final document includes a change log at the end that I decided to leave in. If you want to know which areas to pay special attention to that’s a good start.)
Many of those recommendations were already posted in the forums or in the blogs of individual ERC members. Hopefully nothing in the ERC document is too surprising.
In this post I’m going to walk through some of the key changes and talk about what I remember from both ERC and Board discussions. I’ll pay a little extra attention to things the Board changed from the ERC. I’d also encourage any of the Board or ERC members to blog their thoughts on this.
- The Nominating Committee will continue to exist. Personally, I was curious to see what the non-Board ERC members would think about the NomCom. There was broad agreement that a group to vet candidates had value to the organization.
- The NomCom will be composed of five members. Two will be Board members and three will be from the membership at large. The only requirement for the three community members is that you’ve volunteered in some way (and volunteering is defined very broadly). We expect potential at-large NomCom members to participate in a forum on the PASS site to answer questions from the other PASS members.
- We’re going to hold an election to determine the three community members. It will be closer to voting for Summit sessions than voting for Board members. That means there won’t be multiple dedicated emails. If you’re at all paying attention it will be easy to participate. Personally I wanted it easy for those that cared to participate but not overwhelm those that didn’t care. I think this strikes a good balance.
- There’s also a clause that in order to be considered a winner in this NomCom election, you must receive 10 votes. This is something I suggested. I have no idea how popular the NomCom election is going to be. I just wanted a fallback that if no one participated and some random person got in with one or two votes. Any open slots will be filled by the NomCom chair (usually the PASS Immediate Past President). My assumption is that they would probably take the next highest vote getters unless they were throwing flames in the forums or clearly unqualified. As a final check, the Board still approves the final NomCom.
- The NomCom is going to rank candidates instead of rating them. This has interesting implications. This was championed by another ERC member and I’m hoping they write something about it. This will really force the NomCom to make decisions between candidates. You can’t just rate everyone a 3 and be done with it. It may also make candidates appear further apart than they actually are. I’m looking forward talking with the NomCom after this election and getting their feedback on this.
- The PASS Board added an option to remove a candidate with a unanimous vote of the NomCom. This was primarily put in place to handle people that lied on their application or had a criminal background or some other unusual situation and we figured it out.
- We list an explicit goal of three candidate per open slot.
- We also wanted an easy way to find the NomCom candidate rankings from the ballot. Hopefully this will satisfy those that want a broad candidate pool and those that want the NomCom to identify the most qualified candidates.
- The primary spokesperson for the NomCom is the committee chair. After the issues around the election last year we didn’t have a good communication plan in place. We should have and that was a failure on the part of the Board. If there is criticism of the election this year I hope that falls squarely on the Board. The community members of the NomCom shouldn’t be fielding complaints over the election process. That said, the NomCom is ranking candidates and we are forcing them to rank some lower than others. I’m sure you’ll each find someone that you think should have been ranked differently.
I also want to highlight one other change to the process that we started last year and isn’t included in these documents. I think the candidate forums on the PASS site were tremendously helpful last year in helping people to find out more about candidates. That gives our members a way to ask hard questions of the candidates and publicly see their answers.
This year we have two important groups to fill. The first is the NomCom. We need three people from our membership to step up and fill this role. It won’t be easy. You will have to make subjective rankings of your fellow community members. Your actions will be important in deciding who the future leaders of PASS will be. There’s a 50/50 chance that one of the people you interview will be the President of PASS someday. This is not a responsibility to be taken lightly.
The second is the slate of candidates. If you’ve ever thought about running for the Board this is the year. We’ve never had nine candidates on the ballot before. Your chance of making it through the NomCom are higher than in any previous year. Unfortunately the more of you that run, the more of you that will lose in the election. And hopefully that competition will mean more community involvement and better Board members for PASS.
Is this the end of changes to the election process? It isn’t. Every year that I’ve been on the Board the election process has changed. Some years there have been small changes and some years there have been large changes. After this election we’ll look at how the process worked and decide what steps to take – just like we do every year.
Sunday, June 05, 2011
The latest release of ClearTrace (Build 38) now shows the execution history of a particular statement.
You’ll need to save the trace files to a trace group instead of just using the default. That’s as easy as typing something into the trace group name when you upload the trace. I usually put the server name in this field.
Build 38 also re-enables support for statement level events. If your trace includes RPC:StmtCompleted or SQL:StmtCompleted events those will be processed and save. In the results tab you can choose to view statement level or batch level events.
Please note that saving statement level events in a trace can generate HUGE trace files very quickly.
Friday, June 03, 2011
Earlier this year we published preliminary fiscal year 2010 financials to the Governance page on the PASS web site. Please remember that FY10 runs from July 1st, 2009 through June 30th, 2010 and includes the November 2009 Summit. We do our fiscal year this way so that the Summit falls earlier in the fiscal year.
The financials we had posted were P&L numbers at the portfolio level. Prior to this we had posted our detailed budget but only posted the auditors report at the end of each year. Today we updated our published financials to include:
- Pre-audit actuals from FY10 at the same level as our budget. The document has both actuals and budget for FY10 side by side. This is over 20 pages of detailed financial information covering hundreds of line-items.
- A letter describing key differences between our budget and actuals. I walked through each line item where the difference was greater than $25,000 and explained what happened and why.
- We updated the financial graph going back to 2003 to include FY10.
This update should “close the loop” on our financials. You can now start with the published budget and compare it to the finished financials at the same level of detail. We also plan to publish the auditor’s report when that is completed -- as we do every year.
Overall I’m very happy with how FY10 turned out. Keep in mind that this was the November 2009 Summit so we were still facing economic challenges. With all that we were roughly break-even showing a $15,000 profit on $3.9 million of revenue. I didn’t find anything shocking in reviewing our actual vs. budget but there were a few things that needed explanation. You can see those in the letter on the governance page.
Please keep in mind that these are the actuals from our operating financials. The auditor may have us make adjustments for depreciation or other financial transactions. We may also account for certain transactions differently for tax purposes than we do for financial reporting purposes. I feel these financial statements give you the clearest picture of how our organization spends its money.
We were late publishing these this year. We were working through some tax issues and that delayed our ability to file our final tax forms which delayed this process. In hindsight I should have published these documents as soon as we had them and not waited for the tax issues. We’ll do this better in the future.
And on a final note, you don’t need to login to view these documents. If you have any questions you can post them here. If we get more than a few questions we may see about creating some forums for financial issues on the PASS web site.
Monday, May 23, 2011
I’m going back to speak in St. Louis next month. I didn’t make it last year and I’m looking forward to it. You can find additional details on the St. Louis SQL Server user group web site. The meeting will be held at the Microsoft office and I’ll be speaking at 1PM.
I’ll be speaking on the procedure cache. As people get better and better tuning queries this is the next major piece to understand. We’ll talk about how and when query plans are reused. The most common issue I see around odd query plans are stored procedures that use one query plan but the queries run completely different when you extract the SQL and hard code the parameters. That’s just one of the common issues that I’ll address.
There will be a second speaker after I’m done, then a short vendor presentation and a drawing for a netbook.
Monday, April 04, 2011
I wrote a small utility to script some key SQL Server configuration information. I had two goals for this utility:
- Assist with disaster recovery preparation
- Identify configuration changes
I’ve released the application as open source through CodePlex. You can download it from CodePlex at the Script SQL Server Configuration project page.
The application is a .NET 2.0 console application that uses SMO. It writes its output to a directory that you specify.
Disaster Planning
ScriptSqlConfig generates scripts for logins, jobs and linked servers. It writes the properties and configuration from the instance to text files. The scripts are designed so they can be run against a DR server in the case of a disaster. The properties and configuration will need to be manually compared.
- Each job is scripted to its own file.
- Each linked server is scripted to its own file. The linked servers don’t include the password if you use a SQL Server account to connect to the linked server. You’ll need to store those somewhere secure.
- All the logins are scripted to a single file. This file includes windows logins, SQL Server logins and any server role membership.
- The SQL Server logins are scripted with the correct SID and hashed passwords. This means that when you create the login it will automatically match up to the users in the database and have the correct password. This is the only script that I programmatically generate rather than using SMO.
- The SQL Server configuration and properties are scripted to text files. These will need to be manually reviewed in the event of a disaster. Or you could DIFF them with the configuration on the new server.
Configuration Changes
These scripts and files are all designed to be checked into a version control system. The scripts themselves don’t include any date specific information. In my environments I run this every night and check in the changes. I call the application once for each server and script each server to its own directory.
The process will delete any existing files before writing new ones. This solved the problem I had where the scripts for deleted jobs and linked servers would continue to show up. To see any changes I just need to query the version control system to show many any changes to the files.
Database Scripting
Utilities that script database objects are plentiful. CodePlex has at least a dozen of them including one I wrote years ago. The code is so easy to write it’s hard not to include that functionality. This functionality wasn’t high on my list because it’s included in a database backup. Unless you specify the /nodb option, the utility will script out many user database objects. It will script one object per file. It will script tables, stored procedures, user-defined data types, views, triggers, table types and user-defined functions. I know there are more I need to add but haven’t gotten around it yet. If there’s something you need, please log an issue and get it added.
Since it scripts one object per file these really aren’t appropriate to recreate an empty database. They are really good for checking into source control every night and then seeing what changed. I know everyone tells me all their database objects are in source control but a little extra insurance never hurts.
Conclusion
I hope this utility will help a few of you out there. My goal is to have it script all server objects that aren’t contained in user databases. This should help with configuration changes and especially disaster recovery.
Tuesday, March 29, 2011
Register by March 31st and save $200. On April 1st we increase the price. On July 1st we increase it again. We have regular price bumps all the way through to the Summit. You can save yourself $200 if you register by Thursday.
In two years of marketing for PASS and a year of finance I’ve learned a fair bit about our pricing, why we do this and how you react to it. Let me help you save some money!
Price bumps drive registrations. We see big spikes in the two weeks prior to a price increase. Having a deadline with a cost attached is a great motivator to get people to take action.
Registering early helps you and it helps PASS. You get the exact same Summit at a cheaper rate. PASS gets smoother cash flow and a better idea of how many people to expect. We also get people that are already registered that will tell their friends about the conference.
This tiered pricing lets us serve those that are very price conscious. They can register early and take advantage of these discounts. I know there are people that pay for this conference out of their own pockets. This is a great way for those people to reduce the cost of the conference. (And remember for next year that our cheapest pricing starts right after the Summit and usually goes up around the first of the year.)
We also get big price bumps after we announce the program and the pre-conference sessions. If you wrote down the 50 or so best known speakers in the SQL Server community I’m guessing we’ll have nearly all of them at the conference. We did last year. I expect we will this year too. We’re going to have good sessions. Why wait? Register today.
If you want to attend a pre-conference session you can always add it to your registration later. Pre-con prices don’t change. It’s very easy to update your registration and add a pre-conference session later.
I want as many people as possible to attend the Summit. It’s been a great experience for me and I hope it will be for you. And if you are going to go, do yourself a favor and save some money. Register today!