Running sql queries from Powershell

Have you ever needed to test out the performance of sets of sql queries?

Recently we had some performance issues on a live infrastructure which needed diagnosing. Getting code released was slightly tricky so instead we concocted some scripts to allow us to run sql queries from Powershell. The advantage of these, we could run from different boxes in the farm to try and isolate where the issues arrived.

The key settings in this setup are:

# Specify SQL Connection String. 
$master = "Data Source=###;Initial Catalog=Master;Integrated Security=SSPI;" 
$core = "Data Source=###;Initial Catalog=Core;Integrated Security=SSPI;" 
# Specify SQL Connection String. 

$runs = 10

# Specify SQL Connection String.

$master = "Data Source=###;Initial Catalog=Master;Integrated Security=SSPI;"

$core = "Data Source=###;Initial Catalog=Core;Integrated Security=SSPI;"

# Specify SQL Connection String.

$runs = 10

The connection strings are much like your standard application connection strings.

To then run in bulk you can use the script shown below. Note, adjust the specific queries you want to execute:

function ExecNonQuery 
{ 
    param ($conStr, $cmdText) 
   
    # Determine if parameters were correctly populated. 
    if (!$conStr -or !$cmdText) 
    { 
        # One or more parameters didn't contain values. 
        write-Host "ExecNonQuery function called with no connection string and/or command text." 
    } 
    else 
    { 
        write-Host "Creating SQL Connection..." 
        # Instantiate new SqlConnection object. 
        $Connection = New-Object System.Data.SQLClient.SQLConnection 
         
        # Set the SqlConnection object's connection string to the passed value. 
        $Connection.ConnectionString = $conStr 
         
        # Perform database operations in try-catch-finally block since database operations often fail. 
        try 
        { 
            #write-Host "Opening SQL Connection..." 
            # Open the connection to the database. 
            $Connection.Open() 
             
            #write-Host "Creating SQL Command..." 
            # Instantiate a SqlCommand object. 
            $Command = New-Object System.Data.SQLClient.SQLCommand 
            # Set the SqlCommand's connection to the SqlConnection object above. 
            $Command.Connection = $Connection 
            # Set the SqlCommand's command text to the query value passed in. 
            $Command.CommandText = $cmdText 
             
            #write-Host "Executing SQL Command..."   
            # Note, to see a result back here, ExecuteNonQuery has been changed to ExecuteScalar
            write-host $Command.ExecuteScalar() 
        } 
        catch [System.Data.SqlClient.SqlException] 
        { 
            # A SqlException occurred. According to documentation, this happens when a command is executed against a locked row. 
            #write-Host "One or more of the rows being affected were locked. Please check your query and data then try again." 
        } 
        catch 
        { 
            # An generic error occurred somewhere in the try area. 
            write-Host "An error occurred while attempting to open the database connection and execute a command." 
        } 
        finally { 
            # Determine if the connection was opened. 
            if ($Connection.State -eq "Open") 
            { 
                write-Host "Closing Connection..." 
                # Close the currently open connection. 
                $Connection.Close() 
            } 
        } 
    } 
} 

 
# Specify SQL Connection String. 
$master = "Data Source=###;Initial Catalog=Master;Integrated Security=SSPI;" 
$core = "Data Source=###;Initial Catalog=Core;Integrated Security=SSPI;" 
# Specify SQL Connection String. 

$runs = 10
$stopWatch = [System.Diagnostics.Stopwatch]::StartNew()

