Hello everybody,
I have a very stranger problem that I need to understand...
In last days I executed a plan SQL 2005 Profiler to review TSQL Duration. When reviewing the results encounter that a SP displays a value of 4037312 in field DURATION which are not normal.
Could to help me to identify why passes this?
If monitoring for duration with sql profiler, what does the number represent ie 2733906 is it milliseconds, thousandths, looked in BOL no clear definition
View 2 Replies View RelatedCan someone please tell me what unit of measurement the Duration column is in when running SQL Profiler? I assume milleseconds, but am not sure.
I'm trying to filter on this and want to be sure I'm not missing anything.
Thanks!
Sarfaraz writes "I have captured SQL Profiler data. I was reviewing top running CPU intensive SQL statements. The Duration (in seconds) 1.39, 1.09, 0.16 and CPU (in seconds) 0.97, 0.95, 0.16 respectively for some SQL statements. How do I know what is the normal baseline for duration and CPU in order to determine the CPU intensive SQL statements.
Secondly same question for long running procedure duration 0.14, 0.11. What is the normal baseline here. Is this normal or too long.
Thanks,
Sarfaraz"
Hi Experts,
We have a VLDB ( few table with above 200 million records ). This database is used for performance testing by simulating for 150 users and executing all necessary functional flows.
When I examined the profiler results , I could see some very high values as shown below in the duration column for many events.
1521729
3462142
1624325
3211255
1248276
3903998
Does it mean that that SP or the T-Sql statement is taking this much time in milliseconds to execute and give the output ?
Any help would be greatly appreciated.
Thanks & Regards,
DBLearner
Hi
I have a procedure in a history database that does insert into 3 tables inside a transaction. users complaint that the proc sometimes takes too long during heavy usage. I did some traces to see what is taking up the time, I found that the rpc duration was averaging > 500 ms (should only take 50ms). I checked to see if one of that statements were taking too much time, but only see the commit transaction statement taking around 500 ms). I check the avg disk queue to be around 30. ( this is on a single local disk) .
So is this definitely a disk issue, or is there something else I need to check
thanks
P
how i can setup an e-mail alert for a specific agent job, such that it sends an e-mail report when the run duration exceeds 30 minutes.
The agent job in question kicks off our ETL, and it runs every hour. It's happened before where a job running elsewhere(Not best practice, but we run reports from our datawarehouse) creates a deadlock on 1 of the tables being updated in the ETL, but i only get notified when i get calls from end users saying their reports aren't returning results.
I was thinking of creating a new job that query either the sysjobhistory or sysjobactivty table in msdb, but i would need it to refer to the run duration value of the job as it's still running( I think i can assume it's not updating that value every second...)
I ran Sql profiler and got the following results for a stored procedure
CPU of 1078;
Reads of 125464
writes of 0
Duration of 1882
how do i interpret the above results.
also what CPU and Duration is considered high and indicating a poor performing query.
I am using SQL Server 2005. Thanks
I've set the Duration of my trace to "Greater than or Equal to: 1000". However when I start my trace the Duration column is now empty. Prior to the setting, there were values showing in this column. Any ideas on how to fix this?
View 1 Replies View RelatedProfiler was run against a database looking for "long running" queries. I used the Duration column to filter out the queries that I didn't want. When reviewing the output, I noticed that for some queries the StartTime was equal to the EndTime even though the Duration was set higher.
My question is, "What can account for this discrepancy and what inferences should I draw about the difference?"
Does the difference represent a resouce being locked or some other type of blocking (Duration) and once the query was allowed to run, it completed quickly?
TIA
=======================================
If Tyranny and Oppression come to this land, it will be in the guise of fighting a foreign enemy. -James Madison, fourth US president (1751-1836)
I have the following SQL query
SELECT
[Req_ID]
,[Service_Name]
,[Req_Started_Date]
,[Task_Name]
,[Task_Status]
,[Performer_Full_Name]
[code]....
Which works fine, but what I need to calculate the total duration of a request based on the duration of the tasks completed in the request based on Req_ID. I would like to use the CASE statement I have to determine the SLA_Mins for each task and add them together to get total request SLA_Mins.
Below is the create table schema and data
GO
/****** Object: Table [dbo].[MidrangeOtherSourceControl] Script Date: 06/03/2015 18:13:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[MidrangeOtherSourceControl](
[Req_ID] [float] NULL,
[Service_Name] [nvarchar](255) NULL,
[code]....
Hi all;
I want to implement "Auto disconnection" (or kill) of user session that exceed a specified idle duration in SQL Server 2005.
I know how do that in oracle by creating a profile and set the IDLE_TIME paramter and asign that profile to all users. But in SQL Server i don't know how to do it.
Can anyone help me.
Kindly
Hi,
Is there a way to configure mirroring to go from High Availability to High Protection without having to reconfigure Database Mirroring? Using the interface in Management Studio, I can change the configuration option to High Performance, but not High Protection despite both of them being Synchronous.
If not, what are the recommended steps to configure the mirror once it already has been configured? Is just like initially setting up the mirror or would there be any shortcuts I could take? If I stop the mirroring and remove the witness, will the High Protection option be available?
Thanks,
J.
Hi There
I realise this is a stupid quesiton but i cannot really find any confirmation of this in BOL.
If you are running High Safety with automatic failover, when failover occurs does this automatically change to High Performance mode. SInce for failover to occur something has happen with the primary , it will be impossible to commit transactions on the new primary and mirror asyncronously since 1 of them is no longer available.
So am i correct in assuming that automatic failover also automatically changes the mode to High Performacne for that session?
Thanx
We have a live server that has had very high CPU usage in the last few days, therefore the site is extremely slow. There are about 60,000 users per day on the average. It always had high CPU usage, but not as bad as in the last few days, in the 90s, sometimes even reaching a 100.
Any solutions? I've run SQl Profiler to check on queries that have high CPU usage. When I run the same queries on our staging server, they are very quick. For example, the same query can take 1 minute on stage, but 12 minutes on the live site. We do know that this is also related to traffic, during lower traffic times, the same query takes less time, but still never as fast as stage.
Oh yeah, it's SQL Server 2005 and asp code running IIS6 on windows 2003 server.
Please help. I really appreciate any advice. Thank you.
where in the sql 2005 profiler i set the DB that i want to trace?
i know where it is in sql 2000 but cant find it in sql 2005
thnaks
peleg
Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)
Hi
I would like to know, Is Logshipping is supported on SQL 2005 clustering?
Thanks in Advanceshan
Our company recently combined our DBs into one SQL 2005 Server.
Dell Power Edge 1800 with 3.00 GHz Xeon Processor 800 FSB, 1 GB of RAM
Dell Power Edge 1600 with 2.80 GHz Xeon Processor 533 FSB, 1 GB of RAM
Combined into one:
Dell Power Edge 2950 Dual Core 1.6 GHz Xeon Woodcrest Processor, 4 GB of RAM
However, the CPU utilization on this new server is maintaining at about 90% with 3.82 GB of RAM used as well. It's a Windows Server 2003 R2 x64 edition running SQL Server 2005 SP2 x64. I have searched around Microsoft's website for any information that could be of help to me, but I was unable to locate anything. I was hoping that someone could provide some insight as to why this might be occuring. Or if this is a known issue.
Thanks,
Peter
Hi,
We are having a big performance issue at our site. Here is the configuration of the box running SQL Server 2005:
64 bit Windows Enterprise Edition + SP1
Dual CPU, 16GB RAM
RAID 1 and RAID 5 - internal
SQL Server 2005 64-bit Enterprise Edition
With SP2 (CTP from December)
The "Lock Pages in Memory" is set and is being run under the same account that is being used to run SQL Server Services.
We are noticing that under load, the CPU utilization becomes nearly
100%. I have researched this and have come across a couple of
posts that indicate that this issue was fixed in SP2 - example: One
post talked about the hotfix #716 which is also a part of SP2 but even
after the application of that service pack, we are still having this
issue. I haven't tried setting the parameterization option to
forced for the database yet.
Is this a known issue with SP2? If not, what can we look for and
fix in our environment? Please let me know if I can provide more
information.
Is there any way to use sql profiler with sql 2005 express or is there a similar tool that can be used with it? I only have enterprise manager with sql 2000.
View 1 Replies View RelatedDear friends,
I got script for creation trace file on server drive.
and it's work fine,when we migrate to sql server 2005 i take the same script for this trace and he create it successfully but the problem when i try to open this file for example today file:
SQL-Server1 SQL Trace20080417000000847.trc
I got the following message:
Sql server profiler :failed to open a file ,access denied.
old files created by sql server 2000 ,i can open it until now but the file created after migrate to sql 2005 ,i got that message above.
Guys/Girls
Have a problem am currently running a sql trace on a database, have the normal options setup.
Stored Procedures
RPC:Completed
RPC:Starting
TSQL
SQL:BatchCompleted
SQL:BatchStarting
But am also using the text data field but am trying to only bring back items that have a certain string like. Does anybody know how to use wild cards in the text data colounm
Lee
Hi everyone,I am steven.
I have some questions about the SQL Server Profiler 2005.
1,haoI am using TransactionScope of .Net Frameworks 2.0 for DB query and update.I want to know the lock level(table,page,row,etc) and status of lock status(u,s,x,etc) by profiler tool when I run the query and update statement.How can I do?
2,I had got some traces when I run the DB query and update.But I cann't understand the meaning of the trace.such as Binary Data,ObjectID,Type,etc.How can I know the meaning of these?
Whould anyone can help me?
I want profiler to run for 5 minutes whenever the system cpu exceeds/reaches 80%. Is this possible?
TIA
Hi
I am experiencing a problem while performing failover testing on 2005 Standard.
I have an application which uses an ODBC connection SQL Native Client with a failover partner.
The following is the series of events that cause the problem. I am using a domain admin account whilst testing which has a server login on both servers with the same privileges.
Server A (Principle)
Server B (Mirror)
**Application can connect**
Manual failover
Server A (Mirror)
Server B (Principle)
**Application can connect following a restart**
Server A is switched off
Server B (Principle, disconnected)
**Application continues working**
***restarting the application the following error occurs***
€œConnection Failed
SQLState 08001
SQL Server Error 53
[Microsoft][SQL Native Client]Named Pipes Provider: Could not open a connectionto SQL Server [53].
Connection failed:
SQLState: '08001'
SQL Server Error: 53
[Microsoft][SQL Native Client]An error has occured while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.
Connection failed:
SQLState: 'S1T00'
SQL Server Error: 0
[Microsoft][SQL Native Client]Login timeout expired€œ
Any help with this would be most appreciated
Peter
I notice this running the SQL Server Profiler under the application name Report Server. Every time it runs, it reports an error (in the Error field of Profiler). Does anyone know what this is?declare @p1 nvarchar(64)set @p1=NULLexec GetDBVersion @DBVersion=@p1 outputselect @p1
View 1 Replies View RelatedHow do you get sql2005 profiler to filter on host name, (computer name of the client) like you can in sql2000 profiler. 2005 Profiler column filter options seem to omit this, this makes the application virtually useless in most situations.
View 1 Replies View RelatedHow do you get sql2005 profiler to filter on host name, (computer name of the client) like you can in sql2000 profiler. 2005 Profiler column filter options seem to omit this, this makes the application virtually useless in most situations.
View 1 Replies View RelatedDear All,
I need a tool to see what transactions and in what order are being issued
against the SQL Server Express 2005 DB.
Because of the SQL Server tools don't come with SQL Server Express 2005,
then i run SQL Server 2000's Profiler with SQL Server Express 2005. But It
prompt error "Failed to start a new trace".
FYI, i don't want to use the backend sql trace .in command prompt..
Pls advise me on how to run the sql profiler to trace the transaction being executed in SQL Express 2005?
Thank you.
I SQL Server 2005 Profiler when ran the T- SQL template against some Stored Procedures, in the error column i get 2 - Abort. Can some one help me on this issue.
Thanks
I am in the process of designing a database infrasture layout that can virtually scale to an very large number of servers in efforts to improve performance. The Scale-out architecture vs. grid computing (something like Oracle RAC) seems to be the way to go. It may take a lot more work up front, but it seems very flexible in the long run.
One of the issues that I am trying to tackle is how should I grow this thing. Right now, I have one single 4 way server running SQL 2005 Ent. edt. We are planning on getting a second server as well as a Enterprise level San solution.
With my 2 goals in mind (Scale out architecture and High Avail) should I bring this second server online as a passive cluster node, or should I partition out the data across both nodes. Will clustering even be part of my fault tolerence plan or should I use replication?
Its hard to find a good answer as what is the *best* way to make this happen.
Any insight will be greatful...
Thanks!
Eric Elliston
http://www.rbdstudios.com
I have six stored procedures that I have to run hourly during the day. When I schedule them into separate SQL jobs (they must run concurrently), the percent CPU of Sqlagent90.exe process goes way up, eventually pegging out the overal CPU usage on the server. When I run the six stored procedures concurrently from query windows in SQL Server Management Sudio, the overall CPU usage stays very low.
Is there something about the SQL Server Agent that inherently adds CPU overheard? Thanks in advance ....
I have a question for anyone who mas some tips/pointers for optimizing SQL merge replication publications.
The front end web server is running IIS 6.0 on Windows 2003 x86 Server Standard (Server A). The back end database server is running SQL 2000 Standard on Windows 2003 x86 Standard (Server B). The merge replication clients connect via HTTPS over the Internet from a custom C#.NET 2005 application using SQL 2005 Mobile running on Windows Mobile 5.0 (Client).
The publication itself has several filters on it. The entry point uses the user's Windows username to start the filter. Based on the user, it then filters the records in multiple tables. There are 68 articles and 44 filter statements. The filters extend multiple layers deep, in other words they are not all filtering off the HOST_NAME() variable, some tables filter from records in tables that filter from the HOST_NAME() variable. The publication is set to minimize data sent to the clients, and considers a subscription out of date if it has not synced in the last 4 days. All the rowguids are indexed as well.
There are approximately 35 clients actively using the application at any given time. On average, a client will initiate a merge replication 3-4 times per hour from 8am-5pm. Generally, a sync will take between 10 seconds and 2 minutes to complete, with most of them being around 30 seconds on average.
When a client starts a sync, there is a spike to about 50% on the server's CPU graph. If multiple clients attempt to sync at the same time the CPU utilization can be pushed to 100% for extended periods (more than 30 seconds).
I recently completed a project to increase the bandwidth available to the clients, and plan to reduce the number of filters significantly (although this will obviously increase the amount of data going to the clients and the storage needs on the individual devices). I also plan on changing the setting to not minimize the amount of data sent to the clients.
Having said all that, does anyone have any information about how to further optimize merge publications to mobile clients? The next publication will be on SQL 2005 x64 Standard if I can solve the issues in the text environment. I would like to enhance the publication as much as possible to make the end user experience better than it currently is.
Thanks!