USERSTORE_TOKENPERM

On versions of SQL Server before 9.0.3171.0, a known issue can degrade query performance on 32-bit and 64-bit systems with 2 gigabytes (GB) or more of memory. When you execute queries under the context of a login that is not a member of the sysadmin fixed server role, you might encounter performance degradation symptoms that arise from a large and growing Security Token cache. These issues can include performance degradation of queries, high CPU usage for the SQL Server process, and a sharp increase in worker threads and SQL user connections. Systems that have less than 2 GB of memory should not experience these issues because normal memory usage requirements keep the Security Token cache from growing too large.

Symptoms

1. A large number of users running queries on the SQL instance and intermittently you find that the duration of queries increases intermittently
2. Most of the queries in the environment exhibit this pattern for random parameters and not any specific query/proc with any specific parameter
3. DBCC MEMORYSTATUS output for the SQL instance shows that the memory usage for SQL is gradually increasing over a period of time
4. High CPU during the problem period
5. DBCC FREEPROCCACHE or DBCC FREESYSTEMCACHE fixes the issue temporarily

Related Articles

KB article 927396: Queries take a longer time to finish running when the size of the TokenAndPermUserStore cache grows in SQL Server 2005

KB article 933564: FIX: A gradual increase in memory consumption for the USERSTORE_TOKENPERM cache store occurs in SQL Server 2005

KB article 937745: FIX: You may receive error messages when you try to log in to an instance of SQL Server 2005 and SQL Server handles many concurrent connections

http://blogs.msdn.com/psssql/archive/2008/06/16/query-performance-issues-associated-with-a-large-sized-security-cache.aspx

The product instance been patched with more recent updates

I have seen a few SQL Server 2005 Failover Clusters running into this issue. Recently, a KB Article was published explaining 2 methods (KB934749) to resolve this issue.

One scenario when you can run into this issue is when you have a SQL Server 2005 Failover Cluster patched with Service Pack 2 or higher and you add a new node to the Failover Cluster, you could run into the issue then.

Another scenario is that the SQL Server binaries on the one node got upgraded to a higher build but one of the member nodes was not upgraded due to some fatal error. But this is a very very rare scenario because SQL Server 2005 setup makes sure it patches all the member nodes where the SQL instance is not active before patching the node on which the SQL instance is installed.

SQL Best Practices Analyzer - II

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.

Cluster Failovers

We do get quite a few issues regarding root cause analysis for Cluster Failovers. Failovers mostly happen due to the IsAlive check failing for the SQL Server resource after which the following two conditions arise:

1. SQL Server service restarts on the same cluster node
2. SQL Server resource fails over to a member cluster node

So, for looking into the possible root causes of a cluster failover, a SQL version of the MPS Reports capture is required from the node on which SQL is currently active. From the data requested by the PSS Engineer, the following files would be of utmost importance:

1. All the SQL Server ERRORLOGs
2. Windows Event Logs (System/Application)
3. Cluster Log

Based on the SQL Server ERRORLOGs, we would check for any errors or tell-tale signs which would point us to why the IsAlive check failed for the SQL Server resource. After that, we would look into the cluster log and the windows event logs to find out co-relation among the events during the failover time on the server.

Since, the cluster log rolls over and also the SQL Server ERRORLOGs can roll over very quickly if a job is in place to recycle if after a certain size, it is a very good idea to save the cluster log and the SQL Server ERRORLOG(s) right after the failover to prevent them from rolling over and overwriting valuable data from the problem time period.

Sometimes, a post mortem analysis provides us a hypothesis of what happened but doesn't paint the picture completely due to lack of data from the period the problem happened. Based on the nature of the problem, the PSS Engineer might ask to you to do the following for the the next problem occurrence along :

1. Capture a light-weight PSSDIAG round the clock with file rollover
2. Or a filtered dump of the SQL Process during the problem period if there is heavy blocking on the server or if the failover had occurred due to memory dump(s) on the server
3. OR a round the clock Perfmon log if there were possible external memory pressure on the server. This can be configured while capturing a PSSDIAG

Deadlock Troubleshooting

Deadlock troubleshooting has always been a bit tricky. A deadlock is a special type of blocking scenario, but blocking and deadlocking are not the same thing. Deadlocks are a natural side effect of blocking. The most typical deadlock solution is either a stored proc/app code optimization, or a change in the database schema or indexing strategy.

To capture a deadlock graph in the SQL Server ERRORLOG, use the following trace flags:

• For SQL Server 2000: 1204 & 3605
• For SQL Server 2005: 1112

Refer Bart Duncan's Weblog for deadlock troubleshooting. Bart was an Escalation Engineer for SQL Server and his blog article spanning three parts for deadlock troubleshooting is probably the most definitive guide for this topic.

http://blogs.msdn.com/bartd/archive/2006/09/09/747119.aspx

The -T1204 output can be interpreted using the following file: http://blogs.msdn.com/bartd/attachment/747119.ashx

If you find that your SQL Server is encountering deadlocks, then what data do you need to collect:

1. SQL Server Profiler traces with the following events:

• All statement level events
• Showplan All events because the statement that is chosen as the deadlock victim will not have a Statistics Profile Event as it would have been terminated
• Lock: Deadlock and if you are using SQL Server 2005, then capture Deadlock Graphs as well

2. Perfmon Logs

3. SQL 2000 Blocker Script output/SQL 2005 PerfStats

Additionally, if as a developer if you are concerned about your statements being terminated due to deadlocks, then you must build a retry logic into your code for handling the deadlock and resubmitting the query. This can be done using a try-catch block withing the query batch that you are executing.

SQL Nexus

This is a tool that is used by the Microsoft PSS team to analyze PSS Diag 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

RML Utilities

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.


SQL Server provides an in-built function fn_trace_gettable() to load the collected profiler traces into a SQL Server database table. Please refer for more details: http://msdn2.microsoft.com/en-us/library/ms188425.aspx

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

Transaction Log Growth

My Transaction Log is Growing!!!

The transaction log growth issue... What do we do?? A few things not to do when this happens:

1. Delete the LDF file of the database
2. Create a new LDF file if there is no space in the existing transaction log and if the SQL Server ERRORLOG is reporting 900* messages. (Personal opinion.. Seen a lot of situations where this is has gone horribly wrong on production environments)
3. Take the database offline
4. Restart the SQL Server service

Now that the above steps are avoided, what is to be done. Simple set of steps can be done to shrink the T-LOG of the database in question using the steps mentioned in the KB Article below. My troubleshooting steps involve the following:

1. Check the current SQL Server ERRORLOG for any 900* error messages related to the T-LOG
2. If you have enough disk space and the database is not in SIMPLE recovery, make a backup of the transaction log so that the you can most of the current active virtual log files
3. Use DBCC SQLPERF (LOGSPACE) to find out the current used percentage of the transaction log for the database in question
4. If it's SQL Server 2005, find out the log reuse value in the sys.databases catalog view output
5. Find out if there are any open transactions using DBCC OPENTRAN command. KILL the transaction if you can ascertain that this open transaction can be redone or recovered later.
6. Try shrinking the T-LOG using DBCC SHRINFILE or if it is in SIMPLE recovery, try using BACKUP LOG WITH TRUNCATE_ONLY command. But be advised that this truncate command will truncate your transaction log.
OR
You can even try firing a CHECKPOINT into the database and then trying to truncate the transaction log.

Another important thing is to make sure that your autogrowth considerations are taken into account. The following article should help you decide what needs to done in deciding the autogrowth of a database file: http://support.microsoft.com/kb/315512/en-us

For more information, see the following topics in SQL Server Books Online:
• Shrinking the transaction log
• DBCC SHRINKFILE (Transact-SQL)
• Truncating the transaction log

Useful articles
How to use the DBCC SHRINKFILE statement to shrink the transaction log file in SQL Server 2005
http://support.microsoft.com/kb/907511/en-us
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/kb/272318/en-us
INF: How to Shrink the SQL Server 7.0 Transaction Log
http://support.microsoft.com/kb/256650/en-us
How to stop the transaction log of a SQL Server database from growing unexpectedly
http://support.microsoft.com/kb/873235/en-us
A transaction log grows unexpectedly or becomes full on a computer that is running SQL Server
http://support.microsoft.com/kb/317375/en-us

Common Reasons for T-LOG growth
1. Wrong autogrowth values (Mostly high percentage values)
2. Index rebuild operations for database done infrequently with database in full recovery model or with log shipping/mirroring enabled
3. Something failing in your replication topology
4. BULK insert/update operations

Disk Contention Issues

It is recommended that Average Disk Queue Length for disks be less than 2 * number of spindles on the disk. So if your disk had 10 spindles, then values above 20 woud be a cause of concern. But on a SAN, this particular counter can be misleading at times.

You might want to the check the following counters under perfmon too:

Physical Disk:
Avg. Disk Secs/Write
Avg. Disk Secs/Read
Avg. Disk Secs/Transfer

Logical Disk
Avg. Disk Secs/Write
Avg. Disk Secs/Read
Avg. Disk Secs/Transfer

Also, you would want to find out if there are a lot of Split I/O operations happening on the server.

For disks on which SQL Server Data (MDF,NDF) and Log (LDF) files reside, we recommend that these counters show values less that 30 milli-seconds. If they are constantly showing values of 0.5 and above, then we have a disk contention issue on our hands.

The disk contention could be due to these reasons:

1. There is an underlying H/W issue. This could mean that some driver or firmware needs an upgrade (essentially all your firmware/drivers should always be on the latest builds available) or it could be a problem with the SAN configuration or it could be that this particular SAN is not dedicated to SQL Server.

