In this blog post, we are going to learn about how to Speed Up Performance Without Code Change or Configuration Change in SQL Server. This blog post is going to be a fun and quick read.
Please make sure that you read this blog post till the end to learn the secret of how I improved query performance from 40 seconds to just 12 seconds.
Behind the Scene – Code Change
If you are living an independent consultant’s life you know that the month of December is an amazing month for you. Most organizations have year ending in December and they usually have some budget left from their yearly spending. Most of the organizations save their budget till the last month as they want to make sure that if something urgent comes up they have enough money to cover it. Well, this is a blessing for independent consultants, when organizations have not figured out where to spend money till the end of the month.
When organizations have budget left over, they usually look for the opportunity to spend it constructively. In most cases, if they do not spend money they lose that portion of the budget and the next year they get lesser budget. It is a very common practice.
Opportunity for Consultants
Now no one wants slow performance for their business application. Everyone wants the amazing performance of their front-end application and back-end database. This is when the organizations usually look outside for consultants who can help them speed up their application’s performance. Every year in the month of December I get lots of requests for helping them to tune their system’s performance. My most popular service is Index Tuning and Strategy Guidance (99 minutes). In this, I help organizations to tune their system with the help of Indexing. It is very powerful and it gives an instant performance improvement. Once the tuning is over, organizations have to test their system to make sure it is giving optimal performance. This service requires that DBAs with whom I work have administrator rights to the database.
However, in the month of December, the most popular request is not about Index Tuning but rather a very strange request. I believe this is because the December month is a financial year ending as well as a month of festivals. Not everyone has time to test their system as well do various code changes in the month of December.
Here is the most popular request I often hear in the month of December.
“Help us to Speed Up our application’s performance without changing any code or configuration in SQL Server.”
Now think about it for a moment. The request is indeed very strange. They want to improve the performance of their system without allowing me to touch any settings or code in SQL Server. Well, though it sounds strange, it is not as strange as you think.
Network Bandwidth Optimizations
I know the request is very strange, but there has to be a solution to every problem. Fortunately, this is when my formal education as a Master of Science in Computer Network (from the University of Southern California, USC, Los Angeles, California) helps me. Not many people know that when sending data packets on a network, we are not fully optimizing the network packets as well as lots of bandwidth goes to waste. When I am not allowed to touch code or computer system, I always look at the network optimization.
I did intensive research to find a good Network Bandwith Optimization product on the internet and finally landed upon NitroAccelerator. I did quite a lot of performance tests with this product and I figured out it indeed helps to improve SQL Server Performance without Code Change.
Speed Up Performance Without Code Change
Here is a quick video where you can see how we can improve performance with the help of NitroAccelerator and improve the performance of our query. Please note that I am on my fastest possible gigabit network and my network traffic is absolutely near to nothing. Even though in this case, I have got amazing performance gain.
My query which was earlier running for 40 seconds is now running for 12 seconds.
Think about it, in my fastest possible network, I have gained over 300% times query performance gain. How much performance can you gain in your environment where the network is heavily congested?
Reference: Pinal Dave (https://blog.sqlauthority.com)
16 Comments. Leave new
Hi Pinal, as per our morning discussion on youtube I tried the same and its reduce 40-50% time to sending data on application server. For your information we have 4 databases located on different locations and on application server we had created linked for this all 4 databases together and fetching data on front-end using synonym of the linked server but suddenly the application was fetching data slowly (might be due to traffic etc.) and in the morning I received your mail and I try the same and now its reduce the time to 40-50%. My question is, is Linked server workable environment which we had created or do we have to create some different environment?
Please note as we were getting problem in msdtc so we are doing transaction on page for 4 different location.
I know it is not good practice but we were tired doing all possible settings for msdtc and firewall (after following step define by you in one of your blog for msdtc.)
Your advice/suggestion will be highly appreciated.
Waiting for your favorable reply.
Hi Imran,
I am glad that you were able to reduce your 50% workload by just using the suggestion provided in the comment.
To answer your linked server in question, I might have to understand your business more in details, but I will say this, if it is working fine at this point of time. Do not worry, otherwise there are different options available but we can consider that in the future.
We have 4 different locations where we are saving data and linking 3 locations into one database and 1 location works separately due to security reason.
But on central database where I linked 3 other locations database and when fire trigger on table after insert then I am getting an error of
“The operation could not be performed because OLE DB provider “SQLNCLI11” for linked server “INDIA” was unable to begin a distributed transaction.”
then I search on net and found solutions on your blog then I follow the step which define by you and still I am getting same error message.
For your information I have different SQL Server and windows version on India server and other location server. Other have SQL Server 2012/14 and Windows 2014 and India have 2008R2 and windows 2008 R2. Is this creating problem for msdtc?
Currently I am fetching data from linked server to display record on webpage and on transaction level I am sending records on each location database using webpage (which taking too much time.) that why i want to run trigger on linked database table.
I try hard but not able to get result. And your today blog give me some hope but still my client wants more speed.
I hope to receive a a favorable suggestion from you.
It sounds to me like the application server is using embedded SQL. If so, I might suggest using stored procedures, and, if security permits, link the databases in SQL Server itself. Then SQL Server can use best optimization techniques. You might also run Brent Ozar’s SpBlitz procedures to list missing indexes (only create indexes that list highest gain, not all indexes listed as “missing”) and make sure statistics are updated. There are other tuning techniques you can find on both Pinal Dave’s forum and on Brent Ozar.
is this software work on SAP B1 server
No. Its a SQL Server and web and Desktop base application.
It works on SQL Server.
Hi Chandresh, while NitroAccelerator is SQL Server, we have a beta release of a new product that can accelerate any Windows network traffic. Let me know if you are interested in trying this out and I can have my team send to you. Thanks, Fred (CEO, Nitrosphere)
Hi Pinal.
Looking at the test you did in the YouTube video, I see one potential inconsistency. You are restarting the local SQL server, not FREEDOM (which is a linked server). The first query might have populated the in-memory page from disk and second query might have then read that in-memory page, which would cut down on overall time.
Couple of ideas for the
1. Instead of restarting only local server, restart both local and FREEDOM
2. Do a back-to-back-to-back-to-back test (without nitro, with nitro, without nitro and finally with nitro)
Either of the above suggestions would account for cold starts.
Thank you for providing great content over the years.
Bojan
Hi Bojan,
I have done the test by restarting FREEDOM as well, however the result is the same. It always gives optimal performance.
Hi, I have tested the same scenario but i have not seen the performance improvement. Actually, my table has same 500000 rows and no index.
I see, I will be happy to help you with the same, send me email.
No Index? Fix that. Should have both a clustered index (preferably on an always incrementing value — INT IDENTITY(1,1) if no other unique value will be always increasing) and a query index as well as indexes on foreign keys.
It may be that the poor performance of the query itself (due to missing index?) is overshadowing any possible improvement from network optimization. Also, I believe, in ADO.Net and possibly the drivers, you can set batch size which sets how many records come back in one batch. I believe that for Oracle (might also work for SQL Server), Tom Kite did some experiments and found that usually a setting of around 700 records was best, but you could experiment. Something between 500 and 1000 records was his general recommendation. It’s been a while since I dealt with this, so let me know if I have this wrong.
Good Suggestion Henry!
Very good point.