ProblemSomeone has reported a perfo

Problem
Someone has reported a performance issue for your SQL Server application. When you look into the database server you see CPU utilization is very high and the SQL Server process is consuming most of the CPU. You launch SSMS and run sp_who2 and notice that there are a few SPIDs taking a long time to complete and these queries may be causing the high CPU pressure. At the server level you can only see the overall SQL Server process, but within SQL Server you can see each individual query that is running. Is there a way to tell how much CPU each SQL Server process is consuming? In this article I explain how this can be done.

Solution
Normally we monitor this type of issue using sp_who2, Activity Monitor or DMVs. If there are a lot of processes running on your instance and the CPU is very high, then it's hard to find the exact process eating up your CPU using just the SQL Server tools. One way to correlate the data between what is running within SQL Server and at the Windows level is to use SPID and KPID values to get the exact process.

Before discussing how to do this, we should discuss these terms:

SPID is the SQL Server Process ID number and is assigned by SQL Server to each new connection. It starts with one and is globally unique. SPID 1 through 50 are reserved for system uses and are not used for any user connections.
KPID is the kernel-process ID. Under SQL Server for Windows this is the thread ID number, also known as "ID Thread," and is assigned by Windows when the thread is created. The Thread ID number is a system-wide identifier that uniquely identifies the thread. KPID is visible by querying the KPID column of master..sysprocesses. It is only filled in for spid numbers four and higher. You can also get KPID/ID Thread from Windows Perfmon using the "Thread" object and the "ID Thread" counter.
I am sure you are familiar with the SPID value, but have you ever noticed KPID? Probably not unless you queried the sysprocesses view directly. Let's look at example of how this works.

Example

Step 1
If we look at Task Manager on the server we can see the CPU usage. So the first step is to verify that the SQL Server process (sqlservr), and not some other process, is responsible for the excessive CPU use. Remember, the "sqlservr" process is made up of many different threads, each one generally (but not always) representing a specific user connection. In the screenshot below we see the sqlservr process is taking approximately 70 percent of CPU.

verify the sql server process
Now that we know SQL Server is the main culprit and responsible for the high CPU, the next step is to find out which SQL Server process is causing the high CPU.

Step 2
Now we need to find out what SQL Server thread is causing the high CPU. Once we have this ID Thread we can correlate that ID thread (KPID) to the SPID in SQL Server.

We will use Performance Monitor to get this info. Type perfmon in a Windows CMD prompt or launch from Control Panel.

Click on Add counters and select the "Thread" object in the drop down.

Select these counters at the same time:

% Processor Time
ID Thread
Thread State
Thread Wait Reason
In the right pane, you will see multiple instances from multiple applications, which are running on that server. Since we are looking for "sqlservr" select all of the instances that begin with "sqlservr" from the list box as shown below and click Add. You can add other counters as needed and will see below that I added a few additional counters.

select all the instances that begin with "sqlservr"
Step 3
Press (Ctrl+R) or click on the view Report tab to change from graphical to report view as shown below. Here you should be able to find which ID Thread is eating up your CPU resources by using the scroll bar to see all instances.

Below we have identified which thread is causing the problem. Here you can see ID Thread 30 is taking 46.841% of the total CPU. You can also find the ID Thread which is 872. Now that we know the ID Thread, we can run a query in SSMS to find the actual query.

having identified the id thread, run a query in ssms
Step 4
Our last step is to correlate the Thread ID (KPID) identified in the last step to the SPID. To do this, run the following query in Query analyzer:

SELECT spid, kpid, dbid, cpu, memusage FROM sysprocesses WHERE kpid=872

correlate the thread id(kpid) to spid
Step 5
From the query above we can see SPID 71 is causing the issue. To find how many threads and open transactions this is running we can run this query.

SELECT spid, kpid, status, cpu, memusage, open_tran, dbid FROM sysprocesses WHERE spid=71


Step 6
To get the exact query that is running, we can run DBCC INPUTBUFFER using the SPID. The below output shows us that a backup job is causing our CPU issues on our server.

run dbcc inputbuffer using spid
Next Steps
Use this procedure to find the main culprit in SQL Server which is eating up your CPU. You can use this correlation in many performance related tasks and can track many Perfmon counters to an individual thread and with that you can optimize your SQL Server.
If you have a lot of SQL Server processes, you can use the line graph in Perfmon to find the process that is using high CPU and hover over the line in the chart and it will show you the SQL Server thread. Then you can change to the Report view and scroll over to that SQL Server process to see the details for the counters you selected.

ผลลัพธ์ (ไทย) 1:

ปัญหาบางคนได้รายงานปัญหาประสิทธิภาพการทำงานสำหรับโปรแกรมประยุกต์ของ SQL Server เมื่อคุณมองเข้าไปในเซิร์ฟเวอร์ฐานข้อมูลคุณเห็น ใช้งาน CPU จะสูงมาก และกระบวนการของ SQL Server ที่กำลังใช้งานของ CPU คุณเปิด SSMS และเรียกใช้ sp_who2 และสังเกตว่า มีกี่ Spid ใช้เวลานานให้เสร็จสมบูรณ์ และแบบสอบถามเหล่านี้อาจทำให้เกิดแรงดัน CPU สูง ที่ระดับเซิร์ฟเวอร์ คุณสามารถดูกระบวนการ SQL Server โดยรวม แต่ภายใน SQL Server คุณสามารถดูแต่ละแบบสอบถามแต่ละที่อยู่ มีวิธีการบอกจำนวนของ CPU แต่ละกระบวนการของ SQL Server ที่กำลังใช้งานหรือไม่ ในบทความนี้ ผมอธิบายวิธีนี้สามารถทำโซลูชั่นโดยปกติเราตรวจสอบชนิดนี้ออกโดยใช้ sp_who2 จอภาพกิจกรรมหรือ DMVs ถ้ามีจำนวนมากบนอินสแตนซ์ของกระบวนการ และ CPU สูงมาก แล้วเป็นหากินค่าของ CPU ที่ใช้เพียงเครื่องมือของ SQL Server กระบวนการแน่นอน วิธีหนึ่งที่จะเชื่อมโยงข้อมูลระหว่างสิ่งที่ถูกเรียกใช้ใน SQL Server และที่หน้าต่าง ระดับคือการ ใช้ค่า SPID และ KPID เพื่อรับการแน่นอนก่อนที่จะสนใจวิธีการทำเช่นนี้ เราควรหารือเงื่อนไขเหล่านี้:SPID มีเลขรหัสกระบวนการของเซิร์ฟเวอร์ SQL และ SQL Server ถูกกำหนดแต่ละการเชื่อมต่อใหม่ มันเริ่มต้น ด้วยหนึ่ง และจะไม่ซ้ำกัน SPID 1 ถึง 50 ถูกสงวนไว้สำหรับการใช้ระบบ และไม่ได้ใช้สำหรับการเชื่อมต่อผู้ใช้ใด ๆKPID เป็นรหัสกระบวนการเคอร์เนล ภายใต้ Windows เซิร์ฟเวอร์ SQL นี้เป็นหมายเลข ID ของเธรด เรียกอีก อย่างว่า "ID หัวข้อ และกำหนด โดย Windows เมื่อสร้างเธรด หมายเลขเธรดมีรหัสระบบซึ่งระบุเฉพาะถึงเธรด KPID จะมองเห็นได้ ด้วยการสอบถามคอลัมน์ KPID ของหลัก...sysprocesses เฉพาะรายในสำหรับ spid หมายเลข 4 และมากกว่านั้น คุณสามารถยังได้ KPID/รหัส กระทู้ตรวจประสิทธิภาพของ Windows โดยใช้ "หัวข้อ" วัตถุและตัวนับ "ID หัวข้อ"ผมแน่ใจว่า คุณคุ้นเคยกับค่า SPID แต่มีคุณเคยสังเกต KPID คงไม่เว้นแต่คุณสอบถาม sysprocesses การดูโดยตรง ลองดูตัวอย่างของวิธีการนี้ทำงานตัวอย่างขั้นตอนที่ 1ถ้าเรามองในตัวจัดการงานบนเซิร์ฟเวอร์ เราสามารถดูการใช้ CPU ดังนั้น ขั้นตอนแรกคือการ ตรวจสอบว่า การเซิร์ฟเวอร์ SQL (sqlservr), และไม่ใช่อื่น ๆ กระบวนการ เป็นผู้รับผิดชอบในการใช้ CPU มากเกินไป จำ กระบวนการ "sqlservr" ขึ้นเป็นหัวข้อต่าง ๆ มากมาย แต่ละคนโดยทั่วไป (แต่ไม่เสมอไป) แทนการเชื่อมต่อผู้ใช้ระบุ ในภาพด้านล่าง เราเห็นกระบวนการ sqlservr ใช้เวลาประมาณ 70 เปอร์เซ็นต์ของ CPUตรวจสอบการเซิร์ฟเวอร์ sqlตอนนี้ให้เรารู้ว่า SQL Server เป็นผู้ร้ายหลัก และชอบการ CPU สูง ขั้นตอนถัดไปเพื่อ ค้นหาว่ากระบวนการ SQL Server ทำให้เกิด CPU สูงขั้นตอนที่ 2ตอนนี้ เราต้องหาหัวข้อ SQL Server สาเหตุ CPU สูง เมื่อเราได้หัวข้อ ID นี้ เราสามารถสร้างความสัมพันธ์ที่ ID หัวข้อ (KPID) การ SPID ใน SQL Serverเราจะใช้การตรวจสอบประสิทธิภาพเพื่อรับข้อมูลนี้ พิมพ์ตรวจประสิทธิภาพในพรอมต์คำสั่ง Windows หรือเปิดจาก'แผงควบคุม'คลิกที่เพิ่มเคาน์เตอร์ และเลือก "หัวข้อ" วัตถุในแบบหล่นลงเลือกตัวนับเหล่านี้ในเวลาเดียวกัน:เวลาประมวลผล%เธรด IDสถานะกลุ่มหัวข้อเหตุผลรอเธรดในบานหน้าต่างด้านขวา คุณจะดูอินสแตนซ์หลายจากหลายโปรแกรม ที่ทำงานบนเซิร์ฟเวอร์ เนื่องจากเรากำลังมองหา "sqlservr" เลือกทั้งหมดของอินสแตนซ์ที่เริ่มต้น ด้วย "sqlservr" จากกล่องรายการดังต่อไปนี้ และคลิกเพิ่ม คุณสามารถเพิ่มตัวนับอื่น ๆ ตามความจำเป็น และจะเห็นด้านล่างว่า เพิ่มกี่ตัวนับเพิ่มเติมเลือกอินสแตนซ์ทั้งหมดที่ขึ้นต้น ด้วย "sqlservr"ขั้นตอนที่ 3กด (Ctrl + R) หรือคลิกที่มุมมองแท็บรายงานการเปลี่ยนแปลงจากภาพมุมมองรายงานดังต่อไปนี้ ที่นี่คุณควรจะสามารถหาที่ ID หัวข้อกินค่าทรัพยากรของ CPU โดยใช้แถบเลื่อนเพื่อดูทั้งหมดด้านล่างเราได้ระบุหัวข้อใดเป็นสาเหตุของปัญหา ที่นี่คุณสามารถดู ID หัวข้อ 30 กำลัง 46.841% ของ CPU ทั้งหมด นอกจากนี้คุณยังสามารถหาได้ ID ของเธรดที่ 872 ตอนนี้ให้เรารู้ว่าเธรด ID เราสามารถเรียกใช้แบบสอบถามใน SSMS หาถามจริงมีระบุหัวข้อรหัส เรียกใช้แบบสอบถามใน ssmsขั้นตอนที่ 4ขั้นตอนสุดท้ายของเราคือการ สร้างความสัมพันธ์เธรด ID (KPID) ระบุในขั้นตอนสุดท้ายการ SPID การทำเช่นนี้ เรียกใช้แบบสอบถามต่อไปนี้ในตัววิเคราะห์คำถาม:เลือก spid, kpid, dbid, cpu, sysprocesses memusage จาก kpid = 872ซึ่ง id(kpid) เธรดการ spidขั้นตอนที่ 5จากแบบสอบถามข้างต้น เราสามารถเห็น SPID 71 เป็นสาเหตุของปัญหา จำนวนกระทู้ และเปิดธุรกรรม นี้จะทำงานเราสามารถเรียกใช้แบบสอบถามนี้เลือก spid, kpid สถานะ cpu, memusage, open_tran, sysprocesses dbid จาก spid = 71ขั้นตอนที่ 6ได้สอบถามตรงที่ใช้งาน เราสามารถเรียกใช้ INPUTBUFFER DBCC SPID ใช้ ด้านล่างผลแสดงว่า งานสำรองทำให้เกิดปัญหาของ CPU บนเซิร์ฟเวอร์ของเราเรียกใช้การ dbcc inputbuffer ใช้ spidขั้นตอนถัดไปใช้ขั้นตอนนี้จะหาผู้ร้ายหลักใน SQL Server ซึ่งกินค่าของ CPU คุณสามารถใช้ความสัมพันธ์นี้ประสิทธิภาพการทำงานต่าง ๆ ที่เกี่ยวข้องกับงาน และสามารถติดตามตรวจประสิทธิภาพหลาย counters หัวข้อแต่ละตัว และมีที่ คุณสามารถปรับปรุง SQL Server ของคุณได้ถ้าคุณมีจำนวนมากของกระบวนการ SQL Server คุณสามารถใช้กราฟเส้นในตรวจประสิทธิภาพหากระบวนการที่ใช้ CPU สูงและโฮเวอร์เหนือรายการในแผนภูมิ และมันจะแสดงหัวข้อของ SQL Server แล้ว คุณสามารถเปลี่ยนมุมมองรายงานและเลื่อนผ่านไปว่ากระบวนการ SQL Server เมื่อต้องการดูรายละเอียดสำหรับตัวนับที่คุณเลือก

ผลลัพธ์ (ไทย) 3:

ปัญหา
มีคนรายงานประสิทธิภาพปัญหาสำหรับโปรแกรมเซิร์ฟเวอร์ SQL เมื่อคุณมองเข้าไปในเซิร์ฟเวอร์ฐานข้อมูลที่คุณเห็นการใช้ CPU สูงมากและ SQL Server กระบวนการคือการบริโภคมากที่สุดของซีพียู คุณเปิด SSMS และวิ่ง sp_who2 และแจ้งให้ทราบว่า มีไม่กี่ spids ใช้เวลานานให้เสร็จสมบูรณ์และข้อมูลเหล่านี้อาจจะก่อให้เกิดแรงดัน CPU สูงในระดับ Server คุณสามารถดูโดยรวม SQL Server กระบวนการ แต่ใน SQL Server คุณสามารถดูแต่ละคน แบบสอบถามที่ใช้ มีวิธีการบอก CPU เท่าไรแต่ละ SQL Server กระบวนการบริโภค ? ในบทความนี้ผมจะอธิบายวิธีการนี้สามารถทำได้


ปกติเราตรวจสอบโซลูชั่นประเภทนี้ของปัญหาการใช้ sp_who2 , กิจกรรมการตรวจสอบหรือ dmvs .หากมีจำนวนมากของกระบวนการทำงานบนอินสแตนซ์และ CPU สูงมาก แล้วมันก็ยากที่จะหากระบวนการที่กิน CPU ของคุณโดยใช้เพียง SQL Server เครื่องมือ วิธีหนึ่งเพื่อหาความสัมพันธ์ของข้อมูลระหว่างที่วิ่งภายใน SQL Server และ Windows และระดับที่จะใช้ค่า spid kpid ได้รับกระบวนการที่แน่นอน

ก่อนการอภิปรายเกี่ยวกับวิธีการทำเช่นนี้ เราควรจะหารือเงื่อนไขเหล่านี้ :

spid เป็นกระบวนการเซิร์ฟเวอร์ SQL หมายเลข ID และได้รับมอบหมายจาก SQL Server แต่ละการเชื่อมต่อใหม่ . มันเริ่มต้นด้วยหนึ่ง และเป็นเอกลักษณ์ของทั่วโลก spid 1 ถึง 50 จะถูกสงวนไว้สำหรับการใช้ระบบและไม่ได้ถูกใช้สำหรับการเชื่อมต่อผู้ใช้ใด ๆ .
kpid เป็น ID กระบวนการเคอร์เนลภายใต้ SQL Server สำหรับ Windows นี้เป็นกระทู้เลขบัตรประชาชน หรือที่เรียกว่า " รหัสหัวข้อ " และได้รับมอบหมายจากหน้าต่างเมื่อด้ายถูกสร้างขึ้นกระทู้หมายเลข ID เป็นระบบระบุความกว้างที่เฉพาะระบุหัวข้อ kpid สามารถมองเห็นได้โดยการสอบถาม kpid คอลัมน์ของอาจารย์ . . . . . . . sysprocesses . มันมีแต่ใน spid หมายเลข 4 และสูงกว่า นอกจากนี้คุณยังสามารถได้รับ kpid / ID ด้ายจาก Windows perfmon โดยใช้ " หัวข้อ " และ " นับวัตถุ ID ด้าย " .
ผมมั่นใจว่าคุณจะคุ้นเคยกับค่า spid แต่คุณเคยสังเกต kpid ?อาจจะไม่ถ้าคุณสอบถาม sysprocesses ดูโดยตรง ลองดูตัวอย่างของวิธีการทำงานนี้

