Shiloh-Yukon-Katmai Discussion

My New SQL Server Blog

2009-02-01T01:54:00.001-08:00

Please visit my new blog. I have migrated all my posts to Spaces on Live:

http://troubleshootingsql.spaces.live.com/

Katmai

2008-09-06T23:07:00.001-07:00

Katmai - SQL Server 2008 has been launched. I was playing around with the new features in Katmai and some of them really struck me as ground breaking changes compared to earlier versions. My speciality being the Database Engine, I have not been able to do justice to my poking around for other components like the BI part of it. Though whatever I have managed to poke around, I found the following enhancements and new features to be very impactful in the near future:

Compression - Database and Backup compression is now available in Katmai. This is a boon for DBAs who manage databases which are TBs in size. Now you have Row or Page level compression to choose from. Also, you have a SP which makes an estimate of how much benefit you will derive from enabling compression or will it benefit you at all.

Backup Compression is also another major boon as storing backups of VLDBs can be quite a task which have strict SLA requirements.

T-SQL Enhancements - I quite liked this one. Now I can use programming level constructs while coding in T-SQL. Eg. declare @var varchar(50) = 'Declare and Initialize!'

New Table Hints and Query Hints - A few other tools added to the repertoire of the DBA to make queries run faster in environments where the DBA has no control over the queries being sent to the the database engine via the application.

XEvents - This is an important new feature from a troubleshooting perspective.

Policy Based Management - This is another of those administration joys that a DBA will derive when using this new feature. With a few clicks, the DBA can administer multiple database environments to conform organizational policies and also have checks in place to report non-compliance.

Server Audit Tracing - This is a step towards helping in meeting Compliance standards like PCI etc. No longer will you need to run profiler traces which have a tendency to affect server performance if capturing a large number of events.

Resource Governor - Another new feature which provides more granularity in deciding on resource bifurcation for different applications and deciding on application priorities. This virtually allows the DBAs to decided on what kind of queries should get how much resources on the server w.r.t. CPU, Memory etc. However, one should understand the full impact and ramifications of enabling such resource throttling on the server before implementing it. Such an implementation of RG at a production level requires thorough testing as you might end up throttling an operation which you didn't intend to.

How to connect to Embedded Edition from SSMS

2008-07-28T09:02:00.000-07:00

From SQL Server Management Studio, you cannot connect to a SQL Server Embedded Edition instance by just providing the server name. You need to put the following in the server name input:
\\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query

The files for an embedded edition instance should be at:

C:\WINDOWS\SYSMSI\SSEE\MSSQL.2005\MSSQL\

Using WMI to manage SQL Server 2000 services

2008-07-26T02:00:00.000-07:00

There are quite a few blog posts out there to manage SQL Server 2005 services using the :\\.\root\Microsoft\SqlServer\ComputerManagement

However, WMI Admin Provider is not pre-installed for SQL Server 2000. It needs to be installed separately using the WMI Admin Provider Setup available along with the SQL Server 2000 Setup CD under x86\other\wmi folder.

Sample script to change SQL Server 2005 service startup account and password using WMI:
http://blogs.msdn.com/mwories/archive/2006/11/03/wmi_5F00_change_5F00_password.aspx

MSDN Documentation on Win32_Service class
http://msdn.microsoft.com/en-us/library/aa394418.aspx

Sample Script to change a SQL Server 2000 instance startup account using root\MicrosoftSQLServer namespace:

strComputer = "."
Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\MicrosoftSQLServer")

' Obtain an instance of the the class
' using a key property value.

Set objShare = objWMIService.Get("Win32_Service.Name='MSSQL$SQL2000'")

' Obtain an InParameters object specific
' to the method.

Set objInParam = objShare.Methods_("Change"). _ inParameters.SpawnInstance_()

' Add the input parameters.
objInParam.Properties_.Item("StartName") = "LocalSystem"
objInParam.Properties_.Item("StartPassword") = ""

' Execute the method and obtain the return status.
' The OutParameters object in objOutParams
' is created by the provider.

Set objOutParams = objWMIService.ExecMethod("Win32_Service.Name='MSSQL$SQL2000'", "Change", objInParam)

' List OutParams
Wscript.Echo "Out Parameters: "Wscript.echo "ReturnValue: " & objOutParams.ReturnValue

RDTSC

2008-06-08T23:20:00.000-07:00

I have seen a few cases where administrators have been concerned with CPU Drift and think that the SQL Server ERRORLOG reporting the following message is a serious cause for concern:

Error message 1
The time stamp counter of CPU on scheduler id 2 is not synchronized with other CPUs.
Error message 2
CPU time stamp frequency has changed from 191469 to 1794177 ticks per millisecond. The new frequency will be used

The SQL Server ERRORLOG reports a variety of informational, error and warning messages and not all messages are problems. This message is just telling you that CPU frequency between one or more processors is not synchronized. And how does this affect you??

Quoting from one of the below articles:

"Generally the Microsoft SQL Server support team considers drift less than several seconds, noise."

If you are concerned that the drift values are actually affecting your test results, then it would be a good idea to have the Speed Step, Power Now etc. features turned off during your testing phase. This would require changes at the BIOS level. Also, it would be a good idea to have consulted your H/W manufacturer and find out if there are any updates that require to be installed. Once again, I reiterate unless the drift values are constantly reporting several seconds for prolonged periods, only then do we have a Beginning of a problem, otherwise these warnings are mostly noise.

Additionally, trace flag (–T8033) can be used to suppress the drift warnings. However, please do not enable this trace flag on an instance of SQL Server 2005 unless and until, you fully understand the ramifications of ignoring the drift warnings.

Related Links
SQL Server timing values may be incorrect when you use utilities or technologies that change CPU frequencies
http://support.microsoft.com/kb/931279/en-us
SQL Server 2005 SP2 will introduce new messages to the error log related to timing activities
http://blogs.msdn.com/psssql/archive/2006/11/27/sql-server-2005-sp2-will-introduce-new-messages-to-the-error-log-related-to-timing-activities.aspx
SQL Server 2005 - RDTSC Truths and Myths Discussed
http://blogs.msdn.com/psssql/archive/2007/08/19/sql-server-2005-rdtsc-truths-and-myths-discussed.aspx

