When we are asked by a client to do some SQL Server Consulting a lot of the time what we are asked what we can do in order to improve the performance of a SQL Server. So we thought we’d put together some advise to help you out in you are in a similar situation.
How To Speed Up A SQL Server
There are times when your SQL server will appear to be slow and you need to adjust this. If you are the administrator of the server, there are a few methods that you can use to do this. Of course, it is important to note that not all of these methods will work for every database and it will depend on what has been placed on the server.
Use The SET NOCOUNT ON
When you have a stored procedure in your SQL database, there is a default which is used. This default will send a message from the server to the client telling them about the number of rows that will be affected by the stored procedure. This message is not actually necessary and will generally add nothing to the client side of things. What it will do is slow down the server.
When you turn off this default setting, you will be reducing the network traffic between the server and the client. As this information is rarely needed, there is no reason to not do so. To turn off the default feature, you will need to use the SET NOCOUNT ON statement in all of your stored procedures.
This statement will need to be included at a stored procedure level and cannot be applied to the server as a whole. The statement will need to be included at the start of each stored procedure that you write.
Do Not Use The SP_ Prefix
When you create a stored procedure that will run in a database that is not the Master one, you should avoid the prefix “sp_”. This is due to the fact that this prefix is specifically used for system stored procedures. This does not mean that the procedure will not run, it simply means that the execution will be slightly slower.
The reason for the delay in execution is the fact that procedures with this prefix are first attempted to be resolved from the Master database. As the procedure is not located there, the server will waste time looking for it. When the procedure is not found in the Master database, the server will resolve it as if the owner of the object is “dbo”. This is an unnecessary delay which can slow down the server drastically if it needs to be completed multiple times.
Adjust The Minimum Memory Per Query Setting
When you have a query running on a Microsoft SQL server, the server will allocate the optimum amount of memory to the task. This ensures that the query is run quickly and efficiently. The default for this will be 1024 KB per query and this is the minimum amount.
If you have a query which needs more memory to run, the server will automatically assign the memory if it is available. As this is the case, most people will recommend not changing the default setting for the minimum memory per query. However, if you have a SQL server with more RAM than it actually needs, you can boost the performance of certain queries by adjusting this minimum default.
To boost the query, you will need to increase the minimum which is set to around 2048 KB or more. As long as there is an excess of memory on the server, the boosting will help the overall speed of the server. The problem comes when there is no excess as the higher minimum will slow down the server.
This is why this adjust is not often recommended and you need to be very careful when you choose to use it. Before doing this, you need to ensure that your server has more RAM than it actually needs and that there will always be an excess even with the higher minimum amounts.