SQL SCRIPT to identify Bottleneck / Diagnostic Information Queries

Leave a comment (0) Go to comments

I was thinking for building a consolidated Script to to identify performance bottlenecks, where I wanted to looks at following information :

  1. SQL and OS Version information for current instance
  2. Hardware information
  3. System Manufacturer and model number and processor description
  4. SQL Server configuration
  5. File Names and Paths for TempDB and all user databases
  6. Recovery model, log reuse wait description, log file size, log usage size and compatibility level
  7. Average stalls per read, per write, and per total input/output for each database file
  8. CPU utilization by database
  9. Buffer usage by database
  10. How much memory (in the buffer pool) is being used by each database
  11. Top waits for server instance
  12. Signal Waits information
  13. logins that are connected and how many sessions they have
  14. Average Task Counts
  15. Get CPU Utilization History for last 256 minutes
  16. Good basic information about memory amounts and state
  17. SQL Server Process Address space info
  18. Page Life Expectancy (PLE) value for default instance
  19. Memory Grants Pending value
  20. Memory Clerk Usage
  21. Find single-use, ad-hoc queries that are bloating the plan cache
    1. – Gives you the text and size of single-use ad-hoc queries that waste space in the plan cache
    2. – Enabling ‘optimize for ad hoc workloads’ for the instance can help (SQL Server 2008 and 2008 R2 only)
    3. – Enabling forced parameterization for the database can help, but test first!
  22. Individual File Sizes and space available for current database
  23. How large and how full the files are and where they are located
    1. Make sure the transaction log is not full!!
  24. I/O Statistics by file for the current database

- This helps you characterize your workload better from an I/O perspective

  1. Get VLF count for transaction log for the current database,
    1. Lower the Counter is better
  2. Top Cached SPs By Execution Count
  3. Tells you which cached stored procedures are called the most often
  4. Top Cached SPs By Avg Elapsed Time
  5. helps you find long-running cached stored procedures that may be easy to optimize with standard query tuning techniques
  6. Top Cached SPs By Total Worker time (SQL 2008). Worker time relates to CPU cost
  7. Top Cached SPs By Total Logical Reads (SQL 2008). Logical reads relate to memory pressure
  8. Find the most expensive cached stored procedures from a memory perspective
  9. Top Cached SPs By Total Physical Reads (SQL 2008). Physical reads relate to disk I/O pressure
  10. Find the most expensive cached stored procedures from a read I/O perspective
  11. Top Cached SPs By Total Logical Writes
  12. find the most expensive cached stored procedures from a write I/O perspective
  13. Lists the top statements by average input/output usage for the current database
  14. find the most expensive statements for I/O by SP
  15. Possible Bad NC Indexes (writes > reads)
  16. indexes with high numbers of writes and zero or very low numbers of reads
  17. Missing Indexes current database by Index Advantage
  18. Find missing index warnings for cached plans in the current database
  19. missing indexes to specific stored procedures
  20. Breaks down buffers used by current database by object (table, index) in the buffer cache
  21. what tables and indexes are using the most memory in the buffer cache
  22. Get Table names, row counts, and compression status for clustered index or heap
  23. When Statistics were last updated on all indexes?
  24. Get fragmentation info for all indexes above a certain size in the current database?
  25. Index Read/Write stats (all tables in current DB)
  26. Show which indexes in the current database are most active

I was amazed to see that a senior SQL Server Community member "Glenn Berry" has already posted a great script, which helps you to get all this information.

You can download this Script freely here . As per Mr. Glenn Berry, it’s recommend that you run each query separately, after you read the instructions and comments on how to interpret the results. The first half of the queries focus on your SQL Server instance, while the second half focuses on a specific SQL Server database on that instance.

SQL Server 2008 Diagnostic Information Queries.

Once you analyzing data which is being captured by above mentioned script, identify bottleneck and resolving that bottleneck.

Once that bottleneck is resolve, you CPU / Memory will say thank you to you !!!

SQL SCRIPT to identify Bottleneck / Diagnostic Information Queries sql server performence tuning script sql server Diagnostic Information Queries

EOF - SQL SCRIPT to identify Bottleneck / Diagnostic Information Queries, SQL Server 2012 , 2008 R2 , 2008 , 2005 , 2000

Leave a Reply

Your email address will not be published. Required fields are marked *


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Notify me of followup comments via e-mail. You can also subscribe without commenting.