Override Bad Table Hints With a Plan Guide

Unfortunately, we’ve all seen some third party applications pass some pretty awful SQL into databases. The worst of these always seem to be queries that cannot be edited or modified from within the application. Table/index and MAXDOP hints are a couple of the things I don’t like to see. These hints might be perfectly fine for a while, but as data grows and tables get larger, certain hints can prevent the query optimizer from working properly and providing the optimal execution plan.

I will be using the AdventureWorks2012 database for this example, and as part of the setup, I’ve already created the index below on the [Sales].[SalesOrderDetail] table. (It will be required for the FORCESEEK hint in my examples to work):

USE [AdventureWorks2012]
GO

CREATE NONCLUSTERED INDEX [idx_ProductID] 
ON [Sales].[SalesOrderDetail]
([ProductID] ASC)
GO

Let’s take this simple query with and without an added FORCESEEK hint as an example.

--SalesOrderDetail - 372,956 reads
SELECT * FROM Sales.SalesOrderDetail s WITH (FORCESEEK)
INNER JOIN Production.Product p ON s.ProductID = p.ProductID;

FORCESEEK_plan

--SalesOrderDetail - 1,246 reads
SELECT * FROM Sales.SalesOrderDetail s 
INNER JOIN Production.Product p ON s.ProductID = p.ProductID;

PlanGuide_plan

Using the WITH(FORCESEEK) hint, the query is doing 372,956 reads on the SalesOrderDetail table (with a pretty nasty Key Lookup), but without the hint, the query only does 1,246 reads (in this case, a scan is a good thing).

Let’s assume for this blog post that the application is passing in the query with the FORCESEEK hint, and that we cannot modify the application to remove the hint, even though we know, for a fact, that the query will perform much better without the hint. How are we supposed to remove the FORCESEEK hint and cut down on reads?

First, we need to take a look at adding another TABLE HINT to the query to override the existing FORCESEEK hint. There is a pretty nifty trick to do this. We can simply append ‘OPTION(TABLE HINT(s))’ to the end of the query and it’ll wipe out any prior TABLE HINT on the SalesOrderDetail table (aliased as ‘s’). Since we aren’t actually specifying any specific TABLE HINT, it basically resets all existing table hints on SalesOrderDetail. It brings us back down to 1,246 reads and gives us the query plan with the Clustered Index scan.

--SalesOrderDetail - 1,246 reads
SELECT * FROM Sales.SalesOrderDetail s WITH (FORCESEEK)
INNER JOIN Production.Product p ON s.ProductID = p.ProductID
OPTION(TABLE HINT (s)); --TABLE HINT reset

Now that we’ve figured out how to add to the existing query to remove the FORCESEEK hint, we need to figure out how to automatically have this statement appended to this query every time it is executed. Two words: Plan Guide.

Here is the basic syntax for getting this plan guide added to the database. You must give it a name, specify which SQL statement the hint will be added to, the type of plan guide, and the hint that will be added to the SQL Statement. (NOTE: the SQL statement must be passed into @stmt EXACTLY as the application will pass it in to the database. This even includes trailing spaces – which have caused me headaches many times.)

USE [AdventureWorks2012]
GO

EXEC sp_create_plan_guide 
@name = N'RemoveFORCESEEK', 
@stmt = N'SELECT * FROM Sales.SalesOrderDetail s WITH (FORCESEEK)
INNER JOIN Production.Product p ON s.ProductID = p.ProductID;', 
@type = N'SQL', 
@hints = N'OPTION(TABLE HINT (s))'

GO

Now that the plan guide is created, we can test running the original query with the FORCESEEK hint.

query_planguide

We can see from the execution plan properties that the query is now using the Plan Guide and doing the Clustered Index scan on SalesOrderDetail for 1,246 reads. We’ve successfully circumvented the bad FORCESEEK hint and saved a ton of reads in the process.

Checkout this MSDN link for further reading on Query Hints:
https://msdn.microsoft.com/en-us/library/ms181714.aspx

Find All Queries Using a Plan Guide in Cache

The inspiration for this post came a few days ago when a developer wanted to figure out if his application’s queries were still using plan guides that had been setup a couple of years ago. I asked him if he knew the specific queries in question, and of course, he did not.

