March 23, 2016 at 1:57 pm
I just downloaded this script.
1. Is there any documentation\tutorial explaining the meaning (and units) of the columns returned?
2. We have an issue with high CPU on a sql server. What are the key elements of sp_whoisactive that could indicate the source of the problem? (The CPU column seems to show how long a query took to run but I don't know if that necessarily translates into high CPU.)
TIA,
BD
March 23, 2016 at 2:29 pm
Have you read this? Including the links https://www.brentozar.com/archive/2010/09/sql-server-dba-scripts-how-to-find-slow-sql-server-queries/
Also, the procedure is heavily commented to document functionalities, or you could use the help parameter.
EXEC dbo.sp_WhoIsActive @help =1;
March 23, 2016 at 5:48 pm
Luis,
I will watch that URL tonight!
BD
March 29, 2016 at 4:08 am
If you have high cpu usage, it smells to me like queries which aren't able to use indexes, either because they are not there, or they are but the query is not able to use them to do seeks.
.....unless there is a query which has no alternative but to do massive joins without filtering, like some crazy report.
Are you using 2008 or are you just posting here because there is nowhere else to post a performance related problem for 2012+?
I ask because 2012+ extended events will help find those queries without having to run profiler.
March 29, 2016 at 7:18 am
SSC-Addicted,
We have Sql 2008! Everyone holds the tantalizing "sp_WhoIsActive" in front of me but I was hoping to find documentation describing how to effectively use that script. Sine I didn't find any I am now digging into the t-sql. (Something I usually do once but normally after I'm convinced the offering is worth the effort.)
BD
March 29, 2016 at 8:42 am
Do you need a script which will give you top 10 costliest procedures or costliest queries? Added them below just incase.
This is the first route. whoisactive will only tell you at a point in time, as far as I know (and I'm sure people will let me know if I am wrong), which is fine for when someone has left an uncommitted transaction.
But for consistently poor performing sql, I would suggest finding top 10 procs that use highest IO, and also top 10 queries which use highest IO, and find missing indexes,.
Some procs may not use indexes due to the way they are written, so first look at the queries before throwing indexes at the problem.
A warning, do not just create the indexes suggested by the missing index script.
Consideration must be made to factorise the indexes needed into the least amount of indexes to serve the most queries.
Also, the missing indexes suggested are nonclustered. You need to decide whether it is best to create a clustered index or many covering nonclustered indexes.
You may actually benefit from a tailored filtered index so and so use the missing index as a guide, and symptom to a problem, not the solution.
Try these.
--ProcInformation
Use MyDB
GO
select top 10 sch.name+'.'+o.name,s.*,t.text,p.query_plan
from sys.dm_exec_procedure_stats S
inner join sys.objects o on s.object_id = o.object_id
inner join sys.schemas sch on sch.schema_id = o.schema_id
cross apply sys.dm_exec_sql_text(sql_handle) t
outer apply sys.dm_exec_query_plan(plan_handle) p
--where text like '%mytext%'
order by total_logical_reads desc
go
--QueryInformation
SELECT top 10
SUBSTRING(text, statement_start_offset/2+1,
((CASE WHEN statement_end_offset = -1 THEN DATALENGTH(text)
ELSE statement_end_offset
END - statement_start_offset)/2) + 1) AS running_statement,
left(replace(replace(SUBSTRING(text, statement_start_offset/2+1,
((CASE WHEN statement_end_offset = -1 THEN DATALENGTH(text)
ELSE statement_end_offset
END - statement_start_offset)/2) + 1) ,char(13),' '),' ',' '),30)
,text AS current_batch,
t.dbid,
t.objectid,
p.query_plan,
s.*
FROM sys.dm_exec_query_stats s
cross apply sys.dm_exec_sql_text(sql_handle) t
outer apply sys.dm_exec_query_plan(plan_handle) p
--where text like '%mytext%'
order by s.total_logical_reads desc
go
PRINT 'Missing Indexes: '
PRINT 'The "improvement_measure" column is an indicator of the (estimated) improvement that might '
PRINT 'be seen if the index was created. This is a unitless number, and has meaning only relative '
PRINT 'the same number for other indexes. The measure is a combination of the avg_total_user_cost, '
PRINT 'avg_user_impact, user_seeks, and user_scans columns in sys.dm_db_missing_index_group_stats.'
PRINT ''
PRINT '-- Missing Indexes --'
SELECT statement,mid.equality_columns,mid.inequality_columns,
migs.unique_compiles compiles,migs.user_seeks Seeks, CONVERT (decimal (28,0), migs.avg_total_user_cost/100 * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)) AS saving,
migs.avg_total_user_cost cost,migs.avg_user_impact impact,migs.last_user_seek LastSeek,
'CREATE INDEX misIND'+' ON ' + mid.statement
+ ' (' + ISNULL (mid.equality_columns,'')
+ CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ',' ELSE '' END + ISNULL (mid.inequality_columns, '')
+ ')' + ISNULL (' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement
FROM sys.dm_db_missing_index_groups mig
INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
--WHERE DB_NAME(mid.database_id) = 'MyDB' --and CONVERT (decimal (28,0), migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)) > 10000
--AND migs.last_user_seek > GETDATE()-1 -- exclude old scans
--and CONVERT (decimal (28,1), migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)) > 100000
--AND migs.avg_total_user_cost > 500
--and user_seeks > 500
ORDER BY
1,2,6 desc
--1 desc
PRINT ''
GO
March 29, 2016 at 5:58 pm
Very interesting script. I'm reviewing it now....
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply