The Android Speaks

Continuing the thread I started in an earlier post I mentioned how I’ve been working today on trying to diagnose some performance issues on a SQL Server at the office.  I started with a document on SQL Server 2005 Waits and Queues and looked specifically at the section on determining if I might have a CPU related bottleneck.  I don’t think I do, but for the sake of completeness I thought I would tackle a query to check on how much work the Server was doing to break queries into smaller chunks thereby dividing the work up between the CPUs on the server.  In other words, how much parallelism was going on.

Parallelism is indicated by the CXPACKET wait type.  OLTP systems with their short, repetitive SELECT, INSERT, UPDATE, and DELETE type queries are not typically candidates for parallelism.  In fact parallelism on an OLTP system is a performance killer, reducing throughput.  In a Warehousing/Reporting system however, with is large datasets and long running queries, a certain amount of parallelism is desirable.  The Waits and Queues document suggests if you have CXPACKET waits representing more than 5% of your total waits on an OLTP system, you have a problem.  Likewise having CXPACKET waits representing less than 10% of the total waits on a Warehouse system suggests an issue.

With this in mind I thought it would be good to check out my system and see how it was doing in this area.  I put the query below together using the sys.dm_os_wait_stats system view to figure it out.  The result: my system the time spent waiting on parallelism is 3.0%.  Since the system is primarily doing OLTP I’m happy with that.

/*
 * Show degree of Parallelism: CXPACKET waits
 *
 * For OLTP systems this number should be <= 5%,
 * but for Warehouse systems this number should be >= 10%.
 * 
 * CXPACKET waits indicate that multiple CPUs are working
 * in parallel, dividing up the query into smaller pieces.
 * In an OLTP system this reduces throughput and it may
 * indicate a missing index, an incomplete WHERE clause,
 * or a query that is not a true OLTP transaction.
 *
 * On the other hand, parallelism is desirable in the
 * larger, longer running queries of a Warehouse system.
 *
 */

SELECT  CONVERT(varchar, CONVERT(numeric(10,1), (CX.[wait_time_ms]
        / CONVERT(numeric(15,3), SUM(T.[wait_time_ms])) * 100))) + '%' as 'CXPACKET Waits'
FROM    sys.dm_os_wait_stats CX
CROSS JOIN  sys.dm_os_wait_stats T
WHERE   CX.[wait_type] = 'CXPACKET'
GROUP BY CX.[wait_type], CX.[wait_time_ms]

In an earlier post I mentioned how I’ve been working today on trying to diagnose some performance issues on a SQL Server at the office.  I started with a document on SQL Server 2005 Waits and Queues and continuing my look at the section to determine if the performance bottleneck might be CPU related I tackled the calculation to determine how much time SQL Server sessions were spending in the runnable queue.

That calculation is explained in the document as being the difference between how much time is spent waiting for CPU as compared to the total time spent waiting overall.  If the signal wait time grows to be more than 25% of the total waits then there is probably pressure on the CPU.  I put the query below together using the sys.dm_os_wait_stats system view to figure it out.  Hmm… my system is currently only 0.6%.  That’s a good sign.

/*
 * Show signal wait time percentage
 *
 * Signal wait time should never be more than 25% of 
 * the total wait time.
 * 
 * Time spent in the runnable queue is pure CPU wait.
 * A high number indicates a CPU bottleneck.
 */

SELECT  CONVERT(varchar, CONVERT(numeric(10,1), SUM([signal_wait_time_ms]) 
        / CONVERT(numeric(15,3), SUM([wait_time_ms])) * 100)) + '%' AS 'Signal Wait'
FROM    sys.dm_os_wait_stats

I’ve been working today on trying to diagnose some performance issues on a SQL Server at the office.  I started with a document on SQL Server 2005 Waits and Queues but I was confused when looking at the section on determining if your performance issue might be CPU related and it talked about Plan re-use.  I found it confusing because it points to Perfmon SQL Server Statistics but doesn’t give any details about how to determine what statistics to look at nor how to calculate the plan re-use percentage.

Then I found this article entitled Top SQL Server 2005 Performance Issues for OLTP Applications which had this to say about plan re-use:

