Sunday, July 05, 2015
You can download the latest version of ClearTrace from my consulting site: scaleSQL Consulting.
I’ve decided to stop using the year as part of the version. I did that to match SQL Server branding but it doesn’t work for ClearTrace. The current build supports SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, and SQL Server 2014. It tries to load the most recent SMO and goes backwards until it successfully loads an SMO library. Always use the most recent version of ClearTrace and it should support any current SQL Server.
If you’re having issues with ClearTrace you can visit the new ClearTrace Support Forums. That’s the best place to ask questions or report bugs.
The latest build adds a number of features. The first is an option that doesn’t reprocess trace files. I regularly generate large traces that have many files and I’m too impatient to wait for them all to finish. The script I use generates a unique name for each trace file. This option allows me to skip the trace files that have already been loaded.
I also fixed a pseudo-memory leak that had been hanging around for a LONG time. One of my early uses of ClearTrace was for a client that had lots of prepared SQL. In the trace file I would find lots of sp_execute 32423445 or some such. That number identifies the prepared SQL statement. ClearTrace captures the statement that prepares that SQL and reports back the actual SQL executed. This was all being stored in memory. Either I wasn’t clearing it properly or I didn’t understand how it got cleared but this structure was ballooning up. I’d regularly fill up memory to just under 2GB and watch the application crash. Now the application clears this structure after each trace file. If you want better handling of prepared SQL just increase the trace file size. That will capture more of the prepared SQL statements.
Trace Groups allow you to group traces together. They are especially helpful to view changes over time. I typically have one trace group per server. I’ve seen users that have dozens and dozens of trace groups with all kinds of fancy names. So I made the Trace Group Name drop down wider and hopefully easier to work with.
If you capture statement level traces and you have lots of nested stored procedures you’ll see lots of EXEC statements in your list of statements. If you end up a few layers deep you can see LOTS of EXEC statements. So I added an entry to the Events drop down filter to exclude statements that start with EXEC. All it does is eliminate anything that starts with “EXEC”. So far it’s been helpful.
I also fixed the history tab to work better with statements. And by “better” I mean it actually works now. You should get a statements history without the application blowing up.
(And if you downloaded build 46 please update to build 47. It’s just minor bug fixes around how statements are displayed in the grid. Or not displayed as was happening in some situations.)
And that’s it. Give it a try and let me know any issues in the ClearTrace forums.
Wednesday, October 08, 2014
I’ve seen this error off and on in our environment for a while. The full error looks like this:
Login failed for user DOMAIN\login’. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: X.X.X.X]
Error: 18456, Severity: 14, State: 11.
Aaron Bertrand wrote the post I always start with on troubleshooting 18456 errors. I’ve always struggled tracking down the root cause of state 11 though. I’ve seen it intermittently occur and then clean itself up. One of the reported causes is difficultly connecting to a domain controller so that kind of fits.
But this time it was occurring and not going away. People have suggested deleting and recreating the login, running as Administrator, disabling UAC, etc.
In our case, the login had been given permission through a Windows Group. That group had been removed, thus the user no longer had permission. But rather than give a standard login failure, it gave the state 11 error. That left me heading down a rabbit hole until xp_logininfo cleared up that they just didn’t have access. My only guess is that because they’d logged in before there was some artifact left on the system and the result was a slightly different login failure error message.
I’m hoping this might help the next person tracking down the dreaded state 11.
Monday, July 14, 2014
SQL Saturday #300 is coming up right here in Kansas City on September 13th, 2014. This is our fifth SQL Saturday which means it's the fifth anniversary of our now infamous BBQ Crawl. We get together on Friday afternoon before the event and visit a few local joints. We've done nice places and we've done dives. We haven’t picked the venues yet but I promise you’ll be well fed!
And if you’re thinking about the BBQ crawl you should think about submitting a session. Our call for speakers closes Tuesday, July 15th so you just have time!
If you’re going to be at the event, contact me and I’ll get you added to the list.
Thursday, January 10, 2013
PASS launched a Global Growth Initiative in the Summer of 2011 with the appointment of three international Board advisors. Since then we’ve thought and talked extensively about how we make PASS more relevant to our members outside the US and Canada. We’ve collected much of that discussion in our Global Growth site. You can find vision documents, plans, governance proposals, feedback sites, and transcripts of Twitter chats and town hall meetings. We also address these plans at the Board Q&A during the 2012 Summit.
One of the biggest changes coming out of this process is around how we elect Board members. And that requires a change to the bylaws. We published the proposed bylaw changes as a red-lined document so you can clearly see the changes.
Our goal in these bylaw changes was to address the changes required by the global growth initiatives, conduct a legal review of the document and address other minor issues in the document. There are numerous small wording changes throughout the document. For example, we replaced every reference of “The Corporation” with the word “PASS” so it now reads “PASS is organized…”.
The biggest change in these bylaw changes is how the Board is composed and elected. This discussion starts in section VI.2. This section now says that some elected directors will come from geographic regions. I think this is the best way to make sure we give all of our members a voice in the leadership of the organization. The key parts of this section are:
The remaining Directors (i.e. the non-Officer Directors and non-Vendor Appointed Directors) shall be elected by the voting membership (“Elected Directors”). Elected Directors shall include representatives of defined PASS regions (“Regions”) as set forth below (“Regional Directors”) and at minimum one (1) additional Director-at-Large whose selection is not limited by region. Regional Directors shall include, but are not limited to, two (2) seats for the Region covering Canada and the United States of America.
Additional Regions for the purpose of electing additional Regional Directors and additional Director-at-Large seats for the purpose of expanding the Board shall be defined by a majority vote of the current Board of Directors and must be established prior to the public call for nominations in the general election. Previously defined Regions and seats approved by the Board of Directors shall remain in effect and can only be modified by a 2/3 majority vote by the then current Board of Directors.
Currently PASS has six At-Large Directors elected by the members. These changes allow for a Regional Director position that is elected by the members but must come from a particular region. It also stipulates that there must always be at least one Director-at-Large who can come from any region.
We also understand that PASS is currently a very US-centric organization. Our Summit is held in America, roughly half our chapters are in the US and Canada and most of the Board members over the last ten years have come from America. We wanted to reflect that by making sure that our US and Canadian volunteers would continue to play a significant role by ensuring that two Regional seats are reserved specifically for Canada and the US.
Other than that, the bylaws don’t create any specific regional seats. These rules allow us to create Regional Director seats but don’t require it. We haven’t fully discussed what the criteria will be in order for a region to have a seat designated for it or how many regions there will be. In our discussions we’ve broadly discussed regions for
- United States and Canada
- Europe, Middle East, and Africa (EMEA)
- Australia, New Zealand and Asia (also known as Asia Pacific or APAC)
- Mexico, South America, and Central America (LATAM)
As you can see, our thinking is that there will be a few large regions. I’ve also considered a non-North America region that we can gradually split into the regions above as our membership grows in those areas.
The regions will be defined by a policy document that will be published prior to the elections. I’m hoping that over the next year we can begin to publish more of what we do as Board-approved policy documents.
While the bylaws only require a single non-region specific At-large Director, I would expect we would always have two. That way we can have one in each election. I think it’s important that we always have one seat open that anyone who is eligible to run for the Board can contest. The Board is required to have any regions defined prior to the start of the election process.
Board Elections – Regional Seats
We spent a lot of time discussing how the elections would work for these Regional Director seats. Ultimately we decided that the simplest solution is that every PASS member should vote for every open seat. Section VIII.3 reads:
Candidates who are eligible (i.e. eligible to serve in such capacity subject to the criteria set forth herein or adopted by the Board of Directors) shall be designated to fill open Board seats in the following order of priority on the basis of total votes received: (i) full term Regional Director seats, (ii) full term Director-at-Large seats, (iii) not full term (vacated) Regional Director seats, (iv) not full term (vacated) Director-at-Large seats. For the purposes of clarity, because of eligibility requirements, it is contemplated that the candidates designated to the open Board seats may not receive more votes than certain other candidates who are not selected to the Board.
We debated whether to have multiple ballots or one single ballot. Multiple ballot elections get complicated quickly. Let’s say we have a ballot for US/Canada and one for Region 2. After that we’d need a mechanism to merge those two together and come up with the winner of the at-large seat or have another election for the at-large position.
We think the best way to do this is a single ballot and putting the highest vote getters into the most restrictive seats. Let’s look at an example:
There are seats open for Region 1, Region 2 and at-large. The election results are as follows:
- Candidate A (eligible for Region 1) – 550 votes
- Candidate B (eligible for Region 1) – 525 votes
- Candidate C (eligible for Region 1) – 475 votes
- Candidate D (eligible for Region 2) – 125 votes
- Candidate E (eligible for Region 2) – 75 votes
In this case, Candidate A is the winner for Region 1 and is assigned that seat. Candidate D is the winner for Region 2 and is assigned that seat. The at-large seat is filled by the high remaining vote getter which is Candidate B.
The key point to understand is that we may have a situation where a person with a lower vote total is elected to a regional seat and a person with a higher vote total is excluded. This will be true whether we had multiple ballots or a single ballot.
Board Elections – Vacant Seats
The other change to the election process is for vacant Board seats. The actual changes are sprinkled throughout the document.
Previously we didn’t have a mechanism that allowed for an election of a Board seat that we knew would be vacant in the future. The most common case is when a Board members moves to an Officer role in the middle of their term. One of the key changes is to allow the number of votes members have to match the number of open seats. This allows each voter to express their preference on all open seats. This only applies when we know about the opening prior to the call for nominations. This all means that if there’s a seat will be open at the start of the next Board term, and we know about it prior to the call for nominations, we can include that seat in the elections. Ultimately, the aim is to have PASS members decide who sits on the Board in as many situations as possible.
We discussed the option of changing the bylaws to just take next highest vote-getter in all other cases. I think that’s wrong for the following reasons:
- All voters aren’t able to express an opinion on all candidates. If there are five people running for three seats, you can only vote for three. You have no way to express your preference between #4 and #5.
- Different candidates may have different information about the number of seats available. A person may learn that a Board member plans to resign at the end of the year prior to that information being made public. They may understand that the top four vote getters will end up on the Board while the rest of the members believe there are only three openings. This may affect someone’s decision to run. I don’t think this creates a transparent, fair election.
- Board members may use their knowledge of the election results to decide whether to remain on the Board or not. Admittedly this one is unlikely but I don’t want to create a situation where this accusation can be leveled.
I think the majority of vacancies in the future will be handled through elections. The bylaw section quoted above also indicates that partial term vacancies will be filled after the full term seats are filled.
Section VI.7 on removing directors has always had a clause that allowed members to remove an elected director. We also had a clause that allowed appointed directors to be removed. We added a clause that allows the Board to remove for cause any director with a 2/3 majority vote. The updated text reads:
Any Director may be removed for cause by a 2/3 majority vote of the Board of Directors whenever in its judgment the best interests of PASS would be served thereby.
Notwithstanding the foregoing, the authority of any Director to act as in an official capacity as a Director or Officer of PASS may be suspended by the Board of Directors for cause.
Cause for suspension or removal of a Director shall include but not be limited to failure to meet any Board-approved performance expectations or the presence of a reason for suspension or dismissal as listed in Addendum B of these Bylaws.
The first paragraph is updated and the second and third are unchanged (except cleaning up language). If you scroll down and look at Addendum B of these bylaws you find the following:
Cause for suspension or dismissal of a member of the Board of Directors may include:
- Inability to attend Board meetings on a regular basis.
- Inability or unwillingness to act in a capacity designated by the Board of Directors.
- Failure to fulfill the responsibilities of the office.
- Inability to represent the Region elected to represent
- Failure to act in a manner consistent with PASS's Bylaws and/or policies.
- Misrepresentation of responsibility and/or authority.
- Misrepresentation of PASS.
- Unresolved conflict of interests with Board responsibilities.
- Breach of confidentiality.
The bold line about your inability to represent your region is what we added to the bylaws in this revision. We also added a clause to section VII.3 allowing the Board to remove an officer. That clause is much less restrictive. It doesn’t require cause and only requires a simple majority.
The Board of Directors may remove any Officer whenever in their judgment the best interests of PASS shall be served by such removal.
There are numerous other small changes throughout the document.
Proxy voting. The laws around how members and Board members proxy votes are specific in Illinois law. PASS is an Illinois corporation and is subject to Illinois laws. We changed section IV.5 to come into compliance with those laws. Specifically this says you can only vote through a proxy if you have a written proxy through your authorized attorney.
English language proficiency. As we increase our global footprint we come across more members that aren’t native English speakers. The business of PASS is conducted in English and it’s important that our Board members speak English. If we get big enough to afford translators, we may be able to relax this but right now we need English language skills for effective Board members.
Committees. The language around committees in section IX is old and dated. Our lawyers advised us to clean it up. This section specifically applies to any committees that the Board may form outside of portfolios. We removed the term limits, quorum and vacancies clause. We don’t currently have any committees that this would apply to. The Nominating Committee is covered elsewhere in the bylaws.
Electronic Votes. The change allows the Board to vote via email but the results must be unanimous. This is to conform with Illinois state law.
Immediate Past President. There was no mechanism to fill the IPP role if an outgoing President chose not to participate. We changed section VII.8 to allow the Board to invite any previous President to fill the role by majority vote.
Nominations Committee. We’ve opened the language to allow for the transparent election of the Nominations Committee as outlined by the 2011 Election Review Committee.
Revocation of Charters. The language surrounding the revocation of charters for local groups was flagged by the lawyers. We have allowed for the local user group to make all necessary payment before considering returning of items to PASS if required.
Bylaw notification. We’ve spent countless meetings working on these bylaws with the intent to not open them again any time in the near future. Should the bylaws be opened again, we have included a clause ensuring that the PASS membership is involved. I’m proud that the Board has remained committed to transparency and accountability to members. This clause will require that same level of commitment in the future even when all the current Board members have rolled off.
I think that covers everything. I’d encourage you to look through the red-line document and see the changes. It’s helpful to look at the language that’s being removed and the language that’s being added. I’m happy to answer any questions here or you can email them to firstname.lastname@example.org.
Tuesday, October 09, 2012
There’s another update to my little utility to script a SQL Server’s configuration. I use this for two purposes. First, I use it to keep my database mirroring servers up to date. Second, I capture the output in a version control system and keep that for historical reference.
In release 3.0.9 I made the following changes:
- Rewrote the encrypted trigger scripting. It will now list the encrypted triggers in a comment in the table script but can’t actually script them.
- It now scripts any server event notifications.
- You can script a single database using the /scriptdb flag. Please note that it will also script the instance and system databases when it does this.
- It will script any user-defined endpoints. This will capture your mirroring endpoints and more importantly any service broker endpoints.
- It will gracefully skip database mail on the Express Edition.
It still doesn’t support SQL Server 2012. I think that’s the next feature to add though.
Tuesday, July 10, 2012
I’ve been gradually adding features to my utility that scripts the configuration of a SQL Server. Since my last post I’ve added the following features:
- Skip any encrypted object in a database
- Script alerts, alert notifications and operators
- Script audits
- Always script model, master and msdb to capture any user-defined objects in those databases
- Logins are now scripted so that everything for a login is grouped together.
- There’s a second section in the logins that handles default databases. In many cases a login’s default database is a mirror target and can’t be set. This is now handled gracefully. It also includes a separate section for all default databases so those can be quickly set in the event of a disaster.
- Script credentials
- Script proxy accounts
- Script database mail
My goal is still to get everything outside a database scripted. This release is enough that I can keep my mirror target servers in sync with their principals.
Thursday, June 21, 2012
Nihao! One of the largest changes PASS is going through is the the expansion outside the US and Canada. We’ve had international chapters and events in Europe since the early 2000’s. But nothing on the scale we’re seeing now. Since January 1st there have been 18 SQL Saturday events outside North America and 19 events in North America. We hope to have three international SQLRally events outside the US in FY13 (budget willing). I don’t know the exact percentage of chapters outside the US but it’s got be 50% or higher.
We recently started an effort to remake the Board to better reflect the growing global face of PASS. This involves assigning some Board seats to geographic regions. You can ask questions about this in our feedback forum, participate in a Twitter chat or ask questions directly of Board members. You can email me at if you’d like to ask a question directly. We’re doing this very slowly and deliberately in hopes that a long communication cycle gives us a chance to address all the issues that our members will raise.
After the Summit we passed a budget exception allocating an extra $20,000 for Board members to travel to local events. I think it’s important for Board members to visit new areas and talk to more of our members. I sent out an email asking where people had attended events outside their home city. Here’s the list I got back: Albuquerque, Amsterdam, Boston, Brisbane, Chicago, Colorado Springs, Columbus, Dallas, Houston, Jacksonville, Las Vegas, London, Louisville, Minneapolis, New York City, Orange County, Orlando, Pensacola, Perth, Philadelphia, Phoenix, Redmond, Seattle, Silicon Valley, Sydney, Tampa Bay, Vancouver, Washington DC and Wellington. (Disclaimer: Some of this travel was paid for by employers or Board members themselves. Some of this travel may have been completed before the Summit. That’s still one heck of a list!)
The last SQL Saturday event this fiscal year is SQL Saturday Shanghai. And that’s one I’m attending. This is our first event in China and is being put on in cooperation with the local Microsoft office. Hopefully this event will be the start of a growing community in China that includes chapters, SQL Saturdays and maybe a SQLRally or two in the future. I’m excited to speak with people that are just starting down this path and watching this community grow.
I encourage you to visit the PASS Global Growth site and read through the material there. This is the biggest change we’ve made to our governance since I’ve been on the Board. You need to understand how it affects you and how it affects the organization.
And wish me luck on the 15 hour flight to Shanghai on Friday afternoon. Rob Farley flies from Australia to the US for PASS events multiple times per year and I don’t know how he does it so often. I think one of these is going to wipe me out. (And Nihao (knee-how) is Chinese for Hello.)
Wednesday, June 20, 2012
The next Kansas City SQL Saturday is coming up on August 4th. We’ll have the usual SQL Saturday goodness: lots of technical sessions, great networking events and a fantastic speaker dinner. And we’ll have the Third Annual Kansas City SQL Saturday BBQ Crawl.
On Friday afternoon we’ll visit a few BBQ places in town. We tend to order big sampler plates and just share everything around. It’s a great way to try a variety of styles. This year we’ll be hitting an all new selection of BBQ joints.
You don’t need to be a speaker to attend. However the call for speakers is open until June 28th (hint, hint). Locals and out-of-towners are all welcome.
If you’re interested in attending send me an email and I’ll get you added to the list.
We finish in plenty of time to get you to the speaker dinner – as if you could eat any more.
Tuesday, June 12, 2012
I’ve updated the beta for ClearTrace that support SQL Server 2012. This requires SQL Server 2012 to be installed on the computer where ClearTrace is running. It will read traces from SQL Server 2008 R2, SQL Server 2008 and SQL Server 2005.
It includes some minor improvements in performance and handling large SQL statements. It should also give better errors.
If you do find any of those errors, please report them in the support forum.
Friday, June 01, 2012
The PASS Board recently decided that we wouldn’t put another US-based SQLRally on the calendar until we had a chance to review the program. I wanted to provide some of my thinking around this. Keep in mind that this is the opinion of one Board member.
The Board committed to complete two SQLRally events to determine if an event modeled between SQL Saturday and the Summit was viable. We’ve completed the two events and now it’s time to step back and review the program.
This is my seventh year on the PASS Board. Over that time people have asked me why PASS does certain things. Many, many times my answer has been “Because that’s the way we did it last year”. And I am tired of giving that answer. We need to take a step back and review the US-based SQLRally before we schedule another one. It would be irresponsible for me as a Board member to commit resources to this without validating that what we’re doing makes sense for the organization and our members. I have no doubt that this was a great event for the attendees. We just need to validate it’s the best use of our resources.
Please keep in mind that we haven’t cancelled the event. We’ve just said we need to review it before scheduling another one. My opinion is that some fairly serious changes are needed to the model before we consider it again – IF we do it again. I’ve come to that conclusion after speaking with the Dallas organizers, our HQ team, our Marketing team, other Board members (including one of the Orlando organizers), attendees in Orlando and Dallas and visiting other similar events. I should point out that their views aren’t unanimous on nearly any part of this event -- which is one of the reasons I want to take some time and think about this before continuing.
I think it’s helpful to look at the original goals of what we were trying to accomplish. Andy Warren wrote these up in August of 2010. My summary of these goals and some thoughts on each one is below. Many of these thoughts revolve around the growth of SQL Saturdays. In the two years since that document was written these events have grown significantly. The largest SQL Saturdays are now over 500 people which mean they are nearly the same size as our recent SQLRally.
Our goals included:
- Geographic diversity. We wanted an event in an area of the country that was away from any given Summit location. I think that’s still a valid goal. But we also have SQL Saturdays all over the country. What does SQLRally bring to this that SQLSaturday doesn’t?
- Speaker growth. One of the stated goals was to build a “farm club” for speakers. This gives us a way for speakers to work up to speaking at Summit by speaking in front of larger crowds. What does SQLRally bring to this that the larger SQL Saturdays aren’t providing? Pre-Conference speakers is one obvious answer here.
- Lower price. On a per-day basis, SQLRally is roughly 1/4th the price of the Summit. We wanted a way for people to experience something Summit-like at a lower price point. The challenge is that we are very budget constrained at that lower price point.
- International Event Model. (I need to write more about this but I’m out of time. I’ll cover it in the next installment.)
There are a number of things I really like about SQLRally. I love the smaller conferences. They give me a chance to meet more people than at something the size of Summit. I like the two day format. That gives you two evenings to be at social events with people. Seeing someone a second day is a great way to build a bond with that person. That’s more difficult to do at a SQL Saturday.
We also need to talk about the financial aspects of the event. Last year generated a small $17,000 profit on revenues of $200,000. Percentage-wise that’s reasonable but on an absolute basis it’s not a huge amount in our budget. We think this year will lose between $30,000 and $50,000 and take roughly 1,000 hours of HQ time. We don’t have detailed financials back yet but that’s our best guess at this point. Part of that was driven by using a convention center instead of a hotel. Until we get detailed financials back we won’t have the full picture around the financial impact.
This event also takes time and mindshare from our Marketing team. This may sound like a small thing but please don’t underestimate it. Our original vision for this was something that would take very little time from our Marketing team and just a few mentions in the Connector. It turned out to need more than that. And all those mentions and emails take up space we could use to talk about other events and other programs.
Last I wanted to talk about some of the things I’m thinking about. I don’t think it’s as simple as saying if we just fix “X” it all gets better.
- Is this that much better of an event than SQL Saturdays? What if we gave a few SQL Saturdays some extra resources? When SQL Saturdays were around 250 people that wasn’t as viable. With some of those events over 500 we need to reconsider this.
- We need to get back to a hotel venue. That will help with cost and networking.
- Is this the best use of the 1,000 HQ hours that we invested in the event?
- Is our price-point correct? I’m leaning toward raising our price closer to Summit on a per-day basis. I think this will let us put on a higher quality event and alleviate much of the budget pressure.
- Should growing speakers be a focus? Having top-line pre-conference speakers helps market the event. It will also have an impact on pricing and overall profit. We should also ask if it actually does grow speakers.
- How many of these people will eventually register for Summit? Attend chapters? Is SQLRally a driver into PASS or is it something that chapters, etc. drive people to?
- Should we have one paid day and one free instead of two paid days? This is a very interesting model that is used by SQLBits in the UK. This gives you the two day aspect as well as offering options for paid and free attendees. I’m very intrigued by this.
- Should we focus on a topic? Buried in the minutes is a discussion of whether PASS should have a Business Analytics conference separate from Summit. This is an interesting question to consider. Would making SQLRally be focused on a particular topic make it more attractive? Would that even be a SQLRally? Can PASS effectively manage the two events? (FYI - Probably not.) Would it help differentiate it from Summit and SQL Saturday?
These are all questions that I think should be asked and answered before we do this event again. And we can’t do that if we don’t take time to have the discussion. I wanted to get this published before I take off for a few days of vacation. When I get back I’d like to write more about why the international events are different and talk about where we go from here.
Thursday, April 26, 2012
I’ve always worked to make ClearTrace perform well. That’s probably because I spend so much time watching it work. I’m often going through two or three gigabytes of trace files but I rarely get the chance to run it on a really large set of files.
One of my clients wanted to run a full trace for a week and then analyze the results. At the end of that week we had 847 200MB trace files for a total of nearly 170GB.
I regularly use 200MB trace files when I monitor production systems. I usually get around 300,000 statements in a file that size if it’s mostly stored procedures. So those 847 trace files contained roughly 250 million statements. (That’s 730 bytes per statement if you’re keeping track. Newer trace files have some compression in them but I’m not exactly sure what they’re doing.) On a system running 1,000 statements per second I get a new file every five minutes or so.
It took 27 hours to process these files on an older development box. That works out to 1.77MB/second. That means ClearTrace processed about 2,654 statements per second. You can query the data while you’re loading it but I’ve found it works better to use a second instance of ClearTrace to do this. I’m not sure why yet but I think there’s still some dependency between the two processes.
ClearTrace is almost always CPU bound. It’s really just a huge, ugly collection of regular expressions. It only writes a summary to its database at the end of each trace file so that usually isn’t a bottleneck. At the end of this process, the executable was using roughly 435MB of RAM. Certainly more than when it started but I think that’s acceptable.
The database where all this is stored started out at 100MB. After processing 170GB of trace files the database had grown to 203MB. The space savings are due to the “datawarehouse-ish” design and only storing a summary of each trace file.
You can download ClearTrace for SQL Server 2008 or test out the beta version for SQL Server 2012. Happy Tuning!
Wednesday, April 25, 2012
I have a beta version of ClearTrace that supports 2012. You can download it at http://www.scalesql.com/cleartrace/ClearTrace.2012.40.beta.zip. Please let me know if you find any issues.
Sunday, March 11, 2012
Last spring I released a utility to script SQL Server configuration information on CodePlex. I’ve been making small changes in this application as my needs have changed. The application is a .NET 2.0 console application.
This utility serves two needs for me. First it helps with disaster recovery. All server level objects (logins, jobs, linked servers, audits) are scripted to a single file per object type. This enables the scripts to be easily run against a DR server. If these are checked into source control you can view the history of the script and find out what changed and when.
The second goal is to capture what changed inside a database. Objects inside a database (tables, stored procedures, views, etc.) are each scripted to their own file. This makes it easier to track the changes to an object over time. This does include permissions and role membership so you can capture security changes. My assumption is that a database backup is the primary method of disaster recovery for databases so this utility is designed to capture changes to objects.
You can find the full list of changes from the original on the Downloads page on CodePlex.
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.