[sql-server] How do I find out what is hammering my SQL Server?

You can run the SQL Profiler, and filter by CPU or Duration so that you're excluding all the "small stuff". Then it should be a lot easier to determine if you have a problem like a specific stored proc that is running much longer than it should (could be a missing index or something).

Two caveats:

  • If the problem is massive amounts of tiny transactions, then the filter I describe above would exclude them, and you'd miss this.
  • Also, if the problem is a single, massive job (like an 8-hour analysis job or a poorly designed select that has to cross-join a billion rows) then you might not see this in the profiler until it is completely done, depending on what events you're profiling (sp:completed vs sp:statementcompleted).

But normally I start with the Activity Monitor or sp_who2.

Examples related to sql-server

Passing multiple values for same variable in stored procedure SQL permissions for roles Count the Number of Tables in a SQL Server Database Visual Studio 2017 does not have Business Intelligence Integration Services/Projects ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database How to create temp table using Create statement in SQL Server? SQL Query Where Date = Today Minus 7 Days How do I pass a list as a parameter in a stored procedure? SQL Server date format yyyymmdd

Examples related to performance

Why is 2 * (i * i) faster than 2 * i * i in Java? What is the difference between spark.sql.shuffle.partitions and spark.default.parallelism? How to check if a key exists in Json Object and get its value Why does C++ code for testing the Collatz conjecture run faster than hand-written assembly? Most efficient way to map function over numpy array The most efficient way to remove first N elements in a list? Fastest way to get the first n elements of a List into an Array Why is "1000000000000000 in range(1000000000000001)" so fast in Python 3? pandas loc vs. iloc vs. at vs. iat? Android Recyclerview vs ListView with Viewholder

Examples related to cpu-usage

High CPU Utilization in java application - why? How prevent CPU usage 100% because of worker process in iis SQL Server 100% CPU Utilization - One database shows high CPU usage than others How to read the Stock CPU Usage data What is the correct Performance Counter to get CPU and Memory Usage of a Process? Get Memory Usage in Android How to calculate the CPU usage of a process by PID in Linux from C? MySQL high CPU usage Retrieve CPU usage and memory usage of a single process on Linux? How do I find out what is hammering my SQL Server?