USERSTORE_TOKENPERM

2008-06-06T21:13:00.001-07:00

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

A large number of users running queries on the SQL instance and intermittently you find that the duration of queries increases intermittently
Most of the queries in the environment exhibit this pattern for random parameters and not any specific query/proc with any specific parameter
DBCC MEMORYSTATUS output for the SQL instance shows that the memory usage for SQL is gradually increasing over a period of time
High CPU during the problem period
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

Allow Updates Option

2008-06-06T21:06:00.001-07:00

SQL Server 2005 doesn't have the allow updates option. So, if you execute:

sp_configure 'allow_updates',1

and then if you execute reconfigure, you would get the following error:

Msg 5808, Level 16, State 1, Line 2
Ad hoc update to system catalogs is not supported.

After this all changes to the sp_configure settings followed by a reconfigure would yield this error. To rectify this, you will have to change the allow_updates option back to 0 and run reconfigure. As per SQL Server 2005 Books Online:

This option is still present in the sp_configure stored procedure, although its functionality is unavailable in Microsoft SQL Server 2005 (the setting has no effect). In SQL Server 2005, direct updates to the system tables are not supported.

Changing the allow updates option will cause the RECONFIGURE statement to fail. Changes to the allow updates option should be removed from all scripts.

So, if in case you use allow_updates in any script in SQL Server 2005, please refrain from doing so. Updates to the system catalogs are not permitted in SQL Server 2005 and any attempt/changes made to the System Resource database would get you into an unsupported scenario.

WMI Tracing

2008-06-02T05:30:00.001-07:00

A lot of companies find the need to monitor all DDL and DML activity on the server for Compliance reasons. Though SQL Profiler Traces provides this functionality by using Stored Procedures and Functions. These system stored procedures can be used from within your own applications to create traces manually, instead of using SQL Server Profiler. This allows you to write custom applications specific to the needs of your enterprise. However, you might choose not to use this approach due to the overhead of running profiler traces on the server. You can use server side profiler traces which tend to have a lesser performance impact on the server.

Another method is to use WMI Events to monitor SQL Server Events. Consider the following example, let's say you want to monitor the SQL Server events for all Update Statistics Events. Then you can use script below to create the WMI Alert and also the job to put the event details into a SQL Server database table.

USE TestDB
GO

-- Creating the table to store the DDL Events

CREATE TABLE [dbo].[UPD_STATS_Events](
[AlertTime] [datetime] NULL,
[SPID] [int] NULL,
[DBName] [nvarchar](100) NULL,
[TextData] [nvarchar](max) NULL
) ON [PRIMARY]

-- Adding the job to run when the WMI Alert is raised

EXEC msdb.dbo.sp_add_job @job_name=N'Capture Update Statistics Events',
@enabled=1,
@description=N'Job for responding to DDL events' ;
GO

-- Adding the job step

EXEC msdb.dbo.sp_add_jobstep
@job_name = N'Capture Update Statistics Events',
@step_name=N'Insert data into Update Statistics Events table',
@step_id=1,
@on_success_action=1,
@on_fail_action=2,
@subsystem=N'TSQL',
@command= N'INSERT INTO UPD_STATS_Events
(AlertTime, Spid,DBName,TextData)
VALUES (getdate(), $(ESCAPE_NONE(WMI(SPID))),
''$(ESCAPE_SQUOTE(WMI(DatabaseName)))'',
''$(ESCAPE_SQUOTE(WMI(TSQLCommand)))'')',
@database_name=N'TestDB' ;
GO

-- Set the job server for the job to the current instance of SQL Server.
EXEC msdb.dbo.sp_add_jobserver @job_name = N'Capture DDL Events' ;
GO
-- Add an alert that responds to all DBCC events for
-- the default instance. To monitor deadlocks for a different instance,
-- Change MSSQLSERVER to the name of the instance.
-- For the named instance you need to use \\.\root\Microsoft\SqlServer\ServerEvents\<INSTANCE NAME>

EXEC msdb.dbo.sp_add_alert @name=N'Respond to DDL Events',
@wmi_namespace=N'\\.\root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER',
@wmi_query=N'SELECT * FROM UPDATE_STATISTICS',
@job_name='Capture DBCC Events' ;
GO

</script>

Whenever an update statistics task is fired against a database, the time when the command was executed, the SPID number, the database name and the T-SQL Command command associated with the event would be put into the table in the TestDB database.

Service Broker should be enabled for the MSDB and the database in which you are storing the event details.

Information about the namespaces can be found on MSDN. Also, you could use WMI Code Creator to browse through the namespace and the classes available and their properties.

Furthermore, you can also use the WMI Event Watcher Task or the WMI Data Reader Task of SQL Server 2005 SSIS to perform the same.

The product instance been patched with more recent updates

2008-05-30T20:32:00.001-07:00

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

2008-05-30T18:38:00.001-07:00

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:

Security rules
Database Engine rules
Analysis Services rules
Replication rules
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.

Connect: SQL Server

2008-05-25T13:09:00.001-07:00

I have spoken to a lot of people who are not really aware of the right medium of submitting product feedback for SQL Server or reporting defects online. The CONNECT site for MS is the way to go here. For reporting SQL Server related issues, please use the link http://connect.microsoft.com/sqlserver

Product Wishes can also be filed here. If you do submit product defect that can be fixed then the Dev team will definitely fix it. Fixing a part of the code requires lot of strategic thinking as well as testing. One of the biggest reasons why some fixes are rejected is because of the fact this current change in the code would impact a lot of other parts of the product which is working seamlessly fine or has the possibility of impacting a fully functional error free component in an adverse manner.

So, in future if you do need to submit product feedback, please do so at Connect: SQL Server

We always welcome feedback regarding our Products and want our user community to tell us how to improve the same and what features they think they would be benefited with the most.

Cluster Failovers

2008-05-11T08:51:00.000-07:00

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

2008-05-11T07:46:00.000-07:00

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

2008-05-11T07:13:00.000-07:00

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

2008-03-22T08:56:00.000-07:00

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

Best Practice Analyzer

2008-03-22T08:38:00.000-07:00

I have seen a lot of people asking "Are we following best practices for our SQL Servers?". The Best Practice Analyzer cantry 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

Transaction Log Growth

2007-12-29T12:17:00.000-08:00

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

2007-10-28T04:13:00.000-07:00

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

How to update statistics for large databases

2007-10-24T08:23:00.000-07:00

Statistics are something that the SQL Optimizer depends on to generate a plan. The more outdated your statistics are, greater are the chances that your query optimizer will land up with a sub-optimal plan.

So what do you do when you have a database which is highly transactional in nature and is quite large in size. So, you might land up in a scenario where the database is online 24X7 and there is no downtime window where you can update your statistics with a FULLSCAN.

Before we get into what my opinion is about updating statistics of a database is, lets make sure we understand what I mean by statistics being "up-to-date". Statistics being up-to-date is the sampling rate used while updating statistics was 100% or they were updated with a FULLSCAN. Statistics updated using AUTO UPDATE STATISTICS property of a database will not update statistics with a FULLSCAN contrary to popular belief. The sole reason for this being is that suppose a table has 1 GB of data and a query on the database initiated a AUTO UPDATE of a STAT on this database, then if the sampling was 100%, then your query duration would take an eternity.

So, if you have a large database:
1. Ensure that the AUTO UPDATE STATS is turned on for the database. This would provide for some relief to the database
2. Identify which tables need to have their statistics updated with a fullscan. Only those would be required which have the maximum number of queries running on them
3. Next identify a period when the database usage is low. This is the time when you can use to update the stats of the other tables which did not qualify the list in #2.
4. Use a script to update statistics of the tables indentified step # 2 probably based on rowmodctr values (ROWMODCTR: Counts the total number of inserted, deleted, or updated rows since the last time statistics were updated for the table. In SQL Server 2005, this is not going to be always helpful [Ref: http://msdn2.microsoft.com/en-us/library/ms190283.aspx]. But in SQL 2000, this could be used as a deciding factor)

Sample Script
============

Let's say I had identified 4 tables in STEP 2:

CREATE TABLE UPD_STATS_TBL (tblname varchar(10), dt_updated datetime, rowmodctr bigint)

SELECT * FROM UPD_STATS_TBL
tblname dt_updated rowmodctr
TBL1 2007-10-24 21:13:46.123 20000
TBL2 2007-10-23 21:13:46.123 400000
TBL3 2007-10-22 21:13:46.123 508000
TBL4 2007-10-24 20:13:46.123 87000

**************************************************
CREATE PROC UPD_STATS_DB
AS
DECLARE @tbl varchar(10)

UPDATE upd_stats_tbl SET rowmodctr = (SELECT MAX(rowmodctr) from sys.sysindexes where id=OBJECT_ID(tblname))

SELECT TOP 1 @tbl = tblname
FROM UPD_STATS_TBL
WHERE rowmodctr >
ORDER BY dt_updated

DECLARE @stmt varchar (100)

SET @stmt = 'UPDATE STATISTICS'+SPACE(1)+@tbl+SPACE(1)+'WITH FULLSCAN'

EXEC (@stmt)

UPDATE upd_stats_tbl SET dt_updated = (SELECT GETDATE())
WHERE tblname = @tbl

**************************************************

NOTE: You could refine this down to the table statistic if you wanted to.

Missing Indexes in SQL Server 2005

2007-10-24T08:04:00.000-07:00

Indexes are essential in making sure that your queries have effecient query plans and for SELECTS, you don't end up doing searches on HEAPS.

One of the biggest improvements in SQL Server 2005 is that it tracks all the transactions happening on the server and makes a list of indexes which could prove beneficial for those queries. Of course, one index might be beneficial for one query but detrimental to another. So, it is highly essential that we test the feasibility of implementing these indexes on a production environment before rolling out changes to our indexes.

The missing indexes feature is on by default. No controls are provided to turn the feature on or off, or to reset any of the tables returned when the dynamic management objects are queried. When SQL Server is restarted, all of the missing index information is dropped. This feature can only be disabled if an instance of SQL Server is started by using the -x argument with the sqlservr command-prompt utility.

So, the first thing I would do if a query or a set of queries are running slowly in SQL Server, I would query the DMVs related to these missing indexes and find out if there are an indexes related to the tables on which those slow running queries are executing. This feature becomes highly useful when the following conditions are true:
1. NO CPU bottleneck
2. NO Blocking on the server
3. NO Disk bottleneck

You can view a list of all the missing indexes using Performance Dashboard which can be used with SQL Server 2005 Service Pack 2 and above.

Relate Links for Missing Indexes
About Missing Indexes
http://msdn2.microsoft.com/en-us/library/ms345524.aspx
Finding Missing Indexes
http://msdn2.microsoft.com/en-us/library/ms345417.aspx
Limitations of this feature
http://msdn2.microsoft.com/en-us/library/ms345485.aspx

SQL Server is not configured for Remote Connections

2007-10-13T04:25:00.000-07:00

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.

Federated Servers

2007-10-12T22:24:00.000-07:00

To achieve the high levels of performance required by the largest Web sites, a multitier system typically balances the processing load for each tier across multiple servers. SQL Server 2005 shares the database processing load across a group of servers by horizontally partitioning the data in a SQL Server database. These servers are managed independently, but cooperate to process the database requests from the applications; such a cooperative group of servers is called a federation.

