Looking To Outsource PAL Analysis

May 26, 2008 at 2:59 AM
Hi Everyone,
We are looking to outsource the analysis of PAL reports to someone who has significant expertise in the area. Our in house engineers whilst being capable of understanding and interpreting the results tend to have only generalised knowledge and have difficulty in identiying specifically where the bottleneck exists in hte system and what can be done to resolve it.

We are specifically looking for a group of individuals or a company to provide us with a performance recomendation based on the analysis that PAL provides. A written report would be expected with statistical data from the analyis identifying where the bottleneck exists and what can be done to resolve it.

Predominantly the reports are run because of performance issues with either SQL or Exchange. Someone with a good understanding of SQL would be ideal. Rates can be negotiated on and we would look to pay via EFT or Paypal however are open to suggestions. We are based in Australia so someone local in our time zone would be an advantage but not necessary.

If anyone is interested could you please let me know via e-mail chiper1@optushome.com.au or by replying to this post.


Kind Regards,
Chiper
Coordinator
May 26, 2008 at 5:42 PM

Hi Chiper,

I'm glad you mentioned this and I'll take this opportunitute to reiterate that PAL is not a replacement for performance analysis, but simply a time saver and a way to consolidate performance anlaysis knowledge. My team (Microsoft Premier Field Engineering) offers Health Checks and RAPs (Risk Assessment Program) for both Exchange and SQL. The PAL thresholds are actually based on the these processes, but as you already mentioned, the interpretation of the results is important.

If you wish to engage my team for these services and if you have a premier contract with us (Microsoft), then contact your Microsoft Technical Account Manager (TAM). If you do not have a contract, then contact Microsoft at http://www.microsoft.com/services.


Chiper wrote:
Hi Everyone,
We are looking to outsource the analysis of PAL reports to someone who has significant expertise in the area. Our in house engineers whilst being capable of understanding and interpreting the results tend to have only generalised knowledge and have difficulty in identiying specifically where the bottleneck exists in hte system and what can be done to resolve it.

We are specifically looking for a group of individuals or a company to provide us with a performance recomendation based on the analysis that PAL provides. A written report would be expected with statistical data from the analyis identifying where the bottleneck exists and what can be done to resolve it.

Predominantly the reports are run because of performance issues with either SQL or Exchange. Someone with a good understanding of SQL would be ideal. Rates can be negotiated on and we would look to pay via EFT or Paypal however are open to suggestions. We are based in Australia so someone local in our time zone would be an advantage but not necessary.

If anyone is interested could you please let me know via e-mail chiper1@optushome.com.au or by replying to this post.


Kind Regards,
Chiper



May 27, 2008 at 12:44 AM
Hi Clint,
Thanks for the reply, i checked out that website and to be honest im a bit lost. We dont have a premier contract with MS and are located in Australia, can you tell me who i can call/e-mail to get things moving as I have 2 customers that need this analysis done ASAP. Could you give me an indication of costs?
May 27, 2008 at 3:32 AM
Hi Clint,
I have included snippets of the analysis from the SQL server in question. Is the High User-mode Memory Usage warning something to be alarmed about with reference to SQL?

I was under the impression that this is normal operation for SQL as SQL will, in most cases take as much available memory as is possible and give back memory when it is required? The customer is experiencing delays in operation of the SQL database application. The PAL software has also thrown slow disk response errors > 25ms. The server is running 15K SCSI drives so I am struggling to find a reason for the slow disk access. The server has 2 raid volumes C: is  RAID 1 with the OS loaded and pagefile set 2gb initial 4gb max. D: is a RAID5 with the SQL database files on it. Both volumes are slow according to the PAL analysis. I initially though it would be due to the server running low on memory causing swapping on the disk however it doesn't seem to be the case. There are a number of memory related alerts so i'm assuming either disk or memory is at fault here just struggling to correlate which and why.

Any advice even a point in the right direction would be appreciated :)

