SQL Server Training | Tutorials | How to's

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 !!!