2. OR it could be that the disk contention is due to the fact that the queries executing on SQL Server are performing excessive amount of reads due to sub-optimal plans. Sub-optimal plans would be generated if your indexes are out of shape or if your statistics are skewed. This would require a statistics update with a full scan. (Statistics maintenance, please refer: http://yukonspace.blogspot.com/2007/10/statistics-update.html)

3. OR it could be that the disk activity on the disks has increased over a period of time due to increase in data or addition of new databases. In such a case, it would be a good idea to provide a separate disk or storage area for some of the databases as the current activity might be too much for the disk to handle. Sometimes, load balancing for physical storage also helps in alleviating a disk contention issue.

Also, it would be a good idea to check if you are receiving any stuck/stalled IO warnings in the SQL Server ERRORLOGs. Please refer http://yukonspace.blogspot.com/2007/09/stuck-stalled-io-messages.html

Related Articles
Improving SQL Server Performance
http://msdn2.microsoft.com/en-us/library/ms998577.aspx

Troubleshooting Performance Problems in SQL Server 2005

http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx

Application performance troubleshooting with SQL Server
http://support.microsoft.com/kb/224587

I/O Related DMVs for SQL Server 2005

http://msdn2.microsoft.com/en-us/library/ms190314.aspx

Sometimes, it's also worthwhile checking the activity on tempdb and finding out if the contention is due to tempdb activity. The links below could be helpful:

http://www.microsoft.com/technet/prodtechnol/sql/2005/workingwithtempdb.mspx

http://support.microsoft.com/kb/328551

SQL Server is not configured for Remote Connections

This is one of the most common error messages when users use default settings to connect to a SQL Server Express Instance.

ERROR
Sqlcmd: Error: Microsoft SQL Native Client: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.


Please refer the following article to make sure that your SQL Express Instance is configured to accept Remote Connections:
http://support.microsoft.com/kb/914277

Also, make sure from the SQL Server Configuration Manager that TCP/IP and Named Pipes are enabled and you have VIA protocol disabled.

Stuck Stalled IO Messages

Sometimes, we see the following messages in the SQL Server 2000 (Service Pack 4 and above) and SQL Server 2005 ERRORLOGs:

2004-11-11 00:21:25.26 spid1 SQL Server has encountered 192 occurrence(s) of IO requests taking longer than 15 seconds to complete on file [E:\db_data2.ndf] in database [user_db] (7). The OS file handle is 0x00000000000074D4. The offset of the latest long IO is:
0x00000000022000".

A given I/O request that becomes stalled or stuck is only reported one time. If the message reports 10 I/Os as stalled, these 10 reports will not occur again. If the next message reports 15 I/Os stalled, it indicates that 15 new I/O requests have become stalled.

Most of these messages if occur frequently in a SQL Server, then it could be due to one of the following:

1. There is an underlying hardware issue where in your firmware and disk drivers need to be upgraded. This is mostly done by the Internal Storage Team or preferrably by the Hardware Vendor
2. Or the disks are overwhlemed with the IO Requests being posted on the isks. In this case, you would want to go ahead and make sure that if possible some of the data files and log files would need to be moved to a different drive
3. Or it could be due to bad plans SQL Server ends up performing extra number reads than required

Also, monitoring the disks using the following counters under performance monitor under the would be helpful (under the performance objects - Logical Disk and Physical Disk):

1. Average Disk Queue Length
2. Average Disk Secs\Transfer
3. Average Disk Secs\ Write
4. Average Disk Secs\ Reads

The last three counters should never show a prolonged peak period which shows values of 30 milli-seconds and above. Please refer my blog article on how to setup a perfmon trace:

http://yukonspace.blogspot.com/2007/09/setting-up-perfmon-logs.html

Helpful Articles:
SQL IO Basics
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqliobasics.mspx
SQL Stuck Stalled IO KB Article
826433: PRB: Additional SQL Server Diagnostics Added to Detect Unreported I/O Problems
Stuck Stalled IO Messages:
http://msdn2.microsoft.com/en-us/library/aa175396(SQL.80).aspx

Troubleshooting SQL Server Setup and Upgrade Failures

1. The Setup Logs for SQL Server 2005 are created in the following location
"(system drive):\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG"
2. There are two folders underneath this location:
a. Files
b. Hotfix
3. The files under the Files folder are created during the RTM installation of SQL Server 2005
4. The files under the Hotfix folder are created during Service Pack/Hotfix installation
5. For every failed setup of SQL Server 2005, there will be a .CAB (Cabinet file) created under the Files or Hotifx depending on what installation you are performing: RTM/Hotfix/Service Pack installation
6. Also, the LOG folder would have a summary.txt file which would give a brief overview of which component(s) failed to install
7. Using the Error Number and the Component Name, find out if you come back with any helpful links from http://support.microsoft.com/
8. If you are getting nowhere, then please post a question on the MSDN/TechNet Forums or open up a support incident with Microsoft Product Support Services

Please refer the following for more details:
http://technet.microsoft.com/en-us/library/ms143702.aspx
http://msdn2.microsoft.com/en-us/library/ms144287.aspx

Setup Logs for SQL Server 2000
The setup logs for RTM installations for SQL Server 2000 are installed with the following log sqlstp.log getting created in the %windows% folder.

The Service Pack setup logs are created having the following filename sqlsp.log under the same location specified above.

However, the hotfix logs are created with the following prefix "KB***.log"

The drawback with the SQL setup logs for SQL Server 2000 is that the setup logs get overwritten every time setup is run. But in SQL Server 2005, this is not the case. All the logs are retained in the BOOTSTRAP folder and a new set of log files are created with incremental numbers in the same location.

Blog Introduction

Hello Everyone,

It's been a while that I have been working with SQL Server. One of biggest challenge that I have seen people face is with troubleshooting is that people are at a loss to where to look and what to collect.

If you know what data to collect and how to collect, that's half your solution. Two reasons for this:

1. Too much unnecessary information can divert you in the wrong direction
2. Troubleshooting becomes much simpler with relevant data collection

So, in this blog we are going to cover two aspects of maintaining SQL Serves:

1. Pro-active work which will help us minimize points of failure and narrow down to the root if a problem does occur
2. Re-active work which involves having the right approach and action plan ready in case a problem does appear

All comments are most welcome.

Two of the best resources for Troubleshooting SQL Server Issues is:

1. Technet Forums
2. SQL 2005 Practical Troubleshooting: The Database Engine by Ken Henderson (This is a must read. This is same author for Guru's Guide to SQL Server. This book has inputs from the DEV team for SQL Server)
http://www.khen.com/2007/06/sql-server-2005-practical.html
http://www.amazon.com/SQL-Server-2005-Practical-Troubleshooting/dp/0321447743