Category Archives: Tools

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 Nexus

Posted on by

This is a tool that is used by the Microsoft PSS team to analyze PSSDiag data. This tool has the capability of loading the Profiler Traces collected (provided ReadTrace is installed on the box).

In addition to this, it also loads the other .OUT files and the PerfStats script output file into the Nexus database. I use this tool regularly to analyze PSSDIAG data due to the following reasons:
1. Saves me time from manually opening each file and looking through them
2. Based on the graphs provided in the reports, I can quickly drilldown to the major bottleneck

The SQL Nexus reports act as a brilliant guide when you are analyzing PSSDIAG for the following scenarios:
1. High CPU
2. Blocking
3. High IO
4. General Performance Problems

Since, the SQL Nexus Reports use Reporting Services client-side report viewer, you can create your own reports to work against the Nexus database and then deploy them to the %appdata%/SQL Nexus/Reports folder. Once, this is done, the new reports would show up along with the defult reports every time SQL Nexus is launched.

What are the advantages of this tool?
1. Shows aggregation reports for the Profiler Traces loaded into it
2. Blocking and wait statistics information based on the PerfStats script output. This helps in analyzing blocking on the server
3. Since, the data collected are loaded into tables, you can run queries against the database to draw inferences. If you use these queries frequently enough, you can create your own reports and deploy them to the above mentioned location
4. You can export the data from the SQL Nexus Reports into files (.XLS,.PDF,.JPG etc) and send them as reports
5. The reports let you narrow down to specific time frames which makes analysis easier
Download location: http://www.codeplex.com/sqlnexus

Addition: April 4, 2011

A list of known issues documented with SQL Nexus:

http://blogs.msdn.com/b/sqlserverfaq/archive/2011/02/07/sql-nexus-and-rml-utilities-a-primer-on-the-issues-that-i-have-dealt-with-last-year.aspx

Addition: April 6, 2011

A very common issue that I worked on multiple times after which I realized that it warranted a blog post:

SQLDIAG: There are no instances of version 10 on this computer

RML Utilities OR ReadTrace

Posted on by

The Microsoft Download site has a tool called RML Utilities which can be used to process the profiler traces collected from a SQL Server instance.
SQL Profiler Traces are very helpful when we need to perform a Bottleneck Analysis in any SQL environment or you need to find out the before & after picture. But the daunting task remains in analyzing the Profiler Traces because the size and volume of the trace files.

SQL Server provides an in-built function fn_trace_gettable() to load the collected profiler traces into a SQL Server database table.
Then you can run queries on this table to draw inferences from the profiler traces collected during that period. Lets say you wanted to find out all queries that had a duration above 10,000 and group the results by host name, then you could write a query in the following manner:

SELECT hostname, count(*) as counts
FROM tbl_trace
WHERE Duration > 10000
GROUP BY hostname

This is a tool that is used by Microsoft PSS to analyze SQL Server Profiler Traces collected by the PSSDIAG utility.

For more details, please refer:
Replay Markup Language
http://support.microsoft.com/?kbid=944837
RML Utilities (x86) Download
http://www.microsoft.com/downloads/details.aspx?familyid=7EDFA95A-A32F-440F-A3A8-5160C8DBE926&displaylang=en
RML Utilities (x64) Download
http://www.microsoft.com/downloads/details.aspx?familyid=B60CDFA3-732E-4347-9C06-2D1F1F84C342&displaylang=en

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