My first thought was to create an Extended Events session to capture the “sqlserver.plan_guide_successful” and “sqlserver.plan_guide_unsuccessful” events. This would tell me, upon execution, if a query was successful or unsuccessful at using a plan guide. Unfortunately, the developer wasn’t able to execute the queries at the time, so this wouldn’t work in this case.

My next thought was to drill down into the database in Object Explorer -> Programmability -> Plan Guides and look to see what plan guides actually existed. Then I could drill into each individual plan guide and find the query that plan guide referenced. I could then lookup that query in cache and find the execution plan to see if the query was indeed using the plan guide.

This can usually be done by looking at the properties window of the left-most operator (the Select operator in this case) of the query plan. If the query is using a plan guide, there will be two Properties: PlanGuideDB and PlanGuideName.

PlanGuideInPlan

This could be tedious and take a little bit of time if you needed to lookup queries for many different plan guides. There had to be another way.

If you look at the raw XML of the execution plan, you can see the same PlanGuideDB and PlanGuideName properties (see line #5 below):

<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.2" Build="11.0.2100.60">
  <BatchSequence>
    <Batch>
      <Statements>
        <StmtSimple StatementText="select ? from Schema1.Object1" StatementId="1" StatementCompId="1" StatementType="SELECT" RetrievedFromCache="true" PlanGuideDB="AdventureWorks2012" PlanGuideName="PlanGuide_1" StatementSubTreeCost="0.00363" StatementEstRows="290" StatementOptmLevel="TRIVIAL" QueryHash="0x4578128A64C31C86" QueryPlanHash="0x4C7F74CCEDD0EAE0">
          <StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false" />
          <QueryPlan NonParallelPlanReason="MaxDOPSetToOne" CachedPlanSize="16" CompileTime="0" CompileCPU="0" CompileMemory="120">
            <MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" />
            <OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="207295" EstimatedPagesCached="51823" EstimatedAvailableDegreeOfParallelism="1" />
            <RelOp NodeId="0" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="290" EstimateIO="0" EstimateCPU="2.9e-005" AvgRowSize="11" EstimatedTotalSubtreeCost="0.00363" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row">
              <OutputList>
                <ColumnReference Column="Expr1003" />
              </OutputList>
              <ComputeScalar>
                <DefinedValues>
                  <DefinedValue>
                    <ColumnReference Column="Expr1003" />
                    <ScalarOperator ScalarString="ScalarString1">
                      <Const ConstValue="Value1" />
                    </ScalarOperator>
                  </DefinedValue>
                </DefinedValues>
                <RelOp NodeId="1" PhysicalOp="Index Scan" LogicalOp="Index Scan" EstimateRows="290" EstimateIO="0.003125" EstimateCPU="0.000476" AvgRowSize="9" EstimatedTotalSubtreeCost="0.003601" TableCardinality="290" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row">
                  <OutputList />
                  <IndexScan Ordered="0" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore">
                    <DefinedValues />
                    <Object Database="Database1" Schema="Schema1" Table="Object1" Index="Index1" IndexKind="NonClustered" />
                  </IndexScan>
                </RelOp>
              </ComputeScalar>
            </RelOp>
          </QueryPlan>
        </StmtSimple>
      </Statements>
    </Batch>
  </BatchSequence>
</ShowPlanXML>

Ah ha! We should be able to search through the execution plan xml to find all plans that contain %PlanGuideName% currently in cache. With that in mind I came up with the following query. (Note: This will only find queries using a plan guide that are currently cached. It is very possible that a query that is not in the procedure cache could use a plan guide, also. But if it isn’t cached, we won’t find it.)

--Could take a while to execute on a server with a large plan cache, be patient
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SELECT qt.text
	,DB_NAME(qt.dbid) AS db_name
	,LEFT(SUBSTRING(CAST(qp.query_plan AS nvarchar(max)), CHARINDEX('PlanGuideName', 
	CAST(qp.query_plan AS nvarchar(max))) + 15, 100), 
	CHARINDEX('"', SUBSTRING(CAST(qp.query_plan AS nvarchar(max)), 
	CHARINDEX ('PlanGuideName', CAST(qp.query_plan AS nvarchar(max))) + 16, 100))) AS PlanGuideName
	,qs.total_logical_reads / qs.execution_count AS [Avg Logical Reads]
	,qs.total_elapsed_time / qs.execution_count / 1000 AS [Avg Elapsed ms]
	,qs.execution_count
	,qs.last_execution_time
	,qs.creation_time as Compile_Time
	,qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE CAST(qp.query_plan AS nvarchar(max)) LIKE ('%PlanGuideName%')
	AND qt.text NOT LIKE '%sys.dm%' --filter out any DBA user queries that may be hitting system tables
ORDER BY qs.last_execution_time desc
OPTION (RECOMPILE, MAXDOP 1);--prevent this query from filling the cache & try to minimize the cpu impact

Sample output:

queryoutput

Voilà! Now we have a list of all the queries, currently in cache, that are using plan guides. Much easier than digging through each Plan Guide individually!

featuredimage

HammerDB and The New SQL Server 2014 Cardinality Estimator

OR

(The Importance of Workload Testing When Upgrading to SQL Server 2014)

With SQL Server 2014 comes a brand spankin’ new cardinality estimator. Here is an excerpt from the summary of the whitepaper titled “Optimizing Your Query Plans with the SQL Server 2014 Cardinality Estimator” by Joe Sack:

“SQL Server 2014 introduces the first major redesign of the SQL Server Query Optimizer cardinality estimation process since version 7.0.  The goal for the redesign was to improve accuracy, consistency and supportability of key areas within the cardinality estimation process, ultimately affecting average query execution plan quality and associated workload performance. “

Sounds fancy, huh? So I decided to play around and compare a HammerDB TPC-C workload played against a 2012 database versus that same workload against a 2014 version of that database. For those that do not know, HammerDB is a free open-source benchmarking tool that can be used on a variety of RDBMSs.

For this scenario, let’s pretend that our application (HammerDB) currently uses a SQL Server 2012 database on the backend. We have just been approved to upgrade to SQL Server 2014, and we want to make sure that after the upgrade our application will perform at least at the same level as it did while on the 2012 database. Generally speaking, users are not happy if an upgrade causes a performance degradation. =)

