SQL Server is a very important application of Windows system like any other application. It plays a very vital role with Windows’ cooperative scheduler and helps in sharing CPU and other resources. It is capable of handling and executing multiple operations simultaneously which means SQL Server is allowed to take its turn on the host.
MS SQL faces performance issues while executing queries and working within SQL environment in a regular routine. There are many issues that you must be dealing with such as poor queries, slow processing, indexes with tons of writes and reads, memory or CPU issues. Therefore, it is a good chance to discuss SQL performance issues and their resolutions.
Typical MS SQL Server Performance Issues and their Relevant Solutions:
Issue 1: Queries take longer time than usual in processing:
Are your normal queries taking longer time in processing than usual? This issue could occur due to resource contention from locking. The issue may occur when new processes get added to your system, or when the load on the system gets increased.
You can fix the issue by troubleshooting this problem using Query Analyzer and the SP_WHO2 command. You will see BlkBY field in the results of this command which show up offending SPID numbers as Output if they are blocked. You might have to follow a large chain of blocks to find the head. Also, you can use the DBCC inputbuffer command to see the SQL statement that the SPID is running. By doing this you will be able to find the cause and resolution of the problem.
Issue 2: Database faces out of space errors:
It happens a lot of time that you receive messages that your database is out of space. There could be two reasons for this issue, first, the physical drive which stores the database, or its transaction log has run out of space. To resolve this, examine that disk for any file(s) that are rapidly growing or any other problems. Secondly, the database or the transaction log file has a set maximum file size. To resolve the case, simply increase the limit to allow database operation. Other reason could take place when tempdb is full.
To fix this, restart the MSSQL service and restarting SQL will recreate tempdb from scratch. Make sure tempdb has a maximum size and increase that size if necessary to avoid the issue in future.
Issue 3: Permissions denied after granting them rights to the stored procedure:
When you assign user rights to execute a stored procedure, you will also have to allow permissions on the objects referenced by the stored procedure. Say, for example, suppose you have a stored procedure that performs a select from the customers table, then you must allow the user to select rights on the customer table.
Issue 4: Clients lose connectivity to the SQL Server:
The issues with SQL Server connection is not that frequent but if it takes place, it generally blocks SPID to hold a lock on a resource while waiting on the timeout. This is a widespread issue in SQL client running older versions of Microsoft Access.
To fix the issue, use the commands SP_WHO2 and KILL to delete the orphaned process from the server. Another way to resolve is decreasing your clients’ timeout value, this will lead the server to kill orphaned processes more quickly and easily.
Issue 5: Entire system goes to Deadlock state:
If you are receiving error messages saying that you are in deadlock state (or a similar error depending upon your error reporting system) and the system is suffering from deadlocking. The system comes to a deadlock state when two or more queries each locking resources and none of them is getting completed. When a deadlock state gets resolved the SQL Server log will report the situation. And, server log is the best place to check whether the problem has been solved or not.
Another way to solve the issue is turning ON trace flag-t1204, this will provide complete information in your log. You will be required to determine the contention level on your server.
SQL performance and database consistency checks are very essential to keep SQL Server always up and running smoothly. You should always conduct regular backups, monitor resources and check SQL Server services. If you face issues like corrupt MDF/NDF files or inaccessible SQL database files, it is recommended to use a professional tool Kernel for SQL Database Recovery. This SQL database recovery software is an advance innovative technology specifically designed to deal with SQL database files for any version of SQL.