Hot answers tagged

34

SQL Compilations/sec is a good metric, but only when coupled with Batch Requests/sec. By itself, compilations per sec doesn't really tell you much. You are seeing 170. If batch req per sec is only 200 (a little exaggerated for effect) then yes, you need to get down to the bottom of the cause (most likely an overuse of ad hoc querying and single-use ...


19

Have a look at the following perfmon counters: SQL Server, Buffer Manager Object: Page lookups/sec Page reads/sec Readahead pages/sec SQL Server, Access Methods Object Full Scans/sec Range Scans/sec Skipped Ghosted Records/sec SQL Server, Wait Statistics Object Page IO latch waits SQL Server driving a high number of IO requests would be corroborated ...


12

To start use Glenn Berry's Diagnostic queries and Adam Machanic's SP_Whoisactive to find out whats really happening. First see which database files have the most IO bottleneck by running this query(Query by Glenn Berry) SELECT DB_NAME(fs.database_id) AS [Database Name] , mf.physical_name , io_stall_read_ms , num_of_reads , ...


11

Not sure why you want to use performance counters for this when you can get it from a simple query. And in fact while you can get this information about log files from performance counters (Log File(s) Size (KB) / Log File(s) Used Size (KB)), there is no such counter for how much space is used in a data file. ;WITH f AS ( SELECT name, size = size/128.0 ...


10

There are three relevant counters that should be recorded using PerfMon (or another 3rd party solution). The key point is to record these stats somehow. SQL Statistics\Batch Requests/sec SQL Statistics\SQL Compilations/sec SQL Statistics\SQL Re-Compilations/sec As Thomas Stringer mentioned, it's good to keep an eye on the ratio of compilations/batch ...


7

I don't know of a fully accurate and reliable way to track this. One way to get at least something potentially useful is to keep snapshots of sys.dm_db_index_usage_stats, specifically the user_lookups column for index_id 0 (RID Lookup) and 1 (Key Lookup). This DMV can be reset for many reasons, including database or instance restarts. Rebuilding (but not ...


7

I have another method to proactively monitor data file space and alert if the free space falls below a certain percentage using SQL Alert. The basics are Create a user defined error message in sys.messages. This will be used by sql agent alert. -- User-defined error messages can be an integer between 50001 and 2147483647. EXEC sp_addmessage @msgnum=...


7

Sorry, not to disparage Thomas' advice, but please take "general rules" with a grain of salt, or just throw them out the window altogether. Baseline. What is normal for your system? Is the system currently responding ok? If there is no performance issue, don't try to compare your system to some number someone plucked out of the air or potentially based ...


6

How about Database pages, Free list stalls/sec, Page reads/sec, Page lookups/sec, Readahead pages/sec? Their behavior, correlated with the event, would be very useful to know. Similar, all the counters in Memory Manager category. Could be a query that scans a large amount of cold data hits the server. As it reads the cold data, it evicts pretty much ...


6

Both AG and DBM will replicate primarily the log being generated on the principal. The buffer flush rate has some relation to the the log generation rate, but is not identical (think how 100 updates of the same row will generate 100 log records but will only flush the page once, and also think how a single insert could be described in maybe 256 bytes of log, ...


6

There is no perfmon counter that would you show you this information. Why don't you just pull this directly from SQL Server (at least as how SQL Server sees it)? select session_id, program_name from sys.dm_exec_sessions where is_user_process = 1; If you're looking for data collection, you can periodically run this and store the output of the above ...


5

Just to build on Aaron's and Kin's answers, you can do it with perf counters, but one of the user settable counters. I would: create a stored procedure that will use Aaron's query to get the free space in a single file or loop through all files and get the min/max value that's of interest create a job that will periodically run the stored proc In case ...


4

It is probably an OK indicator. Do keep in mind that AlwaysOn is shipping transaction log records not actual data. The amount of writes going to your transaction log LUNs is probably a better indicator.


4

sys.dm_os_buffer_descriptors From the official documentation on sys.dm_os_buffer_descriptors (Transact-SQL): sys.dm_os_buffer_descriptors returns pages that are being used by the Resource database. sys.dm_os_buffer_descriptors does not return information about free or stolen pages, or about pages that had errors when they were read. You are looking at ...


4

While it probably doesn't really answer your question I'll post it anyway, it may be more of a comment though. I don't think anybody outside of Microsoft can give us a definitive reason why this counter was never added. There is only minor feedback on this connect item asking for a similar thing, but it hasn't been closed as wontfix either: "Percent Data ...


3

Also there is a simple alert settings to monitor data file size: Additonal 'Low Log Space Alert' will monitor free space for transaction log file (autogrowth is off):


3

Use a server-side trace, not Profiler. Both have an impact on throughput, Profiler much more so. ClearTrace is a great tool for offline analysis of the trace files. To answer question 1), you connect to the instance not the node. Question 2), you obviously need to gather data from the node the instance is currently running on.


