Hi guys , may I know is there any examples of store procedure/scripts for monitoring replication status and performance ? I just know about sp_replmonitorhelppublisher. Thx for the assistance.
From,
Hans
We are in plan to build a Monitoring tool using PowerShell and Performance Monitor which could monitor 10 to 20 servers. Do you have any reference of any existing tool using Performance Monitor to monitor the SQL Server and available for free? I didn't want to put some effort, if something is available already.
View 2 Replies View RelatedHi All,
My question is: can the parse of this command bellow afect the perfomance of my store procedure.
SET @sql = 'insert xxx select TOP ' + CAST(@a as varchar)+ ' * from yyy where zzz = ''' + @zzz + ''' and yyy = ''' + @yyy + ''' order by zzz desc, yyy desc, www desc' exec (@sql)
Thanks
Sergio
:rolleyes:
After you've created an SSIS package or multi-package solution, the next step is usually to deploy it to a production environment and schedule it to run at regular intervals. We took this approach at our company and scheduled the master package as a step in a SqlAgent Job. I import the dtsx packages into the server by connecting to the Integration Services connection and importing them into Stored Packages > MSDB. Next, I create a SqlAgent job via the Database Engine connection under SQL Server Agent > Jobs. I then add a step and select "SQL Server Integration Services Package" under the step Type menu. This brings up a powerful dialog that lets you set your production runtime environment parameters for the connection strings, SSIS package variables, config file path, and logging parameters.
As part of my deployment process, I wanted to run the job one time at the end of the build and check the status before deeming the build a complete success. The script is long running (several minutes) so I had to deal with the issue of polling. I had been recently using the WHILE loop in T-SQL and I also found the Waitfor Delay command. I discovered how to get to the job activity via the sp_help_jobactivity extended stored proc. The source for this proc indicates that you can get to the most recent execution of the job, including the currently running job. If the job is running, the run_status column of the resultset is null. Once the job completes, this flag is 0 for failure and 1 for success. I wanted to query the underlying tables directly so I extracted the bare minimum queries to get at the fields that I needed. I provided timeout variables so you can set your desired polling interval and timeout.
I am not sure whether this while/waitfor loop approach is very CPU-intensive or can cause locking problems. I'm hoping it does not. But it's a convenient way to use T-SQL to test for a SqlAgent job status if your build tool supports running a query against a database, which most of them do. You can always use WMI or a .Net app to achieve the same result. If someone has done this already, perhaps you can post your code sample or a link to it as a reply to this thread.
I'm assuming that other DBAs and developers responsible for production deployment and monitoring will find this script useful. I also wouldn't mind if I scored a couple MVP points . One confusion I had to deal with was that sp_help_jobactivity returns a return code of 0 or 1 with the opposite meaning as the run_status column that is in the result set. It took me a little while to figure this out since possible values for run_status were not well-documented.
Enjoy!
Norm Katz
www.ipconsulting.com
/************************************************************************************************
**
** Script Name: CheckJobStatus.sql
** Description: Checks the status of the last instance of a SqlAgentJob
** Author: Norm Katz
** Date: 11-15-2007
************************************************************************************************/
USE MSDB
GO
DECLARE @job_id UNIQUEIDENTIFIER
DECLARE @job_name sysname
DECLARE @jobStatus int
DECLARE @message varchar(1000)
DECLARE @jobid int
DECLARE @jobEndTime varchar(32)
DECLARE @runtime int
DECLARE @timeout int
DECLARE @delay datetime
DECLARE @delayIncr int
DECLARE @session_id int
DECLARE @jobCheckTimeoutOccurred bit
SET @job_name = 'MySqlAgentJobName'
-- initialize @delay using the datetime string, e.g., "00:00:05" for 5 seconds
-- initialize @timeout to seconds you want to wait, e.g. 300 for 5 minutes.
-- initialize @delayIncr to the integer number of seconds for @delay
SET @runtime = 0
SET @timeout = 300
SET @delay = '00:00:05'
SET @delayIncr = 5
SET @jobCheckTimeoutOccurred = 0
SELECT TOP(1) @session_id = session_id FROM msdb.dbo.syssessions ORDER by agent_start_date DESC
exec msdb.dbo.sp_verify_job_identifiers '@job_name', '@job_id', @job_name OUTPUT, @job_id OUTPUT
print 'Checking status of ' + @job_name
WHILE @jobStatus is null
BEGIN
SELECT @job_id = ja.job_id,
@message = jh.message,
@jobStatus = jh.run_status,
@jobEndTime = stop_execution_date
FROM
(msdb.dbo.sysjobactivity ja LEFT JOIN msdb.dbo.sysjobhistory jh ON ja.job_history_id = jh.instance_id)
join msdb.dbo.sysjobs_view j on ja.job_id = j.job_id
WHERE
ja.job_id = @job_id
AND
ja.session_id = @session_id
IF @jobStatus is null
BEGIN
SET @runtime = @runtime + @delayIncr
IF @runtime > @timeout
BEGIN
SET @jobCheckTimeoutOccurred = 1
SET @jobStatus = -1
BREAK
END
Waitfor Delay @delay
END
ELSE
BREAK
END
IF @jobCheckTimeoutOccurred = 1
print 'Check for status of Job ID ' + cast(@job_id as varchar(64)) + ' for ' + @job_name + ' timed out after ' + cast(@timeout as varchar(5)) + ' seconds'
ELSE
BEGIN
print 'Job ID ' + cast(@job_id as varchar(64)) + ' for ' + @job_name + ' completed on ' + @jobEndTime
IF @jobStatus = 1
print 'Job Succeeded'
ELSE
print 'Job Failed with Message: ' + @message
END
SELECT @jobStatus
-- The final select query will return one of three values:
-- -1: Script timed out
-- 0: Job failed
-- 1: Job Succeeded
Hi,
I'm new to the service broker service. All I want to do is to monitor the queue status. If the queue is disabled, send me an email alert.
Can you let me know what's the best way to accomplish it?
Thanks,
Jia
Hi, I'd like to know which is the best choice to execute a store procedure that execute transformations from table to files using bcp command, 2 millions rows everyday (i'ts a Data Warehouse database). So in the sql task in connectionType I had choiced ADO.NET is the best option?
Thanks
Hi,
I am new to sql server and sould like to know how I can monitor the server performance on sql servers 6.5 and 7. In sybase we can run sp_sysmon. Is there anything similar to this for sql server.
Thanks.
Hi
I am learning SQL SERVER ..Can anyone tell me how do I tune a given stored procedure in 6.5 and 7.0.?
In performance tunning which counters are really necessary to watch and the remedies to be taken?
Thanks
Reddy
Does anyone know of a way to determine which SPs or which tables are being accessed the most heavily or often from an application? I have inherited a site that is heavily used, but poorly tuned. However we have distributed C/S apps that hit the server and do not contain debug code. In addition, the SPs are encrypted and I do not want to replace them in the short term with new SPs that log hits.
I realize I can use triggers, but is there any other method?
Hi,
I want to collect performance measures regarding the import of data
and the growth of resulting extract_tables.
I use - say - 15 tables from a erp-system (like JDE Edwards)
to build a -say - sales-warehouse and a MS-OLAP-cube.
For every incoming table I got a dts-package witch is
protocolled into msdb.sysdtspackagelog.
Every package got the name
[Build]_[Subsystem]_[Table_name]
e.g. JDEdwards_Sales_F0005
The destination table is namend e.g. extr_F0005
Now:
With a seperate DTS-package I transport the
records from msdb-db into my build-db
- say - JDEdwardsExtract.
Name: extr_performance_monitor
(eventually filter on buildname, because there are several builds in
my system)
So this result is quit good and easy to handle for
seeing elapsed time per day.
But the dtslog won't tell me, how many records the dtspackage
had to copy.(and there is one at least with no records (Cubeupdate))
Now the count(*) comes in.
In the dts-package
sys...log ---- to --- extr_performance_monitor
I added the columns
extr_table_name,
extr_table_rowcount,
extr_table_timestamp.
With
select name, 'extr_' + replace(name, '[Build]_[Subsystem]','') as
extr_table_name from extr_performance_monitor
I cut the original dts packagename down to the extr_.. name.
But I don't have a clue how to solve the count(*)
Example:
dts_package_name,.... , extr_table_name,extr_table_rowcount, extr_table_timestamp
[Build]_[SS]_[F0005], ..., extr_F0005, 0, sysdate.
I think about a package wich is running after the last
data_import (and cube_refresh) is done.
(but the same day)
So the result could be:
Table_name (as dimension category)
Time to perform
Number of records in import table
Records per second.
The next step could be to look for required space.
The result should be a grafik - say - over 12 month
were you can easily see the amount of data performend
time consumend, (table space used),
and - very important -
you could extrapolate your hardware requirements.
Looking forward for any hints.
Thanks in advance.
Michael
My SQL Server 2000 system is pegged. Disk activity is maxed out and system is very unresponsive. Several people have database tasks running through this system and I'm pretty sure there is a single application that is the culprit and I'd like to identify which one.
Does anyone have any practical tips on using "Process Info" in Enterprise Manager? What units are CPU and Physical I/O displayed in? Why does the column sort on these fields not work as expected?
Do I just pick the process with the largest Physical I/O and assume that's the problem?
I was wondering if the CPU is the bottleneck. Hence I used the PerformanceMonitor to look up some values. Here are the results.Counter Scale Average MaximumMinimum%Disk Time 1 2.74 24.9320Current Disk Queue Length 10 0.090 6 0Buffer cache hit ratio 1 99.752 99.75299.751%Processor Time (Total) 1 2.307 9.6880.313%Processor Time (sqlserv) 1 1.218 7.188 0Processor Queue Length 10 6.960 11 5The thing that puzzles me is that the Processor Time is quite low but theProcessor Queue Length is very high. Why is the processor processes queuingup when the processor not bogged down ? I would assume both values to be onthe high side. Is the high buffer hit ratio an indicator that the CPU iswaiting for some reads from the buffer before processing the next process?The users often run reports on historical data. That might be the reason forthe high buffer hit rates.Am I monitoring the wrong values ?Any advise would be very much appreciated and thanks in advance.Pit
View 1 Replies View RelatedHi
In performance monitor, Add Counters interface, it is pointed to the local SQL server as default.
Probably a kind of 'stu..' question. When I typied a remote sql server name in the "select counters from computer dropdown listbox", which is in my E.M. registered already, I got the error of "Unable To Connect To the Machine".
thanks
David
I am looking for a good solution for monitoring the performance on my SQL Server. I've used Performance Monitor but think there are better solutions out there. Specifically, I'd like to run the app on my desktop to monitor the server remotely. I'd like the app to run constantly and notify me if anything weird starts to happen.
I'm looking at i3 for SQL Server by Veritas as an option, but it's gonna cost ~$2k.
Anybody have comments, suggestions with the Veritas product or any other packages out there you would recommend.
Thanks,
Alex
Running our software on many client sites.
Application:
1) using a 4gl language
2) uses ntwdblib.dll
Client MSSQL Servers:
1) varies from site to site on average dual core, 4GB, 20-100GB HD
2) MSSQL 2000 sp3-4, 2005 sp1-2
A handfull of our clients have complained about how slow things seem to be working. So after getting online with the customer and doing some investigating/testing, I have found that 'slow' is not the word to use. We are talking about snails pace. Now this same software at our other mssql sites doesnt seem to have any problems. very good performance numbers. Some of our customers with single cpu, 2GB, 10GB HD have better performance than a couple of our 4 cpu, 16GB, 2 - 20GB HD systems.
we have written a simple test program that we run on these slow sites that individually (useing local drives/files) are comparable to our faster sites. We then run the test program against the MSSQL database/tables. On the servers the run times are about the same. However, when we run it on a workstation the results go off the scale. At our good sites, the test time is around 10 minutes, on our bad sites the test time is around 1 HR 30 minutes.
Now i know this sounds like a network issue, but one of these slow sites had someone come out and check the network traffic/packets/routers/switches. then we paid to send that person to one of our faster sites to test their network. The results were that our slower client had a faster network. Our slower client uses CISCO smart switches/hubs/router.
Now for my question, does anyone know of or could recommend something that i could use to determine what is happening between the server and workstation on these slow sites.
Is there a problem with CISCO equipment and MSSQL 2005, NTWDBLIB.DLL. I am looking for anything here.
We have a shared SQL 2005 instance and we have concerns about performance.
What tools would you suggest to monitor performance of a SQL server database? Would you use perf-mon? If so which monitors give you the best results?
Any help would be appreciated.
Thank you in advance.
I have SQL 7 running on an Active/Passive Cluster configuration. My problem is that I can not see any
SQL objects listed on perfmon.
Can this be because of the Cluster configuration? I trying to run perfmon directly from the main system console.
Thanks,
Randy
Hi,
I'm looking for tool that will help me to monitor and analyze SQL server performance including SQL activities and server activities(cpu, memory usage) as well.
I appriciate any feedback,
Thank you,
Yelena
I'm fairly new to the DBA thing.
I've been doing quite a bit of research on monitoring MSSQL performance.
What tools are you guys using?
PerfMon, Dbartisan, Spotlight, or what?
I'd like to find some free or affordable way to watch our Servers to check health/performance/etc.
Does SQL Compact Edition expose performance counters to tools like Perfmon as SQL Server does? For instance, can you view lock wait times, cache hit ratio, etc.?
View 1 Replies View RelatedHi,
I'm using Visual Web Developer Express and Management Studio Express, and my web site is on a shared web host´, running SqlServer2000. I'm looking for software that enables me to monitor the server, but is it possible? The only apps I've found (and downloaded and installed and unistalled) so far need administrative rights to the server so they won't work on a shared web host.
All help would be welcome!
Thanks in advance,
Pettrer
Hello to anyone who may read this.
I want to know is it possible to monitor the Replication Agent (if thats the right bit to monitor) using T-SQL.
I want to do this because I am writing a VB front end to an SQL 2000 database and it uses merge replication to another SQL 2000 server.
The replication works perfectly BUT the only way at the moment to find out if it is working correctly and hasnt failed is to either log on to the other database or use Enterprise manager to check the replication status.
I want a visual indication in my VB program to all users that the replication has either failed or running normally. That way should the link fail they are told immediately.
The only reason for me choosing this approach is the people using the system are not computer minded. If it doesnt scream FAULT!!!!! they just carry on and ignore it.
Hope this makes sense to anyone reading this.
Thank
Neil
I am using below code to get patient status as an out put to my form. not sure whats happening but each time I run this its not executing my last "IF" if set to "N" show me N if not show me "Y" but it is by passing my first "IF" condition and jumps to last?
The column alerts_ind shows only Y or N in the table patient_status.
The table patient_status_mstr show the description of the patient which "discharged". All I want to do is if the patient is flagged with "discharge" the columns "alerts_ind" shows "Y". but something wrong? below is the code.
Alter PROCEDURE GBCheckPatientStatus (@enc_id varchar(36), @data_ind Char(1) OUTPUT)
as
begin
declare
@alerts_ind char(1);
select @alerts_ind =pm.alerts_ind
[Code] ....
Hello,
I just wanted how we can find the system performance without using tool like performance monitor or profiler.
I just need the query like equalent to peformance monitor for see the systmem status of CPU ,IO ,Memory and etc..
Thanks,
Ravi
I need to know how can i see the replication status. I have 4 servers with ms sql 200 standard server and hundreds of commnad to replicate. Sometime is important to know how mush of this commnads is replicated.
Thank You.
In SQL 2005 when i expand the local publication , Under replication when i try to view Synchronization Status i see the below message.
The initial snapshot for publication '%Database' is not yet available.
can any one help me on this issue.
Thanks
Hello,
I currently have a transactional replication between SQL Server 2000 and an Oracle database. Once a week on Monday mornings we have a process that drops replication, uploads data to the SQL Server database from Oracle, does some manipulation of the data and then recreates replication.
We came across the thought of what would happen if replication had erred out and there were still transactions within the distribution database to be replicated across. Then our job just came about and dropped the replication.
We are looking for a way to tell what the status is of replication through Transact-SQL. This way we can check the status and if it contained an error that we could abort the job, so as to not lose any transactions.
Does anyone have an idea of how to accomplish this?
Thanks
crusso
Hi,
what are possiblities of tracing/ loginng execution steps inside of procedure WITHOUT modifiing code.
Example
Develper created procedure that run for 3 hrs , getting data
from different sources using openquery(db2 , sql servers, xml files), inside procedure 25-30 different statements
If we want to use profiler, what steps and filters to use in order to capture this procedure AND all steps inside procedure ?
Does anyone know how to check when a distribution job has finished
distributing a snapshot programatically?
I am writing an app that adds subscribers programatically. It first add the subscription (which creates a new snapshot) and after the snapshot is done, the distributor gets invoked automatically. Because I need to access the subscriber database after the publication is pushed I need to wait until
the distributor has finished its work.
Any help would be greatly apreciated! Thanks.
Kurt
Hello,
I'd like to be able to poll a SQL 2000 subscriber database to obtain the current replication status. For example, I'd like to know when the subscriber is sitting idle (e.g. "waiting for changes..."), when it is actively replicating (e.g. "applying script..."), or when it is in an error state (e.g. "The Merge Agent failed...").
I realize I can query the table MSmerge_history which contain a comments and error_id field that may be useful. However, there is no status field that I could easily code against.
Does anyone have any ideas?
Hi.
I have setup an SQL Server 2005 Merge Replication.
Now I need to display the status of the replication programatically from the subscriber side, I have checked the documentation which mentions MergeSubscription & MergeSubscriberMonitor but I couldn't know how to use them!!
Any Help ?
Thanks
To monitor the status of replication in SQL 2000 we insert the resuts of sp_MShelp_replication_status into a temp table and review the status. In SQL 2005 this stored procedure has changed and now does it's own insert into execute command. Since insert execute commands can not be nested we must find another way to programically monitor replication using TSQL. We could call sys.sp_replmonitorhelppublisherhelper but we can't seem to find it in the distribution database. It must be hidden somehow. Any ideas?
Thanks,
Danny
If I'm on a remote machine, meaning a computer not in the WSFC cluster, and I open SSMS 2014, point it to a SQL Instance, and open activity monitor:
1. I get all the panes and charts except % Processor Time.
2. Then, if I authenticate to the cluster's domain by mapping a drive with valid domain credentials, I'm free to put performance counters in the Perfmon - - - but SQL Activity Monitor shuts down with“The Activity Monitor is unable to execute queries against server SQL-V01INSTANCE1..Activity monitor for this instance will be placed into a paused state.Use the context menu in the overview pane to resume the activity monitor.
Additional information: Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED))(Mscorlib)”
3. Of course, the Activity monitor can't be resumed via the context menu. Removing counters and closing the perfmon do not work. I dropped the mapped drive and rebooted the machine. That brought back 95% of the information in the Activity monitor.
4. Further experimentation showed that any mapping of drive shares present on the SQL Server to the computer running SSMS cut off functionality of the 'overview' pane in the remote machine's SQL Activity monitor -- the monitor that had been trying to watch the server offering the shares.