Execution time before and after index creation for query with params

  • ebooklub

    Hall of Fame

    Points: 3885

    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 1 week, 1 day ago by  ebooklub.
  • Phil Parkin

    SSC Guru

    Points: 243849

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

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

  • ebooklub

    Hall of Fame

    Points: 3885

    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

    1. 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

     

    Thank you

     

     

     

     

     

     

     

    • This reply was modified 1 week, 1 day ago by  ebooklub.
    • This reply was modified 1 week, 1 day ago by  ebooklub.
  • Phil Parkin

    SSC Guru

    Points: 243849

    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?

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

  • Grant Fritchey

    SSC Guru

    Points: 395653

    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

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • frederico_fonseca

    SSChampion

    Points: 14228

    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: 995144

    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;">

    1. 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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

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

  • ebooklub

    Hall of Fame

    Points: 3885

    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: 995144

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

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

  • Grant Fritchey

    SSC Guru

    Points: 395653

    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

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • ebooklub

    Hall of Fame

    Points: 3885

    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

    1. run query with all parameters assigned
    2. 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: 995144

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

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

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

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