Execution time before and after index creation for query with params

Question

Execution time before and after index creation for query with params

ebooklub

Hall of Fame

Points: 3905
More actions
December 2, 2019 at 9:34 pm

#3703607
Hi
Hi,
example
select Order_Id from Orders where item_Id in (@P1, @P2, @P3 ... P103) and Category in (@P110, ...@P120)
I want to compare actual query execution time before and after index creation .
How to get it without assigning values to all 120 parameters (@P1... P120)
Than you
- This topic was modified 4 years, 11 months ago by ebooklub.

Viewing 12 posts - 1 through 11 (of 11 total)

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

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

Your post is difficult to understand, can you explain in more detail?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

ebooklub Hall of Fame Points: 3905 More actions · Answer 2

Query store capture long running query for last 1 hr period in following format

(@P1 bigint, @P2 bigint, @P3 bigint, ...@P120 )select Order_Id from Orders where item_Id in (@P1, @P2, @P3 ... P103) and Category in (@P110, ...@P120)

and show missing index (create index ....)

Query store return following info when you highlight long running query

Query id = 411111

Total Duration = 864563.4 ms

Execution count = 45

Plan count 1

Total duration/execution count provide me ~ query duration before index created

without providing values for all 120 parameters I want to re execute the query in SSMS query window and get query execution time and compare it with with values from Query store Total duration/execution count

2. I want to create missing index and without providing values for all 120 parameters I want to execute the query in SSMS query window and get query execution time after index creation

Thank you

This reply was modified 4 years, 11 months ago by ebooklub.
This reply was modified 4 years, 11 months ago by ebooklub.

Phil Parkin SSC Guru Points: 246960 More actions · Answer 3

So you want to be able to capture the text of the actual T-SQL query which was executed so that you can paste into a query window in SSMS and execute it interactively?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 4

In order to measure the performance of the query, you'll need all 150 parameters (or however many are required and don't have defaults). There's no escaping this.

So, probably, best bet is to capture the execution of the query including parameter values. For this, I'd suggest using Extended Events. rpc_completed since this sounds like a stored procedure. That will have the execution text, including parameter values.

Now, if you really, really, don't want to do that, you have another option. Get the execution plan from the Query Store. Open the first operator (SELECT or whatever) and look at the properties. There, you'll find the compile values for every parameter. You can use those to execute the query.

"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

frederico_fonseca SSCoach Points: 16154 More actions · Answer 5

and I would rethink the way the query is built

with so many parameters it would probably be better to create a temp table, populate it from the parameters and then use the table on the join (2 temp tables here so it seems)

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 6

ebooklub wrote:

Query store capture long running query for last 1 hr period in following format
(@P1 bigint, @P2 bigint, @P3 bigint, ...@P120 )select Order_Id from Orders where item_Id in (@P1, @P2, @P3 ... P103) and Category in (@P110, ...@P120)
and show missing index (create index ....)
Query store return following info when you highlight long running query
Query id = 411111
Total Duration = 864563.4 ms
Execution count = 45
Plan count 1
Total duration/execution count provide me ~ query duration before index created

<li style="list-style-type: none;">
<li style="list-style-type: none;">
without providing values for all 120 parameters I want to re execute the query in SSMS query window and get query execution time and compare it with with values from Query store Total duration/execution count

2. I want to create missing index and without providing values for all 120 parameters I want to execute the query in SSMS query window and get query execution time after index creation

Ummm... all of the parameters are for just two columns. You can easily write a bit of code to do that for you.

Also, someone really needs to get a grip on themselves... there are a lot better methods to pass what is essentially a lookup table. I don't know what is determining the list of 120 items to lookup but there has to be a way to do that in a stored procedure, which would make this code absolutely fly.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ebooklub Hall of Fame Points: 3905 More actions · Answer 7

Thanks a lot for suggestions

Role and permission of dba in this project allow to use Query store (no extended events)

Dba might suggest and if needed create new indexes (preferably providing execution stat before and after creation as proof

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 8

It would be of great benefit, like I said, to converse with the Developers of the code and see if you can find out how they're creating the values for the parameters. If you can do that and turn this into a stored procedure, things will run a shedload faster (especially with the correct indexing) and it will prevent a whole lot of unnecessary network traffic.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 9

ebooklub wrote:

Thanks a lot for suggestions
Role and permission of dba in this project allow to use Query store (no extended events)
Dba might suggest and if needed create new indexes (preferably providing execution stat before and after creation as proof

I agree with Jeff, talk to people about what they're doing and why. However, also talk to your DBAs. Even if they don't give you permission to capture some queries using Extended Events, they should be able to do that for you and feed you the necessary data. Or, you can do it yourself in your development environment (and if you don't have the necessary permission in development, your DBAs are wrong). All these tools exist and should be used to help us in our endeavors. Weird restrictions (in Dev, not in Prod, they're not weird in prod, but they can be dealt with) against using valid tools to solve problems is just dumb.

"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

ebooklub Hall of Fame Points: 3905 More actions · Answer 10

Hi, thank a lot for all suggestions,

close connection/work with dev team is good ,but I am adapting to reality 🙂

Project is running in SQL azure ,I posted topic in 2016 because in both cases we can use Query Store and (Azure forum does not get that much attention as on premises servers)

Reality...

Production support DBA L1 get alert notifications about DTU spike and based on monitoring tools providing "workarounds"

by creating new indexes or adjusting DTU (preferably within 15-20 minutes after alert...)

My goal is to make sure L1 DBA check execution stats before and after index creation and submit this info to product support

when I see DBA notes "before index creation it took 0.5 seconds to execute query and after creation it is 0.05 seconds" I am not sure that getting correct stats ... since no way they will copy paste complied param values for all 190 parameters

I want them to provide realistic execution time, I/O for both cases

Since Query statistic intervals set to 1 hr ( DBA are not allowed change in prod) I see few options to see if index working

run query with all parameters assigned
query sys.query_store_query (but if query might used 100000 times between 6 and 7 am and not used in next several hours)

Thank you

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 11

ebooklub wrote:

close connection/work with dev team is good ,but I am adapting to reality

I guess I'll have to introduce you to the idea of "Informative Pork Chop Dinners". 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)