Tuesday, February 23, 2016

List of Users Who Recently Changed Their Password

There may come a time when you need to generate a list of SQL logins and the last time their password was changed. By using the LOGINPROPERTY of the name in sys.server_principals we can generate such a list.

Note: This will only work with SQL logins.

The following query will generate the results below.

            ,LOGINPROPERTY(SP.name,N'PasswordLastSetTime') 'LastPWReset'
FROM    sys.server_principals SP
WHERE   SP.[type] = 'S'

Thursday, January 14, 2016

Using Extended Events to Find the Actual Execution Plan for a Table Valued Function

While finding the estimated Execution Plan for a Table Valued Function (TVF) isn’t all that difficult, getting to the actual Execution Plan is somewhat of a challenge. Take this example from the AdventureWorks database.
SELECT * FROM ufnGetContactInformation(3)
By clicking on the Display Estimated Execution Plan we get the following:


To get the actual plan, make sure the following option is selected and execute the query.


While the estimated gives us all kinds of information, the actual plan keeps the underlying operations hidden in favor of a Clustered Index Scan and a TVF operator. This isn’t very useful when it comes to troubleshooting performance issues especially if your query has multi-table joins to the TVF.
Thankfully, this is where Extended Events (EE) comes into play. By using EE, we can capture the Post Execution Showplan that will give us the actual full plan behind the Clustered Index Scan and TVF operators.
We’ll use the following code to setup the EE:
ADD EVENT sqlserver.query_post_execution_showplan (
    ACTION (sqlserver.database_name,sqlserver.plan_handle,sqlserver.sql_text)
    WHERE  (sqlserver.session_nt_user = N'<Your User Name>'))
ADD TARGET package0.ring_buffer

NOTE: This EE can several decrease performance if used on a high transaction environment. It is not recommended to use this in a production environment.
This session will capture the Showplan XML event, as well as the database name, plan handle, and SQL text of the query. The session is filtered on whatever name you choose (I used my NT login name). Finally, the session sends its output to the ring buffer so we can watch it live in SSMS.
Once the session has been created and turned on, right click on the EE and choose the “Watch Live Data” option.

And the following window will open in SSMS.

Now, in another SSMS window, we’ll execute the query with the TVF again and see the following data flow into the live data viewer.

By clicking on the Query Plan tab, we’ll finally see the full execution plan. Since this TVF has several code blocks in it, there are several plans, but I’ll only show the main one here with the knowledge there are several plans.

Check out the following posts for further reading on using Extended Events and the Query_Post_Execution_Showplan event:
Impact of the query_post_execution_showplan Extended Event in SQL Server 2012
Getting Started with Extended Events in SQL Server 2012

Monday, January 11, 2016

Why Isn’t My Filtered Index Working?

With the introduction of filtered indexes in SQL 2008, DBA’s were finally able to create small, well defined indexes with a simple predicate that would allow queries to search a subset of a rows on a table rather than all the rows on a table. While the technology has been around for some time, I haven’t seen them mentioned too often in forums, blogs or newsletters. That said, just the other day I found an occasion to use a filtered index, and after creating it, was left wondering why the query didn’t take advantage of it.

First, let’s set up our test scenario. We’ll be using the Sales.SalesOrderHeader and Sales.SalesOrderDetail tables in the AdventureWorks database. In this case, I’m getting the Sum of each order during a specified time period while specifying whether or not it was an online order.

Here’s the query:

DECLARE @OnlineFlag BIT = 1
DECLARE @StartDate DATETIME = '7/1/2001'
DECLARE @EndDate DATETIME = '7/31/2002'

SELECT SalesOrderHeader.SalesOrderID,
  FROM Sales.SalesOrderHeader INNER JOIN Sales.SalesOrderDetail
    ON SalesOrderHeader.SalesOrderID = SalesOrderDetail.SalesOrderID
WHERE SalesOrderHeader.OnlineOrderFlag = @OnlineFlag
   AND SalesOrderHeader.OrderDate BETWEEN @StartDate AND @EndDate
GROUP BY SalesOrderHeader.SalesOrderID

The resulting execution plan:

From here, I thought I might be able to use a filtered index on the OnlineOrderFlag in the SalesOrderHeader table.

Here’s the index creation statement:

CREATE NONCLUSTERED INDEX IX_SalesOrderHeader_OnlineOrderFlagFiltered
    ON Sales.SalesOrderHeader(OrderDate,SalesOrderID,OnlineOrderFlag)
 WHERE OnlineOrderFlag = 1

Note the Where statement, this is where the filter comes in to play. Functions like getdate() or dateadd() are not available to use in this context.

Once the filter is added, I think I’m in business, so I execute the query again and get the following execution plan.

