SQL Server Performance Analysis Part II
PART II: "How To" get down to whatever is bringing SQL to its knees...
SQL Server Error Log:
If SQL Server has an issue, this is most likely where you will find an indication to what is going wrong.
This log provides us with lots of information:
1.
The first line tells us what version of sql server we are using and what build number. This version of SQL (example: standard / enterprise / express) lets us know what restrictions we may have. An example of such is how much memory SQL can use (standard vs enterprise):
Example:
SQL 2000 Std Edition:
Up to 4 Processors
Up to 2 GB of Memory
SQL 2000 EE Edition:
Up to 32 Processors
Up to 64 GB of Memory
The SQL Build lets us know on what SP / CU we are currently on. We normally recommend the latest version of SQL to avoid issues that already have been found and corrected. Also, should an issue occur, being on the latest build avoids the necessity of upgrading to the latest versions and testing.
Best practices normally involve having a test environment, similar (to not say the same) as production environment in which the latest patches can be tested once publically launched. This facilitates then the installation of packages on production environment.
SQL Server 2008:
https://www.microsoft.com/sqlserver/2008/en/us/editions.aspx
SQL Server 2005:
https://www.microsoft.com/Sqlserver/2005/en/us/compare-features.aspx
SQL Server 2000:
https://www.microsoft.com/hk/sql/evaluation/overview/default.mspx
SQL BUILDS:
https://www.sqlsecurity.com/FAQs/SQLServerVersionDatabase/tabid/63/Default.aspx
2.
In SQL Server 2005, the start-up parameters are now shown in the beginning of the error log. Should they have been triggered by the DBCC TRACE ON command, they will not be at the beginning of the log but a row will be written at the time of the execution.
It is always good to control what parameters SQL Server is starting with as they influence SQL Server's behaviour.
3.
Common errors such as "The SQL Network Interface library could not register the Service Principal Name (SPN) for the SQL Server service" can be found as well in the SQL Server error log. These errors can have an impact on your SQL Server and should be addressed.
Blogs:
https://blogs.msdn.com/sql_protocols/archive/2008/04/02/service-principal-name-spn.aspx
Depending on what may be happening to your SQL Server, this should be the first stop to look. When it comes down to performance issues, you may see various errors / warnings here that can help pinpoint the issue.
Common messages (example):
SQL Server has encountered XXX occurrence(s) of IO requests taking longer than 15 seconds to complete on file xxxx.mdf / .ndf / .ldf in database xxxx.
This error is an indication that you may have slow disks or that the disks are just being over loaded causing SQL Server to be underperformed.
(more insight on this when addressing performance monitor)
Blog:
A significant part of sql server process memory has been paged out
This error is an indication that SQL Server was told to release memory hence it paged its memory onto disk. If you are on an X64 bit machine, then SQL Server service account should have the permissions LOCK PAGES IN MEMORY. If you have x64 and lock pages in memory attributed, this error, as in x86 machines, is an indication of memory bottleneck (most likely external).
Database corruption messages, login failures, DLL's loaded onto SQL Server (highly useful when analyzing memory leaks) can all be found in the SQL Server error log.
Blog:
https://blogs.msdn.com/psssql/archive/2009/04/24/sql-server-locked-pages-and-standard-sku.aspx
Next Blog Post: Part III - Performance Monitors....