Goodbye to MCM/MCSM Certifications

Originally I had planned to blog on my path to the Microsoft Certified Master certification but given Microsoft's recent decision to cancel the certification my plans changed. At 1AM last Friday, the start to Labor Day weekend, Microsoft sent an email to the MCM/MCA community alerting us to the fact that effective October 1st, the advanced certifications will be no more. This abrupt decision coupled with the timing of the announcement, 10PM PST at the start to Labor Day weekend, raises many questions. The fact that this announcement comes almost two weeks after Microsoft announced plans to expand the testing for the program makes this announcement even more surprising.

Tim Sneath from Microsoft Learning responding to comments on a Microsoft Connect item points out:

The truth is, for as successful as the program is for those who are in it, it reaches only a tiny proportion of the overall community. Only a few hundred people have attained the certification in the last few years, far fewer than we would have hoped. We wanted to create a certification that many would aspire to and that would be the ultimate peak of the Microsoft Certified program, but with only ~0.08% of all MCSE-certified individuals being in the program across all programs, it just hasn't gained the traction we hoped for.

I certainly understand Microsoft's point here but it seems this could have been handled better. My journey to MCM status took a little over a year once I had decided to go for it. A long the way there were costs for exams, books, and online studying materials. I can only imagine that there are many people who have started their journey and are now told that they have until October 1st to complete it. It seems Microsoft could given more notice to those people who are in the process of trying to achieve MCM or MCSM status. Paul Randal has a survey on his blog asking Survey: SQL Server MCM cancellation – does it affect you?. From a PR standpoint it seems Microsoft certainly could have handled this better.

I had hoped to eventually recoup my investment in getting the MCM certification over time but now I wonder will anyone recognize or respect the MCM title once the program is cancelled. I also wonder if everyone will understand what it takes to become a SQL MCM and the depth of knowledge it requires. Only time will tell as far as that goes but so far it doesn't look good.

Even with that being said my path to MCM was a good one. The knowledge I gained made me a better DBA long before I gained the MCM title. I understand not everyone is able to afford the costs associated with the MCM program but I would certainly recommend that everyone take a look at the free MCM readiness videos on the Microsoft website. These videos along with blogs and books were the study material that I used.

According to Time Sneath Microsoft is looking to modify or create a new advanced certification with the hopes of reaching more people. I certainly welcome this and encourage them to do so but I hope that it will still require some sort of hands-on testing, which validates a user's knowledge and understanding, as opposed to just multiple choice questions. As others have stated we don't want to see a "Masters Lite" certification. The Masters certification gives people something to strive towards and allows them to differentiate themselves from their peers hopefully whatever Microsoft comes up will still allow this.

I certainly hope Microsoft reconsiders cancelling the advanced certifications.

SQL Cursors and TempDB

During a meeting yesterday the question was asked "Aren't cursors stored in tempDB?"  My initial response was no they will be rendered in memory but the correct answer is..."It depends!" Let me preface the rest of this blog post by saying when possible use set-based operations rather than cursors as cursors may be a hit on performance, but as in all things SQL it is best to test.

According to Books Online, Insensitive/Static cursors create a temporary copy of the data being accessed by the cursor in TempDB.  Likewise a Keyset cursor will build a keyset containing the uniquely identifiying columns for the cursor in TempDB.  Other cursors Fast_Forward, Forward_Only, and Dynamic do not incur the overhead associated with storing all of the cursor data in TempDB.  By using the dmv, sys.dm_session_space_usage, I was able to view the page allocations in TempDB using the different cursor types.  I noticed that there were page allocations to TempDB no matter which type of cursor was chosen but there were far more page allocations for a Keyset cursor  versus a Fast_Forward or Static cursor.

When programming cursors I typically declare a cursor as Fast_Forward which creates a forward only static cursor.  This option performs better than other cursor options and seems to work well for me as I rarely have to move backwards through a cursor or modify the cursor data.  If I am updating or deleting data through a cursor then I will use a Forward_Only, Dynamic cursor.  I often have seen developers use the ISO syntax when creating cursors such as follows.

1:  DECLARE cur_DBs CURSOR  
2:  FOR  
3:  SELECT Name  
4:  FROM sys.databases d  
5:  WHERE d.state_desc = 'ONLINE';  

By omitting the T-SQL cursor model in the declaration statement a Keyset, optimistic, non-scrollable cursor is created which will have its keyset materialized in TempDB.

So there you have it.  Cursors will have some page allocations in TempDB but how many will depend on the type of cursor being declared.  When declaring cursors Fast_Forward cursors have less overhead and impact on TempDB.  So when reviewing or writing code remember to declare the type of cursor being created to reduce overhead.

Dynamically Setting Max Server Memory on Cluster

I was recently given the task of adding a new node to our one node cluster with two SQL instances.  Due to politics within the company what was originally going to be a two-node SQL cluster had lingered around as a single node cluster for over a year. The issue I was confronted with was how to maximize memory usage on both nodes of the cluster but ensure that the two instances do not compete for memory if they happen to be running on the same node.

The solution I came up with was to query the server to determine the physical node name that each of the SQL instances was running on.  If after running both queries it is determined that the instances are running on the same node then the max server memory for both servers is reduced to prevent contention.  Conversely, if both instances are running on different nodes then the max server memory is increased to leave just enough for the operating system.  I remembered that changing the max server memory on an instance causes a dump of the procedure cache so I had to add logic to determine if the max server memory needed to be changed or if it could be left as it was.

There are several ways to get the code I created to run at startup.  One option is to create a stored procedure that runs the code and to have the stored procedure run at startup for the instance.  To execute the procedure this way the server must be configured to scan for startup procedures and you must run sp_procoption to set the stored procedure to run at startup. I chose to create a SQL Agent job to run the code and set the job schedule to run at agent startup to keep from having to reconfigure the server.  This way is also more transparent for any future DBAs.

In order to change the max server memory setting I had to create a linked server on both servers that I was sure would have ALTER SETTINGS permission.

Below is the code I created.

1:  DECLARE @standalonememory    int      = 125000  
2:  DECLARE @sharedmemory      int      = 55000  
3:  DECLARE @SQLRPTHost       nvarchar(50)  
4:  DECLARE @SQLDWHost        nvarchar(50)   
5:  DECLARE @SQLRPTmaxmemory     int   
6:  DECLARE @SQLDWmaxmemory     int   
7:     
8:  --Retrieve the host name for SQLRPT host    
9:  SELECT @SQLRPTHost = CAST(SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS nvarchar(50))--Returns Node Name  
10:    
11:  -- Retrieve the host name for SQLDW      
12:  SELECT @SQLDWHost = ComputerNamePhysicalNetBIOS  
13:  FROM OPENQUERY (SYS_SQLDW,'SELECT CAST(SERVERPROPERTY(''ComputerNamePhysicalNetBIOS'') AS nvarchar(50)) AS ComputerNamePhysicalNetBIOS');  
14:    
15:  --Retrieve the max server memory on current server    
16:  SELECT @SQLRPTmaxmemory = CAST(value as int)     
17:  FROM sys.configurations    
18:  WHERE name = 'max server memory (MB)'   
19:    
20:  --Retrieve the max server memory on remote server    
21:  SELECT @SQLDWmaxmemory = value     
22:  FROM OPENQUERY(SYS_SQLDW, 'SELECT CAST(value as int) AS value  
23:      FROM sys.configurations  
24:      WHERE name = ''max server memory (MB)''')    
25:    
26:  IF @SQLRPTHost = @SQLDWHost      --Both instances running on same host      
27:   BEGIN        
28:      --Max Memory should be set to sharedmemory if not set it       
29:      IF @SQLRPTmaxmemory != @sharedmemory       
30:       BEGIN         
31:        
32:         EXEC sp_configure 'Max Server Memory', @sharedmemory         
33:         RECONFIGURE        
34:    
35:       END     --Check the memory setting for the SQLDW instance       
36:    
37:      IF @SQLDWmaxmemory != @sharedmemory  
38:       BEGIN         
39:       
40:         EXEC ('sp_configure ''Max Server Memory'', ' + @sharedmemory + '; RECONFIGURE') AT SYS_SQLDW        
41:     
42:       END        
43:    
44:      END     
45:         ELSE --Servers are each running on their own node      
46:          BEGIN         
47:            --Max Memory should be set to stand alone memory if not set it       
48:             IF @SQLRPTmaxmemory != @standalonememory       
49:    
50:              BEGIN         
51:                 
52:                EXEC sp_configure 'Max Server Memory', standalonememory         
53:                RECONFIGURE        
54:    
55:              END       
56:               
57:              --Check the memory setting for the SQLDW instance       
58:              IF @SQLDWmaxmemory != @standalonememory       
59:    
60:               BEGIN         
61:                    
62:                   EXEC ('sp_configure ''Max Server Memory'', ' + @standalonememory + '; RECONFIGURE') AT SYS_SQLDW        
63:                   
64:    
65:                END        
66:              END