Performance Tuning – Wait Statistics

January 29, 2012 1 Comment

Many times we got a call from the business team regarding the performance issue on the database server. As a first step you can check for the blocking, if the blocking is not there. We have to check for the waits, Query is internally waiting for the resources to complete its process.

By identifying the correct wait type will give you the directions to troubleshooting issue further. You can execute below query to get the 10 wait statistics.

SELECT TOP 10
        wait_type ,
        max_wait_time_ms wait_time_ms ,
        signal_wait_time_ms ,
        wait_time_ms - signal_wait_time_ms AS resource_wait_time_ms ,
        100.0 * wait_time_ms / SUM(wait_time_ms) OVER ( )
                                    AS percent_total_waits ,
        100.0 * signal_wait_time_ms / SUM(signal_wait_time_ms) OVER ( )
                                    AS percent_total_signal_waits ,
        100.0 * ( wait_time_ms - signal_wait_time_ms )
        / SUM(wait_time_ms) OVER ( ) AS percent_total_resource_waits
FROM    sys.dm_os_wait_stats
WHERE   wait_time_ms > 0
ORDER BY wait_time_ms DESC

You have to checkout for the below kind of wait statistics and troubleshoot as per the stats.

CXPACKET :Most of the time it indicates nothing more than that certain queries are executing with parallelism; CXPACKET waits in the server are not an immediate sign of problems, it may be the symptom of another problem, associated with one of the other high value wait types in the instance.

SOS_SCHEDULER_YIELD :The tasks executing in the system are yielding the scheduler, having exceeded their quantum, and are having to wait in the runnable queue for other tasks to execute. This may indicate that the server is under CPU pressure.

THREADPOOL :A task had to wait to have a worker bound to it, in order to execute.

LCK_* :These wait types indicate that blocking is occurring in the system and that sessions have had to wait to acquire a lock of a specific type, which was being held by another database session. This problem can be investigated further using, for example, the information in the sys.dm_db_index_operational_stats.

PAGEIOLATCH_*, IO_COMPLETION, WRITELOG :These waits are commonly associated with disk I/O bottlenecks, though the root cause of the problem may be, and commonly is, a poorly performing query that is consuming excessive amounts of memory in the server.

PAGELATCH_* :Non-I/O waits for latches on data pages in the buffer pool. A lot of times PAGELATCH_* waits are associated with allocation contention issues. One of the best-known allocations issues associated with PAGELATCH_* waits occurs in tempdb when the a large number of objects are being created and destroyed in tempdb and the system experiences contention on the Shared Global Allocation Map (SGAM), Global Allocation Map (GAM), and Page Free Space (PFS) pages in the tempdb database.

LATCH_* :These waits are associated with lightweight short-term synchronization objects that are used to protect access to internal caches, but not the buffer cache. These waits can indicate a range of problems, depending on the latch type. Determining the specific latch class that has the most accumulated wait time associated with it can be found by querying the sys.dm_os_latch_stats DMV.

ASYNC_NETWORK_IO :This wait is often incorrectly attributed to a network bottleneck.

Filed under SQL Server, SQL Server 2008, SQL Server 2008 R2 Tagged with , , , , , , , , ,

Happy Republic Day

January 25, 2012 Leave a Comment

Filed under SQL Server

Different ways to check the SQL Server Instance Port number

January 18, 2012 1 Comment

Problem: If there are multiple SQL instances running on the same computer, it is difficult to identify the instance port number. You can use the below solution to find the instance specific port numbers.

Solution: You can check the list of port number used by the SQL Server instances using one of the below way.

Soln 1# Using SQL Server Configuration Manager

  • Go to SQL Server Configuration Manager
  • Select Protocols for SQL2005/2008 under SQL server Network Configuration
  • Right click on TCP/IP and select Properties
  • Select the IP Addresses-tab
  • In the section IP ALL, you can see the ports

Soln 2#From Registry Values
SQL Server 2005
Type the regedit command in Run window and check the below registry values.HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.#

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\ MSSQL.#\ MSSQLServer\ SuperSocketNetLib\TCP\IPAll

SQL Server 2008
Default instance
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQLServer\SuperSocketNetLib\TCP\IPAll

Named instance
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.(InstanceName)\MSSQLServer\SuperSocketNetLib\TCP\IPAll

Soln 3# Error Log
Query the error log as below to get the port number.

EXEC xp_readerrorlog 0,1,”Server is listening on”,Null

Soln 4# Command Prompts
Execute the below command from the command prompt.

Netstat -abn

