August 8, 2011 at 12:04 pm
Are there any general guidelines for performance tuning of a CPU bound SQL Server system? We have a system with just 1 database. If less than 5 users are connected, it works fine but if more than 10 users are connected, CPU consumption would be over 80%. Memory and I/O are just fine. Page life expectancy in thousands and no waits on PAGEIOLATCH.
I am looking for general guidelines such that system can scale fine even if 100 users are accessing the application.
Thanks.
August 8, 2011 at 12:13 pm
SQL Server is able to handle more than 100 Users too. All you need to find is what queries are causing your CPU to use 80%.
Here is query I had it from long time but not written by me.
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT TOP 20
CAST((qs.total_worker_time) / 1000000.0 AS DECIMAL(28,2))
AS [Total CPU time (s)]
, CAST(qs.total_worker_time * 100.0 / qs.total_elapsed_time
AS DECIMAL(28,2)) AS [% CPU]
, CAST((qs.total_elapsed_time - qs.total_worker_time)* 100.0 /
qs.total_elapsed_time AS DECIMAL(28, 2)) AS [% Waiting], qs.execution_count
, CAST((qs.total_worker_time) / 1000000.0
/ qs.execution_count AS DECIMAL(28, 2)) AS [CPU time average (s)]
, SUBSTRING (qt.text,(qs.statement_start_offset/2) + 1,
((CASE WHEN qs.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2) + 1) AS [Individual Query]
, qt.text AS [Parent Query]
, DB_NAME(qt.dbid) AS DatabaseName
, qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE qs.total_elapsed_time > 0
ORDER BY [Total CPU time (s)] DESC
After finding the queries tune them. My guess is there should be parallelism
August 8, 2011 at 12:36 pm
Using the DMVs is quick, but it's not necessarily going to show you the complete picture due to queries being aged from cache, cache being cleared and queries that aren't cached at all.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply