When looking at the performance of your system, you may take differing approaches to diagnose what you are experiencing. In this Blog I am not here to tell you how you should go about that, rather I am sharing a piece of work that I have undertaken to help diagnose 1 part of the Performance bottleneck being experienced.
After running some Perfmon traces against the required Instances of SQL Server, the analysis indicated a large number of Forwarded Records /sec. I hear you asking what is Forwarded Records / sec. Paul Randal has a really good Blog on this for further reading. The bottom line is this can have a large impact with your queries having to perform far more I/O’s than required, As I/O is probably the biggest factoring part to the performance of your system you want to ensure your system is not performing any unnecessary I/O.
Using Perfmon I included the Access Methods Counter – Forwarded Records/sec. Upon analysis of the resultant files found that 3 of the 5 Instances had rather high counts of Forwarded Records/sec. I set about working out how to determine which tables in the SQL Server Instances were the culprits for this performance hit.
I put together this SQL Script to run to give me the tables to go looking at to try and rectify the situation.
Normally I do not advocate the use of Cursor’s but I have used them in this situation rather than using sp_MSForEachTable as the select statement in the cursor gives you more flexibility with regards to the tables that you which to check for the Forwarded Records/Sec. Be very careful with your select statement in your cursor as this will have a big impact on the number of tables that you put through the Physical Index Stats DMV.
I have left the Where condition at >= 0 to pick up everything but if you are just checking Heap Tables then change this to a 0.
The results from this will lead you to the tables that are causing your I/O issues with regards to the Forwarded Records/sec. You can look at rectifying the issue by creating some appropriate Clustered Indexes once you have tested and are confident the benefits outweigh the negatives to adding your new Clustered Indexes.
I hope that you have found this Blog interesting and helpful. All comments are welcome.
Leave a Reply