Surely HammerDB will perform better with the new and improved 2014 Cardinality Estimator (CE), right? Let’s see.

The Setup Process

1. I began by using HammerDB to create a new TPC-C database called ‘tpcc’ with 10 Virtual Warehouses on my local 2012 instance. The result is a small 1GB database with both the data and log files on my C: drive. (Note: I am using the Original schema to build the database. I have not tested with the Updated schema and results could very well be different.)

2. Next, I took a backup of the tpcc database. This allowed me to use the same starting database for each test, without having to use HammerDB to rebuild it.

BACKUP DATABASE [tpcc] TO DISK = N'C:\2012INSTANCES\tpcc_blogtesting_10WH.bak'; 

3. I restored the tpcc database to my new 2014 instance and ran some necessary upgrade commands:


/*
Using this blog post from Thomas LaRock as a guideline for upgrading to 2014:
http://thomaslarock.com/2014/06/upgrading-to-sql-server-2014-a-dozen-things-to-check/
*/

USE [master]
GO

--Both data and log file on my C drive - not being fancy here
RESTORE DATABASE [tpcc]
FROM DISK = N'C:\2012INSTANCES\tpcc_blogtesting_10WH.bak'
WITH FILE = 1
,MOVE N'tpcc' TO N'C:\2014INSTANCES\DATA\tpcc.mdf'
,MOVE N'tpcc_log' TO N'C:\2014INSTANCES\LOGS\tpcc_log.ldf'
,NOUNLOAD
,STATS = 5
GO

--Be sure to set the database compatibililty_level up to the newest version!
ALTER DATABASE [tpcc] SET COMPATIBILITY_LEVEL = 120;

USE [tpcc]
GO

DBCC CHECKDB WITH DATA_PURITY;

DBCC UPDATEUSAGE(tpcc);

USE [tpcc]
GO
EXEC sp_MSforeachtable @command1='UPDATE STATISTICS ? WITH FULLSCAN';

4. Now that the database was ready to go on both my local 2012 instance and on my local 2014 instance, it was time to prepare the workload driver options on HammerDB. I used a timed test with 1 Minute of Rampup Time and 3 minutes for the Test Duration. The Virtual User Options can be seen below:
HammerDBSetup

5. Time to setup PerfMon. I used PerfMon to capture ‘Batches Requests/Sec’ (separate counter for each instance) and CPU Usage. These two metrics give a good enough idea of how the workload is performing. I set the Scale for ‘% Processor Time’ to 10.0 to make it easier to view compared to Batch Requests/sec.

perfmon_setup

The Workloads

Now that the setup is complete, it’s time to run the HammerDB workloads. Here are the results and PerfMon charts:

2012 (Performance Baseline):

In our scenario, this is how our application performs pre-upgrade. This is the performance baseline. After upgrading to 2014, these are the numbers we want to try to hit.

HammerDB Results: 114,901 Transactions Per Minute (TPM); 25,035 New Orders Per Minute (NOPM)

CPU Average: 35%; Batch Requests / Sec average: ~1,750

2012_initialPerfMon

2012 PerfMon Chart

 2014 (Workload after the upgrade):

Let’s point the application at the upgraded 2014 database and see what happens!

HammerDB Results: 31,542 Transactions Per Minute; 6,894 New Orders Per Minute

CPU Average: 74%; Batch Requests / Sec average: ~ 492

2014_initialPerfMon

2014 PerfMon Chart

Uh Oh! After the upgrade our CPU Utilization has more than doubled and our application throughput has decreased by over 70%! If this were in production, users would be looking for heads to roll.

Let’s take a look at the performance numbers side-by-side:

Workload      TPM NOPM %CPU Avg BatchRequests/Sec Avg
2012 114,901 25,035 35%                         1,750
2014   31,542   6,894 74%                            492

What gives?! I thought 2014 was supposed to improve performance?! Well, let’s see if we can figure out what happened.

What Happened?

Because CPU usage was so different between the two workloads, the first thing I decided to do was to query sys.dm_exec_query_stats and compare query statistics based on which queries used the most CPU time (total_worker_time).


SELECT TOP 15 SUBSTRING(qt.TEXT, (qs.statement_start_offset / 2) + 1, (
(
CASE qs.statement_end_offset
WHEN - 1
THEN DATALENGTH(qt.TEXT)
ELSE qs.statement_end_offset
END - qs.statement_start_offset
) / 2
) + 1) AS 'QueryText'
,qs.total_logical_reads / qs.execution_count AS AvgLogicalReads
,qs.total_worker_time / qs.execution_count AS 'AvgWorkerTime(µs)'
,qs.total_worker_time AS 'total_worker_time(µs)'
,qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE qt.TEXT NOT LIKE '%sys.dm%' --filter out dmv queries
ORDER BY qs.total_worker_time DESC -- CPU time

Here are the results from the query on both instances (2012 on the left, 2014 on the right):

2012vs2014_fixed

2012 Results                                                            2014 Results

Right away, the query using the most total CPU on the 2014 instance jumps out at us. You can see the difference between the query on the 2012 instance and the 2014 instance. But why is the query behaving differently? Let’s check the execution plans.

The query (formatted for readability):

SELECT @stock_count = count_big(DISTINCT STOCK.S_I_ID)
FROM dbo.ORDER_LINE
 ,dbo.STOCK
WHERE ORDER_LINE.OL_W_ID = @st_w_id
 AND ORDER_LINE.OL_D_ID = @st_d_id
 AND (ORDER_LINE.OL_O_ID < @st_o_id)
 AND ORDER_LINE.OL_O_ID >= (@st_o_id - 20)
 AND STOCK.S_W_ID = @st_w_id
 AND STOCK.S_I_ID = ORDER_LINE.OL_I_ID
 AND STOCK.S_QUANTITY < @threshold

2012vs2014_plansSo, the query went from doing an Index Seek + a RID lookup on the STOCK table in 2012, to a parallelized table scan (with a missing index hint) in 2014.

The Fix

Luckily for us, the problematic query belongs to a stored procedure created by HammerDB called SLEV. This means it will be pretty easy to modify the query since we can directly edit the stored procedure on the database.

SQL Server 2014 introduces a brand new trace flag to assist in reverting to the legacy Cardinality Estimator. Here is another quote from the whitepaper mentioned earlier by Joe Sack:

“Trace Flag 9481 reverts query compilation and execution to the pre-SQL Server 2014 legacy CE behavior for a specific statement.”

So, we can use Trace Flag 9481 to bring back the old CE. This trace flag can be enabled globally, or at the individual query level. This allows us to fine tune certain queries where performance may have degraded since the upgrade, without affecting queries that may have actually improved with the upgrade.

We can place “OPTION QUERYTRACEON(9481)” at the end of the problem query inside the stored procedure to revert just that query to the old version. Hopefully this will get our HammerDB workload back to acceptable performance levels.

CREATE PROCEDURE [dbo].[SLEV] @st_w_id INT
	,@st_d_id INT
	,@threshold INT
AS
BEGIN
	DECLARE @st_o_id INT
		,@stock_count INT

	BEGIN TRANSACTION

	BEGIN TRY
		SELECT @st_o_id = DISTRICT.D_NEXT_O_ID
		FROM dbo.DISTRICT
		WHERE DISTRICT.D_W_ID = @st_w_id
			AND DISTRICT.D_ID = @st_d_id

		SELECT @stock_count = count_big(DISTINCT STOCK.S_I_ID)
		FROM dbo.ORDER_LINE
			,dbo.STOCK
		WHERE ORDER_LINE.OL_W_ID = @st_w_id
			AND ORDER_LINE.OL_D_ID = @st_d_id
			AND (ORDER_LINE.OL_O_ID < @st_o_id)
			AND ORDER_LINE.OL_O_ID >= (@st_o_id - 20)
			AND STOCK.S_W_ID = @st_w_id
			AND STOCK.S_I_ID = ORDER_LINE.OL_I_ID
			AND STOCK.S_QUANTITY < @threshold
		OPTION (QUERYTRACEON 9481) --*****ADD THIS OPTION TO REVERT QUERY TO LEGACY CE*****

		SELECT @st_o_id AS N'@st_o_id'
			,@stock_count AS N'@stock_count'
	END TRY

	BEGIN CATCH
		SELECT ERROR_NUMBER() AS ErrorNumber
			,ERROR_SEVERITY() AS ErrorSeverity
			,ERROR_STATE() AS ErrorState
			,ERROR_PROCEDURE() AS ErrorProcedure
			,ERROR_LINE() AS ErrorLine
			,ERROR_MESSAGE() AS ErrorMessage;

		IF @@TRANCOUNT > 0
			ROLLBACK TRANSACTION;
	END CATCH;

	IF @@TRANCOUNT > 0
		COMMIT TRANSACTION;
END
GO

Now that the trace flag is in the stored procedure, let’s fire HammerDB back up.

2014_traceflag_Perfmon

2014 Workload with TF 9481 on problem query

querystats_afterTF

BOOM! HammerDB throughput is back up to the same level it was at before the upgrade, CPU on my laptop is back in the 35% range, and the query’s statistics are back to the legacy CE levels. End users are rejoicing!

Conclusion

I’ve simply shown one way to fix the problem query in HammerDB. There are other ways this could have been handled, but the trace flag Microsoft introduced makes it very easy to quickly revert queries to the legacy CE to ‘fix’ performance.

It is very important to always test applications, workloads, or queries before fully upgrading to SQL Server 2014. To make the upgrade process easier on yourself, take some time to get to know your workloads (application throughput, query performance, etc.) before the upgrade ever takes place. With a good baseline for reference, it makes it much easier to compare performance post-upgrade. A single bad query can bring the entire application to its knees. The new Cardinality Estimator will most likely do much more good than bad for queries, but there will always be edge cases where performance declines.

SSIS: Troubleshooting Error -1071607685

Ever have to work with the obscure SSIS error numbers? They can be a pain to understand. Luckily someone wrote a blog post that details how to look up and figure out what the error numbers actually mean – check it out at the link below if this is new to you:

