 |
| Troubleshooting performance issues |
A lot of times, user complain about high resource utilization on the instance or slow response times, being most of the times, not configuration issues, but bad designed queries).
One of the most common causes is not using a search argument (or SARGs) and therefore not using a full benefit of an index, if you have an index created of course!
Database engine cannot do miracles by itself, you must be careful on what you ask it to do.
So let’s take a look of different approach for the same query: Orders from a year ago (assume getdate() as Jun 01, 2015, because I don’t have data for last year on my test db)
First option: Make the function in the table field, you can see that you use an index scan, in the details, it reads 31K+ rows to obtain only 34
 |
| Don't do this, don't be that guy! |
 |
| An index seek is used now |
 |
| CPU usage improvement |
Ok, that is nice, we can see that we have to make all the function and logic stuff from the parameter side, in order to have a good query, but unfortunately, even If your query is optimal, it will be useless if you don’t have the correct index created.
In this example, I have disabled the date index and executed the last query: you can see that the execution plan changed and for this particular query, the CPU usage is bad, even when the query is optimized.
 |
| IDE gives you an alert of a missing index |
 |
Even when for these 3 examples they perform similar in a test machine, on large environments having tables with millions of rows and high concurrency, these IO and CPU differences can make a huge difference.
source:
https://docs.microsoft.com/en-us/sql/t-sql/data-types/datetime-transact-sql
Comments
Post a Comment