Cindy Gross | Befriending Dragons

Category: Career

  • SQL PowerShell Demo – Intro

    SQL PowerShell Demo – Intro

    Cindy Gross, Dedicated Support Engineer  

    Audience: SQL Server DBAs and/or developers who are familiar with basic SQL Server commands, SQL Server Management Studio (SSMS), and SQL Agent jobs.

    0.     Prerequisites

    1.     Install Basics

    • Check what version of PowerShell you currently have on your desktop.
      • Run PowerShell.exe with the “run as administrator” option
      • Get the version by executing the following in the PowerShell window:

    # Get the version

    $host.version

    2.     Hints

    • To paste into a PowerShell window (or a dos-prompt), copy your source then right click in the PowerShell window to paste. If you paste something that has a bullet point you will need to delete the o: or hidden character/space at the beginning before you hit enter.
    • To copy from a PowerShell window (or a dos-prompt), right click in the menu bar at the top. Choose edit.mark and highlight a “box” of data. You’re basically drawing from one corner to another, not picking individual words. Then without clicking anywhere else within the window, right click on the menu bar again and choose edit.copy. Then paste wherever you need to.
    • The pound sign (#) indicates a comment. You can cut/paste the demo steps including the lines with #. However, be careful about pasting an entire section at once, it will execute them all and the results may scroll off the screen.
    • For my SQL Server connections in this demo, I have used localhost to indicate the default instance on my local box. If you want to connect to a named instance, to an instance on another box, or to any clustered instance, replace localhost with the instance name (i.e. SERENITYHOMEInstance 1 or SomeRemoteSQL or localhostInstance1). Note that localhost is equivalent to a period (.) or (local).
    • Within SQLPS even a default instance needs an “instance” name specified and for a default instance the name is DEFAULT. For example this is the default instance on my local box: SQLSERVER:SQLlocalhostDEFAULTDatabasesAdventureWorks2008R2TablesPurchasing.Vendor
    • To change the dimensions of the PowerShell window (current and future), right click in the menu bar and choose properties. Here you can change the height, width, font, etc.
    • Many basic dos commands still work, such as cls to clear the screen.

    3.     Explore Basic PowerShell Functionality

    • Run PowerShell.exe from the start menu with the “run as administrator” option. PowerShell should be in your path, but if you need to find the source location, look in a path similar to C:WindowsSystem32WindowsPowerShellv1.0.
    • Check your security level. I have put the # sign (comment) on the lines that are not PowerShell commands so that you can cut/paste the entire section into PowerShell or a script. See the hints section on how to cut/paste.

    # Run this command to see what your security level is:

    Get-ExecutionPolicy

    # For information about execution policies:

    get-help about_execution_policies

    # If the execution policy is restricted then change it to unrestricted, RemoteSigned, or whatever level

    # is most appropriate http://technet.microsoft.com/en-us/library/ee176961.aspx

    # Example of changing the execution policy: 

    Set-ExecutionPolicy unrestricted

    • In the PowerShell window run these commands and review the output. This is just a small sampling of the information available via Windows cmdlets.

    # List of available commands and info about each:

    get-help

    # “Dir” is an alias for get-childitem so we see the help info for get-children when we pass in dir:

    get-help dir

    # “Ls” is also an alias for get-childitem:

    get-help ls

    # The “full” option includes examples, parameters, etc.:

    get-help dir –full

    # Returns one row per running instance of sqlservr.exe:

    get-process sqlservr

    # Returns one row per running instance of sqlservr.exe with different output:

    get-process sqlservr -fileversioninfo

    # one row per service with SQL in the name:

    get-service *sql*

    # system path from environment variables/set:

    $env:path

    # All environment variables that start with proc:

    Dir env:proc*

    # List of all PowerShell “drives” including basic regkey hives:

    Get-psdrive

    # Look at a registry key, in this case we can see which SQL instances are installed:

    # Note that you can leave out HKEY_LOCAL_MACHINE since that is covered by the switch to hklm

    # It appears that the HKEY_LOCAL_MACHINE part of the name is case sensitive

    #32bit instances on an x64 box have keys in the Wow6432Node key

    CD hklm:
    dir “HKEY_LOCAL_MACHINESOFTWAREMicrosoftMicrosoft SQL ServerInstance Names”

    dir “HKEY_LOCAL_MACHINESOFTWAREWow6432NodeMicrosoftMicrosoft SQL ServerInstance Names”

    4.     Formatting

    # Look at some of the output format options

    # Switch to the registry

    CD hklm:

    dir “HKEY_LOCAL_MACHINESOFTWAREMicrosoftMicrosoft SQL ServerInstance Names” | format-list

    dir “HKEY_LOCAL_MACHINESOFTWAREMicrosoftMicrosoft SQL ServerInstance Names” | format-list –property name, property

    dir “HKEY_LOCAL_MACHINESOFTWAREMicrosoftMicrosoft SQL ServerInstance Names” | format-list –property *

    5.     Run a PowerShell Script

    # Change to the directory where the PowerShell script resides

    cd c:installsPowerShelltest

    # Specify the current directory with a period (.) and call the PowerShell script

    .getsysproperties.ps1

    • You can also run the PowerShell script from a dos-prompt
      • From start, execute cmd with “execute as administrator”. # is not a valid comment indicator in dos, so use REM instead.

    REM Execute the script

    cd c:installsPowerShelltest

    PowerShell.exe -noexit .getsysproperties.ps1

    • (Optional) Save one or more of the earlier PowerShell cmdlet calls in a .ps1 file and test executing them.
    • Close your PowerShell window and CMD window.

    6.     Run a SQL PowerShell cmdlet

    • On a box with the SQL 2008 client tools installed, run sqlps.exe with the “run as administrator” option which opens a PowerShell mini-shell window (it will look like a normal dos-prompt but have “PS SQLSERVER:>” at the prompt). Run these commands and explore the output (where you see localhost you can enter your own SQL Server serverinstance name)

    # Look at the syntax and options for the cmdlet that lets you execute any SQL query

    Get-help invoke-sqlcmd

    # Connect to SQL and return the version information

    invoke-sqlcmd -ServerInstance localhost -Query “select @@version”

    # The output was cut off, let’s try a different format

    invoke-sqlcmd -ServerInstance localhost -Query “select @@version” | format-list

    # Close the sqlps window

    • From SSMS 2008+, right click on any SQL Server instance name and choose “start PowerShell”. You will see a dos-prompt open with something like PS SQLSERVER:SQLSERENITYHOMEDEFAULT (my instance =is a default instance on the server SERENITYHOME). That line shows the context, I no longer have to specify the instance name for invoke-sqlcmd. I do still have to specify a database if I want a context other than my default database.

    # No instance name is specified, and it puts me in the default database for my Windows auth login

    invoke-sqlcmd -Query “select @@version, db_name()”

    # I can specify a specific database as a parameter

    invoke-sqlcmd -Query “select @@version, db_name()” –database tempdb

    # Close the “SQL Server PowerShell” window

    • From SSMS 2008+, right click on any SQL database or SQL database object and choose “start PowerShell”. When I do this for AdventureWorks2008.HumanResources.Department I see a dos-prompt open with PS SQLSERVER:SQLSERENITYHOMEDEFAULTDatabasesAdventureWorks2008TablesHumanResources.Department>). That’s the context, I no longer have to specify the instance name or database name for invoke-sqlcmd. I can then change to another database and/or another SQL Server (in this case I switched to the Kaylee instance and the AdventureWorks database).

    # The database context is the database I clicked on in SSMS

    invoke-sqlcmd -Query “select @@servername, db_name()”

    # We can change to another db (if you do not have AdventureWorks, choose another database)

    cd SQLSERVER:sqlserenityhomekayleedatabasesAdventureWorks

    # Now look at the current database context

     invoke-sqlcmd “select @@servername, db_name()”

    # Close the “SQL Server PowerShell” window

    7.     Execute from a SQL Agent Job

    • In SSMS, go to SQL Server Agent.Jobs and right click to create a new job called Test PS.
    • On the Steps page create a new job step and set the “type” = “PowerShell”.
    • I used “Run as” = “SQL Server Agent Service Account”, make sure whatever account you use has sufficient permissions.
    • Command = invoke-sqlcmd -serverinstance localhost -database TempDB -query “select getdate() AS RunTime, @@servername AS ServerInstance, db_name() AS DBName” > c:temppstest.txt
    • Note that if you don’t specify the ServerInstance parameter it will try to connect to the local default instance even if you run from a job on a named instance. The above command will connect to the local default instance, change it to another instance if needed.
    • Save and run the job.
    • Run notepad c:temppstest.txt and see that it outputs the expected ServerInstance and DBName.
    • Note that each job step opens its own sqlps.exe which takes about 20-40MB of memory.

    8.     SQL from Windows PowerShell

    • Close any existing PowerShell windows.
    • Run PowerShell.exe.
    • Try to switch to the SQL mini-shell and it will fail

    # Change to the SQL mini-shell

    Cd sqlserver:

    • In PowerShell, run sqlps to load the SQL provider and cmdlets.

    # Load SQLPS

    sqlps

    • Try to switch to the SQL mini-shell and it now works

    # Change to the SQL mini-shell

    Cd sqlserver:

    • You can now do things like get-help invoke-sqlcmd because loading sqlps loaded the SQL cmdlets.

    # Test a SQL cmdlet

    invoke-sqlcmd -ServerInstance localhost -Query “select @@version”

    • BOL topic “Running SQL Server PowerShell” has other ways to load the SQL provider/snap-ins and SMO to Windows PowerShell.

    9.     Run a SQL PowerShell Script

    Open notepad and create c:tempTestSQL.ps1 (make sure .txt is not added to the end). Paste in this text:

    # get a list of instances on this server

    cd hklm:

    dir “HKEY_LOCAL_MACHINESOFTWAREMicrosoftMicrosoft SQL ServerInstance Names”

    # see what we can admin

    cd sqlserver:

    ls

    # see what the machine name is

    cd sql

    ls

    # issue a query

    invoke-sqlcmd -ServerInstance localhost -query “SELECT top 5 name FROM sys.databases ORDER BY name”

    • In Windows PowerShell load the SQL cmdlets and then execute the SQL PowerShell script

    # Load the SQL cmdlets

    Sqlps

    # Change to the C:temp folder

    Cd c:temp

    # Execute the PowerShell script

    .testsql.ps1

    • By default the format is slightly different for some of the output when executed from a script.

    10.     Cleanup

    • Close the PowerShell window.
    • Delete c:temptestsql.ps1 and c:temppstest.txt
    • Delete the SQL Agent job you created.

    My other PowerShell blogs:

    Powershell for SQL – Getting Started http://blogs.msdn.com/b/cindygross/archive/2011/09/23/powershell-for-sql-getting-started.aspx

    PowerShell for SQL Server – Basics http://blogs.msdn.com/b/cindygross/archive/2011/09/23/powershell-for-sql-server-basics.aspx

  • PowerShell for SQL Server – Basics

    PowerShell for SQL Server – Basics

    Cindy Gross, Dedicated Support Engineer

     Audience: SQL DBAs and/or developers who are familiar with basic SQL Server commands, SQL Server Management Studio (SSMS), and SQL Agent jobs.

    Basics

    • PowerShell processes objects based on .NET.
    • Cmdlets are named in a Verb-Noun format such as Start-Service and Get-Help.
    • For help on any Cmdlet, use: get-help <cmdlet-name> -detailed.
    • Scripting is good for things like automating system administration tasks. Scripting languages include VBScript, CScript, and PowerShell.
    • Basic script editing can be done in notepad.exe.
    • SQL08+ installs sqlps.exe which is SQL’s PowerShell interface.
      • If you run sqlps.exe on its own it is a “mini-shell” with all the SQL functionality but not all of the Windows functionality.
      • If you open Windows PowerShell (PowerShell.exe) and then run sqlps to load the SQL provider and cmdlets you have both SQL and Windows functionality.
    • For the most part scripting is NOT case sensitive (though I found one registry key that is case sensitive – HKLM).
    • PowerShell scripts have the .ps1 suffix and you always specify the full path (or . for the current dir) when you call the script.

    SQL

    • You must use the SQL08+ components to use PowerShell, but you can connect to SQL 2005 SP2+ and SQL 2000 SP4+ though the functionality may be limited.
    • Sqlps.exe and SSMS (right click, start PowerShell) start PowerShell with the SQL Server PowerShell provider and cmdlets loaded.
    • Within Windows PowerShell you can run sqlps to load the SQL cmdlets into Windows PowerShell.
    • The main cmdlet for SQL is Invoke-SQLCmd which lets you run any SQL query. The other cmdlets in SQL Server 2008/2008R2 are invoke-PolicyEvaluation (PBM), Encode-SQLName/Decode-SQLName (format SQL identifiers), and Convert-UrnToPath (navigation). You can also load the SQL Server Management Object (SMO) DLLs for additional functionality.
    • Read gettingStarted.rtf in the “Windows PowerShell 1.0 Documentation Pack” and complete the demo steps.

    Learning

    Syntax

    • Compare with –eq –ne –gt –like
    • $_ is the current object
    • The main difference between the script hosts cscript and wscript (the default) is that echo for cscript is to the command window and echo for wscript is to a message box.
    • If a path has a space you need to add & “…”. For example: & “C:tempmy PowerShellmytest.ps1”.
    • The current path is . as in .mytest.ps1

    References

    SQL Powershell

    Windows Powershell

    Downloads

    My other PowerShell blogs:

    Powershell for SQL – Getting Started http://blogs.msdn.com/b/cindygross/archive/2011/09/23/powershell-for-sql-getting-started.aspx

    SQL PowerShell Demo – Intro http://blogs.msdn.com/b/cindygross/archive/2011/09/23/sql-powershell-demo-intro.aspx

  • PowerShell for SQL – Getting Started

    PowerShell for SQL – Getting Started

    Cindy Gross, Dedicated Support Engineer

    You may want to use PowerShell to automate SQL activities such as backups. There are various ways to automate backups. For example, you can centralize the backup calls on an admin server which has some type of list of instances and databases and possibly varying schedules/types of backups for each. Another method is to use an admin server to push backup jobs to newly installed SQL instances. A variation of the 2nd is to periodically scan for new databases and/or databases without backups and/or databases that don’t meet some standard backup schedule and push out backup jobs. I’m not sure how automated or complex your backup plans are now. I’ve seen a wide range such as:

    • Manually schedule backups for each database as it is added (and hope you don’t miss one)
    • When you install the instance run a script to generate backup jobs for each database. Then periodically scan for new databases/databases not backed up and add backup jobs.
    • Schedule one backup job per instance to backup all local databases (either all on the same schedule or read a table to find the type/interval for each database on that instance)
    • Use single server or multi-server maintenance plans (not something I personally recommend in an enterprise – http://sqlserverpedia.com/blog/sql-server-management/maintenance-plans-vs-t-sql-scripts/)
    • Use a central scheduling system (master-target / multiserver administration via SQL jobs, UCP, 3rd party tools, etc.) with varying levels of complexity
    • Use a central Central Management Server (CMS) and/or a custom central admin databases to track which databases need which backup schedules and generate/check appropriate jobs

    My next two blogs will have some details about the first steps with Powershell. Most of the SQL related operations you can do from Powershell are done by calling SMO http://msdn.microsoft.com/en-us/library/ms162169.aspx. Allen White does training on SMO, and I’m sure you can find others who do so as well.

    Here are some backup samples that use PowerShell:

    The Enterprise Policy Management (EPM) Framework is a classic example of how to use the new SQL Server 2008 Central Management Server (CMS) to automate tasks across multiple servers. In this case it is automating Policy Based Management (PBM) with PowerShell but the concept could apply to many types of automation.  http://epmframework.codeplex.com/releases/view/28621  

    Next:

    PowerShell for SQL Server – Basics http://blogs.msdn.com/b/cindygross/archive/2011/09/23/powershell-for-sql-server-basics.aspx

    SQL PowerShell Demo – Intro http://blogs.msdn.com/b/cindygross/archive/2011/09/23/sql-powershell-demo-intro.aspx

  • Using Mount Points with SQL Server

    Using Mount Points with SQL Server

    Author: Cindy Gross, Dedicated Support Engineer

    Tech Reviewer: Min He, SQL Cluster PM

     

    Summary: Microsoft does NOT support installing or putting databases on the “root” of a mount point in SQL Server 2005, 2008, 2008 R2 unless you manually manage the ACLs/permissions yourself (modified 1/13/12).

     The key points for mount points in combination with SQL Server 2005, 2008, 2008 R2 are:

    • A valid mount point that can be used by SQL Server databases is one that is mounted to a host volume (a shared drive in a cluster) that is associated with a drive letter. Multiple mount points can be hosted by a single drive meaning multiple mount points share a drive letter.
    • In a cluster, SQL Server must depend on each mount point it uses to avoid database corruption.
    • Do not put a SQL Server 2000 instance in a cluster where mount points exist, whether or not SQL Server 2000 is expected to use them (which it cannot). Do not add mount points to a cluster where SQL 2000 is already installed or future SQL Server 2000 patches will break.
    • Do not install SQL Server to the root directory of a mount point, always specify a subdirectory for all files. This has to do with how permissions are granted. If you must put files in the root of the mount point you must manually manage the ACLs/permissions.
    • Do not put DTC on a mount point.

    For various reasons such as standardization, flexibility, space management, and just not enough letters in the alphabet many people use mount points on their servers. A mount point (aka mounted drive or volume junction) is a separate file system that is “mounted” onto a host drive so that it appears to be a subdirectory of the host drive. For example, say you have LUN volume A that is made visible to Windows as drive X:. You have a LUN volume B from another storage array and you want to present it to Windows. You might choose to mount it as X:SQL1. To SQL it looks like a subdirectory, but it’s really a whole different file system. Because it is a different file system, permissions are not inherited from the host system. So when you grant permissions to X: and say to propagate them to child folders, they are NOT applied to the mount point!

    For testing purposes, try mounting a USB drive to your client box as C:USBMountPoint. You can move files to C:USBMountPoint and they will appear on the USB drive. Disconnect the USB drive and attach it to another box and those files are available on the new system but NOT on the old C:USBMountPoint location. That’s because the files aren’t really on C:, they’re on the USB drive that was mounted to C: for a while.

    Example of creating a mount point on non-clustered disks:

    • Attach a USB drive formatted to NTFS
      • In my example it showed up as G:
      • It has a volume name of CGROSSFLASH
      • There is a file called BeforeMount.txt on the drive
    • Open “Disk Management” (Diskmgmt.msc)
    • Right click on CGROSSFLASH and choose “Change Drive Letter and Paths ”
    • Click “Remove” for G: then “Yes”
    • Right click on CGROSSFLASH  and choose “Change Drive Letter and Paths ”
    • Choose “Add” then “Mount in the following empty NTFS folder”
    • Click the “New Folder” button and give the directory a name, I’ll use USBMountPoint
    • Click “OK”
    • C:USBMountPoint is the root of my mount point. C: is the root of the host. You can see test.txt in C:USBMountPoint.
    • Try creating various files and directories in C:USBMountPoint.
    • Now remove the mount point then re-add it but create new folders first. You will end up mounting C:USBMountPointLevel2Level3. The files you created before now show up in the Level3 directory, not in the USBMountPoint directory.

     SQL Server 2000 and later standalone boxes and SQL Server 2005 and later clustered instances are supported on mount points mounted to a host volume (a shared drive in a cluster) that is associated with a drive letter. However, you must put all databases, including the system databases, on a subdirectory of the mount point, not in the root of the mount point, unless you manually manage the ACLs/permissions. The root of a mount point is the entire directory as defined when you mount it. For example, say I mount d:SQL1. That is the root of the mount point and I cannot install to it. If I mount d:SQL1ALL_DATA that is the root and I cannot install to d:SQLALL_DATA. I could install SQL to a subdirectory/subfolder such as d:SQL1ALL_DATADATA. If you install SQL to the root of a mount point setup makes the assumption that permissions are propogated up from the host root (d: in this example). However, that is not how Windows works with mount points. When you create a subdirectory setup creates permissions explicitly and therefore setup works when you install to a subdirectory of the mount point. The same logic applies to adding new databases or moving existing databases, they should not be in the root of a mount point.

    In addition to putting the databases in subdirectories, you also have to make SQL depend on each individual mount point. When you make SQL depend on a mount point you force that mount point to come online before SQL does and therefore prevent certain types of potential corruption.

    It is becoming more and more common in Windows 2008 and later clusters to have DTC depend on one of the lesser used/less important SQL Server drives. However, if you are using mount points for your SQL files you cannot do this as currently DTC is not supported on mount points.

     Mount points are a very useful tool. As long as you follow the rules for SQL Server you can increase your flexibility, spread your IO out over more IO paths, and/or add space to the system from multiple storage system with mount points.

     References:

  • Invite a guest in, or not?

    The guest user in SQL Server is a bit of a funny object. While we’re taught to be polite to guests, sometimes we don’t want to invite them into our databases.

    If you have a login to an instance of SQL Server that doesn’t automatically give you access to any databases…. or does it? By default guest has been “enabled” by GRANTing CONNECT to guest in master, msdb, and tempdb. While guest exists in each database, unless you enable guest in the model database guest will not be enabled by default for user databases. If you do enable guest it, like every other user, is a member of the public and has some basic permissions in that database. This is mostly around metadata.

    Unfortunately this isn’t always clear in our current documents. There are some places in BOL where we say you can remove guest from any database other than master and tempdb and others where we say you can remove it from any user database. The real answer is that as of SQL Server 2008 R2 and earlier guest must be enabled in master, tempdb, AND msdb or some functionality will break. The details are documented in this new KB article: You should not disable the guest user in the msdb database in SQL Server http://support.microsoft.com/kb/2539091

    The below query shows you that each database has a user called guest but you can see that most (at least by default) do not have guest enabled/CONNECT permission granted to guest. Of course sp_msforeachdb is undocumented and unsupported with all the usual caveats. You may want to look at Bob Taylor’s SQL Iterators code on Codeplex, though Codeplex is still unsupported sample code.

    DECLARE @var1 varchar (1000);
    SELECT @var1 = ‘USE ?;SELECT ”=== ”+DB_NAME();
    SELECT name, type_desc, owning_principal_id
    FROM sys.database_principals
    WHERE name = ”guest”;
    SELECT prins.name AS grantee_name, perms.*
    FROM sys.database_permissions AS perms
    JOIN sys.database_principals AS prins
    ON perms.grantee_principal_id = prins.principal_id
    WHERE prins.name = ”guest” AND perms.permission_name = ”CONNECT”;’
    EXEC sp_msforeachdb @var1;

  • Taming the Tempdb Tempest – WI SQL Server Virtual User Group, 22 Apr 2011

    Thanks to the Wisconsin Virtual SQL Server User Group for letting me talk about tempdb today! The slides and demo queries are attached. Once the recording is available I will update this blog with a link to it.

    Taming the Tempdb Tempest

    Summary:

    ·         Multiple data files of the same size, one log file

    ·         Enough data files to avoid contention, not so many to cause problems.

    ·         Presize for peak periods of next X months, re-evaluate

    ·         Set autogrow to be rare but “big enough”

    ·         Instant File Initialization on (small security risk)

    ·         Fast IO subsystem

    ·         Change size/settings if you add new features that use tempdb

    ·         Monitor for approaching full, change in activity/size

    ·         Performance tune user databases and applications

    ·         Limit use of versioning or temp objects

     

    The demo queries are:

    ·         sys.dm_db_file_space_usage.sql: How space is used inside tempdb

    ·         sys.dm_db_file_space_usage_companion1.sql: Show how different activities cause space to be used in tempdb

    ·         Autogrow.sql: Find autogrow settings for all dbs on an instance

    ·         TempdbContention.sql: Find contention on tempdb metadata

     

    Also see my previous blog post with the same basic data in a different format: Compilation of SQL Server TempDB IO Best Practices http://blogs.msdn.com/b/cindygross/archive/2009/11/20/compilation-of-sql-server-tempdb-io-best-practices.aspx

    I also delivered the talk to the Boise SQL Server User Group on 13 Sep 2011 and the updated queries and slide deck are attached below.

    TempdbBoiseSQLSep2011.zip

  • Save the SQL error logs….

     

    Many people use sp_cycle_errorlog to rollover to a new SQL error log periodically. Please make sure this is necessary and/or that you are not doing it too frequently. The more often you roll over the log the greater the chance you will not have troubleshooting or historical information available, especially after a series of unexpected sequential restarts. If you are cycling the log, you probably want to keep more than the default number of 7 logs so you’re less likely to lose valuable troubleshooting information.

     

    2199578 How to manage the SQL Server errorlog

    http://support.microsoft.com/default.aspx?scid=kb;en-US;2199578

    Configure SQL Server Error Logs (General Page)

    http://msdn.microsoft.com/en-us/library/ms177285.aspx

    “In Object Explorer, expand your server, expand Management, right-click SQLServerLogs, and then click Configure.“

     

  • 24HOP: SQL Server Performance Tools

    Thanks to everyone who attended my session today! I think the 740 or so of you overloaded the audio. 🙂 Sorry everything wasn’t clear on the voice delivery, hopefully the slides (below) provide enough information for you to know what to download. Then you can load your own SQLDiag data into SQL Nexus and see all the stuff I showed in the demos! Remember that I was showing you the “near future” version 4.0 of SQL Nexus, what you can download today from CodePlex is version 3.0. I’ll tweet when the new version is released.

     

    There are still many sessions left in this spring 2011 24 Hours of PASS, attend as many as you can today and tomorrow! Look for the recordings to be available soon.

     

    My slides are below, the abstract is: SQL Server Performance Tools: Troubleshooting performance issues with SQL Server can be confusing and time consuming. I want to make sure you are familiar with some of the common tools used to help troubleshoot performance issues and when to use each. I will provide best practices around troubleshooting methodology, SQLDiag, PSSDiag, SQL Nexus, Profiler / SQL Trace, and PerfMon. After this presentation you will be better able to narrow down a performance problem and focus in on where to spend your time and effort.

    Please: Complete Session Evaluation

     

    Some of the key references:

     

    ·          SQL Server 2008 Internals and Troubleshooting – Chapter 8 Defining Your Approach to Troubleshooting

    ·          PerfStats – Download

    ·          SQL Nexus on Codeplex – Download

    ·          RML/Readtrace – Download

    ·          SQLDiag – Parameters/Usage

    ·          Waits and Queues – White Paper

    ·          New Opportunities to be a SQL Master – Changes to the program

    #24HOP

    PerformanceTools_24HOP_CindyGross_Spring2011.pptx

  • General Hardware/OS/Network Guidelines for a SQL Box

    I have put together some general guidelines for how you want a server to be delivered to the DBA team for a new SQL Server install. You won’t necessarily use all of them, but consider it a starting point for your SQL Server install standards. Places where it may be common to change the statements are in [red]. Always run the SQL Server Best Practices Analyzer or an equivalent tool to check for the latest recommendations before releasing the system to production. I’m sure some of you will disagree with some of the points for various reasons, but I’ve found them to be a good baseline for discussion and a great starting point for standards documents. I’m ok with that because I am very fond of saying “it depends”. 🙂

    The below is specific to SQL Server 2008/200R2 on Windows 2008/2008R2.

    OS Specifications (things often controlled by a Windows team)

    ·         Power saving features: For a SQL Server box if you want consistent, predictable, high performance you either need to fine tune the power setting parameters for each individual workload and/or for different times of day or just set the power options to high performance. Databases are harder to fit into the normal power saving profile so they don’t fit as well into the default power saving settings. [If your environment requires that you favor power savings over performance change this statement and be aware of the impact.]

    ·         You should double check that your virus scanner is certified for Windows 2008 R2. Older scanners use TDI and you need WFP models to work properly on the newer OSs. The older type of anti-virus scanners can cause serious IO problems.
    981889 A Windows Filtering Platform (WFP) driver hotfix rollup package is available for Windows Vista, Windows Server 2008, Windows 7, and Windows Server 2008 R2     http://support.microsoft.com/default.aspx?scid=kb;en-US;981889
    979278 Using two Windows Filtering Platform (WFP) drivers causes a computer to crash when the computer is running Windows Vista, Windows 7, or Windows Server 2008     http://support.microsoft.com/default.aspx?scid=kb;EN-US;979278
    979223 A nonpaged pool memory leak occurs when you use a WFP callout driver in Windows Vista, Windows 7, Windows Server 2008, or in Windows Server 2008 R2     http://support.microsoft.com/default.aspx?scid=kb;EN-US;979223
    976759 WFP drivers may cause a failure to disconnect the RDP connection to a multiprocessor computer that is running Windows Vista, Windows Server 2008, windows 7 or Windows Server 2008 R2     http://support.microsoft.com/default.aspx?scid=kb;EN-US;976759
    Windows Filtering Platform     http://www.microsoft.com/whdc/device/network/WFP.mspx

    ·         Virus scanners and spyware detection should not scan SQL Server data and log files (usually mdf/ldf/ndf extensions) and other SQL related files because the scanning significantly degrades performance. [Note that this is a tradeoff with security and you must decide on performance vs. security based on your own security guidelines.]
    REASON: Performance, smoother setup. See 309422 Guidelines for choosing antivirus software to run on the computers that are running SQL Server     http://support.microsoft.com/default.aspx?scid=kb;EN-US;309422

    ·         Firmware, BIOS, network adapter drivers, storport drivers, etc. will be at their most recent, stable versions before the server is released to the DBAs.
    REASON: There are common SQL Server performance, usability, and supportability problems caused by older firmware, BIOS, network adapter drivers, etc.

    ·         For Windows 2008 and Windows 2008 R2 you can download a Windows storport enhancement (packaged as a hotfix). This enhancement can lead to faster root cause analysis for slow IO issues. Once you apply this Windows hotfix you can use Event Tracing for Windows (ETW) via perfmon or xperf to capture more detailed IO information that you can share with your storage team.  http://blogs.msdn.com/b/cindygross/archive/2010/07/13/windows-storport-enhancement-to-help-troubleshoot-io-issues.aspx

    ·         Do not install SQL Server on a domain controller (DC).
    REASON:  A busy DC can take resources away from SQL Server. There are also negative security implications from installing SQL Server on a DC.

    ·         Grant SE_MANAGE_VOLUME_NAME to the SQL Server group to allow instant file initialization of data (but not log) files. There is a small security risk associated with this but it can greatly improve the performance of CREATE/ALTER data (but not log) files. [Decide as a company whether this performance enhancement is worth the small risk] http://blogs.msdn.com/b/sql_pfe_blog/archive/2009/12/23/how-and-why-to-enable-instant-file-initialization.aspx

    ·         Critical updates for Windows will be tested and applied ASAP after their release.
    REASON: Security that affects Windows often affects SQL Server as well.

    ·         Resource intensive screensavers will be disabled and replaced with low resource consumption security to lock the consoles.
    REASON: Performance – Resource intensive screen savers can steal resources from SQL Server.

    ·         Files will be secured: All copies of the data and log files as well as all copies of the backup files will be secured with access given only to those documented in the SQL Server Disaster Recovery plan.
    REASON: Data and log files can be copied and attached to another instance of SQL Server, thereby exposing the information to the sysadmins of the new instance. Therefore access to these files must be very limited. However enough access must be granted to allow for recovery.

    ·         EFS: SQL Server will not be installed on disk that is encrypted with EFS.
    REASON: 922121 You may experience decreased performance in some features of SQL Server 2005 when you use EFS to encrypt database files     http://support.microsoft.com/default.aspx?scid=kb;EN-US;922121

    Storage Specifications (often configured by a Windows and/or storage team)

    ·         Battery backup must be enabled for all controllers or storage media which do write caching.
    REASON: This is required by the WAL protocol to ensure stable media for SQL Server. See     http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqliobasics.mspx#EYCAE

    ·         For SQL Server disks, performance is more important than conserving space. This means there may be what would be considered “wasted space” on a file server and that the overall cost per stored MB will be higher for a database system than for a file server. [This is a general guideline, if your environment prefers costs savings and space usage maximization over performance change this statement.]
    REASON: High performance is generally a major requirement of a database system, and is much more important than on most file systems. Higher performance requires that disk be laid out, configured, and managed in particular ways.

    • Disk alignment must be done to a multiple of 64KB. Some vendors may express a preference for a particular value, but most mainstream hardware vendors have agreed that 1024KB is acceptable. That is the default for Windows 2008+. If you use dynamic disks it is difficult to see the alignment from Windows.
      REASON: If the disk is not aligned, performance can suffer as much as 30-40% because some read/write activity may be to/from two blocks instead of one. See 929491 Disk performance may be slower than expected when you use multiple disks in Windows Server 2003, in Windows XP, and in Windows 2000 http://support.microsoft.com/default.aspx?scid=kb;EN-US;929491 and Disk Partition Alignment Best Practices for SQL Server http://msdn.microsoft.com/en-us/library/dd758814.aspx
    • Disk allocation unit should be 64KB for SQL Server boxes.
      REASON: See       Predeployment I/O Best Practices http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/pdpliobp.mspx

    NTFS Allocation Unit Size

    When formatting the partition that will be used for SQL Server data files, it is recommended that you use a 64-KB allocation unit size for data, logs, and tempdb. Be aware however, that using allocation unit sizes greater than 4 KB results in the inability to use NTFS compression on the volume. SQL Server, although it is not recommended that you use this, does support read-only data on compressed volumes.

    ·         Drive Compression: Drives will not be compressed.
    REASON: Compression has a big negative performance impact on SQL Server.

    ·         NTFS file system will be used instead of FAT or Raw partitions.
    REASON: NTFS allows features such as database snapshots, online DBCC checks, instant file initialization, mount points, and additional security. It has larger file size limits (16 exabytes) than FAT (4 GBs). Raw partitions limit your recoverability options.

    ·         Often you will need one or more of these to achieve optimal performance for a database [Decide which of these you will deploy for each tier of storage and whether each can be requested by a DBA at server configuration time.]

    1.       HBA queue depth for SQL Server is often best at 64 or 128; testing will determine the optimal value.
    REASON: See Predeployment I/O Best Practices http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/pdpliobp.mspx
    HBA Queue Depth Settings

    When configuring HBAs on the host, ensure that the Queue Depth is set to an optimal value. SQL Server applications are generally I/O-intensive, with many concurrent outstanding I/O requests. As a result, the default values for Queue Depth on HBAs are usually not high enough to support optimal performance. Currently the default value for the major HBA vendors is in the range of 8 to 32.

    In our SQL Server testing, we have seen substantial gains in I/O performance when increasing this to 64 or even higher. It is worth noting that in these tests SQL Server was usually the only application using the storage array. It is important to discuss with your storage administrator the appropriate values for this setting, as the setting may affect other applications in sharing the same storage environment. When Queue Depth is set too low, a common symptom is increasing latency and less-than-expected throughput given the bandwidth between host/storage and the number of spindles in a particular configuration.

    2.       RAID 10 or its equivalent will be used for the highest performance and best recoverability. Read-only data (no updates from users, replication, batch jobs, or anything else) can see acceptable performance on RAID 5. RAID 5 systems will have slower write performance and less recoverability but might be allowed for lower tiered systems with a signoff that high performance is not guaranteed.
    REASON: RAID 10 is the fastest disk for SQL Server data and logs. It also provides the best recoverability options.

    o   See Physical Database Storage Design http://www.microsoft.com/technet/prodtechnol/sql/2005/physdbstor.mspx
    “For excellent performance and high reliability of both read and write data patterns, use RAID10.”

    o   “RAID10 (stripe of mirrors): RAID10 is essentially many sets of RAID1 or mirrored drives in a RAID0 configuration. This configuration combines the best attributes of striping and mirroring: high performance and good fault tolerance. For these reasons, we recommend using this RAID level. However, the high performance and reliability level is the trade-off for storage capacity.”

    o   RAID 10 is recommended for “Data requiring high performance for both read and write and excellent reliability while trading off storage efficiency and cost.“

    3.       Follow hardware vendor recommendations for configuring the storage for a database, often this is very different than configuring for other non-database systems.

    4.       Keep the physical disks no more than about 80% full (avoid full stroking, get closer to short stroking). Some SAN configurations may make this difficult to determine from Windows with concepts such as thin provisioning.

    5.       Use multiple HBA controllers with a high throughput capacity. The same applies for other components such as switch ports, NICs, Fibre Channel array ports, storage array service processors, etc.

    6.       Favor (battery backed) write cache over read cache for an OLTP system. Often 80/20 or 90/10 in favor of writes is beneficial. It is relatively easy for a busy SQL Server to flood the cache.

    1.       Log writes have the lowest allowable latency of any SQL activity on an OLTP system.

    2.       Write cache can help absorb checkpoint bursts as they write data to the disks.

    3.       Maintenance operations can be write intensive and long running.

    4.       SQL Server’s internal data organization rarely matches the physical layout of the data on disk so IO subsystem level read ahead through the read cache is rarely effective for a database.

    7.       Performance will be more predictable, IO troubleshooting will be easier, and in many cases overall performance can be higher if SQL Server is isolated to an IO path not shared with other systems. If it does share with others (which is very common), it is better to share with other databases than with file servers or other systems that have different needs.

    ·         Data and log files will not go on the same drive (they can have the same drive letter if on different mount points). SANs often hide the physical layer behind the drive letter/mount point by mixing data on the back end but it is still important to keep them separate in case you later move to totally separate IO paths for each.

    o   Where cost/benefit analysis allows, each database’s transaction log file(s) will get a separate drive.  For maximum performance this would be a truly separate IO path.
    REASON: Since writes to the transaction log are sequential (even if there are multiple log files for a given database, only one is written to at a time), if there are multiple database’s log files, or a mix of data and log files, on one drive the read head is moving between them and decreasing performance.

    o   Where cost/benefit analysis allows it, multiple, separate IO paths may be requested for each data file.
    REASON: The more drives you can use for data files, the more the IO can be spread out to increase performance. For certain types of activities guaranteeing that the IO does not overlap for two different data files can be advantageous.

    ·         Before putting each SQL box into production, run SQLIO under various loads, gather data to see if the IO subsystem is likely to be capable of the expected load. If you have numbers for a typical and current peak load, we can use those numbers and then add some to it (perhaps test things like 150% of expected peak batch cycle).

    ·         Before putting each SQL box into production, run SQLIOSim to test for data correctness.

    ·         Disk IO Subsystem Performance Requirements:

    o   Log writes will not exceed [put your values here] ms per write under the expected peak load (typical is 1-5ms for log writes)

    o   Non-log writes will not exceed [put your values here] ms per read under the expected peak load (typical is 1-20ms for OLTP and 25-30ms for warehouses)

    o   Reads will not exceed [put your values here] ms per read under the expected peak load (typical is 5-20ms for OLTP and 25-30ms for warehouses)

    o   These are general standards; some systems may require faster IO subsystems and some may be ok with slower IO subsystems.

    o   Measures of read and write speed are from the Performance Monitor counters reads/sec and writes/sec and/or from SQLIO as these are the speeds seen by SQL Server.
    REASON: Databases require fast disk access in order to deliver high performance. Many DBA and application developer hours can be spent troubleshooting performance problems that are later tracked to slow IO.

     

    Network Specifications

    ·         SNP/TCP Chimney settings will depend on whether your NIC vendor supports it. If you have NICs/drivers that support it, turn it on. Otherwise disable it to avoid known problems with SQL Server and other products.
    REASON: Performance and usability. When TCP Chimney is enabled it on a NIC that doesn’t support it, you will often see failed connectivity to SQL Server and/or     dropped packets and connections that affect SQL server. See Information about the TCP Chimney Offload, Receive Side Scaling, and Network Direct Memory Access features in Windows Server 2008 http://support.microsoft.com/kb/951037  and 942861 Error message when an application connects to SQL Server on a server that is running Windows Server 2003: “General Network error,” “Communication link failure,” or “A transport-level error” http://support.microsoft.com/default.aspx?scid=kb;EN-US;942861

    ·         Put a firewall between your server and the internet. Block TCP port 1433, UDP port 1434, and static ports used by named instances on the perimeter firewalls but not necessarily on individual internal servers. Be careful which ports you block in the other firewalls, SQL Server will use various ports to communicate on.
    REASON: Security – Hackers may try to use well known ports to attack SQL Server.

    ·         Open ports used by SQL Server in the individual server firewalls. [If you define a specific port range for all instances include it here.]
    REASON: The DBAs have defined this range of ports as what each SQL Server uses for incoming connections.

    Windows Cluster

    ·         Identical hardware: While Windows 2008 clusters are not required to be identical, to have a greater chance of predictability no matter which node owns each SQL Server group it is recommended that they be configured as close to the same as possible.

    ·         Windows policies and rights: Windows policies and rights should be the same on all nodes.
    REASON: The behavior of SQL Server must be the same on all nodes. Policies can change SQL Server behavior.

    ·         Mount points: Do not install SQL Server 2000 on any Windows cluster with mount points.  The mount points must have an associated drive letter and must be cluster resources in the group where SQL Server will reside. SQL Server must “depend on” all mount points that it uses.
    REASON: SQL Server 2005+ supports mount points but SQL Server 2000 setup, including service packs and hotfixes, will fail when it tries to enumerate the mount points, even if they are not in the SQL Server 2000 group. For 2005+ instances, the mount points must be in the SQL Server group in order for SQL Server to access them.

    ·         The Cluster service account must be a login in the SQL Server instance and a simple user in the master database, but should NOT be a sysadmin role member.
    REASON: Avoid elevated privileges.

    ·         MS DTC Choose a consistent standard for how you configure DTC. On Windows 2008+ clusters you can have more than one DTC per cluster and there are pros/cons to various configuraitons on how SQL Server uses one or more of those DTCs. http://blogs.msdn.com/cindygross/archive/2009/02/22/how-to-configure-dtc-for-sql-server-in-a-windows-2008-cluster.aspx DTC must be enabled for network access. If you choose to put DTC in a group with SQL Server and choose to have a DTC failure cause the group to fail, you should be aware that DTC can cause a SQL Server failover. This may be appropriate in some environments.

    ·         Cluster Group Names will not contain any special characters such as <, >, ‘, “, &
    REASON: Special characters in any group name may cause SQL Server setup to fail.

    ·         NIC names will not have any special characters or trailing spaces.
    REASON: Special characters in any network name may cause SQL Server setup to fail.

    ·         Auto start must NOT be on for any clustered resource in a Windows cluster.
    REASON: The cluster administrator needs to bring the resources online (start the services). If Windows startup has already started the service the cluster service cannot bring it online which results in errors.

    ·         Use SIDs in a Windows cluster. If you choose not to use Service SIDs, create unique domain groups: Each individual service installed with SQL Server needs a unique domain group created for it unless you choose the default of SIDs. The following naming standard will be used: [put your company standard here:  Examples: myserver1_instance1_SQLServer, myserver1_instance1_SQLAgent  ]     

    http://technet.microsoft.com/en-us/library/ms143504.aspx

    Best practices that you can use to set up domain groups and solutions to problems that may occur when you set up a domain group when you install a SQL Server 2005 failover cluster http://support.microsoft.com/default.aspx?scid=kb;EN-US;915846
    REASON: On a cluster domain groups are used to manage permissions for the SQL Server components. Each service needs a unique group to reduce the attack surface.

    Remote Control

    ·         Remote control to the server will only be done when absolutely necessary. No SQL Server tools will be opened on the production server itself unless there is no other way to access the server.
    REASON: It adds overhead to the server and can cause performance problems. Most access will be done from client tools installed on desktops. The overhead of the GUI interfaces is not acceptable on a production server. Some SQL Server client tools are not available on 64-bit systems and all servers will be x64 going forward.

    Hardware Specifications

    ·         Expected Life: Servers are spec’d with an expectation of being in service for up to [put your policy here, 3-4 is common] years unless otherwise stated.
    REASON: We need to know in advance how long the hardware is expected to stay in service so we can predict the resources needed for that time period. An uncertainty factor will be considered as well, so systems with high uncertainty in the predictions may need more expansion opportunity (have the ability to add more hardware resources as needed).

    ·         Architecture: All new servers will be based on x64 hardware with an x64 version/edition of Windows.
    REASON: 64-bit systems allow much more memory to be used than 32-bit systems.

     Cindy Gross, Microsoft Dedicated Support Engineer for SQL Server and Microsoft Certified Master : SQL Server 2008

  • Power Saving Options on SQL Server

    Power Saving Options on SQL Server

     

    Windows 2008 and Windows 2008 R2 have settings for “Power Options”. Windows 2008 R2 has additional power improvements related to “core parking” (temporarily suspending certain cores) and “tick skipping” (extended idle and sleep states). The default power setting for Windows 2008 and 2008 R2 is “Balanced Power” which means that when the system isn’t “busy” various components, such as the CPU and storage, are throttled back. This only happens when the box has CPUs and a BIOS that supports the ACPI specification for power that allows these options to take effect. Newer model CPUs are more likely to support this specification. Database systems that don’t have high sustained CPU usage are probably going to perform better with “High Performance”.  Most people size their SQL Server boxes so that the CPUs run at a fairly lower percentage of total available so it’s pretty easy to drop down to the slower CPU speeds and get slow, inconsistent performance. There are many documented cases of “Balanced Power” hampering SQL Server performance.  For a SQL Server box if you want consistent, predictable, high performance you either need to fine tune the power setting parameters for each individual workload and/or for different times of day or just set the power options to high performance. If power savings are more important than fast, predictable performance then you can leave it at “Balanced Power”. As a side note that isn’t directly related to SQL Server but may affect virtualized SQL Servers and/or application servers that connect to SQL Server, VMWare has a doc on the need for “High Performance” power settings on the VM host.

     

    SQL

    ·         SQL Server on Power-Saving CPUs? Not So Fast. http://www.brentozar.com/archive/2010/10/sql-server-on-powersaving-cpus-not-so-fast

    ·         Are your CPUs running slowly? Tool tip and survey http://www.sqlskills.com/BLOGS/PAUL/post/Are-your-CPUs-running-slowly-Tool-tip-and-survey.aspx

    ·         Windows OS Power Saving Feature Directly Affects CPU Performance http://www.bradmcgehee.com/2010/07/windows-os-power-saving-feature-directly-affects-cpu-performance/

     

    Virtualization

    ·         Poor virtual machine application performance may be caused by processor power management settings http://kb.vmware.com/selfservice/microsites/search.do?language=en_US&cmd=displayKC&externalId=1018206

     

    General

    ·         Degraded overall performance on Windows Server 2008 R2  http://support.microsoft.com/kb/2207548
    “    applications or scripts that intensively use resources (primarily processor and memory) may exhibit the problem”

    ·         Improve Energy Efficiency and Manage Power Consumption with Windows Server 2008 R2 http://download.microsoft.com/download/5/B/D/5BD5C253-4259-428B-A3E4-1F9C3D803074/WS08%20R2%20Power%20Savings%20White%20PaperTDM.docx
    “The “High Performance” mode may be appropriate for servers that run at very high utilization and need to provide maximum performance, regardless of power cost.”

    ·         Performance Tuning Guidelines for Windows Server 2008 http://download.microsoft.com/download/9/c/5/9c5b2167-8017-4bae-9fde-d599bac8184a/Perf-tun-srv.docx

    “However, Balanced might not be appropriate for all customers. For example, some applications require very low response times or very high throughput at high load. Other applications might have sensitive timing or synchronization requirements that cannot tolerate changes in processor clock frequency. In such cases, changing the power plan to High Performance might help you to achieve your business goals. Note that the power consumption and operating cost of your server might increase significantly if you select the High Performance plan.”

    To view/set

    ·         Powercfg.cpl

    ·         Powercfg.exe – Configure Power Plans http://technet.microsoft.com/en-us/library/dd744398(WS.10).aspx

    ·         Ensuring Maximum CPU Performance via Group Policy http://greg.blogs.sqlsentry.net/2011/01/ensuring-maximum-cpu-performance-via.html
    PBM Policy / WMI query to see which CPUs are in power-saving mode     http://www.sqlservercentral.com/scripts/CPU/72156/

    ·         From Powershell: gwmi -class win32_Processor | SELECT CurrentClockSpeed, MaxClockSpeed