Filed under SQL Scripts, SQL Server, SQL Server 2008, SQL Server 2008 R2, SQL Server 2011 (Denali) Tagged with , , ,

Performance Tuning Series

January 6, 2012 1 Comment

Lock Pages in Memory

You can prevent the Windows operating system from paging out the buffer pool memory of the SQL Server process by locking the memory that is allocated for the buffer pool in physical memory. You lock the memory by assigning the Lock pages in memory user right to the user account that is used as the startup account of the SQL Server service.

Model Database Whenever we create a new database, it will use model as template. Configure model DB for the Auto Shrink OFF, Auto Update/Create Statistics on

Maximum Worker Threads: Based on the load increase the maximum work thread.

Address Windowing Extensions (AWE) is an API that allows a 32-bit application to manipulate physical memory beyond 4 GB memory limit. The AWE mechanism technically is not necessary on 64-bit platform. It is, however, present there. Memory pages that are allocated through the AWE mechanism are referred as locked pages on the 64-bit platform.

On both 32-bit and 64-bit platforms, memory that is allocated through the AWE mechanism cannot be paged out. This can be beneficial to the application. (This is one of the reasons for using AWE mechanism on 64-bit platform.) This also affects the amount of RAM that is available to the system and to other applications, which might have detrimental effects. For this reason, in order to use AWE, the Lock Pages in Memory privilege must be granted for the account that runs SQL Server.

 

 

Please note:

  • Turn auto-shrink off.
  • Make sure auto-update of statistics is turned on.
  • If a database is read only, set it to read only.
  • Use triggers very judiciously. They mostly operate in the background making them difficult to monitor and troubleshoot.
  • Be very careful of auto growth settings on the database. 10% auto growth will be fine when the database is 500mb. It makes a huge difference when the system is 50gb. For larger databases, change the setting to grow by a fixed amount rather than a percentage of total database size.
  • Files and FileGroups
  • Other factors that can affect the performance of your system include the way the files and file groups are laid out. You should be creating multiple files for your databases to optimize performance. A baseline for this would be to create one file for the logs, another for the data (defined by the clustered index), and another for non-clustered indexes. Additional files may be necessary to separate out BLOB data or XML data or unusually active tables, each onto its own file, and where possible, onto its own disk. This has been found to be true even on SAN systems because distributing the load takes further advantage of the architecture of the SAN.
  • Data Types
  • Define the data types that you need, not what you think you might need someday. A phone number is a string, not a number. Define the length of field that you need and enforce that length.

.

Filed under SQL Server Tagged with , , , , , , , , , ,

Happy New Year 2012

December 31, 2011 1 Comment

Dear Readers,

Wish you all very happy and prosperous New Year 2012.

Thanks,
Jugal Shah

Filed under SQL Server

Basics of Replication Troubleshooting

December 30, 2011 1 Comment

There are many scenarios where you have been alerted for the replication failure and you have to troubleshoot the issue. In this article I will guide you what should be your approach to get the detailed error message and transaction details in replication.

First check the replication monitor and click on the failed publisher. Next step is double click on the failed subscriber from All Subscriptions list.

Now next step is click on the error and check its description.
Error :
Command attempted:
if @@trancount > 0 rollback tran
(Transaction sequence number: 0x0000044100002D93000100000000, Command ID: 1)

From the above error message we have to identify which command is failed to execute on the subscriber.

To get the exact command, find out the distributer server and distribution database for the failed publisher.

Once you get the distribution database server, execute the below query against the distribution DB.

use distribution
go
SELECT * FROM msrepl_commands
WHERE xact_seqno = 0x0000044100002D93000100000000
AND command_id = 1

Once you execute the above query against the distribution database, you will get the more information about the error, for example Publisher database ID, Article ID and much more…

We have to use the above details, to get the exact command using either SP_BROWSEREPLCMDS (If CLR is enabled) or you can cast the command column in msrepl_commands table.

We will check both the alternatives.

Using SP_BROWSEREPLCMDS
Please note CLR must be enabled for to use this procedure.

EXEC SP_BROWSEREPLCMDS
@xact_seqno_start = '0x0000044100002D930001',
@xact_seqno_end = '0x0000044100002D930001',
@publisher_database_id = 1033,
@article_id = 12,
@command_id= 1

By casting command column in msrepl_commands table

Please note if you want to see the better output use the Result to Text as output in SSMS (CTRL + T)

SELECT CAST(SUBSTRING(command, 7, 8000) AS NVARCHAR(MAX))
FROM msrepl_commands
WHERE xact_seqno = 0x0000044100002D930001
AND command_id = 1

