ffarias Jul 15th, 2019 (edited) 94 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. query.txt
  3. IDQuery,QueryName,DBSpecific,Description
  4. 1,Version Info,FALSE,SQL and OS Version information for current instance
  5. 2,Core Counts,FALSE,"Get socket, physical core and logical core count from the SQL Server Error log."
  6. 3,Server Properties,FALSE,Get selected server properties
  7. 4,Configuration Values,FALSE,Get instance-level configuration values for instance
  8. 5,Global Trace Flags,FALSE,Returns a list of all global trace flags that are enabled
  9. 6,IFI Status,FALSE,Returns status of instant file initialization
  10. 7,Process Memory,FALSE,SQL Server Process Address space info
  11. 8,SQL Server Services Info,FALSE,SQL Server Services information
  12. 9,Last Backup By Database,FALSE,Last backup information by database
  13. 10,SQL Server Agent Jobs,FALSE,Get SQL Server Agent jobs and Category information
  14. 11,SQL Server Agent Alerts,FALSE,Get SQL Server Agent Alert Information
  15. 12,Host Info,FALSE,Host information
  16. 13,SQL Server NUMA Info,FALSE,SQL Server NUMA Node information
  17. 14,System Memory,FALSE,Good basic information about OS memory amounts and state
  18. 15,Cluster Node Properties,FALSE,Get information about your cluster nodes and their status
  19. 16,AlwaysOn AG Cluster,FALSE,Get information about any AlwaysOn AG cluster this instance is a part of
  20. 17,AlwaysOn AG Status,FALSE,Good overview of AG health and status
  21. 18,Hardware Info,FALSE,Hardware information from SQL Server 2017
  22. 19,System Manufacturer,FALSE,Get System Manufacturer and model number from SQL Server Error log
  23. 20,PVSCSI Driver Parameters,FALSE,Get pvscsi info from Windows Registry
  24. 21,BIOS Date,FALSE,Get BIOS date from Windows Registry
  25. 22,Processor Description,FALSE,Get processor description from Windows Registry
  26. 23,BPE Configuration,FALSE,See if buffer pool extension (BPE) is enabled
  27. 24,BPE Usage,FALSE,Look at buffer descriptors to see BPE usage by database
  28. 25,Memory Dump Info,FALSE,"Get information on location, time and size of any memory dumps from SQL Server "
  29. 26,Suspect Pages,FALSE,Look at Suspect Pages table
  30. 27,TempDB Data Files,FALSE,Get number of data files in tempdb database
  31. 28,Database Filenames and Paths,FALSE,File names and paths for all user and system databases on instance
  32. 29,Fixed Drives,FALSE,Drive information for all fixed drives visible to the operating system
  33. 30,Volume Info,FALSE,Volume info for all LUNS that have database files on the current instance
  34. 31,Drive Level Latency,FALSE,Drive level latency information
  35. 32,IO Latency by File,FALSE,"Calculates average stalls per read, per write, and per total input/output for each database file "
  36. 33,IO Warnings,FALSE,Look for I/O requests taking longer than 15 seconds in the six most recent SQL Server Error Logs
  37. 34,RG Resource Pools,FALSE,Resource Governor Resource Pool information
  38. 35,Database Properties,FALSE,"Recovery model, log reuse wait description, log file size, log usage size "
  39. 36,Missing Indexes All Databases,FALSE,Missing Indexes for all databases by Index Advantage
  40. 37,VLF Counts,FALSE,Get VLF Counts for all databases on the instance
  41. 38,CPU Usage by Database,FALSE,Get CPU utilization by database
  42. 39,IO Usage By Database,FALSE,Get I/O utilization by database
  43. 40,Total Buffer Usage by Database,FALSE,Get total buffer usage by database for current instance
  44. 41,Version Store Space Usage,FALSE,Get tempdb version store space usage by database
  45. 42,Top Waits,FALSE,Isolate top waits for server instance since last restart or wait statistics clear
  46. 43,Connection Counts by IP Address,FALSE,Get a count of SQL connections by IP address
  47. 44,Avg Task Counts,FALSE,Get Average Task Counts (run multiple times)
  48. 45,Detect Blocking,FALSE,Detect blocking (run multiple times)
  49. 46,CPU Utilization History,FALSE,Get CPU Utilization History for last 256 minutes (in one minute intervals)
  50. 47,Top Worker Time Queries,FALSE,Get top total worker time queries for entire instance
  51. 48,PLE by NUMA Node,FALSE,Page Life Expectancy (PLE) value for each NUMA node in current instance
  52. 49,Memory Grants Pending,FALSE,Memory Grants Pending value for current instance
  53. 50,Memory Clerk Usage,FALSE,Memory Clerk Usage for instance
  54. 51,Ad hoc Queries,FALSE,"Find single-use, ad-hoc and prepared queries that are bloating the plan cache "
  55. 52,Top Logical Reads Queries,FALSE,Get top total logical reads queries for entire instance
  56. 53,Top Avg Elapsed Time Queries,FALSE,Get top average elapsed time queries for entire instance
  57. 54,UDF Stats by DB,TRUE,Look at UDF execution statistics
  58. 55,File Sizes and Space,TRUE,Individual File Sizes and space available for current database
  59. 56,Log Space Usage,TRUE,Log space usage for current database
  60. 57,Last VLF Status,TRUE,Status of last VLF for current database
  61. 58,Database-scoped Configurations,TRUE,Get database scoped configuration values for current database
  62. 59,IO Stats By File,TRUE,I/O Statistics by file for the current database
  63. 60,Query Execution Counts,TRUE,Get most frequently executed queries for this database
  64. 61,SP Execution Counts,TRUE,Top Cached SPs By Execution Count
  65. 62,SP Avg Elapsed Time,TRUE,Top Cached SPs By Avg Elapsed Time
  66. 63,SP Worker Time,TRUE,Top Cached SPs By Total Worker time. Worker time relates to CPU cost
  67. 64,SP Logical Reads,TRUE,Top Cached SPs By Total Logical Reads. Logical reads relate to memory pressure
  68. 65,SP Physical Reads,TRUE,Top Cached SPs By Total Physical Reads. Physical reads relate to disk read I/O pressure
  69. 66,SP Logical Writes,TRUE,Top Cached SPs By Total Logical Writes
  70. 67,Top IO Statements,TRUE,Lists the top statements by average input/output usage for the current database
  71. 68,Bad NC Indexes,TRUE,Possible Bad NC Indexes (writes > reads)
  72. 69,Missing Indexes,TRUE,Missing Indexes for current database by Index Advantage
  73. 70,Missing Index Warnings,TRUE,Find missing index warnings for cached plans in the current database
  74. 71,Buffer Usage,TRUE,"Breaks down buffers used by current database by object (table, index) in the buffer cache "
  75. 72,Table Sizes,TRUE,"Get Table names, row counts, and compression status for clustered index or heap "
  76. 73,Table Properties,TRUE,Get some key table properties
  77. 74,Statistics Update,TRUE,When were Statistics last updated on all indexes?
  78. 75,Volatile Indexes,TRUE,Look at most frequently modified indexes and statistics
  79. 76,Index Fragmentation,TRUE,Get fragmentation info for all indexes above a certain size in the current database
  80. 77,Overall Index Usage - Reads,TRUE,Index Read/Write stats (all tables in current DB) ordered by Reads
  81. 78,Overall Index Usage - Writes,TRUE,Index Read/Write stats (all tables in current DB) ordered by Writes
  82. 79,XTP Index Usage,TRUE,Get in-memory OLTP index usage
  83. 80,Columnstore Index Physical Stat,TRUE,Look at Columnstore index physical statistics
  84. 81,Lock Waits,TRUE,Get lock waits for current database
  85. 82,UDF Statistics,TRUE,Look at UDF execution statistics
  86. 83,QueryStore Options,TRUE,Get QueryStore Options for this database
  87. 84,High Aggregate Duration Queries,TRUE,Get highest aggregate duration queries over last hour
  88. 85,High Aggregate CPU Queries,TRUE,Get highest aggregate CPU time queries over last hour
  89. 86,Input Buffer,TRUE,Get input buffer information for the current database
  90. 87,Resumable Index Rebuild,TRUE,Get any resumable index rebuild operation information
  91. 88,Automatic Tuning Options,TRUE,Get database automatic tuning options
  92. 89,Recent Full Backups,TRUE,Look at recent Full backups for the current database
  94. #########################################################################
  96. $file = Import-Csv C:\ORAMIX\servidores.TXT
  97. $file2 = Import-Csv C:\ORAMIX\query.txt
  100. #Get-Content -Path C:\CONTISYSTEMS\servidores.txt | SELECT INSTANCIA
  101. Clear-Host
  103. #Client Name
  105. $Client = Read-Host -Prompt 'Insert Client Name'
  107. #Create Directory
  108. $Path_Instance = 'C:\ORAMIX\'+$Client.ToUpper()+'\IN\MapeamentoInstanceOnlyCSV\'
  109. $Path_Database = 'C:\ORAMIX\'+$Client.ToUpper()+'\IN\MapeamentoDatabaseOnlyCSV\'
  112. if (-not(Test-Path -Path $Path_Instance -PathType Container))
  113. {
  114.     New-Item -ItemType directory -Path $Path_Instance
  115. }
  116. if (-not(Test-Path -Path $Path_Database -PathType Container))
  117. {
  118.     New-Item -ItemType directory -Path $Path_Database
  119. }
  121. $cred = Get-Credential ff.oramix
  124. ForEach ($item in $file)
  125. {
  126.     ForEach ($item2 in $file2)
  127.     {
  128.         $Path_Instance_GET = $Path_Instance + $item2.QueryName.Replace(" ","_").ToLower()
  129.         $Path_Database_GET = $Path_Database + $item2.QueryName.Replace(" ","_").ToLower()
  130.         if ($item2.DBSpecific -eq 'FALSE')
  131.         {
  132.             #INSTANCE
  134.             Invoke-DbaDiagnosticQuery -SqlCredential $cred -SqlInstance $item.instance -InstanceOnly -QueryName $item2.QueryName -Verbose 4>> $Path_Instance+"Save-Verbose.txt" | Export-DbaDiagnosticQuery -NoPlanExport -NoQueryExport -Path $Path_Instance_GET
  136.         }
  137.         else
  138.         {
  139.             #DATABASE
  141.             Invoke-DbaDiagnosticQuery -SqlCredential $cred -SqlInstance $item.instance -DatabaseSpecific -QueryName $item2.QueryName -Verbose 4>> "C:\ORAMIX\MapeamentoDatabaseOnlyCSV\Save-Verbose.txt"  | Export-DbaDiagnosticQuery -NoPlanExport -NoQueryExport -Path $Path_Database_GET
  143.         }
  144.     }
  145. }
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand