Query optimization

Question

Post reply

Query optimization

ram302

SSCommitted

Points: 1618
More actions
November 17, 2016 at 3:06 pm

#337394

Hi all,
I've been searching for a tool that would take a query (sql server) and generate alternatives and execute each x number of times so that you can see its performance and sort by that. I remember using something exactly like this at my previous job for an Oracle db. Anyone know what I can use for SQL Server 2014?
Backstory: The query is part of a stored proc, and was running ok in SQL Server 2012. The system was recently upgraded to 2014, and the s.proc is taking so much longer than before.
I've tried:
- changing the order of the joins, putting the direct joins (1 to 1) on top.
- removing the TOP 50000 from the query.
I know it's not much that I've tried, but I'm not sure what else to try. I've come across the use of SQL Server Profiler, to execute the s. proc as a privileged user, but I'd rather not have to ask for admin credentials. And I've also seen where you can specify to execute the query under a previous SQL Server build performance, but this requires a privileged user also.
What am I missing here?
Thanks in advance,
-Rudy.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 1

I'm not aware of any proper query re-writing tool that can handle anything more than relatively basic queries.

😎

If you can get the actual execution plan then that is a good place to start.

Grant Fritchey SSC Guru Points: 398886 More actions · Answer 2

Quest had a tool like that once upon a time. I think it's long been discontinued.

For testing the running of a procedure, it's not hard to write a quick PowerShell script to do it. Here's a very rough example that I used recently to test a series of values against a query:

[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null

# Get the connection

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection

$SqlConnection.ConnectionString = 'Server=WIN-3SRG45GBF97\DOJO;Database=WideWorldImporters;trusted_connection=true'

$BillToCustomerCmd = New-Object System.Data.SqlClient.SqlCommand

$BillToCustomerCmd.CommandText = "SELECT DISTINCT i.BillToCustomerID

FROM Sales.Invoices as i;"

$BillToCustomerCmd.Connection = $SqlConnection

$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter

$SqlAdapter.SelectCommand = $BillToCustomerCmd

$BillToCustomerList = New-Object System.Data.DataSet

$SqlAdapter.Fill($BillToCustomerList)

$SQLCmd = New-Object System.Data.SqlClient.SqlCommand

$SQLCmd.Connection = $SqlConnection

$SQLCmd.CommandText = "DECLARE @sqlquery NVARCHAR(MAX);

SET @sqlquery

= N'SELECT si.StockItemName,

i.InvoiceDate,

i.SalespersonPersonID

FROM Sales.Invoices AS i

JOIN Sales.InvoiceLines AS il

ON il.InvoiceID = i.InvoiceID

JOIN Warehouse.StockItems AS si

ON si.StockItemID = il.StockItemID

WHERE i.BillToCustomerID = @BillToCustomerID;';

DECLARE @parms NVARCHAR(MAX);

SET @parms = '@BillToCustomerID int';

EXEC sys.sp_executesql @stmt = @sqlquery,

@params = @parms,

@BillToCustomerID = @btc;"

$SQLCmd.Parameters.Add("@btc",[System.Data.SqlDbType]"Int")

foreach($row in $BillToCustomerList.Tables[0])

{

$SqlConnection.Open()

$SQLCmd.Parameters["@btc"].Value = $row[0]

$SQLCmd.ExecuteNonQuery() | Out-Null

$sqlconnection.Close()

}

The ExecuteNonQuery command runs the query, but ignores the result set. This makes it possible to ignore any kind of network latencies that could interfere with testing. If you want to include those in the test, just change this to ExecuteQuery.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

HanShi SSC-Dedicated Points: 33507 More actions · Answer 3

ram302 (11/17/2016)
...Backstory: The query is part of a stored proc, and was running ok in SQL Server 2012. The system was recently upgraded to 2014, and the s.proc is taking so much longer than before.
...

In SQL 2014 the cardinality optimizer has been redesigned (see SQL Server 2014’s new cardinality estimator[/url]). This could lead to different performance compared to SQL 2012. Try executing the query under the "old" cardinality to check if the performance degration is related to this cardinality. See the linked article for ways to control the cardinality estimator.

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

ram302 SSCommitted Points: 1618 More actions · Answer 4

@HanShi, thanks for the link to the article. I think this may be it in my case, so I'll be reviewing this further.

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 5

Quick question, after the upgrade, did you update all stats etc.? Are the server platforms identical? Same server settings (maxdop, cost threshold for parallelism etc.)?

😎

HanShi SSC-Dedicated Points: 33507 More actions · Answer 6

All settings remain the same. Stats were updated with the regular maintenance tasks.

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **