I'm collecting performance data via DMV to check the buffer hit ratio on several servers. Sometimes I get values high above 100% hit Ratio.
Example (50050%):
SQLServer:Buffer Manager:Buffer cache hit ratio 2002
SQLServer:Buffer Manager:Buffer cache hit ratio base 4
Our server administrator forwarded some messages from SCOM that indicate:
SQL DB Engine 2012 Page Life Expectancy and Buffer Cache Hit Ratio is too low
When I logged into the offending server, I could not find anything in the SQL Log File that indicates this.
I was wondering how did SCOM identify this issue - where in SQL Server would this have been reported to SCOM?
Does anyone know how to measure the buffer cache hit ratio? I have been reading a lot about it but can't find this measurement in Performance Monitor.
Thanks
What is better values for Buffer Cache Hot Ratio in SQL Server 2012. Is the values changed from 2008r2 to 2012?
View 2 Replies View RelatedI am wondering if 100% buffer cache hit ratio is considered not good in general?
Are there instances that it is actually bad and can contribute to server performance degradation?
Any thoughts on the topic most welcome :)
--------------------
keeping it simple...
This issue just happen recently. The buffer cache ratio went from > 90%to 50% and has slowly been climbing back up over 8 hours or so. Itscurrently @ 76%. Is this something I should take action on immediately?It seems to be coming back to normal...
View 1 Replies View RelatedHiI have trouble with MSSQL2000 SP4 (without any hotfixes). During last twoweeks it start works anormally. After last optimalization (about few monthsago) it works good (fast, without blocks). Its buffer cache hit ratio wasabout 99.7-99.8. Last day it starts work slow, there was many blocks anddedlocks. There are no any queries, jobs and applications was added. Nowbuffer cache hit ratio oscilate about 95-98. I try update statistics andreindex some hard used tables, but there is no effect or effect is weryshort (after few hours problem return).Mayby somene know what it could be?Is it possible to estimate how each table (using DBCC SHOW_STATISTICS orDBCC SHOWCONTIG or others) how the table affect on total buffer cache hitratio?Marek---www.programowanieobiektowe.pl
View 1 Replies View RelatedHi,
In m*y SQL server 7.0,
when using Performance, I see in graph: Buffer Catche Hit Ratio Counter
(SQLServer Buffer Manager objects)always is maximum (100). What does this mean ? What's the Buffer Catche Hit Ratio?
How do I configure SQL server to performance?
Thanks in advance.
I've a spatial (GIS) Data which is used frequently insertion, updation.
5 lakh records insertion in daily basis. when I trying to generate reports last 3 days or one weak, it takes 20-30 minute.
very disappointing while playing with clients. how to boostup and perform fast.
I think as so once we set query plan in buffer permanently then i would be faster than ever.
Extending the buffer pool onto an SSD drive that is shared ? For instance, if we had a mirrored system drive(The C logical partition) on SSD's, can we use the remaining space on that mirrored partition to extend my SQL 2014 buffer pool ? I understand that in this scenario, there is some competition for the I/O throughput between SQL Server's extended buffer pool and the OS. We intend to have the pagefile on a different disk, other than this specific SSD.
View 11 Replies View Relatedsyntax of scripting out the linked server.
View 4 Replies View RelatedI have a server that has several DBs .. some are log shipping(ls_) and others are not. The current TRAN backup script just does a:
select name from sys.databases
where name != 'ls_xxxx'
and name != 'ls_yyyyy'
Is there a way to check a status byte or something so I can remove the hard coded names?
select name from sys.sysdatabase
where status = 'I log ship' -- ;)
I checked several places, but found nothing. Finding other status like RECOVERING et al ... is easy
Recently one of our DB went to suspect mode, we have resolved it(repair_allow_dataloss) and DB came online but when we fire CheckDB on that it is throwing following error
Msg 7985, Level 16, State 2, Line 2
System table pre-checks: Object ID 3. Could not read and latch page (1:355) with latch type SH. Check statement terminated due to unrepairable error.
DBCC results for 'xxxxxxx'.
Msg 5233, Level 16, State 98, Line 2
Table error: alloc unit ID 196608, page (1:355). The test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. The values are 12716041 and -4.
CHECKDB found 0 allocation errors and 1 consistency errors not associated with any single object.
CHECKDB found 0 allocation errors and 1 consistency errors in database 'xxxxxxx'.
And error log is also continuously popping the below message
Error:
824,
Severity: 24,
State: 2.
SQL
Server detected a logical consistency-based
I/O
[code]....
I have a user who is trying to run a job (call an Stored Procedure) which connects to a Linked Server. He can run it OK using EXEC SP_Name but when he runs from the SQL Jobs it gives him the error: Linked servers cannot be used under impersonation without a mapping for the impersonated login.[SQLSTATE 42000] (Error 7437). The step failed.The Linked Server was setup using another account. Would this be fixed if I add the new user to the Security section of Linked Server without breaking the current configuration?
View 6 Replies View RelatedLast night we had a problem caused by a stored proc being deployed to only one server rather than two. I want a way to ensure that we can automatically run on a schedule a check to ensure that the 2 procs on 2 servers are the same.
I need a less robust solution that I implement tomorrow that doesn't involve buying third party tools and a more robust long term strategy that could if need make use of third party tools.
I encountered the following error while attempting to preview an RDL report I was developing in VS2010 using SSDT:"The size necessary to buffer the XML content exceeded the buffer quota"
View 3 Replies View RelatedWe have a set of reports with same header section in all the reports. So while developing a new report i used to copy that header section to the new report with same dataset names (without any change) , but while rendering the report it is throwing error " The size necessary to buffer the XML content exceeded the buffer quota".
View 2 Replies View RelatedHi
I have a master package that executes a series of sub packages run from a SQL Agent job. One of those sub packages has been stable for a week, running at least once per day, but it just failed despite having been run once already today with the same set of input data.
There were a series of errors showing in the event log for the Execute Package Task starting with "Buffer Type 15 had a size of 0 bytes.", then "The buffer manager failed to create a new buffer type.", then "The Data Flow task cannot register a buffer type. The type had 32 columns and was for execution tree 3.", then "The layout failed validation." and finally "Error 0xC0012050 while loading package file "C:[Package].dtsx". Package failed validation from the ExecutePackage task. The package cannot run.".
SQLIS.com reports the constant for the error code as DTS_E_REMOTEPACKAGEVALIDATION ( http://wiki.sqlis.com/default.aspx/SQLISWiki/0xC0012050.html ).
I then ran the package on my dev machine in BIDS and it worked fine, so I re-ran the job on the server and this time that package executed ok, but another one fell over but did not put anything in the event log.
Does any one have any idea what happened?
TIA . . . Ed
I have 4 servers, 2 each for application (Dev & Prod)
DEV 1 & DEV 2 are standalone servers
Prod 1 & Prod 2 are Windows Clustered Servers.
From one application to other we do Distributed transactions. Dev 1 - Dev 2 or Dev 2 - Dev 1 can start DTC and working fine,but issue comes when Prod 1 - Prod 2 or Prod 2 - Prod 1. I get error message OLE DB provider "SQLNCLI" for linked server "xyz" returned message "No transaction is active.".
Msg 7391, Level 16, State 2, Line 3
The operation could not be performed because OLE DB provider "SQLNCLI" for linked server "xyz" was unable to begin a distributed transaction.
I have tested Dev 1 - Prod 1, Dev 1 - Prod 2, Dev 2 - Prod 1, Dev 2 - Prod 2 everything is working fine only Production servers are causing issue.
I enabled all settings needed for DTC on Cluster MSDTC service but no luck.
I am following website link:
[URL]
I require to gather status details about all the SQL Agent jobs in the environment on multiple SQL Servers.
I tried to edit the script using:
[void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO")
$sqlServerName = 'localhostdeveloper'
$sqlServer = New-Object Microsoft.SqlServer.Management.Smo.Server($sqlServerName)
foreach($job in $sqlServer.JobServer.Jobs)
{
$job | select Name, OwnerLoginName, IsEnabled, LastRunDate, LastRunOutcome, DateCReated, DateLastModified
}
but SQL Agent jobs are not reflecting in the mail output...
i have 70 SQL database servers and i setup DB Mail on the 70 Servers, i want to know is there a way to find the status of all the jobs which i assigned the DB Mail and if its working/failing... is there a script i can run on powershell or SQL to find out that information
View 1 Replies View RelatedAre there anyway I can check my last database reorg date and time using Tsql ?
View 5 Replies View RelatedWhen i have multiple sql server instances running on one server, how to check the names of that instances with out connecting to sql server management studio.
View 12 Replies View RelatedGood day everyone,
I'm experiencing a completely random warning from any given row count component within any given data flow task. It occurs sporadically. Whilst distracting, I don't see any adverse effects to the data after the packages complete. Can someone weigh in on this warning and let me know if it is indeed benign or what I maybe able to do to fix it?
Here's the warning:
"A call to the ProcessInput method for input 75997 on component "CNT Rows sent for STG table" (75995) unexpectedly kept a reference to the buffer it was passed. The refcount on that buffer was 4 before the call, and 5 after the call returned."
Thanks,
Langston
Trying to clean up after installing several SQL servers on a server. Started by installing SQL2000 enterprise as a named instance. Then added a instalation of SQL2005 enterprise again as a named instance. Then renamed OS level Server / Computer name. Now want to rename the SQL Servers or drop and then re add.
Anyway a long story short I have searched many boards and tired a bunch of things but still I come back to How can I remove things that show up in the list when you use the osql /L command from a command prompt.
Here are several things I have either tried or checked with many different varaitions:
SELECT @@SERVERNAME
SELECT SERVERPROPERTY ('MachineName'), SERVERPROPERTY ('InstanceName')
sp_helpserver
go
sp_dropserver 'MachineNameInstanceName'
GO
sp_addserver 'MachineNameInstanceName', local
GO
DECLARE @srv sysname
SET @srv = CAST(SERVERPROPERTY('ServerName') AS sysname)
UPDATE sysjobs SET originating_server = @srv
If this is a target server (you have jobs sent from a master server), you have to exclude them, so you don't convert those jobs into local jobs:
WHERE originating_server = '<old_name>'
I have been searching for a means to change the System Failure Error Check policy that comes as part of the Best Practice policies. I want to look back 24 hours. The WQL query shipped with the policy doesn't have a WHERE clause component that looks at TimeGenerated. That query looks like:
IsNull(ExecuteWql('Numeric', 'rootCIMV2', 'select EventCode from Win32_NTLogEvent where EventCode=6008 and Logfile="System"'), 0)
After searching for an example of how to do this and not finding any that are specific to PBM, I decided to fall back to a very basic approach - use wbemtest.exe to try out where clause additions and see how they work, then plug the result into the policy and see if it works. As a start, I tried the following query using wbemtest.exe:
select Event Code
from Win32_NTLogEvent
where EventCode = 6008
and Logfile = 'System'
and TimeGenerated > '20130101010000.000000–000'
This works great in wbemtest.exe. My next step was to plug this into the policy condition expression as follows: IsNull(ExecuteWql ('Numeric', 'rootCIMV2', 'select EventCode from Win32_NTLogEvent where EventCode=6008 and Logfile="System" and TimeGenerated > "20130101010000.000000–000"'), 0)
When I try to manually evaluate this policy in SSMS, I receive an "Invalid Query" error message.I assume that SWbemDateTime isn't available to use inside Policy Based Management policies. All the examples of how to handle the kind of dynamic date creation I have seen are for use in PowerShell, VBScript, or SSIS. I've played with using DateDiff, DateAdd, and GetDate inside the query string, with no success.
Why does the ExecuteWql above fail?Is it at all possible to dynamically generate a datetime (say, 24 hours ago) as part of the query string parameter of the ExecuteWql call?What might that look like?
On Microsoft performance monitor, what is the difference between SQL Server Cache Manager: Cache Hit Ratio and SQL
Server Buffer Manager: Buffer Cache Hit Ratio? We have a production server where the buffer cache hit ratio is
consistently at 99%, which is normal. However, the cache hit ratio is 73%. What is the difference between the two
hit ratios, and why would we have such a significant difference between the two?
Any help or advice would be much appreciated.
The scenario is as follows: I have a source with many rows. Each row has a column called max_qty_value. I need to perform a calculation using another column called qty. This calculation is something similar to dividing qty/(ceiling) max_qty_value. Once I have that number I need to write an additional duplicate row for each value from the prior calculation performed. For example, 15/4 = 4. I need to write 4 rows to the same target table as in line information for a purchase order.
The multicast transform appears to only support fixed and/or predetermined outputs. How do I design this logic in SSIS to write out dynamic number of rows to a target table.
Any ideas would be greatly appreciated.
thanks
John
I have been seeing this strange statistics in one of our servers. The cache hit ratio has gone beyond 100%, it is currently showing 124%. Has anyone seen this before.
Thanks
I have a large dell server with 4 processors, and 8 gig of memory on win 2000 advanced with sql 2000 enterprise edition running a 3rd party app. My cache hit ratio averages about 76%. I thought the gneral rule was if you get below 80% to add more memory. However my memory manager shows I am only using 71% of my memory and have a full gig available. I have the sql server set to use about 7.1 gig of the 8 gig on the server. My ? is if I am only using 71% of my memory, will will adding more memory actually help my cache hit ratio
View 5 Replies View RelatedHello,
I have a calculated member that is a ratio calculation. It works fine but I would like that the value for the "All" member to be the sum of the children.
So I have the following MDX where the calculated member [Dim Group Rubric].[Group Rubric Description].[BO + ADM Cost] does not give me good results for the "All" member:
WITH
MEMBER [TOTAL YEAR CONTRACT] AS sum({[Total Year]},[Dim Contract].[Contract Number].[563131])
MEMBER [TOTAL YEAR BU] AS sum ([Total Year])
MEMBER [Dim Group Rubric].[Group Rubric Description].[TOTAL BU PVN] as
([Dim Group Rubric].[Group Rubric Description].[PVN], [TOTAL YEAR BU])
MEMBER [Dim Group Rubric].[Group Rubric Description].[TOTAL BU BO + ADM] as
([Dim Group Rubric].[Group Rubric Description].[BO + ADM], [TOTAL YEAR BU])
MEMBER [Dim Group Rubric].[Group Rubric Description].[BO + ADM Cost] as
([Dim Group Rubric].[Group Rubric Description].[PVN], [TOTAL YEAR CONTRACT])
/ ([Dim Group Rubric].[Group Rubric Description].[TOTAL BU PVN], [TOTAL YEAR BU])
* ([Dim Group Rubric].[Group Rubric Description].[TOTAL BU BO + ADM], [TOTAL YEAR BU])
SELECT
NON EMPTY {[TOTAL YEAR CONTRACT],[TOTAL YEAR BU]}
ON 0,
{[Dim Group Rubric].[Group Rubric Description].[BO + ADM Cost]}
*
UNION(
[Dim BV Organisation Finance].[Business Unit].[All]
,FILTER(
[Dim BV Organisation Finance].[Business Unit].children
,[Dim BV Organisation Finance].[Business Unit Code].currentmember.name
= '071MIND1'
)
,FILTER(
[Dim BV Organisation Finance].[Business Unit].children
,[Dim BV Organisation Finance].[Business Unit Code].currentmember.name
<> '071MIND1'
)
)
ON 1
FROM (SELECT ([Dim Year].[Year].&[2008]) ON 0
FROM (SELECT ({[Dim BV Organisation Finance].[Business Unit Code].[071MIND1]
,[Dim BV Organisation Finance].[Business Unit Code].[071DIDD1]}) ON 0
FROM [BV Contract Margin DW]))
When I launch this query I obtain:
TOTAL YEAR BU
BO + ADM Cost All - 45013 (I would like to obtain here - 90651)
BO + ADM Cost 071MIND1 - 35680
BO + ADM Cost 071DIDD1 - 54971
Please do you know what should be changed in this query to obtain the good results?
Thanks.
Guillaume
I have to get the ratio like A:B with two calculated columns. A and B both are the counts of the rows.
Please help me with this, it's really urgent!
HelloI am tring to figure out why our SQL server is a bit sluggish fromtime to time.It is running a dual XEON, with 2.5 GB RAM, and a fast SCSI I/O subsystem setup as follows.OS, mirrored 2 drivesSQL DATA 16 HDD RAID 10SQL LOG 4 HDD RAID 10SQL tempdb 4 HDD RAID 10OS = win 2003SQL = sql2000 standard editiondbcc showcontig shows me nothing special, it looks okI launch performacne monitor and add SQL server cache manager: hitratioand it is constantly at 7% and never changes up or down, it is justconstant.Can this be correct? and if so it sounds rather bad, we have ahandfull of large tables that are heavily used and enough RAM to holdthem all in RAM so I really do not understand why the cache hit ratiois not higher.Any hints would be greatrgdsMatt
View 9 Replies View Related