http://blogs.msdn.com/b/helloworld/archive/2008/07/25/how-to-decipher-understand-ssis-error-code.aspx

After running an SSIS package for a migration of a single table from DB2 to SQL Server, I had an error file with 9984 rows that were not able to be migrated. Upon looking at the error file, it looked like every row had failed with an error of -1071607685.

Capture_errors

So following the instructions of the msdn blog post, I decided to look up the error message. I converted the error number to it’s Hex value (FFFFFFFFC020907B), and looked up this value in the dtsmsg.h file.

Capture

Hmmm… “DTS_E_OLEDBDESTINATIONADAPTERSTATIC_UNAVAILABLE… No status is available”. Well that’s not very helpful!

To make matters even more confusing, when I manually tried to insert one of the failed rows, it successfully inserted with no errors. What the heck?! Time to figure this out.

Looking at the destination table structure, I saw there was a unique index on one of the columns. I decided to remove the unique index and give the load another try.  BOOM! All rows were inserted into the table with no errors.

So there were 9984 different rows that violated the unique constraint on the table?

  • The table structure on the source and destination were identical, so this didn’t sound right.
  • Plus, earlier I was able to manually insert one of the rows into the table, so I knew for a fact that ALL of these rows did not violate the unique constraint.

At this point, I was stumped. I couldn’t figure out what the problem seemed to be. SSIS gave me a vague error message and was logging rows to the error file that shouldn’t actually fail to insert.

Then, a co-worker reminded me about SSIS batch size and the fast load option. Suddenly it started to make sense.

I went back to the SSIS package and saw that DefaultBufferMaxRows was set to 10,000. So, SSIS was loading rows in batches of 10,000. This could explain why there were 9,984 rows in my error file. I changed DefaultBufferMaxRows to 1 on the Data Flow task.

Capture_buffer

Next, I changed the Data access mode on the OleDBDesination_SQL from “OpenRowset Using FastLoad” to “OpenRowset”.

Capture_rowset

Now SSIS should load the table one row at a time. I dropped and recreated the table (with the unique constraint enabled) to get ready for a fresh load. I started the SSIS package again. BOOM! Only ONE row was logged to the error file now. Hopefully this time it would give me something better than “No Status is Available”.

This time the error was -1071607683 (hex value FFFFFFFFC020907D) which translates to “DTS_E_OLEDBDESTINATIONADAPTERSTATIC_INTEGRITYVIOLATION… The data value violates integrity constraints.” MUCH more manageable now!

Capture_integrity

It turns out, the source DB2 database was Case Sensitive and the destination SQL Server database was Case Insensitive. So the duplicate value wasn’t actually a duplicate on the DB2 database, but WAS a duplicate on the SQL Server side.

I decided to go back and look at the original error file that had 9984 “error rows”. I found the one row that violated the unique constraint and it had the same -1071607685 error as every other row. There would have been no way to find the one bad row from the original error file.

When using large row batch sizes and the FastLoad option in SSIS, be aware of potentially having trouble with the error files. One bad row in a batch of 10,000 caused the entire batch to error out for me, and it took a little bit of work to uncover the actual problem.

Using Powershell to loop through a list of SQL Server Databases

I use Powershell quite a bit to automate routine tasks with SQL Server – mainly because Powershell makes it so simple to perform the same task on a given list of servers. With Powershell, we can execute a .sql script on many servers and save the output for later use. For example, say you need to script out and save database security for all production databases. We can do this with Powershell easily, and I’ll show you how.

Here is the sql script I will be using to script out database level security.

First of all, there needs to be an inventory or list of the databases that you will be querying. Here is an example of a SQL_DATABASES inventory table, and the query we will be using to get our list of databases. We will need both the server and database names.

CREATE TABLE [dbo].[SQL_DATABASES](
	[INSTANCE] [varchar](50) NOT NULL,
	[DATABASENAME] [varchar](100) NOT NULL,
	[REGION] [varchar](4) NOT NULL)
--------
SELECT INSTANCE
 ,DATABASENAME
FROM dbo.SQL_DATABASES
WHERE REGION = 'PROD' --or 'DEV' / 'TEST'

