Simon's SQL

SQL,DBA,tuning,Trouble Shooting,Performance

Archive for August, 2012

server information include port #

Posted by Simon Cho on 08/20/2012

http://smartypeeps.blogspot.com/2006/11/t-sql-script-to-find-nw-port-of-sql.html

declare @Server as varchar(128) 
declare @KeyToInterogate as varchar(200)
declare @Version as varchar (512) 
declare @PortNumber as varchar(8) 

set @Server = @@ServerName 
set @Version = left(@@Version, 38) 
set @KeyToInterogate = 'SOFTWARE\MICROSOFT\MSSQLSERVER\MSSQLSERVER\SUPERSOCKETNETLIB\TCP'

if charindex('\',@@ServerName) > 0
begin
set @KeyToInterogate = 'SOFTWARE\Microsoft\Microsoft SQL Server\'
set @KeyToInterogate = @KeyToInterogate + substring(@@ServerName,charindex('\',@@ServerName) + 1,len(@@ServerName) - charindex('\',@@ServerName)) 
set @KeyToInterogate = @KeyToInterogate + '\MSSQLServer\SuperSocketNetLib\Tcp'
end

exec xp_regread 
@rootkey = 'HKEY_LOCAL_MACHINE', 
@key = @KeyToInterogate, 
@value_name = 'TcpPort', 
@value = @PortNumber output 

exec master..xp_regread
                     'HKEY_LOCAL_MACHINE'
                    , 'SOFTWARE\Microsoft\Windows NT\CurrentVersion'
                    , 'ProductName'

exec master..xp_regread
                     'HKEY_LOCAL_MACHINE'
                    , 'SOFTWARE\Microsoft\Windows NT\CurrentVersion'
                    , 'CSDVersion'
                    
SELECT @@servername AS hostname
, SERVERPROPERTY('Edition') AS Edition
, ISNULL(SERVERPROPERTY('InstanceName'),'') AS InstanceName
, SERVERPROPERTY('MachineName') AS MachineName
, SERVERPROPERTY('ProductVersion') AS ProductVersion
, SERVERPROPERTY('ProductLevel') as ProductLevel 
, @@VERSION
, CASE 
WHEN CONVERT(VARCHAR(255),SERVERPROPERTY('ProductVersion')) LIKE '8.0%' THEN '2000'
WHEN CONVERT(VARCHAR(255),SERVERPROPERTY('ProductVersion')) LIKE '9.0%' THEN '2005'
WHEN CONVERT(VARCHAR(255),SERVERPROPERTY('ProductVersion')) LIKE '10.0%' THEN '2008'
WHEN CONVERT(VARCHAR(255),SERVERPROPERTY('ProductVersion')) LIKE '10.5%' THEN '2008 R2'
END
, cast(@PortNumber as varchar)
, (select top 1 local_net_address from sys.dm_exec_connections
where local_net_address is not null) as IP_address

SELECT
SERVERPROPERTY('ProductVersion') AS ProductVersion,
SERVERPROPERTY('ProductLevel') AS ProductLevel,
SERVERPROPERTY('Edition') AS Edition,
SERVERPROPERTY('EngineEdition') AS EngineEdition;

Posted in Common | Tagged: , , | Leave a Comment »

Backup Size check including compression backup

Posted by Simon Cho on 08/20/2012

use msdb

--select top 10 * from backupfile
--select top 10 * from backupmediaset
--select top 10 * from backupmediafamily

declare @sql nvarchar(4000)
if exists(select OBJECT_NAME(id) from syscolumns where name='compressed_backup_size') begin
	set @sql = '
	select top 1 @@servername as servername, case when sum(compressed_backup_size)convert(varchar(8),getdate()-30,112)
	group by convert(varchar(8), backup_start_date, 112)
	order by sum(backup_size) desc
	'
end else begin
	set @sql = '
	select top 1 @@servername as servername, ''N'' compressed_backup
	, convert(numeric(10,2),sum(backup_size/1024/1024/1024.0)) as [backup_size(GB)] from msdb.dbo.backupset with(nolock)
	where backup_start_date>convert(varchar(8),getdate()-30,112)
	group by convert(varchar(8), backup_start_date, 112)
	order by sum(backup_size) desc
	'
end

exec(@sql)

Posted in Common | Tagged: , , | Leave a Comment »

Remote Replication(Long distance Replication)

Posted by Simon Cho on 08/14/2012

http://msdn.microsoft.com/en-us/library/aa237436%28v=sql.80%29.aspx

http://msdn.microsoft.com/en-us/library/ms151762.aspx

http://www.google.com/url?sa=t&rct=j&q=&esrc=s&source=web&cd=5&ved=0CGUQFjAE&url=http%3A%2F%2Fdownload.microsoft.com%2Fdownload%2Fd%2F9%2F4%2Fd948f981-926e-40fa-a026-5bfcf076d9b9%2FReplicationAndDBM.docx&ei=_6wqUJeCEdH1iQKl_YHADg&usg=AFQjCNGQ4F4LY3sb4jwY4iIvqUqCmT8NqA&sig2=NUGwh6APmlhMy5N89jW9HA&cad=rja

1. Most importance is Pull subscription.

Consider Pull or Anonymous Subscriptions

The Distribution or Merge Agent runs on the Distributor for push subscriptions, and on Subscribers for pull or anonymous subscriptions. Using pull or anonymous subscriptions can increase performance by moving Distribution or Merge Agent processing from the Distributor to Subscribers.

You can also offload agent processing by using Remote Agent Activation. Agent processing can be moved to the Subscriber for push subscriptions and to the Distributor for pull subscriptions. Administration of the agent still takes place at the Distributor for push subscriptions and at the Subscriber for pull subscriptions. For more information, see Remote Agent Activation.

Anonymous subscriptions, which are especially useful for Internet applications, do not require that information about the Subscriber be stored in the distribution database at the Distributor for transactional replication and reduces the storage of information about the Subscriber in the publishing database for merge replication. This reduces the resource demands on the Publisher and Distributor because they do not have to maintain information about anonymous Subscribers.

Anonymous subscriptions are a special category of pull subscriptions. In regular pull subscriptions, the Distribution or Merge Agent runs at the Subscriber (thereby reducing the resource demands on the Distributor), but still stores information at the Publisher. When a publication supports anonymous subscriptions, the publication is configured to always have a snapshot ready for new Subscribers.

For transactional replication, this means that every time the Snapshot Agent runs, a new snapshot will be generated. Typically, a snapshot is not generated if there are no new Subscribers waiting for a snapshot or no Subscriber needs to be reinitialized at the time the Snapshot Agent is run. So while anonymous Subscribers can reduce the resource demands at the Distributor, the tradeoff is that a snapshot is generated more often. With merge replication, a new snapshot is always generated when the Snapshot Agent runs regardless of the type of subscriptions supported by the publication.

2. Change default distribution agent setup.

http://msdn.microsoft.com/en-us/library/ms147328.aspx

 

3. Performance test

http://msdn.microsoft.com/en-us/library/dd263442.aspx

Performance Indicators Test Scenarios SQL Server 2005 on Windows Server 2003 (A) SQL Server 2008 on Windows Server 2008 (B) Performance Gains or Losses
 
[(A-B)/B]*100
CPU Utilization (%) All

15%

15%

0%
Memory All

99%

99%

0%
Push Replication 1-GB 226.12 (minutes) 110.42 (minutes)

104.78%
 
1,000,000 1k character records
Pull Replication 1-GB 174.87 (minutes) 12.5 (minutes)

1298.96%
 
1,000,000 1k character records
Linked Server 10-MB 107.6 (minutes) 113.6 (minutes) –5.28%
 
10,000 1k character records
Push Replication 112-MB 247.07 (minutes) 59.13 (minutes)

317.84%
 
100,000 varbinary (max) records
Pull Replication Records 112-MB 223.18 (minutes) 1.95 (minutes)

11345.13%
 
100,000 varbinary (max) records
Snapshot Replication 11.3-GB 10,100,000 1k records Not tested 22.75 (minutes) Comparison not available

Posted in Common | Tagged: , , , , | Leave a Comment »

Replication in Non-Trusted Domains

Posted by Simon Cho on 08/14/2012

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

exec sp_addpublication_snapshot
, @publisher_security_mode = 0
, @publisher_login = N’sql_Replication_user’
, @publisher_password = N’sql_Replication_user_password’


exec ReplicationDB.sys.sp_addlogreader_agent 
@job_login = null
, @job_password = null
, @publisher_security_mode = 0
, @publisher_login = N'sql_Replication_user'
, @publisher_password = N'sql_Replication_user_password'
GO

exec sp_addpublication_snapshot @publication = N'TestPublication'
, @frequency_type = 1
, @frequency_interval = 0
, @frequency_relative_interval = 0
, @frequency_recurrence_factor = 0
, @frequency_subday = 0
, @frequency_subday_interval = 0
, @active_start_time_of_day = 0
, @active_end_time_of_day = 235959
, @active_start_date = 0
, @active_end_date = 0
, @job_login = null
, @job_password = null
, @publisher_security_mode = 0
, @publisher_login = N'sql_Replication_user'
, @publisher_password = N'sql_Replication_user_password'

Posted in Common | Tagged: , | Leave a Comment »

Quest LiteSpeed Compressed Backup T-SQL

Posted by Simon Cho on 08/14/2012

Click to access NetVault%20LiteSpeed%20for%20SQL%20Server%20-%20User%20Guide.pdf

EXEC master.dbo.xp_backup_database
@database = 'database_name'
(, @filename = 'backup_file_name') [,...n]
[, @nowrite = 0 | 1 ]
[, @desc = 'backup_description']
[, @backupname = 'backupset_name']
[, @threads = 1..32]
[, @init = 0 | 1 ]
[, @LSECompatible = 1]
[, @mirror = 'mirror_backup_file_name'] [,...n]
[, @doubleclick = 0 | 1 ]
[,( @encryptionkey = 'encryption_key'| @jobp = 'encrypted_key' ) ]
[, @cryptlevel = 'encryption_level']
[, @read_write_filegroups = 0 | 1 ]
[, @file = 'logical_file_name'] [,...n]
[, @filegroup = 'logical_filegroup_name'] [,...n]
[, @priority = -1 | 0 | 1 | 2 ]
[, @with = 'additional_with_parameters'] [,...n]
[, ( @retaindays = 0..99999 | @expiration = 'date' ) ]
[, @logging = 0 | 1 | 2 ]
[, @olrmap = 0 | 1 ]
[, @affinity = 0..2147483648]
[, @throttle = 1..100]
[, @ioflag = 'DISK_RETRY_COUNT=n']
[, @ioflag = 'DISK_RETRY_WAIT=n']
[, @comment = 'comment']
[, @buffercount = 'buffer_count']
[, @maxtransfersize = 'maximum_transfer_size']
[, @adaptivecompression = 'speed' | 'size' ]
[, @compressionlevel = 'compresssion_level']
[, @attachedfile = 'pathname']
[, @tsmclientnode = 'TSM_client_node']
[, @tsmclientownerpwd = 'TSM_client_owner_password']
[, @tsmobject = 'TSM_object']
[, @tsmconfigfile = 'TSM_configuration_file']
[, @tsmmanagementclass = 'TSM_management_class']
[, @tsmarchive = 0 |1 ]
[, @verify = 0 | 1 ]
[, @returndetails = 0 |1 ]

EXEC master.dbo.xp_backup_log
@database = 'database_name'
(, @filename = 'backup_file_name') [,...n]
[, @nowrite = 0 | 1 ]
[, @desc = 'backup_description']
[, @backupname = 'backupset_name']
[, @threads = 1..32]
[, @init = 0 | 1 ]
[, @LSECompatible = 1]
[, @mirror = 'mirror_backup_file_name'] [,...n]
[, @doubleclick = 0 | 1 ]
[, ( @encryptionkey = 'encryption_key' | @jobp = 'encrypted_key' ) ]
[, @cryptlevel = 'encryption_level']
[, @file = 'logical_file_name'] [,...n]
[, @filegroup = 'logical_filegroup_name'] [,...n]
[, @priority = -1 | 0 | 1 | 2 ]
[, @with = 'additional_with_parameters'] [,...n]
[, ( @retaindays = 0..99999 | @expiration = 'date' ) ]
[, @logging = 0 | 1 | 2 ]
[, @ioflag = 'DISK_RETRY_COUNT=n']
[, @ioflag = 'DISK_RETRY_WAIT=n']
[, @affinity = 0..2147483648]
[, @throttle = 1..100]
[, @comment = 'comment']
[, @buffercount = 'buffer_count']
[, @maxtransfersize = 'maximum_transfer_size']
[, @adaptivecompression = 'size' | 'speed' ]
[, @compressionlevel = 'compresssion_level']
[, @attachedfile = 'pathname']
[, @tsmclientnode = 'TSM_client_node']
[, @tsmclientownerpwd = 'TSM_client_owner_password']
[, @tsmobject = 'TSM_object']
[, @tsmconfigfile = 'TSM_configuration_file']
[, @tsmmanagementclass = 'TSM_management_class']
[, @tsmarchive = 0 |1 ]
[, @verify = 0 | 1 ]
[, @returndetails = 0 | 1 ]

Posted in Common | Tagged: , , | Leave a Comment »

Disk IO Response time In SQL

Posted by Simon Cho on 08/14/2012

https://sqlserverperformance.wordpress.com/tag/dmv-queries/

select convert(varchar(19), getdate(), 121) as date_time
	 , db_name(df.database_id) as DatabaseName
	 , f.name as FileLogicalName
	 , f.filename
	 , case when num_of_reads+num_of_writes>0 then io_stall/(num_of_reads+num_of_writes) else 0 end as 'ms/io'
	 , case when num_of_reads>0 then io_stall_read_ms/num_of_reads else 0 end  as 'ms/Read io'
	 , case when num_of_writes>0 then io_stall_write_ms/num_of_writes else 0 end  as 'ms/Write io'
  from sys.dm_io_virtual_file_stats(null,null) as df
  join sys.sysaltfiles as f
    on f.dbid= df.database_id
        and f.fileid = df.file_id
 --where df.database_id in(DB_ID('msdb'), DB_ID('TempDB'))
 where case when num_of_reads+num_of_writes>0 then io_stall/(num_of_reads+num_of_writes) else 0 end >50
	or case when num_of_reads>0 then io_stall_read_ms/num_of_reads else 0 end >50
	or case when num_of_writes>0 then io_stall_write_ms/num_of_writes else 0 end >50
 order by 5 desc

Posted in Common | Tagged: , , | Leave a Comment »