| Database | Proc | Application | Created | Links |
| sybsystemprocs | sp_opt_querystats_main | | 31 Aug 14 | Defects Dependencies |
1 2 /* 3 ** SP_OPT_QUERYSTATS_MAIN 4 ** 5 ** This is the main stored procedure for the sp_opt_querystats utility. 6 ** This stored procedure handles verification of the conditions for 7 ** executing the optimizer diagnostic utility are present and then 8 ** creates and executes the Job Scheduler job to generate the diagnostic 9 ** information. Once the job has completed, this stored procedure 10 ** retrieves the results from the Job Scheduler database and returns 11 ** then to the caller. 12 ** 13 ** History: 14 ** 4/28/2011 pdorfman written 15 */ 16 create procedure sp_opt_querystats_main 17 @query varchar(16384), 18 @input_params varchar(2048) = "all", 19 @target_database varchar(30) = null, 20 @user_name varchar(30) = null 21 as 22 declare @job_name varchar(80) 23 declare @option_string varchar(4096) 24 declare @server_name varchar(256) 25 declare @jobid int 26 declare @ret int 27 declare @first_row int 28 declare @last_row int 29 declare @offset int 30 declare @options int 31 declare @begin int 32 declare @length int 33 declare @current_parameter varchar(50) 34 declare @current_option int 35 declare @input_length int 36 declare @position int 37 declare @command varchar(1024) 38 declare @optgoal_count int 39 declare @return_code int 40 declare @use_db_cmd varchar(50) 41 declare @exit_code int 42 declare @max_job_output int 43 declare @set_user_cmd varchar(60) 44 45 declare @statio_string varchar(30) 46 declare @stattime_string varchar(30) 47 declare @showplan_string varchar(30) 48 declare @missingstats_string varchar(30) 49 declare @resource_string varchar(30) 50 declare @plancost_string varchar(30) 51 declare @option_show_string varchar(30) 52 declare @exec_string varchar(30) 53 declare @option_show_long_string varchar(30) 54 declare @allrows_mix_string varchar(30) 55 declare @allrows_oltp_string varchar(30) 56 declare @allrows_dss_string varchar(30) 57 declare @diagmode_string varchar(30) 58 declare @option_all_string varchar(30) 59 declare @option_allexec_string varchar(30) 60 declare @showdata_string varchar(30) 61 declare @switches_string varchar(30) 62 declare @quoted_identifier_string varchar(30) 63 64 /* 65 ** The following variable definitions and assignments must correspond 66 ** exactly to those in sp_opt_querystats_job 67 */ 68 declare @query_evaluating_const int 69 declare @query_pending_const int 70 declare @query_finished_const int 71 72 declare @statio_const int 73 declare @stattime_const int 74 declare @showplan_const int 75 declare @missingstats_const int 76 declare @resource_const int 77 declare @plancost_const int 78 declare @option_show_const int 79 declare @noexec_const int 80 declare @option_show_long_const int 81 declare @allrows_mix_const int 82 declare @allrows_oltp_const int 83 declare @allrows_dss_const int 84 declare @diagmode_const int 85 declare @nodata_const int 86 declare @switches_const int 87 declare @quoted_identifier_const int 88 89 select @statio_const = 1 90 select @stattime_const = 2 91 select @showplan_const = 4 92 select @missingstats_const = 8 93 select @resource_const = 16 94 select @plancost_const = 32 95 select @option_show_const = 64 96 select @noexec_const = 128 97 select @option_show_long_const = 256 98 select @allrows_mix_const = 512 99 select @allrows_oltp_const = 1024 100 select @allrows_dss_const = 2048 101 select @diagmode_const = 4096 102 select @nodata_const = 8192 103 select @switches_const = 16384 104 select @quoted_identifier_const = 32768 105 106 select @query_pending_const = 0 107 select @query_evaluating_const = 1 108 select @query_finished_const = 2 109 /* 110 ** End common constant definitions 111 */ 112 113 select @statio_string = "statio" 114 select @stattime_string = "stattime" 115 select @showplan_string = "showplan" 116 select @missingstats_string = "missingstats" 117 select @resource_string = "resource" 118 select @plancost_string = "plancost" 119 select @option_show_string = "option_show" 120 select @exec_string = "exec" 121 select @option_show_long_string = "option_show_long" 122 select @allrows_mix_string = "allrows_mix" 123 select @allrows_oltp_string = "allrows_oltp" 124 select @allrows_dss_string = "allrows_dss" 125 select @diagmode_string = "diagmode" 126 select @option_all_string = "all" 127 select @option_allexec_string = "allexec" 128 select @showdata_string = "showdata" 129 select @switches_string = "switches" 130 select @quoted_identifier_string = "quoted_identifier" 131 132 select @options = 0 133 select @return_code = 0 134 135 if (@query = "help") 136 begin 137 print "sp_opt_querystats stored procedure" 138 print "" 139 print "Reports information about the optimization of a query." 140 print "" 141 print ' Usage: sp_opt_querystats "<query text>" | help [, "<diagnostic options>" | null [, <database name> [, <user name>]]]' 142 print "" 143 print " Where:" 144 print "" 145 print " <query text> (required) is the text of the query to be analyzed." 146 print "" 147 print " <diagnostic options> (optional) is a comma-separated list containing one or more of the following strings:" 148 print "" 149 print " - statio" 150 print " - stattime" 151 print " - showplan" 152 print " - missingstats" 153 print " - resource" 154 print " - plancost" 155 print " - showdata" 156 print " - exec" 157 print " - option_show_long" 158 print " - option_show" 159 print " - allrows_mix" 160 print " - allrows_oltp" 161 print " - allrows_dss" 162 print " - diagmode" 163 print " - switches" 164 print " - quoted_identifier" 165 print " - all" 166 print " - allexec" 167 print "" 168 print " Note that the values within the following lists are mutually exclusive. You can only specify one of these:" 169 print " {allrows_mix, allrows_oltp, allrows_dss}" 170 print " {option_show, option_show_long}" 171 print " {all, allexec}" 172 print "" 173 print " <database name> (optional) can be used to specify the name of the database in which the query should be executed. This is used when the table names in the query are not fully qualified. Note that all unqualified tables must be in a single database that is specified by this parameter." 174 print "" 175 print " <user name> (optional) can be used to specify the name of the user under which the query should be executed. The user must be a valid user in the database specified by the database name parameter." 176 print "" 177 print " Note that the database name parameter cannot be NULL if user name is specified." 178 print "" 179 print "For example:" 180 print "" 181 print ' sp_opt_querystats "select * from pubs2.dbo.authors"' 182 print "or" 183 print ' sp_opt_querystats "select * from pubs2.dbo.authors", "showplan,statio,option_show,plancost"' 184 print "or" 185 print ' sp_opt_querystats "select * from authors", "all", pubs2' 186 187 return (0) 188 end 189 190 /* 191 ** Verify that the user has sufficient permissions to 192 ** execute DBCC commands used by the job 193 */ 194 if (proc_role("sa_role") < 1) 195 begin 196 return - 1 197 end 198 199 /* 200 ** Verify that the user has the required Job Scheduler role 201 */ 202 if ((charindex("js_admin_role", show_role()) = 0) 203 and (charindex("js_user_role", show_role()) = 0)) 204 begin 205 /* 206 ** "ERROR: You must have either 'js_user_role' or 'js_admin_role' to run 207 ** this stored procedure." 208 */ 209 raiserror 17094 210 return - 1 211 end 212 213 /* 214 ** Verify that the Job Scheduler is enabled. Note that this does not 215 ** guarantee that the Job Scheduler is actually running. See the next check. 216 */ 217 if not exists (select 1 from master.dbo.syscurconfigs cc, master.dbo.sysconfigures sc 218 where cc.config = sc.config and cc.value = 1 219 and sc.comment = 'enable job scheduler') 220 begin 221 /* 222 ** "ERROR: The Job Scheduler is not enabled on this server. The Job 223 ** Scheduler must be enabled to execute this stored procedure. " 224 */ 225 raiserror 17095 226 return - 1 227 end 228 229 /* 230 ** Verify that the Job Scheduler is running 231 */ 232 if not exists (select 1 from master.dbo.sysprocesses where cmd = "JOB SCHEDULER") 233 begin 234 /* 235 ** "ERROR: The Job Scheduler is not running. Contact a system administrator 236 ** to resolve this problem." 237 */ 238 raiserror 17009 239 return - 1 240 end 241 242 /* 243 ** Parse the input parameters first and set the options bit mask 244 */ 245 if (@input_params is null) 246 begin 247 select @input_params = @option_all_string 248 end 249 250 /* 251 ** Enable nodata and noexec by default. These will 252 ** be disabled if the user specifies exec or showdata options. 253 */ 254 select @options = @nodata_const + @noexec_const 255 256 select @input_params = ltrim(rtrim(@input_params)) 257 select @input_length = char_length(@input_params) 258 select @length = @input_length 259 select @optgoal_count = 0 260 select @begin = 1 261 262 while (@length > 0) 263 begin 264 select @position = charindex(",", substring(@input_params, @begin, 265 (@input_length - @begin))) 266 267 if (@position != 0) 268 begin 269 select @length = @position - 1 270 select @current_parameter = rtrim(ltrim(substring(@input_params, @begin, 271 @length))) 272 end 273 else 274 begin 275 select @length = @input_length - @begin + 1 276 select @current_parameter = rtrim(ltrim(substring(@input_params, @begin, 277 @length))) 278 end 279 280 if (@current_parameter = @option_all_string 281 or @current_parameter = @option_allexec_string) 282 begin 283 select @options = @options 284 | @statio_const 285 | @stattime_const 286 | @showplan_const 287 | @missingstats_const 288 | @resource_const 289 | @plancost_const 290 | @option_show_long_const 291 | @switches_const 292 293 if (@current_parameter = @option_allexec_string) 294 begin 295 select @options = @options ^ @noexec_const 296 end 297 end 298 else if (@current_parameter = @statio_string) 299 select @options = @options | @statio_const 300 else if (@current_parameter = @stattime_string) 301 select @options = @options | @stattime_const 302 else if (@current_parameter = @showplan_string) 303 select @options = @options | @showplan_const 304 else if (@current_parameter = @missingstats_string) 305 select @options = @options | @missingstats_const 306 else if (@current_parameter = @resource_string) 307 select @options = @options | @resource_const 308 else if (@current_parameter = @plancost_string) 309 select @options = @options | @plancost_const 310 else if (@current_parameter = @option_show_string) 311 select @options = @options | @option_show_const 312 else if (@current_parameter = @switches_string) 313 select @options = @options | @switches_const 314 else if (@current_parameter = @quoted_identifier_string) 315 select @options = @options | @quoted_identifier_const 316 else if (@current_parameter = @exec_string) 317 -- Disable the default noexec option 318 select @options = @options ^ @noexec_const 319 else if (@current_parameter = @option_show_long_string) 320 select @options = @options | @option_show_long_const 321 else if (@current_parameter = @showdata_string) 322 -- Disable the default nodata option 323 select @options = @options ^ @nodata_const 324 else if (@current_parameter = @allrows_mix_string) 325 begin 326 select @options = @options | @allrows_mix_const 327 select @optgoal_count = @optgoal_count + 1 328 end 329 else if (@current_parameter = @allrows_oltp_string) 330 begin 331 select @options = @options | @allrows_oltp_const 332 select @optgoal_count = @optgoal_count + 1 333 end 334 else if (@current_parameter = @allrows_dss_string) 335 begin 336 select @options = @options | @allrows_dss_const 337 select @optgoal_count = @optgoal_count + 1 338 end 339 else if (@current_parameter = @diagmode_string) 340 select @options = @options | @diagmode_const 341 else 342 begin 343 /* 344 ** "ERROR: Invalid option parameter: '%1!'", @current_parameter 345 */ 346 raiserror 17013, @current_parameter 347 return - 1 348 end 349 350 select @begin = @begin + @length + 1 351 352 if (@begin > @input_length) 353 begin 354 break 355 end 356 end 357 358 if ((@options & @option_show_const != 0) 359 and (@options & @option_show_long_const != 0)) 360 begin 361 /* 362 ** "ERROR: Both '%1!' and '%2!' were specified. You can only use one of 363 ** these options at a time.", 364 */ 365 raiserror 17014, @option_show_string, @option_show_long_string 366 return - 1 367 end 368 369 if (@optgoal_count > 1) 370 begin 371 /* 372 ** "ERROR: You have specified more than one optmizer goal. Only one 373 ** optimizer goal can be specified at a time." 374 */ 375 raiserror 17049 376 return - 1 377 end 378 379 /* 380 ** Create the job that will be used to execute this query 381 */ 382 if (@@servername is null) 383 begin 384 /* 385 ** "ERROR: The @@servername variable is null. The server name must be set." 386 */ 387 raiserror 17071 388 return - 1 389 end 390 391 select @server_name = @@servername 392 393 /* 394 ** Create a unique job name and then create the job definition 395 */ 396 397 select @job_name = "opt_qstat_" + 398 convert(varchar(4), datepart(yy, getdate())) + 399 convert(varchar(2), datepart(mm, getdate())) + 400 convert(varchar(2), datepart(dd, getdate())) + 401 convert(varchar(2), datepart(hh, getdate())) + 402 convert(varchar(2), datepart(mi, getdate())) + 403 convert(varchar(2), datepart(ss, getdate())) + 404 convert(varchar(4), @@spid) 405 406 /* 407 ** Only create a use database command if the target_database is not null 408 */ 409 if (@target_database is not null) 410 begin 411 if not exists (select 1 from master.dbo.sysdatabases 412 where name = @target_database) 413 begin 414 415 /* 416 ** ERROR: Database '%1!' does not exist on this server.", @target_database 417 */ 418 raiserror 17097, @target_database 419 select @return_code = - 1 420 return - 1 421 end 422 423 select @use_db_cmd = "use " + @target_database + " go " 424 end 425 else 426 begin 427 select @use_db_cmd = "" 428 end 429 430 if (@user_name is not null) 431 begin 432 if (@target_database is null) 433 begin 434 /* 435 ** ERROR: The user name can only be specified if the database name is 436 ** also specified. 437 */ 438 raiserror 17099 439 return - 1 440 end 441 442 select @set_user_cmd = ' setuser "' + @user_name + '" go ' 443 end 444 else 445 begin 446 select @set_user_cmd = " " 447 end 448 449 select @option_string = "jcmd=select jobid = @js_sjobid into #odu1 go " 450 + @use_db_cmd 451 + @set_user_cmd -- must be executed after the user database command 452 + " declare @jid int select @jid = jobid from #odu1 exec sp_opt_querystats_job @jid,server=" 453 + @server_name 454 + ",sjproperties=shared_run" 455 456 exec @jobid = sybmgmtdb.dbo.sp_sjobcreate @name = @job_name, @option = @option_string 457 458 if (@jobid < 0) 459 begin 460 /* 461 ** "ERROR: Failed to create job '%1!' with option '%2!' with error code: %3!" 462 */ 463 raiserror 17073, @job_name, @option_string, @jobid 464 return - 1 465 end 466 467 /* 468 ** Determine whether the work table already exists. If it does 469 ** not, create it now. 470 */ 471 if not exists (select 1 from tempdb.dbo.sysobjects where name = @job_name) 472 begin 473 select @command = 474 "create table tempdb.." + @job_name + " " + 475 "( 476 status unsigned int, -- 0 = not executed; 1 = executing; 2 = executed 477 jobid int, 478 options unsigned int, 479 query text 480 )" 481 482 exec (@command) 483 end 484 485 if not exists (select 1 from tempdb.dbo.sysobjects where name = @job_name) 486 begin 487 /* 488 ** "ERROR: Creation of work table tempdb..%1! failed", @job_name 489 */ 490 raiserror 17072, @job_name 491 return - 1 492 end 493 494 /* 495 * Insert the query into the work table now that we have the jobid 496 */ 497 select @command = "insert tempdb.." + @job_name + " " + 498 "values (@query_pending_const, @jobid, @options, @query)" 499 exec (@command) 500 501 /* 502 ** Call the Job Scheduler to execute the analysis of the query 503 */ 504 exec @ret = sybmgmtdb.dbo.sp_sjobcontrol @name = @job_name, @option = "run_now" 505 506 if (@ret < 0) 507 begin 508 /* 509 ** "ERROR: An error occurred while executing the job. Job name: '%1!', 510 ** jobid: %2!. Error: %3!", 511 */ 512 raiserror 17074, @job_name, @jobid, @ret 513 select @return_code = - 1 514 goto cleanup 515 end 516 517 /* 518 ** Do not continue until the JS Agent completes handling job output 519 */ 520 while exists (select jsh_state from sybmgmtdb.dbo.js_history 521 where jsh_sjobid = @jobid 522 and jsh_state in ("W", "Q", "B", "R1", "R2")) 523 begin 524 waitfor delay "00:00:01" 525 end 526 527 /* 528 ** Pause to allow the Job Scheduler Agent to update the JS database 529 ** before removing the job history 530 */ 531 waitfor delay "00:00:05" 532 533 /* 534 ** Check the exit code for the job and raise an error if it was 535 ** not successful. 536 */ 537 select @exit_code = h.jsh_exit_code 538 from sybmgmtdb.dbo.js_history h 539 where h.jsh_sjobid = @jobid 540 541 if (@exit_code != 0) 542 begin 543 /* 544 ** ERROR: The job (jobid '%1!') failed with exit code '%2!'. Verify that 545 ** the current user has a valid externlogin defined for this server and 546 ** review the Job Scheduler agent log for any errors. 547 */ 548 raiserror 17098, @jobid, @exit_code 549 550 select @return_code = - 1 551 goto cleanup2 552 end 553 554 /* 555 ** Extract the output from the job from the Job Scheduler history 556 ** table and return it to the caller. 557 */ 558 select seq_no = o.jsout_seqno, 559 job_output = convert(varchar(901), 560 substring(o.jsout_text, 1, o.jsout_size)) 561 into #temp_history 562 from sybmgmtdb.dbo.js_output o, 563 sybmgmtdb.dbo.js_history h, 564 sybmgmtdb.dbo.js_scheduledjobs j 565 where j.sjob_id = h.jsh_sjobid 566 and h.jsh_sjobid = @jobid 567 and j.sjob_name = @job_name 568 and o.jsout_exid = h.jsh_exid 569 order by o.jsout_seqno asc 570 571 /* 572 ** Check to see whether there was any job output 573 ** This must immediately follow the select into #temp_history 574 */ 575 if (@@rowcount = 0) 576 begin 577 /* 578 ** "ERROR: No output found for job (jobid: '%1!'). The job may have failed 579 ** or the Job Scheduler may not be running. Contact a system administrator 580 ** to verify that the Job Scheduler is running and review the Job Scheduler 581 ** Agent log for errors.", 582 */ 583 raiserror 17076, @jobid 584 select @return_code = - 1 585 goto cleanup 586 end 587 588 /* 589 ** Locate the rows containing the begin and end markers and delete 590 ** all rows before and after these rows. They only contain progress 591 ** messages. 592 */ 593 select @first_row = seq_no 594 from #temp_history 595 where job_output like "%BEGIN QUERY ANALYSIS%" 596 597 select @last_row = seq_no 598 from #temp_history 599 where job_output like "%END QUERY ANALYSIS%" 600 601 /* 602 ** If the diagnostic results are not found report an error and display the 603 ** job output in case this contains useful information to determine why 604 ** the job did not execute as expected. 605 */ 606 if (@first_row is null or @last_row is null) 607 begin 608 select @max_job_output = cc.value 609 from master.dbo.syscurconfigs cc, master.dbo.sysconfigures sc 610 where cc.config = sc.config 611 and sc.comment = 'maximum job output' 612 613 /* 614 ** ERROR: The optimizer diagnostics were incomplete. This may be because 615 ** the value of the 'maximum job output' configuration parameter (%1!) is 616 ** smaller than the size of the diagnostic output. Consider increasing the 617 ** value of 'maximum job output'. 618 */ 619 raiserror 17077, @max_job_output 620 621 select @return_code = - 1 622 623 if (@options & @diagmode_const != 0) 624 begin 625 goto diagnostic_mode 626 end 627 628 goto cleanup 629 end 630 else if (@options & @diagmode_const = 0) 631 begin 632 delete #temp_history 633 where seq_no < @first_row 634 or seq_no > @last_row 635 636 /* 637 ** Update the rows containing the begin an end markers to 638 ** remove the text that lies before the beginning and 639 ** after the end markers. 640 */ 641 select @offset = patindex("%BEGIN QUERY ANALYSIS%", job_output) - 2 642 from #temp_history 643 where seq_no = @first_row 644 645 update #temp_history 646 set job_output = substring(job_output, @offset, (901 - @offset + 1)) 647 where seq_no = @first_row 648 649 select @offset = patindex("%END QUERY ANALYSIS%", job_output) + 20 650 from #temp_history 651 where seq_no = @last_row 652 653 update #temp_history 654 set job_output = substring(job_output, 1, (@offset - 1)) 655 where seq_no = @last_row 656 end 657 658 diagnostic_mode: 659 660 /* 661 ** Now return the output to the caller 662 */ 663 select job_output 664 from #temp_history 665 order by seq_no asc 666 667 cleanup: 668 669 drop table #temp_history 670 671 cleanup2: 672 673 /* 674 ** Drop the output after we have displayed it 675 */ 676 677 exec @ret = sybmgmtdb.dbo.sp_sjobhistory @name = @job_name, @option = "drop" 678 679 if (@ret != 0) 680 begin 681 /* 682 ** "ERROR: The call to sp_sjobhistory to drop job history '%1!' failed 683 ** with error code %2!", 684 */ 685 raiserror 17078, @job_name, @ret 686 select @return_code = - 1 687 end 688 689 exec @ret = sybmgmtdb.dbo.sp_sjobdrop @name = @job_name, @option = "all" 690 691 if (@ret != 0) 692 begin 693 /* 694 ** "ERROR: call to sp_sjobdrop to drop job '%1!' failed with error code %2!" 695 */ 696 raiserror 17096, @job_name, @ret 697 select @return_code = - 1 698 end 699 700 select @command = "drop table tempdb.." + @job_name 701 exec (@command) 702 703 return @return_code 704
exec sp_procxmode 'sp_opt_querystats_main', 'AnyMode' go Grant Execute on sp_opt_querystats_main to public go
| RESULT SETS | |
| sp_opt_querystats_main_rset_001 | |
| DEPENDENCIES |
| PROCS AND TABLES USED reads table master..sysprocesses (1)  reads table master..sysconfigures (1) reads table master..sysdatabases (1) read_writes table tempdb..#temp_history (1) reads table tempdb..sysobjects (1)  reads table master..syscurconfigs (1) CALLERS called by proc sybsystemprocs..sp_opt_querystats  |
