SQL 2012 :: How To Find CPU Usage From History

Jun 15, 2015

I am trying to find out CPU utilization from the history using process.%processor time. I am having dual core CPU with 2 numa nodes each having 16 logical cpus bind to it.

how to calculate the CPU utilization using perfmon.I tried to use SQL query which gives CPU history using SQL DMV, but I am unable to get the exact value. Because in between I have used the same querry to capture my CPU usage on the run day, the value on run day and the query which iam tryting to pull out is different. I am using the same query to pull the history data with providing the date.

-- Get CPU Utilization History (SQL Server 2008 and above)

DECLARE @ts BIGINT
SELECT @ts =(SELECT cpu_ticks/(cpu_ticks/ms_ticks)
FROM sys.dm_os_sys_info);
SELECT SQLProcessUtilization AS [SQLServer_Process_CPU_Utilization],
SystemIdle AS [System_Idle_Process],
100 - SystemIdle - SQLProcessUtilization AS [Other_Process_CPU_Utilization],

[code]....

View 1 Replies


ADVERTISEMENT

SQL 2012 :: Find Buffer Cache Usage By DB Objects Within Particular Database

Jun 22, 2015

I am using SQL 2012 and I am trying to find buffer cache usage by DB objects within a particular DB.

I am running the following query

select
name as DB,
objname as db_object_name,
COUNT(name) as cache_page_count,
COUNT('x')*8.0/1024 as size_mb

[Code] ....

Following are the results:-

DB db_object_name cache_page_countsize_mb
TEST_DBNULL 428 3.34375
TEST_DBsysobjvalues 369 2.882812
TEST_DBsyscolpars 44 0.34375
TEST_DBsysssislog 38 0.296875
....
.....

Question- Why am I getting 428 pages for which there is no corresponding DB object? Why are so many pages present in sys.dm_os_buffer_descriptors but are missing from sys.allocation_units.

View 0 Replies View Related

SQL Server 2012 :: Query To Get CPU Usage / Memory Usage Details Of Server?

Jan 30, 2014

providing a query for fetching the data for CPU Usage, Memory usage, blocking and all details ...

I want to create a job which will run on a Node every 15 min and store data in a table for each instance...

DMV is not giving more stuff and xtended events not sure if i can store that data into a table?

View 7 Replies View Related

Analysis :: Where To Find Backup History Of Cube

Aug 7, 2015

Where to find history of cube backup? I am new to analysis services.

View 4 Replies View Related

A Simple TSQL To Find The Last N RUN Status Of The SQL Jobs / Job History

Feb 22, 2008

Replace the <<job name>> with the actual job name
---Last n RUN status of the job------


SELECT TOP 20

SJ.name 'JOB Name'

,'Run date : ' +

REPLACE(CONVERT(varchar,convert(datetime,convert(varchar,run_date)),102),'.','-')+' '+

SUBSTRING(RIGHT('000000'+CONVERT(varchar,run_time),6),1,2)+':'+SUBSTRING(RIGHT('000000'+CONVERT(varchar,run_time),6),3,2)+':'+SUBSTRING(RIGHT('000000'+CONVERT(varchar,run_time),6),5,2) 'Start Date Time'

,SUBSTRING(RIGHT('000000'+CONVERT(varchar,run_duration),6),1,2)+':'+SUBSTRING(RIGHT('000000'+CONVERT(varchar,run_duration),6),3,2)+':'+SUBSTRING(RIGHT('000000'+CONVERT(varchar,run_duration),6),5,2) 'Duration'

,CASE run_status WHEN 1 THEN '1-SUCCESS' WHEN 0 THEN '0-FAILED' ELSE CONVERT(varchar,run_status) END AS 'Status'
,Step_id

,[Message]

,[Server]

FROM MSDB..SysJobHistory SJH

RIGHT JOIN MSDB..SysJobs SJ

ON SJ.Job_Id = SJH.job_id

WHERE SJ.name LIKE '%<<job name>>%'

AND Step_ID = 0 --Comments this line if you want to see the status of each step of the job

ORDER BY run_date DESC, run_time DESC, step_ID DESC

---Last n RUN status of the job---------

View 7 Replies View Related

SQL Server 2008 :: How To Find History Of Blocking Batch / Query

Mar 4, 2015

I see some time email say blocking took place and but it get resolved itself. how to find the history of batch/query which blocked another SPID?

View 3 Replies View Related

How To Find Index Size / Usage (mb)

Nov 18, 2004

I'm trying to establish the mb usage of a series of nonclustered indexes, I'm used to using the manage indexes GUI in 6.5, and showcontig doesn't quite give me what I want, any suggestions?

View 2 Replies View Related

Index Usage - Find Unused Indexes

Sep 13, 2004

Can anyone tell me a good way to monitor which indexes are not being used? Over time, I'm sure there are extraneous indexes in our database, which I would like to get rid of.

Any ideas would be appreciated.

Thanks,
Rob

View 3 Replies View Related

Transact SQL :: Query To Find Out Database Usage

Sep 15, 2015

I can use Profiler to see database usage activity. However, in addition to it, is there a good query I can use to see whether user databases are being used (last select, last update, last alter or last delete etc., with date/time stamp)?I am looking for both SQL2000 and SQL2005 as we need to decommission some of the older servers.

View 6 Replies View Related

SQL Server Admin 2014 :: How To Find Memory Usage By Index

Oct 4, 2015

I want to create a lot of index for my database for performance.

But I need find memory usage by indexes.

How to find memory usage by index in sql server?

View 1 Replies View Related

SQL 2012 :: Query For History Table?

Nov 3, 2015

I have order history table that tracks who worked on a order

ID. ColA. ColB. ColC
1. Process 1234
2. Work. 7666
3. Return. 6789
4. Work. Null Role1
5. Return. 6538

I want a query to return recent colB or ColC where colA or colB are not null. In this case row 4

I have order history table that tracks who worked on a order

Another example

ID. ColA. ColB. ColC
1. Work. 1234
2. Process. 7666
3. Return. 6789

I want a query to return most recent colB or ColC where colB or ColC are not null. In this case row 2

View 1 Replies View Related

SQL Server 2012 :: Password History Table

May 7, 2014

I have a table of users including: UserName, Password (comuted col), FirstName, LastName, Address and other details....

I have to keep 10 Recent passwords , so I created another table "ut_Password " (Table2)

This table contains the following columns : Username, Password , and Password_Date.

I searched a lot but could not find something similar in my opinion need SP for it.

- 10 row Max for Password History in table 2
- when user change password it's need to be uniqe and it should not appear last 10 passwords
- Each user can have a maximum of 10 lines containing history password table
- Most old password deleted and replaced with a new password will enter the correct date (FIFO method first in first out).

View 9 Replies View Related

SQL Server 2012 :: Getting End Dates For History Segments?

Aug 14, 2014

I have a customer history table with the follow structure and data:

CustomerID Tier StartDate RecordStatus

123 A 01/01/2013

View 3 Replies View Related

SQL Server 2012 :: Getting End Dates For History Segments

Aug 14, 2014

I have a customer history table with the follow structure and data:

CustomerID Tier StartDate RecordStatus

123 A 01/01/2013 1
123 A 03/01/2013 0
123 B 03/01/2013 1
123 B 06/01/2013 0
123 A 08/01/2014 1
456 C 02/01/2014 1

CREATE TABLE TEMPHISTORY(
CUSTOMERID VARCHAR(11),
TIER VARCHAR(10),
STARTDATE DATE,
RECORDSTATUS TINYINT)

[Code] .....

The RECORDSTATUS value of 1 means the record is active. A corresponding record of the same CustomerID, Tier. in startdate chronology, with a value of 0 indicates that the previous record with the status of 1 has now terminated and the startdate of the record with recordstatus of 0 is the start date of the termination of the previous record, or better stated, the end date of the previous record.

What I need to do is re-record the above data the startdate of each terminated record become an enddate for the previous record, minus 1 day, as follows:

CUSTOMERID TIER STARTDATE ENDDATE
123 A 01/01/2013 02/28/2013
123 B 03/01/2013 05/31/2013
123 A 08/01/2014 NULL
456 C 02/01/2014 NULL

View 1 Replies View Related

SQL 2012 :: Select Statement From History Table?

Jan 26, 2015

Project has 2 tables process(parent) and processchild(child).

Project workflow recorddsany changes to these tables as a history.

I want to find out all the process that are in status = saved(1) where processchild is at status = started(1).

Here is example.

Process table

PK, processid, status , other data
1, 1, 1,...
2, 1, 2,...
3, 2, 1,...
4, 3, 1,...

ProcessChild table

PK, processid, processchildid status, other data
1, 1, 1, 1,..
2, 1, 1, 2,..
3, 1, 2, 1,...
4, 1, 2, 2,...
5, 2, 1, 1,..
6, 2, 1, 2,...
7, 2, 2, 1,...
8, 3, 1, 1,..

I want to find out all the processes where processchildid=2 and processchild.status =1

View 3 Replies View Related

SQL 2012 :: View Server Agent Job History ONLY

Jul 6, 2015

I want to give developers access to view SQL Job history, but not let them inhert permission to create local jobs like they get with the fixed server roles.

how can this be achieved? Or can it only be achieved by giving them access to the tables in msdb and querying them?

View 1 Replies View Related

SQL 2012 :: Database Failover History In AlwaysOn Group

Jun 16, 2014

If there is a history kept somewhere of failover events of a database in an AO group? I have 2 replicas with automatic failover and I'm looking for a history of failovers.