ตัวอย่างขั้นที่ 1


ถ้าเราดูที่ Task Manager บนเซิร์ฟเวอร์เราสามารถดูการใช้งาน CPU . ดังนั้นขั้นตอนแรกคือการตรวจสอบว่า SQL Server กระบวนการ ( sqlservr ) และไม่ใช่กระบวนการอื่น มีความรับผิดชอบในการใช้ CPU มากเกินไป จำไว้" กระบวนการ sqlservr " ถูกสร้างขึ้นจากกระทู้ต่าง ๆ มากมาย แต่ละคนมักจะ ( แต่ไม่เสมอ ) ซึ่งเป็นตัวแทนของการเชื่อมต่อผู้ใช้ที่เฉพาะเจาะจง ในภาพด้านล่างที่เราเห็นกระบวนการ sqlservr ใช้เวลาประมาณ 70 เปอร์เซ็นต์ของ CPU .

ตรวจสอบกระบวนการเซิร์ฟเวอร์ SQL
ตอนนี้เรารู้ SQL Server เป็นผู้ร้ายหลักรับผิดชอบและ CPU สูงขั้นตอนต่อไปคือการดูที่ SQL Server กระบวนการทำให้ CPU สูง ขั้นตอนที่ 2


ตอนนี้เราต้องดูว่า SQL Server ด้ายเป็นสาเหตุสูง CPU เมื่อเราได้ ID ด้ายเราสามารถมีความสัมพันธ์ที่ ID ด้าย ( kpid ) เพื่อ spid ใน SQL Server .

เราจะใช้การตรวจสอบประสิทธิภาพในการรับข้อมูลนี้ ประเภท perfmon ในหน้าต่าง cmd prompt หรือเปิด

จากแผงควบคุมคลิกเพิ่มเคาน์เตอร์และเลือก " หัวข้อ " วัตถุในแบบเลื่อนลง

เลือกเคาน์เตอร์เหล่านี้ในเวลาเดียวกัน :

%
ID ประมวลผลเวลาด้าย


รอด้ายด้ายรัฐเหตุผลในบานหน้าต่างด้านขวา คุณจะเห็นหลายอินสแตนซ์จากหลายโปรแกรม ซึ่งจะทำงานบนเซิร์ฟเวอร์ที่เนื่องจากเรากำลังมองหา " sqlservr " เลือกของกรณีที่ขึ้นต้นด้วย " sqlservr " จากกล่องรายการตามที่แสดงด้านล่าง และคลิกเพิ่ม คุณสามารถเพิ่มเคาน์เตอร์อื่น ๆได้ตามต้องการ และจะเห็นด้านล่างที่ฉันได้เพิ่มเคาน์เตอร์ไม่กี่เพิ่มเติม

เลือกทุกกรณีที่ขึ้นต้นด้วย " sqlservr "
3
ขั้นตอนกด ( Ctrl R ) หรือคลิกที่แท็บรายงานมุมมองเปลี่ยนไปจากกราฟิกเพื่อดูรายงานที่แสดงด้านล่างที่นี่คุณสามารถค้นหาซึ่ง ID ด้ายกินทรัพยากร CPU ของคุณโดยการใช้แถบเลื่อนเพื่อดูทั้งหมดอินสแตนซ์

ด้านล่างเราได้ระบุหัวข้อใดเป็นสาเหตุของปัญหา ที่นี่คุณสามารถดู ID ด้าย 30 กำลัง 46.841 % ของซีพียูทั้งหมด นอกจากนี้คุณยังสามารถค้นหาหมายเลขหัวข้อที่เป็นคุณ . ตอนนี้ที่เรารู้ ID ด้าย เราสามารถเรียกใช้แบบสอบถามใน SSMS หาแบบสอบถามจริง

มีระบุหมายเลขกระทู้ เรียกใช้แบบสอบถามใน SSMS

ขั้นตอน 4 ขั้นตอนสุดท้ายของเราคือความสัมพันธ์หัวข้อ ID ( kpid ) ระบุในขั้นตอนสุดท้ายเพื่อ spid . ทำแบบนี้ เรียกใช้แบบสอบถามในการวิเคราะห์แบบสอบถามต่อไปนี้ :

เลือก spid kpid dbid , , , CPU , memusage จาก sysprocesses ที่ kpid = ผม

มีความสัมพันธ์หัวข้อ ID ( kpid )

ขั้นตอน spid 5 จากคำถามข้างต้นเราสามารถเห็น spid 71 เป็นสาเหตุของปัญหาเพื่อหาวิธีการหลายหัวข้อและรายการนี้เปิดอยู่ เราสามารถเรียกใช้แบบสอบถามนี้

เลือก spid kpid , สถานะ , CPU , memusage open_tran dbid , , จาก sysprocesses ที่ spid = 71


6
รับแน่นอน แบบสอบถามที่ใช้ เราสามารถเรียกใช้ dbcc inputbuffer spid . ด้านล่างออกแสดงให้เราเห็นว่า backup งานก่อให้เกิดปัญหา CPU บนเซิร์ฟเวอร์ของเรา inputbuffer dbcc วิ่ง spid

ใช้ขั้นตอนต่อไป
ใช้ขั้นตอนนี้เพื่อหาผู้ร้ายหลักของ SQL Server ซึ่งจะกิน CPU ของคุณ คุณสามารถใช้ความสัมพันธ์นี้ในการปฏิบัติงานที่เกี่ยวข้องหลายงานและสามารถติดตามเคาน์เตอร์ perfmon มากด้ายรายบุคคล และ ที่คุณสามารถเพิ่มประสิทธิภาพของ SQL Server ของคุณ .
ถ้าคุณมีมากของกระบวนการ SQL Server ,คุณสามารถใช้เส้นกราฟใน perfmon เพื่อหากระบวนการที่ใช้ CPU สูงและโฉบเหนือเส้นในแผนภูมิและมันจะแสดง SQL Server ด้าย แล้วคุณก็เปลี่ยนไปดูรายงานและเลื่อนไปที่ SQL Server กระบวนการเพื่อดูรายละเอียดสำหรับเคาน์เตอร์ที่คุณเลือก .