Have you ever felt the frustration of waiting for your data to be fetched while your Sql query is taking millions of years to get executed? Sometimes it goes fast when the server is moderately loaded, but rush hour time and yo ! you are staring at the thing looking as dumb as a person staring at the hot toaster. So it is the mark of an educated mind to strive and make your queries as efficient as possible. Here I wish to mention one way you can make your queries get executed faster.
It can be done by using : Option(MAXDOP n) , where n is the called the Force order. So does the Force give you some power? Well this allows you to control the number of processing cores being used to process your query.
Generally, if you have not used this, your server makes its own plan to execute the query using 1 or more processor cores, which means we might see some parallel processing in action. However, the server cannot always be trusted with making the most efficient plan. So MAXDOP allows you to give your own instruction about how many cores should be used.
For n=0, you end up utilizing all cores for the execution. For n=1, no parallel plan is used and only one core is utilized. For n=2 to 64, n number of cores will be running your baby.
Now how fast it would get using this depends on how wisely you use it. For example, parallel processing, although it sounds cool, may not be the best option always. Loading of tables on the server memory, joining them there and then fetching your results may be faster on a single core than using more than one. This is very true in case of Warehouse data and OLTP servers. In these scenarios the server execution plan is usually not in favour of speed given the simplicity of queries and the number of requests for execution. So n=1 might actually do wonders here. Choose wisely, live well :D
Use it and experiment, I’m sure you will find more worth exploring. I found these links invaluable :
No comments:
Post a Comment