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"

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

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

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)]

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