A federated database tier can achieve very high levels of performance only if the application sends each SQL statement to the member server that has most of the data required by the statement. This is referred to as collocating the SQL statement with the data required by the statement. Collocating SQL statements with the required data is not a requirement unique to federated servers. It is also required in clustered systems.

Federated Server Tier

There is one instance of SQL Server on each member server.

Each member server has a member database. The data is spread through the member databases.

The tables from the original database are horizontally partitioned into member tables. There is one member table per member database, and distributed partitioned views are used to make it appear as if there was a full copy of the original table on each member server.

The application layer must be able to collocate SQL statements on the member server that contains most of the data referenced by the statement.

Backing Up and Restoring Federated Database Servers

In a federated-database-server tier that is built by using distributed partitioned views, the member servers form one logical unit. Therefore, you must coordinate the recovery of the member databases to make sure that they remain synchronized correctly.

SQL Server 2005 does not require that you coordinate backups across member servers. Backups can be independently taken from each database, without regard for the state of the other member databases. Because the backups do not have to be synchronized, there is no processing overhead for synchronization and no blockage of running tasks.

The most important aspect of recovering a set of member databases is the same as recovering any other database: Plan and test the recovery procedures before you put the databases into production. You must set up processes to restore all the databases to the same logical point in time. SQL Server includes features to support the recovery of all member databases to the same point in time.

Pros

1. Federated servers if implemented correctly are a great way to load balance a database server environment and is very similar to a database farm implementation.

2. This would greatly allow you to distribute the load on your servers based on any of the following criteria:

a. Geographic location

b. Traffic in terms of users

c. Traffic in terms of transactions

d. Database table size

3. Also, federated servers give you the option on partitioning data across servers with the help of distributed partitioned views

4. This gives you the option of horizontally partitioning the data across various servers which ultimately leads to greater throughput

5. It lets you control the traffic coming in and also helps in maintaining the load thresholds across the entire setup

6. Furthermore, if we have a middle tier in the entire setup, then design changes in the federated server environment will not affect the client side applications in any manner as they would be connecting to the middle tier and the middle tier will connect with the database server

Cons

1. One of the major drawbacks is disaster recovery. If one of the member server fails, there needs to a failback plan in place which could cause minimum hindrance to the normal operations while implementation.

2. Also, if distributed partitioning is being implemented, then rules and constraints need to be strong enough to prevent any sort on inconsistencies from arising due to data modification

3. Also, if the performance of one of the member servers takes a hit, most of the distributed partitioned views would also take a hit in terms of operations done on them

4. The middle tier should be designed in such a robust manner that there is no ambiguity in resolving which server in the environment needs to targeted based on the query coming into the server

5. The backup/restore scenarios need to be designed and planned in such a way that all the member servers are all synchronized at all times

Useful Articles

The following whitepaper and TechNet articles should help a great deal here:

Planning, Implementing, and Administering Scaleout Solutions with SQL Server 2005Solution

http://download.microsoft.com/download/4/7/a/47a548b9-249e-484c-abd7-29f31282b04d/ImplementingScaleOut.doc

Scaling Out SQL Server with Data Dependent Routing

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

Designing Data Tier Components and Passing Data Through Tiers http://msdn2.microsoft.com/en-us/library/ms978496.aspx

SQL Server 2005 Upgrade

2007-10-12T07:18:00.000-07:00

Upgrading from SQL Server 2000 to SQL Server 2005 is always a daunting task if you have had a chance to contemplate as to what needs to be done before and after the upgrade process. On the other hand, if you had a chance to plan out everything before hand, then it is very unlikely that you would land yourself in a soup unless and until the Gods choose to be very unkind towards you on that fateful day. :)

So, first let us understand what are the two procedures of upgrading from SQL Server 2000 to SQL Server 2005.

1. IN PLACE UPGRADE
2. SIDE BY SIDE UPGRADE

So, before I go ahead and explain these two upgrade procedures, let us dwell on the pre-requisites for upgrading to SQL Server 2005.

