Today, I have a very interesting scenario while I was working with my client on the Comprehensive Database Performance Health Check. Let us today talk about SSMS Query Execution configuration for SQL Server where SET ROWCOUNT settings gave us an interesting challenge with query tuning.
When we were working with on tuning their system, every single time when we executed the query to tune in SSMS, we kept on getting an execution plan which as per my opinion was very harmless. However, whenever we ran the same query from the application it took a longer period of time. After careful observation of multiple runs of the same query with a different execution plan, we realized that every time when we ran the query in SSMS, we got around 100 rows in the result but whenever we ran the same via application we got a different amount of the rows and most of the time over 100.
After realizing that SSMS which we were using at the client’s machine is giving us 100 only records, we immediately run the same query with different machines to realize that the query was indeed giving different execution plans and also more number of rows. This leads us to believe that there is some kind of local configuration settings in the SSMS which we were using is leading us to give us result with fixed 100 rows.
Once we realized it was easy for us. We opened SQL Server Management Studio (SSMS) and opened Options from the Tools menu bar. Over here we noticed that the value of SET ROWCOUNT was around 100. This was a problem for us. Essentially for all the new connections on that SSMS, it was limiting the row count by running the SET ROWCOUNT 100 before any query runs. This not only affected the query results but also the execution plan.
Once we realized the issue, we changed that to zero and after that, every single query started to work as it should be with the correct number of rows and relevant execution plans.
Honestly, working with Comprehensive Database Performance Health Check is indeed a lot of fun as I get to learn new things very often.
Reference: Pinal Dave (http://blog.SQLAuthority.com)
Interesting observations. I have never encountered a problem with the rowcount setting yet but will definitly remember to check it the next time I have an error. Thank you for the tips!