Plan re-use < 90% . A query plan is used to execute a query. Plan re-use is desirable for OLTP workloads because re-creating the same plan (for similar or identical transactions) is a waste of CPU resources. Compare SQL Server SQL Statistics: batch requests/sec to SQL compilations/sec. Compute plan re-use as follows: Plan re-use = (Batch requests - SQL compilations) / Batch requests.

Using this information and discovering the sys.dm_os_performance_counters view led me to create the query shown below which calculates the plan re-use percentage.  Unfortunately my server is currently running at 68.3% and has an OLTP workload.

/*
 * Show plan re-use percentage
 * For OLTP systems this number should be >= 90%,
 * but for Warehouse systems this number should be <= 25%.
 * 
 * All queries have a query plan created for them, this
 * query shows how often they are being re-used.
 */

SELECT  CONVERT(varchar, CONVERT(numeric(10,1), (BR.[cntr_value] - SC.[cntr_value])
        / CONVERT(numeric(15,3), BR.[cntr_value]) * 100)) + '%' AS 'Plan re-use'
FROM    sys.dm_os_performance_counters BR
CROSS JOIN  sys.dm_os_performance_counters SC
WHERE   BR.[object_name] LIKE '%SQL Statistics%'
AND     BR.[counter_name] = 'Batch Requests/sec'
AND     SC.[object_name] LIKE '%SQL Statistics%'
AND     SC.[counter_name] = 'SQL Compilations/sec'

I mentioned at the end of my first posting on creating our company's logon script that I was rather proud of my accomplishment with a script I called "MapPrinters.vbs". 

Now I don't know about where you work, but the office where I work is in constant flux.  For instance the Human Resources department has twice been located on the second floor and twice been located on the third floor and is currently transitioning again to a new location within the building.  That's a fairly high rate of change, especially considering we've only occupied the building for 11 years.  And the thing is, sometimes the nearby network printers move with the departments and sometimes they don't, consequently there is constantly a need to change printer settings to make sure users have access to their printers. 

This correspondence used to be encoded in our network logon script, but what a nightmare!  Every change meant opening up the script, editing it, and testing it to make sure nothing got broken in the process.  And what do you do about the legacy printers still setup on the computer?  Do you send the Help Desk guys out to the workstations to manually remove the printer mappings?  Even worse, what if you move the network printers to a new printer server?  Now the workstations have printer mappings that don't work at all!  That's going to cause you plenty of undesired social interaction with your users.

So since coding the printer mappings into the logon script was not working very well, how could I do it differently.  After examining a few options, I finally settled on the simplest solution being to leverage Active Directory.  To setup my solution, I first created an organizational unit (OU) that I named Printers. Then inside the Printers OU I created OU's for each print server using the machine name of the print server.  And, inside each print server OU, I created Domain Local Security Groups named after each printer attached to that print server.  Finally I connected each security group to the groups or individuals that were to be automatically mapped to the network printer represented by the security group.  Now, if a printer becomes unavailable or moves to a different print server, I leave the security group, but remove all the members from the group, so the corresponding printer will not be mapped by the logon script.  Similarly, if a printer server is decommissioned, I leave the corresponding printer server OU in Active Directory along with it's contained security groups, but remove the members from those security groups so they will be unmapped by the logon script.

The strategy used by the MapPrinters script is to first build a dictionary of all the groups the user is a member of, and then build a dictionary of all the security groups under the Printers OU that the user has membership in, either specifically by user name, or by the name of a group as recorded in the user's groups dictionary. With the list of valid printer mappings in hand, the script then iterates over the list of printers in the PRINTERS_AND_FAXES special folder on the local machine.  If the printer name ends with the name of a printer server then it looks it up in the dictionary.  If it matches, it leaves it, if it doesn't match it, then it deletes it.  Once it's finished, if there are any valid mappings left in the dictionary that didn't have a mapping they are created.  There are a few other things going on in the script, but that is basically all there is to it.

Using this strategy, the script is very efficient because it only has to unmap and map printers that have changed.  (Mapping and unmapping printers is a slow process!)  It also preserves any local printers that have been mapped, as well, all the printer administration can be handled in Active Directory, so a change can't break the script!  This is a much better solution to hard-coding things into the logon script.

Now, about the only thing you need to do to get started with this solution yourself is my script and  Active Directory.  Here is the script... (you're on your own with the Active Directory part.) but don't forget you'll need to tweak it to point to your actual Printer OU.  I hope you find it useful.

' VBScript source code
'******************************************************************************
' Group Policy Object User Logon Script
'
' MapPrinters.vbs
'
' This script enumerates the group objects in an OU named Printer, checks its
' member list against the logged in user and the logged in user's groups.  If
' there is a match it maps a printer based on the Printer OU's group object
' name.  It also removes invalid printer mappings based on permission changes
' or printers moving to new print servers.
'******************************************************************************
Option Explicit         'All variables must be defined
On Error Resume Next   

'******************************************************************************
' Define Global Variables and Constants
'******************************************************************************
' Configuration Constants
Const PRINTERS_AND_FAXES = &H4&
Const PRINTERSOU        = LDAP://ou=Printers,ou=Groups,dc=conteso,dc=com
Const DEVICEKEY         = "HKEY_CURRENT_USER\Software\Microsoft\Windows NT\CurrentVersion\Windows\Device"
Const DEFPRNKEY         = "HKEY_CURRENT_USER\Software\Conteso\Workstation\Default Printer"

' Declare Global Variables
Dim gobjWshNetwork      'Network object
Dim gobjWshShell        'Shell object
Dim gblnHadErrors       'Boolean variable indicating if the script completed cleanly
                        'or with errors

'******************************************************************************
' Main
'******************************************************************************
Call Initialize
Call SavePreferences
Call MapPrinters
Call RestorePreferences
Call CleanUp
'******************************************************************************
' END
'******************************************************************************


'******************************************************************************
' SUBROUTINES
'******************************************************************************

'******************************************************************************
' Sub:      Initialize
' Purpose:  Perform initialization for script.
' Input:    
' Output:
' Usage:    Call Initialize
'******************************************************************************
Private Sub Initialize
    On Error Resume Next

    Dim objWMIService       'Windows Management Instrumentation Service object
    Dim objSpoolerService
    Dim nStartCode

    'Initialize Global Variables with Default Values
    gblnHadErrors = False
    
    'Initialize Shell and Network Objects
    Set gobjWshShell = CreateObject("WScript.Shell")
    Set gobjWshNetwork  = CreateObject("WScript.Network")
    Set objWMIService = GetObject("winmgmts:\\.\root\CIMV2")
    
    'Verify that the Spooler service is started
    '(Otherwise we cannot browse the printer mappings)
    Set objSpoolerService = objWMIService.Get("Win32_Service.Name='Spooler'")
    If objSpoolerService.State = "Stopped" Then
        nStartCode = objSpoolerService.StartService()
        If nStartCode And (nStartCode <> 10) Then
            WScript.Quit(101)
        End If
    End If
End Sub

'******************************************************************************
' Sub:      SavePreferences
' Purpose:  Store user's printer preferences in the registry.
' Input:    
' Output:
' Usage:    Call SavePreferences
'******************************************************************************
Private Sub SavePreferences
    On Error Resume Next
    
    Dim strDevice
    Dim intPos
    Dim strPrinter
    
    'Get Current Default Printer and preserve it elsewhere in registry
    strDevice = gobjWshShell.RegRead(DEVICEKEY)
    intPos = InStr(1, strDevice, ",") - 1
    strPrinter = Left(strDevice, intPos)
    If (strPrinter = Empty) Or (strPrinter = Null) Then
        Wscript.StdErr.WriteLine "Unable to discover current default printer."
        gblnHadErrors = True
    Else
        gobjWshShell.RegWrite DEFPRNKEY, strPrinter
        If Err.number Then
            Wscript.StdErr.WriteLine "Unable to save default printer preference to registry."
            Wscript.StdErr.WriteLine Err.Description
            gblnHadErrors = True
            Err.Clear
        End If
    End If
End Sub

'******************************************************************************
' Sub:      MapPrinters
' Purpose:  Map network printers to the local machine for the current user.
'           Possibly set one of the new printer mappings as the default printer.
' Input:    
' Output:
' Usage:    Call MapPrinters
'******************************************************************************
Sub MapPrinters
    On Error Resume Next

    Dim blnForce                'Force printer mappings/unmappings?
    Dim dictValidPrinters       'Dictionary of printers which are valid for the current user
    Dim arrPrintServers         'The names of all current and former print servers
    Dim objShApp                'Shell Application object
    Dim objFolder               'Object referring to a special system folder
    Dim objItem                 'Object referring to an item inside a special system folder
    Dim arrWords                'Store the words making up
    Dim arrServer               'A filtered result, empty if no server, otherwise first element
                                'is the server name
    Dim strPrinter              'The name of a shared Printer
                                
    'Force printer unmapping
    blnForce = True

    'Get a dictionary of valid printer mappings for the current user
    Set dictValidPrinters = MyNetworkPrinters()
    
    'Get a list of all the Print Servers
    arrPrintServers = ListOfPrintServers
    
    'Get the special folder PRINTERS_AND_FAXES
    Set objShApp = CreateObject("Shell.Application")
    Set objFolder = objShApp.Namespace(PRINTERS_AND_FAXES)
    
    'For every entry in the PRINTERS_AND_FAXES special folder
    For Each objItem In objFolder.Items
    
        'Only deal with those items whose name ends in a valid print server name
        arrWords = Split(objItem.Name, " ")
        arrServer = Filter(arrPrintServers, arrWords(UBound(arrWords)))
        If UBound(arrServer) <> -1 Then
            'See if the printer is in the valid printer list
            If MemberOf(dictValidPrinters, arrWords(0)) Then
                'See if the server matches the server item in the valid printer list
                'If so, then remove the item from the dictionary since the printer
                'is already mapped correctly
                If arrWords(UBound(arrWords)) = dictValidPrinters.Item(arrWords(0)) Then
                    dictValidPrinters.Remove(arrWords(0))
                Else
                    'The server name isn't correct for the printer mapping
                    'so remove the printer mapping
                    UnMapPrinter objItem.Name, blnForce                    
                End If
            Else
                'The printer isn't in the valid printer list
                'so remove the printer mapping
                UnMapPrinter objItem.Name, blnForce
            End If
        End If
    Next 

    'NOTE: At this point all invalid printer mappings are deleted and
    'some valid printer mappings may exist, but if there is anything
    'left in the valid printer dictionary, then we need to create
    'new printer mappings for each of those items
    
    'For every item remaining in the valid printer mappings list
    For Each strPrinter In dictValidPrinters.Keys
        'Map the printer
        MapPrinter "\\" & dictValidPrinters.Item(strPrinter) & "\" & strPrinter, False 
    Next
End Sub

'******************************************************************************
' Sub:      RestorePreferences
' Purpose:  Restore system to account for user's printer preferences saved in
'           the registry.
' Input:    
' Output:
' Usage:    Call RestorePreferences
'******************************************************************************
Private Sub RestorePreferences
    On Error Resume Next
    
    Dim strPrinter
    
    'Get default printer preference from registry and set default printer
    strPrinter = gobjWshShell.RegRead(DEFPRNKEY)
    If Not (IsNull(strPrinter) Or IsEmpty(strPrinter)) Then
        Err.Clear
        gobjWshNetwork.SetDefaultPrinter strPrinter
        If Err.number Then
            WScript.StdErr.WriteLine "Unable restore default printer from preference settings."
            WScript.StdErr.WriteLine Err.Description
            gblnHadErrors = True
            Err.Clear
        End If
    End If
End Sub

'******************************************************************************
' Sub:      MapPrinter
' Purpose:  Map a network printer to the local machine for the current user.
'           Possibly set the new printer mapping as the default printer.
' Input:    strPrinterName: a string containing the network name for the printer.
'           blnSetAsDefault: a boolean indicating if this should be the default
'           printer.  At most only one call to MapPrinter should have 
'           blnSetAsDefault set to true.
' Output:
' Usage:    Call MapPrinter( "NameString", BooleanValue )
'           (for example: MapPrinter "\\TestServer\Printer1", false)
'******************************************************************************
Private Sub MapPrinter( strPrinterName, blnSetAsDefault )
    On Error Resume Next

    'Add mapping to the printer
    gobjWshNetwork.AddWindowsPrinterConnection strPrinterName
    If Err.number Then
        WScript.StdErr.WriteLine "ERROR: Failed to map printer: " & strPrinterName
        WScript.StdErr.WriteLine Err.Description
        gblnHadErrors = True
        Err.Clear
    End If

    'Set default printer
    If blnSetAsDefault then
        gobjWshNetwork.SetDefaultPrinter strPrinterName
        If Err.number Then 
            WScript.StdErr.WriteLine "ERROR: Failed to set printer: " & strPrinterName & _
                " as the default printer."
            WScript.StdErr.WriteLine "You will need to reset your default printer by hand."
            WScript.StdErr.WriteLine Err.Description
            gblnHadErrors = True
            Err.Clear
        End If
    End If
End Sub

'******************************************************************************
' Sub:      UnMapPrinter
' Purpose:  Unmap a printer.
' Input:    strPrinterName: The name of the printer to disconnect
'           blnForce: True to Force disconnect, False otherwise
' Output:
' Usage:    Call UnMapPrinter( "PrinterName" , False )
'******************************************************************************
Sub UnMapPrinter( strPrinterName, blnForce )
    On Error Resume Next

    gobjWshNetwork.RemovePrinterConnection strPrinterName, blnForce, True
    If Err.number Then
        Wscript.StdErr.WriteLine "Unable to unmap printer."
        Wscript.StdErr.WriteLine strPrinterName
        Wscript.StdErr.WriteLine Err.Description
        gblnHadErrors = True
        Err.Clear
    End If
End Sub

'******************************************************************************
' Sub:      MyNetworkPrinters
' Purpose:  Generate a dictionary of network printers that are available to
'           the current user.  The dictionary key will be the Printer Name
'           and the value will be the associated Print Server
' Input:    none
' Output:   Dictionary: Entry pair is Printer=PrintServer
' Usage:    Call MyNetworkPrinters()
'           For example: If MemberOf MyNetworkPrinters, "Rembrandt" Then
'******************************************************************************
Function MyNetworkPrinters()
    On Error Resume Next
    
    Dim strDomainName
    Dim strUserName
    Dim dictUserMembership
    Dim strServer
    Dim strPrinterOU
    Dim objPrinterOU
    Dim objPrinter
    Dim strPrinter
    Dim dictPrinterMembers
    Dim strGroup
    
    'Set some handy variables
    strDomainName = gobjWshNetwork.UserDomain
    strUserName = gobjWshNetwork.UserName
    
    'Create a dictionary of the users group membership
    Set dictUserMembership = GetMemberDictionary(strDomainName, strUserName, True)
    
    'Create a dictionary object for the results
    Set MyNetworkPrinters = CreateObject("Scripting.Dictionary")
    MyNetworkPrinters.CompareMode = vbTextCompare
    
    'Get a list of all the former and active Print Servers
    'For every Print Server
    For Each strServer In ListOfPrintServers
        'Get a list of all its Printers
        strPrinterOU = PRINTERSOU
        strPrinterOU = Replace(strPrinterOU, "ou=Printers", "ou=" & strServer & ",ou=Printers")
        Set objPrinterOU = GetObject(strPrinterOU)
        objPrinterOU.Filter = Array("Group")

        'For each Printer
        For Each objPrinter In objPrinterOU
            strPrinter = objPrinter.CN
            'Put together a dictionary of groups and users that are members of the printer
            Set dictPrinterMembers = GetMemberDictionary(strDomainName, strPrinter, False)
            'If the current user is a member of the printer then add the printer to the dictionary
            If MemberOf(dictPrinterMembers, strUserName) Then
                MyNetworkPrinters.Add strPrinter, strServer
            Else
                'If the current user is a member of a group that is a member of the printer
                'then add the printer to the dictionary
                For Each strGroup in dictUserMembership.Keys
                    If MemberOf(dictPrinterMembers, strGroup) Then
                        MyNetworkPrinters.Add strPrinter, strServer
                    End If
                Next
            End If
        Next    
    Next
End Function

'******************************************************************************
' Sub:      ListOfPrintServers
' Purpose:  Return an array containing the names of the network print servers
'           (List includes both current and former print server names)
' Input:    
' Output:   Array of strings.
' Usage:    Call ListPrintServers()
'           For example: For Each strServer In ListOfPrintServers
'******************************************************************************
Function ListOfPrintServers()
    On Error Resume Next
    
    Const ADS_SCOPE_SUBTREE = 1
    
    Dim objConnection
    Dim objCommand
    Dim objRecordSet
    Dim arrResult()
    Dim intIterator

    Set objConnection = CreateObject("ADODB.Connection")
    Set objCommand =   CreateObject("ADODB.Command")
    objConnection.Provider = "ADsDSOObject"
    objConnection.Open "Active Directory Provider"
    Set objCommand.ActiveConnection = objConnection

    objCommand.Properties("Page Size") = 100
    objCommand.Properties("Searchscope") = ADS_SCOPE_SUBTREE 

    objCommand.CommandText = _
        "SELECT name FROM '" & PRINTERSOU & "' WHERE " & _
            "objectCategory='organizationalUnit'"  
    Set objRecordSet = objCommand.Execute

    intIterator = 0
    ReDim arrResult(intIterator)
    objRecordSet.MoveFirst
    
    Do Until objRecordSet.EOF
        arrResult(intIterator) = objRecordSet.Fields("name")
        objRecordSet.MoveNext
        intIterator = intIterator + 1
        ReDim Preserve arrResult(intIterator)
    Loop
    
    ReDim Preserve arrResult(intIterator - 1)
    ListOfPrintServers = arrResult
End Function

'******************************************************************************
' Sub:      GetMemberDictionary
' Purpose:  Given a domain name and a username (or group name), returns a 
'           Dictionary of groups the user (or group) has membership in.
' Input:    strDomain: AD Domain name
'           strObjectName: AD object (must be name of a user or group object)
'           blnIsUserObject: True if strObjectName is a User object
'                            False if strObjectName is a Group object
' Output:   Returns a dictionary object
' Usage:    Call CreateMemberOfObject( "Domain", "UserName" )
'******************************************************************************
Function GetMemberDictionary(strDomain, strObjectName, blnIsUserObject)
    On Error Resume Next
    
    Dim objADObject, objGroup

    Set GetMemberDictionary = CreateObject("Scripting.Dictionary")
    GetMemberDictionary.CompareMode = vbTextCompare
    If blnIsUserObject Then
        Set objADObject = GetObject("WinNT://" _
            & strDomain & "/" _
            & strObjectName & ",user")
        For Each objGroup In objADObject.Groups
            GetMemberDictionary.Add objGroup.Name, "-"
        Next
    Else
        Set objADObject = GetObject("WinNT://" _
            & strDomain & "/" _
            & strObjectName & ",group")
        For Each objGroup In objADObject.Members
            GetMemberDictionary.Add objGroup.Name, "-"
        Next
    End If
    
    Set objADObject = Nothing
End Function

'******************************************************************************
' Sub:      MemberOf
' Purpose:  Given a Dictionary object containing groups to which the user/group
'           is a member and a group name, then returns True if the group
'           is in the Dictionary else return False.
' Input:    objDict: Name of a Dictionary object
'           strKey: Value being searched for in the Dictionary object
' Output:   Boolean value: True if user/group is a member, False otherwise
' Usage:    Call MemberOf( ObjDict, "GroupName" )
'           For example: If MemberOf gobjGroupDict, "Domain Admins" Then
'******************************************************************************
Function MemberOf(objDict, strKey)
    On Error Resume Next
    
    MemberOf = CBool(objDict.Exists(strKey))
End Function

'******************************************************************************
' Sub:      CleanUp
' Purpose:  Release common objects and exit script
' Input:
' Output:
' Usage:    Call CleanUp
'******************************************************************************
Sub CleanUp
    On Error Resume Next

    Dim intCode

    'Calculate exit code
    intCode = 0
    If gblnHadErrors Then
        intCode = intCode + 1
    End If
    
    ' Free script objects
    Set gobjWshNetwork  = Nothing
    Set gobjWshShell    = Nothing

    ' Exit script
    Wscript.Quit(intCode)
End Sub

Technorati Tags: logon,login,script,vbscript