Friday, October 28, 2011

SQL Pass 2011 - Code Names

SQL Server Code Names

Un poco de historia acerca de los nombres en codigo de las versiones de SQL
Desde SQL 4.21 hasta SQL 2012 "Denali"

Some history about SQL code names
From SQL 4.21 to SQL 2012 "Denali"

Wednesday, October 19, 2011

SQL Denali es SQL Server 2012

Voy a ir subiendo fotos y links de SQL PASS Summit 2011 en Seattle, Wa

Lo mas importante fue la introducción de SQL Server 2012

Esteré subiendo comentario y fotos del los tres Key Notes. Haciendo especial referencia a el caso de estudio de MSC utilizando AlwaysOn

Monday, October 10, 2011

All you need to know about your backups and Database Mirror Status

This script shows all the backup status information as well as the current database mirroring status (if enabled)


- Database Name
- Last Full backup
- Last Transaction Log backup
- Last Restore
- Recovery Model and Compatibility Level
- Database Mirror
   - Curent Role
   - Status
- Transaction Log usage (Percentage)





-- MAIN SCRIPT

--// SQL Version
USE
msdb
GO
-- Last Full Backup Info
SELECT
D.name as Database_Name
--,D.database_id as dbid
,B.last_backup_finish_date as [Last Full Backup Finish Date]
,TR.last_TRLog_backup_finish_date as [Last T.Log Backup Finish Date] --Transaction Log
,R.last_restore_date as [Last Restore Date]
,D.recovery_model_desc+' ('+cast(D.compatibility_level as varchar(3))+')' as [Recovery Model / Compatibility Level]
,DM.mirroring_role_desc+'('+DM.mirroring_state_desc+')' as [DBMirror Info]
, LOG.cntr_value as [% T.Log Used]
FROM sys.databases D
LEFT JOIN (
SELECT
BS.database_name
--,BS.backup_start_date
,max(BS.backup_finish_date) as last_backup_finish_date
--,BS.backup_size
FROM
msdb.dbo.backupset BS (NOLOCK)
WHERE
BS.backup_start_date >= CAST(CONVERT(varchar(10),dateadd(mm,-3,getdate()),120) AS datetime)
AND BS.type='D'
GROUP BY
BS.database_name
--ORDER BY
-- BS.backup_start_date ASC
) B on D.name=B.database_name
LEFT JOIN (
SELECT
BS.database_name
,max(BS.backup_finish_date) as last_TRLog_backup_finish_date
FROM
msdb.dbo.backupset BS (NOLOCK)
INNER JOIN
msdb.dbo.backupmediafamily MF(NOLOCK) ON BS.media_set_id = MF.media_set_id
WHERE
BS.backup_start_date >= CAST(CONVERT(varchar(10),dateadd(mm,-1,getdate()),120) AS datetime)
AND BS.type='L'
GROUP BY
BS.database_name
) TR on D.name=TR.database_name

LEFT JOIN (
SELECT
rh.destination_database_name,
max(rh.restore_date) as last_restore_date
FROM
msdb.dbo.restorehistory rh (NOLOCK)
INNER JOIN msdb.dbo.backupset BS (NOLOCK) ON rh.backup_set_id=BS.backup_set_id
WHERE
BS.type= 'D' AND
RH.restore_date >=CAST(CONVERT(varchar(10),dateadd(mm,-3,getdate()),120) AS datetime)
GROUP BY
rh.destination_database_name
-- ORDER BY

-- max(rh.restore_date) desc
) R on D.name=R.destination_database_name
LEFT JOIN
msdb.sys.database_mirroring dm (nolock) on d.database_id=dm.database_id
LEFT JOIN (
select object_name,instance_name,cntr_value from sys.dm_os_performance_counters
where object_name = 'SQLServer:Databases'
--and instance_name = 'Interlink_Main90'
and counter_name in ('Percent Log Used')
--order by cntr_value desc
) LOG on d.name=LOG.instance_name



order by
--D.name asc,
R.last_restore_date desc
GO




Tuesday, October 4, 2011

Useful information about the SQL instance

This script gets useful information about the SQL instance, like:
- Name
- SQL Version
- Time Zone
- Max Degree of Parallelism (Configured , Running & Optimal)
- Number of Cores
- Memory
- Cluster ot Standalone
- SQL Server service start time



-- MAIN SCRIPT
--// SQL Version
SET NOCOUNT ON
DECLARE
@date datetime,
@start int,
@ver varchar(13),
@config_value varchar(20),
@run_value varchar(20),
@sqlstart datetime,
@Mem int,
@optimal_maxdop int,
@cpu_count int,
@hyperthread_ratio int
DECLARE @CM Table
(
[Index] int,
Name nvarchar(1000) NOT NULL,
Internal_Value int,
Character_Value nvarchar(1000)
)