for ($i=0; $i -le $runs; $i++)
{
ExecNonQuery -conStr $core -cmdText "SELECT COUNT (*) FROM aspnet_Users" 

ExecNonQuery -conStr $core -cmdText "SELECT COUNT (*) FROM aspnet_UsersInRoles" 

ExecNonQuery -conStr $core -cmdText "exec sp_executesql N'SELECT [ItemId], [Order], [Version], [Language], [Name], [Value], [FieldId], [MasterID], [ParentID]
                     FROM (
                        SELECT [Id] as [ItemId], 0 as [Order], 0 as [Version], '''' as [Language], [Name], '''' as [Value], [TemplateID] as [FieldId], [MasterID], [ParentID]
                        FROM [Items]

                        UNION ALL                          
                        SELECT [ParentId] as [ItemId], 1 as [Order], 0 as [Version], '''' as [Language], NULL as [Name], '''', NULL, NULL, [Id]
                        FROM [Items] 

                        UNION ALL 
                        SELECT [ItemId], 2 as [Order], 0 AS [Version], '''' as [Language], NULL as [Name], [Value], [FieldId], NULL, NULL
                        FROM [SharedFields] 

                        UNION ALL 
                        SELECT [ItemId], 2 as [Order], 0 AS [Version],       [Language], NULL as [Name], [Value], [FieldId], NULL, NULL
                        FROM [UnversionedFields] 

                        UNION ALL 
                        SELECT [ItemId], 2 as [Order],      [Version],       [Language], NULL as [Name], [Value], [FieldId], NULL, NULL 
                        FROM [VersionedFields]
                     ) as temp  WHERE [ItemId] IN (SELECT [ID] FROM [Items] WITH (nolock)  WHERE [TemplateID]=@p0 OR [TemplateID]=@p1 OR [TemplateID]=@p2 OR [TemplateID]=@p3 OR [TemplateID]=@p4 OR [TemplateID]=@p5 OR [TemplateID]=@p6 OR [ID]=@p7 OR [ParentID]=@p8 OR [TemplateID]=@p9 OR [TemplateID]=@p10 OR [ParentID]=@p11) 
                     ORDER BY [ItemId], [Order] ASC, [Language] DESC, [Version] DESC',N'@p0 uniqueidentifier,@p1 uniqueidentifier,@p2 uniqueidentifier,@p3 uniqueidentifier,@p4 uniqueidentifier,@p5 uniqueidentifier,@p6 uniqueidentifier,@p7 uniqueidentifier,@p8 uniqueidentifier,@p9 uniqueidentifier,@p10 uniqueidentifier,@p11 uniqueidentifier',@p0='AB86861A-6030-46C5-B394-E8F99E8B87DB',@p1='E269FBB5-3750-427A-9149-7AA950B49301',@p2='455A3E98-A627-4B40-8035-E683A0331AC7',@p3='239F9CF4-E5A0-44E0-B342-0F32CD4C6D8B',@p4='A87A00B1-E6DB-45AB-8B54-636FEC3B5523',@p5='F68F13A6-3395-426A-B9A1-FA2DC60D94EB',@p6='B6F7EEB4-E8D7-476F-8936-5ACE6A76F20B',@p7='11111111-1111-1111-1111-111111111111',@p8='11111111-1111-1111-1111-111111111111',@p9='EB06CEC0-5E2D-4DC4-875B-01ADCC577D13',@p10='B2A1FF6C-6B1F-41EF-B2E1-AA598CF7335F',@p11='C74AC643-53C8-4F1E-9508-840CDC72AACA'" 
 
ExecNonQuery -conStr $master -cmdText "exec sp_executesql N'SELECT [ItemId], [Order], [Version], [Language], [Name], [Value], [FieldId], [MasterID], [ParentID]
                     FROM (
                        SELECT [Id] as [ItemId], 0 as [Order], 0 as [Version], '''' as [Language], [Name], '''' as [Value], [TemplateID] as [FieldId], [MasterID], [ParentID]
                        FROM [Items]

                        UNION ALL                          
                        SELECT [ParentId] as [ItemId], 1 as [Order], 0 as [Version], '''' as [Language], NULL as [Name], '''', NULL, NULL, [Id]
                        FROM [Items] 

                        UNION ALL 
                        SELECT [ItemId], 2 as [Order], 0 AS [Version], '''' as [Language], NULL as [Name], [Value], [FieldId], NULL, NULL
                        FROM [SharedFields] 

                        UNION ALL 
                        SELECT [ItemId], 2 as [Order], 0 AS [Version],       [Language], NULL as [Name], [Value], [FieldId], NULL, NULL
                        FROM [UnversionedFields] 

                        UNION ALL 
                        SELECT [ItemId], 2 as [Order],      [Version],       [Language], NULL as [Name], [Value], [FieldId], NULL, NULL 
                        FROM [VersionedFields]
                     ) as temp  WHERE [ItemId] IN (SELECT [ID] FROM [Items] WITH (nolock)  WHERE [ParentID] = @itemId) 
                     ORDER BY [ItemId], [Order] ASC, [Language] DESC, [Version] DESC',N'@itemId uniqueidentifier',@itemId='734FB52F-33E3-4BCF-ADA9-27F1A4D8B4E1'" 

ExecNonQuery -conStr $master -cmdText "exec sp_executesql N'SELECT [ItemId], [Order], [Version], [Language], [Name], [Value], [FieldId], [MasterID], [ParentID]
                     FROM (
                        SELECT [Id] as [ItemId], 0 as [Order], 0 as [Version], '''' as [Language], [Name], '''' as [Value], [TemplateID] as [FieldId], [MasterID], [ParentID]
                        FROM [Items]

                        UNION ALL                          
                        SELECT [ParentId] as [ItemId], 1 as [Order], 0 as [Version], '''' as [Language], NULL as [Name], '''', NULL, NULL, [Id]
                        FROM [Items] 

                        UNION ALL 
                        SELECT [ItemId], 2 as [Order], 0 AS [Version], '''' as [Language], NULL as [Name], [Value], [FieldId], NULL, NULL
                        FROM [SharedFields] 

                        UNION ALL 
                        SELECT [ItemId], 2 as [Order], 0 AS [Version],       [Language], NULL as [Name], [Value], [FieldId], NULL, NULL
                        FROM [UnversionedFields] 

                        UNION ALL 
                        SELECT [ItemId], 2 as [Order],      [Version],       [Language], NULL as [Name], [Value], [FieldId], NULL, NULL 
                        FROM [VersionedFields]
                     ) as temp  WHERE [ItemId] IN (SELECT [ID] FROM [Items] WITH (nolock)  WHERE [ParentID] = @itemId) 
                     ORDER BY [ItemId], [Order] ASC, [Language] DESC, [Version] DESC',N'@itemId uniqueidentifier',@itemId='11A81FBC-77FB-40D9-A3B3-0078106B07FC'" 
 
write-Host "Query Complete!"
}

$stopWatch.Stop()

Write-Host "total " $stopWatch.ElapsedMilliseconds "ms"
Write-Host "average " ($stopWatch.ElapsedMilliseconds/$runs) "ms"

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

function ExecNonQuery

{

param ($conStr, $cmdText)

# Determine if parameters were correctly populated.

if (!$conStr -or !$cmdText)

{

# One or more parameters didn't contain values.

write-Host "ExecNonQuery function called with no connection string and/or command text."

}

else

{

write-Host "Creating SQL Connection..."

# Instantiate new SqlConnection object.

$Connection = New-Object System.Data.SQLClient.SQLConnection

# Set the SqlConnection object's connection string to the passed value.

$Connection.ConnectionString = $conStr

# Perform database operations in try-catch-finally block since database operations often fail.

try

{

#write-Host "Opening SQL Connection..."

# Open the connection to the database.

$Connection.Open()

#write-Host "Creating SQL Command..."

# Instantiate a SqlCommand object.

$Command = New-Object System.Data.SQLClient.SQLCommand

# Set the SqlCommand's connection to the SqlConnection object above.

$Command.Connection = $Connection

# Set the SqlCommand's command text to the query value passed in.

$Command.CommandText = $cmdText

#write-Host "Executing SQL Command..."

# Note, to see a result back here, ExecuteNonQuery has been changed to ExecuteScalar

write-host $Command.ExecuteScalar()

}

catch [System.Data.SqlClient.SqlException]

{

# A SqlException occurred. According to documentation, this happens when a command is executed against a locked row.

#write-Host "One or more of the rows being affected were locked. Please check your query and data then try again."

}

catch

{

# An generic error occurred somewhere in the try area.

write-Host "An error occurred while attempting to open the database connection and execute a command."

}

finally {

# Determine if the connection was opened.

if ($Connection.State -eq "Open")

{

write-Host "Closing Connection..."

# Close the currently open connection.

$Connection.Close()

}

# Specify SQL Connection String.

$master = "Data Source=###;Initial Catalog=Master;Integrated Security=SSPI;"

$core = "Data Source=###;Initial Catalog=Core;Integrated Security=SSPI;"

# Specify SQL Connection String.

$runs = 10

$stopWatch = [System.Diagnostics.Stopwatch]::StartNew()

for ($i=0; $i -le $runs; $i++)

{

ExecNonQuery -conStr $core -cmdText "SELECT COUNT (*) FROM aspnet_Users"

ExecNonQuery -conStr $core -cmdText "SELECT COUNT (*) FROM aspnet_UsersInRoles"

ExecNonQuery -conStr $core -cmdText "exec sp_executesql N'SELECT [ItemId], [Order], [Version], [Language], [Name], [Value], [FieldId], [MasterID], [ParentID]

FROM (

SELECT [Id] as [ItemId], 0 as [Order], 0 as [Version], '''' as [Language], [Name], '''' as [Value], [TemplateID] as [FieldId], [MasterID], [ParentID]

FROM [Items]

UNION ALL

SELECT [ParentId] as [ItemId], 1 as [Order], 0 as [Version], '''' as [Language], NULL as [Name], '''', NULL, NULL, [Id]

FROM [Items]

UNION ALL

SELECT [ItemId], 2 as [Order], 0 AS [Version], '''' as [Language], NULL as [Name], [Value], [FieldId], NULL, NULL

FROM [SharedFields]

UNION ALL

SELECT [ItemId], 2 as [Order], 0 AS [Version], [Language], NULL as [Name], [Value], [FieldId], NULL, NULL

FROM [UnversionedFields]

UNION ALL

SELECT [ItemId], 2 as [Order], [Version], [Language], NULL as [Name], [Value], [FieldId], NULL, NULL

FROM [VersionedFields]

) as temp WHERE [ItemId] IN (SELECT [ID] FROM [Items] WITH (nolock) WHERE [TemplateID]=@p0 OR [TemplateID]=@p1 OR [TemplateID]=@p2 OR [TemplateID]=@p3 OR [TemplateID]=@p4 OR [TemplateID]=@p5 OR [TemplateID]=@p6 OR [ID]=@p7 OR [ParentID]=@p8 OR [TemplateID]=@p9 OR [TemplateID]=@p10 OR [ParentID]=@p11)

ORDER BY [ItemId], [Order] ASC, [Language] DESC, [Version] DESC',N'@p0 uniqueidentifier,@p1 uniqueidentifier,@p2 uniqueidentifier,@p3 uniqueidentifier,@p4 uniqueidentifier,@p5 uniqueidentifier,@p6 uniqueidentifier,@p7 uniqueidentifier,@p8 uniqueidentifier,@p9 uniqueidentifier,@p10 uniqueidentifier,@p11 uniqueidentifier',@p0='AB86861A-6030-46C5-B394-E8F99E8B87DB',@p1='E269FBB5-3750-427A-9149-7AA950B49301',@p2='455A3E98-A627-4B40-8035-E683A0331AC7',@p3='239F9CF4-E5A0-44E0-B342-0F32CD4C6D8B',@p4='A87A00B1-E6DB-45AB-8B54-636FEC3B5523',@p5='F68F13A6-3395-426A-B9A1-FA2DC60D94EB',@p6='B6F7EEB4-E8D7-476F-8936-5ACE6A76F20B',@p7='11111111-1111-1111-1111-111111111111',@p8='11111111-1111-1111-1111-111111111111',@p9='EB06CEC0-5E2D-4DC4-875B-01ADCC577D13',@p10='B2A1FF6C-6B1F-41EF-B2E1-AA598CF7335F',@p11='C74AC643-53C8-4F1E-9508-840CDC72AACA'"

ExecNonQuery -conStr $master -cmdText "exec sp_executesql N'SELECT [ItemId], [Order], [Version], [Language], [Name], [Value], [FieldId], [MasterID], [ParentID]

FROM (

SELECT [Id] as [ItemId], 0 as [Order], 0 as [Version], '''' as [Language], [Name], '''' as [Value], [TemplateID] as [FieldId], [MasterID], [ParentID]

FROM [Items]

UNION ALL

SELECT [ParentId] as [ItemId], 1 as [Order], 0 as [Version], '''' as [Language], NULL as [Name], '''', NULL, NULL, [Id]

FROM [Items]

UNION ALL

SELECT [ItemId], 2 as [Order], 0 AS [Version], '''' as [Language], NULL as [Name], [Value], [FieldId], NULL, NULL

FROM [SharedFields]

UNION ALL

SELECT [ItemId], 2 as [Order], 0 AS [Version], [Language], NULL as [Name], [Value], [FieldId], NULL, NULL

FROM [UnversionedFields]

UNION ALL

SELECT [ItemId], 2 as [Order], [Version], [Language], NULL as [Name], [Value], [FieldId], NULL, NULL

FROM [VersionedFields]

) as temp WHERE [ItemId] IN (SELECT [ID] FROM [Items] WITH (nolock) WHERE [ParentID] = @itemId)

ORDER BY [ItemId], [Order] ASC, [Language] DESC, [Version] DESC',N'@itemId uniqueidentifier',@itemId='734FB52F-33E3-4BCF-ADA9-27F1A4D8B4E1'"

ExecNonQuery -conStr $master -cmdText "exec sp_executesql N'SELECT [ItemId], [Order], [Version], [Language], [Name], [Value], [FieldId], [MasterID], [ParentID]

FROM (

SELECT [Id] as [ItemId], 0 as [Order], 0 as [Version], '''' as [Language], [Name], '''' as [Value], [TemplateID] as [FieldId], [MasterID], [ParentID]

FROM [Items]

UNION ALL

SELECT [ParentId] as [ItemId], 1 as [Order], 0 as [Version], '''' as [Language], NULL as [Name], '''', NULL, NULL, [Id]

FROM [Items]

UNION ALL

SELECT [ItemId], 2 as [Order], 0 AS [Version], '''' as [Language], NULL as [Name], [Value], [FieldId], NULL, NULL

FROM [SharedFields]

UNION ALL

SELECT [ItemId], 2 as [Order], 0 AS [Version], [Language], NULL as [Name], [Value], [FieldId], NULL, NULL

FROM [UnversionedFields]

UNION ALL

SELECT [ItemId], 2 as [Order], [Version], [Language], NULL as [Name], [Value], [FieldId], NULL, NULL

FROM [VersionedFields]

) as temp WHERE [ItemId] IN (SELECT [ID] FROM [Items] WITH (nolock) WHERE [ParentID] = @itemId)

ORDER BY [ItemId], [Order] ASC, [Language] DESC, [Version] DESC',N'@itemId uniqueidentifier',@itemId='11A81FBC-77FB-40D9-A3B3-0078106B07FC'"

write-Host "Query Complete!"

}

$stopWatch.Stop()

Write-Host "total " $stopWatch.ElapsedMilliseconds "ms"

Write-Host "average " ($stopWatch.ElapsedMilliseconds/$runs) "ms"

blog.boro2g .co.uk

Some ideas about coding, dev and all things online.

Leave a Reply Cancel reply