Voice of the DBA

Tag: syndicated

  • Trace Flag 2371 and Statistics

    One of the issues that I see published often on forums like SQLServerCentral is the advice to update statistics on your tables if you have strange performance issues, or sudden changes in performance. Statistics are important for the query optimizer, and you should understand the basics of how they work.

    However there’s a problem with statistics. They get out of date. SQL Server will automatically update statistics, but it doesn’t do this constantly. It does it after 20% of the table changes (by default). If you have 1000 rows, that means 200 rows changed (or added) can trigger the update. If your table has 50 changes every couple days, you’ll get statistics updated every week.

    If you have 1mm rows (think large, historical data here), then those same 50 changes won’t trigger statistics updates for a long time. 200,000 changes will be needed then.

    There’s a trace flag, 2371, that can help with the minimum needed to trigger a stats update (or you can do this with your own jobs). By choosing this, you can lower the minimum for triggering an update.

    What you do really depends on your issues. If you find poor performance in queries, look for wildly incorrect estimates of rows in your query plans. If you find that your statistics aren’t being updated, or not updated enough, you might enable the trace flag, or create your own job to update statistics manually.

    Note that if you are rebuilding indexes, you don’t need to also update statistics on the columns in the index. They are done as part of the index rebuild.

  • On Source Control

    A mention today from Jamie Thomson in his blog on source control. I left a comment, but it was long, so I thought I’d repost it here.

    My comment

    Thanks for the note, Jamie, and it’s interesting, but I don’t see any respondents that aren’t using VSC. I’m guessing many people don’t want to admit that.

    I use source control, sort of. I don’t have a lot of production code, mostly demo stuff, which doesn’t change so much as gets scrapped and restarted. However I am using Subversion (Turtle interface) with Red Gate’s SQL Source Control (of course).

    Why? Well, since I have a 1:1 with my boss today, I should mention Red Gate makes a fantastic product that I couldn’t do without. Actually I think I’m bound to use it since I work for Red gate, but even if I didn’t, it makes things easier.

    I built this habit at a startup, with Visual SourceSafe, and 6 months of browbeating developers to actually check out code from VSS, do a File | Open in Enterprise Manager (SQL 2k at the time), make changes, save the code, and check it back in. It can be done manually, and should, but there are tools like Red Gate’s products that make it easier.

    I know lots of people just edit code in SSMS/VS without controlling it, and that’s a mistake if you are trying to work in a team environment, and product quality software. I haven’t had to roll back often, but there are times we have needed to, and not having control was an issue.

    As an anecdote, I had a job once, waaaayyy back in the last century, using SQL 6.5. The developers had done a great job of using source control for all ASP, VB6, and SQL code. The problem was that they had checked out the code, saved it locally, copied it to another folder, and then checked it in. Then they might check back in the original script that was checked out. Or they might check the original back in and forget the modified version. Or they might not check anything in.

    We found 3, 4, even 7 copies of some stored procedures on various desktops and shared drives, and they had decided to encrypt all the code on the SQL Server. We were never quite sure what version was deployed where, and deployments of the "latest" versions from VSC broke functionality.

    We ended up decrypting all code, and reloading it into VCS, while removing all copies of local storage. It isn’t enough just to use source control or implement something; you need to understand what you are doing and do it consistently. Tools help here, so please use something.

    A Couple More Thoughts

    Source control is insurance. It’s risk reduction, but something that most people don’t understand or think they need. They may not. If you never have a server crash, or a hack of your data, then you don’t need backups. However most people wouldn’t want to run their systems, especially the ones they care about, without backups.

    It’s the same with source control. Use something to provide yourself with a fallback if you have issues.

    It’s not hard, there are lots of tools out there, and they make it easy to conform to some process. You can use SQL Source Control or SQL Connect from my company, or use something else, but integrate your database code with source control.

  • Important Alerts for SQL Server

    One of the things that I think is extremely important for DBAs and really anyone that has to administer a SQL Server instance is a set of alerts on various items that might occur.

    I ran across Glenn Berry’s article on Provisioning SQL Server and there’s a section in there that is titled “Setting Up SQL Server Agent Alerts”. It assumes you have Agent running (and you should), and includes a series of alerts that you can set to inform you when certain things occur.

    Glenn has alerts for severity errors 19-25, which are important, and then includes an alert for Error 825.  This error is indicative of possible corruption and at least some I/O issue with your storage. It’s important to have this error trapped, but in addition, I’d also recommend setting alerts for 823 and 824 errors.

    I would recommend modifying Glenn’s code to include this T-SQL as well.

    -- Error 823: Read Write Request Failure
DECLARE @Error823AlertName SYSNAME = N'I/O Alert - Error 823: Read or Write request failure';

EXEC msdb.dbo.sp_add_alert @name = @Error823AlertName,
              @message_id=823,
              @Severity=0,
              @enabled=1,
              @delay_between_responses=900,
              @include_event_description_in=1,
              @category_name=N'[Uncategorized]',
              @job_id=N'00000000-0000-0000-0000-000000000000';

EXEC msdb.dbo.sp_add_notification @alert_name = @Error823AlertName,
@operator_name=@OperatorName, @notification_method = 1;
GO
-- Error 824: Read Write Request Failure
DECLARE @Error824AlertName SYSNAME = N'I/O Alert - Error 824: Logical Consistency I/O Error';

EXEC msdb.dbo.sp_add_alert @name = @Error824AlertName,
              @message_id=824,
              @Severity=0,
              @enabled=1,
              @delay_between_responses=900,
              @include_event_description_in=1,
              @category_name=N'[Uncategorized]',
              @job_id=N'00000000-0000-0000-0000-000000000000';

EXEC msdb.dbo.sp_add_notification @alert_name = @Error824AlertName,
@operator_name=@OperatorName, @notification_method = 1;
GO

  • Thirteen Years Old

    A very cool shot of our company at 13.

    redgate13

    I first heard of Red Gate back in 2001 and they were our first advertising customer at SQLServerCentral.

    A few years ago they bought the company and I now work for them. It’s a great company, and a fun one. Things like this are very cool, and while a little corny, are a nice way to celebrate a minor milestone.

    I wish I was closer to the office for things like this.