Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Save results DBCC SQLPERF(UMSSTATS) in a table Expand / Collapse
Author
Message
Posted Saturday, October 6, 2007 9:31 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 5:42 AM
Points: 1,287, Visits: 786
Comments posted to this topic are about the item Save results DBCC SQLPERF(UMSSTATS) in a table


Post #407754
Posted Friday, June 20, 2008 4:18 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, June 3, 2010 5:55 PM
Points: 35, Visits: 134
Could you please provide more information on how to analyse the data? Especially DBCC sqlperf(umsstats) itself?



Also when I run
dbcc sqlperf(umsstats) with tableresults, no_infomsgs

I got the following:
One or more WITH options specified are not valid for this command.

Any ideas?
Post #521098
Posted Thursday, November 11, 2010 4:06 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 5:42 AM
Points: 1,287, Visits: 786
The original script was only tested on SQl Server 2000.
If you use SQL Server 2005, try this:

set nocount on
declare @statistics varchar(32)
declare @value float
declare @Scheduler_ID int
declare @Online int
declare @Num_tasks int
declare @Num_runnable int
declare @Num_workers int
declare @Active_workers int
declare @Work_queued int
declare @cntxt_switches float
declare @cntxt_switches_idle float
declare @preemptive_switches float

create table #umsstats
(
[Statistics] varchar(32) not null,
[Value] float not null
)
create table #umsstats2
(
[Scheduler_ID] int not null,
[Online] int not null,
[Num_tasks] int not null,
[Num_runnable] int not null,
[Num_workers] int not null,
[Active_workers] int not null,
[Work_queued] int not null,
[Cntxt_switches] float not null,
[Cntxt_switches_idle] float not null,
[Preemptive_switches] float not null,
[Datetime] datetime default getdate()
)
insert into #umsstats exec('dbcc sqlperf(umsstats) with no_infomsgs')
declare umsstats_cursor cursor for select * from #umsstats
open umsstats_cursor
fetch next from umsstats_cursor into @statistics,@value
while @@fetch_status = 0
begin
if ltrim(rtrim(@statistics)) = 'Scheduler ID' set @scheduler_id = @value
if ltrim(rtrim(@statistics)) = 'online' set @online = @value
if ltrim(rtrim(@statistics)) = 'num tasks' set @num_tasks = @value
if ltrim(rtrim(@statistics)) = 'num runnable' set @num_runnable = @value
if ltrim(rtrim(@statistics)) = 'num workers' set @num_workers = @value
if ltrim(rtrim(@statistics)) = 'active workers' set @active_workers = @value
if ltrim(rtrim(@statistics)) = 'work queued' set @work_queued = @value
if ltrim(rtrim(@statistics)) = 'cntxt switches' set @cntxt_switches = @value
if ltrim(rtrim(@statistics)) = 'cntxt switches(idle)' set @cntxt_switches_idle = @value
if ltrim(rtrim(@statistics)) = 'preemptive switches'
begin
set @preemptive_switches = @value
insert into #umsstats2(Scheduler_ID,[Online],Num_tasks,Num_runnable,Num_workers,
Active_workers,Work_queued,cntxt_switches,cntxt_switches_idle,preemptive_switches)
values(@Scheduler_ID,@Online,@Num_tasks,@Num_runnable,@Num_workers,
@Active_workers,@Work_queued,@cntxt_switches,@cntxt_switches_idle,@preemptive_switches)
end
fetch next from umsstats_cursor into @statistics,@value
end
select * from #umsstats2

drop table #umsstats
drop table #umsstats2
close umsstats_cursor
deallocate umsstats_cursor




Post #1019221
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse