sql - Getting parameters and their values of a stored procedure via DMVs -

-

i have stored procedure using dmvs returns performance statistics on stored procedures. in using tool @ sp performance, found of complex stored procedures have multiple cached plans. stored procedures in question have lot of parameters , performance varies dramatically across cached plans, therefore, want examine how these stored procedures being called. parameters have values (other default).

is there way stored procedure command being called in result set via dmv?

for example, hope able see like: "exec myprocname @param1=value1, @param2=value2, @paramn=valuen"

the second question have on subject multiple instances of cached plans per stored procedure. thought there multiple plans if procedure called varying parameter values (enough variation change or generate new plan). on track here or way off base?

here definition of current stored proc uses sys.dm_exec_procedure_stats dmv:

alter procedure [dbo].[bss_stored_procedure_performance] @dt_start datetime = null ,@dt_end datetime = null ,@db_name varchar(100) = null ,@sp_name varchar(100) = null ,@sort_col varchar(25) = 'avg physical reads' ,@sort_asc bit = 0 ,@record bit = 0  begin  set nocount on;  if @dt_start null     begin         set @dt_start = current_timestamp - 3         set @dt_end = current_timestamp     end   select x.* ( select case when database_id = 32767 'resource' else db_name(database_id)end dbname ,object_schema_name(object_id,database_id) [schema]   ,object_name(object_id,database_id)as [obj name] ,type_desc [object type] ,cached_time [cached dt] ,last_execution_time [last execute dt] ,last_elapsed_time [last duration] ,cast(last_elapsed_time*.000001 decimal(9,2)) [last duration / seconds] ,execution_count [execution count] ,total_worker_time / execution_count [avg cpu] ,min_elapsed_time  [min duration] ,total_elapsed_time / execution_count [avg duration] ,max_elapsed_time [max duration] ,total_logical_reads / execution_count [avg logical reads] ,total_logical_writes / execution_count [avg logical writes] ,total_physical_reads  / execution_count [avg physical reads] sys.dm_exec_procedure_stats   last_execution_time between @dt_start , @dt_end , ((@db_name null) or (db_name(database_id) = @db_name)) , ((@sp_name null) or (object_name(object_id,database_id) '%' + @sp_name + '%')) ) x order case when @sort_col = 'avg physical reads' , @sort_asc = 1 [avg physical reads] end asc, case when @sort_col = 'avg physical reads' , @sort_asc = 0 [avg physical reads] end desc, case when @sort_col = 'last execute dt' , @sort_asc = 1 [last execute dt] end asc, case when @sort_col = 'last execute dt' , @sort_asc = 0 [last execute dt] end desc    print 'date range: ' + convert(varchar(50),@dt_start) + ' -- -- ' +  convert(varchar(50),@dt_end)


Comments

Post a Comment

Popular posts from this blog

java - Run a .jar on Heroku -

-
i trying run .jar file on heroku, , can't find instructions anywhere on how this. have read wants me compile uploading heroku. possible, , if is, how this? if helps, trying run minecraft craftbukkit server, , don't have pom.xml file yet. you didn't provide details i'll try give , people reaches question way it. first of all, create heroku account , create heroku application (assumed have done that). second, need generate application jar , upload heroku. so, open terminal , install heroku cli (assuming unix terminal): $ sudo npm install -g heroku-cli you need have npm , nodejs installed, can check link: https://www.digitalocean.com/community/tutorials/how-to-install-node-js-on-ubuntu-16-04 once done that, can use npm update node version acceptable heroku-cli: $ sudo npm install -g n $ sudo n 7.10.0 and run: $ sudo npm install -g heroku-cli in order deploy jars, need install deploy plugin: $ sudo heroku plugins:install heroku-cli-deploy ...
Read more

java - Jtable duplicate Rows -

-
Image
when add new rows in jtable duplicate rows many times this method update jtable public final void tableaucomande(){ ((defaulttablemodel)tabcommande.getmodel()).getdatavector().clear(); session s=hibernateutil.getsessionfactory().opensession(); criteria cr1= s.createcriteria(commande.class); list <commande> lcs=cr1.list() ; criteria cr2= s.createcriteria(lignecommande.class); list <lignecommande> lck=cr2.list() ; if(lcs.size()!=0 && lck.size()!=0) { (commande lc:lcs) { for(lignecommande lig:lck){ ((defaulttablemodel)tabcommande.getmodel()).addrow(new object[]{ lc.getid_cmd(),lc.getnumcmd(),lc.getdate_cmd(),lc.getclient().getnomprenom(), lig.getproduit().getlib_prd(),lig.getquantite(),lig.getproduit().getid_prd() }); } } // tabcommande.setrowselectioninterval(tabcommande.getrowcount()-1,tabcommande.getrowcount()-1); } } this code add add data rows displaye...
Read more

validation - How to pass paramaters like unix into windows batch file -

-
i need pass various parameters .cmd file unix format file.cmd -configuration=value -source=value -flag but, try this: startlocal @echo off cls setlocal set cmdline=%* set configuration= set source= set badargs= set validation= goto main :splitargs echo splitargs(%*) if "%*" neq "" ( /f "tokens=1,2,* delims== " %%i in ("%*") call :assignkeyvalue %%i %%j & call :splitargs %%k ) goto :eof :assignkeyvalue echo assignkeyvalue(%1, %2) if /i %1==-configuration ( set configuration=%2 ) else if /i %1==-source ( set source=%2 ) else ( rem append unrecognised [key,value] badargs echo unknown key %1 set badargs=%badargs%[%1, %2] ) goto :eof :validate echo validating set validation=fail if defined configuration ( echo -configuration ok if defined source ( echo -source ok if not defined badargs ( set validation=success ) ) ) goto :eof :main cl...
Read more