Archive for the ‘Database Engine’ Category

SQL Server health check using PowerShell and T-SQL   15 comments

I am back with another interesting blog which hopefully the production DBAs would find more useful. We often have the need to capture aggregated data related to SQL Server health which can not only be captured quickly but also be able to customize as per the requirements.

We have many tools available to capture the performance data but it mostly has some data lag and does not tell us what’s currently running on the system.

Since last few days, I have been working on a script based solution which helps us to perform quick health check of our production instance to resolve any production issue with this blog, I intend to share the solution as I have found it quite useful Read the rest of this entry »

Query Store: Exploring new features in SQL Server – vNext   1 comment

Troubleshooting performance problem is one of the challenging and most often faced situation in DBA’s life. Following diagram shows three major scenarios where DBA’s get involved and spend nights troubleshooting performance problems.

Note: This is with community technical preview 2 of SQL Server 2016, things may change with the RTM release


Read the rest of this entry »

Why is affinity mask negative in sp_configure   Leave a comment

While looking at a SQL server health report, I found affinity mask parameter in sp_configure output showing a negative value.


name                                minimum     maximum     config_value run_value
----------------------------------- ----------- ----------- ------------ -----------
affinity mask                       -2147483648 2147483647  -1066394617  -1066394617


This output was from a SQL Server 2008 R2 instance running on Windows Server 2008 R2.


Affinity mask is a SQL Server configuration option which is used to assign processors to specific threads for improved performance. To know more about affinity mask, read this. Usually, the value for affinity mask is a positive integer (decimal format) in sp_configure. The article in previous link shows an example of binary bit mask and corresponding decimal value to be set in sp_configure. Read the rest of this entry »

A read operation on a large object failed while sending data to the client   2 comments

I’ve come across this error message quite a few times. Last weekend, it was when I had to dig deeper due to repetitive alerts. Apparently, I couldn’t find any article on this error message, hence this blog.

Error Message

Error: 7886, Severity: 20, State: 1
A read operation on a large object failed while sending data to the client. A common cause for this is if the application is running in READ UNCOMMITTED isolation level. This connection willbe terminated.

Below are my observations/research on this error message which also includes my leanings on working on similar cases while I was at Microsoft.

Read the rest of this entry »

Latch Timeout: To worry or not to?   1 comment

Hey, I seldom see these messages in ERROLROG, is this something I should be concerned about?

[WARNING] 2013-02-14 05:25:15.97 spid61      Timeout occurred while waiting for latch: class ‘DATABASE_CHECKPOINT’, id 0000000017560830, type 4, Task 0x000000000043EBC8 : 0, waittime 300, flags 0x1a, owning task 0x00000000009F0508. Continuing to wait.

The short answer is – No. This ‘particular’ message is not to be concerned about for the period it occurred on this server.

Why do I say that? Want to know more. Then go ahead and read the longer version.

Read the rest of this entry »