We will be using invoke-sqlcmd to make the connection to the inventory table. You can hit up that link to see the differences between sqlcmd and invoke-sqlcmd. Basically, we need to specify our -ServerInstance, -Database and -Query we will use to get the list of our production databases. Then, each Instance and Database pair will be put into the $databases variable.

# $databases grabs list of production databases 
# from the SQL_DATABASES table on your Database
# ----------------------------------------------
# NOTE* It is always best to test any process on a single dev or test server
# before trying it on ALL production databases!

$databases = invoke-sqlcmd -ServerInstance Server -Database Database `
-Query "SELECT INSTANCE ,DATABASENAME FROM Database.dbo.SQL_DATABASES WHERE REGION = 'PROD'"


Now that we’ve placed the list of Servers and databases into $databases, we need a way to loop through each database in that list. This is where the foreach loop comes in handy.

foreach ($database in $databases) #for each separate server / database pair in $databases
{
# This lets us pick out each instance ($inst) and database ($name) as we iterate
# through each pair of server / database.
$Inst = $database.INSTANCE #instance from the select query
$DBname = $database.DATABASENAME #databasename from the select query
#...

Next we will generate the file name for the output of the query for each database. The goal is to have a separate .sql file for each database with a file name format of “Instance_DatabaseName_security.sql”. I am using C:\DBA_SCRIPTS\DB_Permissions\ as the filepath for the output files. I’ve also included the syntax that could be used to do a Replace on named instances to remove the “\” for file names.

#...
#generate the output file name for each server/database pair
$filepath = "C:\DBA_SCRIPTS\DB_Permissions\"
$filename = $Inst +"_"+ $DBname +"_security.sql" 

# This line can be used if there are named instances in your environment.
#$filename = $filename.Replace("\","$") # Replaces all "\" with "$" so that instance name can be used in file names.

$outfile = ($filepath + $filename) #create out-file file name
#...

Now that we have the list of databases and have generated the output file name for each pair, we can execute our script on each database. For scripting database permissions, I use a variation of this script from S. Kusen. This next piece of powershell will connect to each individual server($Inst) and database($DBname) using invoke-sqlcmd, execute our .sql file (SQL_ScriptDBPermissions_V2.sql) to script out database security (using -InputFile), and output the results from each database into a separate .sql file at the $outfile path we created above.

#...
#connect to each instance\database and generate security script and output to files
invoke-sqlcmd -ServerInstance ${Inst} -Database ${DBname} -InputFIle "C:\DBA_SCRIPTS\DB_Permissions\SQL_ScriptDBPermissions_V2.sql" | out-file -filepath ($outfile)

} #end foreach loop

It’s as simple as that, and this is just one example of how powershell can easily be used to accomplish a task on many SQL Servers at once.

Here is the entire powershell script:

# NOTE* It is always best to test any process on a single dev or test server before trying it on ALL production databases!
# ----------------------------------------------
Import-Module SqlPs -DisableNameChecking #may only need this line for SQL 2012 +

# $databases grabs list of production databases from the SQL_DATABASES table on your Database
$databases = invoke-sqlcmd -ServerInstance Server -Database Database -Query "SELECT INSTANCE ,DATABASENAME FROM Database.dbo.SQL_DATABASES WHERE REGION = 'PROD'"


foreach ($database in $databases) #for each separate server / database pair in $databases
{
# This lets us pick out each instance ($inst) and database ($name) as we iterate through each pair of server / database.
$Inst = $database.INSTANCE #instance from the select query
$DBname = $database.DATABASENAME #databasename from the select query


#generate the output file name for each server/database pair
$filepath = "C:\DBA_SCRIPTS\DB_Permissions\"
$filename = $Inst +"_"+ $DBname +"_security.sql"
 
# This line can be used if there are named instances in your environment.
#$filename = $filename.Replace("\","$") # Replaces all "\" with "$" so that instance name can be used in file names.
 
$outfile = ($filepath + $filename) #create out-file file name


#connect to each instance\database and generate security script and output to files
invoke-sqlcmd -ServerInstance ${Inst} -Database ${DBname} -InputFIle "C:\DBA_SCRIPTS\DB_Permissions\SQL_ScriptDBPermissions_V2.sql" | out-file -filepath ($outfile)
 
} #end foreach loop

Free Tools for the SQL Server DBA

Let me preface this by saying that this is my first ever blog post of any kind. I decided to make this blog to document “interesting” experiences or situations that may arise during my day-to-day work as a DBA. This blog is as much for me as it is for anyone who decides to read it.

I decided to make my very first post about free tools that I use to help solve various problems, whether it be performance tuning or routine maintenance tasks. These tools are completely free – so you have no excuse to not be using them.

I will try to maintain this list on this page, but without the details.

TOOLS

sp_WhoIsActive:

DBA’s need to pinpoint exactly what is running slow on a database and sometimes do it very quickly. Developers love the “Hey why is my app running slow?” or “OMGWTH – Why haven’t you fixed that POS database yet?” questions. sp_WhoIsActive lets you see exactly what queries are running, how long they have been running, and what they are waiting on currently. WhoIsActive also has a lot of extra parameters you can pass to it to find even more info. Some of my favorites are @Get_plans, @find_block_leaders, and @get_avg_time.

I could write an entire post just about using WhoIsActive, so I’ll point you to some much better blogs. Brent Ozar did a very good post on WhoIsActive and Adam Mechanic (the creator of WhoIsActive) did a whole blog series on using the procedure.

Pro Tip: Install WhoIsActive on all servers and create a Keyboard Query Shortcut in SSMS (I use Ctrl+F1) to execute sp_WhoIsActive in any open query window.

SQLQueryStress:

SQLQueryStress is a very handy tool, and coincidentally, it was also created by Adam Mechanic. Let’s say you are trying to tune a particular query, whether it be by re-writing the query or by adding indexes. SQLQueryStress provides a very quick look at how the query is performing by showing seconds per iteration, total logical reads per iteration, and CPU seconds per iteration. This removes the step of having to add up the STATISTICS IO output to figure out the total number of reads the query is doing.

It can also do some very cool things with parameter substitution. It is very important when tuning a query to make sure the query is faster with many different parameters, not just a single set of parameters, and SQLQueryStress will help automate that process for you.

SQLQueryStress

SQL Load Generator:

Every need to generate a query load on your SQL Server? SQL Load Generator provides a quick and easy way of doing this. It’ll let you run many queries simultaneously with different logins and different application names. Enough said.

LoadGenerator

SQL Sentry Plan Explorer:

After using Plan Explorer, it is the only way that I will analyze execution plans anymore. SSMS is clunky and can make it challenging to analyze more complex execution plans. Plan explorer helps to immediately identify the most expensive operators or subtrees. Bottom line: Plan Explorer makes query analysis much more efficient and it looks so much better than viewing a plan in SSMS.

Aaron Bertrand did a great post on using Plan Explorer here.

ClearTrace:

Ever need to quickly analyze a trace file? ClearTrace will import a SQL Server trace file into a database of your choosing, summarize the data, and graphically display performance information in an easy-to-digest format. ClearTrace can sort by whichever column you want, making it really easy to find either the most I/O or CPU intensive queries.

ClearTrace

Redgate SQL Search:

SQL Search is a SSMS add-in that does exactly what it sounds like: it will search through objects (tables, views, stored procedures, functions, etc.) in a database for a snippet of text. This is helpful when trying to find everything that may reference a table or any other given object.

SQLSearch

NimbleText:

NimbleText is great if you write a lot of SQL statements. If you do any type of repetitive typing or text editing, NimbleText can probably help.

WEBSITES

StatisticsIOParser.com:

StatisticsIOParser (developed by Richie Rump) is a great way to deal with the obnoxious output of SET STATISTICS IO. It formats and sums up the totals of the output. Very simple and very helpful.

StatisticsIOParser

PoorSQL.com:

PoorSQL is an online SQL formatter. It makes ugly SQL look pretty. (Very similar affect as that third cocktail you drank at the bar the other night – only you probably weren’t looking at SQL)

Pro Tip: Download the SSMS plugin to format SQL in your own query windows.PoorSQL

Did I forget a free tool or website? Is there a great free tool I’m not using, and definitely should be? Let me know in the comments.