1. Testing: Make sure all your applications are thoroughly tested and function as expected on a SQL Server 2005 environment before deciding to take the final step
2. Fall-back plan: This is what I call "insurance". It's a hassle but when a problem does happen, this is what takes care in helping you out of the problem. I believe it is better to be safe than sorry.
3. Backups: This should have been included in Point 2 but this is something of utmost importance, so I thought I would re-iterate this again. We need to have backups of all the user and system (YES! we need them.) databases in case we need to use our fall-back plan.
4. Upgrade Advisor: Run the upgrade advisor on your existing SQL Server 2000 installation and find out if there are any REDFLAGS in upgrading to SQL Server 2005 based on your current configuration. (Please check the following link for more details: https://partner.microsoft.com/40001607)

In-place upgrade process
The upgrade process which refers to upgrading all the existing installed components of SQL Server 2000 to SQL Server 2005 on the same server.

Side-by-side upgrade process
The upgrade process which refers to leaving your existing SQL Server installation intact and installing a fresh SQL Server 2005 instance on the same server or a different server and then migrating over all your databases. Once, all the components (Databases, Plans, DTS Packages etc.) have been migrated the SQL 2000 installation can then be phased out.

Both these upgrade processes have their PROs and CONs which are clearly documented in the TechNet Article mentioned below.

Upgrade Handbook for SQL Server 2005
http://www.microsoft.com/technet/prodtechnol/sql/2005/sqlupgrd.mspx
Upgrading to SQL Server 2005
http://www.microsoft.com/sql/solutions/upgrade/default.mspx
Technet Article on Upgrading SQL Server 2000 to SQL Server 2005
http://www.microsoft.com/downloads/details.aspx?FamilyID=3d5e96d9-0074-46c4-bd4f-c3eb2abf4b66&DisplayLang=en

So, what do we need to take care of when upgrading to SQL Server 2005 from SQL Server 2000?

1. Replication: If you do have replication configured (subscriber/publisher/distributor), you need to find out if your replication topology is compatible and will continue to function smoothly if the current server is upgraded?
2. DTS Packages: These need to migrated to SQL Server 2005 using the Migration Wizard. Related Article: http://msdn2.microsoft.com/en-us/library/ms143501.aspx
3. Maintenance Plans: Again they are different from their SQL Server 2000 counterparts because in SQL 2005, we use SSIS in the background.
4. Logins: In case of an side-by-side upgrade, then these need to be migrated too.

These are a few of prime areas of concern that come to my mind. A whole bunch of other considerations are mentioned in the above mentioned Technet Whitepaper. Please do read through that if you are planning to upgrade to SQL Server 2005.

SQL Server Tools

2007-10-03T00:31:00.001-07:00

The following tools could be really helpful in monitoring your SQL Server instances:

Best Practices Analyzer Tool for Microsoft SQL Server 2000 1.0
http://www.microsoft.com/downloads/details.aspx?displayla%20ng=en&familyid=B352EB1F-D3CA-44EE-893E-9E07339C1F22&displaylang=en

SQL Server 2005 Performance Dashboard Reports (Requires minimum Service Pack2 for SQL 2005)
http://www.microsoft.com/downloads/details.aspx?FamilyId=1D3A4A0D-7E0C-4730-8204-E419218C1EFC&displaylang=en

This is quite a useful resource for managing SQL Server:
SQL Server 2000 Administrator's Pocket Consultant
http://www.microsoft.com/technet/prodtechnol/sql/2000/books/adminpoc.mspx
Microsoft SQL Server 2005 Administrator's Pocket Consultant
http://www.microsoft.com/mspress/books/6794.aspx

SQL Server Tools

2007-10-03T00:31:00.000-07:00

Backup Strategy

2007-10-03T00:27:00.000-07:00

Backup strategy needs to be designed in such a manner that it causes minimum amount of downtime in case during a disaster recovery scenario if backups need to be restored. The backup strategy depends on the following:

1. Size of the database – This would determine how often a FULL BACKUP can be taken
2. Recovery Model – This would determine if transaction log/differential backups are possible
3. The importance of the database – This would determined how often transaction log backups need to be takenIf the database is in “simple” recovery model, then the only option that we would have is to take full backups.

If the database is in FULL/BULK-LOGGED recovery models, then we have the option of taking transaction log/differential backups.

Now the next question is how often to take a transaction log backup. Depending of the importance of the database, transaction log backups can be taken every 10-15 minutes or even every hour. This needs to be determined at your end. Furthermore, if the transaction log backups are being taken very frequently and a full backup is taken once a week, then it is advisable to take differential backups during the middle of the week. This would ensure that the process of restore becomes less cumbersome as after the full backup, then differential backup can be applied followed by the transactional log backups.

In case of full/bulk-logged recovery models, the following backup strategy would be ideal:

1. Full backup

a. Differential backup(s)

i. Transaction log backup(s)

b. Differential backup(s)

i. Transaction log backup(s)

2. Full backup (and repeat the above cycle)

Furthermore, for creating sound fail-over strategies for disaster recovery scenarios, the following can be considered:
1. Log shipping
2. Database Mirroring (For SQL 2005 SP1 and above)
3. Replication

Also, it is considered a good practice to restore a backup on a test server and a CHECKDB run on the restored database to make sure that the backup is in good shape. This becomes of utmost importance when it is not possible to run a CHECKDB before taking a backup. In SQL Server 2005, you have the option to perform a piece meal restore which would allow you to restore up to the page level in case you have database corruption.

The following articles could be helpful:
Overview of Restore and Recovery in SQL Server
http://msdn2.microsoft.com/en-us/library/ms191253.aspx
SQL Server 2005 provides the option of performing an online restore which is available for SQL Server 2005.

Please refer the following for more information:
Performing Online Restores
http://msdn2.microsoft.com/en-us/library/ms188671.aspx
Storage Top 10 Best Practices
http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/storage-top-10.mspx
Using Recovery Models
http://msdn2.microsoft.com/en-us/library/aa173678(SQL.80).aspx

Statistics Update

2007-10-03T00:20:00.000-07:00

This is an important task in terms of database maintenance. This ensures the statistics are up-to-date which in turn would ensure that the query optimizer doesn’t land up with sub-optimal plans. The two ways to monitor for the need for an update on statistics are:

1. STATS_DATE function (http://msdn2.microsoft.com/en-us/library/ms190330.aspx) 2. rowmodctr value in the sysindexes (For SQL 2000) and sys.sysindexes (For SQL 2005) output

The STATS_DATE function would give you the date when the statistics were updated last. The rowmodctr value would tell you how many changes have taken place in terms of update, inserts and deletes in the data of the column the index is associated with.

The frequency of statistics update depends on the following:
1. Do BULK operations happen on the database tables in question?
2. Is there an off-peak period where a statistics update with full scan can be done?
3. How often is the data modified in the database tables in question?

Statistics update can be a resource intensive operation depending on the size of the table. If the data in the tables in question change very rarely, then a statistics update with a full scan can be done during a maintenance window. Statistics update is always an ONLINE operation and doesn’t cause the database to be in an OFFLINE mode.
If there are BULK operations happening in a table, then statistics have a tendency to getting skewed after the BULK operation. The best option is do perform a statistics update on this table if this tables is to be used by an application after the bulk operation.
If the database is updated very frequently and the database is very large in size, then it needs to be determined which tables are most frequently updated. Based on this, a statistics updated can be performed for only those tables and a statistics update can be done for the entire database with a lower sampling rate like 20-30% depending on what is suitable. This can be determined by comparing historical data and finding out what kind of sampling rate is suitable for your needs.
Another option is to enable AUTO UPDATE STATISTICS option for the database. But it needs to be monitored whether this is a boon or a bane. AUTO UPDATE STATISTICS can be good when the statistics are relatively updated and it makes sure that the statistics don’t fall too out of place. This feature has a downside when the statistics become out-dated too frequently, then you would have AUTO UPDATE STATISTICS being fired every time and this would cause all cached plans associated with the rows whose statistics have been updated to be recompiled. This can cause a serious bottleneck if there are too many auto update statistics events fired in short time span.

The following methods can be used to update the statistics:
1. UPDATE STATISTICS WITH FULLSCAN (or a lower sampling rate) Please refer the following article for further details: http://msdn2.microsoft.com/en-us/library/aa260645(SQL.80).aspx
2. sp_updatestats (http://msdn2.microsoft.com/en-us/library/ms173804.aspx)
3. A maintenance plan or SQL Agent job to update the statistics

The following script for SQL Server 2005 would be helpful in determining how badly affected the statistics are for the index associated with it. The rowmodctr counts the total number of inserted, deleted, or updated rows since the last time statistics were updated for the table.

A DBCC DBREINDEX would update the statistics associated with those indexes. But this would not update the AUTO CREATED STATISTICS. The DBCC SHOW_STATISTICS (http://msdn2.microsoft.com/en-us/library/aa258821(SQL.80).aspx) command could also help you to determine the statistics condition for the particular index. The “Row Sampled” and “Rows” if equal would indicate that the sampling is currently 100%.

Statistics for INDEX 'pk_customers'.

Updated Rows Rows Sampled Steps Density Average key length -------------------- -------------------- -------------------- ------ ------------------------ ------------------------
Jun 23 2007 5:03PM 91 91 91 1.0989011E-2 10.0

The following articles could be helpful:

UPDATE STATISTICS
http://msdn2.microsoft.com/en-us/library/aa260645(SQL.80).aspx
Statistics used by Query Optimizer in SQL Server 2005
http://www.microsoft.com/technet/prodtechnol/sql/2005/qrystats.mspx

Re-organize data and index pages

2007-10-03T00:11:00.000-07:00

There are a few ways that this can be achieved:

1. Shrinking the database files (data file or transaction log file)

2. DBCC DBREINDEX (Similar to dropping and re-creating the indexes)

3. DBBC INDEXDEFRAG (De-fragmenting the indexed pages to remove logical scan fragmentation)

Note: It needs to be kept in mind that DBREINDEX is a fully-logged operation and can cause the transaction log to grow by large amounts when the database size is considerably large. So there have been situations when the transaction log has grown out of proportion and filled up the entire disk. If the T-log does grow out of proportion and you do choose to truncate/shrink the file, then please do so after verifying the points below:

1. Transactional replication is not configured for the database

2. Log shipping is not configured for the database

3. Database mirroring is not configured for the database (For SQL 2005 SP1 and above)

4. Any other operations are not being performed which need transactional log consistency in terms of LSN chains

Online re-indexing is a feature which is available in SQL Server 2005 Enterprise Edition only. Please note that rebuilding the indexes would cause the statistics associated with those indexes to also be updated. This however would not update the AUTO CREATED statistics.

The following articles could be useful:

SET OPTION considerations when running DBCC with indexes on computed columns

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

DBCC DBREINDEX

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

The DBCC SHOWCONTIG output (http://msdn2.microsoft.com/en-us/library/aa258803(SQL.80).aspx) for SQL Server 2000 and 2005 will help you find out the current status of fragmentation for the database. A sample output would look like this:

DBCC SHOWCONTIG scanning 'Customers' table...

Table: 'Customers' (2073058421); index ID: 1, database ID: 6

TABLE level scan performed.

- Pages Scanned................................: 3

- Extents Scanned..............................: 2

- Extent Switches..............................: 1

- Avg. Pages per Extent........................: 1.5

- Scan Density [Best Count:Actual Count].......: 50.00% [1:2]

- Logical Scan Fragmentation ..................: 0.00%

- Extent Scan Fragmentation ...................: 50.00%

- Avg. Bytes Free per Page.....................: 246.7

- Avg. Page Density (full).....................: 96.95%

The logical scan fragmentation should ideally be below 20% but anything above 40% would mean an INDEXDEFRAG or DBREINDEX is required. The script which is from the same MSDN article can help you run an INDEXDEFRAG or DBREINDEX based on the logical scan fragmentation count.

A sample script for the same can be found under the topic of "DBCC SHOWCONTIG" in SQL Server Books Online (2000 and 2005)

In SQL Server 2005, the following features may be helpful to you while using the ALTER INDEX command:

REBUILD [ WITH ( [ ,... n]) ]

Specifies the index will be rebuilt using the same columns, index type, uniqueness attribute, and sort order. This clause is equivalent to DBCC DBREINDEX. REBUILD enables a disabled index. Rebuilding a clustered index does not rebuild associated nonclustered indexes unless the keyword ALL is specified. If index options are not specified, the existing index option values stored in sys.indexes are applied. For any index option whose value is not stored in sys.indexes, the default indicated in the argument definition of the option applies.

The options ONLINE and IGNORE_DUP_KEY are not valid when you rebuild an XML index.

If ALL is specified and the underlying table is a heap, the rebuild operation has no affect on the table. Any nonclustered indexes associated with the table are rebuilt.

The rebuild operation can be minimally logged if the database recovery model is set to either bulk-logged or simple.

WITH ( LOB_COMPACTION = { ON OFF } )

Specifies that all pages that contain large object (LOB) data are compacted. The LOB data types are image, text, ntext, varchar(max), nvarchar(max), varbinary(max), and xml. Compacting this data can improve disk space use. The default is ON.

All pages that contain large object data are compacted.Reorganizing a specified clustered index compacts all LOB columns that are contained in the clustered index. Reorganizing a nonclustered index compacts all LOB columns that are nonkey (included) columns in the index. For more information, see Creating Indexes with Included Columns. When ALL is specified, all indexes that are associated with the specified table or view are reorganized, and all LOB columns that are associated with the clustered index, underlying table, or nonclustered index with included columns are compacted. OFF Pages that contain large object data are not compacted.

OFF

has no affect on a heap.The LOB_COMPACTION clause is ignored if LOB columns are not present.

REORGANIZE

Specifies the index leaf level will be reorganized. This clause is equivalent to DBCC INDEXDEFRAG. ALTER INDEX REORGANIZE statement is always performed online. This means long-term blocking table locks are not held and queries or updates to the underlying table can continue during the ALTER INDEX REORGANIZE transaction. REORGANIZE cannot be specified for a disabled index or an index with ALLOW_PAGE_LOCKS set to OFF.

Furthermore, the dynamic management view sys.dm_db_index_physical_stats (http://msdn2.microsoft.com/en-us/library/ms188917.aspx) would help you get statistical information pertaining to indexes on SQL Server 2005. The columns mentioned below in this DMV output could be helpful in analyzing the need for index reorganization.

avg_fragmentation_in_percent: Logical fragmentation for indexes, or extent fragmentation for heaps in the IN_ROW_DATA allocation unit. The value is measured as a percentage and takes into account multiple files. For definitions of logical and extent fragmentation, see Remarks. 0 for LOB_DATA and ROW_OVERFLOW_DATA allocation units.NULL for heaps when mode = SAMPLED.

fragment_count: Number of fragments in the leaf level of an IN_ROW_DATA allocation unit. For more information about fragments, see Remarks.NULL for nonleaf levels of an index, and LOB_DATA or ROW_OVERFLOW_DATA allocation units. NULL for heaps when mode = SAMPLED.

avg_fragment_size_in_pages: Average number of pages in one fragment in the leaf level of an IN_ROW_DATA allocation unit. NULL for nonleaf levels of an index, and LOB_DATA or ROW_OVERFLOW_DATA allocation units. NULL for heaps when mode = SAMPLED.

The following article could be helpful:Microsoft SQL Server 2000 Index Defragmentation Best Practices

http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx

Database Maintenance Plans

2007-10-03T00:08:00.000-07:00

Database Maintenance Plans for SQL Server 2000

Database Maintenance Plans offer the following features in SQL Server 2000:

1. Backing up databases
2. Reorganization of indexes and data pages
3. Statistics Update
4. Shrinking the database
5. Database integrity checks

The above tasks of the maintenance plans would have associated with respective SQL Agent jobs and also these could be scheduled to suit your needs. Furthermore, these jobs could be created manually also without the help of maintenance plans.

Database Maintenance Plans for SQL Server 2005
In addition to the above mentioned features, SQL Server 2005 Enterprise Edition provides the option of rebuilding the indexes as an online operation. This makes it possible for the indexes to remain online while a rebuild index task is being performed on the database.

ONLINE = { ON OFF }
Specifies whether underlying tables and associated indexes are available for queries and data modification during the index operation. The default is OFF.
Note: Online index operations are available only in SQL Server 2005 Enterprise Edition.

ON
Long-term table locks are not held for the duration of the index operation. During the main phase of the index operation, only an Intent Share (IS) lock is held on the source table. This enables queries or updates to the underlying table and indexes to continue. At the start of the operation, a Shared (S) lock is held on the source object for a very short amount of time. At the end of the operation, for a short time, an S (Shared) lock is acquired on the source if a nonclustered index is being created; or an SCH-M (Schema Modification) lock is acquired when a clustered index is created or dropped online and when a clustered or nonclustered index is being rebuilt. ONLINE cannot be set to ON when an index is being created on a local temporary table.

OFF
Table locks are applied for the duration of the index operation. An offline index operation that creates, rebuilds, or drops a clustered index, or rebuilds or drops a nonclustered index, acquires a Schema modification (Sch-M) lock on the table. This prevents all user access to the underlying table for the duration of the operation. An offline index operation that creates a nonclustered index acquires a Shared (S) lock on the table. This prevents updates to the underlying table but allows read operations, such as SELECT statements.

The frequency of these tasks and which tasks are needed need to be performed need to be decided on the following factors:
1. Importance of the database in question
2. Size of the database
3. Maintenance window frequency
4. Load on the server
5. Whether it is an OLTP or OLAP database or both
6. The kind of transactions that take place on the database (Point 5 and 6 would decide how often index reorganization/index rebuild/statistics updates are required and whether a shrink database/file is required)

Stuck Stalled IO Messages

2007-09-23T11:09:00.001-07:00

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

2007-09-16T14:05:00.000-07:00

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.

Database Integrity Checks

2007-09-03T11:20:00.000-07:00

Database Integrity Checks Database integrity checks are recommended to ensure that the database consistency is intact and if there is a problem with consistency, then it is reported to the appropriate team(s) so that necessary action can be taken to rectify it. This can be done with the help of Database Maintenance Plans. In the event that a CHECKDB on a database fails, then it needs to be reported by Email(using xp_sendmail or SQL Agent Mail or Database Mail for SQL Server 2005) or events fired in the Operating System Event Logs (if these are monitored regularly) with the help of Operators which can be configured for SQL Server Agent. The xp_sendmail feature is not available for SQL 2005 64-bit versions.

The frequency of these checks largely depends on the following factors:
1. Importance of the database
2. How often data changes in the database (If a database integrity check fails for a database where data is not modified, then it would be advisable to restore the last known good backup rather than trying to repair the consistency database)
3. The size of the database
4. In the event of consistency checks failing, it needs to be determined which is the most feasible option:
a. Restore the last known good backups in accordance with the recovery model for that database to allow for a minimum amount of data loss
b. Or try and repair the database and falling back on Option (a) only if this fails In case a repair option is suggested in the CHECKDB output, it is important to note that a REPAIR_ALLOW_DATA_LOSS be never performed on the database without understanding its full consequences and consulting Microsoft PSS. In the event, that this route needs to be taken, it is always recommended to fall back on the last known good backups if possible. The REPAIR_FAST and REPAIR_REBUILD repair options can be performed without having any data loss. Please note that these are time consuming operations and in the event of database inconsistency it is not possible for us to predict how long these tasks would run for. Also, the time taken for CHECKDB on a database cannot be predicted. An educated guess can be made to how long it would take by referring to the last durations of the CHECKDB operations on that particular database. For the above mentioned repair options, please refer the following article: http://msdn2.microsoft.com/en-us/library/aa258278(SQL.80).aspx

REPAIR_ALLOW_DATA_LOSS
Performs all repairs done by REPAIR_REBUILD and includes allocation and deallocation of rows and pages for correcting allocation errors, structural row or page errors, and deletion of corrupted text objects. These repairs can result in some data loss. The repair may be done under a user transaction to allow the user to roll back the changes made. If repairs are rolled back, the database will still contain errors and should be restored from a backup. If a repair for an error has been skipped due to the provided repair level, any repairs that depend on the repair are also skipped. After repairs are completed, back up the database.

REPAIR_FAST
Performs minor, nontime-consuming repair actions such as repairing extra keys in nonclustered indexes. These repairs can be done quickly and without risk of data loss.

REPAIR_REBUILD
Performs all repairs done by REPAIR_FAST and includes time-consuming repairs such as rebuilding indexes. These repairs can be done without risk of data loss. In case of SQL Server 2005, you have the option of checking the suspect_pages in the MSDB database to find out the affected pages.

Please refer the following articles for more detailed information:
Suspect_pages table (SQL 2005)
http://msdn2.microsoft.com/en-us/library/ms174425.aspx
Understanding and managing the suspect_pages table
http://msdn2.microsoft.com/en-us/library/ms191301.aspx
Designing a Backup and Restore Strategy
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_bkprst_63eh.asp

In the event, that only a few pages have a problem, then for SQL Server 2005 a page level restore can be performed. Online page restore operation is a feature available in SQL Server 2005 Enterprise Edition but all other Editions of SQL Server 2005 support offline page level restores. The page level restores are done using the NORECOVERY option for that database. Then a backup of the current transaction log is taken and applied to the database with the RECOVERY option. This feature is applicable to databases in FULL or BULK-LOGGED recovery models.

For performing page level restores, please refer the following article: http://msdn2.microsoft.com/en-us/library/ms175168.aspx

It is highly important that a disaster recovery plan is in place to ensure the following:
· A plan to acquire hardware in the event of hardware failure
· A communication plan.
· A list of people to be contacted in the event of a disaster.
· Instructions for contacting the people involved in the response to the disaster.
· Information on who owns the administration of the plan.
· A checklist of required tasks for each recovery scenario. To help you review how disaster recovery progressed, initial each task as it is completed, and indicate the time of completion on the checklist.

In conclusion, if the database is of a considerably large size, then an integrity check needs to be scheduled during a window when the load on the server is at a minimum. The definition of minimum here refers to a load which is lesser than the normal workload on the server. If the database sizes are quite small, then daily integrity checks on the database would be the order of the day.

It is recommended that DBCC CHECKDB be run during hours when the load is light on the server. If DBCC CHECKDB is run during heavy peak usage time, expect a performance hit on the transaction throughput as well as DBCC CHECKDB completion time.

Recommendations for Good DBCC Performance
· Run CHECKDB when the system usage is low.
· Be sure that you are not performing other disk I/O operations, such as disk backups.
· Place tempdb on a separate disk system or a fast disk subsystem.
· Allow enough room for tempdb to expand on the drive. Use DBCC with ESTIMATE ONLY to estimate how much space will be needed for tempdb.
· Avoid running CPU-intensive queries or batch jobs.
· Reduce active transactions while a DBCC command is running.
· Use the NO_INFOMSGS option to reduce processing and tempdb usage significantly.

Server Side Profiler Traces

2007-09-03T00:30:00.000-07:00

When we want to monitor the kind of queries that hit across your SQL Server, most of us set up a Profiler Trace. But what most of us are not aware of is that there are two ways to setup a Profiler trace:

1. A GUI based Profiler trace

2. A Server side Profiler trace

A GUI based profiler trace causes a performance impact on the server as high as 25% depending on the events being captured. So if you are monitoring SQL Server with the help of profiler traces on a long term basis then it would always be advisable to setup a server side profiler trace.

Steps to setup a Server side Profiler Trace
1. Create a Profiler Trace using the GUI and then script out the trace to a file called say sql_trace.sql. Open the SQL_TRACE.SQL file in query analyzer/management studio query window and make the following changes

2. In the line “exec @rc = sp_trace_create @TraceID output, 2, N'', @maxfilesize, NULL”, change the location where you want to store the trace file.

3. Make sure @maxfilesize parameter is set to 350 or a desirable size. (This makes sure that you roll over to the next trace file once the current size hits 350 MB)

4. Execute the script to create the trace. This will generate a Trace ID. The trace information can be obtained from fn_trace_getinfo function

5. To stop the trace, use sp_trace_setstatus ,0

6. To close the specified trace and delete its definition from the server us sp_trace_setstatus ,2

Useful Articles

283790 INF: How to Create a SQL Server 2000 Trace

283786 How to monitor SQL Server 2000 traces

Setting Up PerfMon Logs

2007-09-03T00:22:00.000-07:00

One of best ways to monitor your system performance is to use Windows Performance Monitor Logs and Alerts. These logs can be used to capture historical information about different performance objects and would help us understand what was happening on the system at that point of time.

Steps to setup a Perfmon Log

This can be done by opening up Perfmon:
Click on “Performance Logs and Alerts”
Click on “Counter Logs”
Right click on the same and click on “New Log Settings”
Give the log a name
Click on “Add Objects” and add all the objects
Click on the “Log Files” tab
You can change the log file location by clicking on the “Configure” button
Click on the “Schedule” tab and then for “Start Log” and “Stop Log”, select the “Manually (Using the Shortcut menu)” option
Click on the “OK” button to create the log
Then right-click on the Log and click on “Start” to begin the logging
Right-click on the log file and click on “Stop” to end the logging
There is hardly any performance impact in collecting perfmon logs on any server

Blog Introduction

2007-08-05T01:18:00.000-07:00

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