Initial SQL Server performance issues (1/4)

When you are a DBA, many people will complain to you: "This program data loading and snail, you see the server is not a problem?" "There are many reasons for this problem. May be the application server problems, network problems, program implementation of the problem, the database server overload. Regardless of the problem, the database is always the first to be complained about. Our DBA's job is to find out where the problem lies and to solve them.

Problem solving the first step, diagnostic analysis:

1 SELECT 2parent_node_id asnode_id,3 COUNT(*) as [No.of CPU in the NUMA],4 SUM(COUNT(*)) Over() as [Total No. of CPU],5 SUM(Runnable_tasks_count) as [Runnable Task Count], 6 SUM(Pending_disk_io_count) as [Pending disk I/O count],7 SUM(Work_queue_count) as  [Work Queue Count]8  fromSys.dm_os_schedulersWHEREStatus='VISIBLE ONLINE' GROUP  byparent_node_id

Return result Description:

• no.of CPU in the NUMA: number of CPUs allocated to NUMA nodes, or number of schedules ( number of schedulers ).
• total No. Of CPU: The total number of CPUs available on the server.
• runnable Task Count: In the operational queue, waiting to be scheduled for recurrence, The number of workers (workers) that are used to assign the task (tasks). That is, the number of requests that can be run in the queue.
• pending disk I/O count: The number of wait Io waiting to be completed. Each schedule has a wait IO checklist to determine whether they are complete during context switching. This number is incremented when the request is inserted. When the request is complete, the number is reduced.
• work queue count: The number of tasks in the wait queue. These tasks are waiting for workers to take away.

I will save the output of this script to a table and set it to run every 10 minutes, and the collection runs for 2 days. This gives us a basic understanding of the health of the server. On the server I tested, when runnable Task count was 10, the user was complaining that the server was slow! Normally, this number should be less than 10 for each node. This gives us an overview of the current system operation. If the output of this step is normal, we can rule out the problem with the database server, the slow response may be caused by blocking that we cannot control, or only some of the sessions are slow, not the entire server.

This is the 1th step of the problem analysis of diagnostic methods, the next article will explain how to deal with the following.

2 drawings to help you understand the relationship between tasks, workers (works), scheduling (schedulers).

For each cpu,sqlserver there will be a scheduler corresponding to it. In each scheduler, there will be several workers, corresponding to each thread. After the client sends a request, SQL decomposes it into one or more tasks. Depending on how busy each scheduler is, the task is assigned to a scheduler. If there is free worker,task in the scheduler, it will be assigned to a worker. If not, Scheduler creates a new worker for use by the task. If the worker in the scheduler has reached his upper limit and they all have a task to run, then the new task has to enter the state of the waiting worker.

Initial SQL Server performance issues (1/4)