SELECT @date = getdate()
SELECT @start = CHARINDEX ( 'Microsoft SQL Server 2005',@@version)
if @start = 1
SELECT @ver = rtrim(substring(@@version,29,13))
if @start = 0
SELECT @ver = rtrim(substring(@@version,30,9))
SELECT @start = CHARINDEX ( 'Microsoft SQL Server 2008',@@version)
if @start = 1
SELECT @ver = rtrim(substring(@@version,35,12))
SELECT @start = CHARINDEX ( 'Microsoft SQL Server 2008 R2',@@version)
if @start = 1
SELECT @ver = rtrim(substring(@@version,38,12))



-- MAX DEGREE OF PARALLELISM
CREATE TABLE #MDP
(
name nvarchar(1000),
minimun int NOT NULL,
maximun int NOT NULL,
config_value int NOT NULL,
run_value int NOT NULL
)
Insert into #MDP exec sp_configure 'max degree of parallelism'
SELECT @config_value=rtrim(convert(varchar(8),config_value)) ,@run_value=rtrim(convert(varchar(8),run_value)) from #MDP
DROP TABLE #MDP
select @sqlstart = create_date from sys.databases where name = 'Tempdb'

-- CPU and Memory
Insert into @CM exec xp_msver
--//REPORT
select @Mem = Internal_Value from @CM Where Name = 'PhysicalMemory'
select
@cpu_count=cpu_count,
@hyperthread_ratio=hyperthread_ratio,@optimal_maxdop=case
when cpu_count / hyperthread_ratio > 8 then 4
else CEILING((cpu_count / hyperthread_ratio)*.5)
end
from sys.dm_os_sys_info;





--//REPORT
SELECT
@@servername as Servername
,getdate() as [Current Date/Time]
,DATEDIFF(HH,GETUTCDATE(),GETDATE()) as [Time Zone]
,@ver as SQL_version
,@config_value as [MDP cfg]
,@run_value as [MDP run]
,@hyperthread_ratio as [Hyperthread Ratio]
,@optimal_maxdop as [Optimal MDP]
,@sqlstart as sql_srv_start
,serverproperty('Edition') as sql_edition
,case serverproperty('IsClustered') when 0 THEN 'NO' when 1 THEN 'YES' end as IsCluster
,@cpu_count as [CPU Count]
,@Mem as [Memory (MB)]

Monday, October 3, 2011

Extended Events - SQL Errors

This script creates an Extended Events Session (XEvent) to capture SQL errors. Very useful to detect the object and the statement that is raising the error. 


The first part creates the session and the second one is to retrieve the errors that are been raising.




IMPORTANT: This is a template so make sure you enter the proper values (Path and session name)


USE master
GO

-- Create XEvents Session
exec master.dbo.xp_cmdshell 'Dir "< XEvents_Folder,varchar(1000),C:\XEvents_output\xEvent_Target >*.*"'

CREATE EVENT SESSION < XEvents_Session_Name,varchar(1000),sql_text_and_errors >
ON SERVER

ADD EVENT sqlserver.error_reported
(
-- ACTION( sqlserver.tsql_stack )
ACTION (sqlserver.tsql_stack, sqlserver.sql_text, sqlserver.database_id, sqlserver.username, sqlserver.client_app_name, sqlserver.client_hostname)
WHERE
((
[error] <>  2528 -- DBCC execution completed...
AND [error] <>  3014 -- BACKUP LOG successfully processed ...
AND [error] <>  4035 -- Processed 0 pages for database ...
AND [error] <>  5701 -- Changed database context to ,,,
AND [error] <>  5703 -- Changed language setting to ...
AND [error] <>  18265 -- Log was backed up. ...
AND [error] <>  14205 -- (unknown)

AND [error] <> 14213 -- Core Job Details:
AND [error] <> 14214 -- Job Steps:
AND [error] <> 14215 -- Job Schedules:
AND [error] <> 14216 -- Job Target Servers:
AND [error] <>  14549 -- (Description not requested.)
AND [error] <>  14558 -- (encrypted command)
AND [error] <>  14559 -- (append output file)
AND [error] <>  14560 -- (include results in history)
AND [error] <>  14561 -- (normal)
AND [error] <>  14562 -- (quit with success)
AND [error] <>  14563 -- (quit with failure)
AND [error] <>  14564 -- (goto next step)
AND [error] <>  14565 -- (goto step)
AND [error] <>  14566 -- (idle)
AND [error] <>  14567 -- (below normal)
AND [error] <>  14568 -- (above normal)
AND [error] <>  14569 -- (time critical)
AND [error] <>  14570 -- (Job outcome)

AND [error] <>  14635 -- Mail queued.
AND [error] <> 14638 -- Activation successful.
AND [error] <= 50000 -- Exclude User Errors
)))

----------------------------------------------------------------------------------


-- Target File Mode
-- ****** Make sure you have enough disk space - Also you must monitor the disk while the session is running ******
ADD TARGET package0.asynchronous_file_target
(SET filename='< XEvents_Folder,varchar(1000),C:\XEvents_output\xEvent_Target >'
,max_file_size=4000
)
----------------------------------------------------------------------------------
WITH
(
MAX_MEMORY = 4096KB,
EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,
MAX_DISPATCH_LATENCY = 1 SECONDS,
MEMORY_PARTITION_MODE = NONE,
TRACK_CAUSALITY = ON, -- Very important to get the event in order later on
STARTUP_STATE = ON -- Note that we start on
);

----------------------------------------------------------------------------------------------

-- STEP: Start the session & see which sessions are currently running

ALTER EVENT SESSION < XEvents_Session_Name,varchar(1000),sql_text_and_errors >
ON SERVER
STATE = START
GO

----------------------------------------------------------------------------------------------
-- STEP: Wait for events to be recoredd

-- Ring Buffer Mode
SELECT
len( target_data ) as [Len Buffer]
FROM sys.dm_xe_session_targets st (nolock)
JOIN sys.dm_xe_sessions s (nolock) ON
s.address = st.event_session_address
WHERE
s.name = ''
-- Target File Mode
exec master.dbo.xp_cmdshell 'Dir "< XEvents_Folder,varchar(1000),C:\XEvents_output\xEvent_Target >*.*"'
GO
----------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------------

--*********************************************************************************************

-----------------------------------------------------------------------------------------------

-- Retrieve XEvents output

SET NOCOUNT ON
DECLARE @outputfile varchar(500)='< XEvents_Folder,varchar(1000),C:\XEvents_output\xEvent_Target >' -- !!!! Edit your custom path here

---------------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------------

--  Target File Mode

-- STEP: Get the Events captured - Note that the session can be still running & this can be repeated

IF (object_id( 'tempdb..#EventXML' ) IS NOT NULL) DROP TABLE #EventXML ;
DECLARE
@path NVARCHAR(260) = @outputfile+'*',
@mdpath NVARCHAR(260) = @outputfile+'*.xem',
@initial_file_name NVARCHAR(260) = NULL,
@initial_offset BIGINT = NULL

Select
Identity(int,1,1) as ID
,*
,cast
(
Replace(
E.event_data, char(3), '?'
) as xml
) as X
into #EventXML
FROM
master.sys.fn_xe_file_target_read_file (@path, @mdpath, @initial_file_name, @initial_offset) E

----------------------------------------------------------------------------------------------
-- STEP: Shred the XML for the above event types

IF (object_id( 'tempdb..#EventDetail' ) IS NOT NULL) DROP TABLE #EventDetail ;
--Shred the XML
SELECT
node.value('./@timestamp', 'datetime') AS event_time,
node.value('./@name', 'varchar(4000)') AS event_name
,CASE node.value('./@name', 'varchar(4000)')
WHEN 'error_reported' THEN node.value('(./data)[5]', 'varchar(4000)')
ELSE NULL
END AS [Message]
,CASE node.value('./@name', 'varchar(4000)')
WHEN 'error_reported' THEN node.value('(./data)[1]', 'int')
ELSE NULL
END AS Error_Value
,CASE node.value('./@name', 'varchar(4000)')
WHEN 'error_reported' THEN node.value('(./action)[7]', 'varchar(50)')
WHEN 'sp_statement_completed' THEN node.value('(./action)[1]', 'varchar(50)')
ELSE node.value('(./action)[2]', 'varchar(50)')
END AS activity_guid
,cast(null as int) as activity_sequence
,cast
(
CASE
WHEN node.value('./@name', 'varchar(4000)') IN ('sp_statement_starting', 'error_reported') THEN node.value('(./action)[1]', 'varchar(4000)')
ELSE NULL
END
as xml
) AS TSql_stack
,CASE node.value('./@name', 'varchar(4000)')
WHEN 'error_reported' THEN node.value('(./action)[2]', 'varchar(4000)')
ELSE NULL
END AS SQL_Text
,CASE node.value('./@name', 'varchar(4000)')
WHEN 'error_reported' THEN node.value('(./action)[3]', 'int')
ELSE NULL
END AS [DBID]
,cast(null as int) as ObjectID
,CASE node.value('./@name', 'varchar(4000)')
WHEN 'error_reported' THEN node.value('(./action)[4]', 'varchar(256)')
ELSE NULL
END AS [UserName]
,CASE node.value('./@name', 'varchar(4000)')
WHEN 'error_reported' THEN node.value('(./action)[5]', 'varchar(256)')
ELSE NULL
END AS [AppName]
,CASE node.value('./@name', 'varchar(4000)')
WHEN 'error_reported' THEN node.value('(./action)[6]', 'varchar(256)')
ELSE NULL
END AS [HostName]
,cast(null as varbinary(1000) ) AS handle
,cast( null as int) as offsetstart
,cast( null as int) as offsetend
,cast(null as varchar(4000) ) as Statement_Text
,cast( null as sysname) as [DatabaseName]
,cast(null as sysname) as [ObjectName]
,#EventXML.*
INTO #EventDetail
FROM #EventXML
CROSS APPLY #EventXML.x.nodes('//event') n (node)

-- Select count(*) as Events FROM #EventDetail

-- SELECT * FROM #EventDetail

----------------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------------

-- STEP: Separate Activity GUID from Sequence number - for sorting later on (should be combined w above step)

Update D Set
activity_sequence = CONVERT(int, RIGHT(activity_guid, LEN(activity_guid) - 37))
,activity_guid = CONVERT(uniqueidentifier, LEFT(activity_guid, 36))
FROM #EventDetail D

-- SELECT * FROM #EventDetail

----------------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------------

-- STEP: Extract handles & Offsets (should be combined w above step)

--Get the SQL handles

Update D Set
Handle = CONVERT(varbinary(1000), frame.node.value('@handle', 'varchar(1000)'), 1)
,offsetstart = frame.node.value('@offsetStart', 'int')
,offsetend = frame.node.value('@offsetEnd', 'int')
FROM #EventDetail D
OUTER APPLY D.tsql_stack.nodes('(/frame)[1]') frame (node)
-- SELECT * FROM #EventDetail

----------------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------------

-- STEP: For each handle, grab the SQL text (should be combined w single table above)

Update D Set
statement_text = Left( SUBSTRING(t.text, (IsNull(offsetstart,0)/2) + 1, ((case when IsNull(offsetend,0) > 0 then offsetend else 2*IsNull(len(t.text),0) end - IsNull(offsetstart,0))/2) + 1) , 4000 )
,[DatabaseName] = DB.Name
,objectid = T.objectid
FROM
#EventDetail D
cross APPLY sys.dm_exec_sql_text(D.handle) t
inner join master.sys.sysdatabases db (nolock) on db.dbid = D.dbid

-- select count(*) as Results from #EventDetail

----------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------------------

-- Dereference ObjectName

set nocount on
Declare @Tbl table ( Name sysname )
Declare @Stmt varchar(max)
Declare @DBName sysname
Declare @ObjectName sysname
Declare @ObjectId int
DECLARE curObj CURSOR
FOR Select Distinct [DatabaseName], objectid from #EventDetail where ObjectName is null and [DatabaseName] is not null
OPEN curObj
WHILE (1=1)
BEGIN

FETCH NEXT FROM curObj INTO @DBName, @ObjectId

      IF (@@fetch_status <> 0)
      break

      Set @Stmt = 'select Name from ' + @DBName + '.sys.sysobjects (nolock) where id = ' + convert( varchar(10), @ObjectId)
      Insert into @Tbl

      exec ( @Stmt )

      Set @ObjectName = null
      Select @ObjectName = Name from @Tbl
      Delete from @Tbl

      Update #EventDetail Set ObjectName = @ObjectName where [DatabaseName] = @DBName and objectid = @ObjectId

      END

CLOSE curObj

DEALLOCATE curObj

set nocount off

-- Show Details

Select
getdate() as now
,@@servername as Server_Name
,count(*) as [# of Errors]
,[message]
,[DatabaseName]
,[ObjectName]
,min([statement_text]) as Ex1_statement_text
,max([statement_text]) as Ex2_statement_text
,min([SQL_Text]) as Ex1_SQL_Text
,max([SQL_Text]) as Ex2_SQL_Text
--,[event_name]
,min([event_time]) as Min_event_time
,max([event_time]) as Max_event_time
,[Error_Value]
,[AppName]
,min([HostName]) as Ex1_HostName
,max([HostName]) as Ex2_HostName
,min([UserName]) as Ex1_UserName
,max([UserName]) as Ex2_UserName

from #EventDetail

where
IsNull([Error_Value],0) < 50000
group by
[DatabaseName]
,[ObjectName]
,[event_name]
,[Error_Value]
,[message]
,[AppName]
order by
[# of Errors] desc
 GO







Introducing SQL Server Extended Events