SQL Server Performance Analysis Part V
PART V: "How To" get down to whatever is bringing SQL to its knees...
At this point, following the previous Performance Analysis posts, we have arrived to the following conclusions:
1. We know what server / SQL Server build we are dealing with
2. We have checked for all errors SQL Server could have generated to indicate a problem / mis-configuration
3. We have pinpointed possible bottleneck issues (memory / cpu / disk)
4. We filtered through to see which queries could be the cause of the pressure mentioned above.
In order to complete our analysis (because by this time we have a good idea of what needs tuning / tweaking / changing), we have a look at the other files PSSDIAG collects.
SQL_filelist.txt:
This file is great to see which drives are hosting SQL Server files - mdf / ldf files.
a. Are all database files on the same disk? If so this may be quite heavy pressure on just one disk.
b. Are the ldf files separated from the mdf files?
c. Are tempdb files separated from the rest?
NTFS_Compression_Check.txt:
This file is great to make sure no SQL Server drive is compressed as this is not supported.
https://blogs.msdn.com/khen1234/archive/2005/04/25/411852.aspx
Perf_Stats_HTDump(Hyperthreading).txt:
This file lets us know if the SQL Server is using hyper threading or not. If hyper threading is enabled, this should be taken into consideration.
Example:
Processors are hyper threading capable
A physical processor exposes logical processor(s): 0, 4
A physical processor exposes logical processor(s): 1, 5
A physical processor exposes logical processor(s): 2, 6
A physical processor exposes logical processor(s): 3, 7
System has 8 logical processors exposed by 4 physical processors
https://blogs.msdn.com/slavao/archive/2005/11/12/492119.aspx
Pinned_Table_Check.OUT:
Shows us which tables have been pinned. This means the SQL Server Database Engine does not flush the pages for the table from memory.
Note:
This functionality was introduced for performance in SQL Server version 6.5. DBCC PINTABLE has highly unwanted side-effects. These include the potential to damage the buffer pool. DBCC PINTABLE is not required and has been removed to prevent additional problems. The syntax for this command still works but does not affect the server
https://msdn.microsoft.com/en-us/library/ms178015(SQL.90).aspx
So once all these files have been analyzed, we can then pinpoint the SQL Server issues and what is causing them. Depending on what is the culprit, we apply a solution.
An example would be long running queries in which an optimization would be something to highly consider.
Hope this has been helpful.