Now you got the exact SQL Command. As a next step check the objects from both the publisher and the subscriber to see the violation of the keys or do the data comparisons etc.

Filed under SQL Server, SQL Server 2008, SQL Server 2008 R2 Tagged with , , , ,

Script to Monitor the Running Job Status

December 27, 2011 1 Comment

Think of environment where multiple jobs are running at the same time and you want to monitor the current status of each job. One option is to use the job activity monitor.

 

And another way is you can use the below system stored procedures by running it against the MSDB database.

 

exec msdb..sp_get_composite_job_info
@execution_status=1

exec msdb.dbo.sp_help_job
@execution_status = 1

 

You can check the job execution status parameter values as below.

0 = Not idle or suspended

1 = Executing

2 = Waiting For Thread

3 = Between Retries

4 = Idle

5 = Suspended

6 = Waiting For Step To Finish

7 = Performing Completion Actions

 

Filed under SQL Server Tagged with , , , ,

Different ways to monitor Log Shipping for SQL Server databases

December 21, 2011 1 Comment

Problem
Log shipping has been an option for creating a fail over server for SQL Server for quite some time. In this tip, we look at different ways that you can monitor the status of your log shipped databases.

Solution

http://www.mssqltips.com/sqlservertip/2553/different-ways-to-monitor-log-shipping-for-sql-server-databases/

Filed under SQL Server 2008, SQL Server 2008 R2 Tagged with , , ,

Steps to Attach a SQL Server database without transaction log file

December 20, 2011 Leave a Comment

Problem: There could be situation where you missed the database transaction log file(.LDF) and you have only data file (.MDF). You can attach the database using below solution.

Solution: In the below script I have created the database,dropped its log file and created the database with the .mdf file.

--created database with .mdf and .ldf file
CREATE DATABASE [singleFileDemo] ON  PRIMARY 
( NAME = N'singleFileDemo', FILENAME = N'L:\singleFileDemo.mdf' , SIZE = 2048KB , FILEGROWTH = 10240KB )
 LOG ON 
( NAME = N'singleFileDemo_log', FILENAME = N'F:\singleFileDemo_log.ldf' , SIZE = 1024KB , FILEGROWTH = 5120KB )
GO

--inserting data into database
use singleFileDemo
create table tb1 (name varchar(10))

--inserting records
insert into tb1 values('Jugal')
go 10;

--deleting the log file
--detaching the database file
USE [master]
GO
EXEC master.dbo.sp_detach_db @dbname = N'singleFileDemo'
GO

-- now next step is delete the file manually or you can do it from command prompt
EXEC xp_cmdshell 'del F:\singleFileDemo_log.ldf'

-- script to attach the database 
USE [master]
GO
CREATE DATABASE [singleFileDemo] ON 
( FILENAME = N'L:\singleFileDemo.mdf' )
FOR ATTACH
GO

When you will execute the CREATE DATABASE FOR Attach script you will get the below warning message.

File activation failure. The physical file name "F:\singleFileDemo_log.ldf" may be incorrect.
New log file 'F:\singleFileDemo_log.LDF' was created.

Once the database is ready execute the DBCC CHECKDB for any error.

Filed under Database, SQL Server 2008, SQL Server 2008 R2, SQL Server 2011 (Denali) Tagged with , , , , , , , ,

PathPing Command

December 13, 2011 1 Comment

Pathping is a Windows based command-line tool used to provide information about the path data takes to its destination, network latency and network loss at intermediate hops between a source and destination.

Example

Filed under Networking Tagged with , , ,

Extended Stored Procedure xp_msver

December 8, 2011 1 Comment

xp_msver returns information about the SQL Server version, actual build number of the server and information about the server environment.

You can also pass the parameter to get the specific information.

Filed under SQL Server, SQL Server 2008, SQL Server 2008 R2, SQL Server 2011 (Denali) Tagged with , , ,

Script to Enable/Disable Database for Replication

December 2, 2011 Leave a Comment

You can enable the database for replication using below script.

use master
exec sp_replicationdboption @dbname = 'sqldbpool',
@optname = 'publish',
@value = 'true'
go

If you have restore the database on test environment and you are getting the error that “Database is part of Replication”, you can clear/disable it by executing below query.

use master
exec sp_replicationdboption @dbname = 'sqldbpool',
@optname = 'publish',
@value = 'false'
go

Filed under Database, SQL Server, SQL Server 2008, SQL Server 2008 R2, SQL Server 2011 (Denali) Tagged with , , , , ,

← Older posts

Follow

Get every new post delivered to your Inbox.

Join 133 other followers