3

Try this: Get-Counter "$($instance):Buffer Manager\Buffer cache hit ratio" What happens is that your $instance variable isn't isolated, so therefore you need to explicitly state what the variable is and where it ends. You can do that with the above notation (dollar sign and parenthesis around the variable name). Take a look at the different outputs of ...


3

Wes. Those Perfmon counters are a good start to make sure that the VM is configured correctly (meaning, the host isn't wildly overcommitted on RAM.) However, they don't help size the VM. If you're having performance problems, you'll want to identify two things: The SQL Server's top wait during that time, and The queries causing that wait To get the top ...


2

It looks like the Performance Counters for SQL Server are DISABLED or the registry entries for the performance counters are corrupted. lodctr /E:MSSQLSERVER will reload the performance counter registry settings.


2

I dug a little deeper myself and it seems that the definition of information errors is errors with severity 10 according to the documentation of Severity and found my specific "Info errors" by profiling on the User Error Message Event. In my case it was database context switches with the message: Changed database context to 'DBNAME' and language changes ...


2

I would suspect that there is a a query kicking off that is performing a large table/index scan forcing SQL Server to dump the buffer cache and pull in the entire Table/Index into memory thus your Page Life Expectancy drastically dropping. You could look at querying the plan cache to see if you can find indications of large table scans. This previous post, ...


2

It is important that in vmware itself that the VM be guarunteed the amount of RAM that windows thinks it has. Next things to check: -- windows virtual memory is set appropriately for the VM -- SQL itself is configured for RAM appropriate to to the VM -- SQL Tempdbs are set appropriate to number of cpu cores and RAM After that, the odds are that there is a ...


2

8GB doesn't sound like very much RAM for SQL Server. How big are the databases on that server? If the problem isn't VMware, perhaps you should check what's happening in SQL Server itself. Check the Windows event viewer's application log for messages like, "A significant part of sql server process memory has been paged out. This may result in a performance ...


2

It seems like the schema used will be the default schema of the database user associated with the login you provide to the DSN. How you set this up in detail depends on factors like whether SQL Authentication is allowed to this SQL Server instance. You can set the default schema with ALTER USER. Regarding the edit (almost becoming a new question now), from ...


2

This should be possible via SQLCLR. The PerformanceCounter class is in in System.dll, which is one of the Supported .NET Framework Libraries. The Assembly containing the custom .NET code will need to be set to at least EXTERNAL_ACCESS. I will try this myself later and update this answer with the results.


2

Following @Srutzky's example of just looking at page faults/sec (not pages/sec), you can do this: DECLARE @before BIGINT, @after BIGINT; SELECT @before = page_fault_count FROM sys.dm_os_process_memory; WAITFOR DELAY '00:00:10'; SELECT @after = page_fault_count FROM sys.dm_os_process_memory; SELECT PageFaultsPerSec = (1.0*@after - @before)/10.0; It is ...


2

You should use the sys.dm_os_performance_counters DMV instead of the sysperfinfo backward compatibility view. That will report the same value, though. The DMV counter value is cumulative since the SQL instance was started. Counter values reported by Performance Monitor for this counter are per second so you'll see non-zero values only when the total lock ...


1

Ideally / in the long-term, I do agree with @Aaron (comment on the Question) about contacting SolarWinds to request that they create a custom metric for this. But even if you do that and they accept the idea, it could take a while for them to release it. In the mean time, or if you ever just want to view this info in an ad hoc fashion, you can try the ...


1

Is there a problem? That depends on your definition. Page splits occur because SQL is inserting records into a page that is already full. Full pages are great for reading, as the data is densely packed. Inserts require that page to be split into two, each one becoming 50% full. The new record is then inserted into the appropriate new page. As you can ...


Only top voted, non community-wiki answers of a minimum length are eligible