View 0 Replies View Related

SQL Server 2012 :: RAISERROR Not Showing Custom Message In Job History

Apr 1, 2014

So I have two steps in my job that check conditions and call RAISERROR to gracefully complete the job if those conditions are met (meaning I only want the final step to run in a specific situation). Works great, except for some reason my messages are not making it into the log. It is appending the SQLSTATE, and a default error instead of anything that I'm putting into RAISERROR.

View 2 Replies View Related

SQL 2012 :: Copy SSRS Report Snaphot History To Another Folder

Mar 17, 2015

How can I make a copy of an SSRS Report history snaphot to another folder in Report Manager. I have a report that has history snapshots. But want a copy of the report snapshot history in another folder for users to view easier in Report Manager.

View 0 Replies View Related

SQL 2012 :: Retrieve Job History For All The Jobs Using Central Management Server?

Apr 2, 2015

Is there anyway we could retrieve the job history for all the jobs in all the sql server using Central Management Server?

View 0 Replies View Related

SQL 2012 :: How To Track SPs To TempDB Usage

Apr 10, 2015

I can get a snapshot of tables in tempDB, but I would like to track which procs are causing the load in the tempDB.

I think I can sample and record objects in the tempdb, but I would like to record the proc creating the most tempDB usage, and disk read/writes associated with those procs.

The DMV's give usage in the individual DB's, but what's a good way to correlate procs in the DB's to tempdb usage?

View 9 Replies View Related

SQL Server 2012 :: List Catalog Reports With No Execution History Returns 0 Rows

Feb 13, 2015

comparing UNIQUEIDENTIFIER columns..This query returns several rows where the [ReportId] and [LastRunDate] columns are both NULL:

SELECT [c].[Name],[c].[ItemID],[xl].[ReportID]
, MAX([TimeStart]) [LastRunDate]
FROM [dbo].[Catalog] [c]
LEFT JOIN [dbo].[ExecutionLogStorage] [xl] on [c].[ItemID] = [xl].[ReportID]
WHERE [c].[Type] NOT IN (1,5) -- Not a folder or a data source!
group by [c].[Name],[c].[ItemID],[xl].[ReportID]
order by 4

However, trying to just list catalog reports with no execution history returns 0 rows, but I'm expecting it to return a row for every NULL [ReportId] from the above query:

SELECT *
FROM [dbo].[Catalog]
WHERE [Type] NOT IN (1,5) -- Not a folder or a data source!
AND [ItemID] NOT IN (SELECT [ReportID] FROM [dbo].[ExecutionLogStorage])

I even tried casting [ItemId] and [ReportId] columns in the 2nd query to VARCHAR(255), and still got no rows, but the following queries return 0 rows and 1 row (respectively).

select * from [dbo].[ExecutionLogStorage] where [ReportID] = '0BB2209C-7736-46C8-AD02-4614EBA4F0F1'
select * from [dbo].[Catalog] where [ItemID] = '0BB2209C-7736-46C8-AD02-4614EBA4F0F1'

View 4 Replies View Related

SQL 2012 :: High Percent Signal Waits With Very Low Cpu Usage

Nov 15, 2013

I am trying to find out what could be causing this issue. Why would we be waiting on cpu when its barely being used. Signal waits are varying from 35 to 55% and cpu usage is only at 5% usage.We are using Windows Server 2012 with SQl Server 2012 Standard edition with cpu5. There are 3 instances on the server each with max memory 50gb memory and the server has a total of 190gb memory. The machine is a 12 core machine with hyperthreading enabled.

View 8 Replies View Related

SQL Server 2012 :: Avoiding If Conditional To Reduce CPU Usage

Jun 23, 2015

I have a function with multiple if ( condition) which is CPU intensive. How could I avoid this.

CREATE FUNCTION prici.[fn_pricipalamt]
(
-- Add the parameters for the function here
@Tcode char(10),
@SecTypeCode1 char(10),
@SecTypeCode2 char(10),
@TradeAmount float,

[Code] .....

View 2 Replies View Related

SQL Server 2012 :: Calculating Number Of Hours In Usage

Jul 23, 2015

I am trying to calculate the number of hours a device has been used and I cant find how. I need a query that calculated and does an average of the number of hrs used in a week.

View 5 Replies View Related

SQL Server 2012 :: Increase In Usage Percentage Of All Columns

Aug 24, 2015

I have a table which gets updated with the usage figure every week. Any similar t-sql which returns the increase in usage percentage of all the columns.

View 8 Replies View Related

SQL Server 2012 :: How To Capture CPU And Memory Usage For A Stored Procedure

Jan 19, 2015

I have around 100 packages (all [packages run at same time) each package calls a stored Procedure once the Stored proc Execution is completed the package will write the log information into a log table, here how can i capture the CPU and Memory usage for execution of each stored proc.

View 2 Replies View Related

SQL 2012 :: Memory Usage Extremely High And Causes Server To Crash

Jun 11, 2015

I have been having issues with our SQL server for awhile now. It seems to run out of memory every few days and when I look at the memory dump, the MEMORYCLERK_SQLOPTIMIZER seem to take over memory and eventually cause the server to crash.

Here is the SQL verison we are using: Microsoft SQL Server 2012 (SP1) - 11.0.3460.0 (X64) Jul 22 2014 15:22:00 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.2 (Build 9200: ) (Hypervisor)..It is on a VM on Windows 2012 server. It has 20gb of RAM allocated to it and the MAX Server Memory is set to 16.5gb.

I have seen the MEMORYCLERK_SQLOPTIMIZER grow to about 11gb at the time of the server crash. Why that is happening? What is causing the memoryclerk_sqloptimizer to get so high? I have looked it up and it looks like it has to do with ad hoc requests, but is there something I can do to bring that memory down when it gets so high so that I can prevent a server crash?Do we just need to add more memory or is there a memory leak somewhere?

View 2 Replies View Related

SQL 2012 :: Tracking Page File Usage - Values Appear Incorrect

Jul 31, 2015

I'm looking for monitoring page file usage for SQL Server. Ultimately, I'm trying to prove that there is not a problem with a server and that it is not struggling with the workload, but someone has looked at page file usage and suggested there is a problem.

I've set up performance counters running from a separate server, but the counters relating to page file usage seem wildly inaccurate. E.g., I've got (_Total\% Usage), and (Total)\%Usage Peak), with Total Usage showing a constant value of 64, and the peak showing 92. I've also got Process (sqlservr)Page File Bytes and Page File Bytes Peak, which are showing values like 61,275,688,960 and hardly dropping below this.

If I look at Performance Monitor directly the values shown in here correlate with this, as shown in my first screenshot. However, if I look at resource monitor and look at disk activity, you can see that there is basically nothing going on (screenshot 2).

The server is more than equipped to deal with the workload, and looking at other counters for SQL there is no indication of problems, e.g. page life expectancy has a minimum value showing of 576,258, there is never less than 6GB of free RAM, SQL compilations are at a maximum of 5% of batch requests.

It is a physical server with 2x 8 core multi threaded CPUs, 64GB RAM with 58GB as the SQL server maximum. Hopefully this is enough info, but I'm happy to check anything else.

View 7 Replies View Related

SQL 2012 :: High Memory Usage On Idle Reporting Services

Aug 25, 2015

I know I should probably be posting this in the RS section but I have a Win 2008 R2 server and RS 2012 along with SSIS and Database server installed. I also have SQL 2005 instance with Sql 2005 SSIS running on the server.

I saw that Reporting services was consuming 9gb of ram a few days ago with no published reports. It's just a default install.

So I investigated the settings and the document: [URL]......

I added this section to the reportserver.config file to restrict memory usage

<WorkingSetMaximum>512000</WorkingSetMaximum>
<WorkingSetMinimum>128000</WorkingSetMinimum>

Any example altering the reportserver.config file in your standard build of sql server reporting services?

View 0 Replies View Related

SQL 2012 :: Does Trace Flag 2371 Indirectly Reduces CPU Usage

Oct 13, 2015

After carefully analyzing the situation for almost a month, I pulled the trigger and made an exception at work; we did enable trace flag 2371. We have some tables with billion of rows and outdated statistics causing horrible plans. Tried several methods to update those, but did not solve the problem or was too CPU intensive, causing other issues.

Anyway, one of the side effects I am seeing so far is average vCPU went down by almost 40%. Nothing out of usual (besides the flag) has been enabled or was executed. So my assumption is, CPU hungry plans are now gone or reduced.

View 2 Replies View Related

SQL In-Memory :: How To Find Memory Usage By Index

Oct 4, 2015

i want to create a lot of index for my database for performance.but i need find memory usage by indexes.

How to find memory usage by index in sql server?

View 9 Replies View Related

SQL 2012 :: Agent Job History Shows Step Still Running But Shows Start And End Times?

Jul 1, 2015

I have a SQL Agent job that runs at 4:15 in the morning. The job has 5 steps, each step only runs if the preceding step succeeds. The second step, which calls an SSIS package that does the main processing, appears to finish as it goes on to the next step; however, when looking in 'View History' there are 2 entries for this step - the first one shows it as still running (Circled Green Arrow) but with a start and end time. The second entry says the job succeeded.

I have been seeing conflicts, such as deadlocks, with later jobs. I suspect this job is causing the conflicts - maybe the package is still running in the background instead of having actually completed?

what conditions a job step my be showing in the job history as both running AND completed successfully?

View 6 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved