Performance analysis on SQL Server—how to find resource hogs

There's many different ways of doing performance analysis in SQL Server, and multiple tools you can use. Or, you can also wait for users to complain, and tell you that that certain parts of the application are slow.

Of course, the built-in dynamic management view dm_exec_query_stats is a great tool to add to your list. It can be very useful just as it is. But in doing some performance research recently, I was able to get some very targeted comparative information on performance. This information was extremely useful in deciding which stored procedures, precisely, should be analyzed and improved. Often the stored procedures ones that are at the top of the list have a glaringly obvious flaw that can be fixed, for a big performance boost.

The below script creates a global temporary table, called ##PerformanceReporting. The table is then populated with information from dm_exec_query_stats. The ObjectName field is parsed out from the query text.

A few caveats needs to be given here. The code below is best suited for systems where the database is accessed through stored procedures. Also, stored procedures and functions are listed separately, but most often functions will be used within stored procedures, so that may be misleading.

 

if object_id('tempdb..##PerformanceReporting') is not null drop table tempdb..##PerformanceReporting
go

Create table ##PerformanceReporting (
    ObjectName varchar(100)
    ,QueryText varchar(100)
    ,TotalLogicalReads bigint
    ,ExecutionCount int
    ,TotalCPUTimeInSeconds int
    ,LastExecutionTime date
    ,Rank_LogicalReads int
    ,Rank_CPU int
    ,SumTotalLogicalReads bigint
    ,SumTotalCPU bigint
)    

-- Insert high logical reads
Insert into ##PerformanceReporting (
    QueryText
    ,TotalLogicalReads 
    ,ExecutionCount 
    ,TotalCPUTimeInSeconds 
    ,LastExecutionTime 
    ,Rank_LogicalReads 
    ,SumTotalLogicalReads
    ,SumTotalCPU
)
select 
    QueryText = convert(varchar(100), SQLText.text) 
    ,TotalLogicalReads = sum(total_logical_reads )
    ,ExecutionCount = sum(execution_count )
    ,TotalCPUTimeInSeconds = sum(total_worker_time/1000000 )    
    ,LastExecutionTime = max(last_execution_time )
    ,RowNumber = ROW_NUMBER() OVER (ORDER BY sum(total_logical_reads)  desc)
    ,SumTotalLogicalReads = (select sum(total_logical_reads) from sys.dm_exec_query_stats )
    ,SumTotalCPU = (select sum( total_worker_time)/1000000 from sys.dm_exec_query_stats )    
from 
    (select top 30
        last_execution_time
        ,execution_count
        ,plan_handle
        ,total_worker_time
        ,total_logical_reads
    from sys.dm_exec_query_stats 
    order by total_logical_reads desc
    ) QueryStats
cross apply sys.dm_exec_sql_text(plan_handle) SQLText
Group by convert(varchar(100), SQLText.text) 

-- Insert high CPU 
Insert into ##PerformanceReporting (
    QueryText
    ,TotalLogicalReads 
    ,ExecutionCount 
    ,TotalCPUTimeInSeconds 
    ,LastExecutionTime 
    ,Rank_CPU 
    ,SumTotalLogicalReads    
    ,SumTotalCPU
)
Select 
     QueryText = convert(varchar(100), [text]) 
    ,TotalLogicalReads = sum(total_logical_reads)
    ,ExecutionCount = sum(execution_count )
    ,TotalCPUTimeInSeconds = sum( total_worker_time)/1000000 
    ,LastExecutionTime = max(last_execution_time )
    ,RowNumber = ROW_NUMBER() OVER (ORDER BY sum(total_worker_time) desc)
    ,SumTotalLogicalReads = (select sum(total_logical_reads) from sys.dm_exec_query_stats )
    ,SumTotalCPU = (select sum( total_worker_time)/1000000 from sys.dm_exec_query_stats )    
from 
    (select top 30
        last_execution_time
        ,total_logical_reads
        ,execution_count
        ,plan_handle 
        ,total_worker_time
    from sys.dm_exec_query_stats 
    order by total_worker_time desc) QueryStats
cross apply sys.dm_exec_sql_text(plan_handle) 
Group by convert(varchar(100), [text]) 

-- Update ObjectName to clean up the QueryText
;With CleanUpQueryText as (
 Select
        ObjectNameClean = 
   replace (
    QueryText 
    ,'CREATE Function [dbo].['
    ,'Func: ' )
  ,QueryText
 From ##PerformanceReporting
 Where QueryText like '%function%'
 Union all 
 Select
  ObjectNameClean = 
   replace (
    QueryText 
    ,'CREATE Procedure [dbo].['
    ,'Proc: ' )
  ,QueryText
 From ##PerformanceReporting
 Where QueryText like '%Procedure%'
)
Update ##PerformanceReporting
Set ObjectName = Substring(ObjectNameClean, 1, Charindex ( ']', ObjectNameClean)- 1)
From ##PerformanceReporting 
    join CleanUpQueryText
        on ##PerformanceReporting.QueryText = CleanUpQueryText.QueryText

-- Update ObjectName - only if null 
--(which can happen if it's not a procedure or a function)
Update ##PerformanceReporting
Set ObjectName = QueryText 
Where ObjectName is null

Once the ##PerformanceReporting table has been created, you can query it in a number of useful ways. The first 2 queries below show the details of the objects that have the highest logical reads, and highest CPU usage. The third query shows those objects that have both high logical reads and high CPU. Targeting these should give you the most bang for the buck, in terms of analysis and improvement.

 

-- High Logical Reads
Select 
    ObjectName 
    ,TotalLogicalReads 
    ,ExecutionCount 
    ,TotalCPUTimeInSeconds 
    ,LastExecutionTime 
    ,Rank_LogicalReads
    ,PercentOfTotal = 
        convert(decimal(4, 3), 
            (TotalLogicalReads * 1.0)/SumTotalLogicalReads
            )
From ##PerformanceReporting
Where 
    Rank_LogicalReads  is not null
Order by Rank_LogicalReads 

-- High CPU
Select 
    ObjectName 
    ,TotalLogicalReads 
    ,ExecutionCount 
    ,TotalCPUTimeInSeconds 
    ,LastExecutionTime 
    ,Rank_CPU
    ,SumTotalCPU
    ,PercentOfTotal = 
        convert(decimal(4, 3), 
            ( TotalCPUTimeInSeconds* 1.0)/SumTotalCPU 
            )    
From ##PerformanceReporting
Where 
    Rank_CPU  is not null
Order by Rank_CPU


-- High for BOTH CPU and logical reads
Select 
    ObjectName = 
        isnull(HighLogicalReads.ObjectName, HighCPU.ObjectName)
    ,HighLogicalReads.Rank_LogicalReads
    ,HighCPU.Rank_CPU    
    ,Percent_TotalLogicalReads = 
        convert(decimal(4, 3), 
            (HighLogicalReads.TotalLogicalReads * 1.0)/HighLogicalReads.SumTotalLogicalReads
            )   
    ,Percent_TotalCPU = 
        convert(decimal(4, 3), 
            ( HighCPU.TotalCPUTimeInSeconds* 1.0)/HighCPU.SumTotalCPU 
            )    
From (Select * from ##PerformanceReporting where Rank_LogicalReads is not null) HighLogicalReads
    Full Outer Join (Select * from ##PerformanceReporting where Rank_CPU is not null) HighCPU
        on HighCPU.ObjectName = HighLogicalReads.ObjectName
Order by     
    isnull(HighLogicalReads.Rank_LogicalReads, 0) 
    +
    isnull(HighCPU.Rank_CPU, 0) 

Efficiently analyzing deadlocks on SQL Server with Extended Events

At my current consulting contract, we were running into a lot of deadlocks. It's been a long time since I analyzed deadlocks. And the system I'm working on had a lot of them, right after we had a very sudden and extreme increase in usage.

There were so many deadlocks that we needed to be efficient in deciding which ones to fix first. So I researched. I hit a lot of dead ends, and ran into a lot of advertising for specific database monitoring tools. But it turns out that there's some very good deadlock reporting, built right into SQL Server, out of the box.

Basically, SQL Server Extended events can be queried for deadlock occurrences. You have to use XML querying, with nodes, but there's plenty of examples online. The script I started out with is the one here: Querying Deadlocks From System_Health XEvent. I made some modifications to be able to do more analysis without constantly rerunning the base query, which takes a while.

So, Step 1 is to run the below, which creates a global temp table called ##DeadlockReporting, which can be queried later.

 if object_id('tempdb..##DeadlockReporting') is not null drop table tempdb..##DeadlockReporting  
      
 DECLARE @SessionName SysName   
 SELECT @SessionName = 'system_health'  
 /*   
 SELECT Session_Name = s.name, s.blocked_event_fire_time, s.dropped_buffer_count, s.dropped_event_count, s.pending_buffers  
 FROM sys.dm_xe_session_targets t  
      INNER JOIN sys.dm_xe_sessions s ON s.address = t.event_session_address  
 WHERE target_name = 'event_file'  
 --*/  
   
 IF OBJECT_ID('tempdb..#Events') IS NOT NULL BEGIN  
      DROP TABLE #Events  
 END  
   
 DECLARE @Target_File NVarChar(1000)  
      , @Target_Dir NVarChar(1000)  
      , @Target_File_WildCard NVarChar(1000)  
   
 SELECT @Target_File = CAST(t.target_data as XML).value('EventFileTarget[1]/File[1]/@name', 'NVARCHAR(256)')  
 FROM sys.dm_xe_session_targets t  
      INNER JOIN sys.dm_xe_sessions s ON s.address = t.event_session_address  
 WHERE s.name = @SessionName  
      AND t.target_name = 'event_file'  
   
 SELECT @Target_Dir = LEFT(@Target_File, Len(@Target_File) - CHARINDEX('\', REVERSE(@Target_File)))   
   
 SELECT @Target_File_WildCard = @Target_Dir + '\' + @SessionName + '_*.xel'  
   
 --Keep this as a separate table because it's called twice in the next query. You don't want this running twice.  
 SELECT DeadlockGraph = CAST(event_data AS XML)  
      , DeadlockID = Row_Number() OVER(ORDER BY file_name, file_offset)  
 INTO #Events  
 FROM sys.fn_xe_file_target_read_file(@Target_File_WildCard, null, null, null) AS F  
 WHERE event_data like '<event name="xml_deadlock_report%'  
   
 ;WITH Victims AS  
 (  
      SELECT VictimID = Deadlock.Victims.value('@id', 'varchar(50)')  
           , e.DeadlockID   
      FROM #Events e  
           CROSS APPLY e.DeadlockGraph.nodes('/event/data/value/deadlock/victim-list/victimProcess') as Deadlock(Victims)  
 )  
 , DeadlockObjects AS  
 (  
      SELECT DISTINCT e.DeadlockID  
           , ObjectName = Deadlock.Resources.value('@objectname', 'nvarchar(256)')  
      FROM #Events e  
           CROSS APPLY e.DeadlockGraph.nodes('/event/data/value/deadlock/resource-list/*') as Deadlock(Resources)  
 )  
 SELECT *  
 Into ##DeadlockReporting  
 FROM  
 (  
      SELECT   
     e.DeadlockID  
           ,TransactionTime = Deadlock.Process.value('@lasttranstarted', 'datetime')  
           , DeadlockGraph  
           , DeadlockObjects = substring((SELECT (', ' + o.ObjectName)  
                                    FROM DeadlockObjects o  
                                    WHERE o.DeadlockID = e.DeadlockID  
                                    ORDER BY o.ObjectName  
                                    FOR XML PATH ('')  
                                    ), 3, 4000)  
           , Victim = CASE WHEN v.VictimID IS NOT NULL   
                                    THEN 1   
                               ELSE 0   
                               END  
           , SPID = Deadlock.Process.value('@spid', 'int')  
           , ProcedureName = Deadlock.Process.value('executionStack[1]/frame[1]/@procname[1]', 'varchar(200)')  
           , LockMode = Deadlock.Process.value('@lockMode', 'char(1)')  
           , Code = Deadlock.Process.value('executionStack[1]/frame[1]', 'varchar(1000)')  
     , ClientApp = CASE LEFT(Deadlock.Process.value('@clientapp', 'varchar(100)'), 29)  
                               WHEN 'SQLAgent - TSQL JobStep (Job '  
                                    THEN 'SQLAgent Job: ' + (SELECT name FROM msdb..sysjobs sj WHERE substring(Deadlock.Process.value('@clientapp', 'varchar(100)'),32,32)=(substring(sys.fn_varbintohexstr(sj.job_id),3,100))) + ' - ' + SUBSTRING(Deadlock.Process.value('@clientapp', 'varchar(100)'), 67, len(Deadlock.Process.value('@clientapp', 'varchar(100)'))-67)  
                               ELSE Deadlock.Process.value('@clientapp', 'varchar(100)')  
                               END   
           , HostName = Deadlock.Process.value('@hostname', 'varchar(20)')  
           , LoginName = Deadlock.Process.value('@loginname', 'varchar(20)')  
           , InputBuffer = Deadlock.Process.value('inputbuf[1]', 'varchar(1000)')  
      FROM #Events e  
           CROSS APPLY e.DeadlockGraph.nodes('/event/data/value/deadlock/process-list/process') as Deadlock(Process)  
           LEFT JOIN Victims v ON v.DeadlockID = e.DeadlockID AND v.VictimID = Deadlock.Process.value('@id', 'varchar(50)')  
 ) X --In a subquery to make filtering easier (use column names, not XML parsing), no other reason  
 ORDER BY TransactionTime DESC  
   

Now I have a global temp table called ##DeadlockReporting.

Step 2 is to run queries against it. Here's some that I wrote up. The most useful is the one with the header "Get the worst offenders in past 24 hours". This will give you a good picture of the deadlock—the affected tables, the stored procedures that are being called, the specific problem code. Then, click on the DeadlockXML in order to see the full details of the deadlock.

You may find some really obvious targets for fixes that need to be made. I found some update statements that contained subqueries in the Set clause that needed to be fixed.

 Select top 100 * From ##DeadlockReporting order by TransactionTime desc  
   
 -- Get the worst offenders in past 24 hours  
 -- Click on the DeadlockXML to show details  
 Select   
   DeadlockObjects  
   ,ProcedureName  
   ,Code = min(Code)  
      ,DeadlockXML = convert(xml, min(convert(varchar(max), DeadlockGraph) ))  
      ,FirstDeadlock = Min(TransactionTime)  
      ,LastDeadlock = Max(TransactionTime)  
   ,PresumedTotalDeadlocks = Count(*)    
 from ##DeadlockReporting    
 Where TransactionTime >= dateadd (hh, -24, getdate() )  
 Group by   
   DeadlockObjects  
   ,ProcedureName  
 Order by Count(*)  desc  
   
 -- Get summary past 24 hours, by hour  
 ;with DeadlockReportingFormatted as (  
   Select   
     TransactionHour_UTC = convert(varchar(13), TransactionTime, 120) + ':00'  
     ,TransactionHour_PST =   
       convert(varchar(13), TransactionTime AT TIME ZONE 'UTC' AT TIME ZONE 'Pacific Standard Time', 120)   
       + ':00'  
     ,*  
   from ##DeadlockReporting     
 )  
 Select   
   TransactionHour_UTC  
   ,TransactionHour_PST  
   ,DeadlockObjects  
   ,ProcedureName  
   ,PresumedTotalDeadlocks = Count(*)    
 From DeadlockReportingFormatted  
 Where TransactionTime >= dateadd(hh, -24, getdate())  
 Group by   
   TransactionHour_UTC  
   ,TransactionHour_PST  
   ,DeadlockObjects  
   ,ProcedureName  
 Order by   
   TransactionHour_UTC desc  
   ,PresumedTotalDeadlocks desc  
   

Automatically scaling up and down between pricing tiers in Azure SQL databases

We want a certain level of performance for our Azure databases, but sometimes we don't need that performance 24/7. And if we don't need it, we don't want to pay for it!

So, I put together a script that can be run regularly (perhaps hourly) and has a set of specific business hours during which we want to be scaled up, along with the tiers that we want to be scaled to. The business hours are set up to be 7 AM to 10 PM. Then during non-business hours, we scale down to the appropriate level. It takes into consideration weekends/weekdays as well.

This is currently running with SQLCMD on a VM, but we'll probably switch to Azure Automation eventually.

The heart of the code is the @PricingTierTarget table (done as a table variable in the actual code below). It has all the DB names, and the scale up/scale down information.

Note that you use this code at your own risk! I would particularly be aware of switching between editions (for instance, switching from Standard to Basic). This will affect the point in time to which you can restore the database (among other features that differ between the editions).

 /*
----------------------------------------------------------------------------
Switch Azure databases between pricing tiers depending on whether the current time is business hours or not

If it's during business hours, then mode is ScaleUp.
If it's not during business hours, then mode is ScaleDown.

Our main database (I'm calling it MainDB database here) is scaled between Standard S2 to Standard S4.
All other databases are scaled down to Basic Basic and must be manually scaled up

Note - this code will run and complete, but the databases take longer to switch tiers. That 
may take a minute or two (depending on size).
To see when it actually finishes, you can check the table sys.dm_operation_status.

----------------------------------------------------------------------------
*/


Set nocount on

Declare 
    @Now datetime = GetDate()
    ,@SQL nvarchar(3000) = ''
    ,@BusinessHoursStartTime tinyint = 7
    ,@BusinessHoursEndTime tinyint = 20
    ,@ScalingMode varchar(20) = 'ScaleDown'
    ,@SingleQuote  nchar(1)    = char(39)
    ,@NewLine char(2) = char(13) + char(10)
    ,@Message nvarchar(3000)
   

Declare
    @PricingTierTarget table (
        DBName sysname
        ,ScaleUpEdition varchar(20)
        ,ScaleUpServiceObjective varchar(20)
        ,ScaleDownEdition varchar(20)
        ,ScaleDownServiceObjective varchar(20)
        ,CurrentEdition varchar(20)
        ,CurrentServiceObjective varchar(20)
        ,TargetEdition      varchar(20)
        ,TargetServiceObjective varchar(20)
        ,NeedAlterDatabase  bit default 0
    )

-- Convert to Central Standard Time    
Select @Now = @Now AT TIME ZONE 'UTC' AT TIME ZONE 'Central Standard Time'

-- Determine if we should scale up or scale down
If 
    -- Is it a business day?
    ((Datename(dw,@Now) in ('Monday', 'Tuesday','Wednesday','Thursday','Friday')) 
    and 
    -- Are we in business hours?
    (Datepart(hour, @Now) between @BusinessHoursStartTime and @BusinessHoursEndTime))
    Begin
    Set @ScalingMode = 'ScaleUp'
End
    
-- Populate @PricingTierTarget table    
Insert into @PricingTierTarget (DBName,ScaleUpEdition,ScaleUpServiceObjective,ScaleDownEdition,ScaleDownServiceObjective)
values
-- DBName                   ScaleUpEdition  ScaleUpServiceObjective   ScaleDownEdition    ScaleDownServiceObjective
('MainDB'              , 'Standard'    , 'S4'                    , 'Standard'        , 'S2')
,('MainDBDev'          , 'Basic'       , 'Basic'                 , 'Basic'           , 'Basic')
,('MainDBStaging_Map'  , 'Basic'       , 'Basic'                 , 'Basic'           , 'Basic')
,('MainDB_WebDev'      , 'Basic'       , 'Basic'                 , 'Basic'           , 'Basic')

-- If there are NEW databases, set them up as basic/basic
Insert into @PricingTierTarget (DBName,ScaleUpEdition,ScaleUpServiceObjective,ScaleDownEdition,ScaleDownServiceObjective)
Select Name, 'Basic'       , 'Basic'                 , 'Basic'           , 'Basic'
From sys.databases 
Where 
    Name not in (Select DBName from @PricingTierTarget)
    and Name <> 'Master'

-- Get what the databases are currently set to - the current Edition and Service Objective
Update PricingTierTarget
Set 
    CurrentEdition = slo.Edition
    ,CurrentServiceObjective = slo.Service_Objective
from @PricingTierTarget PricingTierTarget
    Join sys.databases db
        on db.Name = PricingTierTarget.DBName
    Join sys.database_service_objectives slo    
        on db.database_id = slo.database_id 
        
-- Loop through the databases, finding those databases that need to be altered because they are not
-- at the appropriate pricing tier 
If @ScalingMode = 'ScaleUp' Begin
    Update @PricingTierTarget
    Set
        TargetEdition = ScaleUpEdition
        ,TargetServiceObjective = ScaleUpServiceObjective
        ,NeedAlterDatabase = 1
    Where 
        CurrentEdition <> ScaleUpEdition
        or 
        CurrentServiceObjective <> ScaleUpServiceObjective
End

else if @ScalingMode = 'ScaleDown' Begin
    Update @PricingTierTarget
    Set
        TargetEdition = ScaleDownEdition
        ,TargetServiceObjective = ScaleDownServiceObjective
        ,NeedAlterDatabase = 1
    Where 
        CurrentEdition <> ScaleDownEdition
        or 
        CurrentServiceObjective <> ScaleDownServiceObjective
End

-- Start setting up @Message
Select @Message = @NewLine + @NewLine + 'Current time in Central Standard time zone: ' + convert(varchar(20), @Now) + @NewLine 

-- Generate the Alter Database script where NeedAlterDatabase is True
Select @SQL = @SQL + 
    'Alter database ' + DBName + ' modify (Edition = ' 
        + @SingleQuote + TargetEdition + @SingleQuote 
        + ',SERVICE_OBJECTIVE = ' 
        + @SingleQuote +  TargetServiceObjective + @SingleQuote + ')'
        + @NewLine
From @PricingTierTarget
Where 
    NeedAlterDatabase = 1

If @@Rowcount > 0 Begin    
    -- If databases exist that need their pricing tier changed, print a message and run the alter database script
    Select @Message = @Message + 'Running SQL: ' + @NewLine + @SQL
    Print @Message
    Exec sp_executesql @SQL

End
Else Begin
    -- No databases need to be changed - just print message
    Select @Message = @Message + 'No databases need to have their pricing tier changed'
    Print @Message
end

Why most databases have so many useless and confusing objects in them

Recently I was working with a database system, trying to validate some output from an OLAP cube. I ran into discrepancies, and (with a lot of work and troubleshooting) narrowed it down to the fact that in one query, two tables were joined with the Product_ID field, and in another query, the two tables were joined via the dim_Product_ID field. These two fields, Product_ID and dim_Product_ID were identical most of the time, but not always! They were different enough to cause major problems trying to match numbers.

Here's another example of useless and confusing objects - the database system I'm working with now is littered with the remains of what look like three abortive attempts to create a data warehouse, all of them currently unused. But the fact that there's all these separate tables and databases out there, replicating very similar data in different formats - it's enormously confusing to anyone trying to do new work on the system, and trying to figure out what's being used and what's not.

Why is it? Why do databases become cluttered with unused objects, that cause massive headaches for anyone trying to use the data?

Here's what I think. It's a heck of a lot of work to try to figure out what's used and what's not, and archive off/delete the unused objects. Not only is it a a lot of work, but it's unrewarding and unappreciated work. You need to check with lots of people, try to figure out what might actually be used, compare lots of code and data.

And most managers don't really have a solid understanding of how much more work basic database tasks can be if the database contains a lot of leftover tables and other database objects. So the task of cleanup is never prioritized. Even though, over the long run, a clean, well-ordered database makes life so much easier for everyone that uses it.

The work that gets the glory and the praise is building new things - a brand new shiny data warehouse, for instance - even though it may just be adding to the general confusion in the database, especially (as happens so often) if it ends up not being used.

For readable code, avoid code you need to translate in your head

I'm a big advocate of readable code. These include things like good aliasing for tables when necessary, separating code out into logical chunks, and CTE (common table expressions) instead of derived tables.

One thing I've always tried to do is avoiding code that requires translation. What do I mean by that? I try to avoid code such as this:

-- Call Answer Time After Hours
when IsBusinessHours = 'False' and SLARuleID = 2  
    then Sum([AnswrIn30Seconds]+[AnswrIn45Seconds])

It's okay when there's just one chunk of code like this, but if there's a whole set of code like this, you're more likely to make mistakes, because you need to internally translate, "So, if it's after hours, than the IsBusinessHours must be False." This caused me a hard-to-troubleshoot bug recently.

What I'd rather read is something like this:

-- Call Answer Time After Hours
when IsAfterHours = 'True' and SLARuleID = 2  
    then Sum([AnswrIn30Seconds]+[AnswrIn45Seconds])
    

It's much easier to understand. So what I do now in these situations is to set up a field that's the opposite of the original, when it would help readability. I use something like this

        ,IsAfterHours   = 

            convert(bit,
                Case 
                    When IsBusinessHours = 1 Then 0
                    When IsBusinessHours = 0 Then 1
                    Else Null 
                End
                )

Flexible update trigger - take the column value when provided, otherwise have the trigger provide a value

We have a small, departmental database with some tables that will sometimes be updated via a batch process, and sometimes via a people inputting the data directly in a datasheet. Here's a neat trick to allow us to have auditing fields (UpdateDate , LastUpdatedBy) that will either reflect the value provided by the update statement, OR provide a default value, if no value is provided in the update statement.  The trigger does this using the Update() function, which has apparently been around for a while, but I'd never used it before.

Here's the trigger:

 IF EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID('dbo.trg_Update_SLAMetric'))  
 DROP TRIGGER dbo.trg_Update_SLAMetric  
 go  
 CREATE TRIGGER trg_Update_SLAMetric  
 ON dbo.SLAMetric  
 AFTER UPDATE  
 AS  
 -- Update UpdateDate , LastUpdatedBy  
 UPDATE SLAMetric  
 SET   
   UpdateDate = GETDATE()  
   ,LastUpdatedBy =   
     case   
       When UPDATE(LastUpdatedBy) then Inserted.LastUpdatedBy  
       else SUSER_NAME()  
     end  
 from dbo.SLAMetric SLAMetric  
   join Inserted  
     on Inserted.SLAMetricID = SLAMetric.SLAMetricID  

It correctly updates the LastUpdatedBy field in both situations - updating the table and including the LastUpdatedBy field, and also just updating other fields (in which case it's just set to SUSER_NAME().

 update slametric set MetricNumerator = 0  
 select * from slametric  
 update slametric set LastUpdatedBy = 'test'  
 select * from slametric  

Microsoft Azure error - "Cannot open database "master" requested by the login"

I've been busy setting up a database for a TSQL programming class that I'll be teaching in a few weeks. I've run into a few things that made me scratch my head. Here's one of them.

I created a sample database, a new login, and a user to go with the login. Then I gave the user read only access to the database, via sp_addrolemember.

When I used that login to connect via SSMS (File, Connect Object Explorer, enter the new login, also click on the Options button to specify the correct database), it appears to connect fine, and I see the connection.

However, when I expand the databases tab, and try to right-click the sample database to get the New Query option, I get the message "Cannot open database "master" requested by the login. The login failed.". So even though I was trying to open a query in the sample database, it tried accessing the master database.

Workaround:
This is a strange one, but the workaround is to right click on the connection object, and not the database. This will allow you to open up a new query, and will automatically put you in the database you specified in the connection options.

Doing this involves a bit of rethinking, because most developers who have worked extensively with SSMS are accustomed to right clicking on a database and selecting New Query to open a connection. But this won't work with Azure, unless you're also a user in the Master database.

Hopefully this write-up will save a few people some time when they go searching for details on this issue. Feel free to comment with more information.