cannot view SQL Server Databases: Backup/Restore throughput in perfmon
-
im trying to measure the amount of CPU used in a backup and restore, using the SQL Server Databases: Backup Restore counter in Perfmon.
I can select The counter, but it does not let me select an instance to monitor.
I have 3 instances on my machine, 2 sql 2005 and 1 sql 2008. why do these instances not appear? i dont know how to set up this monitoring.
-
winston Smith (4/4/2008)
im trying to measure the amount of CPU used in a backup and restore, using the SQL Server Databases: Backup Restore counter in Perfmon.I can select The counter, but it does not let me select an instance to monitor.
I have 3 instances on my machine, 2 sql 2005 and 1 sql 2008. why do these instances not appear? i dont know how to set up this monitoring.
You can use DMVs to get an estimate of the amount of CPU an operation consumes:
select
[Average CPU used] = qs.total_worker_time / qs.execution_count
,qs.total_worker_time
,qs.execution_count
,[Individual Query] = SUBSTRING (qt.text,qs.statement_start_offset/2,
(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)
,qt.text
,qs.last_execution_time
,DatabaseName = DB_NAME(qt.dbid)
from
sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
where
DB_NAME(qt.dbid) = 'UTILITY' -- database in which the backup operation occurs goes here
order by
[Average CPU used] DESC;
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url] -
i had an idea i could do it this way also but i wanted the nice visual colored lines in perfmon for a presentation on how much quicker a backup/restore is in sql 2008
-
The colored lines are a display feature. Not something you return in T-SQL.
-
You can download & install Performance Dashboard Reports for SQL2005. SQL2008 CTP has it built in already. There is a backup and restore events report, and it will show you the duration of the backup/restore.
-
Steve Jones - Editor (4/8/2008)
The colored lines are a display feature. Not something you return in T-SQL.thanks Steve. This again is something i know. I know Perfmon is not part of sq
-
Steve Jones - Editor (4/8/2008)
The colored lines are a display feature. Not something you return in T-SQL.thanks Steve. This again is something i know. I know Perfmon is not part of sql server at all. but i expect to be able to select any instances of sql server i have installed on my pc , in perfmon to compare different attributes and performance of them. that is what it is for.
this is why i want the "coloured lines" from perf mon, to show that backing up and restoring a db in sql 2008 with compression is quicker than the same task in sql server 2005. what better way to demonstrate this to business folk than on a nice graph with colourd lines in realtime. unfortunately i cant do this on my pc, although i can select any instance of sql server on other servers in my domain.
see the image to explain better.
in the instances box i should see at least 4 instances, as i have 3 sql 2005 instances installed and 1 sql2008 instance installed. on any other server i can see all instances installed on that server, but on my pc ( where this screen capture was taken) i can see no instances and thus not monitor the backup restore process duration
-
That's strange. Do you have SS2k8 on a server? I wonder if it's messed up the counters.
Or it could be an issue with Vista.
-
yea. i have sqlserver 2008 installed.
I have discovered that if in the "Select counters from computer" box i type localhost or 127.0.0..1 i can see all the db's from my sql server 2005 instances, but none from my sql server 2008 instance.
i disabled all sql server 2005 services on my pc and left only sql server 2008 instances open but still cannot see any of the sql 2008 databases
-
Don't select from 127.0.0.1 ( I think localhost resolves to this). The listeners for other instances might not come up. Try the Windows name.
-
still shows no instances. is there some settings in sql server or new to sql server 2008 that means you have to enable it to be viewable in perfmon, or perhaps its a permission issue?
Funny how i dont have this issue with my sql server 2005 instances
-
No idea. All my 2008 stuff is in a VM. Somewhat afraid to drop it on a machine I need 🙂
If anything, I'd guess that there's some issue with the counters because they're half baked.
-
i know it can be done as ive seen blogs where they compare the the restore time of a compressed and uncompressed DB using perfmon.
If i come up with anything il let you know. thanks for all your help
-
one more piece of info that may be useful, i can see any databases that were created during the installation of slq server 2008, but the one im interested in, that i created myself is not viewable in perfmon.
EDIT: The above information is incorrect. when all sql 2005 instances are disabled, and sql 2008 services are left enabled and running, i can see no databases in perfmon for my computer when using localhost, computerName or 127.0.0.1
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply