Category Archives: Monitoring

What can you tell me Default Trace

Posted on by

The default trace functionality introduced in SQL Server 2005 is still a much under appreciated feature of the product. This is somewhat like a black box trace which is analogous to a airline black box can help you in certain post-mortem analysis as a DBA. I am sure a lot of you have a host of in-house scripts that you have in-place or some monitoring/auditing tool that reports some inadvertent/unscrupulous activity on the databases that you administer. Nonetheless, it is always a good idea to be aware of the out-of-the-box features that are available that can make your job a wee bit easier.

Deepak Rangarajan (Blog), a Microsoft MVP, had blogged about the set of events which are captured by this default trace. So, I shall not attempt to duplicate his efforts here but collate a set of issues that CSS has been able to resolve using Default Traces in the past and will continue to do so in the future.

Dude: Where’s my database?
Not really a question that you want to be pondering on if you do not have any monitoring/auditing tools in place. And nor do you want to BING that search term. (Ok, if you prefer even Google it!) The default trace captures the following events: Object:Created, Object:Altered and Object:Deleted which will help you track down inadvertent deletion of databases. SQL Server Management Studio has a standard report called “Schema Changes History” report which can help you track such changes on a SQL instance in the form of a report.

Memory pressure: Where, When?
Ideally this can be tracked by Perfmon data collection and most of the SQL monitoring tools available in the market today track the Target and Total Server Memory counters for SQL instances. When you monitor the delta changes for these counters, you can track the amount of fluctuations that are occurring. The default trace tracks these changes using the Server Memory Change profiler event which will help you decide if there is a large fluctuation in Max Server Memory configuration for the SQL instance. This event gets raised when SQL Server memory usage has increased or decreased by either 1 megabyte (MB) or 5 percent of the maximum server memory (Buffer Pool only), whichever is greater. This is quite useful when tracking memory pressure being experienced for a SQL instance due to factors external to the SQL instance.

Mirrored Database: When did your state change?
All database mirroring state changes for all your mirrored databases are also monitored using the Default Trace using the event: Database mirroring status change. This helps track all database mirroring state changes without having to parse the SQL Errorlogs for the same.

OMG: Someone’s mucking around with my database security?
We have handled multiple issues in the past where we needed to identify various security related audit activity on a SQL instance like new Logins/Users being added, mostly for post-mortem analysis. The default trace “Security Audit” event classes show their usefulness by letting you trace such kind of activity using the default trace.

Full-Text: What are you upto?
The default traces also help trace Full-Text crawl activity which at times have been known to cause performance degradation when very large FT catalogs are involved.

Database: Did you just grow or shrink?
SQL Server databases enabled for Autogrow help prevent the database from going down if the file becomes full but at the same time Autogrow events can cause performance issues like blocking. Additionally, database auto shrink (both log and data files) operations can severely impact database performance. With the help of the default traces, you can can all Auto Grow/Shrink operations for your databases on the instance.

SQL Performance: Now how can I use the default trace for this?
Well the answer is not a definitive one but the default trace can give you an indication if you have a baseline established. The default trace traces the following events: Hash Warning, Missing Column Statistics, Missing Join Predicate and Sort Warning. These events can be used to find out if there is an excessive amount of sorting or hashing that is occurring on your SQL instance (without a baseline, this data is moot point though). You can also identify if a number of your queries are suffering from missing statistics and missing join predicates.

One thing to note here is that the default traces rollover which means that if the default traces are not copied over to an alternate location, they will be overwritten and valuable information required for post-mortem information will be lost.

Now SQL Server 2008 has BPA too

Posted on by

This has been an ask from the community for a long time now. We just launched SQL Server 2008 R2 Best Practices Analyzer. The SQL Server rules are executed using the Microsoft Baseline Configuration Analyzer framework. The rules are all Powershell based. So you need Powershell 2.0 and MBCA 2.0 to use SQL Server 2008 R2 BPA. This version of BPA has an extensive set of rules which can validate a lot of commonly known issues in areas like SQL Setup, Engine, Replication etc. This makes the work of a DBA much easier. Another cool feature about this tool is that you can run remote scans also against other SQL instances.

Addendum: April 13th, 2011

Note: It works with SQL Server 2008 and SQL Server 2008 R2.

Customizing the TokenPerm in Yukon SP3!!

Posted on by
I had written about the TokenPerm cache store in a previous blog post of mine. The trace flags mentioned in the KB Articles helped in throttling the cache to prevent the cache size from growing out-of-proportions. Recently, a new Trace Flag (-T4621) was added SQL Server 2005 Service Pack 3 (KB959823) to customize the size of the Token Perm cache. Instead of hard coding the quota for Token Perm with the earlier trace flags, you can use the formula to specify the Registry value:
 
Quota = 1,024 * 2 * ( <Number of Distinct Logins> + <Total Number of Users in each database> )
 
Remember to turn off the other trace flags for Token Perm issues that you had activated on your systems previously. You cannot enable trace flag 4621 together with trace flag 4618. When trace flag 4621 and trace flag 4618 are enabled together, trace flag 4618 takes precedence. The reason this trace flag was introduced was to prevent detrimental effect to systems where constant flushing of the Token Perm cache would affect the performance rather than helping it.

SQL BPA – Part II

Posted on by

I have already blogged about this in a previous post of mine (Best Practice Analyzer) but I was recently working on the SQL Server 2005 version of the tool and thought that this deserved a second mention.

This tool has been enhanced a lot and provides a great deal more information than it’s SQL 2000 counterpart. This creates a XML data output file in your %appdata%/Microsoft/SQL BPA folder. This output can be imported using the SQL BPA UI and then a set of reports can be generated to check the following:

  • Gathers configuration information from an instance of SQL Server.
  • Performs specific tests on the instance of SQL Server.
  • Proactively verifies that the configuration is set according to recommended best practices. Some high level checks are even performed on the Operating System level.
  • Reports all settings that differ from the default settings.
  • Reports recent changes in the instance of SQL Server.

    On a broader level, the tool verifies the above mentioned based on rules divided into the following categories:

    1. Security rules
    2. Database Engine rules
    3. Analysis Services rules
    4. Replication rules
    5. Integration Services rules

    For example, if you applied the initial release version of SQL Server 2005 SP2, existing SQL Server 2005 maintenance plans and SSIS packages that contain cleanup tasks might run those tasks at shorter intervals. The tool if it scans your SQL Server instance and finds out if you are on a build lower than the one mentioned in KB933508, then it would provide the recommendation to apply the fix.

    The SQL BPA UI can be used to run scans on remote machines also. So, there is no need to install the tool on the SQL Server box which you want to scan. You can also configure the type of scan you want the SQL BPA tool to perform on your server.

    Microsoft PSS also has the capability to include this tool as a part of the PSSDIAG collection that they send out to collect diagnostic data from the instance based on the need to collect BPA analytics data.

    For a more detailed information about the above mentioned points, you can always refer the SQL Server Best Practices Analyzer Help chm file.

    • Best Practices Analyzer

    Posted on by

    I have seen a lot of people asking "Are we following best practices for our SQL Servers?". The Best Practice Analyzer can try and guide you along a path which would lead to the right answer. A simple answer is not possible because of the following reasons:
    1. We are not conversant with your environment and setup
    2. We do not know what kind of business implementation this SQL Sever is
    3. We do not know what constraints are present which prevents some of the best practices from being followed
    4. We do not know which data/databases are critical/non-critical etc. and also do not know what kind of SLAs need to be met.

    Even then, an attempt is made using the Best Practice Analyzer to verify if common best practices are being implemented across your SQL Server. It creates a repository on the our server and stores the analysis for the server in the database which can be used at a later date.
    The SQL Server 2005 Best Practices Analyzer (BPA) gathers data from Microsoft Windows and SQL Server configuration settings. BPA uses a predefined list of SQL Server 2005 recommendations and best practices to determine if there are potential issues in the database environment.

    This tool is available at:
    For SQL Server 2000
    http://www.microsoft.com/downloads/details.aspx?familyid=B352EB1F-D3CA-44EE-893E-9E07339C1F22&displaylang=en
    For SQL Server 2005
    http://www.microsoft.com/downloads/details.aspx?FamilyId=DA0531E4-E94C-4991-82FA-F0E3FBD05E63&displaylang=en