DMV's For Capturing CPU Utilization
Jun 3, 2008Hi
Please let me know which DMV is best to cpature total system CPU utilization. There are plenty of views so i am a lil bit confused
thanks in advance,
ADVERTISEMENT
Using MAX, Then Capturing All Except Max
Sep 26, 2005I need to first capture the max version associated with a file name here is the meat
SELECT
fileName,
f.folderName,
masterID,
iterationId,
iterationNumber version
FROM
fileIteration fi
JOIN iteration i on (i.iterationID = fi.fileIterationID)
JOIN vw_folderFileLink fl on (fl.fileMasterID = i.masterID)
JOIN folder f on (f.folderID = fl.folderID)
WHERE
fileName LIKE '429020652.idw'
--GROUP BY masterID, fi.FileName, f.FolderName, fileName, i.iterationID
ORDER BY masterID, version DESC
--*******end code ********--
that returns these results:
429020652.idwSubPumps71575217
429020652.idwSubPumps71575116
429020652.idwSubPumps71574975
429020652.idwSubPumps71574654
429020652.idwSubPumps71573983
429020652.idwSubPumps71573142
429020652.idwSubPumps71573131
First I need help just getting that top row but then I need to get all expect the top row selected.. I've tryed using MAX in several ways but I'm not getting what i want here :/ any help is appreciated
Capturing A Variable In ASP.NET
Aug 12, 2004Hi all,
I am new to .NET, after many years with classic ASP I am struggling a little with something that I am sure is really easy to do.
Basically what I want to do, is execute an SQL statement, that will return a single value. I then need to store this value as a variable, so that I can pass it into another query later.
It seems easy to output the record, but how do I store it as a variable !!
This is my code to connect and run the SQL... all I need to do as retreive the value, and put it aganist a variable....
Function higher_manager_ein() As System.Data.IDataReader
Dim connectionString As String = "server='myserver'; user id='userid'; password='pwd'; database='DB'"
Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString)
Dim queryString As String = "SELECT distinct MEASURE FROM [CCC_MEASURE] where ein = '" & request("man_ein2") & "'"
Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand
dbCommand.CommandText = queryString
dbCommand.Connection = dbConnection
dbConnection.Open
Dim dataReader As System.Data.IDataReader = dbCommand.ExecuteReader(System.Data.CommandBehavior.CloseConnection)
Return dataReader
End Function
Capturing Queries
May 22, 2000Is there any way(other than Profiler) to capture a full SQL Query statment. I've used dbcc inputbuffer and the current activity screen, both return 255 characters. I'd like to capture the whole query. Thanks
Pete Karhatsu
Capturing Server Name?
Sep 20, 2004Hi y'all....long time, I know...
I have what OUGHT to be a simple question...or so I think (which really hurts, mind you...)
I am writing a stored proc that will reside on several different databases and be used to write a row to a "wait table" that is used to control processing in the various databases...
Essentially, the stored proc already exists, and writes what is essentially a note (or process semaphore) that says "Hey, Process XYZ is waiting on the completion of process 123"
Problem is...process XYZ has the same name on a number of different servers, so I have to come up with a way to differentiate the process name that's waiting on a job on a single server (in other words, 5 or more XYZ's can be waiting on a single job on a single server in the network, and the wait table resides on that single server).
So...my thought (again, think pain) is that I will put a process name of "SERVER.XYZ" into the wait table.
The SP I will use to write the "waiting on" semaphore is a common one, so - long story short(er) I need a way to capture the name of the current server (like db_name(), only server_name() - or something like it).
Any suggestions? Thanks in advance...
Paul
CPU Utilization
Jul 16, 2001Hi All,
We have two production boxes running different ERP Apps on each boxes, We just added 2 more cpu's to one box and 1 cpu to other. The Currect CPUS config as stands is
Box 1 = 4 CPU's
Box 2 = 2 CPU's
My boss whats some stats to see if performace was any better and if yes how much !!
How can i find such information from my box ?
Can anyonw help with this stat's ? !!
Thanks in advance
Girish
CPU Utilization
May 2, 2005Hi all,
I have a problem in trying to find out why only one CPU in a 2 CPU H-T utilized. Using task manager, I can see 4 processors windows but only 1 actually utilized. I select +boost sql server and select all CPU for use. Queries and all other command ie dbcheck.. appear to use only single cpu.
Any help would be nice.
thanks
Andrew
CPU Utilization
Feb 12, 2004Hi All,
I want to keep track of the CPU utilization & number of users connected for each database on our production box. I chose to get the data from sysprocesses table from master database.
But I realised that for some reason the master..sysprocesses.CPU column stays static or just keeps on adding to existing values.
Is there any ways thru which I can clear this data ( cpu column in sysprocesses table) after I have captured it in a table ?
Any help is appreciated.
Thanks.
SQL CPU Utilization Goes To 0
Jul 20, 2005We have a SQL 7.0 Standard Server running on a Windows 2000 Servermachine with 2 800mhz Pentium III with 2GB memory. Our front end isAccess 97 and 2000 with most ADO connections for the scripts but someDAO for forms and reports. We recently "released" a new version ofthe "database" that caused a catastrophic event to start happeningwith our SQL server.Using PerfMon we monitored the CPU utilization on the server andnoticed that the CPU load would drop to 0 for approx 5-10 seconds andthen jump back up to our average 60-70% utilization. During thisdrop, there is NO disk activity no new connections being made, etc.We then took the process a step further and loaded a "stress" programthat put about 30% load on the server to start with. Then wemonitored each processes load. SQL Server process would drop to 0%while the stress process continued at 30%.The problem is that the SQL does absolutely NOTHING for 5-10 seconds.You cannot connect, any querys that are running stop, their is no diskactivity (logs, data drives), and you cannot even get sp_who2 to runfrom Query Analyser. We thought maybe blocking (we have built an"app" that monitors this), but we don't see any blocking before itlocks and nothing after it locks.Out of despiration we "rolled back" to our previous version to getpeople working again. After business hours, we have tried toduplicate the problem on machines (2 or 3 at a time) but cannot get itto duplicate the problem.The only experience we had previous to this was using DNS to resolvethe server name which caused a problem EXTREMELY familar to thisproblem. However, we have double checked every machine we have, andnone of them are using DNS to resolve.Any idea's would be most appreciated.Patrick Moore
View 2 Replies View RelatedCapturing ID When Executing An INSERT
Jan 2, 2008I have a bit of code that executes an INSERT statement to add a record to an existing table. This table of course has an automatically incrementing ID field, and I'd like to somehow have my INSERT statement return the value of that ID field so that I can automatically show the user the record they've added.
Is there a clean way to do this?
Capturing Value Of Identity Column For Use Later?
Jul 20, 2005This doesn't work because the first INSERT is creating multiplerecords for multiple projects. @@IDENTITY, then, contains the Identitycolumn value for the last tblWeekReportedLine record inserted.Consequently, all the hours records are then associated withthat last value.The source work table, #EstimateLines, is a pivoted representationwith a Begin/End date and some Hours for each of six periods - a lineper project that gets pushed up to the DB by some VB code.Definition below the sample coding.The "@WeekReportedID" value was successfully captured whenprevious coding inserted six records into that table: one foreach date range (i.e. column in the UI screen)Sounds like I'm approaching this wrong.Suggestions on the right way to go about it?---------------------INSERT INTO tblWeekReportedLine(WeekReportedID,RelativeLineNumber,ProjectID)SELECT@WeekReportedID1,#EstimateLines.RelativeLineNumber,#EstimateLines.ProjectIDFROM#EstimateLines;SET@CurWeekReportedLineID = @@IDENTITY;INSERT INTO tblHour(WeekReportedID,WeekReportedLineID,HoursDate,Hours,HoursTypeID,HoursType,TaxCodeID,TaxCode)SELECT@WeekReportedID1,@CurWeekReportedLineID,@BeginDate1,Estimate1,@DummyHoursTypeID,@DummyHoursType,@DummyTaxCodeID,@DummyTaxCodeFROM#EstimateLines;------------------------The #Temp table create via VB:------------------------1030 .CommandText = "CREATE TABLE #EstimateLines " & _" ( " & _" PersonID int, " & _" ProjectID int, " & _" RelativeLineNumber int, " & _" Available1 decimal(5,2) Default 0, Estimate1decimal(5,2) Default 0, BeginDate1 DateTime, EndDate1 DateTime, " & _" Available2 decimal(5,2) Default 0, Estimate2decimal(5,2) Default 0, BeginDate2 DateTime, EndDate2 DateTime, " & _" Available3 decimal(5,2) Default 0, Estimate3decimal(5,2) Default 0, BeginDate3 DateTime, EndDate3 DateTime, " & _" Available4 decimal(5,2) Default 0, Estimate4decimal(5,2) Default 0, BeginDate4 DateTime, EndDate4 DateTime, " & _" Available5 decimal(5,2) Default 0, Estimate5decimal(5,2) Default 0, BeginDate5 DateTime, EndDate5 DateTime, " & _" Available6 decimal(5,2) Default 0, Estimate6decimal(5,2) Default 0, BeginDate6 DateTime, EndDate6 DateTime, " & _" );"--------------------------PeteCresswell
View 2 Replies View RelatedCapturing Events In A DataGrid
Jul 20, 2005I'm trying to add functionality to a VB 6 application allowingcustomer service to add a customer number to a new customer.Customers are added to the database by sales personnel, and aprospective customer may have multiple rows due to projected ordersfor multiple products. Customer numbers are assigned when a newcustomer makes their first order.I'm using a DataGrid connected to an ADO Data Control. The datacontrol is connected to a view in SQL Server using the 'distinct'directive (I know it's not updatable) to show only one line per newcustomer. What I wish to do is capture the update event (probablythrough the FieldChangeComplete routine of the data control), manuallyassign the newly entered customer number to all appropriate rows inthe database, and cancel the update event with no notifications.I'm having two problems:1. I can't capture the newly entered customer number. The Textproperty of the DataGrid returns the old value of the cell instead ofthe newly entered value. How do I get the edited value?2. Even though I set adStatus = adStatusCancel in theFieldChangeComplete routine, I get a Microsoft DataGrid Control dialogstating 'Operation was Canceled'. How do I avoid this notification?
View 1 Replies View RelatedCapturing Execution Results
Nov 13, 2006Hi All
When running an SSIS Package from BIDS, we get to see the "Progress" tab which explains us the progress of the SSIS Package. During production, is there any way to capture this log. I am interested in using this as a log file for each run of my SSIS package.
Thanks,
S Suresh
Capturing Component Throughput
Dec 19, 2007[Microsoft follow-up]
I've just been reading this thread by a guy asking about capturing the throughput of a dataflow. I suggested that there is no real notion of capturing throughput of a dataflow but I believe there IS a notion of capturing the throughput of a component or an execution tree.
I believe all the information that one would need to capture the throughput of a component (apart from the name of the component that is) is available in the OnPipelineRowsSent event. If there were a OnPipelineRowsSent eventhandler and the OnPipelineRowsSent event contained the name of the component then I think we would be able to capture the throughput of a component. So, some questions:
Why is there no eventhandler for the OnPipelineRowsSent event?
Can the name of the component be added to the information in the OnPipelineRowsSent event?
Following on from this... I once had a conversation here with Kirk Haselden about capturing pipeline throughput. He thought it was a good idea and suggested I raised a DCR for it which I did but that was in the old pre-Connect days and it seems as though that DCR (like SO many other things) didn't make it across to Connect. So, some more questions:
Can you find any Connect DCRs relating to capturing throughput? I've found this: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=152162 that I raised 18 months ago but which hasn't even had a single comment from anyone at Microsoft.
Do you think that capturing throughput would be useful? I can foresee huge advantages by capturing this in the debugger. (Note that Informatica does this and has done for years. It has a very nice GUI that shows the throughput of each destination in the mapplet.)
I'd welcome any thoughts around this. Its a big ask and it fits in very nicely with my constant, nay INCESSENT, requests for debugging enhancements so maybe this is one for Darvey to have a read of???
Thanks
Jamie
Capturing One Record In A Series
Nov 30, 2007
My question is this.....
I inherited an application that creates reports and each report pulls data from a SQL database. The reports are for each School district in the state and pull the number of students that have Asthma conditions, simple enough. However what happens is this... Each initial report is saved with a code of 2 for initial. As long as there aren't and revisions I am fine. If a school makes a revision (Code of 4) I now have two records in the database and I need the one from the revision (Code 4). Not every record will have a revision, and sometimes there may be multiple revisions. How can I code my stored procedure to go through the database and check to see if there is a code of 4 or multiple codes of 4 and if so grab that data, if not grab the initial data? Below is the code currently being used. Frank
CREATE PROCEDURE dbo.usp_Rpt_Asthma
(@WhereClause VARCHAR(400))
AS
SET NOCOUNT ON
DECLARE @WhereClause_STR VARCHAR(400)
SELECT @WhereClause_STR = CONVERT(VARCHAR(400),@WhereClause)
Exec ('Select D.Code, C.Code, InstCtgyFK, C.[Description] as County, D.DistrictName, A.InstName,
Sum(K4+K+G1+G2+G3+G4+G5+G6+G7+G8+G9+G10+G11+G12+UnGrSpEd+Other) as TotalADM, Asthma, Asthmatics
FROM dbo.tblAnnualReports AR
Inner Join dbo.tblAddresses A on A.InstitutionFK = AR.InstitutionFK
Inner Join [DHHBGSQLPROD1].[Shared Common Data].[dbo].[tblInstitution] I on A.InstitutionFK = InstitutionPK
Inner Join [DHHBGSQLPROD1].[Shared Common Data].[dbo].[tblActiveInstCtgy] AIC on A.InstitutionFK = AIC.InstitutionFK
Inner Join [DHHBGSQLPROD1].[Shared Common Data].[dbo].[tblCounties (PA Standard)] C on C.Code = I.DOHCountyCode
Inner Join [DHHBGSQLPROD1].[Shared Common Data].[dbo].[tblCommunityHealthDistricts] D on D.Code = C.CommunityHealthDistrictCode
Inner Join dbo.tblAverageDailyMemberships ADM on AR.[ID] = ADM.AnnualReportID
Inner Join dbo.tblChronicConditionsInjuries CC on AR.[ID] = CC.AnnualReportID
Inner Join dbo.tblMedicationAdministration MA on AR.[ID] = MA.AnnualReportID
Where (A.StartDate <= AR.DOHDateProcessed and (A.EndDate >= AR.DOHDateProcessed or A.EndDate is Null or A.EndDate = ''1/1/1900''))
and ReportTypeCode = 2
and IndOrdStandOrd = ''I''
'
+ @WhereClause_STR +
'
Group By D.Code, C.Code, InstCtgyFK, C.[Description], D.DistrictName, A.InstName,
Asthma, Asthmatics
')
GO
Index Utilization
Mar 29, 2001SQL 7 SP2
Hi.
True or false on these:
1. A table has a PK of EmployeeID (non-clustered). The sql statement where clause uses something like "WHERE E.Action > 1 AND E.User = 1001 AND E.EmployeeID = 12345
Question: Will the PK index be used in determining the result set ?
2. A table has an index of EmployeeID + Company + State (clustered). The sql statement where clause is "Where EmployeeID = 1001".
Question: Will the index be used in determining the result set ?
Thanks,
Craig
Memory Utilization
May 10, 2000I installed 4GB of memory and I have never seen SQL memory utilization go beyond 2GB. I have SQL server set up to use as much memory as it needs. Does anyone no if SQL server can make use of more than 2GB.
View 1 Replies View RelatedMemory Utilization On SQL 7.0
Feb 28, 2000Ours is a SQL 7.0 Enterprise edition with NT 4.0 Enterprise Edition. SQL Server has been configured with the default, 'Dynamic memory Allocation'. The system has 4GB of RAM. This is a dedicated SQL Server machine. But SQL Server seems to use only 1.8GB RAM(Counter: Total Server Memory) The page faults seem to be a max. of 600 and an avg. of 100. The processor utilization has suddenly increased to 90%. Is there anything wrong with the way SQL server is using memory? Is is not true that SQL Server 7.0 Enterprise edition can use upto 3GB RAM in a 4GB system?
Are there any links that can help troubleshoot this problem?
Thank you.
-Praveena
High Cpu Utilization
Mar 21, 2000After a fresh install of SQL 6.5 with SP5a(or without), the cpu is running at anywhere from 50%-80%. It is loaded on a PDC, but when I stop the sql service cpu utilization drops to 0-2%. When I start the sql service it's right back up there, does anyone have a suggestion as to how to fix this or why the service would be doing this?
Thanks, Christel
High CPU Utilization !!!! Help !!!!!!!
Sep 5, 2002I am getting high CPU utilization on the SQL Server process (>90%).
However the overall utilization (NT -- entire box) always seems to be under 50%.
Can someone explain why this is happening. The server is a quad; the SQL server process seems to be using only two CPUs at a time (not the same ones all the time).
Lightweitht pooling has been turned on and the maximum worker thread size has been left at the default value (255).
How can I configure SQL options to spread the load across all four CPUs ??????
Problem With CPU Utilization
Sep 6, 2006I have windows 2003 server with SQL Server installed on it for live calls billing but the CPU utilization is reaching the maximam and it's average above 60% which is causing lot of problems specially for the live environment. I have enough memory and free hard disk space is more than 40GB,
so where is the problem?!
Memory Utilization
Jul 20, 2005What do people think is normal for memory utilization? I know that's toobroad, so here are some basics.MS SQL Server 2000, Windows 2000 Server, 2GB RAMDb 1, size = 2.0 GBDb 2, size = 300MBDb 3, size = 50MBDb 4, size = 30MBDb 5, size = 30MBTypically 4-6 users, moderate usage 8-hrs/day. Performance has not slowed.Reboot on Sunday. sqlservr.exe in the Task Manager reports the followingSun 61MBMon 200MBTues 800MBWed 1,124MBThu 1,424MBFri 1,303MBI was getting srv 2020 errors when I had just 1 GB RAM: "The server wasunable to allocate from the system paged pool because the pool was empty."Then I did several updates to address this and got more RAM. I haven't seenthe errors since, but I haven't waited for them to happen: I'm rebootingevery week now. The memory numbers make me suspect SQL Server.Scratching my head. Not sure if my problem is gone, and this is normal SQLServer 2000 behavior, or if my problem is still lurking and I've only mutedit a bit.Any thoughts greatly appreciated.Tom
View 3 Replies View RelatedProblem In CPU Utilization
Sep 6, 2006I have windows 2003 server with SQL Server installed on it for live calls billing but the CPU utilization is reaching the maximam and it's average above 60% which is causing lot of problems specially for the live environment. I have enough memory and free hard disk space is more than 40GB,
so where is the problem?!
Memory Utilization
Apr 19, 2007Dear all,
One of the server is having 2 GB of RAM and task manager is showing 1.87 GB memory in use.
I have to migrate few databases on the same server.
With high IO Operations.
I know server require more RAM, but how can i prove that server needs more RAM ?
Regards
Mohd Sufian
High CPU Utilization
Oct 17, 2006Could anyone help me in finding out why the cpu utilization is very high??
I have two servers say, Server A , server B. There is a transactional replication going on from server A to B
There is a table say Table A on server A, which is being replicated to server B.
I created a trigger insert and update trigger on Table A on server B (i.e. on subscriber). Since then, the CPU utilization for server B is very high 80-90%
when i used profiler, i could see .whenever replication stored proc for insert or update executes..cpu utilization goes up..
trigger just insert the updated/inserted rows into some other table.
Could anyone tell me why the cpu utilization has gone up so much?? i am using sql server 2005
thanx
Proc For Capturing Date And Time
Aug 31, 2007I'm creating a sproc to to count the dates and times users log on to a page. I want the date and time broken out into two columns.
When I attempt create the sproc I get the following error:
Subqueries are not allowed in this context. Only scalar expressions are allowed.Create PROCEDURE [dbo].[LoginCounter]
@Username Nvarchar(50),
@IPAddress Nvarchar(50),
@BuilderID, INT
As
INSERT INTO LoginCount ([UserName], [IPAddress], [Date], [Time], [BuilderID])
VALUES (@Username, @IPAddress,
(SELECT CONVERT(VarChar(2), MONTH(GETDATE()))+ '/'+ CONVERT(VarChar(2), DAY(GETDATE()))+ '/'+ CONVERT(VarChar(4), YEAR(GETDATE()))),
(SELECT Convert (varchar (2), DATEPART(hour, GETDATE())+ 3) +':'+ CONVERT(VarChar(2), DATEPART(minute, GETDATE()))),
@BuilderID)
Capturing SQL Queries Of 3rd Party Application
Sep 15, 2003Hello.
I would like to analyze how a particular 3rd party business application interacts with SQL server.
Specifically, I want to capture the "exact" SQL commands (transact-sql statements) that this application issues whenever it completes an operation for the user of this application.
In other words, suppose the application issues the SQL command "INSERT INTO table (a,b,c) values (1,2,3)" to add a new entry to the database. Then, my understanding is that if I instead issue the exact same command with a tool like "SQL query analyzer", the database will be updated in the exact same manner. Hence, I have effectively accomplished the same job that the 3rd party application does without even using it (by idependantly issuing the same command to the database it uses).
Is this possible? Can I obtain all the information I need just by running SQL Profiler?
Capturing Bulk Insert Error
May 26, 2004Hi,
Can someone help me out with capturing the bulk insert error.I have a job which calls a procedure in which I used the bulk insert command .If the bulk insert is failing due to some reason as wrong delimitor,wrong path etc then the job fails.I need to track that error and see that the job doesnt stop and goes onto the next cursor record.
Thanks,
Nodbek
Capturing Client Logins And Hits
Apr 1, 2002I'd like to capture the avg. # of user logins and # db hits per a 5 interval for a weeks time. I'm guessing there are sys tables containing this info. and by using temporary tables and/or creating/modifying SPROCS this info. can be retrieved. If I'm on the right track, a little direction would be very appreciated. If I'm not on track, please assist this rookie dba.
Thank you,
Eoin
Capturing Values From Executing A String
Jun 14, 1999I currently have a need to dynamically build an sql statement that always returns a single value when executed. The sql statement is always the same except the database name reference in the statment.
What I need is to be able to capture that value for later use in the procedure. Since this is a stored procedure, I can't use the "USE" statment to switch databases and I haven't been able to figure this out using the Execute statement. I can execute the string, but I can't capture the value.
I'm simply trying to execute the same set of sql statements in a stored procedure without hardcoding database names or build an identical stored procedure in all our databases.
Any help is appreciated.
Capturing A User Table Name Into A Variable
Nov 25, 1998Can I declare variable and assign a user defined table name in Sql server 6.5. if so how can I do that ,
Thanks
Ali
Capturing WARNINGS To Write To Error Log?
Jul 23, 2004Hey y'all...
Anyone know how to capture SQL Warnings so I can write them to an error log? I can't seem to find any info on it in Books Online...
I can capture the errors just fine by using @@ERROR after a select, but what about warnings such as "Warning: Null value is eliminated by an aggregate or other SET operation."
Thanks!
Capturing Data Type Mismatch
Oct 27, 2005Hi,
Create Table tb_mismatch
(x int)
Create Procedure proc_mismatch
as
begin
insert into tb_mismatch values('s')
if @@error<>0
begin
print ' entered error loop'
end
print 'successfully exited'
end
exec proc_mismatch --executing the proc
Now, when i try to capture the above error its not getting trapped..its directly going to the final end statement.
I have even tried calling subprocedures so that it comes out of the inner procedure and by some means i can move forward in the outer proc,but even that failed.
The proc. is able to capture all the other errors like primary key violation,binary data truncated etc but not the datatype mismatch error (mainly int with varchar...)
any ideas are highly appreciated.
Thanks & regards,
Pavan.