But instead of seeing the use of my shiny new filtered index, the query is still using the Clustered Index Scan in addition to a new warning on the SELECT operator. If I click the operator and check the warnings, Unmatched Indexes is showing True.

This warning is telling me that Parameterization is to blame for the filtered index not being used. From here, I see 3 options.

  1. Remove the parameters and use literals. (not practical)
  2. Use Dynamic SQL
  3. Use OPTION(RECOMPILE) at the bottom of the query.

For the purposes of this exercise, I’ll be using OPTION(RECOMPILE) so that the query can take advantage of the filtered index.

Here the query with the added hint:

DECLARE @OnlineFlag BIT = 1
DECLARE @StartDate DATETIME = '7/1/2001'
DECLARE @EndDate DATETIME = '7/31/2002'

SELECT SalesOrderHeader.SalesOrderID,
  FROM Sales.SalesOrderHeader INNER JOIN Sales.SalesOrderDetail
    ON SalesOrderHeader.SalesOrderID = SalesOrderDetail.SalesOrderID
WHERE SalesOrderHeader.OnlineOrderFlag = @OnlineFlag
   AND SalesOrderHeader.OrderDate BETWEEN @StartDate AND @EndDate
GROUP BY SalesOrderHeader.SalesOrderID

And the resulting execution plan:

This solution for your filtered index may or may not be ideal for you because of the added CPU/Compile time added to each execution of the query. That said, if you have wildly varying parameters for each execution of your query, you may have already added OPTION (RECOMPILE) to deal with issues like parameter sniffing.

Here are some fantastic resources if you would like to read further on Filtered Indexes:

Introduction to SQL Server Filtered Indexes

What You Can (and Can’t) Do With Filtered Indexes

Filtered Indexes: What You Need To Know

Monday, December 21, 2015

Using Powershell to Manipulate TFS Get Latest, Check In, and Merge

In our environment, changes made in the Test branch have to travel through the Main branch and into the Release branch to be deployed into production. Sometimes changes need to move through quickly without regard to other changes, especially in an environment where there may be a single coder. Note: the following code will merge all checked in code regardless of who checked it in. Be careful in multi-coder environments.

While PowerShell is being used to write this process, many of the commands below are actual command line directives. These older, mature commands have more features than the native PowerShell TFS commands and are documented more extensively.

The first line in the PowerShell script should be the Set-Location command. This will set the scripts working location to the location of the TFS workspace. In my instance it is “C:\Development”.

set-location c:\development

Next, the comment that I want attached to the “Check in” of the changed files to each branch.

$Comment = """123456"""

In order to keep from typing the full path of the TF.exe on each Merge and Checkin, a variable is used to hold the file location.

$CommandLocation = "C:\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\IDE\TF.exe "

Since there are multiple branches that need to be merged and checked out, I’m hard coding the GET command for each branch. Basically, it gets the latest version of each file (not including your changes) from each branch. This is done to keep merge confilcts to a minimum.

$GetDevCommand = " get $/<TeamProjectName>/<DevBranch>/<Directory> /recursive /force "
$GetMainCommand = " get $/<TeamProjectName>/<MainBranch>/<Directory> /recursive /force "
$GetReleaseCommand = " get $/<TeamProjectName>/<ReleaseBranch>/<Directory> /recursive /force "

<TeamProjectName> is the name of the Team Project you are working in under the Team Collection.

<xBranch> is either Dev, Main, or Release in this instance.

<Directory> is the name of a directory (and possible subdirectories) below the branch. This can be as granular as needed in that <directory/subdirectory/subdirectory> can be specified. The /recursive flag recurses through all subdirectories under the <Directory> and /force causes them to be overwritten.

The same hard coding occurs in the Merge and Checkin commands below

$MergeToMain = " merge $/<TeamProjectName>/<DevBranch>/<Directory> $/<TeamProjectName>/<MainBranch>/<Directory>/recursive  /version:T"
$CheckinToMain = " checkin /comment:$comment /recursive /noprompt ""c:\development\<TeamProjectName>/<MainBranch>/<Directory>"""

$MergeToRelease = " merge $/<TeamProjectName>/<MainBranch>/<Directory> $/<TeamProjectName>/<ReleaseBranch>/<Directory> /recursive  /version:T"
$CheckinToRelease = " checkin /comment:$comment /recursive /noprompt ""c:\development\<TeamProjectName>/<ReleaseBranch>/<Directory>"""

Once you enter the TeamProjectName, Branch, and Directory, the work of moving files in TFS can begin. This is accomplished with the Start-Process command.

By using the –wait and –RedirectStandardOutput flags, each command executes sequentially and redirects the output from each command to its own txt file for troubleshooting later.

#Get Lastest Files From Sources Control
Start-Process "$CommandLocation" "$GetDevCommand" -wait -RedirectStandardOutput c:\temp\Get-TFS.txt
Start-Process "$CommandLocation" "$GetMainCommand" -wait -RedirectStandardOutput c:\temp\Get-TFS.txt
Start-Process "$CommandLocation" "$GetReleaseCommand" -wait -RedirectStandardOutput c:\temp\Get-TFS.txt

#Merge and Checkin to Main
Start-Process "$CommandLocation" "$MergeToMain" -nonewwindow -wait -RedirectStandardOutput c:\temp\MergePreProd-TFS.txt
Start-Process "$CommandLocation" "$CheckinToMain" -nonewwindow -wait -RedirectStandardOutput c:\temp\CheckinPreProd-TFS.txt

#Merge and Checkin to Release
Start-Process "$CommandLocation" "$MergeToRelease" -nonewwindow -wait -RedirectStandardOutput c:\temp\MergeProd-TFS.txt
Start-Process "$CommandLocation" "$CheckinToRelease" -nonewwindow -wait -RedirectStandardOutput c:\temp\CheckinProd-TFS.txt

In this instance the output for each command is directed to a file the in the C:\Temp directory for viewing later.



Thursday, December 17, 2015

SQL Reporting Services 2014 Save, Refresh, and Print Buttons on Separate Lines in IE

In SSRS 2014 there is a bug in rendering pages in Internet Explorer where the Save, Refresh, and Print buttons will all show on different lines in IE.

Below is a screenshot of an report rendering on an unpatched server.

By moving to build 12.00.4422 (CU2 for SQL Server 2014 Service Pack 1) the problem is corrected.


Thursday, December 3, 2015

Quickly Flip Database to Mirroring Partner

While Database Mirroring is certainly not a new technology, I’ve only started using it for HA on some of our older SQL instances. In doing so, I’ve found the need to quickly (and manually) flip the databases using mirroring to the mirrored instance. While you can accomplish this slowly using the GUI, if you have more than 1 or 2 databases to flip it can get old very quickly.

With that in mind, I’ve created a script where the output of the script can be used to quickly flip the databases that meet certain criteria. In this case, any databases that are currently SYNCHRONIZED and are the PRINCIPAL in the mirroring session will be selected.

   1: SELECT 'ALTER DATABASE [' + DB_NAME(database_mirroring.database_id) + '] SET PARTNER FAILOVER' AS 'Script'
   2:        ,DB_NAME(database_mirroring.database_id) as 'DB Name'
   3:        ,database_mirroring.mirroring_partner_name AS 'Mirroring Failover Partner'
   4: FROM    sys.database_mirroring
   5:  WHERE database_mirroring.mirroring_state_desc = 'SYNCHRONIZED'
   6:    AND database_mirroring.mirroring_role_desc = 'PRINCIPAL'

The meat of the script is in column 1. By running the commands generated in column 1 in another window with the same connection, you can quickly flip the databases to the mirrored servers. Columns 2 and 3 are there for extra information, namely the name of the database to be flipped and the name of the mirrored server that will serve primary instance of the database.


Wednesday, October 14, 2015

Using SQLCMD in SSMS to quickly check sync jobs

For some of us, an unfortunate side effect of using 3rd party applications to run different segments of your business, is the need to sync information between databases. Whether it be orders, employees, or something else, data needs to be synced between these systems to allow for each business segment to run.

As a DBA, running down issues in these sync processes can be very difficult with one SSMS query window looking in each system, constantly switching back and forth between results sets to look for differences.

Enter SQLCMD mode in SSMS.

Using SQLCMD mode inside your query allows you to open up a connection to a server, query a database and return a result from as many different servers as needed all within the same window.

In this example, I’ve created an Employee database on 3 different instances of SQL Server. Inside those DB’s, there is an Employee tables that hold all of my employees. At the top of the query, I’ve created a variable called “Emp” and set it to “Gail Huff” so it can be used across all the connections.

NOTE: In order to run this query, you must turn on SQLCMD mode by going to Query –> SQLCMD mode

   1: :SETVAR Emp "Gail Huff"
   5: USE EmployeeDB2008R2
   6: GO
   7: SELECT 'EmployeeDB2008R2' AS DB,* FROM Employee WHERE Employee.Name = '$(Emp)'
   8: GO
  12: USE EmployeeDB2012
  13: GO
  14: SELECT 'Employee2012' AS DB,* FROM Employee WHERE Employee.Name = '$(Emp)'
  15: GO
  19: USE EmployeeDB2014
  20: GO
  21: SELECT 'Employee2014' AS DB,* FROM Employee WHERE Employee.Name = '$(Emp)'
  22: GO

Note the SETVAR and CONNECT keywords. Both do exactly what they sound like and allow SQLCMD mode to use variables and quickly connect to different SQL servers. After running the query, my result set is as follows and you can see that Gail has the same status across all servers.

Happy querying!