October 12, 2012 at 9:26 am
Can I interpret a high value of load factor calculated as
SELECT AVG(load_factor*1.0) as avg_load_factor
FROM sys.dm_os_schedulers
WHERE scheduler_id < 255
..as some indication of work load on the server?
I currently se value of 2.8 , but an hour ago it was 6.9.
More generally, we are trying to troubleshoot some slow-running stored procedures, and looking for some metrics that would indicate current load on the server. One problem we are facing is wildly different execution times for the same stored procedure [same parameters] at different times of the day.
Thanks,
Sergei Z
October 12, 2012 at 9:46 am
Internal value that indicates the perceived load on this scheduler. This value is used to determine whether a new task should be put on this scheduler or another scheduler. This value is useful for debugging purposes when it appears that schedulers are not evenly loaded. The routing decision is made based on the load on the scheduler. SQL Server also uses a load factor of nodes and schedulers to help determine the best location to acquire resources. When a task is enqueued, the load factor is increased. When a task is completed, the load factor is decreased. Using the load factors helps SQL Server OS balance the work load better. Is not nullable.
http://msdn.microsoft.com/en-us/library/ms177526.aspx
Its a logical value used to balance load.
October 12, 2012 at 9:47 am
Sergei Zarembo (10/12/2012)
Can I interpret a high value of load factor calculated asSELECT AVG(load_factor*1.0) as avg_load_factor
FROM sys.dm_os_schedulers
WHERE scheduler_id < 255
..as some indication of work load on the server?
Based on the definition, the higher the value the bigger the perceived load on SQL.
October 12, 2012 at 10:11 am
Thank you for your answers. I read what MSDN has on it before. I was hoping to get some info on other metrics people use to estimate load on a server.
October 12, 2012 at 1:03 pm
Sergei Zarembo (10/12/2012)
Can I interpret a high value of load factor calculated asSELECT AVG(load_factor*1.0) as avg_load_factor
FROM sys.dm_os_schedulers
WHERE scheduler_id < 255
..as some indication of work load on the server?
I currently se value of 2.8 , but an hour ago it was 6.9.
More generally, we are trying to troubleshoot some slow-running stored procedures, and looking for some metrics that would indicate current load on the server. One problem we are facing is wildly different execution times for the same stored procedure [same parameters] at different times of the day.
Thanks,
Sergei Z
I would look at what is going in the Activity Monitor during the "slow" times. There are different signs to whats going to indicate disk hot spots and IO issues, versus CPU and memory issues. The value you bring up is pretty far down my list of what I look at when I encounter an issue.
October 12, 2012 at 1:48 pm
Just to give you a quick start.
You need to look at least two different areas: db and OS.
Start with db. I recommend you to install Adam Mechanic's procedure sp_WhoIsActive[/url].
Run it, and look if there is any value in columns WAIT and BLOCKED.
The type of wait may indicate direction of the problem (IO WAIT, lock wait, network wait, cpu wait).
Next, you can look at OS performance counters (run perfmon.msc).
Each counter should be per cpu, per drive, etc - do not use "Total".
Counters you should look are:
PhysicalDisk / Avg Disk secs per Read
PhysicalDisk / Avg Disk secs per Write
Both should be less than 0.050 (50 msec). Indicates slow storage response if higher.
System / Processor Queue Length
Should be 2 or less. Higher values indicate there are processes waiting for a CPU to became available - you don't have enough CPU power.
Memory / Available MBytes - should be > 100MB
SQLServer:General Statistics / User Connections - you should know what is your normal number (e.g. 50).
SQL Server: SQL Statistics / Batch Requests per sec - this is a good measure of server load, especially if you log it historically to see how it grows over years and could be valuable input for planning hardware upgrade.
SQLServer: Memory Manager / Total Server Memory (KB) - how much RAM sql server takes
SQLServer: Buffer Manager / Page Life Expectancy - should be very high. It's in seconds. Should be > 300.
If it's a virtual machine, you should check the host OS with the same performance counters.
HTH,
Vedran
October 12, 2012 at 2:04 pm
Vedran, DiverCast
thank you very much for your responses, it helps.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply