Troubleshooting Performance

A useful chart for iterative processes to deal with common query performance and scalability issues for SQL Server 2005/2008.

troubleshooting-sql-server-2005-2008-performance-and-scalability-flowchart.aspx

Service Pack 4 for SQL Server 2005

The next service pack for SQL Server 2005 has been released. Service pack 4 includes cumulative update 1 to 11, some customer requested fixes as well as database Engine support for DAC operations.

It can be downloaded here.

SQL Server Codenames

I was interested to see what all the SQL codenames were

Sphinx - SQL Server 7.0
Plato - SQL Server 7.0 OLAP Services
Shiloh - SQL Server 2000
Liberty - SQL Server 2000 (64-bit)
Rosetta - SQL Server Reporting Services
Yukon - SQL Server 2005
Katmai - SQL Server 2008
Kilimanjaro - SQL Server 2008 R2
Madison - Microsoft SQL Server 2008 R2 Parallel Data Warehouse
Bulldog - SQL Server 2008 R2 Master Data Services (Master Data Management)

SQL Server Profiler

There are 2 ways to run SQL Profiler

• Run the SQL Profiler client tool through the GUI
• Server-side tracing where events are saved to a physical file on the server by using SQL Server system stored procedures and functions.

To view the number of traces running:

SELECT count(*)
FROM :: fn_trace_getinfo(default)
WHERE property = 5 and value = 1

To obtain more detail about the active traces:-

SELECT *
FROM :: fn_trace_getinfo(default)

To stop a trace using the traceid:-

EXEC sp_trace_setstatus 1, @status = 0
EXEC sp_trace_setstatus 1, @status = 2

status = 0 stops the trace
status = 2 closes the trace and deletes its definition from the server

A sample output :-

traceid,property,value
1,1,2
1,2,D:\MSSQL10.MSSQLSERVER\MSSQL\Log\log_1504.trc
1,3,20
1,4,NULL
1,5,1


This means

Result Set Description
Traceid Unique identifier for the trace
Property = 1 Configured trace options
Property = 2 Trace file name
Property = 3 Max file size for the *.trc file
Property = 4 Stop time for the trace session
Property = 5 Current trace status (1 = On and 0 = Off)
Value Current value for the traceid\property combination

The Default trace
The default trace great for diagnosing performance problems, finding deadlocks, and auditing security information.The default trace does not capture all trace events, but it captures key information such as auditing events, database events, error events, full text events, object creation, object deletion and object alteration. It also captures log growth events which can be invaluable to troubleshooting disk capacity problems.

Stop the login SID mismatch

To stop the user SID's mismatch in SQL Server 2005 for databases that are continually moved and restored from one database server to another

Run a script to collect the current sid of the user after a user database has been restored

username = bennett
USE ;
GO
SELECT sid FROM sysusers WHERE name = 'bennett';
GO

Then create a server login. Use the SID from the above query output and replace the username and password with the relevant details.

USE master
CREATE LOGIN [bennett]
WITH PASSWORD = 'Ax$ef6!f', SID = 0xA2AE403E43ED084C8B3021E7E8DFD61C,
CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF;

Then create a database user for bennett, with the default schema dbo.

USE ;
CREATE USER [bennett] FOR LOGIN [bennett]
WITH DEFAULT_SCHEMA = dbo;
GO

When you restore the database again the database user continues to work with no further action required.

Error Log Management

The SQL server error log may require that the size of the log is limited if the growth rate is excessive. This can be done using the sp_cycle_errorlog system stored procedure. This will start a new error log. The script

use msdb
exec sp_cycle_errorlog

The log displays
Current log
Attempting to cycle error log. This is an informational message only; no user action is required.
New log
The error log has been reinitialized. See the previous log for older entries.

SQL Server Surface Area Configuration for Feature

Security configuration settings should only be enabled if you need to use the security feature. You should follow the principal of least privilege, which states that a system can be made more secure by granting a user or process only those privileges it requires.

• Ad Hoc Remote Queries - Disabled
• CLR Integration - Disabled. For use with .NET Assemblies on your SQL Server.
  
• DAC - Disabled. For Database Administrators connection.
• Database Mail - Enabled for SQL Server Agent alerting on jobs and sending emails from SQL Server
  
• Native XML Web Services - This would only have an option if you've configured it manually. If so, consider transitioning to a web service written in .NET deployed on IIS due to the deprecation in SQL Server 2008.
• OLE Autmation - Disabled.
• Service Broker - Disabled.
• SQL Mail - Disabled as Database Mail is the new version for SQL Mail.
  
• Web Assistant - Disabled.
• xp_cmdshell - Disabled.

Advanced option can be changed through the graphical interface or by script

-- To enable xp_cmdshell

EXEC sp_configure 'show advanced options', 1;
GO
-- To update the currently configured value for -- advanced options
RECONFIGURE;
GO
-- To disable xp_cmdshell
EXEC sp_configure 'xp_cmdshell', 0;
GO
-- To update the currently configured value for this -- feature
RECONFIGURE;
GO

Best Practice for Security

The white paper entitled
Best practices for setting up and maintaining security in SQL Server 2005
can be downloaded here

” http://download.microsoft.com/download/8/5/e/85eea4fa-b3bb-4426-97d0-7f7151b2011c/SQL2005SecBestPract.doc