Truncate SQL Server Error Log

sp_cycle_errorlog is useful.

You can cycle the error log by calling sp_cycle_errorlog and then that will close the current error log and cycle the log extensions. Basically, it’ll create a new error log file that SQL Server will be hitting. Then the archived error log(s) can be treated accordingly (delete/move with caution). This will not technically “truncate” the log, it’ll just roll it over and you can handle the old logs as you so please, like any other file system file.

via Safe way to truncate SQL Server Error Log – Database Administrators Stack Exchange.

Software Based Performance

I like to read articles like this: How we made editing Wikipedia twice as fast

MediaWiki recently deployed the Facebook developed HHVM for their PHP code and their server capacity was increased and user experience improved. Overall cpu load on the application dropped from 50% to 10%. Page load time for the user dropped 30% as well (1.3s to 0.9s). Those are nice improvements.

I had a similar experience at my job. We had an application that was cpu intensive and grew to the point where the server couldn’t keep up with the daily work load. We were exploring ideas to either ramp up the server size significantly or redesign the application. I’m a Network Administrator by trade but I’ve dabbled in enough areas to have a passing understanding of SQL Server and some programming skills, so I was tasked with find out what I could before the devs dove too far in to pulling apart this application that a previous employee wrote. (He had since left for greener pastures.)

I captured some sql traces, and with a lot of luck, I narrowed down the culprits. The biggest offender was a stored procedure using a query that was doing a wildcard search with the wildcard in the first character in the search string. (e.g. it was doing something like ‘%/’+ @filename). I found out that putting the wildcard first means the database does an index scan instead of a seek. Every time this query ran, the database went through the entire index which was 2GB in size. Sounds like the index wasn’t really an index was it?

It took a while for the devs to fix it with all the necessary testing and QA, but they ended up removing the wildcard entirely and just searched on the filename. At first they didn’t add an index to that column either, so the database was still doing a table scan. After the second index was added, the time to run that stored procedure dropped from 10 seconds to 2.5 seconds.

Here were some simple timers of each stage:

  1. Pre-fix: wildcard searching.
  2. Post-fix Mon: no wildcard, but no index either.
  3. Post-fix Thurs: added index.

For this application, this was a case of outgrowing the initial design. The application worked great in the beginning when there were only 100,000 records in the table, but with 7 million the logic needed a bit of reworking.

That’s why I love reading stories like the one above. I can write simple programs, but I’m certainly no programmer, and I’m always impressed with the skills and talent that people use to find a way to do more, sometimes much more, with the same or even less.