5/22/2008 1:50:32 PM High User-mode Memory Usage - consuming more than 80% of available virtual memory \\BELLER02\Process(sqlservr#1)\Virtual Bytes 1,814,650,880 1,818,260,523 1,819,369,472 0
5/22/2008 2:39:10 PM High User-mode Memory Usage - consuming more than 80% of available virtual memory \\BELLER02\Process(sqlservr#1)\Virtual Bytes 1,814,126,592 1,818,318,207 1,818,845,184 71,166
5/22/2008 3:27:48 PM High User-mode Memory Usage - consuming more than 80% of available virtual memory \\BELLER02\Process(sqlservr#1)\Virtual Bytes 1,814,126,592 1,817,842,551 1,818,845,184 -257,831
5/22/2008 4:16:26 PM High User-mode Memory Usage - consuming more than 80% of available virtual memory \\BELLER02\Process(sqlservr#1)\Virtual Bytes 1,814,650,880 1,818,232,170 1,818,845,184 -11,660
5/22/2008 5:05:04 PM High User-mode Memory Usage - consuming more than 80% of available virtual memory \\BELLER02\Process(sqlservr#1)\Virtual Bytes 1,818,320,896 1,818,320,896 1,818,320,896 18,621
5/22/2008 5:53:42 PM High User-mode Memory Usage - consuming more than 80% of available virtual memory \\BELLER02\Process(sqlservr#1)\Virtual Bytes 1,814,126,592 1,817,323,668 1,818,845,184 -231,164
5/22/2008 6:42:20 PM High User-mode Memory Usage - consuming more than 80% of available virtual memory \\BELLER02\Process(sqlservr#1)\Virtual Bytes 1,818,845,184 1,818,855,939 1,819,369,472 122,429
5/22/2008 7:30:58 PM High User-mode Memory Usage - consuming more than 80% of available virtual memory \\BELLER02\Process(sqlservr#1)\Virtual Bytes 1,814,126,592 1,817,812,823 1,818,845,184 -78,905
5/22/2008 8:19:36 PM High User-mode Memory Usage - consuming more than 80% of available virtual memory \\BELLER02\Process(sqlservr#1)\Virtual Bytes 1,818,320,896 1,818,320,896 1,818,320,896 9,310
5/22/2008 9:08:14 PM High User-mode Memory Usage - consuming more than 80% of available virtual memory \\BELLER02\Process(sqlservr#1)\Virtual Bytes 1,818,320,896 1,818,320,896 1,818,320,896 8,276
5/22/2008 9:56:52 PM High User-mode Memory Usage - consuming more than 80% of available virtual memory \\BELLER02\Process(sqlservr#1)\Virtual Bytes 1,818,320,896 1,818,321,234 1,818,386,432 7,490
5/22/2008 10:45:30 PM High User-mode Memory Usage - consuming more than 80% of available virtual memory \\BELLER02\Process(sqlservr#1)\Virtual Bytes 1,814,126,592 1,817,788,542 1,818,845,184 -52,936
5/22/2008 11:34:08 PM High User-mode Memory Usage - consuming more than 80% of available virtual memory \\BELLER02\Process(sqlservr#1)\Virtual Bytes 1,814,126,592 1,817,110,169 1,818,320,896 -118,268



5/22/2008 1:50:32 PM More then 10 page file reads per second \\BELLER02\Memory\Pages Input/sec 0 3 204 0
5/22/2008 2:39:10 PM More then 10 page file reads per second \\BELLER02\Memory\Pages Input/sec 0 17 1,692 17
5/22/2008 3:27:48 PM More then 10 page file reads per second \\BELLER02\Memory\Pages Input/sec 0 6 680 2
5/22/2008 4:16:26 PM More then 10 page file reads per second \\BELLER02\Memory\Pages Input/sec 0 18 1,098 6
5/22/2008 5:05:04 PM More then 10 page file reads per second \\BELLER02\Memory\Pages Input/sec 0 3 441 0
5/22/2008 5:53:42 PM More then 10 page file reads per second \\BELLER02\Memory\Pages Input/sec 0 2 333 0
5/22/2008 8:19:36 PM More then 10 page file reads per second \\BELLER02\Memory\Pages Input/sec 0 0 64 0
5/22/2008 9:08:14 PM More then 10 page file reads per second \\BELLER02\Memory\Pages Input/sec 0 0 48 0


5/22/2008 1:50:32 PM > 10% of the number of Batch Requests/Sec \\BELLER02\MSSQL$CONSOLE:Access Methods\FreeSpace Scans/sec 0 1 49 0
5/22/2008 2:39:10 PM > 10% of the number of Batch Requests/Sec \\BELLER02\MSSQL$CONSOLE:Access Methods\FreeSpace Scans/sec 0 0 55 -1
5/22/2008 3:27:48 PM > 10% of the number of Batch Requests/Sec \\BELLER02\MSSQL$CONSOLE:Access Methods\FreeSpace Scans/sec 0 1 28 0
5/22/2008 4:16:26 PM > 10% of the number of Batch Requests/Sec \\BELLER02\MSSQL$CONSOLE:Access Methods\FreeSpace Scans/sec 0 1 72 0


5/22/2008 1:50:32 PM Ratio of Index searches/sec to Full scan/sec < 1000 to 1. \\BELLER02\MSSQL$CONSOLE:Access Methods\Full Scans/sec 0 456 5,771 0
5/22/2008 2:39:10 PM Ratio of Index searches/sec to Full scan/sec < 1000 to 1. \\BELLER02\MSSQL$CONSOLE:Access Methods\Full Scans/sec 0 544 5,284 108
5/22/2008 3:27:48 PM Ratio of Index searches/sec to Full scan/sec < 1000 to 1. \\BELLER02\MSSQL$CONSOLE:Access Methods\Full Scans/sec 0 225 4,084 -143
5/22/2008 4:16:26 PM Ratio of Index searches/sec to Full scan/sec < 1000 to 1. \\BELLER02\MSSQL$CONSOLE:Access Methods\Full Scans/sec 0 265 3,777 -78
5/22/2008 5:05:04 PM Ratio of Index searches/sec to Full scan/sec < 1000 to 1. \\BELLER02\MSSQL$CONSOLE:Access Methods\Full Scans/sec 0 197 3,777 -80



5/22/2008 1:50:32 PM More than 20 Workfiles created per second for every 100 Batch Requests \\BELLER02\MSSQL$CONSOLE:Access Methods\Workfiles Created/sec 0 6 35 0
5/22/2008 2:39:10 PM More than 20 Workfiles created per second for every 100 Batch Requests \\BELLER02\MSSQL$CONSOLE:Access Methods\Workfiles Created/sec 0 9 61 3
5/22/2008 3:27:48 PM More than 20 Workfiles created per second for every 100 Batch Requests \\BELLER02\MSSQL$CONSOLE:Access Methods\Workfiles Created/sec 0 4 27 -2
5/22/2008 4:16:26 PM More than 20 Workfiles created per second for every 100 Batch Requests \\BELLER02\MSSQL$CONSOLE:Access Methods\Workfiles Created/sec 0 4 31 -1



5/22/2008 1:50:32 PM Ratio of Page Lookups/sec to Batch Requests/sec < 100 to 1. \\BELLER02\MSSQL$CONSOLE:Buffer Manager\Page lookups/sec 3 2,201 20,813 0
5/22/2008 2:39:10 PM Ratio of Page Lookups/sec to Batch Requests/sec < 100 to 1. \\BELLER02\MSSQL$CONSOLE:Buffer Manager\Page lookups/sec 3 2,911 18,676 876
5/22/2008 3:27:48 PM Ratio of Page Lookups/sec to Batch Requests/sec < 100 to 1. \\BELLER02\MSSQL$CONSOLE:Buffer Manager\Page lookups/sec 3 1,271 14,991 -574
5/22/2008 4:16:26 PM Ratio of Page Lookups/sec to Batch Requests/sec < 100 to 1. \\BELLER02\MSSQL$CONSOLE:Buffer Manager\Page lookups/sec 3 1,321 12,841 -362



5/23/2008 1:11:24 AM Page Reads/sec > 90 \\BELLER02\MSSQL$CONSOLE:Buffer Manager\Page reads/sec 0 68 6,996 6
5/23/2008 6:51:50 AM Page Reads/sec > 90 \\BELLER02\MSSQL$CONSOLE:Buffer Manager\Page reads/sec 0 6 972 0
5/23/2008 8:29:06 AM Page Reads/sec > 90 \\BELLER02\MSSQL$CONSOLE:Buffer Manager\Page reads/sec 0 6 634 0


5/22/2008 1:50:32 PM Latch Wait Times are greater than 10 milliseconds on average \\BELLER02\MSSQL$CONSOLE:Latches\Latch Waits/sec 0 0.192 1.667 0
5/22/2008 2:39:10 PM Latch Wait Times are greater than 10 milliseconds on average \\BELLER02\MSSQL$CONSOLE:Latches\Latch Waits/sec 0 0.232 1.8 0.05
5/22/2008 3:27:48 PM Latch Wait Times are greater than 10 milliseconds on average \\BELLER02\MSSQL$CONSOLE:Latches\Latch Waits/sec 0 0.162 8.267 -0.019
5/22/2008 4:16:26 PM Latch Wait Times are greater than 10 milliseconds on average \\BELLER02\MSSQL$CONSOLE:Latches\Latch Waits/sec 0 0.092 1 -0.041



5/22/2008 1:50:32 PM A ratio of more than 1 SQL Compilation for every 100 Batch Requests per second \\BELLER02\MSSQL$CONSOLE:SQL Statistics\SQL Compilations/sec 0 2 14 0
5/22/2008 2:39:10 PM A ratio of more than 1 SQL Compilation for every 100 Batch Requests per second \\BELLER02\MSSQL$CONSOLE:SQL Statistics\SQL Compilations/sec 0 2 21 0
5/22/2008 3:27:48 PM A ratio of more than 1 SQL Compilation for every 100 Batch Requests per second \\BELLER02\MSSQL$CONSOLE:SQL Statistics\SQL Compilations/sec 0 1 12 -1


5/22/2008 1:50:32 PM Disk responsiveness is very slow (spike of more than 25ms) \\BELLER02\PhysicalDisk(1 D:)\Avg. Disk sec/Read 0 0.003 0.058 0
5/22/2008 3:27:48 PM Disk responsiveness is very slow (spike of more than 25ms) \\BELLER02\PhysicalDisk(1 D:)\Avg. Disk sec/Read 0 0.002 0.061 0
5/22/2008 4:16:26 PM Disk responsiveness is very slow (spike of more than 25ms) \\BELLER02\PhysicalDisk(1 D:)\Avg. Disk sec/Read 0 0.002 0.03 0
5/22/2008 9:56:52 PM Disk responsiveness is very slow (spike of more than 25ms) \\BELLER02\PhysicalDisk(1 D:)\Avg. Disk sec/Read 0 0.001 0.097 0

5/22/2008 1:50:32 PM Disk responsiveness is very slow (spike of more than 25ms) \\BELLER02\PhysicalDisk(1 D:)\Avg. Disk sec/Write 0.003 0.009 0.039 0
5/22/2008 2:39:10 PM Disk responsiveness is very slow (spike of more than 25ms) \\BELLER02\PhysicalDisk(1 D:)\Avg. Disk sec/Write 0.003 0.009 0.03 0
5/22/2008 3:27:48 PM Disk responsiveness is very slow (spike of more than 25ms) \\BELLER02\PhysicalDisk(1 D:)\Avg. Disk sec/Write 0.003 0.009 0.029 0


5/22/2008 1:50:32 PM Low on available memory - less than 10% available \\BELLER02\Memory\Available MBytes 116 135 153 0
5/22/2008 2:39:10 PM Low on available memory - less than 10% available \\BELLER02\Memory\Available MBytes 119 150 193 18
5/22/2008 3:27:48 PM Low on available memory - less than 10% available \\BELLER02\Memory\Available MBytes 119 137 242 1

5/23/2008 1:11:24 AM Memory: an increasing trend of 10MB's per hour detected \\BELLER02\Process(sqlservr#1)\Private Bytes 71,016,448 364,132,711 584,351,744 24,368,087.3391806
5/23/2008 2:00:02 AM Memory: an increasing trend of 10MB's per hour detected \\BELLER02\Process(sqlservr#1)\Private Bytes 577,155,072 577,458,785 577,818,624 40,289,215.6996889
5/23/2008 2:48:40 AM Memory: an increasing trend of 10MB's per hour detected \\BELLER02\Process(sqlservr#1)\Private Bytes 577,163,264 577,230,911 577,355,776 37,753,568.9141694
Coordinator
Jun 3, 2008 at 6:14 AM

Hi Chiper,

I checked with my management and come to find out, you can only engage us if you have a Premier contract with Microsoft.

With that said, I'm happy to do a quick interpretation of your last posting.


Chiper wrote:
Hi Clint,
Thanks for the reply, i checked out that website and to be honest im a bit lost. We dont have a premier contract with MS and are located in Australia, can you tell me who i can call/e-mail to get things moving as I have 2 customers that need this analysis done ASAP. Could you give me an indication of costs?


Coordinator
Jun 3, 2008 at 6:35 AM
Correct, The reason it was flagged is because if a process normally has this behavior, then it could be in a memory leak condition. SQL typically consumes a lot of virtual address space especially on 32-bit. This is because it is managing it's own cache, so this result can be ignored. I've been meaning to update the SQL threshold file to exclude the SQL processes on this analysis. With that said, SQL could still be running out of memory. The best indicator of this is the page life expectancy. The lower the life expectancy, the more SQL is likely running out of memory. Also, if SQL's target memory value and total memory value are significantly different, then this could also indicate memory pressure on SQL - PAL has a 500MB memory difference threshold looking of this. I don't see that in the above alerts, though.

D: drive appears to be okay because it's average response times are under 10ms. Notice the alert says there was a spike of latency. This alert is in place to catch any spikes in time slices analyzed. For example, if the time slices are each 1 hour, and there was a large spike in disk latency, then the *spike* would be shown in this manner. So this really comes down to you. Are you concerned that your disk latency (response times) were slow for possible a few minutes?

Overall, I don't have enough data to determine what the bottleneck is. Can you send me the full PAL report? For example, available memory (phyiscal memory) appears to be a bit low, but I would need to see if Pages/sec was high as well. If pages/sec is low, then it just might be cache taking up memory. Also, the time slices seem to be in hours. If this is true, then the collection interval might not be collecting often enough to give us accurate values. Try using doing a 1 minute collection interval and have it run for 24 hours. When PAL does the analysis, set it to AUTO for 48 minute time slices or set it to 10 minutes for a more accurate analysis (but takes a lot longer to process).

Finally, since this is SQL Server, I highly recommend using the SQL Profiler. Run it when you feel performance is slow and look at the query durations. Many times SQL slowness is simply due to the lack of a good index in the right place.

This is a great dialog beause I can talk about how I interprete the PAL reports. Let's keep this going. Thanks. ;-)
Jun 3, 2008 at 8:49 AM
Hi Clint,
Will e-mail you the full report if you can send me your e-mail address.

I was under the impression the SQL Profiler tool had to be run in realtime to gain what was occuring? As we are an integration company it would be difficult to catch the performance degradation in the act. By the time the customer reported it, our engineers accept run a remote session and run the tool it will probably be over. However could probably ask them to record what makes the system run slow and then try to repeat the same tasks running the tool. Do you have any advise on how to use the SQL Profiler tool? Im new to performance monitoring let alone SQL performance monitoring :-)

The perfmon data was only collected for 24hrs every 1min, i have new data which i will run tonight which has 3days of collected data sampled every 1min. Once i recieve your e-mail address i will e-mail you this analysis and the original analysis.

I guess the main issue with performance monitoring is truely understanding how windows handles memory, from what i can gather it actually allocates all available physical memory to the virtual memory manager which would instantly show low amounts of available physical memory so this should therfore be ignored?? I have a plethora of questions in relation to memory however will send through to you in a more detailed e-mail.

Thanks for the reply and your assistance thus far, much apreciated :)

Kind Regards,
Chiper
Coordinator
Jun 3, 2008 at 2:10 PM
Yes, SQL Profiler must be capturing data when the problem is occuring.

My email address is clinth@microsoft.com.
Also, I will be at TechEd next week speaking about BizTalk performance analysis (SOA51-TLC - Microsoft BizTalk Server 2006 Performance Analysis).

Yes, virtual versus physical memory can be a bit confusing and it's a lengthy subject. I'll blog about the subject later tonight. My blog is at http://blogs.technet.com/clint_huffman.


Chiper wrote:
Hi Clint,
Will e-mail you the full report if you can send me your e-mail address.

I was under the impression the SQL Profiler tool had to be run in realtime to gain what was occuring? As we are an integration company it would be difficult to catch the performance degradation in the act. By the time the customer reported it, our engineers accept run a remote session and run the tool it will probably be over. However could probably ask them to record what makes the system run slow and then try to repeat the same tasks running the tool. Do you have any advise on how to use the SQL Profiler tool? Im new to performance monitoring let alone SQL performance monitoring :-)

The perfmon data was only collected for 24hrs every 1min, i have new data which i will run tonight which has 3days of collected data sampled every 1min. Once i recieve your e-mail address i will e-mail you this analysis and the original analysis.

I guess the main issue with performance monitoring is truely understanding how windows handles memory, from what i can gather it actually allocates all available physical memory to the virtual memory manager which would instantly show low amounts of available physical memory so this should therfore be ignored?? I have a plethora of questions in relation to memory however will send through to you in a more detailed e-mail.

Thanks for the reply and your assistance thus far, much apreciated :)

Kind Regards,
Chiper